FM Core 1 Day v24.0

Financial Modeling
Constructing and Utilizing a Financial Model
We Unlock Career Potential

New York | Chicago | San Francisco | Charlotte | New Delhi | London | Hong Kong
© 1999-2024 Training The Street LLC

All rights reserved
www.trainingthestreet.com
All rights reserved.
Training The Street LLC (“TTS”) owns all rights, including copyright, in this
publication. This publication may not be reproduced or redistributed, in whole or in
part, in any format, or by any means without TTS’s prior written consent. No TTS
seminar, workshop, or other instructional activity may be recorded or transmitted in
any format or by any means without TTS’s prior written consent.
We Unlock Career Potential
www.trainingthestreet.com
2
About Training The Street
Founded in 1999, Training The Street is a leading financial learning services Core Training Capabilities
company, successfully delivering targeted and customized training courses to Fundamental Content Sector Capabilities Training
corporate and educational clients. Accounting Commercial Real Estate
Corporate Valuation FIG Analysis

(Banks & Insurance)
Our client list includes over 250 institutions representing the world’s leading banks, Financial Modeling
Financial & Corporate
accounting firms, private equity firms, consulting firms, Fortune 100 companies, M&A Modeling Restructuring
LBO Modeling Oil & Gas
and the most respected universities globally. Capital Markets Project Finance
(DCM / ECM Origination)
Our program design and industry expertise includes: Global Markets, Investment Financial Products / Global
Private Equity Investing &
Analysis
Banking, Operations, Consulting, Risk, Wealth Management, and Sales & Trading. Markets (S&T)
Infrastructure
Portfolio / Investments
Analysis Investment and Wealth
We focus on key service elements to unlock career potential: Excel Best Practices
Management
Software (SaaS) Analysis

Specialist / Functional
Venture Capital
Capabilities
Corporate Credit Analysis Renewables
Cash Mgt & Treasury Services

Data Sciences
People Pedagogy Client Partnership Turnkey Solution ESG
Applied Excel
Investment Banking Overview
The largest team of A learner-centric approach Providing an integrated Low-risk proven training Python
industry talent that brings a that adapts to individual learning journey starting at solutions that effectively Investment Authorities
SQL
passion for learning and a needs, encourages the undergraduate level deliver successful learning Private Co. Analysis
Power BI
commitment to service. practice, builds confidence, through the finance outcomes for all of our PowerPoint
and ultimately returns professional career clients and students. VBA
Data Storytelling
work-ready employees. progression. Google Sheets
FP&A
Model Building Solutions
3
Overview
• Overview of Financial Modeling
• Building a Three Statement Model
• The Finished Product
• Parting Thoughts
• Appendices: Modeling Step-by-Step Guide

4
Overview of Financial Modeling
Section 1
5
What is a Financial Model?
• Simplified representation of financial performance
̶ Both past and future
• Blend of skills
̶ Excel
̶ Industry and company knowledge
̶ Accounting
̶ Financial analysis (e.g., ratios)
̶ Model management

6
A Good Model Is...
• Effective
̶ Built for the task
̶ Provides all required outputs
̶ Justifiable assumptions
• Flexible
̶ Modular
̶ Scenarios and sensitivities
• User-friendly
̶ Well-presented
̶ Easy to audit
̶ Easy to manage

7
Financial Modeling Essentials
• Model layout makes all the difference…
What is the
purpose of the
model?
Vertical / Tower Model Horizontal / Matrix Model
• Think modular!
• Income statement: sign convention (positive or positive / negative)
• Level of aggregation (income statement and balance sheet layout)
• Length of forecast
• Model with iterations switched off in Excel Options

8
Income Statement Layout
Examples of alternative income statement layouts
Sales 2,500.0
Sales 2,500.0 COGS (1,750.0)
COGS (1,750.0) Sales 2,500.0 Depreciation (25.0)
Gross profit 750.0 COGS (1,750.0) Gross profit 750.0
SG&A (365.0)
SG&A (375.0) EBITDA 410.0 SG&A (365.0)
EBIT 375.0 Amortization (10.0)
Depreciation (25.0) EBIT 375.0
Depreciation 25.0 Amortization (10.0)
Amortization 10.0 EBIT 375.0 Depreciation 25.0
EBITDA 410.0 Amortization 10.0
Non-recurring items (12.3) EBITDA 410.0
Non-recurring items (12.3) Interest income 10.0
Interest income 10.0 Interest expense (36.0) Non-recurring items (12.3)
Interest expense (36.0) Profit before tax 336.7 Interest income 10.0
Profit before tax 336.7 Interest expense (36.0)
Tax expense (117.8) Profit before tax 336.7
Tax expense (117.8) Net income 218.9
Net income 218.9 Tax expense (117.8)
Net income 218.9
9
Modular Approach
Working Income Debt &

Capital Statement Interest
PP&E, Cash Flow Shareholders’

Depreciation Statement Equity
Intangibles, Balance Other Long

Amortization Sheet Term Items

10
Module Structure
Typical Components of a Module
1 4
Historicals Projections
2 3
Forecast
Historical Ratios
Assumptions

11
Key Learning Insight
Formatting Best Practice Color Codes:

Inputs = Blue
Cell Links = Green
Examples Formulas = Black
Warning = Red
Typically, there isn’t enough

$ signs on first row,
information to reconcile historical
but not subsequent rows
balance sheet accounts.
for like terms
Historical Year Ending December 31, Projected Year Ending December 31,
Hist 1 Hist 2 Hist 3 Proj 1 Proj 2 Proj 3 Proj 4 Proj 5
Sales $13,198.0 $14,197.0 $14,792.0 $15,235.8 $15,692.8 $16,163.6 $16,648.5 $17,148.0
Beginning PP&E, net $3,856.0 $4,011.3 $4,152.5 $4,298.0 $4,447.9

Capital expenditures DO NOT BACK RECONCILE 685.6 706.2 727.4 749.2 771.7 Solid lines
(Depreciation expense) (530.3) (564.9) (581.9) (599.3) (617.3) used for
Ending PP&E, net Right border $3,281.0 $3,782.0 $3,856.0 $4,011.3 $4,152.5 $4,298.0 $4,447.9 $4,602.2 subtotals
separates and totals
Ratios and assumptions historicals from
Capital expenditures projections $594.0 $533.0 $637.0 $685.6 $706.2 $727.4 $749.2 $771.7
Capital expenditures as % of sales 4.5% 3.8% 4.3% 4.5% 4.5% 4.5% 4.5% 4.5%
Depreciation expense $367.0 $427.0 $453.0 $530.3 $564.9 $581.9 $599.3 $617.3 Step function
Depreciation as % of capex 61.8% 80.1% 71.1% 77.3% 80.0% 80.0% 80.0% 80.0%
Depreciation as % of PP&E, net 11.2% 11.3% 11.7% 13.2% 13.6% 13.5% 13.5% 13.4%
$ sign repeated because $ data is

separated by an unlike term (i.e., data
transitioned from a number, to a
percentage, back to a number).

12
Building a Three Statement Model
Section 2
13
Modeling Steps
1. Income Statement
̶ Do not forecast depreciation or amortization
̶ Do not forecast interest income or interest expense
2. Balance Sheet
̶ Do not forecast cash
̶ Flatline forecast debt
3. Cash Flow Statement
4. Balance the Balance Sheet
5. Debt Schedule
6. Interest
7. Ratios

14
Step 1: Income Statement
1 4
Projections
Historicals • Build projections by linking to the assumptions
• Select your desired income statement
• Work top down
structure (line items)
Sales = Salest-1 * (1 + growth assumption)
• Input historicals (from published income statement) Cost of sales = (1 – gross margin assumption) * current sales
• Normalize profit figures, as necessary
(e.g., EBITDA, EBIT, Net income, EPS, etc.) • Do not complete depreciation, amortization, and interest
income / expense line items yet
2 3
Historical Ratios
• Calculate ratios to gauge the company’s Forecast Assumptions
historical performance • Derive income statement assumptions from company
Examples: guidance, research reports, or historical performance
Sales growth % = Current / previous – 1
• Assumptions should be explained/backed up
Gross margin = Gross profit / sales
Effective tax rate = Tax expense / pre-tax income

15
Step 2: Balance Sheet
Overview
1 2
Input Balance Sheet Historicals
• Select appropriate balance sheet structure (line items)
Complete the
• Input historicals (from published balance sheet) Supporting
• Consider combining or breaking out balance sheet line items, Schedules
as appropriate
A. Operating Working Capital

B. Property, Plant, & Equipment
C. Intangible Assets
D. Other Long-Term Assets and Liabilities
E. Shareholders’ Equity and Shares Outstanding
F. Flatline the Debt Items

16
Step 2: Balance Sheet Operating working capital represents the

company’s investment in short-term operating
net assets (e.g., accounts receivables,
A) Operating Working Capital (OWC) inventories, prepaid items, accounts payables,
accrued expenses, etc.)
1 4
• Link operating working capital assets and liabilities from
• Key Operating Working Capital Formulas:
balance sheet into working capital schedule
Operating current assets – Receivables = Receivable days / 365 * Sales
Operating current liabilities = Inventory = Inventory days / 365 * COGS
Operating working capital Payables = Payable days / 365 * COGS
• Calculate the cash flow impact of operating working capital • Link working capital balances into the balance sheet and
(Assets have an inverse relationship with cash and liabilities working capital cash flow impact into the cash flow
have a direct relationship with cash) statement
2 3
Historical Ratios
• Calculate Days ratios:
Forecast Assumptions
Receivable days = Accounts receivable / sales * 365
• Derive OWC assumptions from company guidance,
Inventory days = Inventory / COGS * 365
Payable days = Accounts payable / COGS * 365 research reports or historical performance
• Other operating items can be calculated as % of sales or
COGS, depending on the nature of the item

17
Step 2: Balance Sheet Property, plant, and equipment represents the

company’s investment in long-term physical
assets (e.g., land, buildings, delivery trucks,
B) Property, Plant, & Equipment (PP&E) manufacturing equipment, etc.)
1 4
• Input historical capital expenditures
• Forecast capital expenditures and depreciation using the
(usually found in the published cash flow statement)
assumptions derived in the previous step
• Input historical depreciation • Link the resulting numbers back to the financial statements:
(usually found in the published cash flow statement) ending PP&E to the balance sheet; depreciation to the
• Link PP&E balance from the balance sheet income statement; depreciation and CapEx to the cash flow
statement
Do not try and reconcile historical PP&E opening and closing balances
2 3
Historical Ratios • Derive CapEx and depreciation assumptions from company
• Calculate historical ratios: guidance, research reports or historical performance
CapEx / sales • Capital expenditure is often forecast as % of sales.
Depreciation / CapEx • Depreciation is often forecast as % of capital expenditure or
Depreciation / beginning PP&E % of beginning PP&E

18
Step 2: Balance Sheet Intangible assets have no physical form;

Intangibles are amortized when they have a
definite life (e.g., licenses; customer lists;
C) Intangible Assets patents, etc.)
1 4
• Input historical intangible assets additions
• Forecast additions and amortization using the assumptions
(usually found in the published cash flow statement)
derived in the previous step
• Input historical amortization • Link the resulting numbers back to the financial statements:
(usually found in the published cash flow statement) ending intangibles to balance sheet; amortization to income
• Link intangibles balance from the balance sheet statement; additions and amortization to the cash flow
statement
Do not try and reconcile historical intangible opening and closing balances
2 3
Historical Ratios • Derive additions and amortization assumptions from
company guidance, research reports, or historical
• Calculate historical ratios: performance
Intangibles additions / sales
• Some companies disclose forecast amortization expense
Amortization / beginning intangibles guidance in their published filings

19
Step 2: Balance Sheet Other long-term items may include: deferred

tax assets / liabilities; pension assets /
liabilities; equity investments; etc.
D) Other Long-Term Assets and Liabilities
1 4
• Link the other long-term assets and liabilities from the • Forecast the other long-term assets and liabilities using the
balance sheet into the other long-term items schedule assumptions derived in the previous step
• Calculate the cash flow impact of the other long-term assets • Link the resulting numbers back to the financial statements:
and other long-term liabilities balance sheet and cash flow statement
2 3
• Derive your assumptions from company guidance, research
Historical Ratios reports or historical performance
• Decide what ratios are appropriate for each of the other • Some of the projections may be flatlined due to the difficultly
long-term assets and liabilities, depending on the specific and reliability of forecasting specific items
nature of each item • Using % of sales as a basis may be used as a generic
assumption (the rationale is that the asset / liability needs to
increase to support the business growth)

20
Step 2: Balance Sheet What causes equity to change over time?

Net income; Dividends; Share issuance;
Share repurchases
E) Shareholders’ Equity and Shares Outstanding BASE Analysis: Beginning Balance +
Additions – Subtractions = Ending Balance
1 4
• Link shareholders’ equity from the balance sheet into the
ending balance of the shareholders’ equity schedule • Forecast $ amounts spent on share buybacks and dividends
and $ amounts received as a result of share issuance
• Input historical dividends (usually found in the published • Complete shareholders’ equity roll forward BASE analysis
cash flow statement, or statement of shareholders’ equity)
• Link ending shareholders’ equity to the balance sheet
• Input historical share repurchase and share issuance data • Link net income, dividends, share issuance, and share
(usually found in the published cash flow statement, or in repurchases to the cash flow statement
the notes to the financial statements)
2 3
• Derive assumptions for share repurchases (e.g., number of
Historical Ratios shares repurchased or $ amounts), share issuance
• Calculate the historical Dividend payout ratio: (e.g., number of shares issued or $ amounts), and dividends
Dividend payout ratio = Dividends paid / Net income paid (e.g., using the payout ratio)
• Derive your assumptions from company guidance, research
reports, or historical performance

21
Step 2: Balance Sheet Shares Outstanding

Weighted Average Shares Outstanding
(WASO) is used to calculate Earnings per
E) Shareholders’ Equity and Shares Outstanding Share (EPS); Shares outstanding is used to
calculate market capitalization
1 4
• Input the basic and diluted number of shares outstanding
as of the balance sheet date • Forecast the end-of-year basic and diluted
(usually found in the notes to the financial statements) number of shares outstanding
• Forecast the average basic and diluted
• Input the basic and diluted average number of number of shares outstanding;
shares outstanding link them to the income statement
(usually found in the EPS note or in the income statement)
2 3
Historical Ratios • Derive the number of shares issued and/or repurchased,
using the data in the shareholders’ equity schedule
• Calculate the impact of dilutive securities (e.g., stock
• Derive a dilution assumption either as an absolute amount
options, convertible debt) by calculating the difference
or using the historical calculation from the previous step
between historical diluted and basic WASO
• Check your assumptions using company guidance,
research reports or historical performance

22
Step 2: Balance Sheet Temporarily flatlining debt at this stage of the

process speeds up the balancing of the model
and allows early error checking
F) Flatline the Debt Items
Accounts
C h payable $1,189.0
$460 0 $1,402.0
$281 0 $1,432.0
$273 0 $1,432.6 $1,475.6 $1,519.8 $1,565.4 $1,612.4
Accrued liabilities 1,129.0 1,301.0 1,375.0 1,416.9 1,459.4 1,503.2 1,548.3 1,594.8
Total current liabilities 2,318.0 2,703.0 2,807.0 2,849.5 2,935.0 3,023.0 3,113.7 3,207.1
Revolver 234.0 1,065.0 739.0 739.0 739.0 739.0 739.0 739.0

