19 These are the facts that you need to incorporate into a model: 20 Your company has been spending $200,000 annually to purchase brushes. This expense will cease if supply of brushes is insourced. 21 You estimate that manufacturing inhouse will cost $100,000 in labor and $10,000 in overhead. 22 A significant investment in equipment will be required and the investment will be straight-line depreciated over a 5 year useful life. 23 Additional work needs to be done to firm up an estimate but you believe the equipment will cost between $200,000 and $350,000. 24 The Treasurer of your company needs to decide how the investment would be financed and has asked you to analyze the sensitivity of the return to finan 25 For the purpose of developing the model, you have selected some initial values for the two independent variables. 26 27 Investment Required 300,000 28 29 Percent Debt Financed 50% 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 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 2020 2021 2022 2023 2024 2025 36 Income Statement 37 Finished Goods Expense 200,000 200,000 200,000 200,000 200,000 38 Depreciation (60,000) (60,000) (60,000) (60,000) (60,000) 39 Labor (100,000) (100,000) (100,000) (100,000) (100,000) 40 Overhead (10,000) (10,000) (10,000) (10,000) (10,000) 41 EBIT 30,000 30,000 30,000 30,000 30,000 42 Interest Income (5% interest rate) 43 Pretax 30,000 30,000 30,000 30,000 30,000 44 Tax (30%tax rate) 9,000 9,000 9,000 9,000 9,000 45 Net Income 21,000 21,000 21,000 21,000 21,000 46 47 Balance Sheet (change in account) 48 Cash (150,000) 49 PP&E 300,000 50 Debt 150,000 51 Equity 52 53 Cash Flow Statement 54 Net Income 55 Depreciation 56 Cash Flow from Operations Scr 57 Capex (300,000) 27 Investment Required 300,000 28 29 Percent Debt Financed 50% 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 Flaw Statement. 33 Please enter formulain the yellow boxes below to develop the mode. 34 35 2020 2021 2022 2023 2024 2025 Hints 36 Income Statement 37 Finished Goods Expense 200,000 200,000 200,000 200,000 200,000 Shown as positive because it is an expense that will cease to exist 3 38 Depreciation (60,0001 (50,000 (50,000 (50,000) (50,000) 39 Labor (100,000 (100,000 (100,000 (100,000 (100,000) 40 Overhead (10,000 (10,000 (10,000 (10,000) () (10,000) 41 EBIT 30,000 30,000 30,000 30,000 30,000 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 30,000 30,000 30,000 30,000 30,000 44 Tax (30%tax rate) 9,000 9,000 9,000 9,000 9,000 45 Net Income 21,000 21,000 21,000 21,000 21,000 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 15 years) 56 Cash Flow from Operations 57 Capex (300,00D] 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 62 Cash Generated (150,000) 62 63 Write a fomula that calculates the Net Present Value(NPV) of the cash flows in Row 61 using a 10% discount factor. 64 Screenshot 19 These are the facts that you need to incorporate into a model: 20 Your company has been spending $200,000 annually to purchase brushes. This expense will cease if supply of brushes is insourced. 21 You estimate that manufacturing inhouse will cost $100,000 in labor and $10,000 in overhead. 22 A significant investment in equipment will be required and the investment will be straight-line depreciated over a 5 year useful life. 23 Additional work needs to be done to firm up an estimate but you believe the equipment will cost between $200,000 and $350,000. 24 The Treasurer of your company needs to decide how the investment would be financed and has asked you to analyze the sensitivity of the return to finan 25 For the purpose of developing the model, you have selected some initial values for the two independent variables. 26 27 Investment Required 300,000 28 29 Percent Debt Financed 50% 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 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 2020 2021 2022 2023 2024 2025 36 Income Statement 37 Finished Goods Expense 200,000 200,000 200,000 200,000 200,000 38 Depreciation (60,000) (60,000) (60,000) (60,000) (60,000) 39 Labor (100,000) (100,000) (100,000) (100,000) (100,000) 40 Overhead (10,000) (10,000) (10,000) (10,000) (10,000) 41 EBIT 30,000 30,000 30,000 30,000 30,000 42 Interest Income (5% interest rate) 43 Pretax 30,000 30,000 30,000 30,000 30,000 44 Tax (30%tax rate) 9,000 9,000 9,000 9,000 9,000 45 Net Income 21,000 21,000 21,000 21,000 21,000 46 47 Balance Sheet (change in account) 48 Cash (150,000) 49 PP&E 300,000 50 Debt 150,000 51 Equity 52 53 Cash Flow Statement 54 Net Income 55 Depreciation 56 Cash Flow from Operations Scr 57 Capex (300,000) 27 Investment Required 300,000 28 29 Percent Debt Financed 50% 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 Flaw Statement. 33 Please enter formulain the yellow boxes below to develop the mode. 34 35 2020 2021 2022 2023 2024 2025 Hints 36 Income Statement 37 Finished Goods Expense 200,000 200,000 200,000 200,000 200,000 Shown as positive because it is an expense that will cease to exist 3 38 Depreciation (60,0001 (50,000 (50,000 (50,000) (50,000) 39 Labor (100,000 (100,000 (100,000 (100,000 (100,000) 40 Overhead (10,000 (10,000 (10,000 (10,000) () (10,000) 41 EBIT 30,000 30,000 30,000 30,000 30,000 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 30,000 30,000 30,000 30,000 30,000 44 Tax (30%tax rate) 9,000 9,000 9,000 9,000 9,000 45 Net Income 21,000 21,000 21,000 21,000 21,000 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 15 years) 56 Cash Flow from Operations 57 Capex (300,00D] 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 62 Cash Generated (150,000) 62 63 Write a fomula that calculates the Net Present Value(NPV) of the cash flows in Row 61 using a 10% discount factor. 64 Screenshot