Free Cash Flow Estimation and Forecasting
********************************************
Continues discussion of DCF valuation. This lecture series focuses on the free cash flow (FCF) side of the model. General forecasting material is included to be able to forecast FCFs.
Resources
============
- :download:`Slides - Free Cash Flow Estimation and Forecasting `
- :download:`Lecture Notes - Free Cash Flow Estimation and Forecasting `
- :download:`WMT Income Statement `
- :download:`WMT Balance Sheet `
- :download:`Calculating Historical FCF `
- :download:`Exxon-Mobil Financials `
- `finstmt Documentation `_
- :download:`Sales COGS `
- :download:`Sales COGS Forecasted `
- :download:`Forecast Sales COGS Simple `
- :download:`Debt Interest `
- :download:`Forecasting Financial Statements `
- :download:`CAT Balance Sheet `
- :download:`CAT Income Statement `
- :download:`WMT Balance Sheet `
- :download:`WMT Income Statement `
- :download:`Forecasting Quarterly Financial Statements `
- :download:`CAT Balance Sheet `
- :download:`CAT Income Statement `
Introduction to Free Cash Flows
==================================
.. youtube:: br-5IQjIm6w
:height: 315
:width: 80%
:align: center
|
Notes
--------
- As a recap, we are focusing on FCF calculation so that we can do the discounted cash flow (DCF) valuation of a stock
- We need to calculate historical FCFs and also forecast future FCFs
- Historical FCFs are a mechanical exercise that anyone could do, but forecasting is as much an art as it is a science
- FCFs accrue to all investors, debt and equity, and so they can be used to determine the value of the firm
- Net income is a measure which accountants have devised to smooth out the cash flows of the company, representing one time outlays which are used in multiple periods by splitting the cost across those periods
- As far as valuation is concerned, net income does not matter. FCFs are what matters as they are what is actually happening with the operations in that time period
- Unless the historical FCFs are very stable, you should almost always be forecasting the financial statements and calculating the future FCFs rather than forecasting them directly
- There is a lot of flexibility in forecasting as you can use dozens of different possible models, and three common forecast targets (levels, growth, percentage of another item)
Transcript
-------------
.. raw:: html
|
Introduction to Calculating Historical Free Cash Flows
=========================================================
.. youtube:: kghacp72D3M
:height: 315
:width: 80%
:align: center
|
Notes
--------
- There are multiple sets of equations that can be used to calculate free cash flows
- Here we will focus on a set of equations that does not require the statement of cash flows, only the income statement and the balance sheet
- I usually recommend this approach because then only the income statement and balance sheet need to be forecasted rather than all three financial statements
- The philosophy of this approach is that we are starting with net income, undoing the adjustments which have accountants have done, and adding our own adjustments for cash items which are not included in net income to get back to FCFs
- Net working capital is not counted in net income, but can be a source or use of cash so it should be included
- Capital expenditures are usually spread across the usage of the asset in net income, but here we are adjusting it so the cost is realized in the period of the purchase
Resources
------------
- :download:`WMT Income Statement `
- :download:`WMT Balance Sheet `
Transcript
-------------
.. raw:: html
|
Historical Free Cash Flows in Python Using Pandas and finstmt
================================================================
.. youtube:: lLFaAQH7Jos
:height: 315
:width: 80%
:align: center
|
Notes
--------
- Most of the work in calculating historical free cash flows with Pandas is just getting the data loaded, cleaned up, and in the right structure
- We use .loc in Pandas to look up a statement item when it is in the index and the columns are the dates
- Be careful about the names of columns, they may have gotten loaded in with extra spaces or otherwise not exactly as you expected
- Be sure to think about the sign on your item. Are costs represented as positive or negative in the statement?
- I created the finstmt package to make all of these operations more convenient and repeatable, with the goal being that you should be able to quickly work with financial statements without all the cleanup operations, and you should be able to provide different statements and have the code work the same
- I have continued to add useful features to finstmt such as calculating free cash flows and forecasting
- It is easy to do lags and changes in the calculations with finstmt
Resources
------------
- :download:`Calculating Historical FCF `
- :download:`Exxon-Mobil Financials `
- `finstmt Documentation `_
Transcript
-------------
.. raw:: html
|
Historical Free Cash Flows Lab Exercise Overview
===================================================
.. youtube:: XHGvOA5sYnY
:height: 315
:width: 80%
:align: center
|
Notes
--------
- The first exercise is just about going through the math of the FCF calculation
- The second exercise requires you to work with actual financial statements to do the calculations, similar to the example
Resources
------------
- :download:`WMT Balance Sheet `
- :download:`WMT Income Statement `
- `finstmt Documentation `_
Transcript
-------------
.. raw:: html
- 00:02: hey everyone
- 00:03: this is nick dear vertis teaching you
- 00:05: financial modeling today
- 00:07: i'm going to be introducing the lab
- 00:09: exercise on
- 00:10: calculating historical free cash flows
- 00:13: this is part of our lecture segment on
- 00:15: free cash flow estimation
- 00:16: and forecasting as part of our larger
- 00:19: objective
- 00:20: of building out the full discounted cash
- 00:22: flow evaluation of a stock
- 00:26: so we just wrapped up the historical
- 00:29: free cash flows material
- 00:31: i'm going through an introduction on it
- 00:33: as well as
- 00:34: showing how to calculate it in python
- 00:37: and now we come to
- 00:38: the lab exercise which tests knowledge
- 00:41: of that material
- 00:42: so it's two exercises um and the first
- 00:46: one here
- 00:47: uh i just directly gave you the numbers
- 00:49: to use
- 00:50: to calculate the free cash flows so it's
- 00:53: kind of
- 00:54: just about being able to uh go through
- 00:57: that
- 00:57: formula to calculate the free cash flows
- 01:01: um so you can get used to
- 01:04: uh the components that are involved and
- 01:06: how you put them together
- 01:08: and then level two is a little more
- 01:10: realistic
- 01:11: exercise of you have to actually load in
- 01:15: um income statement and balance sheet
- 01:17: data
- 01:18: and calculate the free cash flows using
- 01:21: those data directly um and you'll want
- 01:24: to calculate two different periods
- 01:26: of the free cash flows
- 01:30: um so this is going to be quite simple
- 01:32: uh
- 01:33: or sorry quite similar to the
- 01:36: example that we went through in python
- 01:39: and you can feel free to
- 01:41: use whatever tools um excel
- 01:44: python or within python you can use just
- 01:47: panos or you can use find statement
- 01:51: however you want to go and calculate the
- 01:54: free cash flows
- 01:56: so that's the lab exercise for the
- 01:59: historical
- 01:59: free cash flows thanks for listening and
- 02:02: see you next time
|
Introduction to Forecasting
==============================
.. youtube:: CKacJsM5ZGE
:height: 315
:width: 80%
:align: center
|
Notes
--------
- I could teach an entire course on forecasting. There is far too much material to cover in the time allowed
- There is so much complexity as there are multiple options on two dimensions: what to forecast and which model to use
- We will focus on only a few possible models in this course to keep things simple
- It is up to the modeler which models to use and what to forecast. It should generally be guided by knowledge of the company as well as understanding of the structure of financial statements
- The simple time-series models are easy to understand, but the advanced models quickly get into a confusing alphabet soup and are outside the scope of this course
- You could also use machine learning models for forecasting
- Regardless of the chosen model, the steps to forecasting are the same. So once you learn the steps in this course, you will be able to learn the more advanced models on your own and apply them in the same framework
Transcript
-------------
.. raw:: html
|
Simple Time-Series Forecasting Models
========================================
.. youtube:: -qtB0NcXKmY
:height: 315
:width: 80%
:align: center
|
Notes
--------
- Use the historical average approach when there is not a defined trend or growth in the historical data and when you think that the average will be more relevant than just looking at the most recent value
- Use the recent value approach in the same situation, but instead you think that the most recent data is more relevant than an average
- If the data are going up or down over time, as long as there is not a repeating pattern within that, use the trend or growth model. Use the trend model when the change seems linear/constant, and the growth model when the change seems exponential/changing over time. If there are repeating patterns within the data, a more complex model will be required
Transcript
-------------
.. raw:: html
|
Simple Time-Series Forecasting in Excel
==========================================
.. youtube:: 1kYbZ98_JgM
:height: 315
:width: 80%
:align: center
|
Notes
--------
- Most of the calculations are straightforward in Excel
- We can use the SLOPE and INTERCEPT functions to fit the trend model without using the Data Analysis Toolpak regression
Resources
------------
- :download:`Sales COGS `
- :download:`Sales COGS Forecasted `
Transcript
-------------
.. raw:: html
|
Simple Time-Series Forecasting in Python
===========================================
.. youtube:: 8jgvoT50NuM
:height: 315
:width: 80%
:align: center
|
Notes
--------
- The calculations themselves are straightforward in Python, though we see a couple new tricks such as transposing a DataFrame and working with the DataFrame index
- This example also builds up some functions which could be taken and used in a project
Resources
------------
- :download:`Forecast Sales COGS Simple `
- :download:`Sales COGS `
Transcript
-------------
.. raw:: html
|
Simple Time-Series Forecasting Lab Overview
==============================================
.. youtube:: xa_Gd46zizE
:height: 315
:width: 80%
:align: center
|
Notes
--------
- This lab exercise follows closely the structure of the previous example
- Through this you should understand the different simple methods
Resources
------------
- :download:`Debt Interest `
Transcript
-------------
.. raw:: html
- 00:03: hey everyone this is nick d robertis
- 00:05: teaching you financial modeling
- 00:07: today we're going to be introducing the
- 00:10: lab exercise
- 00:11: on simple time series forecasting
- 00:15: this is part of our lecture segment on
- 00:17: free cash flow estimation and
- 00:19: forecasting
- 00:20: as part of a broader goal to build at
- 00:22: the full discounted cash flow valuation
- 00:25: of a stock
- 00:26: so we finished up
- 00:30: in prior videos the overview of
- 00:32: forecasting
- 00:33: and all the simple forecast models
- 00:37: uh and then we went through examples of
- 00:39: how to run these simple forecast models
- 00:42: in both excel and python so now
- 00:46: we are coming to the lab exercise which
- 00:50: uh cements understanding of all these
- 00:53: topics
- 00:55: and it's a very similar exercise to what
- 00:58: was shown in the excel and python
- 01:00: examples
- 01:01: so you're going to take this debt
- 01:03: interest file which has
- 01:04: information on the
- 01:07: total debt and the interest payment
- 01:11: and you want to
- 01:13: [Music]
- 01:15: basically forecast uh with all the
- 01:18: different approaches
- 01:19: and you want to forecast on for interest
- 01:23: you want to forecast that both in terms
- 01:26: of levels
- 01:27: as well as forecasting it as
- 01:30: a percentage of debt
- 01:35: so infest is another common example
- 01:40: that is typically percent forecasted as
- 01:42: a percentage
- 01:44: of uh total debt or long-term debt
- 01:46: rather than at levels so it's a good
- 01:48: uh fit for this exercise
- 01:52: um and then you can find all the answers
- 01:54: here
- 01:55: on the um slides as well
- 02:00: so that's the review of the lab exercise
- 02:04: we'll come back next time to talk about
- 02:08: more specifically forecasting financial
- 02:11: statements as a whole
- 02:12: with these simple forecasting methods so
- 02:15: thanks for listening
- 02:16: and see you next time
|
Forecasting Simple Financial Statements in Python with finstmt
=================================================================
.. youtube:: OWDulQWYscU
:height: 315
:width: 80%
:align: center
|
Notes
--------
- Forecasting financial statements can be overwhelming because there are so many different line items to think through
- finstmt allows you to forecast them all at once, conveniently, and with reasonable baseline assumptions. You can modify the forecast method or target (level, growth, % of other item) for any item as desired by adjusting the configuration
- finstmt also automatically generates plots with confidence intervals for all the line items
- finstmt also allows you to make manual adjustments to an existing forecast, either by adjusting the existing forecasted values or by replacing them
Resources
------------
- :download:`Forecasting Financial Statements `
- :download:`CAT Balance Sheet `
- :download:`CAT Income Statement `
- `finstmt Documentation `_
Transcript
-------------
.. raw:: html
|
Complex Time-Series Forecasting
==================================
.. youtube:: 8zIAtI0_dBA
:height: 315
:width: 80%
:align: center
|
Notes
--------
- Forecasts are simple when the data are just trending upwards or downwards without any repeating pattern
- Once there are repeating patterns in the data, more advanced models are needed
- Seasonality is a classic cause of these repeating patterns. A cruise line is going to have much higher sales in the summer than in the winter, and this pattern is going to repeat every year
- In general, these advanced models are outside of the scope of the course. Here we just cover the quarterly seasonal trend model which is specifically designed to deal with this seasonality in quarterly data, which should be sufficient for DCF financial statement forecasting
- Dummy variables are just those which take on a value of 1 for true and 0 for false
- If you need to forecast higher frequency data for other purposes, the quarterly seasonal trend model will not be a good fit. Other frequency seasonality models can be fit, but it would usually be better to go to the full model selection process
- Thankfully, we have software solutions to fit advanced models for us such as prophet, which is integrated into finstmt
Transcript
-------------
.. raw:: html
|
Complex Time-Series Forecasting in Python - Manual Method
============================================================
.. youtube:: aH8Hrvm_5a8
:height: 315
:width: 80%
:align: center
|
Notes
--------
- To estimate the quarterly seasonal trend model, most of the work is in setting up the data
- We need to create a t variable as well as dummies for the quarters
- While it would not be too difficult to manually calculate dummies, thankfully pandas has a get_dummies method
- We still follow the same general forecasting process: fit the model on historical, use it to predict the future
- In contrast to the standard linear trend model, when fitting we just add the coefficient of the dummy variable which corresponds to the current month
- pandas date_range is useful to generate our future dates for prediction
- We can use pandas concat to put together the historical and forecasted series to generate a single plot
Resources
------------
- :download:`WMT Balance Sheet `
- :download:`WMT Income Statement `
- :download:`Forecasting Quarterly Financial Statements `
Transcript
-------------
.. raw:: html
|
Complex Time-Series Forecasting in Python - finstmt Method
=============================================================
.. youtube:: ANhOUMWkD_8
:height: 315
:width: 80%
:align: center
|
Notes
--------
- We need to install fbprophet to work through this exercise
- This is the first package we have installed which needs to be installed by Anaconda rather than pip as there are additional non-Python dependencies
- You can change the default forecast method for your statements in finstmt with stmts.config.update_all
- "auto" is the forecast method which uses fbprophet in the background to fit the model. fbprophet takes some time to do its work
- finstmt handles balancing the balance sheet for you. This also will take substantial time if you are forecasting many periods
- fcst_stmts.plot can plot all the line items or just a subset, including the historical, forecasted, and confidence interval
Resources
------------
- :download:`WMT Balance Sheet `
- :download:`WMT Income Statement `
- :download:`Forecasting Quarterly Financial Statements `
- `finstmt Documentation `_
Transcript
-------------
.. raw:: html
|
Complex Time-Series Forecasting Lab Overview
===============================================
.. youtube:: MTBgSH45yYg
:height: 315
:width: 80%
:align: center
|
Notes
--------
- The exercise here is very similar to the examples we just worked through
Resources
------------
- :download:`CAT Balance Sheet `
- :download:`CAT Income Statement `
Transcript
-------------
.. raw:: html
- 00:02: hey everyone this is nick duraburtis
- 00:04: teaching you financial modeling
- 00:06: today we're just going to be quickly
- 00:08: going over the
- 00:09: lab exercise for the
- 00:12: complex time series forecasting material
- 00:16: this is part of our lecture segment on
- 00:18: free cash flow estimation
- 00:20: and forecasting so
- 00:23: we finished covering all the complex
- 00:26: forecasting material
- 00:27: including the two videos which
- 00:30: go over how to apply this in python
- 00:34: using the manual approach with the
- 00:37: quarterly seasonal trend model as well
- 00:39: as the automated software approach
- 00:41: within statement
- 00:42: and profit and
- 00:45: now in order to test knowledge of that
- 00:47: material we have this
- 00:49: complex forecasting lab so if you go to
- 00:52: the course site you can download
- 00:54: caterpillars balance sheet and income
- 00:57: statement
- 00:58: and you want to use those to
- 01:01: forecast uh the next four periods
- 01:05: which will be one year worth of data
- 01:08: of the cash and you want to do that with
- 01:11: both the quarterly seasonal
- 01:14: trend model as well as the automated
- 01:17: software approach and you want to plot
- 01:21: both to see the results
- 01:23: so really it's going to be very similar
- 01:26: to
- 01:27: what we've done in the
- 01:31: two example videos here
- 01:34: so you can just take that example
- 01:36: notebook and i would
- 01:39: you know recommend playing around with
- 01:41: it trying to see how it works but then
- 01:42: you can just kind of take the
- 01:45: um functions and bring them into your
- 01:47: own notebook
- 01:48: um in order to do this with caterpillar
- 01:52: instead of walmart
- 01:55: so that's basically what's involved
- 01:59: in this lab so thanks for listening and
- 02:02: see you next time
|
Applying Forecasting to Free Cash Flows
==========================================
.. youtube:: ISvxUdMeZgY
:height: 315
:width: 80%
:align: center
|
Notes
--------
- Everything we've learned on forecasting thus far is general to anything you want to forecast. Now let's see what matters for financial statements specically
- It is generally preferred to forecast statement line items rather than FCFs directly, as FCFs tend to be very noisy and hard to predict from the time-series
- Do not forecast calculated items. Forecast their components then calculate them
- Balancing the balance sheet is an additional step unique to financial statement forecasting
- Almost definitely after doing your initial forecasts, your balance sheet will be significantly off from balancing. But we know from accounting that it has to balance
- Usually cash or debt are adjusted as "plugs" through an optimization process
- finstmt makes this happen automatically, but also gives you full control over which line items are used as plugs and how closely to balance it. Balancing closer will take longer.
Transcript
-------------
.. raw:: html
|
Calculating a Terminal Value
===============================
.. youtube:: 9l20iL03fpw
:height: 315
:width: 80%
:align: center
|
Notes
--------
- The last piece of the DCF model we have not covered is the terminal value
- Because it is not possible and certainly not accurate to forecast many years in the future, we use a terminal value to represent the enterprise value at some future date
- DCF models are extremely sensitive to the terminal value assumptions, so they should absolutely be included in sensitivity analysis and MC simulations
- In the exit multiple method, we use current valuation ratios with projected financials to estimate the terminal value
- In the perpetuity growth method, we assume that the FCFs will have constant growth after the forecast period and take the value of those constantly growing FCFs
- We include the TV in the final FCF year and take the present value to get the current enterprise value, which can then be converted into equity value and stock price
Transcript
-------------
.. raw:: html
|