In this assignment you will create a forecast for a fictitious company using the attached spreadsheet...

 

Transcribed Image Text:

In this assignment you will create a forecast for a fictitious company using the attached spreadsheet template. Input the following as assumptions in the assumption part of the spreadsheet (grey highlighted cells at the top of the spreadsheet) and then reference those cells when projecting the appropriate income statement, balance sheet, or break-even account. Do NOT hard code numbers into your projections. 2024 Income Statement and Balance Sheet. • Sales growth rates in each of next 3 years: 15%, 33%, 40% Operating expenses/sales percentage stay at 2021 level • Cost of goods sold as % of sales stays at 2021 level for 2022 and then improves by .05 in 2023 and by another .03 in 2024 • 40% tax rate Project 2022 Assumptions: ● No dividends or new stock issued Interest expense fixed at $400/year Bank loan and LT debt stay constant 2022 A/R days = year 2021 days, then decrease by 3 days in 2023 & another 3 days in 2024 • 2022 Inventory days = year 2021 days then decrease by 3 days in 2023 & another 3 days 2024 • A/P days stays at year 2021 days Net fixed assets and accruals keep relationship with sales • Required cash fixed at 1000 Calculate Additional Funds Needed as plug (to get Balance Sheet to balance!) Calculate 2022 breakeven revenues and units. Assumptions: All operating expenses (excluding interest) are fixed Average price per unit is $50 GRADING: You will be assessed both on the accuracy of your projections as well as your proper use of spreadsheet projection etiquette (such as not hard coding numbers into cells). This assignment is worth 10 points. In this assignment you will create a forecast for a fictitious company using the attached spreadsheet template. Input the following as assumptions in the assumption part of the spreadsheet (grey highlighted cells at the top of the spreadsheet) and then reference those cells when projecting the appropriate income statement, balance sheet, or break-even account. Do NOT hard code numbers into your projections. 2024 Income Statement and Balance Sheet. • Sales growth rates in each of next 3 years: 15%, 33%, 40% Operating expenses/sales percentage stay at 2021 level • Cost of goods sold as % of sales stays at 2021 level for 2022 and then improves by .05 in 2023 and by another .03 in 2024 • 40% tax rate Project 2022 Assumptions: ● No dividends or new stock issued Interest expense fixed at $400/year Bank loan and LT debt stay constant 2022 A/R days = year 2021 days, then decrease by 3 days in 2023 & another 3 days in 2024 • 2022 Inventory days = year 2021 days then decrease by 3 days in 2023 & another 3 days 2024 • A/P days stays at year 2021 days Net fixed assets and accruals keep relationship with sales • Required cash fixed at 1000 Calculate Additional Funds Needed as plug (to get Balance Sheet to balance!) Calculate 2022 breakeven revenues and units. Assumptions: All operating expenses (excluding interest) are fixed Average price per unit is $50 GRADING: You will be assessed both on the accuracy of your projections as well as your proper use of spreadsheet projection etiquette (such as not hard coding numbers into cells). This assignment is worth 10 points.

Answer rating: 100% (QA)

1 Sales Growth Rate Assumptions The sales growth rate assumptions for the next three years are 15 for 2022 33 for 2023 and 40 for 2024 This reflects a steady and consistent increase in sales over the ... View the full answer