Overview Historical FCF Forecasting Overview Simple Forecast Complex Forecast Future FCFs Valuation

Free Cash Flow Estimation and Forecasting

Calculation and Projection

Nick DeRobertis1
1 University
of Florida
Department of Finance, Insurance, and Real Estate

June 9, 2021

DeRobertis (UF) FCF June 9, 2021 1 / 47

Overview Historical FCF Forecasting Overview Simple Forecast Complex Forecast Future FCFs Valuation

Table of Contents

1 Overview

2 Calculating Historical Free Cash Flows

3 Approaches to Forecasting

4 Forecasting Simple Time-Series

5 Forecasting Complex Time-Series

6 Forecasting Free Cash Flows

7 Using the Forecasted FCFs in the DCF Model

DeRobertis (UF) FCF June 9, 2021 2 / 47

Overview Historical FCF Forecasting Overview Simple Forecast Complex Forecast Future FCFs Valuation

FCF Overview

There are two main parts to the FCF part of the DCF model

First is to find all the historical FCFs. This is the straightforward part
of plugging values into set calculations.

The more difficult and important part is projecting future cash flows

We will discuss a variety of approaches for this

DeRobertis (UF) FCF June 9, 2021 3 / 47

Overview Historical FCF Forecasting Overview Simple Forecast Complex Forecast Future FCFs Valuation

What is FCF?

Free cash flow (FCF) represents cash a company earns after paying
for its operations.

It is not to be confused with net income, which amortizes costs across

years and includes non-cash expenses

FCF represents only the cash flows from that year, and so can be a lot
more variable than net income

FCF is the actual cash earned, and so it is what we should use for
valuation

DeRobertis (UF) FCF June 9, 2021 4 / 47

Overview Historical FCF Forecasting Overview Simple Forecast Complex Forecast Future FCFs Valuation

Historical FCFs
We can follow a simple formula
to get FCFs from historical
financials
All we have to do is calculate
the formula for each year of
historical data
Each of the components to this
calculation besides net income
themselves require a calculation.
In the Historical FCF section we
will cover each component.
The formula is all about
reversing the non-cash
adjustments to net income

DeRobertis (UF) FCF June 9, 2021 5 / 47

Overview Historical FCF Forecasting Overview Simple Forecast Complex Forecast Future FCFs Valuation

Forecasting FCFs

The challenge in the FCF model is to project the cash flows

There are two main approaches to getting future FCFs:

forecast the FCFs and forecast the financial statements.

Forecasting FCFs is much easier but is not as accurate, both due to

lack of granularity and due to typically uneven FCFs

We will use time-series methods to forecast the financial statements,

either in the levels of the item, the growth of the item, or as a
percentage of another item

DeRobertis (UF) FCF June 9, 2021 6 / 47

Overview Historical FCF Forecasting Overview Simple Forecast Complex Forecast Future FCFs Valuation

Table of Contents

1 Overview

2 Calculating Historical Free Cash Flows

3 Approaches to Forecasting

4 Forecasting Simple Time-Series

5 Forecasting Complex Time-Series

6 Forecasting Free Cash Flows

7 Using the Forecasted FCFs in the DCF Model

DeRobertis (UF) FCF June 9, 2021 7 / 47

Overview Historical FCF Forecasting Overview Simple Forecast Complex Forecast Future FCFs Valuation

Non-Cash Expenses

Calculate Non-Cash Expenses

Adjustments = depreciation + amortization + stock-based compensation +
impairment charges + gains/losses on investments

Non-cash expenses is just the sum of all items on the income

statement that do not affect cash.

This includes depreciation, amortization, stock-based compensation,

impairment charges, and gains/losses on investments

DeRobertis (UF) FCF June 9, 2021 8 / 47

Overview Historical FCF Forecasting Overview Simple Forecast Complex Forecast Future FCFs Valuation

Change in Net Working Capital

Calculate Change in NWC

