Scribd Logo
Upload

Welcome to Scribd!

  • 40 views

    Case Study - Discounted Cash Flow

    Uploaded by

    Salman Ahmad
    This document provides a financial model for ABC Coffee over 10 years from 2021-2030. It includes assumptions for initial investment, sales growth rate, discount rate, tax rate. It outlines variable and fixed costs. The model forecasts income statements and cash flows under different scenarios of orders growth (5%, 10%, 20%) and price increases (3%, 5%, 15%). Key metrics like NPV, PI, IRR, payback period and ROI are calculated to evaluate the investment. Formulas are provided to understand how the metrics are derived from cash inflows and outflows.

    Copyright:

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

    Case Study - Discounted Cash Flow

    Uploaded by

    Salman Ahmad
    40 views14 pages
    This document provides a financial model for ABC Coffee over 10 years from 2021-2030. It includes assumptions for initial investment, sales growth rate, discount rate, tax rate. It outlines variable and fixed costs. The model forecasts income statements and cash flows under different scenarios of orders growth (5%, 10%, 20%) and price increases (3%, 5%, 15%). Key metrics like NPV, PI, IRR, payback period and ROI are calculated to evaluate the investment. Formulas are provided to understand how the metrics are derived from cash inflows and outflows.

    Original Title

    8. Case Study - Discounted Cash Flow

    Copyright

    © © All Rights Reserved

    Available Formats

    XLSX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document provides a financial model for ABC Coffee over 10 years from 2021-2030. It includes assumptions for initial investment, sales growth rate, discount rate, tax rate. It outlines variable and fixed costs. The model forecasts income statements and cash flows under different scenarios of orders growth (5%, 10%, 20%) and price increases (3%, 5%, 15%). Key metrics like NPV, PI, IRR, payback period and ROI are calculated to evaluate the investment. Formulas are provided to understand how the metrics are derived from cash inflows and outflows.

    Copyright:

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

    Case Study - Discounted Cash Flow

    Uploaded by

    Salman Ahmad
    This document provides a financial model for ABC Coffee over 10 years from 2021-2030. It includes assumptions for initial investment, sales growth rate, discount rate, tax rate. It outlines variable and fixed costs. The model forecasts income statements and cash flows under different scenarios of orders growth (5%, 10%, 20%) and price increases (3%, 5%, 15%). Key metrics like NPV, PI, IRR, payback period and ROI are calculated to evaluate the investment. Formulas are provided to understand how the metrics are derived from cash inflows and outflows.

    Copyright:

    © All Rights Reserved
    Download as XLSX, PDF, TXT or read online from Scribd
    Download as xlsx, pdf, or txt
    of 14

    Basics

    Methods to change name


    Name Box
    Namae Manager

    Shortcut Keys

    CTRL+Shift+F3 Creat from Selection


    CTRL+F3 Display namer manager
    F3 To past

    Sales Growth-Base 10%


    Discount Rate 15%
    Interest Rate 30%

    Present Value Function


    P/'(1+R)^n

    Forumals

    =NPV-II

    =NPV/II PI>1

    Income/Investment
    Briefly
    NPV=NPV function-Initial Investment
    PI = NPV function/initial Investment
    ROI= Acc Disc Infow-Acc Disc Outflow/Acc. Disc Outflow
    Payback
    ABC Coffee

    Financial Model

    For the years 2021 to 2030


    ABC Coffee
    Assumptions
    Initial Investment $600,000
    Price per order-(First Year) $100
    Orders (First year) 1000
    Sales Growth-Base 10%
    Discount Rate 10.0%
    Tax Rate 30%
    Time (Years) 10
    Base Year 2020

    Variable costs
    Material Cost per Unit $10.00
    Selling Expenses per Unit $8.00
    Other Variable Cost $4.00

    Fixed Costs
    Lease payments $4,000.00
    Administration $3,500.00
    Other Fixed Cost $3,000.00
    Investment
    Waffle iron $2,000 TASKs:
    Squeezer $2,000
    Blender $2,000 • Put appropriate figures to prepare an yearly Cas
    Dishwasher $8,000
    Fridge freezer $8,000 • Calculate NPV, PI, IRR, ROI & Payback Period
    Ice maker $4,000
    Confectionery showcase $10,000
    Carob coffee machine $30,000
    • Assign 3 different Scenarios Worst Case, Base C
    Coffee grinder $4,000 a) Growth in sales respectively as 5%, 10% & 20%
    Other Equipments $130,000 b) Selling price per unit respectively as 3%, 5% &
    Land & Building $400,000
    $600,000 HINT --> Use name Ranges to save the time
    are an yearly Cash Flow model
    ayback Period

    orst Case, Base Case & Best Case on the


    s 5%, 10% & 20%
    ely as 3%, 5% & 15%

    ve the time
    ### ABC Coffee
    Discounted Cash Flow Model
    Net Present Value
    Profitablity Index
    Internal Rate of Return
    BayBack Period
    ROI

    Chose Scenario

    Base Year 2020

    A Projected Income Statement


    Years # 0 1 2
    20 FY21 FY22
    Price per order
    Orders (First year)
    Net Sales/Inflow $0 $0

    PV of Inflow
    Accumulated PV of Inflow

    Material Cost per Unit


    Selling Expenses per Unit
    Others per Unit
    Total Variable Cost (per unit) $0.0 $0.0
    Total Variable Cost $0.0 $0.0

    Lease payments
    Administration
    Others Fixed Cost
    Total Fixed Cost $0.0 $0.0

    Total Outflow $600,000.0


    PV of outflow $600,000.0
    Accumulative PV of outflow $600,000.0

    Net Income-IBT -600000 $0.0 $0.0


    Tax $0.0 $0.0
    Net Income after Tax $0.0 $0.0

    Net Cash Flow-Helping Cell -$600,000.0 $0.0 $0.0


    Amount Left to Payback -$600,000.0 -$600,000.0 -$600,000.0

    Via Formula Manual Verification


    Net Present Value ($600,000) $0.00 Acc Inflow-Acc Outflow
    Profitablity Index 0.00
    Internal Rate of Return Err:523
    BayBack Period 10Years
    ROI #DIV/0!

    Power of F3
    F3: to past values
    CTRL+F3: to open name manager
    CTRL+SHFT+F3: Create names from selection
    0
    -0 - 1 -2 - 3 -4 -5 -6 - 7 -8 - 9 0 Scenario Manager
    -100000ear ear ear ear ear ear ear ear ear ear ar-1
    Y Y Y Y Y Y Y Y Y Y Ye
    -200000 2
    esent Value ($600,000) -300000 Base Case
    0.0 -400000 Best Case
    al Rate of Return Err:523 -500000 Worst Case
    10Years -600000
    #DIV/0! -700000 Used in Model

    Years
    3 4 5 6 7 8
    FY23 FY24 FY25 FY26 FY27 FY28

    $0 $0 $0 $0 $0 $0

    $0.0 $0.0 $0.0 $0.0 $0.0 $0.0


    $0.0 $0.0 $0.0 $0.0 $0.0 $0.0

    $0.0 $0.0 $0.0 $0.0 $0.0 $0.0

    $0.0 $0.0 $0.0 $0.0 $0.0 $0.0


    $0.0 $0.0 $0.0 $0.0 $0.0 $0.0
    $0.0 $0.0 $0.0 $0.0 $0.0 $0.0

    $0.0 $0.0 $0.0 $0.0 $0.0 $0.0


    -$600,000.0 -$600,000.0 -$600,000.0 -$600,000.0 -$600,000.0 -$600,000.0

    flow-Acc Outflow For better understanding


    NPV=NPV function-Initial Investment
    PI = NPV function/initial Investment
    ROI= Acc Disc Infow-Acc Disc Outflow/Acc. Disc Outflow
    rio Manager
    Orders Growth Price
    10% 5%
    20% 15%
    5% 3%

    20% 15%

    9 10
    FY29 FY30

    $0 $0

    $0.0 $0.0
    $0.0 $0.0

    $0.0 $0.0

    $0.0 $0.0
    $0.0 $0.0
    $0.0 $0.0

    $0.0 $0.0
    -$600,000.0 -$600,000.0
    Inflow Outflow
    Year-0 -600000
    Year-1 0 0
    Year-2 0 0
    Year-3 0 0 Chart Title
    Year-4 0 0 0
    Year-5 0 0 Year-0 Year-1 Year-2 Year-3 Year-4 Year-5 Year-6 Year-7 Year-8 Year-9 Year-1
    -100000
    Year-6 0 0
    Year-7 0 0 -200000
    Year-8 0 0 -300000
    Year-9 0 0
    Year-10 0 0 -400000

    -500000

    -600000

    -700000

    Inflow Outflow
    ar-6 Year-7 Year-8 Year-9 Year-10

    You might also like