File Home insert Pagelayout Formulas Datil Review View Automate Devaloper Help N22. N22 31 Forecasted Financials Milestone 2 Metrics Milestone 2 Questions Grading RUBAIC (PANT: Enter intereit Expense os a positive number for ne) Home Insert Page Layout formulas Data Review View Automate Developer Help ABBDEFGHIKKL NOTE: You will use a combination of Proforma, Ratios, and the Percent-of-Sales methods to create your forecasted fina 1 The Sharks gave you the $1,000,000 in funding you requested in exchange for 25% ownership of your company's profits. 2 All the following are complete by March 31, 2023: 1) your shark funding has been received, 2) new capital investments have been purchased and set up, and 3 ) additional labor has been hired and 3 SELLING \& ADMINISTRATIVE EXPENSE: Use a 3-year average Percent-of-Sales to forecast S\&A expenses. (HINT: Find what percent S\&A expense is for each of 2020, 2021, and 2022, and average the 3 results together. Use the resulting average S\&A Percent-of-Sales to forecast S\&A into the future.) RENT EXPENSE: Rent expense is a fixed cost in the amount of $15,000 per year in 2020-2022, increasing to $200,000 per year in 2023. 5 DEPRECIATION EXPENSE: Depreciation expense is a fixed cost in the amount of 10% of Plant \& Equipment each year. INTEREST EXPENSE: This is a Fixed cost, and is 10% of Long-term Habilities. TAXES: Because you live in a business-friendly State (Wyoming), you don't have to pay state taxes on your LLC's income. You do, however, still have to pay Federal taxes. Also, in 2022, higher tax rates were passed for the 2023 tax year, pushing income over $400,000 into the 39.6% tax bracket. Because of this, use 36% as your effective tax rate. (NOTE: If the taxes shown for 2020-2022 seem high, it's because you had income from another job thot threw your LLC income into a slightly higher tax bracket. However, you'll quit that job IF the SHARES: Issued 26,000 \$1-par shares to the sharks for a 25% ownership stake. CASH: Increases to $50,000 in 2023 and stays at that level. MARKETABLE SECURITIES: Plan to keep Marketable Securities at 60% of Cash levels. ACCOUNTS RECEIVABLE: Use a 3-year average Receivables Turnover ratio to forecast. (HINT: Find the formulo for Receivables Turnover (RTO) in your Week 2 Chapter readings, and solve for RTO for each of 2020, 2021, and 2022. Average the 3 results together. Plug vour Average RTO into the RTO Assumptions Forecasted Financials Milestone 2 Metrics I Milestone 2 Questions MILESTONE 2: Creation of Forecasted Financials (100 points) DUE: WEEK 5 It's STIL. January 1, 2023, and you'll be going on Shark Tank in 2 weeks. You are unhappy with your previous Sales Forecast (Milestone 1) because using your current level of sales to residences was not a good predictor of your upcoming sales to commercial builders. You believe your sales will increase significantly and that each order will result in a higher profit. You decide to change your forecasting method to a combination of Ratios, Percent-of-Sales, and Pro Forma forecasting. Required: 1 (20 pts) Using the information on the Assumptions tab, create a 5 -year forecasted income Stotement on the Forecasted Financials tab. 2 (30 pts) Using the information on the Assumptions tab, create a 5 -year forecasted Balance Sheet on the Forecasted Financials tab. 3 (30 pts) On the Mlestone 2 Metrics tab, compute various metrics based upon your forecasted financials. 4 (20 pts) Answer questions about your results on the Milestone 2 Questions tab. File Home insert Pagelayout Formulas Datil Review View Automate Devaloper Help N22. N22 31 Forecasted Financials Milestone 2 Metrics Milestone 2 Questions Grading RUBAIC (PANT: Enter intereit Expense os a positive number for ne) Home Insert Page Layout formulas Data Review View Automate Developer Help ABBDEFGHIKKL NOTE: You will use a combination of Proforma, Ratios, and the Percent-of-Sales methods to create your forecasted fina 1 The Sharks gave you the $1,000,000 in funding you requested in exchange for 25% ownership of your company's profits. 2 All the following are complete by March 31, 2023: 1) your shark funding has been received, 2) new capital investments have been purchased and set up, and 3 ) additional labor has been hired and 3 SELLING \& ADMINISTRATIVE EXPENSE: Use a 3-year average Percent-of-Sales to forecast S\&A expenses. (HINT: Find what percent S\&A expense is for each of 2020, 2021, and 2022, and average the 3 results together. Use the resulting average S\&A Percent-of-Sales to forecast S\&A into the future.) RENT EXPENSE: Rent expense is a fixed cost in the amount of $15,000 per year in 2020-2022, increasing to $200,000 per year in 2023. 5 DEPRECIATION EXPENSE: Depreciation expense is a fixed cost in the amount of 10% of Plant \& Equipment each year. INTEREST EXPENSE: This is a Fixed cost, and is 10% of Long-term Habilities. TAXES: Because you live in a business-friendly State (Wyoming), you don't have to pay state taxes on your LLC's income. You do, however, still have to pay Federal taxes. Also, in 2022, higher tax rates were passed for the 2023 tax year, pushing income over $400,000 into the 39.6% tax bracket. Because of this, use 36% as your effective tax rate. (NOTE: If the taxes shown for 2020-2022 seem high, it's because you had income from another job thot threw your LLC income into a slightly higher tax bracket. However, you'll quit that job IF the SHARES: Issued 26,000 \$1-par shares to the sharks for a 25% ownership stake. CASH: Increases to $50,000 in 2023 and stays at that level. MARKETABLE SECURITIES: Plan to keep Marketable Securities at 60% of Cash levels. ACCOUNTS RECEIVABLE: Use a 3-year average Receivables Turnover ratio to forecast. (HINT: Find the formulo for Receivables Turnover (RTO) in your Week 2 Chapter readings, and solve for RTO for each of 2020, 2021, and 2022. Average the 3 results together. Plug vour Average RTO into the RTO Assumptions Forecasted Financials Milestone 2 Metrics I Milestone 2 Questions MILESTONE 2: Creation of Forecasted Financials (100 points) DUE: WEEK 5 It's STIL. January 1, 2023, and you'll be going on Shark Tank in 2 weeks. You are unhappy with your previous Sales Forecast (Milestone 1) because using your current level of sales to residences was not a good predictor of your upcoming sales to commercial builders. You believe your sales will increase significantly and that each order will result in a higher profit. You decide to change your forecasting method to a combination of Ratios, Percent-of-Sales, and Pro Forma forecasting. Required: 1 (20 pts) Using the information on the Assumptions tab, create a 5 -year forecasted income Stotement on the Forecasted Financials tab. 2 (30 pts) Using the information on the Assumptions tab, create a 5 -year forecasted Balance Sheet on the Forecasted Financials tab. 3 (30 pts) On the Mlestone 2 Metrics tab, compute various metrics based upon your forecasted financials. 4 (20 pts) Answer questions about your results on the Milestone 2 Questions tab