Long-term debt 5,798.0 6,837.0 6,619.0 6,619.0 6,619.0 6,619.0 6,619.0 6,619.0
Deferred income taxes 643.0 523.0 928.0 928.0 928.0 928.0 928.0 928.0
Other long-term liabilities 1,152.0 1,576.0 774.0 774.0 774.0 774.0 774.0 774.0
Total liabilities 10,145.0 12,704.0 11,867.0 11,909.5 11,995.0 12,083.0 12,173.7 12,267.1
Total stockholder's equity 1,798.0 2,465.0 3,607.0 4,540.4 5,484.8 6,433.4 7,385.9 8,341.9
Total liabilities and equity $11,943.0 $15,169.0 $15,474.0 $16,449.9 $17,479.8 $18,516.4 $19,559.6 $20,609.1
Parity check (A = L+E) 0.000 0.000 0.000 987.235
Flatline Debt
1,791.614 2,632.074 3,510.361 4,428.305
• Temporarily flatline all debt items on the balance sheet

• Change the font color to red as a reminder that these
numbers will need to be completed later in the process
• Temporarily flatlining debt enables the balance sheet to
balance before completing the debt schedule

23
Step 3: Cash Flow Statement
Four Rules of Cash
The Cash Flow Statement is derived by measuring the change in each
balance sheet item (except for cash) using the four rules below:
 Assets  Cash 
Inverse Relationship
 Assets  Cash 
 L&E*  Cash 
Direct Relationship
 L&E*  Cash 
* L&E = Liabilities & Equity

24
Cash Flow Reconciliations
PP&E, net
Balance Sheet Cash Flow
Beginning Bal. Ending Bal. Difference Depr. CapEx Total
$26,283.000 26,498.000 ($215.000) $2,535.000 (2,750.000) ($215.000)
Definite life intangibles

Beginning Bal. Ending Bal. Difference Amort. Additions Total
$53.000 41.500 $11.500 $11.500 0.000 $11.500
Cash Flow from Debt
Long-term debt • As debt has been temporarily flatlined in the model, the
cash flow from debt will be zero at this stage
Beginning Bal. Ending Bal. Difference Issuance Payment Total • Forecast debt numbers and resulting cash flows will be
linked to the balance sheet later
$11,499.000 13,905.000 $2,406.000 $2,500.000 (94.000) $2,406.000
Total stockholders' equity

Beginning Bal. Ending Bal. Difference NI Dividends StockComp Buyback Options Total
$11,833.000 13,324.602 $1,491.602 $3,660.318 (1,354.318) 147.000 (1,061.694) 100.296 $1,491.602

25
Calculate Net Cash Flow
Cash Flow from

Operating Activities
Cash Flow from

NET CASH FLOW
Investing Activities
Cash Flow from

Financing Activities Net Cash Flow
+ Previous Year’s Cash Balance
= Ending Cash Balance

26
Step 4: Balance the Balance Sheet
1
Calculate Ending Cash Balance
Derive the value of cash at the end of the year in
the cash flow statement Projected Year Ending December 31,
Proj 1 Proj 2 Proj 3 Proj 4 Proj 5
Net change in cash (23.0) 0.0 389.1 495.6 634.9

Beginning cash balance 273.0 250.0 250.0 639.1 1,134.7
Ending cash balance $250.0 $250.0 $639.1 $1,134.7 $1,769.6
2
Link Cash Balance to Balance Sheet
Link the ending cash balance from the cash flow
statement to the balance sheet
Cash $460.0 $281.0 $273.0 $250.0 $250.0 $639.1 $1,134.7 $1,769.6
3
The Balance Sheet Balances!
The balance sheet should balance at this stage;
if it does not, stop and go back to fix the error(s) Hist 1 Hist 2 Hist 3 Proj 1 Proj 2 Proj 3 Proj 4 Proj 5
Parity check (A = L+E) 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000

27
Step 5: Debt Schedule
Cash Flow Available for Financing Activities
Cash Flow from Operating Activities Cash Flow from Investing Activities
+ Net income - Capital expenditures
+/- Non-cash expenses/income + Proceeds from asset sales
+ Change in operating working capital items - Additions to intangibles
+ Change in other LT assets/liabilities - Acquisition of businesses
Cash Flow Available for

Financing Activities
Cash Flow for Financing Activities

Apply available cash flow to:
Repay debt, pay dividends and repurchase equity
Fund cash flow deficit with:
Issuance of short-term or long-term debt, or issuance of equity

28
Steps to Complete the Debt Schedule
1 2 3
Long-Term Debt
Cash Available for Debt Repayment
• Link historical ending long-term debt
+ Cash flow available for financing activities Cash Available
into long-term debt ending balances
- Dividends for Revolver Repayment
• Input debt issuance amount
- Share repurchases in the long-term debt schedule + Cash available for debt repayment
+ Share issuance • Find required debt repayments + Long-term debt issued
+ Beginning cash (found in the published filings) - Long-term debt required repayment
- Minimum cash required and input them in the long-term debt schedule = Cash Available for Revolver Repayment
= Cash Available for Debt Repayment • Complete the long-term debt calculation

29
4
Set Up the Revolver Calculation
Modeling the Revolver

– Link the beginning balance
– Compare the beginning balance to the cash available for revolver repayment
• Use the –MIN formula
– Calculate the projected ending balance of the revolver
Cash sweep (using -MIN logic)

30
5 6
Link Revolver to the Financial Statements Link Long-Term Debt to the Financial Statements
• Link the revolver ending balance to the balance sheet • Link the long-term debt ending balance to the balance sheet
• Link the revolver issuance / (repayment) • Link the long-term debt issuance and repayment
to the cash flow statement to the cash flow statement
• Check the balance sheet balances; • Check the balance sheet balances;
if it does not, troubleshoot before proceeding to the next step if it does not, troubleshoot before you proceed to the next step

31
Step 6: Interest Expense and Interest Income
Calculate Interest Expense and Interest Income
1 2
Interest Income
Interest Expense
• Calculate average balances for cash
• Calculate average balances for both revolver and
long-term debt =AVERAGE(Beginning Balance, Ending Balance)
=AVERAGE(Beginning Balance, Ending Balance) • Make reasonable interest rate assumptions
• Make reasonable interest rate assumptions • Forecast interest income on cash
• Forecast interest expense for revolver and long-term debt Some models show interest income with a negative sign to show
that it is netted off against interest expense (to compute net interest
• Calculate total interest expense
expense).
3
Link Interest to the Income Statement
Interest income and expense cause
• Link interest income and expense to the income statement
circularities when the calculation refers to the
• The interest calculations make the model circular; ending amount of debt and/or cash.
This is discussed in subsequent sections of this guide
Some finance professionals calculate interest
Ensure the iterative calculations tool (in Excel options) is active;
on beginning debt and cash balances to avoid
This enables Excel to calculate circular formulas circular references.

32
Step 7: Ratios
1 2
Earnings Growth & Profitability Asset Efficiency
What are the company’s growth prospects? How effectively is the company using its operating assets?
How profitable is the company?
Asset Efficiency
Growth Rates Net operating assets / sales
Growth rate = Current / previous – 1 PP&E / sales
Profit Margins OWC / sales
EBIT margin = EBIT / sales Asset Turnover
EBITDA margin = EBITDA / sales Sales / assets (Net operating assets, PP&E, OWC)
Net margin = Net income / sales
3 4 Credit Ratios
Returns
What is the yield generated on the company’s net operating How does the company finance its assets?
assets? What is the yield to the owners of the company? What is the degree of financial leverage?
Return on Invested Capital (ROIC) Leverage
NOPAT / Net operating assets Debt / capitalization
Net operating assets = OWC + PP&E + other net operating assets Capitalization = Equity + debt + other capital items
(Net operating assets is also known as Invested capital) Debt / EBITDA
Coverage
Return on Equity (ROE) EBITDA / interest expense
Net income / equity (EBITDA – CapEx) / interest expense

33
The Finished Product
Section 3
34
Dealing with Circular Models Embedded errors “blow up” models because
Excel can “undo” an input but not a calculation.
Error is then “trapped” in a circle.
Error Types
– #VALUE: a cell reference to a letter rather
than a number
– #REF: a reference to a cell that no longer
exists. Usually caused by deleting a cell
– #DIV: an equation divides by a zero or a
blank cell
– #NAME: a reference to a name that does not
exist

35
Breaking Circularity via Jump Starting
• Most common method for fixing a model
• Effective when source of error is known
• Concept: Stop the error from flowing through the IS
Int. Int.
Exp. Exp.
Avg. Net Avg. Net
Debt Inc. Debt Inc.
Free Free
CF CF

Windows Excel Mac Excel
File, Options, Formulas (Alt F T F, Alt+I) Cmd+, type “Calculation” in search box
then check the “Limit Iteration” box

36
How to Jump Start a Model Build a CIRC Switch to turn circularity “ON” or
“OFF” by replacing interest expense/income
formulas with:
1) Fix the Error • IF(CIRC =0,0,debt_interest!L38)
• IF(CIRC=0,0,debt_interest!L42)
2) Copy interest expense/income formulas on the IS
3) Paste the formulas off to the right, beyond the last column
4) Delete the original interest expense/income formulas

̶ This effectively “hard codes” zeros, breaking the circularity
5) Copy interest expense/income formulas back into IS

37
Balancing Your Model

38
What is a Balanced Model?
• Balance Sheet and Cash Flow harmony
̶ Reconcile change in each Balance Sheet line item with flow of funds on the Cash Flow
Statement
Ending Ending
Balance on
Balance Sheet
Year 0
Balance on
Balance Sheet
Year 1
= Cash Flow
Year 1
Cash Flow
BS Yr 0 BS Yr 1 Difference Yr 1
Cash 50.000 50.000 0.000 = 0.000 

Accounts receivables, net 413.471 412.231 1.240 = 1.240 
Inventories 26.320 25.639 0.681 = 0.681 
Other current assets 138.844 138.427 0.417 = 0.417 
PP&E, net 1,495.856 1,498.933 (3.077) = (3.077)
Definite life intangibles 35.150 25.850 9.300 = 9.300 
Indefinite life intangibles 71.711 71.711 0.000 = 0.000 
Goodwill 683.440 683.440 0.000 = 0.000 
Deferred income taxes 112.379 112.379 0.000 = 0.000 
Other long-term assets 278.523 278.523 0.000 = 0.000 

39
Balancing A Model: The Steps
• Balance historical values
• Quick sanity check on BS and CF
̶ Wrong signs on Cash Flow statement
̶ Unreasonable changes on Balance Sheet
• Divide error by 2
̶ Usually a sign error
• Look for consistent trend in the error
̶ Usually omission of an item
• Correctly linked to / from other schedules?
• Check total formulas and other calculations
• Reconcile the changes by hand

40
Why is this not “Deal Ready”?

41
Identify the Key Drivers • Be prepared to defend your drivers

• A good summary adds depth to cases,
pitches, and interviews
• Data tables help sensitize results and
anticipate questions
Key Performance Measure Common Driver(s)
Sales • Percent growth
• Price x Volume
• Market share x Market size
• Industry specific measures: sales per store or comp store sales growth in retail
Operating Expenses • Margin based on sales
(COGS, SG&A, etc.) • “Build-up” based on detailed numbers
Working Capital Items • Days (based on sales or COGS, as appropriate)
• Percent of sales or COGS
Capital Expenditures • Percent of sales
• Fixed dollar amount based on historical or expected levels
• “Build-up” based on detailed numbers
Depreciation • Percent of CapEx (vs. maintenance CapEx level), percent net PP&E or percent sales
• Based on estimated useful lives of fixed assets and CapEx
Dividends • Dividend payout ratio (based on net income)
• Dividend per share
• Fixed dollar amount based on historical or expected levels
Debt Repayment • Known maturities of long-term debt instruments
• “Sweep” with available free cash flow
Stock Buyback Program • $ repurchased: # of shares x estimated price per share (based on PE multiple)
• # repurchased: $ budgeted for repurchase / estimated price per share (based on PE multiple)
Other Key Items • Taxes: Effective tax rate or marginal tax rate with adjustments to taxable income
• Interest rates: Analysis of debt footnote or current market environment

42
Overview of Data Tables
Windows Excel
• Tool used for sensitivity analysis Data, What-If Analysis, Data Table (Alt A W T)
̶ Commonly used for presentations Mac Excel

5 Year Operating Income CAGR Cmd+Shift+? “Data Table”
Sales Growth Rate
3.5% 4.0% 4.5% 5.0% 5.5% 6.0% 6.5% 7.0%

38.0% 1.7% 2.2% 2.7% 3.2% 3.7% 4.2% 4.6% 5.1%
38.5% 2.7% 3.2% 3.7% 4.2% 4.6% 5.1% 5.6% 6.1%
Gross 39.0% 3.6% 4.1% 4.6% 5.1% 5.6% 6.1% 6.6% 7.0%
Margin 39.5% 4.5% 5.0% 5.5% 6.0% 6.5% 7.0% 7.5% 8.0%
40.0% 5.4% 5.9% 6.4% 6.9% 7.4% 7.9% 8.4% 8.8%
40.5% 6.2% 6.7% 7.2% 7.7% 8.2% 8.7% 9.2% 9.7%
41.0% 7.0% 7.5% 8.0% 8.5% 9.0% 9.5% 10.0% 10.5%
41.5% 7.8% 8.3% 8.8% 9.3% 9.8% 10.3% 10.8% 11.3%
42.0% 8.5% 9.0% 9.6% 10.1% 10.6% 11.1% 11.6% 12.1%
42.5% 9.3% 9.8% 10.3% 10.8% 11.3% 11.9% 12.4% 12.9%
• Data tables can slow your model down!

̶ Consider using “Automatic except for data tables” calculation type (or “Manual”)*
2007: Office button, Excel Options, Formulas (Alt F I F, Alt+D) Cmd+, Start Typing Calculations, Cmd+T
2010 & Newer: File tab, Options, Formulas (Alt F T F, Alt+D)
* Note: Press F9 (Cmd+= in Mac Excel) to calculate the data table when using “Automatic except tables” and “Manual” settings

43
One-Way Data Table
Windows Excel
Data, What-If Analysis, Data Table (Alt A W T)
Mac Excel
Cmd+Shift+? “Data Table”

44
Two-Way Data Table

Data, What-If Analysis, Data Table (Alt A W T) Cmd+Shift+? “Data Table”

45
Tweaking the Assumptions
• Assumptions and inputs:
̶ Reasonable?
̶ Defensible?
̶ Remember to note your sources
• Reality test your forecasts
̶ Check against other sources (e.g., industry data, third-party research, internal sources)
̶ Check short-term and long-term
• Scenarios and cases:
̶ Upside, management, and base cases
• Detailed revenue build-up

46
Creating a DCF Valuation
1) Reference in the projections from your model
2) Calculate the projected unlevered free cash flows
3) Estimate the terminal value
4) Estimate the discount rate
5) Calculate the present values
̶ Unlevered free cash flows
̶ Terminal value
TTS Connect
6) Calculate equity value The theory and mechanics of a
DCF are discussed during our
̶ Equity value per share Corporate Valuation Seminar at
Universities, in the Fundamentals
of Corporate Valuation book, and
7) Do some “sensitivity” analysis our Online Valuation Series
̶ Numerous assumptions
.

