Scenario & Requirements
SCENARIO & REQUIREMENTS
SCENARIO:
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!
REQUIREMENTS:
MILESTONE 1: Sales & Gross Profit Forecast (40 points)
DUE: WEEK 3
It’s cu
ently 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 XXXXXXXXXX.
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 XXXXXXXXXXPlace 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 co
ect answers) Answer the questions on the Milestone 1 Questions tab.
Milestone 1 Sales Forecast
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 formulas, out to the right, or both -- clearly labeling your work.
All your work must be shown on this sheet, not on a separate tab.
DURA-CLEAR WINDOWS, LLC
Proforma Income Statement
2020 2021 2022 2023 2024 2025 2026 2027
Sales (all on credit) 1,200,000 1,500,000 1,875,000
Cost of Goods Sold (800,000) (1,040,000) (1,105,000)
Gross Profit 400,000 460,000 770,000
Selling and Administrative Expense
Rent Expense
Depreciation Expense
Operating profit (EBIT)
Interest expense
Net Income before Taxes
Taxes
Net Income
Shares
Earnings per Share
Milestone 1 Questions
MILESTONE 1 QUESTIONS
1 (5 points: 1 pt for gramma
spelling, 4 pts for thought-out, co
ect answers)
How reliable is your Sales Forecast? Explain your answer.
2 (5 points: 1 pt for gramma
spelling, 4 pts for thought-out, co
ect answers)
You used a weighted moving average to forecast your sales figures. Name two other methods of forecasting that
may produce a more accurate result, and explain why you think the results would be better with these two selected methods.
(HINT: See Week 1 and Week 3 Lessons)
3 (5 points: 1 pt for gramma
spelling, 4 pts for thought-out, co
ect answers)
Looking at your Gross Profits across the forecasted years, do you think the sharks will provide the funding you are
requesting? Explain why or why not.
Grading RUBRIC
GRADING RUBRIC
MILESTONE 1 RUBRIC
Item Grading Criteria Points Possible
Sales & Gross Profit Forecast
All work shown in a well-labeled, easy-to-follow format 7
Accuracy of results 18
Questions 1 - 3
All parts of question fully answered and response shows thought and some depth of analysis 4 pts each Q
Limited or no e
ors in spelling, grammar, sentence structure or use of language 1 pt each Q
Total 40 points
Scenario & Requirements
SCENARIO & REQUIREMENTS
SCENARIO:
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 ALL your 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!
REQUIREMENTS:
MILESTONE 3: Analysis of Forecasted Financials (60 points)
DUE: WEEK 7
Milestone 3 is a continuation of the Course Project scenario. However, in Milestone 3, the forecasted financial statements are provided to you on the "Milestone 3 Financial Stmt data" worksheet. Do NOT use your forecasted financial statements from Milestone 2. Instead, use the data provided in this template to compute your metrics and answer questions.
You have created forecasted financials making the assumption that the sharks will provide the $1M in funding you are requesting for expansion. Now you need to determine the profitability of your proposal.
Required:
1 (40 pts) Using the "Milestone 3 Financial Stmt data" given in this template, compute several profitability measures as identified on the Milestone 3 Metrics worksheet. Place your answers to the following directly on the Milestone 3 Metrics worksheet:
Contribution Margin ratio
Breakeven in sales dollars
DOL and DFL
Growth rate of Net Income
Cash Flows from Ops & Free Cash Flow
Year of Payback for your shark investo
IRR and NPV for your shark investo
2 (20 pts) Answer questions about your results on the Milestone 3 Questions tab.
Milestone 3 Financial Stmt data
MILESTONE 3 FINANCIAL STATEMENTS
The financial statements below show Actual data (in white) and Forecasted data (highlighted in blue).
Use the data below to complete your Milestone 3 Metrics and answer the Milestone 3 Questions.
PLEASE NOTE: The figures provided below are NOT the solution to Milestone 2!
DURA-CLEAR WINDOWS, LLC
Proforma Income Statement
2020 2021 2,022 2,023 2,024 2,025 2,026 2,027
Sales (all on credit) 1,200,000 1,500,000 1,875,000 4,000,000 5,000,000 6,000,000 7,000,000 8,000,000
Cost of Goods Sold (800,000) (1,040,000) (1,105,000) (1,957,333) (2,446,667) (2,936,000) (3,425,333) (3,914,667)
Gross Profit 400,000 460,000 770,000 2,042,667 2,553,333 3,064,000 3,574,667 4,085,333
Selling and Administrative Expense (304,900) (350,500) (443,700) (1,100,120) (1,271,650) (1,443,180) (1,614,710) (1,786,240)
Operating profit (EBIT) 95,100 109,500 326,300 942,547 1,281,683 1,620,820 1,959,957 2,299,093
Interest expense (35,000) (45,000) (85,000) (70,000) (60,000) (50,000) (40,000) (30,000)
Net Income before Taxes 60,100 64,500 241,300 872,547 1,221,683 1,570,820 1,919,957 2,269,093
Taxes (36%) (36,900) (49,200) (55,600) (314,117) (439,806) (565,495) (691,184) (816,874)
Net Income 23,200 15,300 185,700 558,430 781,877 1,005,325 1,228,772 1,452,220
Shares 60,000 60,000 78,000 104,000 104,000 104,000 104,000 104,000
Earnings per Share $0.39 $0.26 $2.38 $5.37 $7.52 $9.67 $11.82 $13.96
DURA-CLEAR WINDOWS, LLC
Proforma Balance Sheet
2020 2021 2022 2023 2024 2025 2026 2027
ASSETS
Cash 30,000 40,000 30,000 60,000 60,000 60,000 60,000 60,000
Marketable Securities 20,000 25,000 30,000 40,000 40,000 40,000 40,000 40,000
Accounts Receivable 170,000 259,000 360,000 600,000 800,000 1,000,000 1,100,000 1,200,000
Inventory 230,000 261,000 290,000 693,778 867,222 1,040,667 1,214,111 1,387,556
Total Cu
ent Assets: 450,000 585,000 710,000 1,393,778 1,767,222 2,140,667 2,414,111 2,687,556
Plant and equipment 650,000 765,000 1,390,000 2,140,000 2,140,000 2,140,000 2,140,000 2,140,000
Less: accumulated depreciation (65,000) (141,500) (280,500) (494,500) (708,500) (922,500) (1,136,500) (1,350,500)
Net Plant and equipment 585,000 623,500 1,109,500 1,645,500 1,431,500 1,217,500 1,003,500 789,500
Total Assets 1,035,000 1,208,500 1,819,500 3,039,278 3,198,722 3,358,167 3,417,611 3,477,056
LIABILITIES & STOCKHOLDER'S EQUITY
Accounts Payable 200,000 310,000 505,000 748,560 949,222 1,149,884 1,296,458 1,443,031
Accrued Expenses 20,400 30,000 35,000 75,000 95,000 115,000 130,000 145,000
Total Cu
ent Liabilities 220,400 340,000 540,000 823,560 1,044,222 1,264,884 1,426,458 1,588,031
Long-term Liabilities 325,000 363,600 703,900 603,900 503,900 403,900 303,900 203,900
Total Liabilities 545,400 703,600 1,243,900 1,427,460 1,548,122 1,668,784 1,730,358 1,791,931
Common Stock ($1 par) 60,000 60,000 78,000 104,000 104,000 104,000 104,000 104,000
Capital paid in excess of par 190,000 190,000 262,000 1,236,000 1,236,000 1,236,000 1,236,000 1,236,000
Retained Earnings 239,600 254,900 235,600 271,818 310,600 349,383 347,254 345,124
Total Stockholder's Equity 489,600 504,900 575,600 1,611,818 1,650,600 1,689,383 1,687,254 1,685,124
Total Liabilities & Stockholder's Equity 1,035,000 1,208,500 1,819,500 3,039,278 3,198,722 3,358,167 3,417,611 3,477,056
Milestone 3 Metrics
MILESTONE 3 METRICS
SHOW ALL YOUR SUPPORTING CALCULATIONS! You may do this either within the cell by using formulas, out to the right, or both -- clearly labeling your work.
All your work must be shown on this sheet, not on a separate tab.
Showing your work is worth 5 points!
1 (14 pts) Based upon the figures below and the financial forecast for the years XXXXXXXXXX, compute 1a through 1d below, placing your final results in the yellow highlighted area.
HINT: All formulas needed to solve 1a - 1d come from your Week 4 Lesson and Week 4 Chapter readings.
GIVEN: 2023 2024 2025 2026 2027
Quantity of windows sold (Q) 4,000 5,000 6,000 7,000 8,000
Selling price per window (P) $1,000 $1,000 $1,000 $1,000 $1,000
Variable Costs per window (VC) $500 $600 $583 $571 $625
Total Fixed Costs (FC) $500,000 $500,000 $500,000 $500,000 $500,000
Total Variable Costs (TVC) $2,000,000 $3,000,000 $3,500,000 $4,000,000 $5,000,000
SOLVE FOR: 2023 2024 2025 2026 2027
a Contribution Margin Ratio 3 pts
b Breakeven in sales dollars 3 pts
c DOL 4 pts
d DFL 4 pts
2 (14 pts) Based upon your financial forecast for the years XXXXXXXXXX, compute 2a through 2d below FROM THE VIEWPOINT OF THE SHARK,
placing your final results in the yellow highlighted area.
(HINT: See Week 6 Lesson for 2b - 2d. No hint for 2a!)
GIVEN:
NOTE 1: The shark's investment of $1,000,000 gave him 25% ownership of your company and its profits.
NOTE 2: The shark requires a 9% Rate of Return on his investment.
NOTE 3: 2023 2024 2025 2026 2027
Cash Flows from Operations (Total for company) ($1,000,000) $372,212 $843,095 $1,066,542 $1,330,902 $1,554,349
SOLVE FOR: 2023 2024 2025 2026 2027
a Cash Flows from Operations (shark only) 3 pts
b Payback (in years) of the shark's $1M investment XXXXXXXXXXx.xx years) 3 pts
c IRR of shark's investment 4 pts
d NPV of shark's investment 4 pts
3 (3 pts) What is the growth rate of Net Income for each of the forecasted years?
(HINT: See Week 2 Lesson, horizontal analysis) 2023 2024 2025 2026 2027
4 (4 pts) Compute the following for the forecasted years. (Total company)
(HINT: See Week 1 Chapter readings for FCF formula)
GIVEN:
NOTE 1: Dividends are zero every year.
NOTE 2: $750,000 of the $1M invested by the shark was spent on Capital Expenditures in 2023. No other capital expeditures occu
ed.
SOLVE FOR: 2023 2024 2025 2026 2027
a Free Cash Flow 3 pts
b Free Cash Flow per share 1 pt
Milestone 3 Questions
MILESTONE 3 QUESTIONS
1 DOL & DFL (5 points: 1 pt for gramma
spelling, 4 pts for thought-out, co
ect answers)
a.