∆NWC = NWCt − NWCt−1 (1)
NWC = Accounts Receivable + Inventory − Accounts Payable (2)

Net Working Capital (NWC) represents cash actively tied up in daily

transactions
Cacluating the change in NWC requires information from this period
as well as last period.
First, calculate the NWC in each period using accounts receivable,
inventory, and accounts payable.
Then, take the difference between this period’s NWC and last to get
the change
DeRobertis (UF) FCF June 9, 2021 9 / 47
Overview Historical FCF Forecasting Overview Simple Forecast Complex Forecast Future FCFs Valuation

Capital Expenditures

Calculate CapEx
CapEx = ∆PPE + Depreciation & Amortization (3)

Capital Expenditures (CapEx) are outlays for fixed assets which get
used over time, such as buildings or machinery.

The change in Property, Plant and Equipment from the balance sheet
can be used to estimate CapEx.

Then you just need to add back the current depreciation &
amortization, as they decreased PPE even though no cash exchanged
hands

DeRobertis (UF) FCF June 9, 2021 10 / 47

Overview Historical FCF Forecasting Overview Simple Forecast Complex Forecast Future FCFs Valuation

Put it All Together

Calculating FCF
FCF = Net Income + Non-Cash Expenses − ∆NWC − CapEx (4)

Again, the FCF formula takes net income and reverses the non-cash
adjustments

Add back the non-cash expenses because no actual cash was spent

Decrease by the change in NWC as an increase means additional cash

was used for operations

Decrease by CapEx because this cash was spent for a new building,
machinery, etc.

DeRobertis (UF) FCF June 9, 2021 11 / 47

Overview Historical FCF Forecasting Overview Simple Forecast Complex Forecast Future FCFs Valuation

Example for Calculating FCFs

Two Ways to Calculate FCFs in Python

Go to the course site and download the files in Historical FCF
There should be a Jupyter notebook as well as a data file
We will go through a couple approaches to calculating FCFs in
Python.

DeRobertis (UF) FCF June 9, 2021 12 / 47

Overview Historical FCF Forecasting Overview Simple Forecast Complex Forecast Future FCFs Valuation

Calculate FCF Lab, Level 1

Free Cash Flow Calculation, Level 1

1 Calculate free cash flow from the following information:
2 Net income is 300, the total of non-cash expenditures is 100, the
changes in accounts receivable, inventory, accounts payable, and PPE
are 1000, 500, 800, and 2000, and depreciation & amortization is 200.
Level 2: Slide 50 Answers 1: Slide 51 Resources: Slide 53

DeRobertis (UF) FCF June 9, 2021 13 / 47

Overview Historical FCF Forecasting Overview Simple Forecast Complex Forecast Future FCFs Valuation

Table of Contents

1 Overview

2 Calculating Historical Free Cash Flows

3 Approaches to Forecasting

4 Forecasting Simple Time-Series

5 Forecasting Complex Time-Series

6 Forecasting Free Cash Flows

7 Using the Forecasted FCFs in the DCF Model

DeRobertis (UF) FCF June 9, 2021 14 / 47

Overview Historical FCF Forecasting Overview Simple Forecast Complex Forecast Future FCFs Valuation

Overview of the Methods

As mentioned in the intro, we will use time-series methods on the levels of
the item, the growth of the item, or as a percentage of another item
Time-series methods are about numerically estimating future values based
on past values
Levels mean we forecast the numbers of the item itself, e.g. sales was 1M
two years ago and 1.5M last year so we feed those numbers into our
time-series model and predict 2M for the next year
Growth means to first calculate the historical growth rate of the item, then
forecast that. E.g. sales grew 10% two years ago and 8% last year so we
expect it to grow 6% this year
Percentage of item methods link an item to another item. E.g. setting cost
of goods sold to 60% of sales. That percentage still needs to be forecasted
Any forecast can be adjusted by the analyst’s qualitative projections of the
future. The forecast method may also have to be chosen with the qualitative
analysis in mind
DeRobertis (UF) FCF June 9, 2021 15 / 47
Overview Historical FCF Forecasting Overview Simple Forecast Complex Forecast Future FCFs Valuation