47
Sample DCF Valuation
Discounted Cash Flow Analysis for TTS Sample Company
Dollars in millions, except per share data
Fiscal Year Ending December 31, Fiscal Year Ending December 31, 5 Year
Hist 1 Hist 2 Hist 3 Proj 1 Proj 2 Proj 3 Proj 4 Proj 5 CAGR
Sales $4,172.6 $4,429.2 $4,836.0 $4,981.1 $5,130.5 $5,284.4 $5,442.9 $5,606.2 3.0%
EBITDA 969.4 1,071.4 1,176.1 1,245.3 1,282.6 1,321.1 1,360.7 1,401.6 3.6%
Less: depreciation (158.9) (171.2) (178.3) (180.0) (187.0) (193.6) (200.4) (207.4)
Less: amortization (21.6) (18.4) (17.9) (17.0) (16.5) (16.0) (15.5) (15.0)
EBIT 788.8 881.7 979.9 1,048.3 1,079.1 1,111.5 1,144.9 1,179.1 3.8%
Less: taxes @ 37.6% (296.6) (331.5) (368.4) (394.1) (405.8) (417.9) (430.5) (443.4)
Tax-effected EBIT 492.2 550.2 611.5 654.1 673.4 693.6 714.4 735.8
Plus: depreciation 180.0 187.0 193.6 200.4 207.4

Plus: amortization 17.0 16.5 16.0 15.5 15.0 Windows Excel
Less: capital expenditures (185.0) (189.8) (195.5) (201.4) (207.4)
Less: additions to definite life intangibles (15.0) (15.0) (15.0) (15.0) (15.0) Home, Format, Unhide, Sheet (Alt H O U H)
+/- Changes in working capital (19.8) (19.9) (20.5) (21.1) (21.7)
+/- Changes in other assets and liabilities 0.0 0.0 0.0 0.0 0.0
Unlevered free cash flow $631.4 $652.2 $672.2 $692.8 $714.1
Unlevered free cash flow growth rate 3.3% 3.1% 3.1% 3.1%
Mac Excel
Perpetuity Growth Method EBITDA Multiple Method • Ctrl+F2, Format Enter, Sheet
Enter, Unhide Enter or
Weighted average cost of capital: 8.0% Weighted average cost of capital: 8.0%
Net present value of free cash flow (b) $2,672.5 Net present value of free cash flow (b) $2,672.5
• Cmd+Shift+? “Sheet>Unhide”
Growth rate of FCF after 5 3.5% Exit multiple 12.0x

Terminal value $16,423.4 Terminal value $16,818.7
Present value of the terminal value (c) 11,177.5 Present value of the terminal value (c) 11,446.5
Enterprise Value $13,850.0 Enterprise Value $14,119.0

Less: debt, pref. stock, & minority interest (d) (1,761.9) Less: debt, pref. stock, & minority interest (d) (1,761.9)
Plus: cash & cash equivalents (d) 67.2 Plus: Cash & cash equivalents (d) 67.2
Equity Value $12,155.3 Equity Value $12,424.3
Diluted Shares: 241.587 Diluted Shares: 241.587
Equity Value Per Share (e) $50.31 Equity Value Per Share (e) $51.43

48
Parting Thoughts
Section 4

49
Using Your Model as a Template
• One size does not fit all
• Use extreme care when modifying a model:
1) Study the new company’s financials
2) Identify any new line items
3) Design the flow of funds
New line items to and from your core statements
4) Forecast each of the new line items Windows Excel
Formulas, Dependents (Alt M D)
 Separate schedule or just a simple formula? Data, Hide – Hide detail (Alt A H)
5) Hesitate before deleting anything!
Mac Excel
 Trace dependents Cmd+Shift+? “Auditing>Trace Dependents”
 Grouped rows Cmd+Shift+? “Row>Hide”

50
Appendix
51
Step-by-Step Guide
52
Step-by-Step Guide
1. Core Statements
2. Operating Working Capital Schedule
3. Depreciation Schedule
4. Amortization Schedule
5. Other Long-Term Items Schedule
6. Shareholders’ Equity Schedule
7. Balance the Balance Sheet
8. Debt Schedule
9. Interest

53
Step-by-Step Guide Tips
The number of each Historical Year Ending December 31, Projected Year Ending December 31,
Step X: A few general step will be included in
the blue dialogue box.
formatting principles to Currency symbol on first row Solid lines throughout the presentation are used
keep in mind: Sales $13,198.0
but not $14,197.0
subsequent rows for $14,792.0 $15,235.8 $15,692.8 $16,163.6 $16,648.5 $17,148.0
to indicate subtotals and totals. Add these as
like terms. appropriate, as they give the user a helpful
Beginning PP&E, net $3,856.0 $4,011.3of how
indicator $4,152.5 $4,298.0
the math works. $4,447.9
– Consistency is important Capital expenditures DO NOT BACK RECONCILE 685.6 706.2 727.4 749.2 771.7
where possible, however (Depreciation expense) Gray shading throughout (530.3) (564.9) (581.9) (599.3) (617.3)
there are exceptions. presentation is used to indicate
Ending PP&E, net $3,281.0 $3,782.0 $3,856.0 $4,011.3 $4,152.5 $4,298.0 $4,447.9 $4,602.2
that historical data is not needed.
– Pay special attention to $
signs, number of decimals, Ratios and assumptions
and colors. Capital expenditures $594.0 $533.0 $637.0 $685.6 $706.2 $727.4 $749.2 $771.7
– Decimals should generally
Depreciation expenseCurrency symbol is repeated because $367.0 $427.0 $453.0 $530.3 $564.9 $581.9 $599.3 $617.3
align for like terms. Depreciation as % ofdata
capex 61.8% 80.1% 71.1% 77.3% 80.0% 80.0% 80.0% 80.0%
was separated by an unlike term
– The decimals of unlike Depreciation as % of(i.e.,
PP&E, 11.2% 11.3% 11.7% 13.2% 13.6% 13.5% 13.5% 13.4%
datanet
transitioned from a number, to
terms need not necessarily a percentage, back to a number).
line up, though you should
do the best you can.
Dotted lines throughout the presentation are
used as teaching callouts; you do not need to
add these lines to your Excel spreadsheet.

54
Core Statements
Step-by-Step: Section 1

55
Illustrative Flow of Funds
Core Statements
Income
Statement
Cash Flow
Statement
Balance
Sheet

56
Set Up Income Statement (IS)
Step 1: Set up the
income statement
– Sources for appropriate

line items: financial
statements, management
projections and/or research
– Show more detailed line
items if it aids in your
analysis
– Personalize the income
statement to the company /
industry

57
Set Up Balance Sheet (BS)
Step 2: Set up the
balance sheet
– Sources for appropriate

line items: financial
statements, management
projections and/or research
– Make simplifications or
more detailed break-out of
line items as appropriate
(based on how you think
you’ll project them for
future periods)
– Personalize to the
company / industry

58
Set Up Cash Flow Statement (CF)
Step 3: Set up the cash
flow statement
– Historical entries are

usually not made (historical
cash flow statements are
often difficult to reconcile)
– Be sure to keep your
columns consistent with the
income statement & other
schedules (e.g., column K
is the same year on CF, IS,
BS statements)
– Use a consistent layout
because each line item will
be an imported reference
from another schedule

59
IS: Input Historical Data
Historical Year Ending December 31, Projected Year Ending December 31, 5 Year
Step 4: Input the Hist 1 Hist 2 Hist 3 Proj 1 Proj 2 Proj 3 Proj 4 Proj 5 CAGR
historical income
Sales Inputs are BLUE! $13,198.0 $14,197.0 $14,792.0
statement data Cost of sales (excluding depreciation) 7,266.0 8,005.0 8,531.0 Normalized cells are RED!
Gross profit 5,932.0 6,192.0 6,261.0
– Normalize the income
SG&A expenses (excluding amortization) 3,393.0 3,589.0 3,593.0
statement for any non- EBITDA
Add borders
2,539.0 2,603.0 2,668.0
recurring items; be sure to
source your assumptions Depreciation 367.0 427.0 453.0
– Calculate the historical Amortization 2.0 4.0 9.0
EBIT 2,170.0 2,172.0 2,206.0
ratios and appropriate
growth rates Interest expense 233.0 261.0 235.0
Calculations (BLACK) Formulas)
– Format the cells as you go Interest (income) Watch your signs! (11.0) (9.0) (7.0)
along Other non-operating (income) / expense 21.0 (19.0) 9.0
Pretax income 1,927.0 1,939.0 1,969.0
Income taxes 566.3 585.9 560.5

Normalized net income $1,360.7 $1,353.1 $1,408.5
Diluted weighted average shares (in millions) 364.000 360.000 365.000
Normalized diluted earnings per share $3.74 $3.76 $3.86
Ratios & assumptions Step

Sales growth rate 7.6% 4.2%
Gross margin 44.9% 43.6% 42.3%
Calculations: Use custom
SG&A expenses (as a % of sales) 25.7% 25.3% 24.3%
number format to align parenthesis
EBIT Margin 16.4% 15.3% 14.9%
0.0%_);(0.0%)
Other non-operating (income) / expense ($ amount) $21.0 ($19.0) $9.0
Effective tax rate 29.4% 30.2% 28.5%

60
IS: Project Sales
Step 5: Forecast net Hist 1 Hist 2 Hist 3 Proj 1 Proj 2 Proj 3 Proj 4 Proj 5 CAGR
sales for the projected
Sales $13,198.0 $14,197.0 $14,792.0 $15,235.8 $15,692.8 $16,163.6 $16,648.5 $17,148.0
years Cost of sales (excluding depreciation) 7,266.0 8,005.0 8,531.0
Gross profit 5,932.0 6,192.0 6,261.0
– Drivers: projected annual Forecasts
SG&A expenses (excluding amortization) 3,393.0 3,589.0 3,593.0
sales growth rates EBITDA 2,539.0 2,603.0 2,668.0
– Sources: Management
numbers, research or Depreciation 367.0 427.0 453.0
historical performance; are Amortization 2.0 4.0 9.0
EBIT 2,170.0 2,172.0 2,206.0
the assumptions
reasonable and Interest expense 233.0 261.0 235.0
defensible? Interest (income) (11.0) (9.0) (7.0)
– Footnote your sources Other non-operating (income) / expense 21.0 (19.0) 9.0
Pretax income 1,927.0 1,939.0 1,969.0
Only one
Income taxes 566.3 585.9 560.5 BLUE input
Normalized net income $1,360.7 $1,353.1 $1,408.5 cell
if a constant
Diluted weighted average shares (in millions) 364.000 360.000 365.000 growth rate
Normalized diluted earnings per share $3.74 $3.76 $3.86

Drivers based on inputted assumptions
Sales growth rate 7.6% 4.2% 3.0% 3.0% 3.0% 3.0% 3.0% 0.0%
Gross margin 44.9% 43.6% 42.3%
SG&A expenses (as a % of sales) 25.7% 25.3% 24.3%
EBIT Margin 16.4% 15.3% 14.9% Flatline: BLACK references set equal to
Other non-operating (income) / expense ($ amount) $21.0 ($19.0) $9.0 previous year if kept constant

61
IS: Project Operating Expenses to EBITDA
Step 6: Project future Hist 1 Hist 2 Hist 3 Proj 1 Proj 2 Proj 3 Proj 4 Proj 5 CAGR
operating expenses
Sales $13,198.0 $14,197.0 $14,792.0 $15,235.8 $15,692.8 $16,163.6 $16,648.5 $17,148.0
Cost of sales (excluding depreciation) 7,266.0 8,005.0 8,531.0 8,714.9 8,976.3 9,245.6 9,523.0 9,808.6
– Drivers: margin Gross profit 5,932.0 6,192.0 6,261.0 6,520.9 6,716.5 6,918.0 7,125.6 7,339.3
assumptions for future Forecasts
Calculations
operating expenses SG&A expenses (excluding amortization) 3,393.0 3,589.0 3,593.0 3,687.1 3,797.7 3,911.6 4,028.9 4,149.8
EBITDA 2,539.0 2,603.0 2,668.0 2,833.9 2,918.9 3,006.4 3,096.6 3,189.5
– Sources: Management
numbers, research or Depreciation 367.0 427.0 453.0
historical performance; are Amortization 2.0 4.0 9.0
the assumptions EBIT 2,170.0 2,172.0 2,206.0
reasonable and Interest expense 233.0 261.0 235.0

defensible? Interest (income) (11.0) (9.0) (7.0)
– Calculate down to EBITDA Other non-operating (income) / expense 21.0 (19.0) 9.0
Pretax income 1,927.0 1,939.0 1,969.0
but leave D&A blank for
now. D&A will be outputs Income taxes 566.3 585.9 560.5
from supporting schedules Normalized net income $1,360.7 $1,353.1 $1,408.5
Diluted weighted average shares (in millions) 364.000 360.000 365.000

Only one BLUE input cell Flatline: BLACK references to
Normalized diluted earnings per share $3.74 $3.76 if a constant
$3.86 forecast assumption previous year if kept constant

Gross margin 44.9% 43.6% 42.3% 42.8% 42.8% 42.8% 42.8% 42.8% 0.0%
SG&A expenses (as a % of sales) 25.7% 25.3% 24.3% 24.2% 24.2% 24.2% 24.2% 24.2% 0.0%
EBIT Margin 16.4% 15.3% 14.9%
Other non-operating (income) / expense ($ amount) $21.0 ($19.0) $9.0
Drivers

62
IS: Project Non-Operating Expenses & Taxes
Step 7: Calculate other Hist 1 Hist 2 Hist 3 Proj 1 Proj 2 Proj 3 Proj 4 Proj 5 CAGR
non-operating line
Sales $13,198.0 $14,197.0 $14,792.0 $15,235.8 $15,692.8 $16,163.6 $16,648.5 $17,148.0
items and taxes and Cost of sales (excluding depreciation) 7,266.0 8,005.0 8,531.0 8,714.9 8,976.3 9,245.6 9,523.0 9,808.6
complete the Gross profit 5,932.0 6,192.0 6,261.0 6,520.9 6,716.5 6,918.0 7,125.6 7,339.3
calculations on the SG&A expenses (excluding amortization) 3,393.0 3,589.0 3,593.0 3,687.1 3,797.7 3,911.6 4,028.9 4,149.8
income statement EBITDA 2,539.0 2,603.0 2,668.0 2,833.9 2,918.9 3,006.4 3,096.6 3,189.5
down to net income Depreciation 367.0 427.0 453.0

Amortization 2.0 4.0 9.0
– Use a reasonable effective EBIT 2,170.0 2,172.0 2,206.0 2,833.9 2,918.9 3,006.4 3,096.6 3,189.5
tax rate
Interest expense 233.0 261.0 235.0
– Leave interest expense Interest (income) (11.0) (9.0) (7.0)
and interest income blank Other non-operating (income) / expense 21.0 (19.0) 9.0 0.0 0.0 0.0 0.0 0.0
Calculations
for now; these will be Pretax income 1,927.0 1,939.0 1,969.0 2,833.9 2,918.9 3,006.4 3,096.6 3,189.5
outputs from the debt Income taxes 566.3 585.9 560.5 836.0 881.5 929.0 978.5 1,030.2
schedule Normalized net income $1,360.7 $1,353.1 $1,408.5 $1,997.9 $2,037.4 $2,077.4 $2,118.1 $2,159.3
– Calculate EBIT, pretax
income, and net income Diluted weighted average shares (in millions) 364.000 360.000 365.000
– Be efficient by filling right Normalized diluted earnings per share $3.74 $3.76 $3.86
the historical calculations!
Gross margin 44.9% 43.6% 42.3% 42.8% 42.8% 42.8% 42.8% 42.8% 0.0%
Drivers
EBIT Margin 16.4% 15.3% 14.9% 18.6% 18.6% 18.6% 18.6% 18.6%
Other non-operating (income) / expense ($ amount) $21.0 ($19.0) $9.0 $0.0 $0.0 $0.0 $0.0 $0.0
Effective tax rate 29.4% 30.2% 28.5% 29.5% 30.2% 30.9% 31.6% 32.3% 0.7%

