A B C D E F G H I ] K SCENARIO & REQUIREMENTS 1 2 3 | SCENARIO: 4 5 ! 6 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 g now apartment building contractors across the nation have been requesting your windows. Unfortunately, 2 you're already operating at full capacity. It's time to make the switch from single-family homes to i commercial buildings, but to do so requires a large infusion of funding for expansion. You need more of = everything: space, equipment, employees, etc. 12 13 1A Ever the optimist, you applied to the TV show Shark Tank--where entrepreneurs compete for funding from i 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 _j pitches, and now it's time to put together your forecasted financial statements. You intend to show the 17 sharks that backing your business would be a very profitable investment. You also need to determine how iz much funding to ask for in exchange for how much ownership you'll give up. 20 21 The following 2 Notes apply! 22 23 NOTE 1: You must show ALL your work. Either your computations are in the cell behind your resufi, 24 or you must place them out to the right on the Forecasted Financials tab. If you choose to show 25 your work out to the right, show all steps and label your work clearly so it can be understood. 26 27 NOTE 2: Do not round computations until you have found your final answer. Then, round your 28 result to the nearest dollar. No pennies! 29 i = W - 31 32 33 34 35 36 37 38 39 40 41 42 &G E& a7 43 49 50 31 52 33 34 55 56 57 58 59 60 61 62 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 guestions. '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 investor IRR and NPV for your shark investor {20 pts) Answer guestions about your results on the Milestone 3 Questions tab. B H M A C D E F G K DURA-CLEAR WINDOWS, LLC Proforma Income Statement 2,025 2,026 2,027 10 2020 2021 2,022 2,023 2,024 11 7,000,000 8,000,000 12 Sales (all on credit) 1,200,000 1,500,000 1,875,000 | 4,000,000 5,000,000 6,000,000 (800,000) (1,040,000 (1,105,000) (1,957,333) (2,446,667) (2,936,000) (3,425,333) (3,914,667) 13 Cost of Goods Sold 400,000 460,000 770,000 2,042,667 2,553,333 3,064,000 3,574,667 4,085,333 14 Gross Profit 15 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) 16 Operating profit (EBIT) 95,100 109,500 326,300 942,547 1,281,683 1,620,820 1,959,957 2,299,093 17 Interest expense (35,000) (45,000) (85,000) (70,000) (60,000) (50,000) (40,000) (30,000) 1,919,957 2,269,093 18 Net Income before Taxes 60,100 64,500 241,300 872,547 1,221,683 1,570,820 19 Taxes (36%) (36,900) (49,200) (55,600) (314,117) (439,806) (565,495) (691,184) (816,874) 20 Net Income 23,200 15,300 185,700 558,430 781,877 1,005,325 1,228,772 1,452,220 60,000 60,000 78,000 104,000 104,000 104,000 104,000 104,000 21 Shares $0.39 $0.26 $2.38 $5.37 $7.52 $9.67 $11.82 $13.96 22 Earnings per Share 23 25 26 DURA-CLEAR WINDOWS, LLC 27 Proforma Balance Sheet 2021 2027 2024 2026 2020 2023 2025 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,00 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 Current Assets: 450,000 585,000 710,000 1,393,778 1,767,222 2,140,667 2,414,111 2,687,556 650,000 765,000 1,390,000 2,140,000 2,140,000 2,140,000 2,140,000 2,140,000 Plant and equipment (141,500) (280,500) (708,500) (922,500) (1,136,500) (1,350,500) Less: accumulated depreciation (65,000) (494,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 3.417.611 3.477.056 Total Assets 1.085.000 1.208.500 1.819.500 3.039.278 3.198.722 3.358.167 LIABILITIES & STOCKHOLDER'S EQUITY 200,000 310,000 505,000 748,560 949,222 1,149,884 1,296,458 1,443,031 Accounts Payable 42 Accrued Expenses 20,400 30,000 35,000 75,000 95,000 115,000 130,000 145,000 1,426,458 1,588,031 43 Total Current Liabilities 220,400 340,000 540,000 823,560 1,044,222 1,264,884 44 Long-term Liabilities 325,000 363,600 703,900 603,900 503,900 403,900 303,900 203,900 1,548,122 1,668,784 1,730,358 1,791,931 45 Total Liabilities 545,400 703,600 1,243,900 1,427,460 104,000 104,000 104,000 104,000 46 Common Stock ($1 par) 60,000 60,000 78,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 48 Retained Earnings 239,600 254,900 235,600 271,818 310,600 349,383 347,254 345,124 49 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 Equit 1 085.000 1.208.500 1.819.500 3.039.278 3.198.722 3.358.167 3.417.611 3.477.056 50 Scenario & Requirements Milestone 3 Financial Stemt data Milestone 3 Metrics Milestone 3 QuestionsA B c D E F G H i K I M N o B a MILESTONE 3 METRICS 2 3 |[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. 4 All your work must be shown on this sheet, not on a separate tab. 5 Showing your work is worth 5 points! 6 7 8 9 1 (14 pts) Based upon the figures below and the financial forecast for the years 2023 - 2027, compute 1a through 1d below, placing your final results in the yellow highlighted area. 10 HINT: All formulas needed to solve 1a - 1d come from your Week 4 Lesson and Week 4 Chapter readings. 11 12 GIVEN: 2023 2024 2025 2026 2027 13 Quantity of windows sold (Q) 14 Selling price per window (7] b Variable Costs per window (VC) 1 Total Fixed Costs (FC) 17 Total Variable Costs [TVC) 52,000,000 | 3,000,000 54,000,000 18 19 SOLVE FOR: 2023 2024 2025 2026 2027 20 a Contribution Margin Ratio 3 pis 21 b Breakeven in sales dollars 3 pits 22 c ooL 4 pts 3 d DFL 4 pts S @ PR M e n 26 78 2 (14 pts) Based upon your financial forecast for the years 2023 - 2027, compute 2a through 2d below FROM THE VIEWPOINT OF THE SHARK, 79 placing your final results in the yellow highlighted area. 30 (HINT: See Week 6 Lesson for 2b - 2d. No hint for 2a!) 31 32 GIVEN: 33 NOTE 1: The shark's investment of $1,000,000 gave him 25% ownership of your company and its profits. 34 NOTE 2: The shark requires a 9% Rate of Return on his investment. 35 36 NOTE 3: 2023 2024 2025 2026 2027 Cash Flows from Operations (Total for 37 company) ($1,000,000) $372,212 $843,095 | $1,066,542 | $1,330,902 $1,554,349 38 39 40 SOLVE FOR: 2023 2024 2025 2026 2027 Cash Flows from 41 Operations (shark only) 13 pts Payback (in years) of the b shark's $1M investment 42 (x.xx years) 3 pts 13 C IRR of shark's investment 4 pts 14 d NPV of shark's investment 4 pts 45 16 47 3 (3 pts) What is the growth rate of Net Income for each of the forecasted years? 18 (HINT: See Week 2 Lesson, horizontal analysis) 2023 2024 2025 2026 2027 19 504 (4 pts) Compute the following for the forecasted years. (Total company) 4 (HINT: See Week 1 Chapter readings for FCF formula) 5 GIVEN: NOTE 1: Dividends are zero every year. CO NOTE 2: $750,000 of the $1M invested by the shark was spent on Capital Expenditures in 2023. No other capital expeditures occurred. LD SOLVE FOR: 2023 2024 2025 2026 2027 Free Cash Flow 3 pts h Free Cash Flow per share 1 pt 4 LD O N Scenario & Requirements Milestone 3 Financial Stmt data Milestone 3 Metrics Milestone 3 Questions Grading RUBRI