Scribd Logo
Upload

Welcome to Scribd!

  • 1 views

    1. Excel - SIMPLIFICATION of Financial Model

    Uploaded by

    sahilkumar240801

    Copyright:

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

    1. Excel - SIMPLIFICATION of Financial Model

    Uploaded by

    sahilkumar240801
    1 views20 pages

    Copyright

    © © All Rights Reserved

    Available Formats

    XLSX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Copyright:

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

    1. Excel - SIMPLIFICATION of Financial Model

    Uploaded by

    sahilkumar240801

    Copyright:

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

    Simplification

    Don’t forget to join our Free mentoring group on Telegram https://t.me/+hbk1ypcjgbhkMjk9

    Connect with Parth Sir on Linkedin https://www.linkedin.com/in/caparthverma/


    RiskFreeRate 4% Practical Exercise you need to do
    MarketReturns 10%
    Beta 1.25 1. Do good practice of Name Manager. Go
    Formula - Name Manager - Create from
    CostofDebt 14% Selection
    TaxRate 30%
    EquityValue 125000
    DebtValue 125000

    Cost of Capital 10.65%

    Cost of Capital 10.65%

    Cost of Capital Use name Manager and solve this


    xercise you need to do
    practice of Name Manager. Go to
    ame Manager - Create from
    Practical Exercise you need to do
    Risk Free Rate 4%
    Market Returns 10% 1. Do good practice of Name Manager. Formula - N
    ERP #NAME? Use name Manager and solve this 2. Create this from scratch in your own spreadshee
    Beta 1.25
    3. Complete below excercise (use instructions) - N
    Cost of Equity #NAME? Use name Manager and solve this - Complete the forecast using Names
    - Highlight cells K23:L25 and create name for cells
    - Complete "Next Year Sales"(L27) using Names
    Cost of Debt 14% - Complete "Cost of Sales" (L28) using names
    Tax Rate 30% - Reduce Cost of sales from Next year sales to get
    Post Tax Cost of Debt #NAME? Use name Manager and solve this

    Equity Value 125000


    Debt Value 125000
    Equity Funding #NAME? Use name Manager and solve this
    Debt Funding #NAME? Use name Manager and solve this

    Cost of Capital 10.65%

    Cost of Capital 10.65%

    Cost of Capital #NAME? Use name Manager and solve this

    Cost of Capital #NAME? Use name Manager and solve this


    l Exercise you need to do
    od practice of Name Manager. Formula - Name Manager - Create from Selection

    this from scratch in your own spreadsheet (repeat what I have done in video)

    ete below excercise (use instructions) - NO SOLUTION PROVIDED FOR THIS - DIY
    te the forecast using Names
    ht cells K23:L25 and create name for cells L23, L24, and L25
    te "Next Year Sales"(L27) using Names
    te "Cost of Sales" (L28) using names
    Cost of sales from Next year sales to get "Gross Profit" (L29)

    Exercise
    Current Sales 14,500.0
    Sales Growth 10%
    Gross Margin 60%

    Next Year Sales


    Cost of Sales
    Gross Profit
    D/E Ratio 25%
    Coupon Rate 9.50%

    D/E Ratio Risk Rating Coupon Rate


    0% Baa2 9.50%
    30% Baa3 9.75%
    50% Ba1 12.25%
    60% Ba2 13.75%

    D/E Ratio 25%


    Coupon Rate #### Use VLOOKUP to solve this - Approximate Match (1)

    D/E Ratio Risk Rating Coupon Rate


    0% Baa2 9.50%
    30% Baa3 9.75%
    50% Ba1 12.25%
    60% Ba2 13.75%
    Advanced Simplification
    Don’t forget to join our Free mentoring group on Telegram https://t.me/+hbk1ypcjgbhkMjk9

    Connect with Parth Sir on Linkedin https://www.linkedin.com/in/caparthverma/


    Current Sales 14,500.0 Practical Exercise you need to do
    Sales Growth 10.00%
    1. Use goal seek function - Our goal here is
    Gross Margin 60.00% at which gross profit will be 11,000
    2. First select cell C12, the go to Data Menu
    Next Year Sales 15,950.0 Seek
    Cost of Sales -6,380.0
    3. Ensure "Set cell" is set to C12 - this is the
    Gross Profit 9,570.0 goal

    4. Input 11000 in "To Value" - this is our go

    5. Ensure "By Changing Value" is set to C7 -


    change to achieve goal of 11000 gross profi
    Exercise you need to do
    l seek function - Our goal here is identify the Sales growth
    ross profit will be 11,000
    ect cell C12, the go to Data Menu - What if Analysis - Goal

    "Set cell" is set to C12 - this is the cell where we set of our

    1000 in "To Value" - this is our goal we want to achieve.

    "By Changing Value" is set to C7 - this is what we want to


    achieve goal of 11000 gross profit.
    Current Sales 14,500.0 Practical Exercise you need to do
    Sales Growth 10.00%
    1. Ensure that data is cell C16 should be LINK
    Gross Margin 60.00% and not pasted specially as values.
    2. Select your data table B16:C25
    Next Year Sales 15,950.0
    Cost of Sales -6,380.0 3. Go to Data>What If Analysis>Data Table
    Gross Profit 9,570.0 4. In "Column Input Cell" Select your input w
    column. In this case, it is Sales growth, so we
    5. Practice this multiple times.
    Sales Growth Gross Profit
    9,570.0
    8.00% 9,396.0
    10.00% 9,570.0
    12.00% 9,744.0
    14.00% 9,918.0
    16.00% 10,092.0
    18.00% 10,266.0
    20.00% 10,440.0
    22.00% 10,614.0
    24.00% 10,788.0
    Exercise you need to do
    that data is cell C16 should be LINKED with your calculation
    asted specially as values.
    our data table B16:C25

    ata>What If Analysis>Data Table


    mn Input Cell" Select your input which is mentioned in
    n this case, it is Sales growth, so we will put $C$4. Press OK
    this multiple times.
    Current Sales 14,500.0 Practical Exercise
    Sales Growth 10.00%
    Watch video and
    Gross Margin 60.00% that you have goo
    inputs. It's popula
    Next Year Sales 15,950.0
    Cost of Sales -6,380.0
    Gross Profit 9,570.0

    Gross Margin
    9,570.0 40.00% 50.00% 60.00% 70.00% 80.00% 90.00%
    8.00% 6,264.0 7,830.0 9,396.0 10,962.0 12,528.0 14,094.0
    10.00% 6,380.0 7,975.0 9,570.0 11,165.0 12,760.0 14,355.0
    12.00% 6,496.0 8,120.0 9,744.0 11,368.0 12,992.0 14,616.0
    Sales Growth 14.00% 6,612.0 8,265.0 9,918.0 11,571.0 13,224.0 14,877.0
    16.00% 6,728.0 8,410.0 10,092.0 11,774.0 13,456.0 15,138.0
    18.00% 6,844.0 8,555.0 10,266.0 11,977.0 13,688.0 15,399.0
    20.00% 6,960.0 8,700.0 10,440.0 12,180.0 13,920.0 15,660.0
    22.00% 7,076.0 8,845.0 10,614.0 12,383.0 14,152.0 15,921.0
    24.00% 7,192.0 8,990.0 10,788.0 12,586.0 14,384.0 16,182.0
    Practical Exercise you need to do
    Watch video and do it with me simultaneously. Its VERY IMP
    that you have good hold on operating data tables with two
    inputs. It's popularily known as Scenario Analysis.
    Scenario Summary
    Current Values: baSE WORST BEST
    Changing Cells:
    $C$6 14,500.0 14,500.0 15,550.0 20,000.0
    $C$7 10.00% 10.00% 8.00% 20.00%
    $C$8 60.00% 60.00% 50.00% 80.00%
    Result Cells:
    $C$12 9,570.0 9,570.0 8,397.0 19,200.0
    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.
    Current Sales 14,500.0
    Sales Growth 10.00%
    Gross Margin 60.00%

    Next Year Sales 15,950.0


    Cost of Sales -6,380.0
    Gross Profit 9,570.0
    Practical Exercise you need to do
    1. Go to Data> What if Analysis > Scenario Manager
    2. Click on 'Add Scenario'

    3. Name your Scenario (Worst Case/Base Case/Best Case); then


    mention Input cells (C6:C8) in Changing Cells. Press OK

    4. Now "Scenario Values" window will open. Input Current


    Sales/Sales Growth Percent/Gross Margin as per cell selected. Press
    Ok. The Scenario is now added in Scenario Manager.

    5. Add remaining scenarios.

    6. Once all scenarios are added, Open Scenario Manager - Press


    'Summary' to get result of all scenarios. Keep report type as
    "Scenario Summary"
    Current Sales 14,500.0 Practical Exercise you need
    Sales Growth 10.00%
    1. Use Data Validation on cell
    Gross Margin 60.00% open the data validation - Alt+
    2. Your Sales Growth rate shou
    Next Year Sales 15,950.0 Validation - Settings Tab
    Cost of Sales -6,380.0
    3. On 'Input Message' tab, set
    Gross Profit 9,570.0 "Sales growth generally range
    box.

    4. On the 'Error Alert' tab, set


    message you want. For Eg - "R
    lies between 0%-10%"

    5. Once complete - Test it to e

    6. Practice 'Circle Invalid Data'


    sessions.
    actical Exercise you need to do
    Use Data Validation on cell C7 i.e. Sales growth percent input cell. To
    en the data validation - Alt+A+V+V
    Your Sales Growth rate should be between 0 to 10% on Data
    idation - Settings Tab
    On 'Input Message' tab, set title as "Please Note:" and then type
    ales growth generally ranges between 0 to 10%" in 'Input Message'
    x.

    On the 'Error Alert' tab, set the style to 'Warning' and type the error
    ssage you want. For Eg - "Recheck Sales growth percent, historically it
    s between 0%-10%"

    Once complete - Test it to ensure data validation is working properly.

    Practice 'Circle Invalid Data' function once - We will use it in future


    sions.
    Practical Exercise
    Current Sales 14,500.0
    Watch video again
    Sales Growth 10.00% important excercis
    Gross Margin 60.00% model

    Next Year Sales 15,950.0


    Cost of Sales -6,380.0
    Gross Profit 9,570.0
    Practical Exercise you need to do
    Watch video again and do it with me simultaneously. Its very
    important excercise to ensure that no one can mess up with your
    model

    You might also like