63
IS: Flatline Shares and Calculate EPS
Step 8: Project shares Hist 1 Hist 2 Hist 3 Proj 1 Proj 2 Proj 3 Proj 4 Proj 5 CAGR
outstanding based on
Sales $13,198.0 $14,197.0 $14,792.0 $15,235.8 $15,692.8 $16,163.6 $16,648.5 $17,148.0
historical shares count Cost of sales (excluding depreciation) 7,266.0 8,005.0 8,531.0 8,714.9 8,976.3 9,245.6 9,523.0 9,808.6
Gross profit 5,932.0 6,192.0 6,261.0 6,520.9 6,716.5 6,918.0 7,125.6 7,339.3
– For simplicity, leave shares
SG&A expenses (excluding amortization) 3,393.0 3,589.0 3,593.0 3,687.1 3,797.7 3,911.6 4,028.9 4,149.8
outstanding constant EBITDA 2,539.0 2,603.0 2,668.0 2,833.9 2,918.9 3,006.4 3,096.6 3,189.5
Depreciation 367.0 427.0 453.0

EBIT 2,170.0 2,172.0 2,206.0 2,833.9 2,918.9 3,006.4 3,096.6 3,189.5

Interest (income) (11.0) (9.0) (7.0)
Other non-operating (income) / expense 21.0 (19.0) 9.0 0.0 0.0 0.0 0.0 0.0
Pretax income 1,927.0 1,939.0 1,969.0 2,833.9 2,918.9 3,006.4 3,096.6 3,189.5
Income taxes 566.3 585.9 560.5 836.0 881.5 929.0 978.5 1,030.2
Normalized net income $1,360.7 $1,353.1 $1,408.5 $1,997.9 $2,037.4 $2,077.4 $2,118.1 $2,159.3
Keep
Diluted weighted average shares (in millions) 364.000 360.000 365.000 365.000 365.000 365.000 365.000 365.000
constant
Normalized diluted earnings per share $3.74 $3.76 $3.86 $5.47 $5.58 $5.69 $5.80 $5.92
Calculations
Gross margin 44.9% 43.6% 42.3% 42.8% 42.8% 42.8% 42.8% 42.8% 0.0%
EBIT Margin 16.4% 15.3% 14.9% 18.6% 18.6% 18.6% 18.6% 18.6%

64
IS: Calculate CAGRs
Step 9: Calculate the Hist 1 Hist 2 Hist 3 Proj 1 Proj 2 Proj 3 Proj 4 Proj 5 CAGR
relevant CAGRs for the
Sales $13,198.0 $14,197.0 $14,792.0 $15,235.8 $15,692.8 $16,163.6 $16,648.5 $17,148.0 3.0%
forecasted period Cost of sales (excluding depreciation) 7,266.0 8,005.0 8,531.0 8,714.9 8,976.3 9,245.6 9,523.0 Calculate
9,808.6
the
Gross profit 5,932.0 6,192.0 6,261.0 6,520.9 6,716.5 6,918.0 7,125.6 7,339.3
relevant CAGRs 3.2%
EBITDA 2,539.0 2,603.0 2,668.0 2,833.9 2,918.9 3,006.4 3,096.6 3,189.5 3.6%
Depreciation 367.0 427.0 453.0

EBIT 2,170.0 2,172.0 2,206.0 2,833.9 2,918.9 3,006.4 3,096.6 3,189.5 7.7%

Interest (income) (11.0) (9.0) (7.0)
Pretax income 1,927.0 1,939.0 1,969.0 2,833.9 2,918.9 3,006.4 3,096.6 3,189.5 10.1%
Income taxes 566.3 585.9 560.5 836.0 881.5 929.0 978.5 1,030.2
Normalized net income $1,360.7 $1,353.1 $1,408.5 $1,997.9 $2,037.4 $2,077.4 $2,118.1 $2,159.3 8.9%
TTS Help Normalized diluted earnings per share $3.74 $3.76 $3.86 $5.47 $5.58 $5.69 $5.80 $5.92 8.9%
Review the Commonly Used Ratios & assumptions Step

Functions sheet for the CAGR Sales growth rate 7.6% 4.2% 3.0% 3.0% 3.0% 3.0% 3.0% 0.0%
formula or use either the RATE Gross margin 44.9% 43.6% 42.3% 42.8% 42.8% 42.8% 42.8% 42.8% 0.0%
or RRI functions. SG&A expenses (as a % of sales) 25.7% 25.3% 24.3% 24.2% 24.2% 24.2% 24.2% 24.2% 0.0%
EBIT Margin 16.4% 15.3% 14.9% 18.6% 18.6% 18.6% 18.6% 18.6%
When using RATE, watch your Other non-operating (income) / expense ($ amount) $21.0 ($19.0) $9.0 $0.0 $0.0 $0.0 $0.0 $0.0
signs on the PV and FV. Effective tax rate 29.4% 30.2% 28.5% 29.5% 30.2% 30.9% 31.6% 32.3% 0.7%

65
BS: Input Historical Data
Step 10: Input the Hist 1 Hist 2 Hist 3 Proj 1 Proj 2 Proj 3 Proj 4 Proj 5
historical balance sheet
Cash $460.0 $281.0 $273.0
– Make sure that the Accounts receivable, net 1,188.0 1,454.0 1,424.0
Inputs are BLUE!
historical balance sheet Inventories 1,174.0 1,365.0 1,248.0
Other current assets 247.0 280.0 322.0
balances Total current assets 3,069.0 3,380.0 3,267.0
– Be careful not to omit
anything if you are PP&E, net 3,281.0 3,782.0 3,856.0
combining any line items Definite life intangibles 11.0 110.0 102.0
from the source balance Indefinite life intangibles 1,443.0 2,249.0 2,265.0
sheet Goodwill 3,623.0 5,038.0 5,051.0
Other long-term assets 516.0 610.0 933.0
Total assets $11,943.0 $15,169.0 $15,474.0
Accounts payable $1,189.0 $1,402.0 $1,432.0 Calculations

Accrued liabilities 1,129.0 1,301.0 1,375.0
Total current liabilities 2,318.0 2,703.0 2,807.0
Usually more practical

Revolver 234.0 1,065.0 739.0
to condense debt into
Long-term debt two lines 5,798.0 6,837.0 6,619.0
Deferred income taxes 643.0 523.0 928.0
Other long-term liabilities 1,152.0 1,576.0 774.0
Total liabilities 10,145.0 12,704.0 11,867.0
Total stockholder's equity 1,798.0 2,465.0 3,607.0
Total liabilities and equity $11,943.0 $15,169.0 $15,474.0

Parity check (A = L+E) 0.000 0.000 0.000

66
Operating Working Capital Schedule

67
Operating Working Capital Schedule
Sales
COGS Income
Change in Statement
Working Capital
Working
Capital Working
Capital
Cash Flow
Statement
Balance
Sheet

68
OWC: Set Up the Schedule
Step 11: Set up the
working capital
schedule
– For clarity and efficiency in

Excel, be sure to use the
same working capital line
items in the same order
from the balance sheet
References (GREEN)
– Reference Net Sales and
COGS from the income
statement
(they serve as drivers for
working capital items)

69
OWC: Reference & Calculate Historical Amounts
Step 12: Reference the Hist 1 Hist 2 Hist 3 Proj 1 Proj 2 Proj 3 Proj 4 Proj 5
historical balances
Sales $13,198.0 $14,197.0 $14,792.0 $15,235.8 $15,692.8 $16,163.6 $16,648.5 $17,148.0
from the balance sheet Cost of goods sold 7,266.0 8,005.0 8,531.0 8,714.9 8,976.3 9,245.6 9,523.0 9,808.6
– Calculate historical net Operating working capital balances

Accounts receivable, net $1,188.0 $1,454.0 $1,424.0
working capital and Inventories 1,174.0 1,365.0 1,248.0
increase / decrease in Other current assets 247.0 280.0 322.0
working capital Total non-cash current assets $2,609.0 $3,099.0 $2,994.0
References (GREEN)
Accounts payable Calculations $1,189.0 $1,402.0 $1,432.0
Accrued liabilities 1,129.0 1,301.0 1,375.0
Total non-debt current liabilities $2,318.0 $2,703.0 $2,807.0
Operating working capital / (deficit) $291.0 $396.0 $187.0

Calculations
(Increase) / decrease in operating working capital ($105.0) $209.0
Ratios and assumptions Step function

Accounts receivable, net (collection period in days)
Inventories (days outstanding)
Other current assets (as % of sales)
Accounts payable (days outstanding)

Accrued liabilities (as % of sales)

70
OWC: Calculate Historical Ratios
Step 13: Calculate the Hist 1 Hist 2 Hist 3 Proj 1 Proj 2 Proj 3 Proj 4 Proj 5
historical ratios and
Sales $13,198.0 $14,197.0 $14,792.0 $15,235.8 $15,692.8 $16,163.6 $16,648.5 $17,148.0
percentages (of net Cost of goods sold 7,266.0 8,005.0 8,531.0 8,714.9 8,976.3 9,245.6 9,523.0 9,808.6
sales or COGS) for
Operating working capital balances
each working capital Accounts receivable, net $1,188.0 $1,454.0 $1,424.0
A/R Days Formula
line item Inventories 1,174.0 1,365.0 1,248.0
Total non-cash current assets $2,609.0 $3,099.0 $2,994.0
– Reference the named
range “Days” for these Accounts payable $1,189.0 $1,402.0 $1,432.0
calculations Accrued liabilities 1,129.0 1,301.0 1,375.0
Total non-debt current liabilities $2,318.0 $2,703.0 $2,807.0 Inv. Days Formula
– Use the ending or average
balance? Both are Operating working capital / (deficit) $291.0 $396.0 $187.0
acceptable; just be
consistent in your approach (Increase) / decrease in operating working capital ($105.0) $209.0

Accounts receivable, net (collection period in days) 32.9 37.4 35.1
Inventories (days outstanding) 59.0 62.2 53.4 A/P Days Formula
Other current assets (as % of sales) 1.9% 2.0% 2.2%
Calculations
Accounts payable (days outstanding) 59.7 63.9 61.3
Accrued liabilities (as % of sales) 8.6% 9.2% 9.3%
Excel Shortcut Key

To name a cell:
Alt M M D (Windows Excel)
Ctrl+L (Mac Excel)

71
OWC: Make Preliminary Assumptions
Step 14: Make Hist 1 Hist 2 Hist 3 Proj 1 Proj 2 Proj 3 Proj 4 Proj 5
reasonable
Sales $13,198.0 $14,197.0 $14,792.0 $15,235.8 $15,692.8 $16,163.6 $16,648.5 $17,148.0
assumptions about Cost of goods sold 7,266.0 8,005.0 8,531.0 8,714.9 8,976.3 9,245.6 9,523.0 9,808.6
future ratios and
percentages for Accounts receivable, net $1,188.0 $1,454.0 $1,424.0
working capital items Inventories 1,174.0 1,365.0 1,248.0
Total non-cash current assets $2,609.0 $3,099.0 $2,994.0
– It is also common to keep
certain items without an Accounts payable $1,189.0 $1,402.0 $1,432.0
obvious driver, such as Accrued liabilities 1,129.0 1,301.0 1,375.0
Other current assets, at a Assumptions:
Flatline: BLACK
constant dollar amount, Operating working capital / (deficit) $291.0 $396.0 $187.0 make only first
references to previous
instead of a percent of number BLUE
year if kept constant
input
sales or cost of sales (Increase) / decrease in operating working capital ($105.0) $209.0

Accounts receivable, net (collection period in days) 32.9 37.4 35.1 35.0 35.0 35.0 35.0 35.0 0.0
Inventories (days outstanding) 59.0 62.2 53.4 55.0 55.0 55.0 55.0 55.0 0.0
Other current assets (as % of sales) 1.9% 2.0% 2.2% 2.2% 2.2% 2.2% 2.2% 2.2% 0.0%
Accounts payable (days outstanding) 59.7 63.9 61.3 60.0 60.0 60.0 60.0 60.0 0.0
Accrued liabilities (as % of sales) 8.6% 9.2% 9.3% 9.3% 9.3% 9.3% 9.3% 9.3% 0.0%

72
OWC: Project Balances
Step 15: Using the Hist 1 Hist 2 Hist 3 Proj 1 Proj 2 Proj 3 Proj 4 Proj 5
future ratios and
Sales $13,198.0 $14,197.0 $14,792.0 $15,235.8 $15,692.8 $16,163.6 $16,648.5 $17,148.0
assumptions, project Cost of goods sold 7,266.0 8,005.0 8,531.0 8,714.9 8,976.3 9,245.6 9,523.0 9,808.6
Drivers
the future working

capital balances Accounts receivable, net $1,188.0 $1,454.0 $1,424.0 $1,461.0 $1,504.8 $1,549.9 $1,596.4 $1,644.3
Inventories 1,174.0 1,365.0 1,248.0 1,313.2 1,352.6 1,393.2 1,435.0 1,478.0
Other current assets 247.0 280.0 322.0 335.2 345.2 355.6 366.3 377.3
Total non-cash current assets $2,609.0 $3,099.0 $2,994.0 Projections
Accounts payable $1,189.0 $1,402.0 $1,432.0 $1,432.6 $1,475.6 $1,519.8 $1,565.4 $1,612.4
Operating working capital / (deficit) $291.0 $396.0 $187.0
(Increase) / decrease in operating working capital ($105.0) $209.0

Drivers

73
OWC: Calculate Cash Flows
totals and add the right
Sales $13,198.0 $14,197.0 $14,792.0 $15,235.8 $15,692.8 $16,163.6 $16,648.5 $17,148.0
border Cost of goods sold Right border on the last 7,266.0 8,005.0 8,531.0 8,714.9 8,976.3 9,245.6 9,523.0 9,808.6
historical year as a divider.
– Be efficient by filling right Operating working capital balances Make this the LAST thing
Accounts receivable, net you do on the schedule! $1,188.0 $1,454.0 $1,424.0 $1,461.0 $1,504.8 $1,549.9 $1,596.4 $1,644.3
the historical calculation! Inventories 1,174.0 1,365.0 1,248.0 1,313.2 1,352.6 1,393.2 1,435.0 1,478.0
Total non-cash current assets $2,609.0 $3,099.0 $2,994.0 $3,109.3 $3,202.6 $3,298.7 $3,397.7 $3,499.6
Accrued liabilities 1,129.0 1,301.0 1,375.0 1,416.9 1,459.4 1,503.2 1,548.3 1,594.8 Calculations
Total non-debt current liabilities $2,318.0 $2,703.0 $2,807.0 $2,849.5 $2,935.0 $3,023.0 $3,113.7 $3,207.1
Operating working capital / (deficit) $291.0 $396.0 $187.0 $259.8 $267.6 $275.7 $283.9 $292.5
(Increase) / decrease in operating working capital ($105.0) $209.0 ($72.8) ($7.8) ($8.0) ($8.3) ($8.5)

Excel Shortcut Key

To fill right:
Ctrl+R (Windows Excel)
Cmd+R (Mac Excel)

74
OWC: Link Projections to BS
Step 17: Link the Hist 1 Hist 2 Hist 3 Proj 1 Proj 2 Proj 3 Proj 4 Proj 5
projected working
capital items to the Cash $460.0 $281.0 $273.0
Accounts receivable, net 1,188.0 1,454.0 1,424.0 1,461.0 1,504.8 1,549.9 1,596.4 1,644.3
balance sheet Inventories 1,174.0 1,365.0 1,248.0 1,313.2 1,352.6 1,393.2 1,435.0 1,478.0
Total current assets 3,069.0 3,380.0 3,267.0
PP&E, net 3,281.0 3,782.0 3,856.0

Definite life intangibles 11.0 110.0 102.0
Indefinite life intangibles References (GREEN) 1,443.0 2,249.0 2,265.0
Goodwill 3,623.0 5,038.0 5,051.0
Total assets $11,943.0 $15,169.0 $15,474.0
Revolver 234.0 1,065.0 739.0

