Explore BrainMass
Share

Explore BrainMass

    Develop an NPV profile graph

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

    Can you create an NPV Profile graph based on the Excel spreadsheet analysis information provided?

    ** Please see the attached file for the Excel formatted problem description **

    Inputs
    ATSV old @ t=0 305,000 ATSV formula = Market salvage value-(Market salvage value-Book value)*tax rate
    Equipment 2,000,000
    Tax Credit 100,000
    Depreciaton per year 300,000
    Sales period 1 1,000,000 growth: g yrs 2-3 = 15% g yrs 4-6 = 5%
    CoGS %of sales 45%
    SG&A exp. %of sales 10%
    ATSV new @ t=6 260,000

    Operating Life CFs
    Time 0 1 2 3 4 5 6
    Sales $1,000,000 $1,150,000 $1,322,500 $1,388,625 $1,458,056 $1,530,959
    - COGS 450,000 67,500 77,625 29,756 31,244 32,806
    - SG&A expenses 100,000 15,000 17,250 6,613 6,943 7,290
    - Depreciation 300,000 300,000 300,000 300,000 300,000 300,000
    = EBIT 150,000 767,500 927,625 1,052,256 1,119,869 1,190,863
    -Taxes (40%) 60,000 307,000 371,050 420,903 447,948 476,345
    = Net Income 90,000 460,500 556,575 631,354 671,921 714,518
    + Depreciation 300,000 300,000 300,000 300,000 300,000 300,000
    = Operating CF 390,000 760,500 856,575 931,354 971,921 1,014,518

    Time 0 Investments
    Equipment -2,000,000
    ATSV old 305,000
    Tax credit 100,000
    NWC -200,000

    Terminal Non-OCF:
    ATSV new @ t=6 260,000
    NWC 200,000
    = Net Cash Flow -$1,795,000 $390,000 $760,500 $856,575 $931,354 $971,921 $1,474,518
    = Cummulative CF -$1,795,000 -$1,405,000 -$644,500 $212,075 $1,143,429 $2,115,350 $3,589,868

    Cost of Capital 8%
    NPV $2,173,336.49
    IRR = 35.0%
    PBP = 2.75
    PI = 2.21

    © BrainMass Inc. brainmass.com October 10, 2019, 5:23 am ad1c9bdddf
    https://brainmass.com/economics/personal-finance-savings/developing-npv-profile-graph-503224

    Attachments

    Solution Summary

    This solution provides a detailed sample of the given finance problem in Excel.

    $2.19