Your Name(s): Assumptions (use these to drive projectes 0.151 0.28 0.36 Sales Growth % of Current Sales Average price per item Cost of Goods Sold % of Sales Operating Expense % of Forecast Sales Tax Rate % of EBT Dividend Rate % of NI Required Cash % of Forecast Sales A/R days Inventory Days Net Fixed Assets % of Forecast Sales Days Payable Accrued Liability of Forecast Sales 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 Project 2021 - 2023 Income Statement and Balance Sheet. Assumptions: Sales growth rates in each of next 3 years: 15%, 28%,36%. Operating expenses/sales percentage stay at 2020 level. Cost of goods sold as of sales stays at 2020 level for 2021 and them improves by 05 in 2022 and by another .02 in 2023. 40% tax rate. No dividends or new stock issued. Interest expense fixed at $400/year. Bank loan and LT debt stay constant 2021A/ R days = year 2020 days, then decrease by 3 days in 2022 & another 3 days in 2023 2021 Inventory days = yeur 2020 days then decrease by 3 days in 2022 & another 3 days 2023. A/P days stays at year 2020 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 2021 breakeven revenues and units. Assumptions: All operating expenses (excluding interest) are fixed. Average price per unit is $50 DO NOT HARD CODE NUMBERS please I need the formulas *>>>>>>>>>>>>> 2021 2022 2023 17250 22080 30029 Net Sales Cast of Goods Sold Gross Profit Operating exp Interest EBT Taxes (40%) Net Income Actual 2020 15000 10500 4500 -2500 -400 1600 -640 960 Cash Dividends (0% of NI) Added Retained Earnings 0 960 Balance Sheet Required Cash A/R Inventories Total Current Assets Net Fixed Assets Total Assets 1000 2000 2200 5200 6800 12000 Accounts Payable Bank Loan Accured Liability Total Current Liability Long Term Debt Common Stock Retained Earnings Additional Funds Needed AFN) Total Liability & Equity 1600 1800 1200 4800 2200 2400 2800 0 12000 BREAKEVEN CALCULATION Fixed Costs Gross margin BREAKEVEN REVENUES Price per unit Cost per unit Contribution per unit BREAKEVEN UNITS Your Name(s): Assumptions (use these to drive projectes 0.151 0.28 0.36 Sales Growth % of Current Sales Average price per item Cost of Goods Sold % of Sales Operating Expense % of Forecast Sales Tax Rate % of EBT Dividend Rate % of NI Required Cash % of Forecast Sales A/R days Inventory Days Net Fixed Assets % of Forecast Sales Days Payable Accrued Liability of Forecast Sales 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 Project 2021 - 2023 Income Statement and Balance Sheet. Assumptions: Sales growth rates in each of next 3 years: 15%, 28%,36%. Operating expenses/sales percentage stay at 2020 level. Cost of goods sold as of sales stays at 2020 level for 2021 and them improves by 05 in 2022 and by another .02 in 2023. 40% tax rate. No dividends or new stock issued. Interest expense fixed at $400/year. Bank loan and LT debt stay constant 2021A/ R days = year 2020 days, then decrease by 3 days in 2022 & another 3 days in 2023 2021 Inventory days = yeur 2020 days then decrease by 3 days in 2022 & another 3 days 2023. A/P days stays at year 2020 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 2021 breakeven revenues and units. Assumptions: All operating expenses (excluding interest) are fixed. Average price per unit is $50 DO NOT HARD CODE NUMBERS please I need the formulas *>>>>>>>>>>>>> 2021 2022 2023 17250 22080 30029 Net Sales Cast of Goods Sold Gross Profit Operating exp Interest EBT Taxes (40%) Net Income Actual 2020 15000 10500 4500 -2500 -400 1600 -640 960 Cash Dividends (0% of NI) Added Retained Earnings 0 960 Balance Sheet Required Cash A/R Inventories Total Current Assets Net Fixed Assets Total Assets 1000 2000 2200 5200 6800 12000 Accounts Payable Bank Loan Accured Liability Total Current Liability Long Term Debt Common Stock Retained Earnings Additional Funds Needed AFN) Total Liability & Equity 1600 1800 1200 4800 2200 2400 2800 0 12000 BREAKEVEN CALCULATION Fixed Costs Gross margin BREAKEVEN REVENUES Price per unit Cost per unit Contribution per unit BREAKEVEN UNITS