Long-term debt 5,798.0 6,837.0 6,619.0

Parity check (A = L+E) 0.000 0.000 0.000

75
OWC: Link Projections to CF
Projected Year Ending December 31,
Step 18: Link the Proj 1 Proj 2 Proj 3 Proj 4 Proj 5
projected working Operating activities
capital cash flows to Net income
Depreciation
the cash flow statement Amortization
(Increase) / decrease in operating working capital References (GREEN) (72.8) (7.8) (8.0) (8.3) (8.5)
Change in other long-term assets and liabilities
Cash flow from operating activities
Investing activities
Capital expenditures
Additions to definite life intangibles
Cash flow from investing activities
Financing activities
Issuance / (repayment) of revolver
Issuance of long-term debt
(Repayment) of long-term debt
Dividends
Cash flow from financing activities
Net change in cash

Beginning cash balance
Ending cash balance
Cash flow available for financing activities

76
Depreciation Schedule

77
Depreciation Schedule
Depreciation
Sales
COGS Income
Change in Statement
Working Capital
Working
Capital Working
Capital
CapEx,
Depreciation Cash Flow
PP&E, PP&E Statement
Depreciation
Balance
Sheet

78
Dep: Set Up the Schedule
Step 19: Set up the
depreciation schedule
– Set up the line items

– Reference net sales
– Input historical capital
expenditures
– Calculate CapEx as a % of
net sales Input historical
References (GREEN)
CapEx (BLUE)
Calculations

79
Dep: Reference & Calculate Historical Data
Step 20: Reference Hist 1 Hist 2 Hist 3 Proj 1 Proj 2 Proj 3 Proj 4 Proj 5
historical information
Sales $13,198.0 $14,197.0 $14,792.0 $15,235.8 $15,692.8 $16,163.6 $16,648.5 $17,148.0
– Usually extremely difficult Beginning PP&E, net Will usually need a plug to reconcile;
to reconcile the historical Capital expenditures DO NOT BACK RECONCILE do not waste too much time doing this
PP&E account (Depreciation expense)
Ending PP&E, net $3,281.0 $3,782.0 $3,856.0 References (GREEN) from balance sheet
– Historical "asset sales"
may be a plug Ratios and assumptions
– Include historical Capital expenditures $594.0 $533.0 $637.0
information that will assist Capital expenditures as % of sales 4.5% 3.8% 4.3%
Depreciation expense $367.0 $427.0 $453.0 References (GREEN) from income statement Step function
in your analysis Depreciation as % of capex 61.8% 80.1% 71.1%
Depreciation as % of PP&E, net 11.2% 11.3% 11.7% Calculations

80
Dep: Project Capital Expenditures
Step 21: Project capital Hist 1 Hist 2 Hist 3 Proj 1 Proj 2 Proj 3 Proj 4 Proj 5
expenditures
Sales $13,198.0 $14,197.0 $14,792.0 $15,235.8 $15,692.8 $16,163.6 $16,648.5 $17,148.0
– Sources: research, Beginning PP&E, net

management projections, Capital expenditures DO NOT BACK RECONCILE
MD&A (Depreciation expense)
Ending PP&E, net $3,281.0 $3,782.0 $3,856.0
– CapEx as % sales is a Calculations
common driver if no Ratios and assumptions
guidance is available Capital expenditures $594.0 $533.0 $637.0 $685.6 $706.2 $727.4 $749.2 $771.7
– Also popular to see based Capital expenditures as % of sales 4.5% 3.8% 4.3% 4.5% 4.5% 4.5% 4.5% 4.5%
Depreciation expense $367.0 $427.0 $453.0 Step function
on reasonable "hard Depreciation as % of capex 61.8% 80.1% 71.1%
dollars" or other Depreciation as % of PP&E, net 11.2% 11.3% 11.7%
appropriate driver
Common
driver if
projected Forecasts
CapEx is
not available

81
Dep: Project Depreciation Expense
Step 22: Project Hist 1 Hist 2 Hist 3 Proj 1 Proj 2 Proj 3 Proj 4 Proj 5
depreciation expense
Sales $13,198.0 $14,197.0 $14,792.0 $15,235.8 $15,692.8 $16,163.6 $16,648.5 $17,148.0
– There are several ways to Beginning PP&E, net

project depreciation; Capital expenditures DO NOT BACK RECONCILE
choose a reasonable (Depreciation expense)
Ending PP&E, net $3,281.0 $3,782.0 $3,856.0
method: Calculations
– As a % of CapEx Ratios and assumptions
– As a % of PP&E Capital expenditures $594.0 $533.0 $637.0 $685.6 $706.2 $727.4 $749.2 $771.7
– As a % of sales Capital expenditures as % of sales 4.5% 3.8% 4.3% 4.5% 4.5% 4.5% 4.5% 4.5%
– Amount Depreciation as % of capex 61.8% 80.1% 71.1% 77.3% 80.0% 80.0% 80.0% 80.0%
– A reasonable growth Depreciation as % of PP&E, net 11.2% 11.3% 11.7%
rate
– The trend and relationship Common driver if projected
between CapEx and depreciation expense is not
available. Review historicals, Forecasts
depreciation is key (more research, or management
on this later) expectations

82
Dep: Project PP&E Balances
Step 23: Fill in Hist 1 Hist 2 Hist 3 Proj 1 Proj 2 Proj 3 Proj 4 Proj 5
projected PP&E table Right border on the last
Sales $13,198.0 $14,197.0 $14,792.0 $15,235.8 $15,692.8 $16,163.6 $16,648.5 $17,148.0
and calculate the ratios historical year as a divider.
Make this the LAST thing
for the projected years Beginning PP&E, net you do on the schedule! $3,856.0 $4,011.3 $4,152.5 $4,298.0 $4,447.9
Capital expenditures DO NOT BACK RECONCILE 685.6 706.2 727.4 749.2 771.7 References
(Depreciation expense) (530.3) (564.9) (581.9) (599.3) (617.3)
– Beginning net PP&E is the Ending PP&E, net $3,281.0 $3,782.0 $3,856.0 $4,011.3 $4,152.5 $4,298.0 $4,447.9 $4,602.2
previous year ending
balance (corkscrew) Ratios and assumptions
Capital expenditures $594.0 $533.0 $637.0 $685.6 $706.2 $727.4 $749.2 $771.7 Calculations
Depreciation as % of capex 61.8% 80.1% 71.1% 77.3% 80.0% 80.0% 80.0% 80.0%
Depreciation as % of PP&E, net 11.2% 11.3% 11.7% 13.2% 13.6% 13.5% 13.5% 13.4%

83
Dep: Smooth the Projections
Step 24: Smooth
depreciation
– Think about the appropriate

depreciation and CapEx
relationship
– Use a step function to
increase depreciation as a
percent of CapEx in each
projected year
Annual increase based on previous

year plus step assumption

84
Dep: Link Projections to IS
Step 25: Link the Hist 1 Hist 2 Hist 3 Proj 1 Proj 2 Proj 3 Proj 4 Proj 5 CAGR
projected depreciation
Sales $13,198.0 $14,197.0 $14,792.0 $15,235.8 $15,692.8 $16,163.6 $16,648.5 $17,148.0 3.0%
expense to the income Cost of sales (excluding depreciation) 7,266.0 8,005.0 8,531.0 8,714.9 8,976.3 9,245.6 9,523.0 9,808.6
statement Gross profit 5,932.0 6,192.0 6,261.0 6,520.9 6,716.5 6,918.0 7,125.6 7,339.3 3.2%
EBITDA 2,539.0 2,603.0 2,668.0 2,833.9 2,918.9 3,006.4 3,096.6 3,189.5 3.6%
Depreciation 367.0 427.0 453.0 530.3 564.9 618.3 674.3 733.1 References
EBIT 2,170.0 2,172.0 2,206.0 2,303.6 2,353.9 2,388.2 2,422.4 2,456.4 2.2%

Interest (income) (11.0) (9.0) (7.0)
Pretax income 1,927.0 1,939.0 1,969.0 2,303.6 2,353.9 2,388.2 2,422.4 2,456.4 4.5%
Income taxes 566.3 585.9 560.5 679.5 710.9 737.9 765.5 793.4
Normalized diluted earnings per share $3.74 $3.76 $3.86 $4.45 $4.50 $4.52 $4.54 $4.56 3.4%

Gross margin 44.9% 43.6% 42.3% 42.8% 42.8% 42.8% 42.8% 42.8% 0.0%
EBIT Margin 16.4% 15.3% 14.9% 15.1% 15.0% 14.8% 14.6% 14.3%

85
Dep: Link Projections to BS
projected net PP&E to
the balance sheet Cash $460.0 $281.0 $273.0
Inventories 1,174.0 1,365.0 1,248.0 1,313.2 1,352.6 1,393.2 1,435.0 1,478.0
PP&E, net References (GREEN) 3,281.0 3,782.0 3,856.0 4,011.3 4,152.5 4,261.7 4,336.6 4,375.2
Definite life intangibles 11.0 110.0 102.0
Indefinite life intangibles 1,443.0 2,249.0 2,265.0
Goodwill 3,623.0 5,038.0 5,051.0
Total assets $11,943.0 $15,169.0 $15,474.0
Revolver 234.0 1,065.0 739.0

Long-term debt 5,798.0 6,837.0 6,619.0

Parity check (A = L+E) 0.000 0.000 0.000

86
Dep: Link Projections to CF
projected depreciation Operating activities
expense and capital Net income
Depreciation 530.3 564.9 618.3 674.3 733.1
expenditures to the Amortization
cash flow statement (Increase) / decrease in operating working capital (72.8) (7.8) (8.0) (8.3) (8.5)
References (GREEN)
Capital expenditures (685.6) (706.2) (727.4) (749.2) (771.7)
Additions to definite life intangibles
Financing activities Watch your signs!

Dividends
Net change in cash

Ending cash balance

87
Amortization Schedule

88
Amortization Schedule
Depreciation
Sales
COGS Income
Change in Statement
Working Capital
Amortization
Working
Capital Working
Capital
CapEx,
Depreciation
Additions, Amortization
Intangibles, Balance
Amortization Intangibles Sheet

89
Amort: Set Up the Schedule
Step 28: Set up the
amortization schedule
– Set up the line items

(similar to depreciation
schedule)
– Reference net sales
– Input historical additions to
intangibles
– Check the footnotes for Input historical
References (GREEN)
detailed breakout of additions (BLUE)
intangibles
Calculations

90
Amort: Reference & Calculate Historical Data
Step 29: Reference and Hist 1 Hist 2 Hist 3 Proj 1 Proj 2 Proj 3 Proj 4 Proj 5
enter the historical
information Sales $13,198.0 $14,197.0 $14,792.0 $15,235.8 $15,692.8 $16,163.6 $16,648.5 $17,148.0
Beginning definite life intangibles, net Will usually need a plug to reconcile;
– Usually extremely difficult Additions to definite life intangibles DO NOT BACK RECONCILE do not waste too much time doing this
to reconcile the historical (Amortization expense)
intangibles account Ending definite life intangibles, net $11.0 $110.0 $102.0 References (GREEN) from balance sheet
– Historical additions to
intangibles / intangible Ratios and assumptions
sales may be a plug Additions to definite life intangibles $0.0 $101.0 $0.0
Additions as % of sales 0.0% 0.7% 0.0% References (GREEN) from income statement
– Include historical
Amortization expense $2.0 $4.0 $9.0
information that will assist Amortization as % of definite life intangibles, net 18.2% 3.6% 8.8% Calculations
in your analysis

91
Amort: Project Additions to Intangibles
Step 30: Project Hist 1 Hist 2 Hist 3 Proj 1 Proj 2 Proj 3 Proj 4 Proj 5
additions to intangibles
Sales $13,198.0 $14,197.0 $14,792.0 $15,235.8 $15,692.8 $16,163.6 $16,648.5 $17,148.0
– Is the company purchasing
new intangibles? Beginning definite life intangibles, net Driven by
Additions to definite life intangibles DO NOT BACK RECONCILE dollar
– Common to see this driven (Amortization expense) amount Forecasts
by either % of sales or Ending definite life intangibles, net $11.0 $110.0 $102.0 (simplifying
dollar amount assumption)
– Review historicals, Ratios and assumptions

research or management Additions to definite life intangibles $0.0 $101.0 $0.0 $0.0 $0.0 $0.0 $0.0 $0.0
expectations Additions as % of sales 0.0% 0.7% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
Amortization expense $2.0 $4.0 $9.0
Amortization as % of definite life intangibles, net 18.2% 3.6% 8.8%
Calculations

92
Amort: Project Amortization Expense
Step 31: Forecast Hist 1 Hist 2 Hist 3 Proj 1 Proj 2 Proj 3 Proj 4 Proj 5
amortization expense
Sales $13,198.0 $14,197.0 $14,792.0 $15,235.8 $15,692.8 $16,163.6 $16,648.5 $17,148.0
– Examine the company's
footnotes for disclosure on Beginning definite life intangibles, net
Additions to definite life intangibles DO NOT BACK RECONCILE
expected amortization (Amortization expense)
expense Ending definite life intangibles, net $11.0 $110.0 $102.0
– Straight-line method can
also be used to keep the Ratios and assumptions
amortization amount Additions to definite life intangibles $0.0 $101.0 $0.0 $0.0 $0.0 $0.0 $0.0 $0.0
constant Additions as % of sales 0.0% 0.7% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
Amortization expense $2.0 $4.0 $9.0 $9.0 $9.0 $9.0 $9.0 $9.0
Amortization as % of definite life intangibles, net 18.2% 3.6% 8.8%
Forecast dollar amount

based on management
Forecasts
disclosure or historical
numbers.

93
Amort: Project Intangibles Balances
Step 32: Fill in the Hist 1 Hist 2 Hist 3 Proj 1 Proj 2 Proj 3 Proj 4 Proj 5
amortization table and
Right border on the last
calculate the ratios for Sales
$13,198.0 $14,197.0 $14,792.0 $15,235.8 $15,692.8 $16,163.6 $16,648.5 $17,148.0
the projected years Beginning definite life intangibles, net

$102.0 $93.0 $84.0 $75.0 $66.0
you do on the schedule!
Additions to definite life intangibles DO NOT BACK RECONCILE
References 0.0 0.0 0.0 0.0 0.0
– Unless you know (Amortization expense) (9.0) (9.0) (9.0) (9.0) (9.0)
otherwise, assume no Ending definite life intangibles, net $11.0 $110.0
Calculations $102.0 $93.0 $84.0 $75.0 $66.0 $57.0
intangible sales for the
projected years Ratios and assumptions
Additions to definite life intangibles $0.0 $101.0 $0.0 $0.0 $0.0 $0.0 $0.0 $0.0
– Beginning intangibles is the
Additions as % of sales 0.0% 0.7% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
previous year ending Amortization expense $2.0 $4.0 $9.0 $9.0 $9.0 $9.0 $9.0 $9.0
balance (corkscrew) Amortization as % of definite life intangibles, net 18.2% 3.6%
Calculations 8.8% 9.7% 10.7% 12.0% 13.6% 15.8%

94
Amort: Link Projections to IS
Step 33: Link the Hist 1 Hist 2 Hist 3 Proj 1 Proj 2 Proj 3 Proj 4 Proj 5 CAGR
projected amortization
Sales $13,198.0 $14,197.0 $14,792.0 $15,235.8 $15,692.8 $16,163.6 $16,648.5 $17,148.0 3.0%
expense to the income Cost of sales (excluding depreciation) 7,266.0 8,005.0 8,531.0 8,714.9 8,976.3 9,245.6 9,523.0 9,808.6
EBITDA 2,539.0 2,603.0 2,668.0 2,833.9 2,918.9 3,006.4 3,096.6 3,189.5 3.6%
Depreciation 367.0 427.0 453.0 530.3 564.9 618.3 674.3 733.1

