Reference no: EM1345011
Use the project data given below to build a spreadsheet to:
1. Calculate the cash flows for all years of this project.
2. Calculate the WACC for this project.
3. Calculate the NPV and IRR for this project.
4. Use an IF statement to generate a decision for this project in a worksheet other than the worksheet that calculates the project's cash flows.
Rye Baking Company is considering replacing its manual bread mixing and baking process with a new mixing and baking machine for its specialty breads. The proposed new machine has a purchase price of $340,200 and a cost of $4,000 to ship and install. This cost will be depreciated straight-line to zero over the project's 5-year tax life. The project life of the mixing/baking system for the bakery, however, is 4 years, at the end of which the mixing system is expected to be scrapped for $65,500.
The new mixing and baking system is expected to make a better quality product and the bakery expects to see sales increase by $107,200 per year. The bakery does not expect the new machine to make any change in total operating costs. The proposed new system requires an initial investment in a net working capital of $48,200. The company's tax rate is 34 percent.
The company wants to maintain a 0.36 debt to equity ratio. The YTM on the company's existing bonds is 5.9 percent. The company's stock is selling for $78.00 per share and it just paid a dividend of $2.30 per share. Dividends are expected to grow at an average constant rate of 4.2 percent per year.
Note: The NPV function in Excel is not correctly programmed to calculate NPV as the term is used in finance. The Excel NPV function calculates only the PV of the future cash flows and does not include the initial outlay in its formulation. Adjust your spreadsheet formulation to reflect this programming error. Double check the spreadsheet results with the calculator to be sure spreadsheet results are correct.
The spreadsheet must include:
- At least two worksheets - with at least one worksheet for inputs and summary of output (results).
- At least 2 different types of number format.
- At least 2 different colors for cell highlighting.
- All calculations must be done within the spreadsheet.
- All formulated cell calculations must use cell references (the number inputs must be first listed or input in another cell).
- Use at least one IF statement to automatically generate a project accept/reject decision.
- Use at least 2 built-in formulas or functions (fx),other than =IF (e.g., =sum(D3:F3), =NPV(rate,A3:A9), etc.).
- Include at least 1 cell with a defined name (see the name manager under the formula tab).
|
What required rate of return for this stock
: What required rate of return for this stock would result in a price per share of $40 and if Sonik has an earnings and dividend growth rate of 11%, what required rate of return would result in a price per share of $40?
|
|
Find the maximum height it reaches
: A 115 granite cube slides down a 35 frictionless ramp. At the bottom, just as it exits onto a horizontal table, it collides with a 235 steel cube at rest.
|
|
Evaluate the original price per share
: Evaluate the original price per share that the firm sold its single issue of common stock - issue of preferred stock and one issue of common stock outstanding
|
|
With what initial speed does he throw the apple
: A long wire lying along the x-axis carries a current of 2.8 A in the +x direction. There is a uniform magnetic field present, given by B=0.003i + 0.004j + 0.002k, where i, j, k are the unit vectors along the cartesian coordinate axes') in units of..
|
|
Calculate the wacc and npv
: Rye Baking Corporation is planning replacing its manual bread mixing and baking process with a new mixing and baking machine for its specialty breads.
|
|
What range of speeds can a car safely handle curve
: A rectangular container measuring 10cmx20cmx25cm is filled with gas. What is the mass volume of water in kilograms.
|
|
Explain what was bavarian brewhouses dividend payout
: Explain what was bavarian brewhouse's dividend payout ratio in 2004 - the required rate of return on extruded's shares is 13%. what is the share price today based on the Gordon growth model?
|
|
What was the height of the aeroplane
: State your answer in terms of and Use three significant figures in the numerical coefficient.
|
|
Explain what is the npv of the project
: There is no change expected in the other working capital components. The discount rate is 8% and What is the NPV of the project?
|