What Are Time-Series Methods?

Time-series methods seek to

predict the future using the past

There is a large variety of

possible time-series models to
use for forecasting

They each use different

characteristics of the past to
assist in predicting the future

DeRobertis (UF) FCF June 9, 2021 16 / 47

Overview Historical FCF Forecasting Overview Simple Forecast Complex Forecast Future FCFs Valuation

What are the Time-Series Models?

The simplest models are to use the average of historical values or the
most recent value as the prediction for all future values

A more realistic model will also include estimating the trend or

growth of the historical values and applying that to the future

More advanced models use autoregressive terms (recent values predict

future values), moving average terms (recent errors predict future
values), or conditional heteroskedasticity terms (changing variance
over time)

Examples of these advanced models include AR, MA, ARMA, ARCH,

GAM, and many more

DeRobertis (UF) FCF June 9, 2021 17 / 47

Overview Historical FCF Forecasting Overview Simple Forecast Complex Forecast Future FCFs Valuation

Which Time-Series Model to Use?

The choice of a time-series model will depend on the amount of data,

the frequency of the data, and the historical patterns in the data

If the data is historically constant or expected to be constant in the

future, using the average or most recent value should be enough

If the data follows a defined trend or growth and doesn’t have

historical patterns, then using the trend or growth models should be
enough

If there are historical patterns in the data, such as seasonality, then

more advanced models are required.

DeRobertis (UF) FCF June 9, 2021 18 / 47

Overview Historical FCF Forecasting Overview Simple Forecast Complex Forecast Future FCFs Valuation

Steps to Forecasting

The best place to start is to examine the history either by a plot or

just by looking at the numbers if you only have a few

Based on the amount of historical data, any perceived patterns in the

historical data, and any qualitative knowledge of the data,
choose a time-series model.

Fit the time-series model on historical data, then predict the future
values using the fitted model

Finish by examining the forecast to ensure it worked as intended,

typically via a plot.

DeRobertis (UF) FCF June 9, 2021 19 / 47

Overview Historical FCF Forecasting Overview Simple Forecast Complex Forecast Future FCFs Valuation

What to Forecast?

When we forecast levels, just use the forecasted values and you are
done

When we forecast growth, calculate the historical growth, run the

forecast on that, then apply the predicted future growth from the
final historical period to generate the forecasted levels

For percentage of item methods, calculate the historical percentage of

the item, forecast future item percentages, then use these in
combination with the forecast of the referenced item to generate the
prediction

E.g. use the average approach to say historically COGS was 40% of
sales, and so estimate COGS as 40% of sales going forward. Multiply
the forecasted sales by 40% to get the COGS

DeRobertis (UF) FCF June 9, 2021 20 / 47

Overview Historical FCF Forecasting Overview Simple Forecast Complex Forecast Future FCFs Valuation

Table of Contents

1 Overview

2 Calculating Historical Free Cash Flows

3 Approaches to Forecasting

4 Forecasting Simple Time-Series

5 Forecasting Complex Time-Series

6 Forecasting Free Cash Flows

7 Using the Forecasted FCFs in the DCF Model

DeRobertis (UF) FCF June 9, 2021 21 / 47

Overview Historical FCF Forecasting Overview Simple Forecast Complex Forecast Future FCFs Valuation

Using the Historical Average Model

Historical Average Model

T
1 X
yT +n = y t + t (5)
T
t=0

t: Current time period

T : Last time period of historical data
yt : The current value of the data
yT : The last historical value of the data
n: Number of periods forecasted

Fit: To fit the historical average model, take an average of the

historical values
Predict: To predict, use that average value for all future values
DeRobertis (UF) FCF June 9, 2021 22 / 47
Overview Historical FCF Forecasting Overview Simple Forecast Complex Forecast Future FCFs Valuation