Amortization 2.0 4.0 9.0 9.0 9.0 9.0 9.0 9.0 References
EBIT 2,170.0 2,172.0 2,206.0 2,294.6 2,344.9 2,379.2 2,413.4 2,447.4 2.1%

Interest (income) (11.0) (9.0) (7.0)
Pretax income 1,927.0 1,939.0 1,969.0 2,294.6 2,344.9 2,379.2 2,413.4 2,447.4 4.4%
Income taxes 566.3 585.9 560.5 676.9 708.2 735.2 762.6 790.5

Gross margin 44.9% 43.6% 42.3% 42.8% 42.8% 42.8% 42.8% 42.8% 0.0%
EBIT Margin 16.4% 15.3% 14.9% 15.1% 14.9% 14.7% 14.5% 14.3%

95
Amort: Link Projections to BS
projected net
intangibles to the Cash $460.0 $281.0 $273.0
PP&E, net 3,281.0 3,782.0 3,856.0 4,011.3 4,152.5 4,261.7 4,336.6 4,375.2
Definite life intangibles References (GREEN) 11.0 110.0 102.0 93.0 84.0 75.0 66.0 57.0
Indefinite life intangibles 1,443.0 2,249.0 2,265.0
Goodwill 3,623.0 5,038.0 5,051.0
Total assets $11,943.0 $15,169.0 $15,474.0
Revolver 234.0 1,065.0 739.0

Long-term debt 5,798.0 6,837.0 6,619.0

Parity check (A = L+E) 0.000 0.000 0.000

96
Amort: Link Projections to CF
projected amortization Operating activities
expense and additions Net income
Depreciation 530.3 564.9 618.3 674.3 733.1
to intangibles to the Amortization 9.0 9.0 9.0 9.0 9.0
cash flow statement (Increase) / decrease in operating working capital (72.8) (7.8) (8.0) (8.3) (8.5)
References (GREEN)
Additions to definite life intangibles 0.0 0.0 0.0 0.0 0.0
Watch your signs!
Dividends
Net change in cash

Ending cash balance

97
Other Long-Term Items Schedule

98
Other Long-Term Items Schedule
Depreciation
Sales
COGS Income
Change in Statement
Working Capital
Amortization
Working
Capital Working
Capital
CapEx,
Depreciation
Additions, Amortization Change in

Other LT Items

Sheet Other LT Items
Amortization Intangibles Term Items

99
Other: Set Up the Schedule
Step 36: Set up the
other long-term items
schedule
– Includes all long-term items

without a driver; this format
allows for you to include
drivers for these items References (GREEN)
easily on this page
– Reference the historical Calculations
balances from the balance
sheet
– Calculate the historical
References (GREEN)
changes
Calculations

100
Other: Project Balances
Step 37: Project future
balances
– Keep the long-term items

constant for projected
years if you have no drivers
– First projected year is a BLUE assumption as a reference Flatline: assumed to remain constant as
blue assumption (driver), to last year historical balance a reference to last year historical balance
referencing back to the last
historical year balance; this
keeps the amount exactly
the same as historical
– All other projected years
are black references, if
held constant

101
Other: Calculate Cash Flows
changes from other Right border on the last
long-term assets and Other Long-Term Assets Make this the LAST thing
Indefinite life intangibles you do on the schedule! $1,443.0 $2,249.0 $2,265.0 $2,265.0 $2,265.0 $2,265.0 $2,265.0 $2,265.0
liabilities Goodwill 3,623.0 5,038.0 5,051.0 5,051.0 5,051.0 5,051.0 5,051.0 5,051.0
Other long-term assets 516.0 610.0 933.0 933.0 933.0 933.0 933.0 933.0
– Should total to zero if the Total other long-term assets $5,582.0 $7,897.0 $8,249.0 $8,249.0 $8,249.0 $8,249.0 $8,249.0 $8,249.0
items are held constant (Increase) / decrease in long-term other assets (2,315.0) (352.0) 0.0 0.0 0.0 0.0 0.0
– Be efficient by filling right Calculations
the historical calculation! Other Long-Term Liabilities
Deferred income taxes $643.0 $523.0 $928.0 $928.0 $928.0 $928.0 $928.0 $928.0
Total other long-term liabilities $1,795.0 $2,099.0 $1,702.0 $1,702.0 $1,702.0 $1,702.0 $1,702.0 $1,702.0
Increase / (decrease) in other long-term liabilities 304.0 (397.0) 0.0 0.0 0.0 0.0 0.0
Change in other long-term assets and liabilities ($2,011.0) ($749.0) $0.0 $0.0 $0.0 $0.0 $0.0

102
Other: Link Projections to BS
projected other long-
term items to the Cash $460.0 $281.0 $273.0
PP&E, net 3,281.0 3,782.0 3,856.0 4,011.3 4,152.5 4,261.7 4,336.6 4,375.2
Definite life intangibles 11.0 110.0 102.0 93.0 84.0 75.0 66.0 57.0
Indefinite life intangibles 1,443.0 2,249.0 2,265.0 2,265.0 2,265.0 2,265.0 2,265.0 2,265.0
Goodwill 3,623.0 5,038.0 5,051.0 5,051.0 5,051.0 5,051.0 5,051.0 5,051.0
Total assets $11,943.0 $15,169.0 $15,474.0
References (GREEN)
Revolver 234.0 1,065.0 739.0

Long-term debt 5,798.0 6,837.0 6,619.0

Parity check (A = L+E) 0.000 0.000 0.000

103
Other: Link Projections to CF
projected change in Operating activities
other long-term items Net income
Depreciation 530.3 564.9 618.3 674.3 733.1
to the cash flow Amortization 9.0 9.0 9.0 9.0 9.0
statement (Increase) / decrease in operating working capital (72.8) (7.8) (8.0) (8.3) (8.5)
Change in other long-term assets and liabilities References (GREEN) 0.0 0.0 0.0 0.0 0.0
Dividends
Net change in cash

Ending cash balance

104
Shareholders’ Equity Schedule

105
Shareholders’ Equity Schedule
Depreciation Net Income
Sales
COGS Income
Change in Statement
Working Capital
Amortization
Working
Capital Net
Working
Income
Capital
Share Repo, Option
CapEx,
Proceeds, Dividends
Depreciation Cash Flow Shareholders’
PP&E, Statement Equity
PP&E
Equity
Depreciation Balance
Additions, Amortization Change in

Other LT Items


106
Equity: Set Up the Schedule
Step 41: Set up the
shareholders' equity
schedule
– Input the historical

information; this may be Historicals may be
tricky to reconcile difficult to reconcile
– Add lines for other equity-

References (GREEN)
related items such as from Balance Sheet
options, share repurchase
and dividends

107
Equity: Reference & Calculate Historical Data
Step 42: Enter the Hist 1 Hist 2 Hist 3 Proj 1 Proj 2 Proj 3 Proj 4 Proj 5
historical dividend
Beginning equity balance
information and Net income DO NOT BACK RECONCILE
calculate the historical Dividends
Ending equity balance $1,798.0 $2,465.0 $3,607.0
payout ratio BLUE inputs
Ratios and assumptions
– Look in the footnotes, the Dividends $604.0 $622.0 $653.0
Reference (GREEN) from
Net income 1,360.7 1,353.1 1,408.5 Step function
cash flow statement or the Income Statement
Dividend payout ratio 44.4% 46.0% 46.4%
statement of stockholders’
equity for the information Calculations

108
Equity: Project Dividends
Step 43: Forecast Hist 1 Hist 2 Hist 3 Proj 1 Proj 2 Proj 3 Proj 4 Proj 5
projected dividends
Beginning equity balance
Net income DO NOT BACK RECONCILE
– Driver can be: Dividends
1) A payout ratio Ending equity balance $1,798.0 $2,465.0 $3,607.0 Calculations
2) A fixed dollar amount
3) A per share basis Dividends $604.0 $622.0 $653.0 $684.3 $692.3 $695.4 $698.3 $700.9
Net income 1,360.7 1,353.1 1,408.5 1,617.7 1,636.8 1,644.0 1,650.7 1,656.9 Step function
Dividend payout ratio 44.4% 46.0% 46.4% 42.3% 42.3% 42.3% 42.3% 42.3% 0.0%
References (GREEN)
Assumption / Driver
from income statement

109
Equity: Complete Projected Balances
Step 44: Complete the Hist 1 Hist 2 Hist 3 Proj 1 Proj 2 Proj 3 Proj 4 Proj 5
forecasted equity
Beginning equity balance $3,607.0 $4,540.4 $5,484.8 $6,433.4 $7,385.9
account Net income DO NOT BACK RECONCILE 1,617.7 1,636.8 1,644.0 1,650.7 1,656.9
Watch your signs!
Dividends (684.3) (692.3) (695.4) (698.3) (700.9)
– Beginning equity balance is Ending equity balance Right border on the last $1,798.0 $2,465.0 $3,607.0 $4,540.4 $5,484.8 $6,433.4 $7,385.9 $8,341.9
the previous year ending Ratios and assumptions Make this the LAST thing
balance (corkscrew) Dividends you do on the schedule! $604.0 $622.0 $653.0 $684.3 $692.3 $695.4 $698.3 $700.9
Net income 1,360.7 1,353.1 1,408.5 1,617.7 1,636.8 1,644.0 1,650.7 1,656.9 Step function
Dividend payout ratio 44.4% 46.0% 46.4% 42.3% 42.3% 42.3% 42.3% 42.3% 0.0%
GREEN references from a different sheet

BLACK references / calculations on the same sheet
BLUE inputs / assumption

110
Equity: Link Projections to BS
Step 45: Link projected Hist 1 Hist 2 Hist 3 Proj 1 Proj 2 Proj 3 Proj 4 Proj 5
equity to the balance
sheet Cash $460.0 $281.0 $273.0
Inventories 1,174.0 1,365.0 1,248.0 1,313.2 1,352.6 1,393.2 1,435.0 1,478.0
PP&E, net 3,281.0 3,782.0 3,856.0 4,011.3 4,152.5 4,261.7 4,336.6 4,375.2
Goodwill 3,623.0 5,038.0 5,051.0 5,051.0 5,051.0 5,051.0 5,051.0 5,051.0
Total assets $11,943.0 $15,169.0 $15,474.0
Revolver 234.0 1,065.0 739.0

Long-term debt 5,798.0 6,837.0 6,619.0
Total stockholder's equity References (GREEN) 1,798.0 2,465.0 3,607.0 4,540.4 5,484.8 6,433.4 7,385.9 8,341.9

Parity check (A = L+E) 0.000 0.000 0.000

111
Equity: Link Projections to CF
Step 46: Link net Proj 1 Proj 2 Proj 3 Proj 4 Proj 5
income (from IS) and Operating activities
the projected change in Net income $1,617.7 $1,636.8 $1,644.0 $1,650.7 $1,656.9
Depreciation 530.3 564.9 618.3 674.3 733.1
equity schedule items Amortization 9.0 9.0 9.0 9.0 9.0
to the cash flow (Increase) / decrease in operating working capital (72.8) (7.8) (8.0) (8.3) (8.5)
statement Change in other long-term assets and liabilities 0.0 0.0 0.0 0.0 0.0
Capital expenditures References (GREEN) (685.6) (706.2) (727.4) (749.2) (771.7)
Dividends (684.3) (692.3) (695.4) (698.3) (700.9)
Net change in cash

Ending cash balance

112
Balancing

113
Balancing the Balance Sheet
Sales
COGS Income
Change in Statement
Working Capital
Amortization
Working
Capital Net
Working
Income
Capital
Share Repo, Option
CapEx,
Proceeds, Dividends
PP&E
Equity
Beginning Ending
Additions, Amortization Cash Cash Change in
Other LT Items


114
Balance the Balance Sheet
Step 47: Calculate Hist 1 Hist 2 Hist 3 Proj 1 Proj 2 Proj 3 Proj 4 Proj 5
balance sheet totals
and flatline debt Cash $460.0 $281.0 $273.0
numbers Inventories 1,174.0 1,365.0 1,248.0 1,313.2 1,352.6 1,393.2 1,435.0 1,478.0
– Leave Cash blank for now Total current assets 3,069.0 3,380.0 3,267.0 3,109.3 3,202.6 3,298.7 3,397.7 3,499.6
– Leave Revolver and Long-
term debt constant for now; PP&E, net 3,281.0 3,782.0 3,856.0 4,011.3 4,152.5 4,261.7 4,336.6 4,375.2
this will be changed after Definite life intangibles 11.0 110.0 102.0 93.0 84.0 75.0 66.0 57.0
completing the debt and
Goodwill 3,623.0 5,038.0 5,051.0 5,051.0 5,051.0 5,051.0 5,051.0 5,051.0
interest schedule Other long-term assets 516.0 610.0 933.0 933.0 933.0 933.0 933.0 933.0
– Calculate balance sheet Total assets $11,943.0 $15,169.0 $15,474.0 $15,462.7 $15,688.2 $15,884.4 $16,049.2 $16,180.8
subtotals; easiest way to
this is to copy each total Accounts payable $1,189.0 $1,402.0 $1,432.0 $1,432.6 $1,475.6 $1,519.8 $1,565.4 $1,612.4
over from the historical Accrued liabilities 1,129.0 1,301.0 1,375.0 1,416.9 1,459.4 1,503.2 1,548.3 1,594.8
year Total current liabilities 2,318.0 2,703.0 2,807.0 2,849.5 2,935.0 3,023.0 3,113.7 3,207.1
Flatline for now;
Revolver Calculations 234.0 making
consider 1,065.0
RED 739.0 739.0 739.0 739.0 739.0 739.0
Long-term debt as a reminder
5,798.0 to
6,837.0 6,619.0 6,619.0 6,619.0 6,619.0 6,619.0 6,619.0
Deferred income taxes update late 523.0
643.0 928.0 928.0 928.0 928.0 928.0 928.0
Total stockholder's equity 1,798.0 2,465.0 3,607.0 4,540.4 5,484.8SHEET

THE BALANCE 6,433.4 7,385.9
WON’T BALANCE 8,341.9
YET!
Parity check (A = L+E) 0.000 0.000 0.000 987.235 1,791.614 2,632.074 3,510.361 4,428.305

115
CF: Calculate Operating & Investing Cash Flows
Step 48: Calculate Cash Proj 1 Proj 2 Proj 3 Proj 4 Proj 5
flow from operating Operating activities
activities and Cash flow Net income $1,617.7 $1,636.8 $1,644.0 $1,650.7 $1,656.9
Depreciation 530.3 564.9 618.3 674.3 733.1
from investing Amortization 9.0 9.0 9.0 9.0 9.0
activities (Increase) / decrease in operating working capital (72.8) (7.8) (8.0) (8.3) (8.5)
Change in other long-term assets and liabilities 0.0 0.0 0.0 0.0 0.0
Cash flow from operating activities 2,084.1 2,202.9 2,263.2 2,325.7 2,390.5
Calculations
Cash flow from investing activities (685.6) (706.2) (727.4) (749.2) (771.7)
Dividends (684.3) (692.3) (695.4) (698.3) (700.9)
Net change in cash

Ending cash balance

