Currently the company has four successful products and they are considering selling a new shoe line. The new product will have a selling price of $100.00 per pair. The plant has excess capacity in a fully depreciated building to produce the new product line. The product will be discontinued in four years. The new equipment cost is $400,000, depreciated to zero using straight line depreciation. The new product line requires an increase in working capital of $250,000 and $85,000 of this increase is offset with accounts payable. Projected sales are 5,000 pairs of shoes the first year, with a 20 percent growth for the following years. Variable costs are 45% of total revenues and fixed costs are $170,000 each year. Salvage value on equipment is $65,000. The corporate tax rate is 34 percent and the company currently has 1,000,000 shares of stock outstanding at a current price of $75. The company also has 100,000 bonds outstanding, with a current price of $985. The bonds pay interest semi-annually at the coupon rate is 5%. The bonds have a par value of $1,000 and will mature in ten years Even though the company has stock outstanding it is not publicly traded. Therefore, there is no publicly available financial information. However, management believes that given the industry they are in the most reasonable comparable publicly traded company is Nike (ticker symble is NKE). In addition, management believes the S&P 500 is a reasonable proxy for the market portfolio. Therefore, the cost of equity is calculated using the beta from NKE and the market risk premium based on the S&P 500 annual expected rate of return. (Please list your source or justification for your estimated expected return on the market for the CAPM model. Please use the US Treasury website for the risk-free yeild. 3 month T-bill is most common Risk free rate used.) Required 1. Use information from Instruction tab to place starting assumptions in Input area (YELLOW) of Solutions tab. 2. Calculate the WACC for the company. Please provide brief explanation and show work in upper right hand corner of Solutions tab. 3. Create a partial income statement incremental cash flows from this project in the Blank Template worksheet using the Solutions tab below. Please note you must show work in YELLOW and BLUE (formulas) areas. 4. Enter formulas to calculate the NPV by finding the PV of the cash flows over the next four years. (You can either use the EXCEL formula for PVO or use mathmatical formula for PV of a lump sum.) 5. Set up the EXCEL worksheet so that you are able to change the parameters in E3 to E12 Run three cases best, most likely, and worst case where the growth rate is 30%, 20%, and 5%, respectfully. Summarize your answers at bottom of solutions tab. 6. Create a NPV profile for the most likely case scenario in NPV Profile tab. 7. Save with Solutions for Most Likely Case and Upload your final Excel project in the dropbox provided by the due date listed on the schedule. 8. Please note the grading rubric tab below will be used to grade your final project. REQUIRED: You must enter your assumptions or values in all I. Given the following data on proposed capital budgeting project. Economic life of project in years. Price of New Equipment Fixed Costs Salvage value of New Equipment Effect on NWC: First Year Revenues Variable Costs Rate Marginal Tax Rate Cost of Capital (required rate of return) Growth Rate YELLOW areas. You must enter FORMULAS only in all Enter Estimates for WACC Weight of equity $400,000 Weight of debt $170.000 Cost of equity Bef Tax cost of Debt WACC $250,000 45.00% 34.0% Spreadsheet for determining Cash Flows (in Thousands) Timeline: Year 0 II. Net Investment Outlay= Initial CFS Price (400,000) Increase in NWC III. Cash Flows from Operations Total Revenues Fixed Costs Variable Costs Depreciation EBT Taxes (35%) Net Income Depreciation Net operating CFS IV. Terminal Cash Flows Salvage Value Tax on Salvage Value Return of NWC Cash Flows Present Value of CFs Calculate: NPV Summarize Answers for NPV under three cases in area below NPV Accept? Best Case Most Likely Worst Case Currently the company has four successful products and they are considering selling a new shoe line. The new product will have a selling price of $100.00 per pair. The plant has excess capacity in a fully depreciated building to produce the new product line. The product will be discontinued in four years. The new equipment cost is $400,000, depreciated to zero using straight line depreciation. The new product line requires an increase in working capital of $250,000 and $85,000 of this increase is offset with accounts payable. Projected sales are 5,000 pairs of shoes the first year, with a 20 percent growth for the following years. Variable costs are 45% of total revenues and fixed costs are $170,000 each year. Salvage value on equipment is $65,000. The corporate tax rate is 34 percent and the company currently has 1,000,000 shares of stock outstanding at a current price of $75. The company also has 100,000 bonds outstanding, with a current price of $985. The bonds pay interest semi-annually at the coupon rate is 5%. The bonds have a par value of $1,000 and will mature in ten years Even though the company has stock outstanding it is not publicly traded. Therefore, there is no publicly available financial information. However, management believes that given the industry they are in the most reasonable comparable publicly traded company is Nike (ticker symble is NKE). In addition, management believes the S&P 500 is a reasonable proxy for the market portfolio. Therefore, the cost of equity is calculated using the beta from NKE and the market risk premium based on the S&P 500 annual expected rate of return. (Please list your source or justification for your estimated expected return on the market for the CAPM model. Please use the US Treasury website for the risk-free yeild. 3 month T-bill is most common Risk free rate used.) Required 1. Use information from Instruction tab to place starting assumptions in Input area (YELLOW) of Solutions tab. 2. Calculate the WACC for the company. Please provide brief explanation and show work in upper right hand corner of Solutions tab. 3. Create a partial income statement incremental cash flows from this project in the Blank Template worksheet using the Solutions tab below. Please note you must show work in YELLOW and BLUE (formulas) areas. 4. Enter formulas to calculate the NPV by finding the PV of the cash flows over the next four years. (You can either use the EXCEL formula for PVO or use mathmatical formula for PV of a lump sum.) 5. Set up the EXCEL worksheet so that you are able to change the parameters in E3 to E12 Run three cases best, most likely, and worst case where the growth rate is 30%, 20%, and 5%, respectfully. Summarize your answers at bottom of solutions tab. 6. Create a NPV profile for the most likely case scenario in NPV Profile tab. 7. Save with Solutions for Most Likely Case and Upload your final Excel project in the dropbox provided by the due date listed on the schedule. 8. Please note the grading rubric tab below will be used to grade your final project. REQUIRED: You must enter your assumptions or values in all I. Given the following data on proposed capital budgeting project. Economic life of project in years. Price of New Equipment Fixed Costs Salvage value of New Equipment Effect on NWC: First Year Revenues Variable Costs Rate Marginal Tax Rate Cost of Capital (required rate of return) Growth Rate YELLOW areas. You must enter FORMULAS only in all Enter Estimates for WACC Weight of equity $400,000 Weight of debt $170.000 Cost of equity Bef Tax cost of Debt WACC $250,000 45.00% 34.0% Spreadsheet for determining Cash Flows (in Thousands) Timeline: Year 0 II. Net Investment Outlay= Initial CFS Price (400,000) Increase in NWC III. Cash Flows from Operations Total Revenues Fixed Costs Variable Costs Depreciation EBT Taxes (35%) Net Income Depreciation Net operating CFS IV. Terminal Cash Flows Salvage Value Tax on Salvage Value Return of NWC Cash Flows Present Value of CFs Calculate: NPV Summarize Answers for NPV under three cases in area below NPV Accept? Best Case Most Likely Worst Case