Using the Recent Value Model

Recent Value Model

yt = yt−1 + t (6)

Fit: To fit the most recent value model, take the latest value.

Predict: To predict, use that latest value for all future values

DeRobertis (UF) FCF June 9, 2021 23 / 47

Overview Historical FCF Forecasting Overview Simple Forecast Complex Forecast Future FCFs Valuation

Using the Trend Model

Trend Model
yt = a + βt + t (7)

Fit: Run an OLS regression with a constant and time as the

independent variable, where time is measured in number of periods
since the beginning

Predict: For each t you want to predict, calculate a + βt

DeRobertis (UF) FCF June 9, 2021 24 / 47

Overview Historical FCF Forecasting Overview Simple Forecast Complex Forecast Future FCFs Valuation

Using the CAGR Model

CAGR
yT n1 f
yT +f = yT ∗ ( ) (8)
y0

Fit: Calculate the compounded annual growth rate (CAGR) as

1
yT
y0
n −1

Predict: Calculate future periods by compounding CAGR on the

latest historical period yT +f = yT ∗ (1 + CAGR)f

DeRobertis (UF) FCF June 9, 2021 25 / 47

Overview Historical FCF Forecasting Overview Simple Forecast Complex Forecast Future FCFs Valuation

Forecasting Simple Time-Series in both Excel and Python

Example for Simple Time-Series Forecasting

Go to the course site and download the files in Examples > DCF >
Forecasting > Simple
There should be a Jupyter notebook as well as two Excel files. Place
these all in the same folder
We will walk through ”Sales COGS Forecasted.xlsx” to show
forecasting in Excel, and ”Forecast Sales COGS Simple.ipynb” for
forecasting in Python. ”Sales COGS.xlsx” contains the source data

DeRobertis (UF) FCF June 9, 2021 26 / 47

Overview Historical FCF Forecasting Overview Simple Forecast Complex Forecast Future FCFs Valuation

Simple Forecast Lab

Forecasting Simple Time-Series

1 Go to the course site and download ”Debt Interest.xlsx”
2 Forecast the next value of total debt using trend regression approach
3 Forecast the next value of interest using the four approaches
(average, recent, trend, CAGR)
4 Forecast the next value of interest using the % of total debt method,
with the percentages forecasted using the four approaches (average,
recent, trend, CAGR)
Answers: Slide 54 Resources: Slide 55

DeRobertis (UF) FCF June 9, 2021 27 / 47

Overview Historical FCF Forecasting Overview Simple Forecast Complex Forecast Future FCFs Valuation

Forecasting Financial Statements with Simple Time-Series

in Python

Use finstmt for Financial Statement Forecasting

Go to the course site and download ”Forecasting Financial
Statements.ipynb”
We will walk through using finstmt to forecast financial statements
using simple time-series models

DeRobertis (UF) FCF June 9, 2021 28 / 47

Overview Historical FCF Forecasting Overview Simple Forecast Complex Forecast Future FCFs Valuation

Table of Contents

1 Overview

2 Calculating Historical Free Cash Flows

3 Approaches to Forecasting

4 Forecasting Simple Time-Series

5 Forecasting Complex Time-Series

6 Forecasting Free Cash Flows

7 Using the Forecasted FCFs in the DCF Model

DeRobertis (UF) FCF June 9, 2021 29 / 47

Overview Historical FCF Forecasting Overview Simple Forecast Complex Forecast Future FCFs Valuation

Why Does it get so Complicated?

Shown to the left is Walmart’s

quarterly sales

You can see that a plain trend

line is never going to accurately
forecast these values

Advanced time-series models

can capture these characteristics
easily, and many more patterns

DeRobertis (UF) FCF June 9, 2021 30 / 47

Overview Historical FCF Forecasting Overview Simple Forecast Complex Forecast Future FCFs Valuation

Explaining Seasonal Data

