You are Executive Vice President of Operations of a company that produces and distributes household goods. Your company manufactures some of its products and purchases and resells other products. Your company has been distributing brushes purchased from a third-party and you are analyzing the economics of insourcing (manufacturing internally) the supply. These are the facts that you need to incorporate into a model: Your company has been spending $200,000 annually to purchase brushes. This expense will cease if supply of brushes is insourced. You estimate that manufacturing inhouse will cost $100,000 in labor and $10,000 in overhead. A significant investment in equipment will be required and the investment will be straight-line depreciated over a 5 year useful life (no residual value). Additional work needs to be done to firm up an estimate but you believe the equipment will cost between $200,000 and $350,000. 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 financing options. For the purpose of developing the model, you have selected some initial values for the two independent variables. Investment Required 300,000 Percent Debt Financed 50% You are responsible for developing a model that analyzes the potential investment, providing valuations of alternate strategies and a sensitivity analysis of the results. The following model should include only the incremental impact on the Income Statement, Balance Sheet and Cash Flow Statement. Please enter formula in the yellow boxes below to develop the model. 2021 2022 2023 2024 2025 2026 Hints Shown as positive because Income Statement Finished Goods Expense Depreciation Labor Overhead EBIT 217,695 (60,000) (108,847) (10,000) 38,847 217,695 (60,000) (108,847) (10,000) 38,847 217,695 (60,000) (108,847) (10,000) 38,847 217,695 (60,000) (108,847) (10,000) 38,847 217,695 (60,000) (108,847) (10,000) 38,847 Interest Income (5% interest rate) Pretax Tax (30%tax rate) Net Income 38,847 11,654 27,193 38,847 11,654 27,193 38,847 11,654 27,193 38,847 11,654 27,193 38,847 11,654 27,193 Balance Sheet (change in account) Cash PP&E Debt Equity (150,000) 300,000 150,000 Cash Flow Statement Net Income Depreciation Cash Flow from Operations Capex Cash Flow from Investing Issues (Retirement) Debt Cash Flow from Financing Cash Generated 0 0 0 0 0 (300,000) (300,000) 150,000 150,000 (150,000) Write a fomula that calculates the Net Present Value (NPV) of the cash flows in Row 61 using a 10% discount factor. Change the "Investment Required', cell D27, to $200,000 and enter the resulting NPV value (not the formula) in the yellow box below. Keeping the $200,000 "Investment Required' change the "Percent Debt Financed' to 0% and enter the resulting NPV value (not the formula) in the yellow box below. You are Executive Vice President of Operations of a company that produces and distributes household goods. Your company manufactures some of its products and purchases and resells other products. Your company has been distributing brushes purchased from a third-party and you are analyzing the economics of insourcing (manufacturing internally) the supply. These are the facts that you need to incorporate into a model: Your company has been spending $200,000 annually to purchase brushes. This expense will cease if supply of brushes is insourced. You estimate that manufacturing inhouse will cost $100,000 in labor and $10,000 in overhead. A significant investment in equipment will be required and the investment will be straight-line depreciated over a 5 year useful life (no residual value). Additional work needs to be done to firm up an estimate but you believe the equipment will cost between $200,000 and $350,000. 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 financing options. For the purpose of developing the model, you have selected some initial values for the two independent variables. Investment Required 300,000 Percent Debt Financed 50% You are responsible for developing a model that analyzes the potential investment, providing valuations of alternate strategies and a sensitivity analysis of the results. The following model should include only the incremental impact on the Income Statement, Balance Sheet and Cash Flow Statement. Please enter formula in the yellow boxes below to develop the model. 2021 2022 2023 2024 2025 2026 Hints Shown as positive because Income Statement Finished Goods Expense Depreciation Labor Overhead EBIT 217,695 (60,000) (108,847) (10,000) 38,847 217,695 (60,000) (108,847) (10,000) 38,847 217,695 (60,000) (108,847) (10,000) 38,847 217,695 (60,000) (108,847) (10,000) 38,847 217,695 (60,000) (108,847) (10,000) 38,847 Interest Income (5% interest rate) Pretax Tax (30%tax rate) Net Income 38,847 11,654 27,193 38,847 11,654 27,193 38,847 11,654 27,193 38,847 11,654 27,193 38,847 11,654 27,193 Balance Sheet (change in account) Cash PP&E Debt Equity (150,000) 300,000 150,000 Cash Flow Statement Net Income Depreciation Cash Flow from Operations Capex Cash Flow from Investing Issues (Retirement) Debt Cash Flow from Financing Cash Generated 0 0 0 0 0 (300,000) (300,000) 150,000 150,000 (150,000) Write a fomula that calculates the Net Present Value (NPV) of the cash flows in Row 61 using a 10% discount factor. Change the "Investment Required', cell D27, to $200,000 and enter the resulting NPV value (not the formula) in the yellow box below. Keeping the $200,000 "Investment Required' change the "Percent Debt Financed' to 0% and enter the resulting NPV value (not the formula) in the yellow box below