30 31 You are responsible for developing a model that analyzes the potential investment, providing valuations of alternate strategies and a sensitivity analysis of the results. 32 The following model should include only the incremental impact on the Income Statement, Balance Sheet and Cash Flow Statement. 33 Please enter formula in the yellow boxes below to develop the model. 34 35 2021 2022 2023 2024 2025 2026 Hints 36 Income Statement 37 Finished Goods Expense 210,332 210,332 210,332 210,332 210,332 Shown as positive because it is an expense that will cease to exist 38 Depreciation 160,000) 160,000) (60,000) (60,000) (60,000) 39 Labor (105,166) (105,166) (105,166) (105,166) ( (105,166) 40 Overhead (10,000) (10,000 (10,000 (10,000) (10,000) 41 EBIT 35,166 35,166 35,166 35,166 35,166 42 Interest Income (5% interest rate) Interest Income is based on a 5% interest rate applied to prior year Net Cash (Cash minus Debt) 43 Pretax 35,166 35,166 35,166 35,166 35,166 44 Tax (30%tax rate) 10,550 10,550 10,550 10.550 10.550 45 Net Income 24,616 24,616 24,616 24,616 24,616 46 47 Balance Sheet (change in account) 48 Cash (150,000) Cash is equal to that of the previous year plus Cash Generated 49 PP&E 300,000 PP&E is equal to that of the previous year minus CapEx (shown on Cash Flow Statement as negative) minus Depreciation 50 Debt 150,000 Debt is equal to that of the previous year plus issues (Retirement) of Debt 51 Equity Equity is equal to that of the previous year plus Net Income (there are no Dividends) 52 53 Cash Flow Statement 54 Net Income Net Income from the Income Statement 55 Depreciation Investment Required spread of the useful life (5 years) 56 Cash Flow from Operations 57 Capex (300,000) 0 0 0 58 Cash Flow from Investing (300,000 59 Issues (Retirement) Debt 150,000 Debt based on Percent Debt Financed with 1/5th retired each year 60 Cash Flow from Financing 150,000 61 Cash Generated (150,000) 62 0 30 31 You are responsible for developing a model that analyzes the potential investment, providing valuations of alternate strategies and a sensitivity analysis of the results. 32 The following model should include only the incremental impact on the Income Statement, Balance Sheet and Cash Flow Statement. 33 Please enter formula in the yellow boxes below to develop the model. 34 35 2021 2022 2023 2024 2025 2026 Hints 36 Income Statement 37 Finished Goods Expense 210,332 210,332 210,332 210,332 210,332 Shown as positive because it is an expense that will cease to exist 38 Depreciation 160,000) 160,000) (60,000) (60,000) (60,000) 39 Labor (105,166) (105,166) (105,166) (105,166) ( (105,166) 40 Overhead (10,000) (10,000 (10,000 (10,000) (10,000) 41 EBIT 35,166 35,166 35,166 35,166 35,166 42 Interest Income (5% interest rate) Interest Income is based on a 5% interest rate applied to prior year Net Cash (Cash minus Debt) 43 Pretax 35,166 35,166 35,166 35,166 35,166 44 Tax (30%tax rate) 10,550 10,550 10,550 10.550 10.550 45 Net Income 24,616 24,616 24,616 24,616 24,616 46 47 Balance Sheet (change in account) 48 Cash (150,000) Cash is equal to that of the previous year plus Cash Generated 49 PP&E 300,000 PP&E is equal to that of the previous year minus CapEx (shown on Cash Flow Statement as negative) minus Depreciation 50 Debt 150,000 Debt is equal to that of the previous year plus issues (Retirement) of Debt 51 Equity Equity is equal to that of the previous year plus Net Income (there are no Dividends) 52 53 Cash Flow Statement 54 Net Income Net Income from the Income Statement 55 Depreciation Investment Required spread of the useful life (5 years) 56 Cash Flow from Operations 57 Capex (300,000) 0 0 0 58 Cash Flow from Investing (300,000 59 Issues (Retirement) Debt 150,000 Debt based on Percent Debt Financed with 1/5th retired each year 60 Cash Flow from Financing 150,000 61 Cash Generated (150,000) 62 0