There is a distinct seasonality
to Walmart’s quarterly sales
The time-series model used to
create the plot on the prior slide
split the history into two parts:
a trend component and an
annual seasonality component
Walmart’s sales are high at the
end of January and neutral for
the other quarters
By plotting components of the
time-series model, the analyst
can gain a greater
understanding of the business.

DeRobertis (UF) FCF June 9, 2021 31 / 47

Overview Historical FCF Forecasting Overview Simple Forecast Complex Forecast Future FCFs Valuation

Predicting Complex Time-Series

So you’ve determined a trend alone will not fit the historical data.
What are next steps?

Generally fitting the best time-series model is an involved process

which requires substantial knowledge of how the models work, see
this blog post for details

An easier version is to use an OLS regression model with time and

dummy variables for month of year, etc., we can call this the
quarterly seasonal trend model.

The easiest version is to let time-series software, such as prophet

make the choice for you

DeRobertis (UF) FCF June 9, 2021 32 / 47

Overview Historical FCF Forecasting Overview Simple Forecast Complex Forecast Future FCFs Valuation

Using the Quarterly Seasonal Trend Model

Quarterly Seasonal Trend Model

yt = a + βt t + βd1 D1 + βd2 D2 + βd3 D3 + βd4 D4 + t (9)

Fit: Create dummy variables for the quarters, then run an OLS
regression with the number of time periods as well as the four dummy
variables as the X variables

Predict: For each t you want to predict, calculate a + βt adding the

appropriate dummy for the quarter of the period.

DeRobertis (UF) FCF June 9, 2021 33 / 47

Overview Historical FCF Forecasting Overview Simple Forecast Complex Forecast Future FCFs Valuation

Forecasting Complex Time-Series in Python

Example for Complex Time-Series Forecasting

Go to the course site and download the files in Examples > DCF >
Forecasting > Complex
There should be a Jupyter notebook as well as two Excel files. Place
these all in the same folder
We will walk through ”Forecasting Quarterly Financial
Statements.ipynb” to show forecasting in Python using both the
Quarterly Seasonal Trend Model and the automated software
approach.

DeRobertis (UF) FCF June 9, 2021 34 / 47

Overview Historical FCF Forecasting Overview Simple Forecast Complex Forecast Future FCFs Valuation

Complex Forecast Lab

Forecasting Complex Time-Series

1 Go to the course site and download ”CAT Balance Sheet.xlsx” and
”CAT Income Statement.xlsx”
2 Forecast the next four periods (one year) of cash using both the
Quarterly Seasonal Trend Model and the automated software
approach.
3 Plot both forecasts to see how they worked.
Answers: Slide 56 Resources: Slide 57

DeRobertis (UF) FCF June 9, 2021 35 / 47

Overview Historical FCF Forecasting Overview Simple Forecast Complex Forecast Future FCFs Valuation

Table of Contents

1 Overview

2 Calculating Historical Free Cash Flows

3 Approaches to Forecasting

4 Forecasting Simple Time-Series

5 Forecasting Complex Time-Series

6 Forecasting Free Cash Flows

7 Using the Forecasted FCFs in the DCF Model

DeRobertis (UF) FCF June 9, 2021 36 / 47

Overview Historical FCF Forecasting Overview Simple Forecast Complex Forecast Future FCFs Valuation

What to Forecast?

As mentioned in the intro, we can either directly forecast FCFs, or

forecast the financial statements, then calculate future FCFs from the
future financial statements

Forecasting FCFs doesn’t allow the analyst control over individual line
items. Therefore it is generally preferred to forecast financial
statements.

If you have a short amount of time to put together the model, it can
make sense as it is less steps and it requires less knowledge of the
company.

It can also make sense to do a FCF forecast alongside the financial

statement forecast, as a check on your valuation.

DeRobertis (UF) FCF June 9, 2021 37 / 47

Overview Historical FCF Forecasting Overview Simple Forecast Complex Forecast Future FCFs Valuation

Which Line Items to Forecast?

