80.2K

Verified Solution

Link Copied!

please show with the excel formula and the numeric value.

Adil, the CFO for the firm works at ABS. The new Designer Womens Tops was expected to sell for $ 106 per unit and had projected sales of 4800 units in the first year, with a projected (Most-Likely scenario) 24.0 % growth rate per year for subsequent years. A total investment of $ 909,000 for new equipment was required. The equipment had fixed maintenance contracts of $ 352,276 per year with a salvage value of $ 178,019 and variable costs were 8 % of revenues. Shantel also needed to consider both the Best-Case and Worst-Case scenarios in the analysis with growth rates of 34.00 % and 2.40 % respectively. The new equipment would be depreciated to zero using straight line depreciation. The new project required an increase in working capital of $ 154,110 and $ 30,822 of this increase would be offset with accounts payable.ABS currently has 810000 shares of stock outstanding at a current price of $ 73.00. Even though the company has outstanding stock, it is not publicly traded and therefore there is no publicly available financial information. However, after analysis management believes that its equity beta is 1.26. The company also has 79000 bonds outstanding, with a current price of $ 911.00. The bonds pay interest semi-annually at a coupon rate of 5.60 %. The bonds have a par value of $1,000 and will mature in 7 years. The average corporate tax rate was 37 %. Management believes the S&P 500 is a reasonable proxy for the market portfolio. Therefore, the cost of equity is calculated using the company's equity beta and the market risk premium based on the S&P 500 annual expected rate of return - she would calculate the monthly expected market return using 5 years of past monthly price data available in the worksheet Marketdata. This would then be multiplied by 12 to estimate the annual expected rate. She remembered that if the expected rate of return for the market was too low, too high, or negative, a forward looking rate of an historical average of about 9.5% would have to be used, as the calculated value for the current 5-year period may not be representative of the future. She would consider a E(Rm) between 8-12% acceptable. She would calculate the market risk premium: E(Rm) - Rf from the previous calculations using the risk-free rate data available in the worksheet Marketdata. She noted that the risk-free rate was on an annual basis.She needed to calculate the rate at which the project would have to be discounted to calculate the Net Present Value (NPV) of the proposed project based on the decision of raising capital and the current capital market environment. This discount rate, the WACC, would obviously influence the NPV and could affect the decision of whether to accept or reject the project. Thankfully, all the information needed to calculate this was available. Shantel needed to clearly show all the calculations and sources for all parameter estimates used in the calculation of the WACC (and ultimately the NPV).Gathering all the available information, She got a large cup of extra strong coffee and sat down to work on the development of the Capital Budgeting project model. The correct recommendation to the board was critical to the future growth of the firm! (Numerical Inputs Expected from you are highlighted in yellow and Formula/Function Inputs are highlighted in blue) Parameters Economic life of project in years. 4 Market Value of Equity # of Shares Outstanding Price of New Equipment $ 154,110.00 Market Value of Debt # of Bonds Outstanding Change in NWC Total Market Value $ Market Price of Bonds $ 911.00 Fixed Costs Weight of Equity Market Price of Stock $ 73.00 Variable Costs (% of Revenue) Weight of Debt Salvage value of New Equipment E(R) Marginal Tax Rate R Bond Info Provided for your convenience First Year Unit Sales 4800 B Years to Maturity 7 Sales Growth Rate 24.00% Cost of Equity (r.) PMT Unit Sale Price $ 106.00 Cost of Debt (r) FV $1,000 First Year Revenue After-Tax Cost of Debt Before tax YTM WACC Before tax YTM *2 Note Cells C21 and C22 include the initial (today's) cash flows. Column D through G are the operating cash flows. Spreadsheet for determining Cash Flows Cells G38-640 contain the terminal cash flows. Adil, the CFO for the firm works at ABS. The new Designer Womens Tops was expected to sell for $ 106 per unit and had projected sales of 4800 units in the first year, with a projected (Most-Likely scenario) 24.0 % growth rate per year for subsequent years. A total investment of $ 909,000 for new equipment was required. The equipment had fixed maintenance contracts of $ 352,276 per year with a salvage value of $ 178,019 and variable costs were 8 % of revenues. Shantel also needed to consider both the Best-Case and Worst-Case scenarios in the analysis with growth rates of 34.00 % and 2.40 % respectively. The new equipment would be depreciated to zero using straight line depreciation. The new project required an increase in working capital of $ 154,110 and $ 30,822 of this increase would be offset with accounts payable.ABS currently has 810000 shares of stock outstanding at a current price of $ 73.00. Even though the company has outstanding stock, it is not publicly traded and therefore there is no publicly available financial information. However, after analysis management believes that its equity beta is 1.26. The company also has 79000 bonds outstanding, with a current price of $ 911.00. The bonds pay interest semi-annually at a coupon rate of 5.60 %. The bonds have a par value of $1,000 and will mature in 7 years. The average corporate tax rate was 37 %. Management believes the S&P 500 is a reasonable proxy for the market portfolio. Therefore, the cost of equity is calculated using the company's equity beta and the market risk premium based on the S&P 500 annual expected rate of return - she would calculate the monthly expected market return using 5 years of past monthly price data available in the worksheet Marketdata. This would then be multiplied by 12 to estimate the annual expected rate. She remembered that if the expected rate of return for the market was too low, too high, or negative, a forward looking rate of an historical average of about 9.5% would have to be used, as the calculated value for the current 5-year period may not be representative of the future. She would consider a E(Rm) between 8-12% acceptable. She would calculate the market risk premium: E(Rm) - Rf from the previous calculations using the risk-free rate data available in the worksheet Marketdata. She noted that the risk-free rate was on an annual basis.She needed to calculate the rate at which the project would have to be discounted to calculate the Net Present Value (NPV) of the proposed project based on the decision of raising capital and the current capital market environment. This discount rate, the WACC, would obviously influence the NPV and could affect the decision of whether to accept or reject the project. Thankfully, all the information needed to calculate this was available. Shantel needed to clearly show all the calculations and sources for all parameter estimates used in the calculation of the WACC (and ultimately the NPV).Gathering all the available information, She got a large cup of extra strong coffee and sat down to work on the development of the Capital Budgeting project model. The correct recommendation to the board was critical to the future growth of the firm! (Numerical Inputs Expected from you are highlighted in yellow and Formula/Function Inputs are highlighted in blue) Parameters Economic life of project in years. 4 Market Value of Equity # of Shares Outstanding Price of New Equipment $ 154,110.00 Market Value of Debt # of Bonds Outstanding Change in NWC Total Market Value $ Market Price of Bonds $ 911.00 Fixed Costs Weight of Equity Market Price of Stock $ 73.00 Variable Costs (% of Revenue) Weight of Debt Salvage value of New Equipment E(R) Marginal Tax Rate R Bond Info Provided for your convenience First Year Unit Sales 4800 B Years to Maturity 7 Sales Growth Rate 24.00% Cost of Equity (r.) PMT Unit Sale Price $ 106.00 Cost of Debt (r) FV $1,000 First Year Revenue After-Tax Cost of Debt Before tax YTM WACC Before tax YTM *2 Note Cells C21 and C22 include the initial (today's) cash flows. Column D through G are the operating cash flows. Spreadsheet for determining Cash Flows Cells G38-640 contain the terminal cash flows

Answer & Explanation Solved by verified expert

See Answer