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¶
Introduction to Free Cash Flows¶
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¶
- hey everyone this is nick diabetis
- teaching you financial modeling
- today we're going to be doing an
- introduction to free cash flows
- this is part of our lecture segment on
- free cash flow estimation and
- forecasting
- which follows on to our previous lecture
- segment
- on the weighted average cost of capital
- putting these two lecture segments
- together we will develop the
- discounted cash flow valuation of a
- stock
- so getting into the free cash flows
- so we have already gone through
- basically one half of the dcf model
- where we
- got the weighted average cost of capital
- how much does the company have to pay in
- order to raise money for their
- operations
- so the other half of the dcf model is
- focused on the free cash flows
- so that ultimately we can take the
- present value of
- future free cash flows in order to
- determine the value of the business
- so within that free cash flow side of
- the dcf model
- we can even decompose it into two
- further parts
- and that is first finding the historical
- free cash flows so you have the
- historical financial statements income
- statement balance sheet perhaps
- statement of cash flows
- and you calculate the free cash flows
- from those financial statements and this
- part
- is fairly straightforward uh you're just
- kind of
- going through the formulas in order to
- calculate these things
- there's no real uh decision making to be
- done in the matter no kind of
- qualitative analysis you just do the
- calculations
- the other part which is more
- complicated and difficult is to
- project the free cash flows into the
- future
- remember that when considering the value
- of an asset
- we only consider future cash flows which
- are going to be paid we don't care
- about the historical so uh we calculate
- the historical
- just in that it will help us project the
- future
- but the future is what we really care
- about for the valuation
- so there's a lot of decisions to be made
- in how you forecast the future
- free cash flows and we'll talk about a
- variety of different approaches
- and the conditions under which you would
- might want to use some of these
- different approaches
- and we'll get into all of that within
- the forecasting material
- within this lecture
- but before we get there let me just
- give a quick introduction on free cash
- flows themselves
- so what are free cash flows and why do
- we care about them
- so free cash flows we can think of as
- the cash that the company is getting
- from their operations
- they have some revenue come in they have
- to pay
- for expenses salaries cost of goods sold
- the inventory
- etc and then they end up with some
- amount of cash
- left over after paying for all those
- operations
- so that would be the free cash flows
- and so some people can get this confused
- with
- net income because they think well isn't
- that what net income is
- the cash that's left over after you paid
- for expenses
- um but these are a little bit different
- so net income is a measure that was
- developed
- by accountants which helps
- smooth out irregularities in
- uh the company's earnings um
- especially around expenses
- which represent investments in
- like fixed goods and things like that
- which uh are going to be used over
- a long time span so maybe the company
- purchase a new
- a new warehouse they're going to use
- that warehouse over the next
- 15 years or net income we would say
- just as an example there are different
- ways to account for it but
- 1 15 of the cost is going to accrue to
- each year
- in the net income try and say well let's
- let's match up
- the expense with the usage of the asset
- to
- kind of smooth out uh the earnings
- so in contrast uh free cash flows do not
- do any of this
- smoothing it's all about the actual cash
- which is trading hands uh you know
- electronic or physical cash um
- so if they purchase a building which
- they're going to use over 15 years
- with free cash flows all of that expense
- is coming in the year where they
- actually purchased
- the building so it's it's when was the
- cash actually spent
- not when is the asset going to be used
- um so why do we care
- about free cash flow over net income for
- valuation
- so free cash flow is what is actually
- going on with the cash
- and so that's what we want to consider
- for
- the valuation net income
- is still useful to think about in terms
- of thinking about what is kind of a
- typical
- amount that the company would earn in a
- given year um
- considering smoothing out costs across
- multiple years
- but when we're thinking about valuation
- we always want to use free cash flows
- because that's what
- actually happens um and we want to be
- using the truth for our valuation
- investors care about
- when they're going to get that cash the
- timing of it is important
- um and so we always use free cash flows
- for valuation and
- free cash flows um
- represent uh the
- earnings from the operations and then
- those free cash flows go to service
- both debt and equity holders
- so uh free cash flows are used
- to pay interest on the debt uh and
- they're also
- used to pay dividends or
- uh go into retained earnings and
- increase the value of equity that way
- um when we take the present value of
- free cash flows
- what we get is the enterprise value or
- the entire
- uh full asset value of the firm and then
- we can decompose that further into the
- debt value and equity value
- of the firm as we covered in the prior
- lecture series
- so thinking about calculating the
- historical
- free cash flows so there's a number of
- different approaches which are
- equivalent
- um we're going to
- [Music]
- go through a way in this course that
- only requires
- the income statement and balance sheet
- so that you don't even have to
- work with the statement of cash flows
- but certainly there are
- methods which use the statement of cash
- flows as well
- and so this is the basic formula
- shown here on the right and in the
- next video we'll go into a lot more
- detail
- on this um but basically we're just
- taking this formula and we're just going
- to calculate it
- for each year of historical data
- but it is kind of a multi-level
- calculation
- in that each of these three components
- which we are going to use to adjust the
- net income
- they each have to be calculated on their
- own
- before we can go and do the historical
- free cash flow calculation
- and the philosophy behind this formula
- is that we are taking this net income
- which accountants have adjusted and
- we're going to reverse the adjustments
- that they've made
- and also include what they've excluded
- as far as capital expenditures these
- large
- purchases which are being spread over
- multiple years
- um so by doing these adjustments we'll
- be able to get back
- to the actual cash coming from the
- operations
- the free cash flows
- and then thinking about the other side
- of
- free cash flows in the dcf model
- we need to forecast them into the future
- and this is definitely
- the more challenging part because the
- historical free cash flows are a very
- mechanical thing you just
- do the calculation and you're done
- but with forecasting there's a lot of
- decisions you have to make
- as the modeler
- and you want to use knowledge you have
- about the company in order to make those
- decisions
- so we can broadly break down
- at the highest level uh of how to
- forecast the free cash flows into two
- methods
- and that is forecasting free cash flows
- directly or forecasting the financial
- statements
- um and forecasting free cash flows you
- only have to forecast
- one thing and so it's very easy and
- quick to do
- but it's typically going to not be able
- to be as accurate
- uh unless this is a company that has
- very stable
- free cash flows but generally
- what you're going to want to do is
- forecast the financial statements
- because that gives you a much greater
- level of detail
- into the company's operations
- and then once you've decided on
- what you're going to forecast either the
- financial statements or the free cash
- flows
- then there are a lot of ways
- to go about that forecasting exercise
- um so generally
- generally we're going to look at time
- series methods in order to
- do the forecasts and we'll talk a lot
- more about
- what that means in the following videos
- but basically different models that we
- can use to
- predict the future from the past
- and then even aside from those models
- even after we've already decided what
- we're forecasting
- there's still another decision to make
- which is
- uh what what we're going to forecast
- within that item are we going to
- forecast
- the level of the item the growth of the
- item or are we going to peg it to
- another item and
- forecast the percentage so we'll dig
- into all these different decisions
- and the considerations you want to make
- in those decisions
- and uh how you would actually
- go through each one of these approaches
- and calculate them
- so all that come in this lecture series
- and the last part of the lecture series
- will also look at
- how to finish up the dcf model
- with everything else that we've learned
- from the prior lecture series
- so thanks for listening and see you next
- time
Introduction to Calculating Historical Free Cash Flows¶
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¶
Transcript¶
- hey everyone
- this is nick durabartis teaching
- financial modeling
- today we're going to be doing an
- introduction on how to calculate
- historical
- free cash flows this is part of our
- lecture segment
- on free cash flow estimation and
- forecasting
- which is part of our broader goal of
- building out the full discounted cash
- flow valuation of stock
- so when we think about historical free
- cash flows
- there are a number of different ways to
- calculate them
- uh several different formulas which
- use different parts of the financial
- statements
- what we're going to focus on here is
- this approach of
- basically taking that income and trying
- to adjust it
- back into free cash flows and the reason
- that i've
- chosen this approach in the course
- is because most of the other approaches
- require using the statement of cash
- flows
- this approach uses only the income
- statement and
- balance sheet and it definitely
- simplifies things in your model to only
- have to
- forecast two sets of financial
- statements rather than
- three so you can
- save some of the work in your model and
- get an equivalent result
- by going with this method now if you
- are just calculating historical free
- cash flows and you're not needing to
- project them in the future
- then using the statement of cash flows
- is usually easier
- there's fewer calculations to do but
- you don't need it to calculate the free
- cash flows and
- forecasting it is a good deal of effort
- so that's why we're going with this
- approach
- so as i mentioned in the introduction
- we are going to adjust the net income by
- these various components to get to the
- free cash flows
- but each of these components in another
- of themselves
- need to be calculated as well so let's
- look at the calculation
- of each of those items and then you
- basically just put them together to get
- the free cash flow
- so the first here is non-cash
- expenses and we're calculating non-cash
- expenses because
- net income is this measure which
- accountants have smoothed out
- and they want to include expenses even
- if they weren't
- made in cash this year because they're
- trying to smooth things out over
- multiple years
- but for free cash flows we
- don't want to consider that we want to
- reverse this adjustment that
- the accountants have made to net income
- so we can get back to what cash
- actually changed hands
- so looking here at the non-cash expenses
- we have a few different
- categories within that that we have to
- add up to
- get the total so we have
- uh depreciation and amortization
- that is representing uh basically when
- you buy a fixed asset
- say that a company just
- just bought a jet in order to
- ship things across countries
- and they are going to use this jet
- over the next 10 years
- so with depreciation and amortization we
- would basically say
- one tenth of that cost or there are
- other uh schedules but
- we can think of it as just you know one
- tenth of that cost
- per year over each of the ten years when
- going into net income and that's what
- the depreciation is is saying well let's
- count
- a little bit of that expense each year
- over the time that we're going to be
- using the asset
- that's not cash changing hands the cash
- only changed hands at the beginning
- when we bought the jet um and so we'll
- get to that
- jet purchase itself uh once we get to
- capex
- a little bit later uh but we've gotta
- adjust back this portion that's being
- taken out every year
- for something which was not purchased in
- this year
- so that's depreciation amortization we
- will reverse that
- in the calculation by adding in here
- then coming to stock-based compensation
- so that means that employees were
- compensated but they were compensated
- via stock instead of via cash
- um and so it is an expense for the
- company and it is
- valid to include that in net income and
- thinking about
- how much basically did they actually
- earn kind of on average
- but it wasn't a cash expense and so in
- order to get back to the actual cash
- that changed hands
- we need to add back in that stock based
- compensation that wasn't a cash expense
- and then the last two components here
- impairment charges and gains losses on
- investments
- these are basically the same thing but
- impairment is talking about
- uh longer term assets and gains and
- losses on investments is more about
- shorter term uh assets but it's
- basically
- an asset that the company holds has
- changed in value
- while they've been holding it they
- didn't sell it they didn't buy it
- it just changed in value while they were
- holding it
- um and so accountants will go ahead
- and take that as a gain or a loss in
- calculating that income
- but again no cash changed hands there
- because they didn't actually sell
- the asset and so we're going to reverse
- that as well
- and then coming to the next component we
- have the change in networking capital
- so networking capital basically
- represents
- how much cash does it take um in order
- to
- uh sell your product produce and sell
- your product uh the cash which is just
- tied up
- in the daily um transactions of
- the company so it's not going as far as
- saying uh you know the salaries of
- everyone involved in producing the
- product is just
- uh how much cash is tied up in this kind
- of
- work in progress of creating and selling
- the products
- um so
- we are going to adjust
- uh the net income by the change in
- networking capital
- because um if now this year compared to
- last year
- we need to tie up an additional million
- dollars in
- the uh process of selling
- our goods then that represents a use of
- cash
- we have taken a million dollars and
- invested it into
- the production process and so we need to
- consider that
- in the free cash flows whereas on the
- flip side we
- are able to reduce the networking
- capital by a million dollars because we
- have
- now a more efficient inventory system
- then that represents a source of cash
- now that's money which has been freed up
- from the process and it's available
- to the investors the company or for the
- company to reinvest
- in other operations so any change
- in the networking capital is something
- that we need to consider
- and free cash flows
- and then uh going about calculating it
- so we need to calculate the networking
- capital in the first place
- um and then we can calculate the change
- in networking capital
- so you're going to need these three uh
- line items from the financial statements
- uh
- all from the balance sheet
- and you're going to need it for the
- prior year as well in order to calculate
- the
- change in networking capital so you'll
- never be able to calculate this in the
- first year for which you have data
- but you'll be able to calculate it every
- year thereafter or every period
- thereafter
- so um we
- had accounts receivable and inventory
- because these are uses of
- cash um because
- accounts receivable means that we have
- uh sold some items on credit so that's
- money we're waiting to receive for a
- product we've already sold
- so that is using up cash basically to
- have money tied up in this credit
- um and then inventory represents that
- we've we've paid to produce these goods
- but we haven't sold them yet and so
- that's cash tied up just sitting in
- inventory
- and then we subtract the accounts
- payable because that's money that
- we have not paid to other people that we
- do owe to them
- for um you know just general
- goods and services in the production
- process so that is a source of cash
- and that we don't have to pay for it
- right away
- and so the cash is not tied up there
- so we calculate the net working capital
- following this formula
- and then you just take this year's value
- minus last year's value
- in order to get the change in networking
- capital that will come
- into the free cash flow calculation
- then moving on to the last component
- capital expenditures
- so capital expenditures represent
- these fixed assets which are getting
- used over
- multiple periods so buildings and
- machinery are kind of the most common
- examples of capital expenditures um
- you know buying a warehouse buying a jet
- will be given as examples it doesn't
- have to be huge assets
- buying a computer even can be considered
- a fixed asset because you're going to
- use that computer
- over multiple uh periods fiscal periods
- so basically any asset
- which is purchased and used over a
- longer period of time
- can count in these capital expenditures
- so as i mentioned accountants basically
- spread this cost across multiple periods
- but when we think about free cash flows
- we care about when was it actually
- paid for um and so we want to bring
- those
- capital expenditures into the
- calculation of free cash flows
- so how do we calculate that well we look
- at this other balance sheet item
- the property plant and equipment which
- represents
- basically the total fixed assets
- in the company so when we look at the
- change
- of that property plant and equipment
- that is you know
- how many new fixed assets did they
- acquire this year
- um so that's the basis for estimating
- capex
- but then we also need to add
- the depreciation and amortization for
- this year as well
- because remember with the with the jet
- example spreading that over 10 years
- the accountant has already taken out the
- first one-tenth of the cost
- and accrued it to this year um so we
- can't just
- add then all 10 years of cost back in
- that would be 11 years of cost
- and so we need to add back in that
- depreciation
- as well they actually uh accountants
- will
- decrease the level of property plan
- equipment
- by the amount of the depreciation so we
- need to add it back to get to
- the full price there it would be only
- nine tenths of the cost would be showing
- on the
- balance sheet for the property plant
- equipment for that jet
- assuming straight line depreciation
- so those are the three components uh
- that we need to adjust the net income to
- reverse it
- into free cash flows uh so then
- in order to calculate the free cash
- flows you're just putting these items
- together
- so net income plus non-cash expenses
- minus the change in net working capital
- minus the capital expenditures uh and
- you're adding the non-cash expenses
- because we're
- reversing what was already taken out but
- shouldn't have been taken out
- for thinking about free cash flows
- and then we subtract the other two
- components because they represent uses
- of cash if you uh
- tie up more money in your production
- process that's the use of cash
- and if you purchase a fixed asset
- then that's also a use of cash and so we
- need to reduce
- the free cash flows
- so that's an introduction on calculating
- historical
- free cash flows we're going to uh
- come back next time in order to look at
- how we can
- calculate those in python
- so thanks for listening and see you next
- time
Historical Free Cash Flows in Python Using Pandas and finstmt¶
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
Transcript¶
- hey everyone
- this is nick dear burtis teaching you
- financial modeling
- today we're going to be talking about
- how to calculate free cash flows
- in python using pandas and fin statement
- this is part of our lecture series on
- free cash flow
- estimation and forecasting as part of a
- larger goal
- of building out the discounted cash flow
- valuation of a stock
- so we already gave an introduction
- to the free cash flow side of the model
- and
- how to calculate free cash flows
- historically
- uh in prior videos so take a look at
- those if you haven't already
- and now we're going to actually just
- jump right into the calculation
- in python so let's go over
- to the jupiter notebook
- so at first we'll look at just using
- pennis and then we'll look
- at an additional tool that i
- developed called fin statement that
- makes
- some of this process quite a bit easier
- but first focusing just on panic so we
- have
- some financial statements in an external
- excel file and we want to get them
- into python so the first thing i'm going
- to do is
- import pandas and just set the name of
- that file which has the financial
- statements
- and i'm going to read that in with
- parents's read excel method
- and then we can just do dot head on the
- resulting data frame to check out what
- we got
- now we can immediately see this is maybe
- not
- 100 percent what we expected to get uh
- it looks a little bit messy we have this
- unnamed thing
- we have these mans going on sometimes it
- snans sometimes it stashes
- so some cleanup is needed here in order
- to
- work with this one other thing
- is that these statement line items
- if we have them in the index we'll be
- able to look up
- uh the various line items but it's a
- little bit more difficult to do that
- when they're just in a column
- so we can
- um add this argument to the read excel
- method
- and index column equals zero
- and when we do that it's saying take the
- first column here
- and set that as the index and so that
- solves our unnamed
- issue because now that's the index it
- doesn't need a name
- and it solves this issue of these things
- should probably be in the index now they
- are
- so then next we have to deal with
- this nan versus dash issue
- nan is the representation for
- missing in pandas and dash
- were just some strings in the excel file
- that got loaded in
- where that's how the excel file is
- representing them as
- missing um but here they're coming in as
- the string dash
- so ultimately we want to clear out these
- empty rows
- and we want to make sure that when
- something is missing
- that we're actually getting it as
- missing and not as a string
- so what we can do is we want to replace
- uh this dash with
- the missing representation nam and you
- import uh once you import numpy then
- it's
- numpy dot nam that's
- the missing representation so we're just
- replacing everywhere in the data frame
- where there's the string dash with a
- missing
- and then we can see that what was a dash
- before now
- is the same nan not a number missing
- value so now that we
- have the missing values appropriately
- where they should be
- now we can go ahead and drop the missing
- values
- but we don't want to accidentally drop
- this this is
- important to keep this other revenue
- even though the others
- are not there and so
- we will pass to the drop n a method uh
- this how argument of how equals all
- saying that we only want to drop the row
- if every value in the row
- is missing so when we do that
- then we see that these totally blank
- rows that were there before
- are now gone but we're still left with
- the row that had some missings in it
- but at least one valid value
- so now this is looking quite a bit
- better than what we started with
- so we can go ahead and wrap these steps
- up into a function
- load and clean statement data frame
- give it the path of the statements and
- the name of the sheet and it does these
- three steps that we mentioned so then uh
- here i'm just starting in from scratch
- by using that function
- and we're loading in the income
- statement and now we can see it just
- looks like this
- already it was good to build out a
- function for this because we were going
- to have to do
- all the same things with the balance
- sheet um
- so now that just becomes a one-liner we
- can do the same thing with the balance
- sheet
- and that comes in nice and clean as well
- so that's the kind of initial loading
- process that you do with pandas and
- you'll need to do that
- regardless of whether you choose to do
- the actual
- uh free cash flow calculation with
- pandas or with fin statement
- but let's look at how we can do all this
- with just pandas first
- so as a reminder for what we're doing
- we're calculating these three
- adjustments to net income
- non-cash expenses increase in working
- capital and capital expenditures
- uh all explained in the prior videos um
- and then we're going to calculate the
- free cash flow from the net income and
- these items
- so um we
- are going to need to work with these
- statement items so let's look at
- everything that we've got we can do dot
- index
- to see everything that's in that index
- and you can see
- all the different items that we have
- here
- and then in order to look up a given
- item we can
- use dot look and then pass it the string
- of the index value in order to get that
- entire row
- of the data frame
- so we can get this asset write down
- expense by doing dot loc with asset
- write down and we can see it gets us
- each year
- of the expense as a panda series
- so um
- one other thing we have to consider is
- we've said in our calculation that uh
- we're going to
- add this but it's coming as a negative
- number here um so we need to think about
- basically the costs on the
- income statement are they being
- represented as negative or positive
- numbers different financial statements
- will show it differently
- um because you can say well it's a
- positive cost so it subtracts or you
- could say
- well the cost is subtracting and so it's
- negative
- um so you'll need to pay attention to
- that
- and handle that as you
- work with the line items so we want this
- to be a positive cost
- and so we're going to take the absolute
- value with
- the built-in ads function
- um so we're trying to calculate this
- non-cash uh expenses so we'll also need
- the depreciation
- so we can grab that and since we can do
- math directly with series
- we can just add these two things
- together
- and now we can see that we have
- these values added together
- appropriately
- but we'll notice that something strange
- here happened with the first two years
- even though we had depreciation numbers
- for that year
- we're now getting missing and that's
- because the numbers were missing
- in this uh other series of the
- impairment or asset write down
- so um whenever you uh do math with a
- missing number
- it's going to cause the k the whole
- calculation to become missing
- but really in this case uh it wasn't
- missing it should have been zero
- for these there just was no expense and
- so it ended up is missing
- but really it's zero
- so what we can do is fill in all those
- missing values
- in the data frames with a zero so that's
- what this
- fill and a is doing we're going to put a
- zero in for everywhere that had
- a missing number and then when we look
- again
- at this impairment charge we now see
- zeros coming there
- and that allows the calculation to go
- through as expected
- so then we can go ahead and do the full
- calculation
- of these four different line items
- and then we get our non-cash expenses
- here as a series
- next we go to calculating uh the change
- in working capital
- um so we need to pull the appropriate
- items so let's look at our index again
- we're gonna need to use uh accounts
- receivable
- uh inventory and accounts payable so
- we just put those things together and
- we get these uh connected together but
- now
- this is the current networking capital
- we need to calculate the change
- in networking capital so there is a way
- to do this
- uh change uh kind of concept and panzer
- and pandas
- if we use the dot shift
- method that allows us to move
- the the series values
- up or down by a certain number
- so here shift one is going to
- move all the values forward by
- one period um so that
- um when we look at the 2015 value what
- we have
- uh in the shifted is actually the 2014
- value um so that's what we want to
- calculate the change
- we want to do the original minus the
- shifted by one period
- and then of course we can't get the
- first period because we have no prior
- period
- but then looking at the second we're
- getting 1481 which appears to be
- uh the change between 15 and
- uh or yeah between 14 and
- 15 so that looks like it's
- working appropriately
- then we need to calculate capital
- expenditures
- and you'll notice
- back when we look at this index that
- that property plant equipment
- came with a space on the beginning
- so unfortunately we do need to include
- that space unless we go and change
- the name of the column when we do it
- like that we're able to look it up
- but if we did it without the space we
- would get an
- error so you do need to be careful about
- your column names
- and they're not always going to be
- loaded in perfectly cleanly
- when you just pull in an excel sheet um
- so you may want to just rename the items
- or you can just work with them as is
- with
- the additional spacing or whatever else
- is coming in
- anyway we uh get our net ppe here
- without loc and then we want to
- get the change in that uh similarly to
- what we did
- for the networking capital
- and then to the change we add the
- current appreciation
- and amortization so here now
- we have the capital expenditures
- so then we just put all that together
- into the free cash flows
- um take the net income add the dot cash
- expenses subtract the change in net
- working capital and subtract the capex
- and there we are left with the free cash
- flows
- and of course we can't get them for the
- first year because we had no prior year
- for the changed items
- so then we can look
- at perhaps an easier way to go about
- this uh
- using the fan statement package so
- uh i actually created this package
- because i felt like this process that we
- just went through is a little bit
- arduous or something that should be
- pretty simple
- and that lots of people need to do
- and there were a number of things about
- this that make it
- difficult to repeat um
- and make it make the code just you know
- very tied to these specific financial
- statements
- rather than working for any given
- statements that you would want to work
- with
- um you're going to have different
- names in the financial statements from
- different data providers so you'd have
- to go back and switch out these names
- and we're getting extra spaces and stuff
- in there that would also be affected by
- where you're getting the
- statements from um and then
- we have this negative versus positive
- for cost issue
- um sometimes you might need to be taking
- the absolute value other times not
- um and it's
- you know the dot loc thing is a little
- bit annoying
- in order to look up items getting the
- change
- is also a little bit annoying it's not
- too bad but
- a little bit verbose
- and we also need to think about well is
- this item on the income statement is it
- on a balance sheet
- um and then find it and work with it
- we had to remember about this
- cleanup with the zeros filling in the
- zeros
- and of course we had to go through all
- these manual steps ourselves to
- calculate the free cash flow
- people calculate free cash flow all the
- time shouldn't uh
- there just be a simple way to do that
- so i created this fence statement
- package to solve all these kinds of
- issues
- um and the reason i think
- something like this was not out there
- already is most of
- the um python finance code which is out
- there
- just exists within uh
- large banks for the most part and so
- it's closed source they're not sharing
- it with anybody
- but i wanted to make this analysis
- easier for everyone and so i have open
- sourced
- all this code
- so you're going to need to install the
- pen statement package
- it does not come in anaconda because i
- only recently created it
- so you're going to need to do the
- install
- and we've covered installing packages
- and prior videos
- and since i already have it installed i
- can just go ahead and import it
- and for now the conventional import is
- to
- import the income statement balance
- sheet and financial statements
- from pin statement as of now it supports
- the income statement and balance sheet
- so we're going to use that same method
- we had from before because
- it is expecting to receive kind of this
- cleaned up structure
- you pass it data frames in order to
- create
- statements and it expects that your
- line items are in the index and that you
- have dates as the columns
- which is the structure that we set up in
- the beginning of the video
- so then you know we have those data
- frames
- which are ready to go
- and we can load in the
- financial statement uh income statement
- class
- from that income data frame so we run
- that and we do see some
- output here which is just it
- mentions the items which it saw in the
- data frame which it was not able to
- assign to a statement item um
- so most of this stuff is just are things
- that you don't really need
- and that's why they're showing up here
- so what does this look like when we
- examine the data
- um you'll notice a few things about this
- versus
- the data frame that we were just working
- with one is that
- it kind of just has the core items
- displayed here
- not all these extra things the dates
- have been
- reformatted and you'll notice that we
- have the dollar formatting with commas
- automatically everywhere
- and that we have dashes now to represent
- the
- zero or missing values
- um so
- um and then
- we can use tab completion on that
- income statement data to be able to pull
- uh whatever item that we want out of
- the income statement so you can see all
- these different options here
- and then let's just think about getting
- the revenue
- all we do is is dot revenue and
- we get the revenue series so just like
- what we had to do
- look for and pass it the specific name
- however it's coming
- in the original data now we just do dot
- revenue regardless of
- how the original name was and the
- original data
- um and then we can pull uh
- periods out at the financial statements
- so here i'm just pulling the
- 2014 period and we just get that
- you can also pull out multiple periods
- um
- by giving a list of the periods that you
- want
- and you'll notice also that
- these don't exactly match up in terms of
- date format right this is with
- dashes with the year first
- this slashes with your last but
- the package is smart enough to
- understand that you're talking about the
- same date
- and grab the correct periods
- um so we can go ahead and create the
- balance sheet
- as well so we see a similar message
- coming up there about
- you know things it wasn't able to
- extract because a lot of them weren't
- actually even balance sheet items
- and we can look at the data there as
- well
- so seeing similar things in terms of the
- structure
- compared to the income statement
- um so there's even one
- even more high level ways to work with
- package and that is
- you can create a financial statements
- class
- out of both the income statement
- and balance sheet so we create that
- statements class
- um and then what that
- allows us to do is work with the whole
- statements at once so if we just look at
- statements
- we'll see we get this header of the
- income statement it shows the income
- statement
- and then we'll get this header of the
- balance sheet and it shows the balance
- sheet
- so we can see everything
- and then we have this same thing where
- we can do the
- tab completion but now it works across
- both the income statement and the
- balance sheet
- so we have all those possible items
- there
- uh we can get the cash from the balance
- sheet we can get the cost to get sold
- from the income statement
- and we don't have to think about where
- that item actually comes from
- um and we can do the same kind of
- date access
- as we can with the individual statements
- um and this can be useful in order to
- you know pull certain items for
- different time ranges
- here i'm just getting the total equity
- for 14
- and 15.
- so then getting into uh calculating the
- free cash flows
- well there is a dot fcf and that gets
- you the free cash flows so you're done
- we didn't have to calculate all those
- separate items and put them together
- just fcf and you have the free cash
- flows so
- nice and straightforward but just to
- show
- some of the other convenient
- methods that have uh been added to the
- library to work with statement data
- let's look at how we would go and
- calculate those free cash flows
- uh manually using fin statement
- so we take the nat ppe
- um and we have this
- uh lag method um which
- is able to do um the same thing as shift
- and pandas
- so we're going to lag it by one period
- and
- then we also have this change method
- so the change is already doing just the
- original minus the
- lagged by one period but it's just a
- more convenient way
- to get to the change because the change
- is often what we're concerned about
- getting
- so in order to calculate the free cash
- flows
- uh all these these uh parks are
- calculated for you as well like the
- networking
- capital is already there the capex is
- already there
- the non-cash expenses is already there
- and so you can just uh you know directly
- go and add up those items
- and use the change on the networking
- capital
- so definitely simplifies this process
- quite a bit
- and then the same exact code is going to
- work no matter which
- financial statements you're working with
- and some other things i want to mention
- about using the package
- so i've gotten this working with uh
- stock row and capital iq financial
- statements
- i think it will work for a lot of others
- as well
- um but if you load in your financial
- statements
- and something goes wrong just send them
- to me and i'll make sure that they get
- incorporated into the package as well
- and there is basically a configuration
- system within fin statement
- which allows you to extend it to be able
- to work with any custom
- statements that you want to work with
- but i'm still working on
- filling out the documentation on all
- that so if you are interested
- in customizing how this works at all
- just reach out and i'll give you some
- guidance there
- so uh and also if you see any other
- issues with the library
- or things are not working as you would
- expect
- also just reach out and i would be happy
- to
- look into whatever issues that you have
- so that's an overview on how to
- calculate
- historical free cash flows in python
- using both pandas
- and fin statement so thanks for
- listening and see you next time
Historical Free Cash Flows Lab Exercise Overview¶
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
Transcript¶
- hey everyone
- this is nick dear vertis teaching you
- financial modeling today
- i'm going to be introducing the lab
- exercise on
- calculating historical free cash flows
- this is part of our lecture segment on
- free cash flow estimation
- and forecasting as part of our larger
- objective
- of building out the full discounted cash
- flow evaluation of a stock
- so we just wrapped up the historical
- free cash flows material
- i'm going through an introduction on it
- as well as
- showing how to calculate it in python
- and now we come to
- the lab exercise which tests knowledge
- of that material
- so it's two exercises um and the first
- one here
- uh i just directly gave you the numbers
- to use
- to calculate the free cash flows so it's
- kind of
- just about being able to uh go through
- that
- formula to calculate the free cash flows
- um so you can get used to
- uh the components that are involved and
- how you put them together
- and then level two is a little more
- realistic
- exercise of you have to actually load in
- um income statement and balance sheet
- data
- and calculate the free cash flows using
- those data directly um and you'll want
- to calculate two different periods
- of the free cash flows
- um so this is going to be quite simple
- uh
- or sorry quite similar to the
- example that we went through in python
- and you can feel free to
- use whatever tools um excel
- python or within python you can use just
- panos or you can use find statement
- however you want to go and calculate the
- free cash flows
- so that's the lab exercise for the
- historical
- free cash flows thanks for listening and
- see you next time
Introduction to Forecasting¶
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¶
- hey everyone this is nick diabetis
- teaching you financial modeling
- today we're going to be doing an
- introduction on
- forecasting this is part of our lecture
- segment
- on free cash flow estimation and
- forecasting
- which is part of a broader goal in the
- course of building out the full
- discounted cash flow valuation of a
- stock
- so we already covered uh the historical
- free cash flow
- part of all this so go back to the prior
- videos
- to see that and now we're just getting
- into
- thinking about well we need to think
- know about the future
- free cash flows in order to take the
- present value and get the value of the
- company
- and so that brings us into this whole
- area
- of forecasting
- um so forecasting uh
- is a fairly complicated topic there is a
- lot involved
- i could really teach an entire course
- just on forecasting we're really just
- going to look at a very high level
- overview
- on it in general um
- and it gets quite complicated once you
- get into
- the details uh you know it's a whole
- branch of econometrics to work on these
- time series models used for
- forecasting people make a living off of
- researching these things
- people have full-time jobs working on
- forecasting
- so it's definitely a full topic to
- discuss
- but we're just going to kind of you know
- briefly look through it and
- take the most high level things that we
- need to know about it just to be able to
- do
- our own simple forecasts
- so um as we talked about earlier in the
- introduction
- on this lecture series um
- we are going to be forecasting uh either
- the free cash flows or the financial
- statement line items
- and in order to forecast those we're
- going to use time series methods
- uh which are methods of predicting the
- future from the past
- and we're going to use those on one of
- possible one of a few possible targets
- either the levels of the item
- the growth of the item or
- the percentage that this item is of
- another item
- which is already forecasted
- so these time series methods is any
- method
- which we can use to
- predict the future from the past
- so we'll look at a number of
- simple models such as the average
- approach the recent value approach
- uh linear trend and compounded annual
- growth rates
- um and we'll also get into some more
- complex models
- which can be used for
- more complex time series such as a
- quarterly
- seasonal trend model and looking at
- more advanced models as well
- and thinking about this levels versus
- growth versus
- percentage of so what we're talking
- about there
- is levels means you're forecasting the
- value itself so sales was
- a million and then it's 1.5 million we
- might predict
- using a linear trend that is going to be
- 2 million
- for next year so we're using that
- million and that 1.5 million
- directly in our forecast to generate the
- forecast
- of 2 million so the the original
- values of whatever item are what we are
- feeding into the forecast model
- then looking at the growth means that we
- first calculate the growth rates
- on the item um so say sale grew ten
- percent then it grew eight percent
- and we're feeding those growth rates
- into the forecast model
- so we might predict again based off a
- linear trend that the next year's growth
- is going to be six percent
- uh and then we apply that six percent to
- the prior sales level value in order to
- get
- uh our forecasted sales
- and the last target that we're talking
- about is the percentage of another item
- so that means you have some other item
- which is already forecasted so say that
- we already have sales forecasted
- and now we're trying to forecast the
- cost of goods sold
- well we could say that you know
- historically cost of goods sold has been
- about
- 60 of sales uh and so now
- let's forecast the percentage of sales
- which cost of goods as a percentage of
- sales rather than the level or growth
- of cost of goods sold so then we
- forecast that it will also be 60 in the
- future
- and then we just multiply that 60 by the
- forecasted sales
- to get our estimate of the cost of goods
- sold and
- with all of these different forecast
- targets with any time
- series method that you're going to use
- you can still adjust uh the forecast
- afterwards you can say well you know
- here's kind of the baseline from the
- model
- but because of you know this new project
- that the company is going to start or
- uh this additional risk factor which has
- become more of an issue
- lately or whatever your analysis
- of the company's operations uh is you
- can incorporate that into your
- projections and say no i actually need
- to adjust
- some of these periods uh because of this
- additional information i have which is
- not reflected
- in the historical financials
- and you can also use this kind of
- qualitative information to actually pick
- which forecast method and target you're
- going to use
- so looking a little more at what these
- time series methods are again
- we are trying to predict the future
- using past information
- and there are a lot of possible models
- that we
- can use but the defining characteristics
- of all of them is that they're using
- something about the past
- in order to prevent predict the future
- and depending on the model it's using
- different characteristics
- of the past so it might be the trend it
- might be the growth it might be
- a historical deviations
- from the baseline forecasts i can adjust
- the forecast as well um
- so there's a lot of different um
- models that we can use there
- some of the simplest ones that you can
- use and we'll look
- at calculating these in a future video
- um simple very simple models we can just
- take an average
- of all the historical and say well it's
- going to be at that average
- going forward or we can take whatever
- was the most recent value
- and just say well that's our best
- estimate what it is now
- is our best estimate of what it's going
- to be going forward
- um and those
- are fitting in a number of
- uh forecasts it just depends on what
- you're forecasting it was something
- which tends to be very stable
- then these can be good models
- but most the time probably
- there is going to be some kind of trend
- or growth in
- the historical values
- so it's trending up or down or
- exponentially growing up or down
- and we want to continue that trend or
- growth into
- the future so we'll look at
- models uh for both of those
- and then there's a whole array of more
- advanced time series models
- and this is where a lot of that research
- that i mentioned is
- is around
- and so there are a few different general
- categories
- of how these models are different
- um one is auto aggressive terms
- so that means that you're directly using
- the recent values in the equation to
- predict the future
- our moving average means that
- um you're taking the historical
- you're taking the the forecast for the
- historical and the historical values
- calculating what was the forecast error
- how much were you off in those forecasts
- and those errors actually help you
- predict uh
- what the future uh is going to be
- or conditional heteroskedasticity
- which is a fancy way to say that
- the variance of the underlying data
- changes over time
- so if you're your line item is is
- uh you know very uh stays about the same
- and then it's changing a lot
- and it switches between that um then you
- should consider
- one of these kinds of models
- and so this is where we get into kind of
- the alphabet soup
- of models they tend to be just named uh
- with these different combinations of
- letters
- ar m a arma arch garch
- gam like there are so many of these
- types of models
- which are really uh going to be
- specialized for different purposes
- um so then you might say well
- we have these four simple models we have
- a bunch of more complicated models
- how do i go and pick which model i'm
- going to use
- so there's a number of factors that go
- into it
- and thinking about just the historical
- data
- the how much data you have is important
- as well as how frequent it is
- and whether there are any historical
- patterns and the data is going to be the
- biggest
- contributor to the decision of which
- model to use
- so if the data is basically constant
- historically
- then uh the the average or recent value
- approach
- should be a fine approach um and it just
- comes down to your
- um judgment call of whether you think
- that there's going to be some kind of
- mean reversion
- um and so the average would be more
- appropriate or if it's just going to
- stay where it's at
- and then the more the recent value
- approach would be
- the the best choice there
- but if um there's kind of a trend up in
- the data or
- trend down in the data whether that's
- more straight or it's more exponential
- um then you're going to want to consider
- using a trend or growth
- model so if it's a straighter line
- linear
- growth or decline you want to use the
- trend model
- and if it's more of an exponential
- growth or decline
- then you want to use the growth model
- and um if there are some kind of
- repeating patterns in the data like
- maybe it spikes up
- every like within every year maybe you
- have quarterly data and every
- you know second quarter uh the sales
- spike up
- these are cases where you're definitely
- going to need to use a more advanced
- model any of these for the
- average recent value trend and growth
- are not going to be able to capture
- these kinds of repeating patterns in the
- data that's where you definitely
- need to go to a more complex forecasting
- model
- but one you know there's there's a lot
- of decisions there to be made in terms
- of what
- model you're going to use but what is
- nice about forecasting is that
- regardless
- of what model you choose you go through
- the same process
- to do the forecast itself you just kind
- of switch out this model portion in the
- middle
- so if you understand the general process
- and you can learn the general process
- with simple models
- then it's easy to go and expand on that
- later learn the more complex models and
- just kind of plug them into this
- existing framework
- so the general process to forecasting is
- first you want to look at the historical
- data
- plot is usually a very good way to do
- this
- uh and if you just have a few numbers
- you don't necessarily have to plot them
- you can just look in the numbers
- but um as soon as you have more than a
- few data points it makes sense to plot
- it
- visually see it and what you're trying
- to do
- is get an understanding of that data
- so that you can ultimately make a choice
- on what time series model
- you're going to use
- so you want to look for patterns in the
- historical data whether it's a repeating
- pattern
- and you need a more complex model um or
- whether it's a trend or a growth rate
- and you want to use one of those models
- or if it's basically flat and you want
- to
- use the average or recent value model
- and you also want to consider any kind
- of
- qualitative knowledge that you have
- about it
- um you know if um
- it says in the company's 10k that
- um they're not going to be purchasing
- any new fixed assets
- over the next couple of years then uh
- no it doesn't make sense to use a trend
- if the historical data has been turning
- upwards well you already know
- that the company is not going to keep
- continuing on that path
- um so in that case you know maybe the
- recent value approach is is going to be
- better
- um and then the next step
- is then to fit that time series model
- and you'll have to make a choice there
- on whether you're fitting up on the
- levels
- the growth or the percentage of this
- item
- of another item but
- regardless of which you choose you're
- just fitting this model
- and then you're going to use the fitted
- model to predict
- the future um so you'll have some
- coefficients
- fitted from the historical data and then
- you use those coefficients to then
- predict the future data
- and then you have your forecast you've
- predicted the future
- and the last step is just to examine the
- forecast
- uh to make sure that you got kind of
- what expected and that it makes sense
- um so a plot is definitely the best way
- to do this
- you can look at a plot of both the
- historical
- and going into the forecasted and that
- lets you visually see
- well does this forecast seem to kind of
- continue
- the patterns which i saw in the past
- then i know that the forecast has worked
- correctly unless of course
- you've used your qualitative knowledge
- to adjust the forecast and then
- you know you're making sure that it is
- in line with your expectations of the
- company for the future
- so um
- we have to choose the time series model
- and recent value growth etc but we also
- have to choose
- what to forecast the levels the growth
- or percentage of another item
- um and
- there's a little bit different uh
- process of
- what you do depending on what you're
- forecasting
- when you forecast the levels um
- then you just forecast it and you're
- done the results from your forecasts
- are the results that you need and so
- you're done
- um and forecasting the
- levels makes sense when you have
- these more complex models which are
- really able to take in
- a lot of features of the data um
- in order to do the forecast
- and it makes sense for things which are
- growing
- more slowly or on a linear trend but if
- it's an exponential trend
- you might consider using the growth
- rates instead
- so when you want to forecast using the
- growth rates what you do is you first
- calculate the historical growth rates
- through the percentage change and then
- you
- run the forecast with the growth numbers
- instead of the original numbers
- and that will allow you to predict the
- future growth
- and then you take the most recent level
- and you do
- uh multiply it by one plus next periods
- of growth rate and keep doing that with
- each future growth rate
- to get uh the level forecasted
- into the future
- and then we have these this percentage
- of item approach
- uh where i think
- in deciding whether to use that you
- should think
- about the whatever you're forecasting
- um and how it relates to
- other things which you forecasted
- so having a knowledge of what each
- financial statement line item means and
- how it fits in the context of a
- company's operations
- is how you can guide these decisions
- uh kind of the most classic example for
- doing this
- is forecasting costs of goods sold
- cost of goods sold is almost always
- forecasted as a percentage
- of revenue because as you sell
- additional products
- you also have to increase the cost of
- producing those products
- basically it's the variable cost each
- additional unit that you sell
- you have to pay for the materials which
- you use to create that product
- and so the cost of goods scales uh with
- these sales and so it makes the most
- sense to just
- tie these two things together um
- and then you really just focus on
- getting the sales forecast correct
- and then cost of goods sold just becomes
- a percentage of that
- but you do still have to forecast the
- percentages themselves um
- most commonly people just use the recent
- value or historical average approaches
- but
- again you would want to just go through
- the same kind of evaluation of the
- historical data
- to determine which method to use but
- um you calculate the historical
- percentage so you divide say cost of
- goods by sales
- uh in the historical periods that will
- get you the historical percentage
- then you feed that into the forecast um
- you'll get as a result the future uh
- percentage
- of this item of the other item cost of
- goods sold as a percentage of sales
- and then you just multiply that by your
- forecast
- of the sales or whatever the other item
- is in order to get
- the forecasted levels for this item
- so yeah i have that same example here
- cost of goods sold as a percentage of
- sales
- so maybe you know maybe the historical
- periods were like
- 38 42 41
- and so you say well it's just kind of
- going up and down the historical average
- approach makes sense for the forecast
- so the average is 40 percent we're going
- to use 40
- going forward and then you already have
- a separate forecast of the sales
- so then you just multiply these two
- things together uh
- the forecasted sales with this 40 in
- order to get the forecasted
- cost of goods sold
- so that's an overview on
- everything that we're going to be doing
- in forecasting
- in this course we're going to come back
- next time to
- look at the simple
- forecasting models so thanks for
- listening
- and see you next time
Simple Time-Series Forecasting Models¶
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¶
- hey everyone
- this is nick diabetis teaching you
- financial modeling today we're going to
- be
- talking about simple time series
- forecasting
- models this is part of our lecture
- series on free cash flow estimation and
- forecasting
- as part of a larger goal of developing a
- full discounted cash flow valuation of a
- stock
- so in the prior video we gave a full
- overview on the whole forecasting
- process and all the considerations
- and pieces that go into that so take a
- look at that
- first if you haven't seen that already
- now we're getting into
- uh the simple forecasting portion
- of the lecture series where in this
- video we're going to look at
- simple models we can use to forecast
- time series which are not very complex
- the first model that we're looking at
- here is the historical average model
- um and basically all we're doing here is
- taking an average
- of the historical data and using that to
- predict the future
- so this is useful if uh there's not a
- clear
- trend up or down in the data it's kind
- of fairly constant but it's moving up
- and down
- and you think that it basically reverts
- to the mean
- then this is a good approach to use
- um and so it's just you know taking each
- uh time period in the historical data
- summing them up taking the average
- and then using that average to predict
- all the future periods
- so
- every every future period is going to
- have the same
- value for its forecast it's just all of
- it
- is at the average um
- and this epsilon here just represents
- the error and the forecast that you're
- never going to be able to forecast
- things perfectly
- second model we're looking at is the
- recent value model so this is
- even a little bit simpler than the
- historical average model
- here we're just taking whatever the most
- recent value was
- and assuming that it's going to stay
- there in the future
- um and we just use that same value for
- all the future
- periods so
- this this is applicable in mostly the
- same settings
- as the historical average model
- uh if the data does not have a defined
- uh
- trend up or downward um then
- this can make sense but in contrast to
- the historical average
- this is where you think uh whatever it
- was most
- recently is going to be a better
- estimate for where it's going to be in
- the future
- in other words there's not any kind of
- mean reversion in this data
- so that whatever we had is most recently
- is the best estimate
- then um we're gonna look here at
- two models which deal with when the data
- is going up or down
- over time so the first is the trend
- model
- um and so this is appropriate when it
- seems like it's linearly going up or
- down
- like kind of a straight line uh in the
- data
- whereas if it's kind of a curve an
- exponential change
- then you're going to want to use the
- growth model which we're going to look
- at next
- but if it's linear then uh the trend
- model
- is the most appropriate and so this is
- the first one where we actually have to
- do something other than a very simple
- calculation to actually fit it
- uh what we have to do to fit this is run
- an ols regression
- um so still not very complicated but
- certainly more than just using the last
- period
- um so that
- regression um you would estimate it with
- a constant um and
- the x or independent
- variable there would be t
- time the number of periods and so it can
- be
- you know one for the first period two
- for the second period three for the
- third period and so on that's going to
- be the variable
- you're gonna use as on the right hand
- side
- and then on the left hand side as your y
- your
- response or dependent variable um
- you're going to use whatever you're
- trying to forecast
- so you fit this regression and that's
- going to get you this coefficient
- on the time variable and that's what we
- need to get out of this fitting process
- so then in order to predict that into
- the future
- we then calculate uh
- the the fitted model by using that
- coefficient that we calculated
- from the historical multiplying it by
- the time period um and adding the
- constant which was estimated
- so uh if you had five historical periods
- those might be one two three four five
- as t
- in your uh regression
- then you wanna forecast one period in
- the future that's going to be period
- six so you're going to plug in 6 for t
- here
- and the estimated coefficient
- and intercept then you want to predict
- the next period you're going to plug in
- 7
- period to that you're going to plug in
- eight and so on
- and this is able to capture a linear
- trend up or down
- in the data
- but if that change over time
- going up or down is is more of a curve
- exponential
- rather than a straight line
- then we want to use the growth model or
- compounded annual growth model
- um so what this says is basically we're
- calculating
- the per period growth rate in the
- historical data
- and we're going to apply that growth
- rate going forward
- so you can calculate the compounded
- annual growth rate which is basically
- what
- growth rate would you have to have had
- in each period in order to have the
- growth over the full
- time period
- so you do that by just taking your most
- recent value
- and dividing it by the earliest value
- that you have
- um taking all of that taking it to the
- one over number of periods power so that
- would be
- how many periods are in between that
- beginning of the historical data and the
- end of historical data
- um you raise that fraction to that power
- and then you subtract one and that's
- going to get you the compounded annual
- growth rate or how much should the
- growth rate have been in each period
- to grow as much as it did over the
- entire range of the historical data
- so then once you have that kagger fitted
- then you can predict the future using it
- so uh you know we're just going to use
- the standard
- like growth kind of formula
- where you just take whatever the last
- period was
- and you multiply by one plus the growth
- rate
- compounded at however many periods you
- want to forecast into the future
- so if you're just forecasting one period
- in the future it's just times one plus
- the growth rate
- if you're going two periods in the
- future is times one plus the growth rate
- squared
- and so on um
- so that's how you uh calculate the
- kagger model so
- that's the four different simple
- forecasting
- models that we're going to look at and
- collectively these four models are
- useful uh
- whenever the historical data is fairly
- simple
- now if there are repeating historical
- patterns in the data
- uh maybe there's a spike uh you know
- over and over
- in the data these models are not going
- to be appropriate that's where you need
- to go to the more complex models but
- if it's you know just kind of staying
- around the same or it's just turning up
- or turning down
- then these simple forecast models can
- work just fine
- so that covers the symbol forecast
- models we're going to come back
- next time in the next uh two videos to
- look at
- how we can carry this out in both excel
- and python
- so thanks for listening and see you next
- time
Simple Time-Series Forecasting in Excel¶
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¶
Transcript¶
- hey everyone
- this is nick dear burtis teaching you
- financial modeling today we're going to
- be talking about
- simple time series forecasting in excel
- this is part of our lecture segment on
- free cash flow estimation and
- forecasting as part of a broader goal of
- building out the full
- discounted cash flow valuation of stock
- so we already covered the overview of
- forecasting as well
- as the simple forecast models in
- previous videos so make sure to take a
- look at those if you haven't already
- now we're getting into examples of how
- to
- do this and in this video focusing on
- the excel side
- of how we carry out these simple
- forecasts
- uh with simple models so let's go over
- to an excel sheet which just has the
- data on sales and cost of goods sold
- across three different years
- and the goal here is to forecast next
- year's
- sales and cost of goods sold
- so we will look at the four different
- approaches
- uh four different uh models as well as
- forecasting the levels of the items and
- forecasting
- the percentage of cost of goods sold as
- a percentage of sales
- so um
- we can make a little extra space here
- um so
- see um we're going to
- um have potentially different
- method for sales and cost of goods sold
- um just keep them the same
- um so we can have the
- trend model the compounded annual growth
- model the average
- model and the recent value model
- and we'll just use those same methods
- across both
- um so the
- trend model um
- we um need to run a regression ols
- regression
- um using time as the
- uh independent variable so
- we have to create that time variable so
- we can just start
- at zero you can also start it at one or
- whatever
- so 2017 is zero 2018 is one and
- uh 2019 is two the important thing is
- just that it increases by one
- each period
- and we could go and use the data
- analysis tool pack
- regression in order to do this
- but considering that there's only a
- single independent
- variable uh we can do it just with plain
- excel functions using the slope and
- intercept
- functions so
- we can
- come over here so this is going to be
- our sales forecast and this is our
- our cost to get sold forecast
- so we can use the slope function to
- find the coefficient on the
- t variable and the regression
- so the y here is going to be the values
- for
- sales and the x is going to be
- this value for t and now for t
- uh we're gonna want to have this totally
- fixed because when we drag this down for
- cost of goods sold we don't want it to
- move
- and if we were doing this over in a
- different spot uh we wouldn't want it to
- move
- either so totally fixed on that
- and then for the sales we do want it to
- move down
- when we estimate cost of goods sold but
- we don't
- want it to move left or right so we want
- it fixed on the column but not fixed on
- the row
- um so fixed on the column
- we want the dollar sign in front of the
- column letter
- uh so there we go
- uh so that's the slope slope is 50. um
- and we want to multiply that slope
- by whatever that last period
- as t was plus one to get one
- period into the future and then we want
- to add the
- intercept from the regression so
- again similar fixing here
- fixed on the column
- and here completely fixed
- um so that gets us our estimate of the
- next period
- using the trend approach and we can drag
- that down
- to then uh get the same thing for cost
- of goods sold
- as well
- now coming to the compounded annual
- growth
- uh so the first step in that model is to
- calculate the historical compounded
- annual growth
- and then we're going to apply it into
- the future
- so to calculate that compounded annual
- growth we're going to take the ending
- period
- divided by the beginning period and then
- we're going to take that to the power of
- uh this period's t minus
- the original periods t and then subtract
- one
- at the end um
- okay that doesn't seem right
- um sorry it needs to be
- one divided by the
- difference in periods
- there we go now that looks right growing
- at almost five percent per year
- um so i'll just format that as a
- percentage
- and then i can drag it down to get the
- same for
- the cost of goods sold
- but we'll notice i didn't fix this
- appropriately so let me
- fix these dates
- and then drag again and now it looks
- right
- okay so now we can take that
- calculated compounded annual growth rate
- and apply it to the most recent period
- so we grab the most recent period here
- again fixed on the column not on the row
- multiply by 1 plus
- the compounded annual growth rate fixed
- on the column
- but not the row
- there we have our estimate of the next
- period and we can get the same
- for the cost of goods sold
- and now the average approach you just
- take an
- average of the historical data uh nice
- and straightforward
- fix on the column now on the row again
- um and the recent value approach
- you just take the most recent value and
- again
- fixed in the column not in the row
- so those are four different methods
- on the levels
- um now we can think about doing the same
- but with um
- these different um
- but but using a percentage forecasting
- the percentage
- uh cost of goods of sales instead
- so um in order to use the percentage
- methods we first need to calculate the
- historical percentage so we're just
- going to take
- cost of goods sold divided by sales
- that's going to be the historical
- percentage
- and we want to calculate that for all
- the years
- and we can get the compound and annual
- growth rate of that as well
- and now we have kind of um
- we're focusing now on just getting the
- cost of goods sold forecast
- but now the cost of goods sold forecast
- is affected by the sales forecast as
- well so we have the four different
- methods for forecasting sales and we
- have the four different methods for
- forecasting the percentage
- so that leads to 16 different uh ways
- that we can now forecast this
- um so let me take these same
- uh for the sales
- copy we're going to have these same
- four methods for sales
- and we should be able to just copy these
- as well
- since i did all the fixing appropriately
- okay and now
- we want to go and estimate the
- percentage of sales
- so um we can
- uh then within that we're going to have
- the different approaches so
- for here at the average for here
- at recent value approach
- um we're gonna have four at the trends
- and for at the compound and annual
- growth
- so for the average uh we should be able
- to just copy this over
- hopefully that worked yep so then we can
- use that for all of these
- and for the recent value should be able
- to copy that
- all of this working because we did the
- fixing appropriately
- so recent value is grabbing that recent
- value
- trend we can
- see it is using the trend model
- appropriately
- so bring that over for those four
- and then compounded annual growth rate
- um
- we can see that it is applying that
- to the most recent period
- so then we can drag that over as well
- so then in order to get the actual
- estimate of the sales we just multiply
- these two things together the forecasted
- sales by the forecasted percentage
- and that gets us our estimate of the
- cost of goods sold
- so we have all these different estimates
- that are coming from putting these two
- different methods together
- so some other things that we might want
- to do in the forecasting process
- um you know normally you wouldn't just
- calculate all these models that's really
- just for sake of example
- what you would do as a first step is
- graph the data we want to plot it so we
- can see what it's looked like
- historically so we're going to insert a
- chart
- line chart works well for this
- so this would be the
- financials over time
- um and there we can see
- you know both of these are fairly flat
- they go up and then they go down
- so uh you know maybe the the recent or
- average value approaches would be fine
- for this
- another thing we might want to do since
- we have gone through the process of
- using all these different
- forecast methods we can
- take all these different costs of goods
- sold forecasts that we have
- and we can do
- a box and whisker
- plot on that
- um which is basically going to tell us
- uh the distribution
- of the possible forecasts based on the
- method
- so cogs forecast by method
- um and we can see that forecasts are as
- like low as 6 25 or so go up to 720
- but most of them fall in this 650 to 690
- kind of range
- so then we understand what the range of
- possible forecasts could be
- based on the method
- so that's a quick overview of how we can
- carry out these simple forecasting
- methods in excel in the next video we'll
- come back and look at this on the python
- side
- so thanks for listening and see you next
- time
Simple Time-Series Forecasting in Python¶
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¶
Transcript¶
- hey everyone
- this is nick dierbertis teaching you
- financial modeling today we're going to
- be talking about
- simple time series forecasting in python
- this is part of our lecture segment on
- free cash flow estimation
- and forecasting which is part of our
- broader goal in the course
- to build out the full discounted cash
- flow valuation of stock
- so we already covered introduction
- on forecasting and the simple
- forecasting
- models and also how to go about the
- forecast in excel
- so take a look at those videos um
- now we're getting to carrying out the
- process in python
- so let's jump over to the jupiter
- notebook
- example for this which is on the course
- site
- and here we're going to load in the same
- data
- that we used in the excel exercise
- sales and cost of goods sold for three
- different periods
- and we're going to try and forecast the
- next period into the future
- so just as we had seen with other
- financial statement data
- we should set the first column as the
- index
- so that we can easily look up these
- items
- by their names i will also solve this
- unnamed issue here and allow
- the date columns to work appropriately
- so um the first
- step in the forecasting process is to
- examine the history
- and so we can plot it
- but you can't just easily plot the data
- in this format really we need it to be
- flipped because uh we're plotting over
- the
- index the each column over the index
- um so with this we would have like a
- 2017 a 2018 and a 2019 plot that
- connects sales and cost of goods sold
- not what we want
- so thankfully with pandas we can do dot
- capital t and that transposes the data
- frame
- so now we have the columns as those
- statement items
- and the rows as the time periods which
- is the format
- we need to be able to plot it so if we
- just
- uh take that transposed data frame and
- we do a line plot
- on that then we can see sales and cost
- of goods sold
- over time so it kind of goes up and then
- down and so
- the average or recent value approach may
- be appropriate
- but we'll look at all the different
- simple approaches
- to see how we can do each of them so
- first we can look
- at the recent value approach um
- so we're gonna need to pull
- the um most recent value of sales and
- cost of goods sold
- so one thing we can do to make our life
- a little bit easier there is save
- that last period into a variable
- and we can get that value by looking at
- the columns of the data frame
- and taking the max uh within that
- so um we see that 2019 is the most
- recent period here
- and so now we can
- use the outlook which gets us the entire
- row of sales or cost of goods sold and
- then
- also uh look up the last date
- within that so that will get us the most
- recent
- period out of uh whatever time series
- so we'll do that for both sales and cost
- of goods sold
- and then because with the recent value
- model we just use the recent value going
- forward
- those are our forecasts so um there
- we have forecasted value for sales 1100
- and for
- cost of goods sold 650
- next we'll look at the average uh
- historical average approach method model
- for forecasting
- so for that we're just going to take an
- average of the historical
- so we're going to pull out that sales
- row and take the average and pull out
- that cost of goods sold row and take
- the average and those are our forecasts
- for
- going forward
- so the the trend is the first one that
- actually takes
- a little bit of effort to do we need to
- run an ols regression
- uh with the t uh just
- an incrementing time period variable
- as the independent variable and
- our our values as the y
- and we're going to estimate the
- intercept and the coefficient that we're
- going to use
- in calculating the forecast going
- forward
- so first thing that we need to do is set
- up
- a data frame which um
- is going to be in the structure to run
- the regression so we can first
- grab that sales column out of the data
- frame
- and make a new data frame out of that so
- that now it's in this
- transposed format
- um you also could have transposed it and
- then looks up the sales column that
- would have worked as well
- and then um
- what we want for t is like zero one two
- um so we can do that by
- kind of using pandas's automatic index
- to our advantage so if we use reset
- index
- to get rid of uh this
- date index here we use drop equals true
- to make sure it doesn't come in as a
- column in the data frame
- now we have 0 1 2. this is ultimately
- going to be our t but we have to get it
- to come as a column
- so we um
- [Music]
- do reset index again this time without
- the drop
- and that says take what was the index
- and now make it a column
- in the data frame and generate a new
- auto incrementing index
- um so then we can just rename that
- column
- as t and now we have this t
- column in here um this is certainly not
- the only approach to
- getting this uh t variable but
- it's straight it's a straightforward way
- to do it
- so that was a number of steps there
- uh and we're gonna need to do the same
- thing for both sales and costa gets sold
- so let's create a function which does
- those steps for us
- creates the new data frame from that
- particular
- line item resets
- gets rid of the data index and then uses
- the
- auto incrementing and next as t
- so then we can run that from the
- original
- data frame and just pick
- which line item we want to use it on and
- it generates
- the data frame and the correct structure
- for us
- um so then the next step is then to run
- the regression
- um so
- here we're using stats models to run the
- regression and we're going to run
- an ols regression
- and whatever our line item we want to
- predict is going to be the y
- and then the x is going to be the t and
- make sure to add the constant
- um and then we fit the model
- and we get the summary of the results so
- there we can see
- the um intercept was estimated as 1050
- and that the coefficient on t is 50.
- so we can pull those out of the results
- by
- accessing params on the results and
- pulling out the const
- and the t
- so now we have those as individual
- variables
- so now we have fitted the model we just
- need to predict
- um and so the prediction is just you
- take the intercept plus the
- beta times the t or whatever period you
- want to forecast
- and because 2 was the t of
- the last period 3 is going to be the
- next t
- and so that is our forecast of the sales
- using the train method
- so um we can
- also make some functions
- for this process as well running the ols
- regression and getting the beta and
- intercept and predicting based on that
- beta
- intercept and the t um
- so then we can use those functions
- instead and we can make one more final
- function that puts all these steps
- together
- create that data frame and then run
- these
- uh run the regression and predict from
- the regression
- now we have one function which is able
- to do the full
- uh trend regression forecasting process
- so you just give it the data frame the
- name of the
- item that you want to forecast
- and the number of
- time periods that you want to forecast
- it or the
- the uh t value of the time period that
- you want to forecast
- so if two was your last historical then
- three is one period into the future
- and then we can just use that function
- on the sales the cost of goods sold
- and that will allow us to get our
- forecast here
- and you can feel free to take these
- functions into your own model
- to do this as well
- so then we also have the compounded
- annual growth rate approach
- so we
- can still use that data frame which was
- set up for the forecasting so let's take
- the one for sales
- and now we want to calculate this uh
- kaiger formula so we want to get the
- first
- sales so we can use a.i look
- to get the first value and uh the last
- value
- so we set that as y0 and yt
- um and we can get the number of time
- periods
- by doing the same thing on the t
- variable so two
- time periods elapsed
- between the first and the last period
- and then we can just calculate the
- kagger formula and we have the
- the kagger and then we'll want to
- apply that going forward into the future
- so to apply that then you just
- multiply the last value the yt
- times 1 plus the compounded annual
- growth rate
- to the power of the number of periods in
- the future
- that you want to forecast
- um
- oh that's how you do exponents in python
- i'm a little tired today
- um so there we go that's our forecasted
- value
- and now we can put that into
- um formula and two functions
- one that gets the compound and annual
- growth rate doing those steps we just
- looked at
- and the other which uh creates the
- forecast
- from that compounded annual growth rate
- um so using those two functions
- then we can get the forecasted value
- and we can put those together into one
- final function which
- is the forecast data frame calculates
- the compound anal growth rate and
- applies that into the future
- uh one function which does it all for
- the kagger approach and then
- we can use it in the same way as that
- the regular trend approach
- and we get the results there
- so that was all working with the levels
- of the sales and cost of goods sold
- but usually cost of goods sold is
- forecasted as a percentage
- of sales so let's look at doing that
- so the first thing that we want to do is
- create a row in our data frame which
- represents the cost of goods sold as a
- percentage of sales
- so i'm going to assign a new row
- by taking the cost of goods sold divided
- by
- the um sales
- and then we've already built out these
- forecasting
- uh functions so we can just use them on
- that percentage as well with the trend
- we would estimate that the next
- periods cost of goods sold as a
- percentage of sales would be
- 57
- and then we just multiply that by the
- forecasted sales value
- and that gets us our cost of goods sold
- forecast
- so to kind of put everything together
- around this exercise
- let's make one more function here which
- um
- you can give it the name of the method
- that you want to use
- and it's going to run that particular
- forecast method
- so using the two functions which we
- developed
- for the trend regression and the the
- kagger approach
- as well as just taking the most recent
- value
- and taking the average of the historical
- values
- all these now incorporated into one
- function
- so now we have these four different
- methods
- and we want to forecast the next period
- so then we're going to go through each
- method and we're going to forecast sales
- by that method
- and then uh for each of those
- uh sales methods we're going to go
- through the methods again for the cost
- of goods sold
- and then within that we're going to
- forecast the levels
- of the cost of goods sold and forecast
- the
- percentage cost of goods sold as a
- percentage of
- revenue and uh calculate the levels
- based on that so when we do that then we
- get
- lots of different results here for
- possible
- um results for the cost of goods sold
- depending on
- which method was used for the sales and
- which method was used for the cost to
- get sold
- and whether the levels of the cost of
- goods sold or the percentage
- of sales of cost of goods sold was
- forecasted so lots of options here even
- with just
- the simple forecasting models
- and since there were so many approaches
- here it's useful to visualize that it's
- kind of hard to read all this text
- output
- so we can use a box plot to look at the
- distribution
- of these forecasts and that shows us
- that
- the forecasts basically go down to about
- uh
- 6 20 or so 6 25 maybe
- up to like 7 almost 7 20
- but most of the forecasts lie between 6
- 50
- and 6 90.
- so now we have an idea of kind of the
- general range of the forecast
- depending on the method that we choose
- so that's a quick overview on how to
- use the four different
- simple time series models that we're
- covering
- in this course and applying it to both
- levels
- and percentages of items so
- thanks for listening and i'll see you
- next time
Simple Time-Series Forecasting Lab Overview¶
Notes¶
This lab exercise follows closely the structure of the previous example
Through this you should understand the different simple methods
Resources¶
Transcript¶
- hey everyone this is nick d robertis
- teaching you financial modeling
- today we're going to be introducing the
- lab exercise
- on simple time series forecasting
- this is part of our lecture segment on
- free cash flow estimation and
- forecasting
- as part of a broader goal to build at
- the full discounted cash flow valuation
- of a stock
- so we finished up
- in prior videos the overview of
- forecasting
- and all the simple forecast models
- uh and then we went through examples of
- how to run these simple forecast models
- in both excel and python so now
- we are coming to the lab exercise which
- uh cements understanding of all these
- topics
- and it's a very similar exercise to what
- was shown in the excel and python
- examples
- so you're going to take this debt
- interest file which has
- information on the
- total debt and the interest payment
- and you want to
- [Music]
- basically forecast uh with all the
- different approaches
- and you want to forecast on for interest
- you want to forecast that both in terms
- of levels
- as well as forecasting it as
- a percentage of debt
- so infest is another common example
- that is typically percent forecasted as
- a percentage
- of uh total debt or long-term debt
- rather than at levels so it's a good
- uh fit for this exercise
- um and then you can find all the answers
- here
- on the um slides as well
- so that's the review of the lab exercise
- we'll come back next time to talk about
- more specifically forecasting financial
- statements as a whole
- with these simple forecasting methods so
- thanks for listening
- and see you next time
Forecasting Simple Financial Statements in Python with finstmt¶
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¶
Transcript¶
- hey everyone this is nick dear burtis
- teaching you financial modeling
- today we're going to be talking about
- forecasting simple financial statements
- in python using fin statement this is
- part of our lecture series on
- free cash flow estimation and
- forecasting which is part of a broader
- goal in the course of
- building out a full discounted cash flow
- valuation
- of a stock so
- we had just finished all the simple
- forecasting material in prior videos
- and um we had looked at
- examples of how to do simple time series
- forecasting
- but now this is focusing on working with
- the entire financial statements
- in a forecast and using the fence
- statement package
- to smooth that process
- so we can jump over to the example
- jupiter notebook so
- um the first thing that we'll do here
- is uh load in the
- financial statements so
- we are going to um
- create data frames from the csv files
- that have the statements
- and then create the balance sheet
- income statements uh both from
- those data frames and then create the
- financial statements
- from the income statement and balance
- sheet
- so since we've done that we can view
- the statements so we see the income
- statement
- and all the different periods of values
- and we see the balance sheet also with
- all the different periods of values
- so um we're going to look at
- forecasting um we have a couple of
- different
- uh configuration settings um but here is
- a little aside to say
- uh it's often the case that you might
- want to run multiple different forecasts
- so if you know that's true then you can
- make a copy
- of the statements to keep uh any changes
- that you make to the configuration
- separate so that you can run multiple
- completely separate forecasts
- so here is how you would copy the
- statements
- into uh additional uh
- statements which are now totally
- separate from the first
- so now let's jump into forecasting
- um so what we have
- um in order to understand what
- assumptions are going into the forecast
- is this dot forecast assumptions uh
- attribute on the financial statements
- object
- so when you look at that as penis data
- frame
- of basically each
- line item in the index and some details
- about how the forecast is going to be
- carried out
- so the first column here is
- the method so you can choose
- among one of the available forecast
- methods in order to do the forecast
- and it defaults to the compounded annual
- growth rate
- but we can also choose to use the
- average
- approach the recent value approach or
- the
- trend regression approach
- and when we get into looking at more
- complex time series we'll see another
- approach which is helpful there as well
- and then the next column here is talking
- about
- is this item forecasted as a percentage
- of another item
- so you see here that revenue is not
- forecasted as a percentage of another
- item it's forecasted standalone
- but cost of goods sold is forecasted as
- a percentage of
- revenue and it has kind of the standard
- assumptions that you would normally
- see for forecasting items as a
- percentage of other items
- but you can adjust that further if
- needed
- um and then next we have
- cap and floor that's in case you want to
- put
- a minimum or a maximum on your
- forecasted values
- and then finally here we have plug which
- is about
- balancing the balance sheet so the
- library will automatically
- balance the balance sheet for you based
- off of the forecasts
- but in order to do that it does have to
- avoid uh forecasting uh
- at least one one or two items
- uh typically one on the assets uh
- one on the asset side and one on the
- liabilities side
- liabilities or equity side um in order
- to balance the balance sheet based off
- your forecast it has to
- manually adjust these values
- and so by default cash
- and long term debt are going to be the
- plug items
- so if the assets are too high from your
- initial forecast it's going to increase
- the debt
- and if the assets are too low then it's
- going to
- increase the cash basically
- but you can adjust which items are the
- plug
- should you want to directly forecast
- cash or directly forecast long-term debt
- and have
- something else serve as the plug
- so we can update this configuration
- using the update method so we
- on the statements we have this
- config and we can
- update that um
- so we have update and update all um here
- update
- all so all of these forecast assumptions
- are going to be under
- the forecast config so you pass first a
- list
- uh where the first item will be forecast
- config
- and the second item will be which one of
- these that you want to
- update so here method
- and then that list is the first argument
- what you want to update
- and then the value you want to use to
- update it comes next
- so this sets the compounded annual
- growth rate method for all the forecasts
- of course we already had that
- uh we could also set it a trend and see
- that come through
- on all the assumptions
- um and you can also update
- a single item um so to update
- a single item then it's um
- going to be a similar syntax and we'll
- see that here
- in a moment but i'm going to switch it
- back to the
- compounding growth rate method
- so the way that we forecast once we have
- decided that our
- assumptions are good enough to run the
- forecast then
- um there's just a dot forecast method on
- the financial statements and then you'll
- see
- some output go by
- and the warnings that you see here are
- about
- uh the compounded annual growth rate not
- being an appropriate method for some of
- the items
- basically the items which are at zero
- the entire time it can't calculate a
- growth rate
- um so you could set those to trend
- if the warnings bother you otherwise
- it's just going to remain at zero anyway
- um and then we can look at the
- forecasted statements
- so uh by default it's going to forecast
- for five periods into the future you are
- able to
- adjust that in the forecast
- arguments um
- and then
- we have all of our forecasted statements
- here but it can be a little difficult to
- see what happened just from the numbers
- so there's also a plot method which is
- going to show us
- all of the different forecast plots
- which
- we can easily examine to understand what
- happened with the forecast
- so we can see the forecast for revenue
- we can see that forecasted cost of goods
- sold as a percentage of revenue and
- all these other line items as well
- so all that just kind of happened in the
- background for us
- and now we have forecasts for everything
- if you want to just look at a few plots
- you can pass the subset to the plot
- method
- so you don't have this huge output every
- time
- so um if you want to
- update particular items you can use this
- syntax so it's the same as the update
- all syntax except we first
- put the name of the item that we want to
- update
- um so this is going to set revenue
- to the trend forecast method and it's
- going to set
- cost of goods sold to the mean forecast
- method
- so when we look at the forecast
- assumptions then we'll see that those
- have
- updated revenues now trend cost of goods
- sold
- is now a mean but still a percentage of
- revenue
- so then we can run the forecast again
- and we will see the results here in a
- moment
- it does take a little bit of time to
- balance the balance sheet
- and then we can look at the results that
- come out
- um so we'll see now in contrast to
- before
- we can see that um
- before the cost of goods sold was using
- a compound or annual growth rate
- approach
- and now it is at an average approach
- instead
- and same thing with the revenue
- compounded annual growth rate
- before and now it is using a trendline
- approach
- so um then it's often the case that you
- don't just want to take
- kind of the baseline result that you get
- from the forecast you may have some kind
- of
- qualitative information that you can use
- to adjust the forecast such as some
- new project that you know is coming on
- for the company or some other change in
- the operations which is not reflected
- in the historical information so
- i built this into the library where we
- can adjust
- an existing forecast
- um so after you've done the forecasts
- then you can access uh the forecast by
- forecast and then the name of the
- statement item
- and then you can convert it to a manual
- forecast
- where we just have fixed values for the
- forecast
- and as you're converting it to a manual
- forecast
- then you can make adjustments or you can
- replace
- uh different parts of that forecast
- so and by default it
- uh works in growth rates but you can
- also pass
- use levels equal true to work with the
- levels of the item
- um so the um
- first one here we're adjusting cash
- and it's going to adjust the first
- period
- zero index period
- to increase the growth by 40
- in that period the second example here
- um is on the levels instead of on the
- growth rate
- and it's going to replace uh
- each of the values
- for the cost of goods sold as a
- percentage of sales forecasts with 85
- and then this last one on the revenue
- again on the levels and it's going to
- replace
- the second period value with
- uh 80 billion dollars
- so i do that and then uh run the
- forecast again
- and we'll look at the results that come
- out
- um
- so we have um now in the
- revenue we see that that second forecast
- period was replaced
- by uh 80 billion
- and the cost of goods sold as a
- percentage of revenue
- we see that now it's flatlined at 85
- percent instead
- and in the cash uh we see that we have
- an additional
- uh bump at the beginning uh of a 40
- growth uh for the first period
- so um and then when we look at the
- forecast assumptions there
- we see all these things that we've done
- so revenue cost of goods sold
- and cash got converted to manual
- forecasts
- and now it specifies
- what values are just basically hard
- coded into the forecast
- and we see all our other forecast
- assumptions
- there
- but we made that copy of the statements
- at the beginning because we might want
- to do another forecast
- and we look at that one and we see that
- none of the assumptions have been
- touched
- so that's why we did the copy before
- going and modifying the assumptions
- that way we have a totally clean set of
- assumptions now
- that we can go and run a completely
- separate forecast
- so that's a quick overview on using
- fin statement to do financial statement
- forecasting uh
- we'll have another video where we look
- at uh
- forecasting complex time series
- in financial statements also using fin
- statement
- so thanks for listening and see you next
- time
Complex Time-Series Forecasting¶
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¶
- hey everyone
- this is nick dear burtis teaching you
- financial modeling today
- we're going to be talking about
- forecasting complex
- time series this is part of our lecture
- segment
- on free cash flow estimation and
- forecasting
- so we already covered how to forecast
- more simple time series which is
- represented by basically it being
- flat or i'm just trending up or trending
- down
- and pretty clear simple way
- but it's the case that you have a more
- complex pattern
- going on in your financial statements
- so this kind of pattern
- is what i'm talking about where you see
- just repeating patterns over and over in
- the data
- we can see that there's also a trend
- going on here
- but there's this repeating pattern
- which makes this a complex time series
- and a simple trend or
- growth kind of model is not going to be
- able to represent
- these repeating spikes in the data
- this is uh walmart's quarterly sales
- that i've pulled here
- in order to show this plot
- and um basically
- what's going on is that there's a
- seasonality here
- in the data walmart is
- going to sell more around christmas time
- the holidays
- um and so we see those spikes happening
- year over year every year
- so if you want to forecast the quarterly
- sales you have to be able to account for
- that
- some quarters are going to be better
- than others year over year
- so we can kind of break down
- this uh plot
- into two separate plots one looking at
- the trend component
- and one looking at the seasonality
- component
- to better understand what's going on
- here as we had already seen it is
- turning upwards over time
- and so we see that trend effect going on
- here
- um and then this is representing
- throughout the year
- where are the spikes um so we see this
- spike in the december time frame
- and we also see that that sales tend to
- be low
- after after that initial time frame
- going into march
- so here a certain
- time series model was used that does
- allow to
- decompose these two different parts
- um and
- it's useful to do this kind of
- separation because then you can really
- understand
- the seasonality component in more detail
- which gives you an insight into what's
- going on
- in the business
- so you've got a complex time series
- you've looked at the historical plot
- and you say well this isn't just simply
- going up or
- or exponential or flat
- there is some kind of repeating pattern
- that i need to be able to predict
- so what do i do
- so in general it is a little more
- complicated
- or substantially more complicated than
- the simple
- forecasts still the overall process
- is the same you know you fit the model
- on the historical data and then you use
- that fitted model to predict the future
- but picking the model to use for your
- complex time series
- can be in a complicated and involved
- process
- so i've given a resource here that you
- can read more about that
- if you're interested um
- but there is a simple approach that we
- can do
- specifically for the case of um
- this seasonality issue if you're just
- trying to capture
- seasonality in your forecast then
- there's
- a fairly simple model we can use for
- that and we'll look at that
- explicitly and so when dealing with
- quarterly data we
- call that the quarterly seasonal trend
- model
- but that may not ultimately produce the
- best
- fitting forecast there may be other
- features
- of the data that you need to predict
- than just the seasonality
- and it's definitely outside of the
- scope of the course to discuss all the
- different
- models that you could possibly use for
- all different use cases
- but what i can recommend that you can do
- instead
- in the meantime until you you know want
- to go
- and learn all of that is there are
- software solutions
- which can help you pick the appropriate
- model
- so profit is one such
- uh software package which does this it's
- created by
- facebook they use it for their in-house
- uh forecasting of uses metrics and
- things like that
- and we can just use that in order to
- predict our complex time series without
- having a deep understanding
- of all the details of how you should go
- about
- uh selecting these more complex models
- and as we'll see um in a future video
- here soon
- into the fin statement package which
- i've created
- i've also built in an integration
- with profits so you can automatically
- use profit
- through if and statement
- so let's just uh go through the
- quarterly seasonal trend model
- that's going to be the one complex model
- that we're going to look at actually
- estimating
- and we're just going to say for
- everything else we can just
- use profit in order to automatically
- fit the model for us so the quarterly
- seasonal trend model uh it looks a
- little bit like the trend model
- all it is the trend model but then plus
- these additional components
- and these additional components are
- including dummy variables
- and one dummy variable for each quarter
- and so
- by that uh approach then you're going to
- um have a different uh
- coefficient fit for each quarter
- uh so that it's able to adjust and say
- some quarters should be higher
- than others um so these dummy variables
- are just going to represent uh
- that it is this quarter or it's not this
- quarter
- so if your sales
- or whatever observation is
- for first quarter then this variable is
- going to be one
- and all the others are going to be zero
- whereas
- if it's second quarter then this one's
- going to be one the others are zero
- and so on these dummy variables are just
- one or zero one to represent
- true zero to represent false
- and it's going to be true or one
- whenever
- this particular observation this
- time period is within
- that quarter of the year
- so to fit this basically we have to
- create these
- um dummy variables and then
- we just throw them in there in the trend
- regression model
- and that will fit this quarterly
- seasonal trend model
- as far as predicting it so
- you're just going to
- fit it like the trend model so this this
- constant plus the
- uh beta times the time period
- but you're also going to add on the
- dummy
- corresponding to the appropriate quarter
- so the next period that you're
- forecasting is first quarter
- then you're going to add the coefficient
- on the first
- quarter dummy if it's the second quarter
- then the second quarter coefficient
- third quarter third coefficient and so
- on
- and you're only adding one of them
- because again these are dummy variables
- only one of them
- is going to be uh taking a value of one
- at a given time and all the others are
- going to be zero
- corresponding to whichever is the active
- quarter
- um and then this is the quarterly
- version of the model
- but you could fit it for any kind of
- time horizon
- you could do a monthly seasonal trend
- model if you have monthly data then you
- would have 12
- different of these dummy variables
- corresponding to
- whether the observation is within that
- month
- and the dummies don't necessarily have
- to correspond to the frequency
- of your data either it could be that you
- have daily data
- but then you're still fitting a
- quarterly model
- if you think the seasonality itself is
- actually quarterly
- you want the frequency of the model to
- line up with
- the frequency of the seasonality rather
- than
- the frequency of the data
- so that's a quick overview of
- forecasting these more complex time
- series
- we'll come back in the following videos
- to look
- at estimating this quarterly seasonal
- trend model
- and then using the auto forecasting
- through a profit with fin statement
- so thanks for listening and see you next
- time
Complex Time-Series Forecasting in Python - Manual Method¶
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
Transcript¶
- hey everyone
- this is nick dear burtis teaching you
- financial modeling
- today i'm going to be talking about
- complex
- time series forecasting in python
- and this is going to be covering the
- manual method
- whereas we will look at a more automated
- approach
- in the following video this is part of
- our lecture series on free cash flow
- estimation
- and forecasting
- so we already covered uh basically what
- a complex time series is and um
- when you would want to use one of these
- more complex models to forecast it
- and now we're just going into the actual
- example
- of how to carry that out
- so we're going to look in this video at
- applying the quarterly
- seasonal trend model to be able to
- forecast
- quarterly data which has seasonal
- effects in it
- so let's go over to the jupiter notebook
- which is on the core site so i'd
- encourage you to go through it
- on your side as well
- so the first thing that we have to do is
- load in the data so that we can work
- with it
- so i'm going to use a fin statement here
- in order to
- load the data into the financial
- statement structure
- so it's taking a second to load
- everything in
- and then we should see the statements
- come up here when it's done
- now you'll see some other output coming
- out here um
- basically items which it wasn't able to
- load into statement items
- as long as you don't see anything
- important here then you should be fine
- and the other warning here is about
- both operating income and abbott were in
- the statement
- and so it used the value from ebit
- so then we see the statements here it's
- loaded them in
- properly and we see all the different
- line items on the statements
- and we're working with quarterly data
- here
- and we have um 39
- different periods here so this goes out
- quite far
- so plenty of data to work with the
- forecast
- so the first thing we can do is plot uh
- the item that we want to forecast so
- let's look at forecasting the revenue
- here
- and with in statement when you pull out
- um a line item from these statements
- um then it's going to be a pandas series
- and so that's why we can do.plot that
- line on that series in order to see it
- plotted over time
- so looking at the sales data here
- this is you know what we had seen in the
- prior
- video on the slides there is definitely
- a repeating pattern
- going on here in the data there seems to
- be a trend as well
- but we definitely need a more complex
- model in order to capture
- the seasonality shown here with the
- repeating patterns
- so let's look at using the
- quarterly seasonal trend model and in
- order to do that
- we're going to have to create these
- dummy variables as we
- explained in the prior video
- so again that's just uh data that we're
- working with
- a quick look at it and now we can make a
- data frame
- out of that because we're going to need
- to add additional columns here
- and so we can create a t variable
- and we looked in a
- prior video on simple time series
- forecasting with the
- linear trend model one way to do that
- with set index
- here's another way that we can create
- this t variable with with range
- so either way it works just fine
- um and here's just a look at the end of
- the data we can see it's going out to
- the 39th period
- with t equals 38 so it all looks correct
- so in order to get to four dummy
- variables which are
- one when this
- um certain period is within a certain
- quarter
- and zero otherwise first we need to get
- a variable which says
- what month is the observation in
- so thankfully um dates
- in heinous the pandas panda's timestamp
- datatype does have a dot month
- attribute so you can just directly pull
- out the month from the date
- so we can take the index basically
- which has all these states um
- and take take them out of there and take
- the month
- out of each one so that's what we're
- doing but we're just
- putting that all into a list
- comprehension to do them all
- at once and create a new variable the
- month variable
- column with those months
- so then we see here it's january and we
- get one of the month
- then april we get four july we get seven
- october we get 10 and then that pattern
- just keeps repeating even though we go
- into the next year we get those same
- values for the month variable
- so um you know certainly
- we could write some logic which creates
- these variables
- based off these values uh just i was
- saying
- is it equal to one well this one's gonna
- be one otherwise zero is equal to four
- well the second one is gonna be equal to
- one otherwise zero and so on
- but it's a little tedious and this is
- definitely a common thing that people
- have to do and so pandas has created
- this
- get dummies function
- which is going to be able to do this for
- us
- so you just pass it the data frame and
- then you pass it what columns you want
- to make the dummies on
- here it's just the month column and
- you can see in the created data frame
- that now it has dummies saying
- is this the first month is this the
- fourth month
- seventh and tenth month um so there we
- see
- now we have what we need for these
- quarterly dummies because
- every time it's first quarter we get a
- one here um
- and otherwise it's zero every time it's
- second quarter
- we get a one here otherwise it's zero
- and so on so this is what we need in
- order to pick up the seasonal effect
- so
- here is just putting those steps into
- a function so that it
- you can just immediately have the data
- frame that you need for forecasting
- from a series
- and so you can see here i'm passing it
- the cache
- and it's making a whole the full uh
- forecasting data frame
- for the cache as well so now um
- if you want to go forward with the
- quarterly seasonal trend model
- you can just use this function in your
- own model to prepare
- the data frame
- so now we're going to go and fit the
- model
- um and so we're going to go through the
- classic
- stats models ols approach
- um but we need to tell it that the t
- as well as each of these columns are
- going to be our variables
- and depending on your company
- it's gonna have the quarters uh in
- different months
- and so we certainly can explicitly say
- that the
- columns should be month one month four
- etc
- but then if you try to run a different
- company
- through the same code then it's not
- going to work necessarily because the
- quarters could be different
- so there is a general way we can do this
- that's going to work for
- any company is we can just
- grab the columns which have month and
- the name
- um so we can see
- um that if we just
- uh start from the columns
- which will tell us all the columns and
- we iterate through them with this list
- comprehension
- and we just say if the string month is
- in the column
- then we're going to include it in this
- list and so that's how we get
- uh these four different month columns
- automatically regardless of
- what the actual month was
- then all the x columns are going to be t
- plus those
- and so now we can go to rather
- regression
- so importing stats models as usual
- running the ols regression with the y
- is going to be uh whatever our
- variable of interest for forecasting um
- the revenue here
- and then the x we've already selected
- the x columns and we just want to add
- the constant to that
- and then we fit and see the summary
- and so here then we're able to see
- that we have the intercept and we have
- the t coefficient as well as the
- coefficient
- for each one of these monthly quarterly
- dummy variables
- so then we can go and predict the future
- also we can see that the r squared was
- quite high that's a good sign
- that we're going to have a decent
- forecast
- so we need to predict the next period
- in order to do that we need to know well
- what
- t uh is for the next period
- so we can just take the maximum of the t
- that's already there
- and then add one to it and that's going
- to get the next t
- and we want to do the same with the
- dummies we've got to be able to select
- the appropriate dummy
- and so we see that the last quarter was
- uh month seven and so the next one is
- going to be month 10.
- um so in order to get the forecast
- then we take the constant we add the t
- variable times uh this t that we
- calculated
- and then we add on the dummy
- corresponding to the month
- that this observation is in and we get
- our result there
- but it's a little hard to see you know
- especially with these seasonal patterns
- if just forecasting a single period is
- working correctly really you have to
- to bring it out further um and so let's
- look at how we can do that
- so we want to forecast for a number of
- dates in the future
- um and so
- we can use pandas date range
- function in order to create a range of
- dates that we want to forecast
- and so we're going to start from our
- last quarter
- we're going to have periods every three
- months
- and we're going to have 12 periods and
- that's what gets us
- to this here where
- the first period is
- the last one here and
- then we are going to every period in the
- future
- up to 12 periods
- so then we can create the forecast
- for all these different periods with a
- loop here so just going to loop through
- the forecast states and also
- use enumerate to get a counter variable
- so that we can
- adjust t appropriately
- and store all the forecasts in a
- dictionary um
- so we want the
- first uh forecasted period
- to be adding one
- on to the uh previous t and so
- we can call that the t offset and that's
- going to be the counter plus one so that
- it's one on the first loop
- and so then the t is going to be
- what the last t was plus this offset
- variable
- and then we can pull out the month of
- the
- specific date that we're working with
- and then assign into the forecast
- dictionary
- the result of our prediction same thing
- that we saw before
- constant plus the t times this t
- variable
- and now we're just pulling the
- appropriate month
- using string formatting so we do that
- and we see we get all these forecasts
- here
- so then we can create a series
- from the forecast because it's a
- dictionary
- we can just directly create a series
- from it
- and then we can plot that so
- uh this definitely looks like some of
- the patterns we have been seeing in the
- historical so that's already a good sign
- but it's really best to evaluate the
- forecast when also looking at it with
- the historical
- so we can use pandas's concatenate
- function concat um pass it a list of the
- two series
- the historical as well as the forecasted
- that creates the full uh series with all
- those values and then we can plot that
- and now we see the whole thing um
- so it looks like this turned out pretty
- well because our forecast here
- started around here and you can really
- barely tell
- where uh the historical stops and the
- forecast it starts
- it does get a little more regular here
- but basically it looks like we did a
- pretty good job
- of forecasting this time series even
- though
- it had this seasonality component
- so that is how we can
- deal with seasonality and time series
- forecasting
- and python with a more manual approach
- and then we'll come back in the
- following video
- to look at uh using fence statement
- to kind of do this automatically for us
- without
- so many steps so thanks for listening
- and see you next time
Complex Time-Series Forecasting in Python - finstmt Method¶
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¶
Transcript¶
- hey everyone
- this is nick durabartis teaching you
- financial modeling today we're going to
- be talking about
- forecasting complex time series in
- python
- using the fin statement package this is
- part of our lecture series
- on free cash flow estimation and
- forecasting
- so we already talked about a simple
- versus complex time
- series and uh some basic uh
- models that you would want to use for
- each and we also
- already looked at an example of how to
- fit the quarterly seasonal trend model
- for complex
- uh seasonal time series uh in python
- now this video is focused on
- uh using a more automated approach to
- dealing with complex time series
- which is going to be able to handle
- additional
- types of complexity beyond just the
- seasonality
- so let's go over to the jupiter notebook
- which is on the course site
- the quarterly financial statements
- notebook so i encourage you to follow
- along
- on your end as well
- so we're working here on the forecasting
- using the automated software approach
- section and before i discuss
- we have some installation things to talk
- about here i'm just going to go ahead
- and
- get this forecast running because it
- does take a little bit of time to do
- that
- so and then if you're just jumping into
- this you would want to first run this
- this first block
- in the notebook to get the statements to
- find an infant statement
- um but so in order to
- do the automated complex time series
- approach
- and fin statement it actually uses
- another package under the hood this uh
- profit package developed by
- facebook which they use for their own
- forecasting
- um and is flexible to a lot of
- um features of the data as well as bad
- data such as missing and
- outliers so it's a very nice
- system that they've developed for
- forecasting
- and it's just plugged into fin state
- mode
- now it doesn't automatically get
- installed when you install fin statement
- if you try to use the automated approach
- infant statement it will without
- installing
- fb profit it will say hey you need to
- install fb profit in order to
- use this approach you can use the other
- forecasting methods and invent statement
- trend compounded annual growth recent
- and average approaches you can use all
- those
- without any other dependencies but the
- auto approach
- does take this at the profit um
- and the installation for this is um
- a little different than what we looked
- at previously in the course
- uh because actually fb profit
- is not 100 python there's actually a c
- plus plus component in there and so
- you can't just install it regularly with
- the regular method for installing python
- packages
- um but i have encouraged everyone to use
- anaconda
- in this course because anaconda does
- make this process still pretty easy
- without anaconda
- it's fairly complicated to get all this
- set up
- but with conda it's still just one line
- that you're gonna run
- but where you run that does matter
- before we've done the installing
- within jupiter um but in this case
- you're gonna need to go um to the
- anaconda prompt
- um so if you're in windows you can just
- type anaconda
- into the search and you'll see this
- anaconda prompt come up you should be
- able to access it similarly on mac
- and if you can't find it you can also go
- to the anaconda navigator and you should
- see the command prompt
- in there so you can go that approach as
- well
- but you should see something like this
- when you open that up um
- and you'll want to make sure
- i don't think this is necessary on mac
- but at least on windows
- that you run as administrator
- so when you see this pop up then you
- right click and you do run it as
- administrator
- it does that little pop-up and then you
- can see it it says
- administrator up here in the top left
- um and so the command that you need to
- put in there
- conda that's anaconda's package manager
- comma install
- c condoforge fbprofit so put that
- exactly in there and hit enter and then
- you'll see
- this collecting package metadata solving
- environment stuff
- it takes a little bit of time because it
- actually does need to install a lot of
- things to make this work
- and then eventually you'll see something
- like this
- um where it says hey i'm going to
- install a bunch of things
- and you just need to put y and hit enter
- to proceed
- then it's going to you go ahead and
- take some time to install these packages
- eventually you'll see
- uh done and you'll get back to this
- prompt and that's how you know you're
- done
- at that point you can come back and
- restart your kernel
- um and then you'll be ready to go
- for the auto forecasting
- um so
- i'd already done those steps though and
- so i already had it all set up and ready
- to use you only need to do that once
- on a given system
- so then
- the way that we forecast
- using fb profit within fin statement
- is with the auto forecast method um
- so we have currently five different
- forecasting methods
- in event statement compounded annual
- growth rate trend
- mean recent these all
- uh work just with fin statement no
- external package
- then this auto which uses that be profit
- and so just to see how it works for
- everything uh we can
- use update all to set the forecast
- method to auto for every item
- and so we'll see now in the forecast
- assumptions
- that we have method auto
- for all these items
- and we ran the forecast which i did a
- moment earlier
- and so now we have the forecast
- statements
- um so you can see because we asked for
- 12 periods here
- that it went ahead and got us 12 periods
- out into the future
- um of the entire financial statements
- and it was also able to detect that the
- historical data was quarterly
- and so it also used a quarterly forecast
- going into the future
- um we'll also notice that um
- the balance sheet is um
- basically uh you know not 100 balance
- but
- it's close so 191 187
- 191 187 um
- and so after the forecast
- is made then statement goes in and
- balances the balance sheet for you
- um so then we have this plotting
- functionality infant statement
- so we can do plot on the forecasted
- statements
- and we can give it a subset to say which
- items we want to plot
- um or we can plot the entire results
- with just doing plot and no arguments
- and so um looking at
- some of these forecasts um
- you know we can look at the revenue here
- we can see the points here are the
- historical the black points
- um and then we don't have those once we
- get into the future
- and uh we start to see a confidence
- interval on the forecast in the future
- um and we can see that it seems to have
- picked up this pattern well
- even though we didn't um you know
- tell it anything about this is seasonal
- data or anything it just was able to
- pick up that pattern
- and when we look at all the plots we see
- those kinds of patterns
- going on in a bunch of these different
- line items and it was able to pick that
- up
- in most cases
- so yeah again looking at the forecast
- assumptions we can see the method was
- set to auto for all these items
- except for the uh items which are
- ultimately
- uh being used as a plug to balance the
- balance sheet so cash and long term debt
- will be adjusted
- uh because they're the plug items you
- can also change that in the config
- if desired
- um you can also pull out individual
- forecasts like that you get a forecast
- object and you can
- see what the model was
- and that can tell you some things about
- results etc so there's a lot more detail
- if you want to dig in there
- you can also access the plots that way
- get nice
- full-size plots
- in order to look at any individual item
- so we have this whole
- configuration system in fin statement
- where you can update how any item is
- forecasted
- so if you do tab complete on the
- statements object you'll see
- all the different line items so that
- would be
- like the the key for the
- item and you can update it by
- that key and config
- so we can do a config update on sgna
- and the forecast config to make it a
- percentage of
- revenue um if this is a company that
- uh their wages scale uh with their
- product very easily
- and uh we can update the interest
- expense
- to now be forecasted with the mean
- approach because when we look back
- at the and let me just get this running
- because it's going to take some time
- oh whoops i didn't actually run that
- update
- um so when we look back at the interest
- expense
- forecast um
- we can see that
- it is basically picking up some kind of
- seasonality component there but it
- doesn't seem to really
- exist um
- really it looks like an average is is
- probably a reasonable way to go with
- these data because they're kind of just
- going up and down around
- basically an average
- and so we can shift just specifically
- that item to be forecasted
- with the the average or mean approach
- instead of using this auto approach
- so this is you know a typical um
- procedure for this you kind of just
- you know pick a method and and go um or
- even just let it take the defaults for
- everything and go
- uh with the forecast and then you look
- over all the plots and
- you just look through and say does
- anything
- look like maybe the forecast is not
- fitting very well so interest expense
- kind of stands out
- don't worry about the cash and debt
- because those are the plugs so they're
- being adjusted just to make sure that
- uh the balance sheet can balance um but
- just looking through the others
- they seem to generally seem
- fine um
- goodwill's kind of difficult here
- because the last four periods have been
- high
- but that was much higher than the rest
- so it's not too clear maybe the recent
- approach should be better here to just
- keep it flat lined at that level
- and rest seemed to generally look like
- the forecast
- fit pretty well
- let's come down and see if it finished
- yep it finished with the
- adjusted forecast so we have new
- statements there and we can look at the
- plot of
- sg a and interest expense
- um and now we can see
- whereas before um
- and here let me just yeah we can see
- before that
- um the stna forecasts
- was just on its own not a percent of
- anything it's
- it's forecasting the level um but now we
- can see
- because we adjusted the config to make
- it a percentage of revenue
- and we adjusted interest expense to be
- the mean
- now we have sg a expense
- as a percentage of revenue is being
- forecasted
- and the um interest expense is now using
- an average method um
- and even though we switched sgna to be a
- percentage
- method we didn't have to do anything
- else
- fen statement is able to handle
- ultimately translating that back
- into the levels for the forecast
- um and so then it's it's also often the
- case
- that so you've gone through you've
- done the initial forecast you've
- reviewed all the plots you've adjusted
- the methods
- so that now all the plots look good
- but that's still kind of just the
- baseline forecast
- you may want to manually adjust the
- forecast
- based off of information that you have
- about the company which is not reflected
- in the historical financials
- so if you know that the uh maybe they
- just made an acquisition which is going
- to increase the size of the company or
- maybe they're starting a new project
- which is
- uh substantially more risky than their
- previous projects
- there are a lot of different
- pieces of information that could
- ultimately adjust your forecast in the
- future
- um and so in a statement you're able to
- take the existing forecast and make
- adjustments uh to it
- so there's two ways
- you can you do adjustments or
- replacements
- so adjustments going to take the
- forecast that was there and
- tweak it upwards or downwards and
- replacements are actually going to
- change what the forecast value is and
- just drop
- in whatever um you wanted to have
- instead
- so what we're going to do here
- is here first we're adjusting cash
- so uh the cash um
- we're gonna make a list of zeros so zero
- adjustment
- then just set the second period here to
- 40
- increase and so now that is going to
- make it so that
- our second forecasted period of cash is
- going to grow by 40
- and then go back to the regular uh
- forecasting and then
- with the second one uh we're doing that
- on revenue
- and um by default this is gonna adjust
- with growth rates so you can pass
- use levels equals true to for to
- adjust or replace on the level of the
- item
- um and here going to set the second
- period
- uh forecasted revenue to 80 billion
- uh directly just replacing what that
- value was
- so we can do that and then plot it to
- see the
- results of that
- so we can see in the revenue that the
- original forecast was much higher
- now we've replaced it by this 80 billion
- and so that one period just drops down
- before going back to normal
- um and then looking here over here on
- the cache
- we can see after this first period
- then it has this big jump where it's
- growing by
- 40 and then goes back to the
- original forecast
- so um that's a quick overview
- of um
- doing forecasts on complex time series
- with in statement so thanks for
- listening
- and see you next time
Complex Time-Series Forecasting Lab Overview¶
Notes¶
The exercise here is very similar to the examples we just worked through
Resources¶
Transcript¶
- hey everyone this is nick duraburtis
- teaching you financial modeling
- today we're just going to be quickly
- going over the
- lab exercise for the
- complex time series forecasting material
- this is part of our lecture segment on
- free cash flow estimation
- and forecasting so
- we finished covering all the complex
- forecasting material
- including the two videos which
- go over how to apply this in python
- using the manual approach with the
- quarterly seasonal trend model as well
- as the automated software approach
- within statement
- and profit and
- now in order to test knowledge of that
- material we have this
- complex forecasting lab so if you go to
- the course site you can download
- caterpillars balance sheet and income
- statement
- and you want to use those to
- forecast uh the next four periods
- which will be one year worth of data
- of the cash and you want to do that with
- both the quarterly seasonal
- trend model as well as the automated
- software approach and you want to plot
- both to see the results
- so really it's going to be very similar
- to
- what we've done in the
- two example videos here
- so you can just take that example
- notebook and i would
- you know recommend playing around with
- it trying to see how it works but then
- you can just kind of take the
- um functions and bring them into your
- own notebook
- um in order to do this with caterpillar
- instead of walmart
- so that's basically what's involved
- in this lab so thanks for listening and
- see you next time
Applying Forecasting to Free Cash Flows¶
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¶
- hey everyone
- this is nick dear burtis teaching you
- financial modeling today
- we're going to be talking about applying
- forecasting
- to free cash flows specifically
- this is part of our lecture series on
- free cash flow estimation
- and forecasting
- so we've had a number of videos going
- over
- general forecasting uh how to forecast
- basically any kind of time
- series whether that be statement line
- items
- free cash flows or whatever else
- but here we're trying to take it to
- specifically
- applying these general forecasting
- techniques
- in the context of future free cash flows
- so we have two general ways to go about
- forecasting these free cash flows one
- is to look at his the historical
- free cash flows and do the forecast on
- those
- and the other is to forecast
- the entire financial statements and then
- calculate
- the future free cash flows from those
- forecasted financial statements
- generally you're almost always going to
- want to
- forecast the financial statements
- with a big reason being that when you
- just forecast the free cash flows you
- don't really have any flexibility
- uh you know you can just adjust it up or
- down but it's not really clear
- um you know how the operations translate
- into the free cash flows directly
- um whereas it's easier to say
- um well the the company is opening a new
- branch
- uh they have 20 branches now they're
- gonna have 21
- so maybe we'll see like up to an
- additional five percent sales bump
- um so you can't easily just
- put that into a free cash flow number
- without doing this
- full forecast of the full financial
- statements
- and the other reason that generally
- forecasting the statements is preferred
- is because free cash flows
- can be very noisy and irregular if you
- look at the history
- it may be that in one year they bought a
- big building and so that year
- has way lower free cash flows than all
- the other years
- and because these large capital
- expenditures can
- really shift the free cash flows it's
- uh a difficult exercise to try and get a
- good look
- at future free cash flows only from
- the historical free cash flows now if
- it's
- been very stable in the past and the
- company doesn't have any
- big plan capital expenditures then it
- might be an okay approach
- um so under those conditions and
- especially
- when you just need to quickly get
- together a model and you don't have time
- to go through this full financial
- statement
- forecasting approach um that can be
- another reason to go with it
- but generally it's not preferred
- um where it is helpful to do that
- free cash flow direct forecast is if
- you've already done your financial
- statement forecast you've already
- calculated
- future free cash flows from that then
- you can just
- also do this as a check on the side well
- what if i just directly forecasted the
- historical free cash flows
- how similar does that look to my new
- calculated ones
- so uh the preferred approach
- as we said is to forecast the entire
- financial statements
- um what line items should you forecast
- out of that um
- so you want to
- focus on items which are not
- calculated um
- so like sales
- should definitely be forecasted cost of
- goods sold
- sg a but not operating profit because
- operating profit
- represents sales minus cogs minus sg
- a minus other operating expenses
- um and so it's completely calculated
- from other items
- and so you just fork forecast those
- component items
- and then you can calculate the operating
- profit
- and the forecast you don't have to
- forecast it directly
- so that means that in your future
- financial statements you're going to
- want to have these items
- calculated so
- you know if you're doing this in excel
- that's as simple as just calculating on
- the historical data having your
- forecasted right next to the
- historical and then just dragging that
- over
- um and then in python with the fin
- statement package
- this is going to be handled
- automatically for you
- and thinking about should i just
- forecast the levels
- or should i forecast a percentage
- um so
- when the two items logically scale
- together
- that's when you want to use a percentage
- so we've talked already about this
- example of
- cost of goods sold is typically a
- percentage of sales
- in the forecast because
- uh if the company sells more they need
- to buy
- more inventory in order to sell it and
- go through that whole um processing
- process of creating the inventory
- in order to sell it and that's the cost
- of goods sold
- so for each additional units sold they
- have to expend
- the cost of selling that and if they
- sell fewer units that's also going to be
- a lower associated cost and so
- those two logically scale directly
- together
- and so the cogs
- should be estimated as a percentage of
- the sales
- here interest expense is another good
- example
- the company pays interest as a
- percentage
- on an amount of debt and so it makes
- sense for the interest expense to be
- forecasted as a percentage
- of the total debt um a long-term debt
- could also be a reasonable
- option there for the percentage
- another thing that we have to think
- about which is really specific
- to financial statement forecasting
- is that we got to keep the balance sheet
- balanced
- we know from accounting that
- we always want to have assets equal to
- liabilities plus equity and we've got to
- make sure that relationship
- stays at least close to true and are
- forecast
- for it to be somewhat accurate
- um and so basically the way that you can
- do this is do your initial forecast
- and then adjust a couple of line items
- to make the balance sheet balance
- and so if your asset side
- ends up much greater than your
- liabilities equity side
- that is um indicative
- of by the way that you've forecasted it
- the company doesn't actually have enough
- funds to finance
- the the assets in the company
- so they need to raise those funds by
- some means and so usually
- increasing debt is going to be the
- solution there
- so you're saying in order to support the
- operations
- in the forecast that i've made the
- company needs to take on additional debt
- in order to finance those assets
- whereas if the reverse is true and
- assets are lower
- than liabilities and equity
- that means that the profit the retained
- earnings that have come through
- or additional debt which is forecasted
- to be taken on
- has not been reflected over on the
- assets side
- that hasn't been deployed into any
- assets
- which means that really cash should be
- increased to account for that
- um so really there if your assets are
- lower you're going to
- increase the cash
- so um to operationally
- make this happen um
- again first run your forecast then
- uh do this balancing adjustment
- and these values the cash and debt that
- we just mentioned
- we usually refer to as plugs uh because
- we
- are uh just plugging in the values that
- make the balance sheet balance so that
- the forecast is valid
- so to go about this in excel
- you have all your forecasted financial
- statements
- and then look at the
- total assets look at the total
- liabilities plus equity
- and take the absolute value of the
- difference and have that as a new
- like a difference row
- and then if you're only forecasting a
- single period in the future
- then you can use goal seek to
- set that difference to zero
- and if you've got a multi-year forecast
- as you probably do
- then uh you want to use solver in order
- to
- make that happen so there are plenty of
- resources out there on solver you can
- feel free to google about that
- but you you have to add that as an excel
- add-in which is built-in
- but then you can use it and you
- basically just say take this entire row
- of these difference values and i want to
- minimize
- that so then it's going to
- minimize the total of all of these uh
- values
- and that's going to and you're even
- going to say
- you know adjust cash adjust debt in
- order to make this happen
- so that it should be able to balance the
- balance sheet for you
- um and in python
- um so it's a similar process only you
- would use
- a scipy's minimize function instead of
- solver but if you're using them
- statement
- then this actually is handled
- automatically for you
- so whenever you write a forecast it
- first forecasts all the lambs and then
- it goes through this
- exact balance sheet balancing process
- and by default it's going to use cash
- and debt
- as the plugs but you actually can change
- what the plugs are
- as well if you just adjust the forecast
- configuration um you can change to have
- different
- line items be the plugs as well
- it also defaults to um
- making sure that the balance sheet is
- balanced within ten thousand
- um and you can pass this bs diff max
- um to the forecast method
- in order to change how accurate it
- should be so if
- uh you know your your statements are in
- billions
- um then um
- 10 000 isn't going to make sense there
- because that would be within 10 trillion
- that gives a ton of flexibility way too
- much flexibility
- in that case you may even want to set it
- to like point zero zero one or something
- um so it does matter what your units are
- um for setting that but if your units
- are in dollars
- um then the default of ten thousand is
- usually a pretty good choice
- um and the lower that you set this the
- more closely it's going to balance the
- balance sheet
- but the longer it's going to take to go
- through that process
- so that's another trade up there if you
- have plenty of time for your forecast
- maybe set it lower if you need to get
- this forecast done
- maybe especially as you're testing
- things out
- in development and you're running a
- bunch of different forecasts maybe set a
- little bit higher
- so the forecast can run quicker
- so that's a quick overview of
- taking this forecasting knowledge that
- we already know and
- applying it specifically to free cash
- flows
- so in the next video we'll come back to
- just uh wrap up the dcf model with the
- last portion about the
- terminal value so thanks for listening
- and see you next time
Calculating a Terminal Value¶
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¶
- hey everyone this is nick derbertus
- teaching you financial modeling
- today we're going to be talking about
- calculating a terminal
- value this is part of our lecture series
- on
- free cash flow estimation and
- forecasting
- which is part of a broader goal to build
- out the full discounted cash flow
- valuation of a stock
- so this is the last video in the lecture
- series where after this video we'll have
- everything that we need to be able to
- build the full dcf model
- so the last part that we haven't talked
- about is well now we've
- forecasted all these uh future free cash
- flows
- um and from the previous lecture series
- we've we've got the whack
- as well um and we also know how to
- convert between
- uh enterprise value and equity value and
- what those concepts mean
- so the last thing is just how do we take
- these
- free cash flows and the whack and get to
- an enterprise
- value so
- as we mentioned at the beginning of the
- whack lecture series we can
- value any financial asset any ash
- asset that pays cash flows by taking the
- present value of future cash flows
- so in the dcf model for the company
- we have these free cash flows as the
- cash flows
- and the discount rate is going to be the
- whack that we already calculated
- and we
- can forecast out of the future for some
- number of years
- but forecasts
- get less and less accurate the further
- that you go into the future
- and so you really should be uh
- suspicious of any forecast
- which is going out further than five
- years for financial data
- i mean even even five years um
- unless it's a very stable company it's
- it's fairly unlikely you'll be anywhere
- close to the actual numbers
- by five years
- so what about after five years uh if
- even five years is
- suspect what about 15 20 years in the
- future
- the company's still around they're still
- earning free cash flows
- that should be factored into the
- valuation so how do we account for that
- when we know that our forecasts are not
- going to have
- any reasonable degree of accuracy
- what we can do is calculate a terminal
- value
- for the company and that terminal value
- is saying
- at the end of our forecast period how
- much could we
- sell the company for um basically it's
- it's the enterprise value of the company
- at the end of the forecast forecast
- period
- and we can use that instead of
- having to predict cash flows
- all the way out into infinity
- um instead we can have a forecast period
- where we actually have the forecasted
- values and then at the end of that
- we have the terminal value in order to
- say
- um well if we just sold the company then
- how much would we get and if we sell the
- company
- then that's at that point reflective of
- the present value of all
- further future free cash flows so that
- the free cash flows
- starting from year six in this example
- and going out into infinity
- all that is brought into the terminal
- value as
- present value at year five
- and then we have those five years of
- free cash flows along with this terminal
- value which represents the entire future
- and that allows us to have the entire
- feature
- in our present value calculation
- so now how do we get that terminal value
- because
- you just heard me mention that okay this
- is an enterprise value
- at the end of the forecast period but
- we're getting this terminal value in
- order to try to calculate the current
- enterprise value so that in order to get
- the terminal
- value don't we then need an even later
- enterprise value
- like a a terminal value of that terminal
- values
- time period and then in order to get
- that one we're going to do the same
- thing
- and so if we try to go with the same
- approach we would end up with this
- kind of infinitely nested dcf model and
- you would never have a solution
- for your ultimate stock price
- um also we know that basically any
- forecasts
- which are going out beyond this five
- year period are just gonna be junk
- anyway
- so why bother even trying to go through
- that process even if it was solvable
- so we've got to have a different
- approach to get the terminal value
- and there are two approaches which are
- commonly used
- and those are classified into
- x and multiple approaches and the
- perpetuity growth
- method so we'll look at both methods
- and the difference is that the exit
- multiple approach
- the concept there is well let's take
- some
- valuation ratio uh
- from the current data um
- you know price times earnings or
- something like that
- and then take our future forecasted
- financials
- apply that same current valuation ratio
- to the future financials
- and that will get us our future
- valuation or the terminal value
- whereas the perpetuity growth method um
- says well let's take the last free cash
- flow and apply a growth rate on it
- and say they're just going to grow at a
- constant rate after the forecast period
- and then
- take the present value of that
- so looking more specifically at the exit
- multiple approach
- so there are some common valuation
- ratios that are used for this
- so you do have to have
- some kind of price
- component in it in order to
- use this approach that can be either
- directly the price
- or it can be the enterprise value um
- and so we have four common ones here
- based off the enterprise value
- enterprise value to abbott to ebitda
- to sales uh to free cash flows
- um and then we also have this
- uh one common one which is based off the
- price the price to earnings ratio
- so again basic idea
- take the current ratio forecast the
- financials
- apply the current ratio to the future
- financials to get the future
- valuation
- and you're going to get different
- results depending on which ratio
- that you use so it's very common
- for people to not just report one stock
- price from a gcf model
- but to give a stock price range based on
- the different approaches for the
- terminal value
- so people will usually look at multiple
- different ratios as well
- as the perp-duty growth method and
- establish a valuation range
- based upon that
- so for these enterprise value based
- ratios
- you're going to
- take the um
- ratio and you're going to take the
- future
- um forecasted
- um value whatever
- value is there in the ratio let's say
- we're working with uh
- ev to ebbit so you're going to take your
- ev the ebbit ratio which you have from
- the current data
- and you're going to multiply that by
- your
- last forecasted periods ebit and that
- will get you
- the enterprise value
- for the final period
- and then for the um
- price to earnings then you need to go
- through a similar approach
- take the current ratio multiply it by
- the final forecasted period net income
- and that will get you instead of an
- enterprise value it will get an equity
- value
- and so then you just need to add that
- final periods uh
- forecasted debt onto that and subtract
- the
- cash and that's going to get you that
- final period enterprise value
- which you can use as the terminal value
- and looking at the perpetuity growth
- method so this perpetuity growth method
- actually uses the same math as
- the dividend discount model because
- conceptually
- what we're doing in both models is the
- same
- in the dividend discount model we say
- that the dividends are going to grow at
- a constant rate
- same here we're going to say that the
- free cash flows are going to grow at a
- constant rate and in both cases we're
- trying to find the present value
- um so the structure of the formula
- looks just like the structure of the
- dividend discount formula
- where there we have the last period
- dividend times one plus the growth rate
- over the rate of return on the stock
- minus the growth rate
- here it's the same thing just instead of
- the dividend we have the free cash flow
- and instead of the
- rate of return on the stock we have the
- weighted average cost of capital
- so you do that you do that with your
- final
- final period free cash flow
- and that will get you the terminal value
- now you do have to pick a growth rate
- which is going to go into that
- and the valuation is
- extremely sensitive to the choice of
- that growth rate
- so whenever you use the perpetuity
- growth method
- it is uh even standard to do at least a
- sensitivity analysis
- on it because that assumption matters so
- much
- for the final stock price people want to
- see
- well what does the stock price look with
- a range of different
- terminal growth rates and
- um it would be a good idea to do a monte
- carlo simulation as well to understand
- uh in more detail how much uh
- the stock price is going to move around
- based upon
- your uh assessment of the standard
- deviation
- of the growth rate
- and if you want to know what to plug in
- um
- so two three percent are you know maybe
- up to four like kind of two to four
- percent is
- the typical range here um
- you shouldn't have it be really
- much more than gdp at all
- because this is supposed to be a growth
- rate which is going to last
- forever and so if a company
- grew faster than the overall economy
- forever
- it would at some point become actually
- much much much larger than everything
- else in the economy
- such that this company would become the
- entire economy
- so you can't just throw a 10 terminal
- growth rate in there
- that makes absolutely no sense that
- would say you know
- maybe in 20 30 40 years that this
- company
- would be the entire economy which is
- probably not
- um what you're trying to model so
- because this is uh such a long-term
- growth rate uh
- you need to be fairly conservative here
- even if the last few years of growth
- have been seven ten percent
- you need to pick a much lower rate
- because this is supposed to last
- indefinitely
- um so now
- we have the terminal value we have the
- free cash flows we have the whack
- so what do we do to wrap up the dcf
- model
- um so we have to
- use those free cash flows in conjunction
- with the terminal value
- in order to get the current
- enterprise value so
- the terminal value is going to happen
- during the final forecast period
- so if you're forecasting out of five
- years then your year five
- uh free the year five cash flow that
- you're going to use in the present value
- calculation
- is your original free cash flow for that
- year
- plus the terminal value so you'll have
- you know all the preceding years in the
- five year example you have years one two
- three four
- um with their estimated free cash flow
- value
- and then you'll have year five with that
- free cash flow value
- plus the terminal value and then you're
- going to take the net present value
- of all of that using the whack as the
- discount rate um so that will yield you
- the current
- enterprise value and then we talked
- at the beginning of the prior electric
- series on the whack about how we can
- take an enterprise value
- and convert it into an equity value and
- convert that into a stock price
- which is ultimately what we want to get
- out of the dcf
- model so with that then
- we have everything that we need in order
- to do the entire dcf
- model
- so then in order just to test
- understanding
- of this terminal value material we have
- a lab exercise
- on that as well
- so here you have
- four different ratios that you want to
- use with the
- exit multiple approach and then you also
- want to use the perpetuity growth method
- um and the numbers are basically given
- to you for all these things
- um so you have you know constant free
- cash flow for the next five years
- you have the values of all the ratios uh
- you have these
- these values which you need to use for
- the x and multiple approach for the
- final forecasted period
- you have the current
- financial statement items that you uh
- will use to
- get to a stock price
- um and
- shares outstanding also the stock price
- whack for taking the present value
- terminal growth rate for the perpetrated
- growth method
- um and ultimately what you want to get
- is stock prices
- so you should have five different stock
- prices coming out of this one
- corresponding to each ratio
- and one corresponding to the perpetuity
- growth method
- and all those answers are there provided
- for you to ensure that
- you can get through this all
- appropriately
- so that wraps up our dual lecture series
- related to the discounted cash flow
- valuation of the stock which is
- the capstone model here in the
- basic financial modeling class um
- so i hope everyone enjoyed uh learning
- about this
- and we'll come back in the next lecture
- series
- to kind of give a road map on
- what else we can learn in this financial
- modeling space so we'll start to look at
- more advanced topics
- and do just a very brief rush on each
- topic area to discuss
- what direction you can go in for future
- learning
- so thanks for listening and see you next
- time