Assuming you are going with forecasting financial statements, you

should forecast only line items which cannot be calculated from other
line items

For example, sales, COGS, SG&A should be forecasted, not operating

profit

You should set your model up so that these calculatable items are
calculated in the historicals, then carry that through to the forecasted

Set items as a percentage of other items when it is logical that they

should scale together. For example, interest expense should be
forecasted as a percentage of total debt, as if the company takes on
more debt, interest expense should increase proportionally

DeRobertis (UF) FCF June 9, 2021 38 / 47

Overview Historical FCF Forecasting Overview Simple Forecast Complex Forecast Future FCFs Valuation

Don’t Be Out of Balance

Despite forecasting line items individually, you must attempt to keep

the balance sheet balanced in the future

If your forecast shows assets growing to be much greater than

liabilities and equity, that implies that the company will need to raise
additional funds to finance the growth in the assets. Usually
increasing debt is the solution.

If your forecast shows assets lower than liabilities and equity, it means
that the profits or other sources of funds are not being properly
allocated to the assets side. Usually increasing cash is the solution

DeRobertis (UF) FCF June 9, 2021 39 / 47

Overview Historical FCF Forecasting Overview Simple Forecast Complex Forecast Future FCFs Valuation

Balancing the Balance Sheet

The general process is to run your forecasts, then use another approach to
adjust either cash or debt to make the balance sheet balance.

These adjusted values are called ”plugs” because we are ”plugging” in

whatever makes the forecast valid

To make the adjustment in Excel, calculate the absolute value of the

difference between assets and liabilities + equity, then use goal seek (single
year forecast) or solver (multi-year forecast) to minimize the value

In Python, the steps would be similar, only using scipy minimize instead of
solver, but finstmt handles this automatically for you. When you run a
forecast, it will balance the balance sheet without any further action from
you. You can change how accurate it is by setting bs_diff_max and you can
change which line items are used as plugs in the forecast config

DeRobertis (UF) FCF June 9, 2021 40 / 47

Overview Historical FCF Forecasting Overview Simple Forecast Complex Forecast Future FCFs Valuation

Table of Contents

1 Overview

2 Calculating Historical Free Cash Flows

3 Approaches to Forecasting

4 Forecasting Simple Time-Series

5 Forecasting Complex Time-Series

6 Forecasting Free Cash Flows

7 Using the Forecasted FCFs in the DCF Model

DeRobertis (UF) FCF June 9, 2021 41 / 47

Overview Historical FCF Forecasting Overview Simple Forecast Complex Forecast Future FCFs Valuation

What we use FCFs For

As mentioned in the intro to the DCF model, we can value any asset
by taking the present value of future cash flows

These forecasted FCFs represent the future cash flows for the
company

But we have a limited forecast period, beyond which forecasts are

getting too innaccurate, typically 5 years at most. So what happens
after 5 years? The company will probably still be earning FCFs in the
future.

We can calculate a terminal value for the company, which is an

estimate of how much the company would be sold for if it was sold at
the end of the forecast period. In other words, it is the future
predicted enterprise value.

DeRobertis (UF) FCF June 9, 2021 42 / 47

Overview Historical FCF Forecasting Overview Simple Forecast Complex Forecast Future FCFs Valuation

How to Get the Terminal Value

The terminal value is an enterprise value at the end of the forecast

period. But the bulk of the DCF model is getting to an enterprise
value, so we have to use a different method to estimate the terminal
value otherwise we would have an infinitely nested DCF model that
could never be solved.

The two common methods for estimating the terminal value in a DCF
model are exit multiples and perpetuity growth

The exit multiple method uses current values of valuation ratios and
applies them to the future financials

The perpetuity growth method assumes that the FCFs will grow at a
constant rate after the final forecast period.

DeRobertis (UF) FCF June 9, 2021 43 / 47

Overview Historical FCF Forecasting Overview Simple Forecast Complex Forecast Future FCFs Valuation

