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 Internaly) the supply. These are the facts that you need to incorporate into a modet 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. Asignificant investment in equipment wil be required and the investment will be straight-line depreciated over a 5 year useful life. Additional work needs to be done to firmupan estimate but you believe the equipment wilcost 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 senstivity 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 responsble 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 2020 2021 2022 2023 2024 2025 Hints Shown as positive because it is an expense that wilcease to exist Income Statement Finished Goods Expense Depreciation Labor Overhead EBIT Interest Income (5% interest rate) Pretax Tax (30%tax rate) Net Income 200.000 (60.000) (100,000) (10000) 30,000 200,000 (60,000) (100,000) (10,000) 30,000 200,000 (60,000) (100,000) (10,000) 30,000 200,000 (60,000) (100,000) (10,000) 30,000 200,000 (60,000) (100,000) (10,000) 30,000 Interest Income is based on a 5% interest rate applied to prior year Net Cash Cash minus Debt) 30,000 9000 21,000 30,000 9000 21,000 30,000 9,000 21,000 30,000 9,000 21,000 30,000 9,000 21,000 Balance Sheet (change in account) Cash PP&E Debt Equity (150 000) 300.000 150,000 Cash is equalto that of the previous yearplus Cash Generated PPRE is equalto that of the previous year minus CapEx (shown on Cash Flow Statement as negative) minus Depreciation Debt is equal to that of the previous year plus Issues (Retirement) of Debt Equity is equal to that of the previous year plus Net Income (there are no Dividends) Net income from the Income Statement Investment Required spread of the useful life (5 years) Cash Flow Statement Net Income Depreciation Cash Flow from Operations Capex Cash Flow from Investing Issues (Retirement) Debt Cash Flow from Financing Cash Generated (300 000) (300,000) 150 000 150,000 (150,000) Debt based on Percent Debt Financed with 1/5th retired each year 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',celD27, 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 OX and enter the resulting NPV in the yelow box below. Below we present a Excel Data Table The Excel Data Table functionality permits users to identify a set of potentialvalue for two independent variables. The functionality automatically analyzes a formula that is dependent on these two variables and presents the results of a combinations of the two independent variables The EXCEL Financial Functions Sheet presents how to create a Data Table, but the output is presented here for those that do not want to dive any deeper. Percent Debt Financed 25% 0% 50% 75% 100% Investment Required 200,000 250,000 300,000 350,000 Which would generate a hisher NPV 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 Internaly) the supply. These are the facts that you need to incorporate into a modet 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. Asignificant investment in equipment wil be required and the investment will be straight-line depreciated over a 5 year useful life. Additional work needs to be done to firmupan estimate but you believe the equipment wilcost 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 senstivity 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 responsble 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 2020 2021 2022 2023 2024 2025 Hints Shown as positive because it is an expense that wilcease to exist Income Statement Finished Goods Expense Depreciation Labor Overhead EBIT Interest Income (5% interest rate) Pretax Tax (30%tax rate) Net Income 200.000 (60.000) (100,000) (10000) 30,000 200,000 (60,000) (100,000) (10,000) 30,000 200,000 (60,000) (100,000) (10,000) 30,000 200,000 (60,000) (100,000) (10,000) 30,000 200,000 (60,000) (100,000) (10,000) 30,000 Interest Income is based on a 5% interest rate applied to prior year Net Cash Cash minus Debt) 30,000 9000 21,000 30,000 9000 21,000 30,000 9,000 21,000 30,000 9,000 21,000 30,000 9,000 21,000 Balance Sheet (change in account) Cash PP&E Debt Equity (150 000) 300.000 150,000 Cash is equalto that of the previous yearplus Cash Generated PPRE is equalto that of the previous year minus CapEx (shown on Cash Flow Statement as negative) minus Depreciation Debt is equal to that of the previous year plus Issues (Retirement) of Debt Equity is equal to that of the previous year plus Net Income (there are no Dividends) Net income from the Income Statement Investment Required spread of the useful life (5 years) Cash Flow Statement Net Income Depreciation Cash Flow from Operations Capex Cash Flow from Investing Issues (Retirement) Debt Cash Flow from Financing Cash Generated (300 000) (300,000) 150 000 150,000 (150,000) Debt based on Percent Debt Financed with 1/5th retired each year 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',celD27, 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 OX and enter the resulting NPV in the yelow box below. Below we present a Excel Data Table The Excel Data Table functionality permits users to identify a set of potentialvalue for two independent variables. The functionality automatically analyzes a formula that is dependent on these two variables and presents the results of a combinations of the two independent variables The EXCEL Financial Functions Sheet presents how to create a Data Table, but the output is presented here for those that do not want to dive any deeper. Percent Debt Financed 25% 0% 50% 75% 100% Investment Required 200,000 250,000 300,000 350,000 Which would generate a hisher NPV