Explore BrainMass

Capital Budgeting and Scenario Manager in Excel

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 July 21, 2018, 9:25 pm 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.