Finding the Terminal Value via Exit Multiples

There are typically publicly available valuation ratios for public

companies such as EV/EBIT, EV/EBITDA, EV/Sales, EV/FCF, and
P/E
The idea behind this approach is to use those ratios applied to your
final period projected financials.
Each ratio will yield a different terminal value, leading to a different
final stock price in your model. It is typical to report results with the
different measures to get a range.
For all the EV ratios, multiply the statement item by the ratio to get
the EV, then adjust the EV using final forecasted values to get the
equity value and stock price
For P/E, calculate final period earnings per share and multiply by the
ratio to get the stock price
DeRobertis (UF) FCF June 9, 2021 44 / 47
Overview Historical FCF Forecasting Overview Simple Forecast Complex Forecast Future FCFs Valuation

Finding the Terminal Value via Perpetuity Growth

The perpetuity growth model follows the same mathematics as the

dividend discount model

We just assume that the last period’s FCF continues to grow at some
terminal growth rate which we set.
FCF (1+g)
TV = WACC−g

The stock price is highly sensitive to the choice of terminal growth

rate, so there should absolutely be sensitivity analysis and Monte
Carlo simulations varying it

Typical terminal growth rates are around 3%, approximately GDP

growth rate

DeRobertis (UF) FCF June 9, 2021 45 / 47

Overview Historical FCF Forecasting Overview Simple Forecast Complex Forecast Future FCFs Valuation

Finding EV Using TV and FCFs

The last step to get the current enterprise value is to combine the
FCFs with the TV

The TV cash flow should come in the final forecast period, such that
the final cash flow is FCF + TV

Take the NPV of the cash flows, including the TV

Follow the adjustments described in the intro lecture to get the stock
price from that

DeRobertis (UF) FCF June 9, 2021 46 / 47

Overview Historical FCF Forecasting Overview Simple Forecast Complex Forecast Future FCFs Valuation

Terminal Values Lab

DCF Stock Price using Terminal Values

1 Calculate possible stock prices today for a hypothetical company. Use
EV/EBITDA, EV/Sales, EV/FCF, and P/E and the perpetuity growth method to
determine five different possible terminal values. You have already determined that
the next 5 years FCFs will be $1,324M in each year.
2 EV/EBITDA is 18.58, EV/Sales is 1.92, EV/FCF is 11.82, and P/E is 39.30.
3 Final period forecasted financial statement values are as follows: EBITDA is
$1,500M, sales is $7,898M, and net income is $232M
4 Total debt is $11,631M, and cash is $4,867M, both current and final period
forecasted
5 Shares outstanding is $561M and WACC is 10.0% for the entire time period
6 The terminal growth rate is 3.0%
7 You can assume the next free cash flow is one year away.

Answers: Slide 58 Resources: Slide 59

DeRobertis (UF) FCF June 9, 2021 47 / 47

Lecture Resources (1/2)

Lecture Resources (1/2)

1 Slides - Free Cash Flow Estimation and Forecasting
2 Lecture Notes - Free Cash Flow Estimation and Forecasting
3 WMT Income Statement
4 WMT Balance Sheet
5 Calculating Historical FCF
6 Exxon-Mobil Financials
7 finstmt Documentation
8 Sales COGS
9 Sales COGS Forecasted
10 Forecast Sales COGS Simple

DeRobertis (UF) FCF June 9, 2021 48 / 47

Lecture Resources (2/2)

Lecture Resources (2/2)

1 Debt Interest
2 Forecasting Financial Statements
3 CAT Balance Sheet
4 CAT Income Statement
5 WMT Balance Sheet
6 WMT Income Statement
7 Forecasting Quarterly Financial Statements
8 CAT Balance Sheet
9 CAT Income Statement

DeRobertis (UF) FCF June 9, 2021 49 / 47

Calculate FCF Lab, Level 2

Free Cash Flow Calculation, Level 2

