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