Description
You invented “Dura-Clear windows” that never need washing! Nothing sticks to them – not pollution, pollen, dirt, dust,
bird droppings, fingerprints, nothing. You’ve invested all your own savings, your parents’ savings, and some of your
friends’ savings as well into the R&D, production, and start-up of your business. It’s been three years since you started
selling your windows primarily to single-family homeowners, but now apartment building contractors across the nation
have been requesting your windows. Unfortunately, you’re already operating at full capacity. It’s time to make the
switch from single-family homes to commercial buildings, but to do so requires a large infusion of funding for
expansion. You need more of everything: space, equipment, employees, etc.
Ever the optimist, you applied to the TV show Shark Tank–where entrepreneurs compete for funding from angel
investors (“sharks”)–to see if a shark will invest in your company and serve as a mentor as well. To your surprise, you
were accepted to the show! You’ve already prepared most of your marketing and sales pitches, and now it’s time to
put together your forecasted financial statements. You intend to show the sharks that backing your business would be
a very profitable investment. You also need to determine how much funding to ask for in exchange for how much
ownership you’ll give up.
The following 2 Notes apply!
NOTE 1: You must show ALLyour work. Either your computations are in the cell behind your result, or you
must place them out to the right on the Forecasted Financials tab. If you choose to show your work out to
the right, show all steps and label your work clearly so it can be understood.
NOTE 2: Do not round computations until you have found your final answer. Then, round your result to the
nearest dollar. No pennies!
DUE: WEEK 3
It’s currently January 1, 2023, and you’ll be going on Shark Tank in 2 weeks. Using the information below, create your 5-
year Sales, Cost of Goods Sold (COGS), and Gross Profit forecast for the years 2023-2027.
Information:
1 Actual annual sales in the past:
2020 2021 2022
Actual SALES $1,200,000 $1,500,000 $1,875,000
2 Because you feel your most recent sales are the best predictor of future sales, you weight your
sales as follows: W1 = 0.2 and W2 = 0.8
3 You are certain you can get volume discounts on your parts and materials now that your Sales
are expected to increase. So, you estimate your COGS will be 52% of Sales, which is less than it
has been in the past.
Required:
1 (15 pts: 5 for showing work, 10 for accuracy) Conduct a 2-year weighted moving average to
forecast the annual sales for the years 2023-2027. Place your results on the Sales line of the
Milestone 1 Sales Forecast tab in the highlighted area.
(HINT: See Week 3 Lesson)
2 (10 pts: 2 for showing work, 8 for accuracy) Using the Percent-of-Sales method, compute Cost of
Goods Sold. Then compute Gross Profit. Place your results on Milestone 1 Sales Forecast tab in
the highlighted area.
(HINT: See Week 3 Lesson)
3 (15 pts: 3 for spelling/grammar, 12 for correct answers) Answer the questions on the Milestone
1 Questions tab.
Unformatted Attachment Preview
SCENARIO & REQUIREMENTS
SCENARIO:
You invented “Dura-Clear windows” that never need washing! Nothing sticks to them – not pollution, pollen, dirt
bird droppings, fingerprints, nothing. You’ve invested all your own savings, your parents’ savings, and some of you
friends’ savings as well into the R&D, production, and start-up of your business. It’s been three years since you st
selling your windows primarily to single-family homeowners, but now apartment building contractors across the n
have been requesting your windows. Unfortunately, you’re already operating at full capacity. It’s time to make th
switch from single-family homes to commercial buildings, but to do so requires a large infusion of funding for
expansion. You need more of everything: space, equipment, employees, etc.
Ever the optimist, you applied to the TV show Shark Tank–where entrepreneurs compete for funding from angel
investors (“sharks”)–to see if a shark will invest in your company and serve as a mentor as well. To your surprise,
were accepted to the show! You’ve already prepared most of your marketing and sales pitches, and now it’s time
put together your forecasted financial statements. You intend to show the sharks that backing your business wou
a very profitable investment. You also need to determine how much funding to ask for in exchange for how much
ownership you’ll give up.
The following 2 Notes apply!
NOTE 1: You must show ALL your work. Either your computations are in the cell behind your result, or
must place them out to the right on the Forecasted Financials tab. If you choose to show your work out
the right, show all steps and label your work clearly so it can be understood.
NOTE 2: Do not round computations until you have found your final answer. Then, round your result t
nearest dollar. No pennies!
REQUIREMENTS:
MILESTONE 1: Sales & Gross Profit Forecast (40 points)
DUE: WEEK 3
It’s currently January 1, 2023, and you’ll be going on Shark Tank in 2 weeks. Using the information below, create y
year Sales, Cost of Goods Sold (COGS), and Gross Profit forecast for the years 2023-2027.
Information:
1
Actual annual sales in the past:
2020
2021
2022
Actual SALES $1,200,000 $1,500,000 $1,875,000
2
Because you feel your most recent sales are the best predictor of future sales, you weight your
sales as follows: W1 = 0.2 and W2 = 0.8
3
You are certain you can get volume discounts on your parts and materials now that your Sales
are expected to increase. So, you estimate your COGS will be 52% of Sales, which is less than it
has been in the past.
Required:
1
(15 pts: 5 for showing work, 10 for accuracy) Conduct a 2-year weighted moving average to
forecast the annual sales for the years 2023-2027. Place your results on the Sales line of the
Milestone 1 Sales Forecast tab in the highlighted area.
(HINT: See Week 3 Lesson)
2
(10 pts: 2 for showing work, 8 for accuracy) Using the Percent-of-Sales method, compute Cost of
Goods Sold. Then compute Gross Profit. Place your results on Milestone 1 Sales Forecast tab in
the highlighted area.
(HINT: See Week 3 Lesson)
3
(15 pts: 3 for spelling/grammar, 12 for correct answers) Answer the questions on the Milestone
1 Questions tab.
– not pollution, pollen, dirt, dust,
ts’ savings, and some of your
een three years since you started
ding contractors across the nation
apacity. It’s time to make the
e infusion of funding for
pete for funding from angel
or as well. To your surprise, you
es pitches, and now it’s time to
t backing your business would be
r in exchange for how much
he cell behind your result, or you
hoose to show your work out to
d.
er. Then, round your result to the
information below, create your 527.
ales, you weight your
now that your Sales
s, which is less than it
moving average to
he Sales line of the
thod, compute Cost of
1 Sales Forecast tab in
ions on the Milestone
MILESTONE 1 SALES & GROSS PROFIT FORECAST
Fill in the yellow highlighted cells with your forecasted figures.
SHOW ALL YOUR SUPPORTING CALCULATIONS! You may do this either within the cell by using form
All your work must be shown on this sheet, not on a separate tab.
DURA-CLEAR WINDOWS, LLC
Proforma Income Statement
2020
Sales (all on credit)
Cost of Goods Sold
Gross Profit
Selling and Administrative Expense
Rent Expense
Depreciation Expense
Operating profit (EBIT)
Interest expense
Net Income before Taxes
Taxes
Net Income
Shares
Earnings per Share
########
(800,000)
400,000
2021
2022
1,500,000
(1,040,000)
460,000
1,875,000
(1,105,000)
770,000
2023
thin the cell by using formulas, out to the right, or both — clearly labeling your work.
LC
t
2024
2025
2026
2027
MILESTONE 1 QUESTIONS
1
(5 points: 1 pt for grammar/spelling, 4 pts for thought-out, correct answers)
How reliable is your Sales Forecast? Explain your answer.
2
(5 points: 1 pt for grammar/spelling, 4 pts for thought-out, correct answers)
You used a weighted moving average to forecast your sales figures. Name two other methods of forec
may produce a more accurate result, and explain why you think the results would be better with these
(HINT: See Week 1 and Week 3 Lessons)
3
(5 points: 1 pt for grammar/spelling, 4 pts for thought-out, correct answers)
Looking at your Gross Profits across the forecasted years, do you think the sharks will provide the fund
requesting? Explain why or why not.
GRADING RUBRIC
MILESTONE 1 RUBRIC
Item
Grading Criteria
Points Possible
Sales & Gross
Profit Forecast
All work shown in a well-labeled, easyto-follow format
Accuracy of results
7
18
Questions 1 – 3
All parts of question fully answered
and response shows thought and
some depth of analysis
Limited or no errors in spelling,
grammar, sentence structure or use
of language
Total
4 pts each Q
1 pt each Q
40 points
Purchase answer to see full
attachment
