wacc and fcf in excel

angel91
class10_23.xlsm
Home>Business & Finance homework help>wacc and fcf in excel

Read First

Note that the statement of free cash flows looks slightly different. Do your best to estimate FCF, remembering that we want to come up with cash available to capital providers [something like NI + Net non-cash charges + After-tax interest - working capital increases (current assets and current liabilities) - capital expenditures].

Financial data

Note: All figures in thousands except stock price.
Statement of Cash Flows Income Statement Balance Sheet
31-Dec-14 31-Dec-14 31-Dec-14 31-Dec-13
Net Income $ 445,374 Total Revenue $ 4,108,269 Assets
Cost of Revenue $ 2,990,513 Current Assets
Operating Activities, Cash Flows Provided By or Used In Gross Profit $ 1,117,756 Cash And Cash Equivalents $ 419,465 $ 323,203
Depreciation $ 110,474 Short Term Investments $ 338,592 $ 254,971
Adjustments To Net Income $ 61,191 Operating Expenses Net Receivables $ 51,327 $ 40,885
Changes In Accounts Receivables $ (10,966) Research & Development -   Inventory $ 15,332 $ 13,044
Changes In Liabilities $ 42,032 Selling General and Administrative $ 273,897 Other Current Assets $ 34,795 $ 34,204
Changes In Inventories $ (2,307) Non Recurring $ 15,609 Total Current Assets $ 859,511 $ 666,307
Changes In Other Operating Activities $ 36,269 Others $ 110,474
Total Cash Flow From Operating Activities $ 682,067 Total Operating Expenses -   Long Term Investments $ 496,106 $ 313,863
Operating Income or Loss $ 710,800 Property Plant and Equipment $ 1,106,984 $ 963,238
Investing Activities, Cash Flows Provided By or Used In Goodwill $ 21,939 $ 21,939
Capital Expenditures $ (252,590) Income from Continuing Operations Intangible Assets -   -  
Investments $ (266,254) Total Other Income/Expenses Net $ (6,976) Accumulated Amortization -   -  
Other Cash flows from Investing Activities -   Earnings Before Interest And Taxes $ 714,303 Other Assets $ 42,777 $ 43,933
Total Cash Flows From Investing Activities $ (518,844) Interest Expense -   Deferred Long Term Asset Charges -   -  
Income Before Tax $ 714,303 Total Assets $ 2,527,317 $ 2,009,280
Financing Activities, Cash Flows Provided By or Used In Provision for income taxes $ 268,929
Dividends Paid -   Minority Interest -   Liabilities
Sale Purchase of Stock $ (88,404) Net Income From Continuing Ops $ 445,374 Current Liabilities
Net Borrowings -   Accounts Payable $ 245,710 $ 199,228
Other Cash Flows from Financing Activities -   Net Income $ 445,374 Short/Current Long Term Debt -   -  
Total Cash Flows From Financing Activities $ (66,737) Preferred Stock And Other Adjustments -   Other Current Liabilities -   -  
Effect Of Exchange Rate Changes $ (224) Total Current Liabilities $ 245,710 $ 199,228
Net Income Applicable To Common Shares $ 445,374 Long Term Debt -   -  
Change In Cash and Cash Equivalents $ 96,262 Other Liabilities $ 28,263 $ 23,591
Deferred Long Term Liability Charges $ 240,975 $ 248,173
Minority Interest -   -  
Stock price 684.51 Negative Goodwill -   -  
Shares outstanding 31,038 Total Liabilities $ 514,948 $ 470,992
Stockholders' Equity
Misc Stocks Options Warrants -   -  
Redeemable Preferred Stock -   -  
Preferred Stock -   -  
Common Stock $ 354 $ 352
Retained Earnings $ 1,722,271 $ 1,276,897
Treasury Stock $ (748,759) $ (660,421)
Capital Surplus $ 1,038,932 $ 919,840
Other Stockholder Equity $ (429) $ 1,620
Total Stockholder Equity $ 2,012,369 $ 1,538,288
Net Tangible Assets $ 1,990,430 $ 1,516,349

WACC

Calculating the WACC, Dec. 2014
Cost of equity, rE
Cost of debt, rD
Dec. 2014 equity value, E
Dec. 2014 debt value, D
Total: Equity + Debt, E+D
Percentage of equity, E/(E+D)
Percentage of debt, D/(E+D)
Tax rate, TC
WACC

Valuing

VALUING CMG
Year 2014 FCF
Anticipated FCF growth 1
Terminal FCF growth
WACC
Enterprise value
Initial cash and marketable securities
Asset value
Debt value
Equity value
Number of shares
Per-share valuation

Chipotle has no dividend history and (in this sheet) we do not have a long time series of financial information to judge FCF growth. Let's assume that CMG will continue to grow very fast for 8 years (i.e., t=1 to t=8) at 9%, but that will drop off to a long run growth rate in FCF of 2%.