Excel sheets with instructions on how to complete the assignments. I do not have anything else to write.
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 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: 1Actual annual sales in the past: 202020212022 Actual SALES$1,200,000$1,500,000$1,875,000 2Because 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 3You 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. 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 20202021202220232024202520262027 Sales (all on credit)1,200,0001,500,0001,875,000 Cost of Goods Sold(800,000)(1,040,000)(1,105,000) Gross Profit400,000460,000770,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 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 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 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 funding you are requesting? Explain why or why not. Grading RUBRIC GRADING RUBRIC MILESTONE 1 RUBRIC ItemGrading CriteriaPoints Possible Sales & Gross Profit Forecast All work shown in a well-labeled, easy-to-follow format7 Accuracy of results18 Questions 1 - 3 All parts of question fully answered and response shows thought and some depth of analysis4 pts each Q Limited or no errors in spelling, grammar, sentence structure or use of language1 pt each Q Total40 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 investor IRR and NPV for your shark investor 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 202020212,0222,0232,0242,0252,0262,027 Sales (all on credit)1,200,0001,500,0001,875,0004,000,0005,000,0006,000,0007,000,0008,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 Profit400,000460,000770,0002,042,6672,553,3333,064,0003,574,6674,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,100109,500326,300942,5471,281,6831,620,8201,959,9572,299,093 Interest expense(35,000)(45,000)(85,000)(70,000)(60,000)(50,000)(40,000)(30,000) Net Income before Taxes60,10064,500241,300872,5471,221,6831,570,8201,919,9572,269,093 Taxes (36%)(36,900)(49,200)(55,600)(314,117)(439,806)(565,495)(691,184)(816,874) Net Income23,20015,300185,700558,430781,8771,005,3251,228,7721,452,220 Shares60,00060,00078,000104,000104,000104,000104,000104,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 20202021202220232024202520262027 ASSETS Cash30,00040,00030,00060,00060,00060,00060,00060,000 Marketable Securities20,00025,00030,00040,00040,00040,00040,00040,000 Accounts Receivable170,000259,000360,000600,000800,0001,000,0001,100,0001,200,000 Inventory230,000261,000290,000693,778867,2221,040,6671,214,1111,387,556 Total Current Assets:450,000585,000710,0001,393,7781,767,2222,140,6672,414,1112,687,556 Plant and equipment650,000765,0001,390,0002,140,0002,140,0002,140,0002,140,0002,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 equipment585,000623,5001,109,5001,645,5001,431,5001,217,5001,003,500789,500 Total Assets1,035,0001,208,5001,819,5003,039,2783,198,7223,358,1673,417,6113,477,056 LIABILITIES & STOCKHOLDER'S EQUITY Accounts Payable200,000310,000505,000748,560949,2221,149,8841,296,4581,443,031 Accrued Expenses20,40030,00035,00075,00095,000115,000130,000145,000 Total Current Liabilities220,400340,000540,000823,5601,044,2221,264,8841,426,4581,588,031 Long-term Liabilities325,000363,600703,900603,900503,900403,900303,900203,900 Total Liabilities545,400703,6001,243,9001,427,4601,548,1221,668,7841,730,3581,791,931 Common Stock ($1 par)60,00060,00078,000104,000104,000104,000104,000104,000 Capital paid in excess of par190,000190,000262,0001,236,0001,236,0001,236,0001,236,0001,236,000 Retained Earnings239,600254,900235,600271,818310,600349,383347,254345,124 Total Stockholder's Equity489,600504,900575,6001,611,8181,650,6001,689,3831,687,2541,685,124 Total Liabilities & Stockholder's Equity1,035,0001,208,5001,819,5003,039,2783,198,7223,358,1673,417,6113,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 2023 - 2027, 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:20232024202520262027 Quantity of windows sold (Q)4,0005,0006,0007,0008,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:20232024202520262027 aContribution Margin Ratio3 pts bBreakeven in sales dollars3 pts cDOL 4 pts dDFL 4 pts 2(14 pts) Based upon your financial forecast for the years 2023 - 2027, 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:20232024202520262027 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:20232024202520262027 aCash Flows from Operations (shark only)3 pts bPayback (in years) of the shark's $1M investment (x.xx years)3 pts cIRR of shark's investment4 pts dNPV of shark's investment4 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)20232024202520262027 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 occurred. SOLVE FOR:20232024202520262027 aFree Cash Flow3 pts bFree Cash Flow per share1 pt Milestone 3 Questions MILESTONE 3 QUESTIONS 1DOL & DFL(5 points: 1 pt for grammar/spelling, 4 pts for thought-out, correct answers) a.