Explore BrainMass

Capital Budgeting and Scenario Manager in Excel

This content was STOLEN from BrainMass.com - View the original, and get the already-completed solution here!

A) Suppose that you are approached with an offer to purchase an investment that will provide cash flows of $1,200 per year for 15 years. The cost of purchasing this investment is $9,800. You have an alternative investment opportunity, of equal risk, that will yield 8% per year. What is the NPV that makes you indifferent between the two options?

Frozen Turkeys Scenario
Cost of Land $ 200,000
Cost of Buildings & Equipment $ 350,000
MACRS Class 20
Life of Project (Years) 5
Terminal Value of Land $ 300,000
Terminal Value of Buildings & Equipment $ 175,000
First year sales (pounds) 250,000
Price per Pound $3.50
Unit Sales Growth Rate 7.0%
Variable Costs as % of Sales 62%
Fixed Costs 75,000
Tax Rate 35%
WACC 10.0%

1. Prepare a statement of annual cash flows for years 0 through 5. Cash flows in year 0 are your expenses for building and land.
Sales growth is based on the annual growth rate in units.
Assume no changes in fixed or variable costs.
Depreciate the project cost for 5 years, with the cash flow in year 5 to include the terminal cash flow of ending the investment.

2. Calculate the NPV, profitability index, IRR, MIRR, payback and discounted payback of the cash flows in part 1.

3. Using scenario manager find best case, worst case, and base case of NPV based on sales in pounds, price per pound, and variable cost percent.

© BrainMass Inc. brainmass.com October 25, 2018, 9:36 am ad1c9bdddf

Solution Summary

This solution shows how to evaluate a project by calculating NPV, IRR, MIRR, payback period, discounted payback period, etc. also shows how to use scenario manager in Excel to compare base, best and worst cases.

See Also This Related BrainMass Solution

Capital Budgeting Scenarios

Using net present value, determine the proposal's appropriateness and economic viability.

Prepare a report explaining your calculations and conclusions. Answer the following in your report:

o Explain the effect of a higher or lower cost of capital on a firm's long-term financial decisions.

o Analyze the use of capital budgeting techniques in strategic financial management.

· Format your report according to APA standards.

Proposal C: New Advertising Program

A company wants to invest in a new advertising program. Using the NPV method of capital budgeting, determine the proposal's appropriateness and economic viability with the following information:

? The new program will increase current sales, $10 million, by 20%.
? The new program will have a profit margin is 5% of sales.
? The new program will have a 3-year effect.
? The new program will cost the company $200,000 in the first year.

View Full Posting Details