Answered step by step
Verified Expert Solution
Question
1 Approved Answer
INSERT YOUR NAME AND SECTION HERE Excel Assignment 4 A beverage company that currently sells apple juice is considering adding grape juice to its product
INSERT YOUR NAME AND SECTION HERE
Excel Assignment
A beverage company that currently sells apple juice is considering adding grape juice to its product line. They have determined they can sell the new product, grape juice, for $ per bottle. Their manufacturing plant has excess capacity in a fully depreciated building to process the gape juice drink. The grape juice drink will be discontinued after three years. The new equipment will be depreciated straight line to zero over its four year life. The new grape juice project will require an additional $ of working capital. The projected sales of the grape juice are bottles the first year, with a sales growth rate for both the following years. Variable costs are of sales and fixed costs are $ each year. The new equipment costs $ and will have a salvage value of $
The corporate marginal tax rate is and the company currently has shares of stock outstanding at a current price of $ per share. The company also has bonds outstanding, with a current price of $ The bonds pay interest semiannually and the coupon rate is The bonds have a par value of $ and will mature in years.
Even though the company has stock outstanding it is not publicly traded. Therefore, there is no public financial information available. However management believes that given the industry they are in the most reasonable comparable publicly traded company is PepsiCo, Inc ticker symbol PEP
In addition, management believes the S&P is a reasonable proxy for the market portfolio.
Therefore, the cost of equity is calculated using the beta from PEP and the market risk premium based on the S&P annual expected rate of return. We calculated a monthly expected retijin for the market in the return exercise. You can simply multiply that rate by for an expected arinual rate on the market.
Use the most recent month US Treasury Bill rate for the Risk Free Rate.
The WACC is then calculated using this information and the other information provided above.
Clearly show all your calculations and sources for all parameter estimates used in the WACC.
Required
In the Blank Template worksheet tab:
Fill in all the yellow shaded cells with the given information.
Insert formulas in the blue shaded cells, II Calculations, referencing cells in Section I. Calculate the cost of debt using the Rate formula, and the given bond information.
Enter formulas in the blue cells in Sections III, IV V and VI creating a partial income statement, incremental cash flows including terminal cash flows and the calulation of NPV
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started