Explore BrainMass

Explore BrainMass

    Capital Budgeting and Scenario Manager in Excel

    This content was COPIED 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 2, 2020, 5:17 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.