FS to FCF Template Company Name: McDonald Corp (MCD) Solution Legend Value given in problem Formula/Calculation/Analysis required Qualitative analysis or Short answer required Financial Data...

1 answer below »

FS to FCF Template
        Company Name: McDonald Corp (MCD)                                        Solution Legend
                                                Value given in problem
                                                Formula/Calculation/Analysis required
                                                Qualitative analysis or Short answer required
        Financial Data
            2017    2018    2019    2020    2021
            Year 1    Year 2    Year 3    Year 4    Year 5
        Assets
        Cash & Cash Equivalents    $2,464    $866    $899    $3,449    $4,709
        Short-term Investments    $0    $0    $0    $0    $0
        Receivables    $1,976    $2,442    $2,224    $2,110    $1,872
        Inventories    $59    $51    $50    $51    $56
        Prepaid Expenses    $828    $695    $385    $633    $511
        Total Cu
ent Assets    $5,327    $4,053    $3,558    $6,243    $7,149
        Net Property, Plant & Equipment    $22,448    $22,843    $24,160    $24,958    $24,721
        Total Assets    $27,776    $26,896    $27,718    $31,201    $31,869
        Liabilities
        Accounts payable    $925    $1,208    $988    $741    $1,007
        Cu
ent maturities of long-term debt    $0    $0    $59    $2,244    $0
        Income taxes payable    $266    $228    $332    $741    $361
        Total cu
ent liabilities    $1,191    $1,436    $1,379    $3,726    $1,368
        Long-term debt    $29,536    $31,075    $34,118    $35,197    $35,623
        Total Liabilities    $30,727    $32,512    $35,497    $38,923    $36,990
        Total stockholders’ equity (deficit)
    ($3,268)    ($6,258)    ($8,210)    ($7,825)    ($4,601)
        Total Liabilities & Shareholder's Equity    $27,459    $26,253    $27,287    $31,098    $32,389
        Income Statement    Year 1    Year 2    Year 3    Year 4    Year 5
        Revenue
        Total revenues    $22,820    $21,025    $21,364    $19,208    $23,223
        Expenses
        Cost of Goods Sold    $9,673    $8,521    $8,460    $7,887    $8,871
        General, administrative and other    $2,231    $2,200    $1,967    $2,245    $2,378
        Depreciation and amortization    $1,363    $1,482    $1,868    $1,751    $1,618
        Total expenses    $13,268    $12,203    $12,295    $11,884    $12,867
        Operating income    $9,553    $8,823    $9,070    $7,324    $10,356
        Interest expense    $921    $981    $1,122    $1,218    $1,186
        Income before income taxes    $8,631    $7,841    $7,948    $6,106    $9,170
        Income tax benefit (expense)    $3,381    $1,892    ($1,993)    ($1,410)    ($1,583)
        Net income    $12,013    $9,733    $5,955    $4,696    $7,588
        Dividends Paid    $3,089    $3,256    $3,582    $3,753    $3,919
        Non-distributed Income    $8,923    $6,477    $2,373    $943    $3,669
        Find Changes in NWC
            Year 1    Year 2    Year 3    Year 4    Year 5
        Cu
ent Assets, other than Cash and Marketable Securities    $2,863    $3,187    $2,659    $2,794    $2,439
        Cu
ent Liabilities, excluding Debt    $1,191    $1,436    $1,320    $1,482    $1,368
        Net Working Capital (CA-CL)    $1,673    $1,751    $1,340    $1,312    $1,072
        Change in NWC (NWC Investment)        $78    ($412)    ($28)    ($240)
        Find CapEx
            Year 1    Year 2    Year 3    Year 4    Year 5
        Net Property, Plant & Equipment    $22,448    $22,843    $24,160    $24,958    $24,721
        Depreciation and amortization    $1,363    $1,482    $1,868    $1,751    $1,618
        Capex        $513    $1,703    $682    ($371)
        You can read more using the following link    Howard Keen, Clearing Up Confusion over calculation of FCF.
        Estimate FCF
        Tax Rate    35%
DL: If you have difficulty estimating company's tax rate you may assume statutory tax rate of 35% before 2018 and 21% thereafte
            Yea
            Year 1    Year 2    Year 3    Year 4    Year 5
        EBIT         $8,823    $9,070    $7,324    $10,356
        EBIT(1-T) = NOPAT        $5,735    $7,165    $5,786    $8,181
        Plus: Depreciation Expense        $1,482    $1,868    $1,751    $1,618
        Less: CAPEX        $513    $1,703    $682    ($371)
        Less: Working Capital Investment        $78    ($412)    ($28)    ($240)
        Firm Free Cash Flow        $6,625    $7,741    $6,884    $10,410
Based on historical financial data, find FCF for the company of your choice.
1. In week 1 you were asked to choose a publicly traded company and submit its name for instructor's approval. Assuming
2. Download annual financial data for the last 5 years from Edgar Website (forms 10-K). Attention: if your company became public less than 5 years ago, you will also have to download data from its IPO filing prospectus (form 424B4). Template below is approximate, you may modify it (change, delete, or add rows), to suit your company
3. Using approach similar to the one described in this module's Excel file you will have to calculate the FCF for the company of your choice
If you have difficulty estimating company's tax rate you may assume statutory tax rate of 35% for years before 2018.
For submission, Excel file is enough
http:
www.jgbm.org/page/1%20Howard%20Keen.pdf