1 Load in the income statement and balance sheet data associated with
Project 3, ”WMT Balance Sheet.xlsx” and ”WMT Income
Statement.xlsx”
2 Calculate the free cash flows from these data. Note that some items
are missing in these data such as depreciation. You will just need to
exclude any missing items from your calculation
3 Get the FCFs for 2019-04-30 and 2019-07-31.
Level 1: Slide 13 Answers 2: Slide 52 Resources: Slide 53

DeRobertis (UF) FCF June 9, 2021 50 / 47

Calculate FCF Lab, Answers for Level 1

Free Cash Flow Calculation, Answers for Level 1

1 The NWC is $700
2 The CapEx is $2,200
3 The FCF is $-2,500
Level 1: Slide 13 Level 2: Slide 50 Resources: Slide 53

DeRobertis (UF) FCF June 9, 2021 51 / 47

Calculate FCF Lab, Answers for Level 2

Free Cash Flow Calculation, Answers for Level 2

1 The FCF for 2019-04-30 is $-11,495,000,000
2 The FCF for 2019-07-31 is $4,327,000,000
Level 1: Slide 13 Level 2: Slide 50 Resources: Slide 53

DeRobertis (UF) FCF June 9, 2021 52 / 47

Calculate FCF Lab Resources

Free Cash Flow Calculation Resources

1 Slides - Free Cash Flow Estimation and Forecasting
2 WMT Balance Sheet
3 WMT Income Statement
Level 1: Slide 13 Level 2: Slide 50 Answers 1: Slide 51
Answers 2: Slide 52

DeRobertis (UF) FCF June 9, 2021 53 / 47

Simple Forecast Lab, Answers

Forecasting Simple Time-Series, Answers

1 The forecasted value of total debt should be $6,867
2 The directly forecasted values of interest should be $1,600, $1,900,
$2,300, and $2,391, for average, recent, trend, CAGR, respectively
3 The % of debt forecasted values of interest should be $2,072, $2,139,
$2,379, and $2,312, for average, recent, trend, CAGR, respectively
Exercise: Slide 27 Resources: Slide 55

DeRobertis (UF) FCF June 9, 2021 54 / 47

Simple Forecast Lab Resources

Forecasting Simple Time-Series Resources

1 Slides - Free Cash Flow Estimation and Forecasting
2 Debt Interest
Exercise: Slide 27 Answers: Slide 54

DeRobertis (UF) FCF June 9, 2021 55 / 47

Complex Forecast Lab, Answers

Forecasting Complex Time-Series, Answers

1 The forecasted values of cash using the Quarterly Seasonal Trend
Model should be $8,454,920,455, $8,833,593,182, $8,869,693,182,
and $10,251,393,182
2 The forecasted values of cash using the automated approach should
be $8,071,641,657, $8,185,822,286, $9,132,093,865, and
$9,502,395,879
Exercise: Slide 35 Resources: Slide 57

DeRobertis (UF) FCF June 9, 2021 56 / 47

Complex Forecast Lab Resources

Forecasting Complex Time-Series Resources

1 Slides - Free Cash Flow Estimation and Forecasting
2 CAT Balance Sheet
3 CAT Income Statement
Exercise: Slide 35 Answers: Slide 56

DeRobertis (UF) FCF June 9, 2021 57 / 47

Terminal Values Lab, Answers

DCF Stock Price using Terminal Values, Answers

1 The stock prices using the five methods are as follows:
2 EV/EBITDA: $27.74
3 EV/Sales: $13.67
4 EV/FCF: $14.21
5 P/E: $14.47
6 Perpetuity Growth: $18.45
Exercise: Slide 47 Resources: Slide 59

DeRobertis (UF) FCF June 9, 2021 58 / 47

Terminal Values Lab Resources

DCF Stock Price using Terminal Values Resources

1 Slides - Free Cash Flow Estimation and Forecasting

Exercise: Slide 47 Answers: Slide 58

DeRobertis (UF) FCF June 9, 2021 59 / 47