116
CF: Calculate Change in Cash
Step 49: Calculate Net Proj 1 Proj 2 Proj 3 Proj 4 Proj 5
change in cash Operating activities
Net income $1,617.7 $1,636.8 $1,644.0 $1,650.7 $1,656.9
Depreciation 530.3 564.9 618.3 674.3 733.1
Amortization 9.0 9.0 9.0 9.0 9.0
(Increase) / decrease in operating working capital (72.8) (7.8) (8.0) (8.3) (8.5)
Issuance of long-term debt Watch your
(Repayment) of long-term debt totals!
Dividends (684.3) (692.3) (695.4) (698.3) (700.9)
Cash flow from financing activities (684.3) (692.3) (695.4) (698.3) (700.9)
Calculations
Net change in cash 714.2 804.4 840.5 878.3 917.9
Ending cash balance

Challenge
– How do you calculate “Net
change in cash?”

117
CF: Reference Beginning Cash
Step 50: Reference Proj 1 Proj 2 Proj 3 Proj 4 Proj 5
Beginning cash Operating activities
balance from the Net income $1,617.7 $1,636.8 $1,644.0 $1,650.7 $1,656.9
Depreciation 530.3 564.9 618.3 674.3 733.1
balance sheet Amortization 9.0 9.0 9.0 9.0 9.0
– Verify you have the correct Change in other long-term assets and liabilities 0.0 0.0 0.0 0.0 0.0
column referenced from the Cash flow from operating activities 2,084.1 2,202.9 2,263.2 2,325.7 2,390.5
balance sheet
– Remember this year’s Investing activities
beginning balance is last
year’s ending balance Cash flow from investing activities (685.6) (706.2) (727.4) (749.2) (771.7)
Dividends (684.3) (692.3) (695.4) (698.3) (700.9)

Beginning cash balance References (GREEN) 273.0 0.0 0.0 0.0 0.0
Ending cash balance

118
CF: Calculate Ending Cash
Step 51: Calculate Proj 1 Proj 2 Proj 3 Proj 4 Proj 5
Ending cash balance Operating activities
Net income $1,617.7 $1,636.8 $1,644.0 $1,650.7 $1,656.9
– Also calculate Cash flow Depreciation 530.3 564.9 618.3 674.3 733.1
available for financing Amortization 9.0 9.0 9.0 9.0 9.0
activities as the sum of Change in other long-term assets and liabilities 0.0 0.0 0.0 0.0 0.0
Operating and Investing Cash flow from operating activities 2,084.1 2,202.9 2,263.2 2,325.7 2,390.5
activities; this will be used
on the Debt schedule in a Investing activities
later step Capital expenditures (685.6) (706.2) (727.4) (749.2) (771.7)
Dividends (684.3) (692.3) (695.4) (698.3) (700.9)

Beginning cash balance 273.0 0.0 0.0 0.0 0.0
Ending cash balance $987.2 $804.4 $840.5 $878.3 $917.9
Calculations
Cash flow available for financing activities $1,398.5 $1,496.7 $1,535.9 $1,576.6 $1,618.8

119
CF: Link Cash Balances to BS
Step 52: Link Ending Hist 1 Hist 2 Hist 3 Proj 1 Proj 2 Proj 3 Proj 4 Proj 5
cash balance to the
balance sheet Cash References (GREEN) $460.0 $281.0 $273.0 $987.2 $1,791.6 $2,632.1 $3,510.4 $4,428.3
Inventories TRUST THE STEPS! 1,174.0 1,365.0 1,248.0 1,313.2 1,352.6 1,393.2 1,435.0 1,478.0
Total current assets 3,069.0 3,380.0 3,267.0 4,096.6 4,994.2 5,930.8 6,908.0 7,927.9
PP&E, net 3,281.0 3,782.0 3,856.0 4,011.3 4,152.5 4,261.7 4,336.6 4,375.2
Goodwill 3,623.0 5,038.0 5,051.0 5,051.0 5,051.0 5,051.0 5,051.0 5,051.0
Total assets Right border on the last $11,943.0 $15,169.0 $15,474.0 $16,449.9 $17,479.8 $18,516.4 $19,559.6 $20,609.1
Accounts payable you do on the schedule! $1,189.0 $1,402.0 $1,432.0 $1,432.6 $1,475.6 $1,519.8 $1,565.4 $1,612.4
Revolver 234.0 1,065.0 739.0 739.0 739.0 739.0 739.0 739.0

Long-term debt 5,798.0 6,837.0 6,619.0 6,619.0 6,619.0 6,619.0 6,619.0 6,619.0
Total stockholder's equity 1,798.0 2,465.0 3,607.0 4,540.4 5,484.8 SHEET

THE BALANCE 6,433.4 7,385.9
SHOULD 8,341.9
BALANCE!
Parity check (A = L+E) 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000

120
Iterative CF and BS
Cash Flow Statement
AFTER step 52
Balance Sheet
AFTER step 52

121
Debt Schedule

122
Debt and Interest Schedule
Sales
COGS Income Debt &

Change in Statement Interest
Working Capital
Amortization
Working Debt
Capital Net Balances
Working
Income
Capital
CapEx, Share Repo, Option

Proceeds, Dividends
PP&E
Equity
Beginning Ending
Other LT Items


123
Debt: Set Up the Schedule
Step 53: Set up the
debt schedule
– Reference Cash flow

available for financing
References (GREEN)
activities from the bottom of
the CF statement
– Reference all equity
sources and uses of cash
Partial screen shot for training purposes

124
Debt: Reference Cash Balances
Step 54: Calculate Cash Hist 1 Hist 2 Hist 3 Proj 1 Proj 2 Proj 3 Proj 4 Proj 5
available for debt
repayment Cash flow available for financing activities $1,398.5 $1,496.7 $1,535.9 $1,576.6 $1,618.8
Dividends (684.3) (692.3) (695.4) (698.3) (700.9)
Plus: beginning cash balance References (GREEN) 273.0 987.2 1,791.6 2,632.1 3,510.4
– Reference the Beginning Less: minimum cash balance (250.0) (250.0) (250.0) (250.0) (250.0)
cash balance (last year's Cash available for debt repayment References $737.2 $1,541.6 $2,382.1 $3,260.4 $4,178.3
ending cash balance) from Long-term debt issuance
the balance sheet Long-term debt (repayment) Calculations
– Deduct a minimum cash Cash available for revolver
balance
Revolver
– Skip Long-term debt
Beginning balance
issuance / (repayments), Issuance / (repayment) of revolver Be careful!
Cash available for revolver, Ending balance Choose the correct column for the
and Revolver section for beginning cash balance.
now Long-term debt
Beginning balance
Issuance
(Repayment)
Ending balance

Minimum cash balance Assumptions $250.0 $250.0 $250.0 $250.0 $250.0
Long-term debt issuance
Long-term debt repayment
Interest rate on revolver
Partial screen shot for training purposes Interest rate on long-term debt
Interest rate on cash

125
Debt: Reference Debt Balances
historical long-term
debt balances from the Cash flow available for financing activities $1,398.5 $1,496.7 $1,535.9 $1,576.6 $1,618.8
Dividends (684.3) (692.3) (695.4) (698.3) (700.9)
balance sheet Plus: beginning cash balance 273.0 987.2 1,791.6 2,632.1 3,510.4
Less: minimum cash balance (250.0) (250.0) (250.0) (250.0) (250.0)
Cash available for debt repayment $737.2 $1,541.6 $2,382.1 $3,260.4 $4,178.3
Long-term debt (repayment)
Cash available for revolver
Revolver
Beginning balance
Ending balance
Long-term debt
Beginning balance
Issuance
(Repayment)
Ending balance References (GREEN) $5,798.0 $6,837.0 $6,619.0

126
Debt: Input Debt Repayments
Step 56: Input the Hist 1 Hist 2 Hist 3 Proj 1 Proj 2 Proj 3 Proj 4 Proj 5
future debt repayments
– Enter the known aggregate Dividends (684.3) (692.3) (695.4) (698.3) (700.9)
maturities of long-term debt Plus: beginning cash balance 273.0 987.2 1,791.6 2,632.1 3,510.4
Revolver
Beginning balance
Ending balance
Long-term debt
Beginning balance
References;
Issuance 0.0 0.0 0.0 0.0 0.0
Watch your
(Repayment) signs! (289.0) (435.0) (253.0) (303.0) (209.0)
Ending balance $5,798.0 $6,837.0 $6,619.0
Ratios and assumptions Leave debt issuance

Minimum cash balance ZERO $250.0 $250.0 $250.0 $250.0 $250.0
Long-term debt issuance 0.0 0.0 0.0 0.0 0.0
Inputs from known
Long-term debt repayment 289.0 435.0 253.0 303.0 209.0
debt maturities
Interest rate on revolver

127
Debt: Project Future Debt Balances
Step 57: Project future Hist 1 Hist 2 Hist 3 Proj 1 Proj 2 Proj 3 Proj 4 Proj 5
debt balances
– Reference Beginning Dividends (684.3) (692.3) (695.4) (698.3) (700.9)
balance Plus: beginning cash balance 273.0 987.2 1,791.6 2,632.1 3,510.4
– Calculate projected Ending Cash available for debt repayment $737.2 $1,541.6 $2,382.1 $3,260.4 $4,178.3
balance Long-term debt issuance
Revolver
Beginning balance
Ending balance
Long-term debt
Beginning balance References $6,619.0 $6,330.0 $5,895.0 $5,642.0 $5,339.0
Issuance 0.0 0.0 0.0 0.0 0.0
(Repayment) (289.0) (435.0) (253.0) (303.0) (209.0)
Ending balance Calculations $5,798.0 $6,837.0 $6,619.0 $6,330.0 $5,895.0 $5,642.0 $5,339.0 $5,130.0

128
Debt: Cash Available for Revolver
Step 58: Reference Hist 1 Hist 2 Hist 3 Proj 1 Proj 2 Proj 3 Proj 4 Proj 5
Long-term debt
issuance and Long- Cash flow available for financing activities $1,398.5 $1,496.7 $1,535.9 $1,576.6 $1,618.8
Dividends (684.3) (692.3) (695.4) (698.3) (700.9)
term debt (repayments) Plus: beginning cash balance 273.0 987.2
References
1,791.6 2,632.1 3,510.4
– Calculate Excess cash Cash available for debt repayment $737.2 $1,541.6 $2,382.1 $3,260.4 $4,178.3
available for revolver Long-term debt issuance 0.0 0.0 0.0 0.0 0.0
Long-term debt (repayment) (289.0) (435.0) (253.0) (303.0) (209.0)
Cash available for revolver $448.2 $1,106.6 $2,129.1 $2,957.4 $3,969.3
Revolver
Beginning balance
Calculations
Ending balance
Long-term debt
Beginning balance $6,619.0 $6,330.0 $5,895.0 $5,642.0 $5,339.0
Issuance 0.0 0.0 0.0 0.0 0.0
(Repayment) (289.0) (435.0) (253.0) (303.0) (209.0)
Ending balance $5,798.0 $6,837.0 $6,619.0 $6,330.0 $5,895.0 $5,642.0 $5,339.0 $5,130.0

129
Debt: Reference Historical Revolver
historical revolving
credit facility balances Cash flow available for financing activities $1,398.5 $1,496.7 $1,535.9 $1,576.6 $1,618.8
Dividends (684.3) (692.3) (695.4) (698.3) (700.9)
from the balance sheet Plus: beginning cash balance 273.0 987.2 1,791.6 2,632.1 3,510.4
Revolver
Beginning balance
Ending balance References $234.0 $1,065.0 $739.0
Long-term debt
Issuance 0.0 0.0 0.0 0.0 0.0
(Repayment) (289.0) (435.0) (253.0) (303.0) (209.0)
Ending balance $5,798.0 $6,837.0 $6,619.0 $6,330.0 $5,895.0 $5,642.0 $5,339.0 $5,130.0

130
Debt: Set Up the Revolver Logic
Step 60: Set up the Hist 1 Hist 2 Hist 3 Proj 1 Proj 2 Proj 3 Proj 4 Proj 5
logic for the revolver
– The "cash sweep" must be Dividends (684.3) (692.3) (695.4) (698.3) (700.9)
able to: Plus: beginning cash balance 273.0 987.2 1,791.6 2,632.1 3,510.4
1) Borrow if cash flow Cash available for debt repayment $737.2 $1,541.6 $2,382.1 $3,260.4 $4,178.3
deficit (cash available Long-term debt issuance 0.0 0.0 0.0 0.0 0.0
is negative) Long-term debt (repayment) (289.0) (435.0) (253.0) (303.0) (209.0)
2) Pay down itself with Cash available for revolver $448.2 $1,106.6 $2,129.1 $2,957.4 $3,969.3
free cash flow (cash References
available is positive) Revolver
3) Build cash if revolver Beginning balance Cash sweep
is paid off (stop paying (using min logic)
off revolver once it
has a zero balance) Ending balance $234.0 $1,065.0 $739.0
Calculations

131
Debt: Projected Balances Complete
Debt Schedule Hist 1 Hist 2 Hist 3 Proj 1 Proj 2 Proj 3 Proj 4 Proj 5
AFTER step 60
Dividends (684.3) (692.3) (695.4) (698.3) (700.9)
Plus: beginning cash balance 273.0 987.2 1,791.6 2,632.1 3,510.4
Revolver
Beginning balance $739.0 $290.8 $0.0 $0.0 $0.0
Issuance / (repayment) of revolver (448.2) (290.8) 0.0 0.0 0.0
Ending balance $234.0 $1,065.0 $739.0 $290.8 $0.0 $0.0 $0.0 $0.0
Long-term debt
Issuance 0.0 0.0 0.0 0.0 0.0
(Repayment) (289.0) (435.0) (253.0) (303.0) (209.0)
Ending balance $5,798.0 $6,837.0 $6,619.0 $6,330.0 $5,895.0 $5,642.0 $5,339.0 $5,130.0

132
Debt: Link Projections to BS
Step 61: Link the debt Hist 1 Hist 2 Hist 3 Proj 1 Proj 2 Proj 3 Proj 4 Proj 5
balances to the balance
sheet Cash $460.0 $281.0 $273.0 $987.2 $1,791.6 $2,632.1 $3,510.4 $4,428.3
Inventories 1,174.0 1,365.0 1,248.0 1,313.2 1,352.6 1,393.2 1,435.0 1,478.0
– Link the debt numbers in Other current assets 247.0 280.0 322.0 335.2 345.2 355.6 366.3 377.3
the balance sheet Total current assets 3,069.0 3,380.0 3,267.0 4,096.6 4,994.2 5,930.8 6,908.0 7,927.9
– Format cells as green
– The balance sheet will PP&E, net 3,281.0 3,782.0 3,856.0 4,011.3 4,152.5 4,261.7 4,336.6 4,375.2
temporarily fall out of parity Definite life intangibles 11.0 110.0 102.0 93.0 84.0 75.0 66.0 57.0
This will resolve when the Indefinite life intangibles 1,443.0 2,249.0 2,265.0 2,265.0 2,265.0 2,265.0 2,265.0 2,265.0
Goodwill 3,623.0 5,038.0 5,051.0 5,051.0 5,051.0 5,051.0 5,051.0 5,051.0
debt repayments are linked
into the cash flow Total assets $11,943.0 $15,169.0 $15,474.0 $16,449.9 $17,479.8 $18,516.4 $19,559.6 $20,609.1
Revolver 234.0 1,065.0 739.0 290.8 0.0 0.0 0.0 0.0

References (GREEN)
Long-term debt 5,798.0 6,837.0 6,619.0 6,330.0 5,895.0 5,642.0 5,339.0 5,130.0
Total stockholder's equity 1,798.0 2,465.0 3,607.0 BALANCE 5,484.8

4,540.4 SHEET WILL FALL OUT
6,433.4 OF PARITY!
7,385.9 8,341.9
Parity check (A = L+E) 0.000 0.000 0.000 (737.235) (1,463.000) (1,716.000) (2,019.000) (2,228.000)