Multiples Valuation Template
            (in millions)    (in millions)    (in millions)    (in millions)
        MY COMPANY IN MODULE 1: MCD (MCDONALD CORP)
                                Solution Legend
                                Downloaded data
                                Formula/Calculation/Analysis required
        Peer Companies:    Peer 1    Peer 2    Peer 3    Your Company (MCD)
        Ticke
        PERIOD ENDING
        From Income Statement and Balance Sheet
        Balance Sheet
        Insert Relevant Items here
        Income Statement
        Insert Relevant Items here
        Calculated Multiples
        EV/EBITDA Multiple
        P/E Multiple
        Market to Book Multiple
        Price/Revenue Multiple
        Your Company Valuation (MCD)
                            Your Company (MCD)
        Enterprise Value based on EV/EBITDA multiple
        Plus: Your Company Cash                    $0
        Less: Your Company Interest-bearing debt                    $0
        Total Equity value based on EBITDA
        Shares Outstanding (millions)                    -
        Equity value per share
        Equity value per share based on P/E multiple
        Total Equity value based on P/E multiple
        Plus: Your Company Interest-bearing debt                    $0
        Less: Your Company Cash and equivalents                    $0
        Enterprise value based on P/E multiple
        Equity value per share based on MTB Ratio
        Total Equity value based on MTB ratio
        Plus: Your Company Interest-bearing debt                    $0
        Less: Your Company Cash and equivalents                    $0
        Enterprise value based on MTB ratio
        Equity value per share based on P/Revenue ratio
        Total Equity value based on P/Revenue ratio
        Plus: Your Company Interest-bearing debt                    $0
        Less: Your Company Cash and equivalents                    $0
        Enterprise value based on P/Revenue ratio
This exam requires you, among other things, to estimate the stock price for Virgin America (Ticker: VA), and provide the analysis as requested. You will need to use “Sources of Financial Data” listed in Course Content to obtain the necessary financial info/statements for Virgin America Inc., to identify its peer companies and to obtain pricing and financial information for them.
 
A. Choose several peer companies for Virgin America Inc. and justify your choice. Choose several valuation multiples and using comparable ratios of peer companies (as we did in Project 2 and discussed in Conferences) and Virgin America Inc. financial information from prospectus, estimate the company’s equity value on April 3, 2016. It is required for this question to list your major assumptions and properly reference sources of information that you used in your calculations.
Using the same company as in Module 1 assignment and its peers, find multiple ratios of peer companies and then estimate your company’s equity value and enterprise value.

This Assignment uses the same company as in Modules 2 and 3
1. Choose peer companies for your company and download their relevant financial data. You don't have to download entire financial statements — only the items used in multiples valuation
2. Using approach similar to the one described in this module's Excel file, find valuation multiples (ratios) of peer companies.
3. Estimate your company's equity value and enterprise value
I suggest starting from the "Multiples Valuation Template" in Module 4 Excel file and adjust it accordingly
Answered Same DayApr 15, 2022

Solution

Prince answered on Apr 16 2022
10 Votes
Multiples Valuation Template
            (in millions)    (in millions)    (in millions)    (in millions)
        MY COMPANY IN MODULE 1: MCD (MCDONALD CORP)
                                Solution Legend
                                Downloaded data
                                Formula/Calculation/Analysis required
        Peer Companies:    Restaurant Brands International Inc    Yum! Brands, Inc.     Wendy's    Your Company (MCD)
        Ticker    QSR    (YUM)    WEN    MCD
        PERIOD ENDING    30-Dec-20    30-Dec-20    30-Dec-20    30-Dec-20
        From Income Statement and Balance Sheet
        Balance Sheet
        Asset
        Cash    852.54    730.00    306.99    3,449.10
        Total Cu
ent Assets    2,264.00    1,689.00    687.62    6,243.20
        Total Assets    22,777.00    5,852.00    5,040.01    52,626.80
        Liabilities
        Non-Cu
ent Liabilities
        Interest-bearing debt    10,130.04    10,272.00    2,218.16    35,196.80
        Cu
ent Liabilities
        Interest-bearing debt    48.63    453.00    28.96    2,243.60
        Total Equity    3,721.00    - 7,891.00    549.60    - 7,824.90
        Market Cap (as on Reporting Date)    18,840.78    32,853.00    4,924.93    157,696.82
        Price (in $ as on Reporting Date)    61.83    109.51    21.96    211.56
        Ordinary Shares Number (as on Reporting Date)    304.72    300.00    224.27    745.40
        Enterprise Value    28,166.90    42,848.00    6,865.06    191,688.12
        Income Statement
        Revenue    4,968.00    5,652.00    1,733.83    19,207.80
        Cost of Revenue    2,138.00    2,965.00    1,112.34    9,455.70
        Gross Profit    2,830.00    2,687.00    621.48    9,752.10
        Operating...
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here