Purchase Solution

Fundamentals of Corporate Finance: Spreadsheet Building

Not what you're looking for?

Ask Custom Question

Spreadsheet Assignment (WACC and Capital Budgeting)

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.

The Town Amusement Park is considering adding a new amusement park ride. The new ride equipment would cost $290,000 to buy and ship, and $40,620 for handling and assembly.

The company estimates the project life of the proposed new ride to be three years. The proposed ride equipment is expected to have a market or scrap value of $140,500 at the end of the three year project. The new ride equipment will be depreciated straight line to zero across five years. The proposed new ride will add $20,000 in new maintenance costs if the new ride equipment is purchased. The company expects added sales from the new ride to be $150,000 per year. However, the company expects the sales of their other existing rides to be reduced by $25,000 if the new ride project is adopted and purchased.

The company wants to maintain a 0.48 debt to equity ratio. The YTM on the company's existing bonds is 4.3 percent. Its tax rate is 40 percent. The company's stock is selling for $67 per share and it just paid a dividend of $5.20 per share. Dividends are expected to grow at an average constant rate of 2.9 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 your spreadsheet results with your calculator to be sure your 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)

Purchase this Solution

Solution Summary

The solution contains a spreadsheet that will help with the fundamentals of corporate finance. There is an assumptions tab and an analysis tab. The amounts on the analysis tab are pulled from the assumption tab or computed in the cells (click in cells to see computations). If you change an assumption, the amounts update and so does the decision. The different formats, if-then statements and function cells are pointed out for you.

Solution Preview

Your tutorial is attached. There is an assumptions tab and an analysis tab. The amounts on the ...

Solution provided by:
  • BSc, University of Virginia
  • MSc, University of Virginia
  • PhD, Georgia State University
Recent Feedback
  • "hey just wanted to know if you used 0% for the risk free rate and if you didn't if you could adjust it please and thank you "
  • "Thank, this is more clear to me now."
  • "Awesome job! "
  • "ty"
  • "Great Analysis, thank you so much"
Purchase this Solution

Free BrainMass Quizzes
Marketing Management Philosophies Quiz

A test on how well a student understands the basic assumptions of marketers on buyers that will form a basis of their marketing strategies.

Lean your Process

This quiz will help you understand the basic concepts of Lean.

Business Processes

This quiz is intended to help business students better understand business processes, including those related to manufacturing and marketing. The questions focus on terms used to describe business processes and marketing activities.

Social Media: Pinterest

This quiz introduces basic concepts of Pinterest social media

Income Streams

In our ever changing world, developing secondary income streams is becoming more important. This quiz provides a brief overview of income sources.