133
Debt: Link Projections to CF
Step 62: Link the debt Proj 1 Proj 2 Proj 3 Proj 4 Proj 5
repayments and Operating activities
borrowings to the cash Net income $1,617.7 $1,636.8 $1,644.0 $1,650.7 $1,656.9
Depreciation 530.3 564.9 618.3 674.3 733.1
flow statement Amortization 9.0 9.0 9.0 9.0 9.0
Issuance of long-term debt References (GREEN) 0.0 0.0 0.0 0.0 0.0
(Repayment) of long-term debt (289.0) (435.0) (253.0) (303.0) (209.0)
Dividends (684.3) (692.3) (695.4) (698.3) (700.9)
Cash flow from financing activities (1,421.5) (1,418.1) (948.4) (1,001.3) (909.9)
Net change in cash (23.0) 78.6 587.5 575.3 708.9

Beginning cash balance 273.0 250.0 328.6 916.1 1,491.4
Ending cash balance $250.0 $328.6 $916.1 $1,491.4 $2,200.3

134
Check the Balance Sheet Balances
Balance Sheet Hist 1 Hist 2 Hist 3 Proj 1 Proj 2 Proj 3 Proj 4 Proj 5
AFTER step 62
Cash $460.0 $281.0 $273.0 $250.0 $328.6 $916.1 $1,491.4 $2,200.3
Inventories 1,174.0 1,365.0 1,248.0 1,313.2 1,352.6 1,393.2 1,435.0 1,478.0
PP&E, net 3,281.0 3,782.0 3,856.0 4,011.3 4,152.5 4,261.7 4,336.6 4,375.2
Goodwill 3,623.0 5,038.0 5,051.0 5,051.0 5,051.0 5,051.0 5,051.0 5,051.0
Total assets $11,943.0 $15,169.0 $15,474.0 $15,712.7 $16,016.8 $16,800.4 $17,540.6 $18,381.1
Revolver 234.0 1,065.0 739.0 290.8 0.0 0.0 0.0 0.0

Long-term debt 5,798.0 6,837.0 6,619.0 6,330.0 5,895.0 5,642.0 5,339.0 5,130.0
Total stockholder's equity 1,798.0 2,465.0 3,607.0 BALANCE

4,540.4 SHEET MUST
5,484.8 6,433.4BALANCE
7,385.9AGAIN!
8,341.9
Parity check (A = L+E) 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000

135
Interest

136
Linking Interest
Sales
Interest Expense
COGS Income Debt &
Working Capital
Amortization
Working Debt
Working
Income
Capital

Proceeds, Dividends
PP&E
Equity
Beginning Ending
Other LT Items


137
Debt: Calculate Revolver Interest Exp
Revolver
Step 63: Calculate Beginning balance $739.0 $290.8 $0.0 $0.0 $0.0
interest expense from Issuance / (repayment) of revolver (448.2) (290.8) 0.0 0.0 0.0
Ending balance $234.0 $1,065.0 $739.0 $290.8 $0.0 $0.0 $0.0 $0.0
Revolver
Long-term debt
– Calculate the average Beginning balance $6,619.0 $6,330.0 $5,895.0 $5,642.0 $5,339.0
balance Issuance 0.0 0.0 0.0 0.0 0.0
– Make a reasonable (Repayment) (289.0) (435.0) (253.0) (303.0) (209.0)
Ending balance $5,798.0 $6,837.0 $6,619.0 $6,330.0 $5,895.0 $5,642.0 $5,339.0 $5,130.0
assumption for an interest
rate Revolver Average balances $514.9 $145.4 $0.0 $0.0 $0.0
– Forecast the interest Interest rate References 0.23% 0.23% 0.23% 0.23% 0.23%
expense Interest expense $1.2 $0.3 $0.0 $0.0 $0.0
Long-term debt Average balances Calculations

Interest rate
Interest expense
Total interest expense
Cash Average balances

Interest rate
Interest (income)

Minimum cash balance $250.0 $250.0 $250.0 $250.0 $250.0
Interest rate on revolver Assumptions / Drivers 0.23% 0.23% 0.23% 0.23% 0.23%

138
Debt: Calculate Long-Term Debt Interest Expense
Revolver
Step 64: Calculate Beginning balance $739.0 $290.8 $0.0 $0.0 $0.0
interest expense from Issuance / (repayment) of revolver (448.2) (290.8) 0.0 0.0 0.0
Ending balance $234.0 $1,065.0 $739.0 $290.8 $0.0 $0.0 $0.0 $0.0
Long-term debt
Long-term debt
– Calculate the average Beginning balance $6,619.0 $6,330.0 $5,895.0 $5,642.0 $5,339.0
balance Issuance 0.0 0.0 0.0 0.0 0.0
– Make a reasonable (Repayment) (289.0) (435.0) (253.0) (303.0) (209.0)
Ending balance $5,798.0 $6,837.0 $6,619.0 $6,330.0 $5,895.0 $5,642.0 $5,339.0 $5,130.0
rate Revolver Average balances $514.9 $145.4 $0.0 $0.0 $0.0
– Forecast the interest Interest rate 0.23% 0.23% 0.23% 0.23% 0.23%
expense Interest expense $1.2 $0.3 $0.0 $0.0 $0.0
– Calculate Total Interest
Long-term debt Average balances $6,474.5 $6,112.5 $5,768.5 $5,490.5 $5,234.5
Expense
Interest rate References 3.76% 3.76% 3.76% 3.76% 3.76%
Interest expense $243.4 $229.8 $216.9 $206.4 $196.8
Total interest expense $244.6 $230.2 $216.9 $206.4 $196.8
Cash Average balances Calculations

Interest rate
Interest (income)

Interest rate on revolver 0.23% 0.23% 0.23% 0.23% 0.23%
Partial screen shot for training purposes Interest rate on long-term debt Assumptions / Drivers 3.76% 3.76% 3.76% 3.76% 3.76%

139
Debt: Calculate Interest Income
Revolver Average balances $514.9 $145.4 $0.0 $0.0 $0.0
Step 65: Calculate Interest rate 0.23% 0.23% 0.23% 0.23% 0.23%
interest income from Interest expense $1.2 $0.3 $0.0 $0.0 $0.0
Cash balances
Interest rate 3.76% 3.76% 3.76% 3.76% 3.76%
– Calculate the average Interest expense $243.4 $229.8 $216.9 $206.4 $196.8
balance
– Keep the formula Total interest expense $244.6 $230.2 $216.9 $206.4 $196.8
consistent throughout the
Cash
Debt and Interest Average balances $261.5 $289.3 $622.3 $1,203.7 $1,845.8
forecast periods
Schedule Interest rate References 0.50% 0.50% 0.50% 0.50% 0.50%
– Make a reasonable
Interest (income) ($1.3) ($1.4) ($3.1) ($6.0) ($9.2)
rate Ratios and assumptions
Calculations; Watch your signs!
– Forecast interest income Minimum cash balance $250.0 $250.0 $250.0 $250.0 $250.0
Interest rate on long-term debt 3.76% 3.76% 3.76% 3.76% 3.76%
Interest rate on cash 0.50% 0.50% 0.50% 0.50% 0.50%
Cash $460.0 $281.0 $273.0 $250.0 $328.6 $916.1 $1,491.4 $2,200.3

Accounts receivable, net
Balance Sheet 1,188.0 1,454.0 1,424.0 1,461.0 1,504.8 1,549.9 1,596.4 1,644.3
Inventories 1,174.0 1,365.0 1,248.0 1,313.2 1,352.6 1,393.2 1,435.0 1,478.0

140
Debt: Completed Schedule
Completed Debt and Hist 1 Hist 2 Hist 3 Proj 1 Proj 2 Proj 3 Proj 4 Proj 5
Interest Schedule Cash flow available for financing activities $1,398.5 $1,496.7 $1,535.9 $1,576.6 $1,618.8
Dividends (684.3) (692.3) (695.4) (698.3) (700.9)
BEFORE linking Plus: beginning cash balance 273.0 250.0 328.6 916.1 1,491.4
interest to the IS Cash available for debt repayment $737.2 $804.4 $919.1 $1,544.4 $2,159.3
Cash availableRight border on the
for revolver last $448.2 $369.4 $666.1 $1,241.4 $1,950.3
Revolver Make this the LAST thing
you do on the schedule!
Ending balance $234.0 $1,065.0 $739.0 $290.8 $0.0 $0.0 $0.0 $0.0
Long-term debt
Issuance 0.0 0.0 0.0 0.0 0.0
(Repayment) (289.0) (435.0) (253.0) (303.0) (209.0)
Ending balance $5,798.0 $6,837.0 $6,619.0 $6,330.0 $5,895.0 $5,642.0 $5,339.0 $5,130.0

Interest rate 0.23% 0.23% 0.23% 0.23% 0.23%

Interest rate 3.76% 3.76% 3.76% 3.76% 3.76%
Cash Average balances $261.5 $289.3 $622.3 $1,203.7 $1,845.8

Interest rate 0.50% 0.50% 0.50% 0.50% 0.50%
Interest (income) ($1.3) ($1.4) ($3.1) ($6.0) ($9.2)


141
Debt: Link Projected Interest to IS
Step 66: Link interest Hist 1 Hist 2 Hist 3 Proj 1 Proj 2 Proj 3 Proj 4 Proj 5 CAGR
expense and interest
Sales $13,198.0 $14,197.0 $14,792.0 $15,235.8 $15,692.8 $16,163.6 $16,648.5 $17,148.0 3.0%
(income) to the income Cost of sales (excluding depreciation) 7,266.0 8,005.0 8,531.0 8,714.9 8,976.3 9,245.6 9,523.0 9,808.6
EBITDA 2,539.0 2,603.0 2,668.0 2,833.9 2,918.9 3,006.4 3,096.6 3,189.5 3.6%
Depreciation 367.0 427.0 453.0 530.3 564.9 618.3 674.3 733.1

Amortization Right border on the last 2.0 4.0 9.0 9.0 9.0 9.0 9.0 9.0
EBIT historical year as a divider. 2,170.0 2,172.0 2,206.0 2,294.6 2,344.9 2,379.2 2,413.4 2,447.4 2.1%
Interest expense you do on the schedule! 233.0 261.0 235.0 244.7 230.4 217.0 206.4 196.8
References
Interest (income) (11.0) (9.0) (7.0) (1.3) (1.3) (2.2) (4.4) (7.3)
Pretax income 1,927.0 1,939.0 1,969.0 2,051.1 2,115.8 2,164.4 2,211.4 2,257.9 2.8%
Income taxes 566.3 585.9 560.5 605.1 639.0 668.8 698.8 729.3

Gross margin 44.9% 43.6% 42.3% 42.8% 42.8% 42.8% 42.8% 42.8% 0.0%
Challenge EBIT Margin 16.4% 15.3% 14.9% 15.1% 14.9% 14.7% 14.5% 14.3%
– What impact will linking these
numbers have on the model?

142
Debt: After Linking Interest
Completed Debt and Hist 1 Hist 2 Hist 3 Proj 1 Proj 2 Proj 3 Proj 4 Proj 5
Interest Schedule Cash flow available for financing activities $1,226.9 $1,336.8 $1,387.4 $1,438.4 $1,490.5
Dividends (611.7) (624.7) (632.6) (639.8) (646.6)
AFTER linking interest Plus: beginning cash balance 273.0 250.0 250.0 639.1 1,134.7
to the IS Cash available for debt repayment $638.2 $712.1 $754.8 $1,187.7 $1,728.6
Cash available for revolver $349.2 $277.1 $501.8 $884.7 $1,519.6
Revolver
Issuance / (repayment) of revolver (349.2) (277.1) (112.7) 0.0 0.0
Ending balance $234.0 $1,065.0 $739.0 $389.8 $112.7 $0.0 $0.0 $0.0
Long-term debt
Issuance 0.0 0.0 0.0 0.0 0.0
(Repayment) (289.0) (435.0) (253.0) (303.0) (209.0)
Ending balance $5,798.0 $6,837.0 $6,619.0 $6,330.0 $5,895.0 $5,642.0 $5,339.0 $5,130.0

Interest rate 0.23% 0.23% 0.23% 0.23% 0.23%

Interest rate 3.76% 3.76% 3.76% 3.76% 3.76%
Cash Average balances $261.5 $250.0 $444.6 $886.9 $1,452.1

Interest rate 0.50% 0.50% 0.50% 0.50% 0.50%
Interest (income) ($1.3) ($1.3) ($2.2) ($4.4) ($7.3)


143
The Finished Product
Sales
Interest Expense
COGS Income Debt &
Working Capital
Amortization
Working Debt
Working
Income
Capital

Proceeds, Dividends
PP&E
Equity
Beginning Ending
Other LT Items


144

FM Core 1 Day v24.0

Uploaded by

Copyright:

Available Formats

FM Core 1 Day v24.0

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

FM Core 1 Day v24.0

Uploaded by

Copyright:

Available Formats

Financial Modeling

Constructing and Utilizing a Financial Model

We Unlock Career Potential

© 1999-2024 Training The Street LLC

We Unlock Career Potential

corporate and educational clients. Accounting Commercial Real Estate

Corporate Valuation FIG Analysis

Software (SaaS) Analysis

Cash Mgt & Treasury Services

Model Building Solutions

© 1999-2024 Training The Street LLC

© 1999-2024 Training The Street LLC

© 1999-2024 Training The Street LLC

Vertical / Tower Model Horizontal / Matrix Model

© 1999-2024 Training The Street LLC

Working Income Debt &

PP&E, Cash Flow Shareholders’

Intangibles, Balance Other Long

© 1999-2024 Training The Street LLC

© 1999-2024 Training The Street LLC

Formatting Best Practice Color Codes:

Typically, there isn’t enough

Sales $13,198.0 $14,197.0 $14,792.0 $15,235.8 $15,692.8 $16,163.6 $16,648.5 $17,148.0

Beginning PP&E, net $3,856.0 $4,011.3 $4,152.5 $4,298.0 $4,447.9

$ sign repeated because $ data is

© 1999-2024 Training The Street LLC

© 1999-2024 Training The Street LLC

© 1999-2024 Training The Street LLC

A. Operating Working Capital

© 1999-2024 Training The Street LLC

Step 2: Balance Sheet Operating working capital represents the

© 1999-2024 Training The Street LLC

Step 2: Balance Sheet Property, plant, and equipment represents the

© 1999-2024 Training The Street LLC

Step 2: Balance Sheet Intangible assets have no physical form;

© 1999-2024 Training The Street LLC

Step 2: Balance Sheet Other long-term items may include: deferred

© 1999-2024 Training The Street LLC

Step 2: Balance Sheet What causes equity to change over time?

© 1999-2024 Training The Street LLC

Step 2: Balance Sheet Shares Outstanding

© 1999-2024 Training The Street LLC

Step 2: Balance Sheet Temporarily flatlining debt at this stage of the

Revolver 234.0 1,065.0 739.0 739.0 739.0 739.0 739.0 739.0

• Temporarily flatline all debt items on the balance sheet

© 1999-2024 Training The Street LLC

© 1999-2024 Training The Street LLC

Definite life intangibles

Total stockholders' equity

© 1999-2024 Training The Street LLC

Cash Flow from

Cash Flow from

Cash Flow from

© 1999-2024 Training The Street LLC

Net change in cash (23.0) 0.0 389.1 495.6 634.9

Cash $460.0 $281.0 $273.0 $250.0 $250.0 $639.1 $1,134.7 $1,769.6

© 1999-2024 Training The Street LLC

Cash Flow Available for

Cash Flow for Financing Activities

© 1999-2024 Training The Street LLC