You invented Dura-Clear windows that never need washing! Nothing sticks to them not pollution, pollen, dirt, dust, bird droppings, fingerprints, nothing. Youve 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. Its 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, youre 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 Its currently January 1, 2023, and youll 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. 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 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 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 errors in spelling, grammar, sentence structure or use of language 1 pt each Q Total 40 points