Scribd Logo
Upload

Welcome to Scribd!

  • 42 views

    S16 - Scenario Manager - NPV - Class

    Uploaded by

    ABHAY VEER SINGH
    The document contains financial information for 3 student projects including cash flows over 5 years and the net present value (NPV) for each. Student 1 has an NPV of -$78.54, Student 2 has an NPV of -$57.81, and Student 3 has the highest NPV of $147.96. The document also provides 2 investment project cash flows over 4 years and asks to calculate the NPV and internal rate of return (IRR) of each to determine which project adds more value.

    Copyright:

    © All Rights Reserved
    Download as XLSX, PDF, TXT or read online from Scribd

    S16 - Scenario Manager - NPV - Class

    Uploaded by

    ABHAY VEER SINGH
    42 views11 pages
    The document contains financial information for 3 student projects including cash flows over 5 years and the net present value (NPV) for each. Student 1 has an NPV of -$78.54, Student 2 has an NPV of -$57.81, and Student 3 has the highest NPV of $147.96. The document also provides 2 investment project cash flows over 4 years and asks to calculate the NPV and internal rate of return (IRR) of each to determine which project adds more value.

    Original Description:

    Excel

    Original Title

    S16_Scenario Manager_NPV_Class

    Copyright

    © © All Rights Reserved

    Available Formats

    XLSX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document contains financial information for 3 student projects including cash flows over 5 years and the net present value (NPV) for each. Student 1 has an NPV of -$78.54, Student 2 has an NPV of -$57.81, and Student 3 has the highest NPV of $147.96. The document also provides 2 investment project cash flows over 4 years and asks to calculate the NPV and internal rate of return (IRR) of each to determine which project adds more value.

    Copyright:

    © All Rights Reserved
    Download as XLSX, PDF, TXT or read online from Scribd
    Download as xlsx, pdf, or txt
    42 views11 pages

    S16 - Scenario Manager - NPV - Class

    Uploaded by

    ABHAY VEER SINGH
    The document contains financial information for 3 student projects including cash flows over 5 years and the net present value (NPV) for each. Student 1 has an NPV of -$78.54, Student 2 has an NPV of -$57.81, and Student 3 has the highest NPV of $147.96. The document also provides 2 investment project cash flows over 4 years and asks to calculate the NPV and internal rate of return (IRR) of each to determine which project adds more value.

    Copyright:

    © All Rights Reserved
    Download as XLSX, PDF, TXT or read online from Scribd
    Download as xlsx, pdf, or txt
    You are on page 1of 11

    rate of interest 8%

    Student 1 NPV NPV(r,V!,V2,V3,V4…....)


    Year 1 -5000 $ -78.54
    Year 2 -5000
    Year 3 4000
    Year 4 4000
    Year 5 4000
    2000

    Student 2
    Year 1 -8000 $ -57.81
    Year 2 -2000
    Year 3 6000

    Year 4 4000

    Year 5 2000

    2000
    Student 3
    Year 1 -5000 $ 147.96 400
    Year 2 -5000
    Year 3 6000
    Year 4 4000
    Year 5 2000
    2000

    Q. Consider the following two projects, and assume a company’s cost of cap
    assuming cash flow ($ '000) at end of respective years below. Which projects
    a single project, which project should it choose?

    Year 1 Year 2 Year 3

    Project 1 -40 130 19


    Project 2 -80 36 36
    Option 1
    - Pay
    Once

    Option 2
    - Pay in 5
    Annual
    Installme
    nts
    Cost of
    Capital

    NPV
    PV
    V2,V3,V4…....)

    NPV
    PMT
    ROW() 8
    COLUMN() 14
    Goal Seek
    Scenario Manager

    r=20% (0.2)
    Total
    Time/ 1 2 3 Cash
    Year Flow

    Investme
    nt 1 Cash -10000 24000 -14000 0
    Flow ($
    '000)

    Investme
    nt 2 Cash -6000 8000 -1000 1000
    Flow ($
    '000)

    Year End NPV1 Rs231.48


    Year End NPV2 (Rs23.15)

    ume a company’s cost of capital is 15 percent. Find the IRR and NPV of each project,
    e years below. Which projects add value to the company? If the company can choose only
    ?

    Rate of
    0.15
    Return

    Year 4 NPV IRR NPV@IRR

    26 Err:523 Rs90.87
    36 16.65% Rs1.91
    $
    11,000.00

    $- $- $- $-
    $ -3,000.00
    3,000.00 3,000.00 3,000.00 3,000.00

    12%
    Scenario Summary
    Current Values: best most likely worst

    Changing Cells:
    price $ 2.50 $ 5.00 $ 4.00 $ 2.50
    unit_cost $ 0.40 $ 1.00 $ 0.75 $ 0.40
    fixed_cost $ 25,000.00 $ 65,000.00 $ 45,000.00 $ 25,000.00
    Result Cells:
    profit $ 88,400.00 $ 151,000.00 $ 130,500.00 $ 88,400.00
    Notes: Current Values column represents values of changing cells at
    time Scenario Summary Report was created. Changing cells for each
    scenario are highlighted in gray.
    2 marks question
    Scenario Summary
    Current Values: best most likely worst
    Changing Cells:
    price $ 2.50 $ 5.00 $ 4.00 $ 2.50
    unit_cost $ 0.40 $ 1.00 $ 0.75 $ 0.40
    fixed_cost $ 25,000.00 $ 65,000.00 $ 45,000.00 $ 25,000.00
    Result Cells:
    profit $ 88,400.00 $ 151,000.00 $ 130,500.00 $ 88,400.00
    Notes: Current Values column represents values of changing cells at
    time Scenario Summary Report was created. Changing cells for each
    scenario are highlighted in gray.
    Scenario 1 - Lemonade Stall
    Price per glass of Lemonade $ 5.00
    Monthly Demand for Lemonades 54000
    Unit Cost/ Lemonade $ 1.00 Price
    Total Fixed Cost $ 65,000.00 Unit Cost
    Monthly Revenues $ 270,000.00 Fixed Cost
    Variable Cost $ 54,000.00
    Overall Profits $ 151,000.00
    Best Case Most Likely Worst Case
    MO ML PS
    $5.00 $4.00 $2.50
    $1.00 $0.75 $0.40
    $65,000.00 $45,000.00 $25,000.00
    A company wants to review best, worst, and most-likely scenarios for the sales of an automobile glass wiper blades by varying the
    1 sales price. IT makes use of Scenario Manager to vary more than two inputs in a sensitivity analysis? Suppose it has created the
    value (NPV) of the company based upon next 5 years cash flows. For each scenario, the company looks at the firm’s NPV and each

    Automobile Component Manufacturer Example

    Tax Rate 15%


    Year 1 Sales 10000
    Annual Sales Growth 10%
    Year 1 Price $ 7.50
    Year 1 Cost $ 6.00
    Interest Rate 15%
    Annual Increase in Costs 5%
    Annual Price Increase 3%
    Year 1 2 3 4
    Unit Sales 10000 11000 12100 13310
    Unit Price $ 7.50 $ 7.73 $ 7.96 $ 8.20
    Unit Cost $ 6.00 $ 6.30 $ 6.62 $ 6.95
    Revenues $ 75,000.00 $ 84,975.00 $ 96,276.68 $ 109,081.47
    Costs $ 60,000.00 $ 69,300.00 $ 80,041.50 $ 92,447.93
    Profits Before Tax $ 15,000.00 $ 15,675.00 $ 16,235.18 $ 16,633.54
    Tax $ 2,250.00 $ 2,351.25 $ 2,435.28 $ 2,495.03
    Profits After Tax $ 12,750.00 $ 13,323.75 $ 13,799.90 $ 14,138.51

    NPV $45,423.75 =NPV(intrate,B19:F19)


    obile glass wiper blades by varying the values of Year 1 sales, annual sales growth, and Year
    y analysis? Suppose it has created the following three scenarios related to the net present
    mpany looks at the firm’s NPV and each year’s after-tax profit.

    Best Case Most Likely Worst Case


    MO ML PS
    Year 1 Sales 20000 10000 5000
    Annual Sales Growth 0.2 0.1 0.02
    Year 1 Price $ 10.00 $ 7.50 $ 5.00

    5
    14641
    $ 8.44
    $ 7.29
    $ 123,589.31
    $ 106,777.36
    $ 16,811.95
    $ 2,521.79
    $ 14,290.15

    You might also like