Q&A¶
Answers to student questions about Financial Modeling with Python and Excel
Resources¶
finstmt Q&A 12/14/2020¶
Questions¶
Should we be worried about the data that did not get extracted in finstmt?
Could you show finstmt being used with xlwings?
Is the copy function only in finstmt?
Could you give an example of how to take specific values out of finstmt? E.g. the revenue for the 3rd forecasted period.
How can I see the effects of adjusting the financial statements manually?
Resources¶
Transcript¶
- hey everyone
- this is nick dear burtis teaching you
- financial modeling today i'm going to be
- answering some student questions
- regarding finn's statement
- so the first question that we have here
- is should we should we be worried about
- data
- that did not get extracted in statement
- so let's uh set up a simple example with
- fin statement so that we can take a look
- at that
- so first thing that we'll need to do is
- um let's load the
- statements with pandas so import pandas
- as
- pd and then from then statement
- i'm going to import the
- financial statements income statement
- and balance sheet
- statement
- a little bit of a lag there uh financial
- statements
- income statements and balance sheets
- and then we can load uh
- the income statement
- um walmart income statement i have in
- the same directory
- um and we'll need to set the first
- column as the index
- okay um and now same thing
- for the balance sheet we'll load that
- into a data frame
- um and now this is just balance sheet
- okay that looks good as well so now we
- can
- create the statement classes
- objects um so we'll have
- the income statement is equal to income
- statements
- from the data frame of the
- income data frame and
- the balance sheet um
- is balance sheet from data frame
- um the balance sheet data frame and then
- finally
- the statements are going to be the
- financial statements of the
- income statement and a balance sheet
- so this now that we have our setup here
- this first question again is asking
- about
- basically these messages that come up
- here
- about was not able to extract data from
- the following names
- and should we be worried about that
- so it is worth it to take a look
- over what those items are the first time
- that you load your statements in
- just to make sure that it's not missing
- anything important
- but hopefully most of the data items you
- would need to use are already covered
- in the package um so when we look at
- some of these items
- we're seeing a lot of basically
- calculated
- items which are not
- actually part of the financial statement
- so that's why they're not getting
- picked up lots of different margin
- calculations
- the number of shares outstanding
- so these are just various items which
- are not really part of the traditional
- financial statements and that's why
- they're being excluded
- and in some case items
- have other names which are getting used
- like you can see
- ebitda was not um loaded here but that's
- because
- operating income should already be there
- so you can also just look at statements
- and see what you have
- and
- we can see
- that we
- have the data that we need loaded in
- here so i think
- you know just just check over what names
- are not getting loaded and check
- over the data which is being loaded and
- make sure that there's not
- anything that's missing here that you do
- actually have in your
- financial statements
- so on the next question um could you
- show
- finn statement being used with excel
- wings
- yeah certainly so um that
- is very similar here i mean ultimately
- we're just passing data frames
- into uh fin statement so any way that we
- can make a data frame
- we can then upload that in the fin
- statement
- and so as long as we can
- [Music]
- get back to this same kind of structure
- where we have the statement items
- on the index and the dates as the column
- headers
- it doesn't matter how that was
- originally loaded in
- um so
- [Music]
- we can import excel wings
- um and then we can get a reference to
- the income statement book um so that's
- going to be xw dot book
- of the walmart income statement
- um and then uh
- you can just take a look at what's
- actually in there for
- um targeting the sheets
- so we can see uh so walmart
- uh she wmt is the sheet name
- so i'm going to
- um the income statement sheet
- is the income statement book
- sheets walmart
- um and then
- we want to basically get everything here
- um
- but the my initial instinct would be to
- start from here and expand
- but the top left cell is blank so i'm
- not
- certain whether that's going to work but
- we can give it a try
- um so range a1
- dot expand dot options
- pdi data frame
- this is going to be income df and we
- need to pick the value
- of that let's go what do we get there
- that did work okay that's great um
- yeah now basically this looks the same
- as the one we had loaded in before
- let's do the same thing with the balance
- sheet as well
- here for the balance sheet we're trying
- to match that
- we can do the same kind of thing on
- uh the balance sheet
- um and this would be balance sheets
- and this would be the balance sheet df
- give that a shot um
- and then look at that and looks like we
- got the same thing loaded in there
- so this would go here instead of
- where we have it being loaded in via
- pandas right now
- um
- so then we can run all that and it
- should
- it looks like it's loading everything
- into
- uh in statement properly
- so we'll just look at what the resulting
- statements are to confirm
- that worked correctly um
- but yeah basically any way that you can
- create that data frame
- with the index as the statement items
- and the columns as the dates
- it'll be able to be loaded into if in
- statements so even if you're just
- manually creating that data frame or
- it's
- coming from some other source that will
- work is fine
- that will work fine as well um
- it's as long as you can match that
- structure it'll work so
- yeah now we can see how the statements
- are loaded in we're seeing the same
- thing here
- as when we had used the pandas approach
- next is is the copy function only
- in fin statement
- so this is referring to that um
- in the statement we can do um
- statements.copy um
- and now we have a second statements
- which have the same thing
- um and
- i can go and i can update
- say you know for revenue
- i want to change the forecast method
- sorry forecast config i want to change
- the method
- to
- the average approach
- now when i look at the forecast
- assumptions
- uh we can see that revenue has gone to
- the mean here
- but if we look at the original
- statements
- that was unaffected by the changes that
- we made
- so this is a nice way to be able to do
- multiple different
- forecasts uh with the same financial
- statements
- object you just copy it and make
- whatever
- forecast adjustments run your forecast
- analyze the result as needed and then
- for the next
- configuration it just copies it again
- and makes whatever changes
- so the question is actually asking
- though is this
- just a feature of in statement or can we
- do that elsewhere
- so there are a number of different
- libraries as well as some built-in
- features of python which
- support the dot copy method
- so another good example is uh
- pandas supports it we have this income
- data frame
- we can do income data frame dot copy and
- we get a copy of it and any changes that
- we make
- this copy are not going to affect the
- original
- so same exact kind of thing an example
- with
- um just built in python you can actually
- do that with a list so my list
- is say one two three
- um second list is wireless dot copy
- then you can append to that
- and then we'll see that the original
- list was not affected
- but the second list is affected
- so that's there as well now
- say that you encounter some object in
- python
- that you want to copy and it does not
- have a dot copy
- method what can you do how can you copy
- it
- so there is actually a
- module in python built into every python
- installation that's made
- for copying objects so if you do from
- copy
- import deep copy
- um then this deep copy can be used
- in place of
- the copy method on an object so i'm
- gonna do deep copy
- of the list there instead of dot copy
- and we'll see that we get uh the exact
- same
- result whenever my system wants to
- resolve
- uh that will show up with one two three
- four
- um and you can do that with any object
- in python
- so actually that was what i recommended
- people do there we go
- with in statement before i added the dot
- copy method
- just do a deep copy and you can do that
- with
- anything in python so um
- copy is supported in a number of
- different places but if it's not
- then just use deep copy instead
- the next question is could you give an
- example of how to take specific values
- out of in statement for example
- the revenue for the third forecasted
- period
- so a nice thing about
- statement is well you do have these
- custom classes
- um for the for the financial statements
- the income statement and balance sheet
- it's made to be very compatible with
- other
- workflows in that when you access
- statement items
- you actually get a pandas series
- so when we do statement style revenue
- you can see we get that out of it all
- those different values
- if we check the type of this what we
- have
- is a pandas series and so anything that
- you can do with a series
- you can do with the statement item from
- pen statement um so you know we've
- looked previously in the course how we
- can
- uh index on series and data frames so
- here this would be getting the last
- value out
- just to be getting the first value out
- um
- and then if you have a certain date in
- mind
- you can uh grab that date and
- do dot look and that will allow you to
- pull out a certain date
- um so
- yeah for those methods you can pull
- whichever value you want
- another thing which may be useful for
- you
- is if you don't want to work with series
- all
- series can be converted into lists
- so um just do dot two lists on the end
- and then you have a list of
- values and a lot of people are more
- comfortable working with lists than they
- are with series
- so you have that um you know and then
- you can
- do all the same indexing stuff
- that you can normally do with lists so
- number of different ways to pull out
- individual items
- but the key factor there is that
- it's just a panda series so anything
- that works in panas
- is going to work here as well
- and the last question here is how can i
- see the effects
- of adjusting the financial statements
- manually
- so this is talking about in the
- forecasting approach um
- so after we do a forecast then we can
- adjust the results of that forecast
- so let me look at the forecast
- assumptions
- um so we can go ahead and run the
- forecast
- um statements.forecast
- uh let's just uh
- youtube periods here
- then we can look at the forecasted
- statements
- [Music]
- and we can look at the
- plot of those
- so we see all different forecasts there
- um and what this question is referring
- to is the fact that
- um we can
- adjust one of the existing um
- or any of the existing forecasts to add
- other adjustments
- um so forecast dot
- forecast and then we would grab
- whatever we want to adjust um
- let's say um
- we have what's the key to our retained
- earnings
- um so we can pull out the retained
- earnings forecast
- and do to manual um
- and then in the two manual method we can
- pass the adjustments or replacements
- so um i'm gonna here
- adjust and let's just say
- we want the um first period
- first forecasted period to grow by 40
- percent
- and that didn't work oh we do have to
- put
- um same length as there are a number of
- periods so
- not adjusting the second period
- [Music]
- um
- so now we can take a look and plot
- uh the retained earnings forecast and
- where as you can see before
- it was just kind of you know very slight
- curve a basically straight line
- um now when we look at it
- um we see that we have this big jump
- here and this is our 40
- adjustment that we made um so now that
- has jumped way up here
- and then it goes back to uh no
- adjustment
- so it's going to take the growth that it
- had forecasted before
- and now apply that for the final period
- so you can see it increases just
- ever so slightly here
- and you should be able to also
- see that in the statement
- view as well so now when we look at the
- retained earnings jumps to 78 million
- there
- whereas in the statements you can go
- back
- to statements the last retained earnings
- here
- um was
- okay so it's not showing there
- um reason
- that is insane um
- um
- i think it's because you need to
- actually run the forecast again
- to see it make its way into the
- statements
- yeah there we go now we had the big
- growth
- in the first period and then you know
- smaller more normal growth
- in the second period um
- and we should be able to see that in the
- plot as well
- after you do your adjustment um
- you will be able to see it in the plot
- but you gotta actually
- run a second forecast in order to have
- that
- come into uh the financial statements so
- now we can see that there in the plot
- and also
- sorry forecasts two
- and we can see that in the actual values
- there
- as well so
- that's all the questions that i had for
- today
- on finstateman thank you for the
- students
- who submitted those questions and
- i will see you next time
Project 4 Q&A 12/14/2020¶
Questions¶
What are the variables I should include in the Monte Carlo simulation?
Should I use annual or quarterly financial statements?
How should I approach the write-up around the forecasting assumptions?
Transcript¶
- hey everyone this is nick dear burtis
- teaching you financial modeling
- today i'm going to be answering some
- student questions
- on project four
- so the first question that we have is
- what variables should i include in the
- monte carlo
- simulation so
- [Music]
- you want to try to include the most
- impactful
- variables in your model now depending on
- how you set things up
- and depending on your company that's
- going to change which
- variables those are now i would say that
- generally
- using more variables is better if you
- could
- run the simulation on every single input
- in your model that would be
- ideal but that's also a substantial
- challenge to
- set up all that infrastructure and
- some of those inputs may not have much
- of it an impact
- and so it's not um very high value
- to target all of those um
- so you know if your company
- is mostly equity financed certainly you
- would want to make sure
- that um inputs like beta and the market
- return
- are in the simulation if they're heavily
- debt financed
- then your inputs into the cost of debt
- and market value of debt
- are going to be more important to be in
- there
- one that you should essentially always
- have
- is the terminal growth rate for the
- perpetuity growth
- method for the terminal value that
- assumption tends to change the resulting
- stock price
- stock price by quite a bit and so i
- would always recommend
- including that in simulations as well as
- the sensitivity analysis
- um and you want to try to
- include assumptions which are going into
- your forecasting
- as well maybe you have so
- assumed a certain growth rate or the
- revenue
- uh and a number of things are forecasted
- as a percentage of revenue
- so that is a very impactful
- assumption in your model
- then you would certainly want to include
- that revenue growth rate in the
- simulations
- as well so it's worth it to
- just go through your model and try
- changing around some inputs
- and see how much of an impact it has on
- the stock price
- if it's fairly large then that is
- something you should include in the
- simulation
- if it makes very little impact then
- there's not a lot of value
- to add it won't hurt it's always better
- to include more variables
- but you know considering everyone has
- limited time
- i would prioritize those which have the
- biggest
- impact on the stock price
- so the next question is should i use
- annual or
- quarterly financial statements
- so it certainly is a little bit more
- challenging to use
- quarterly financial statements but you
- can get a little bit more accurate
- result
- as well um so that's something that
- um as far as the grading is concerned
- using quarterly
- financials uh would earn you a bonus
- but it also can add a fair bit of
- complication to your project
- as well i would say that
- most
- [Music]
- ecf models i see are using annual
- that's kind of the standard but you can
- get a little bit more accurate by using
- quarterly
- i mean really getting a deeper
- understanding of the seasonality
- of the company's operations and and
- modeling that appropriately
- um it's more complicated to
- work with the quarterly financials
- mainly because of that seasonality
- effect
- some companies aren't going to have that
- but the majority do
- and so that moves you from a simple time
- series
- world into a complex time series world
- and so you have to use methods which are
- geared towards
- these more complex time series and can
- handle the the seasonality
- inherent in most quarterly financials
- so that's why i say you would earn a
- bonus for
- using the quarterly because you have to
- go to these complex
- uh time series methods
- and then one other thing you'll have to
- consider
- is um once you
- go to uh you'll have to think about
- everything
- in terms of quarters right four per year
- and so if you want a five year forecast
- you need to forecast 20 periods there
- and when you think about
- um if you
- want to get the net income for the year
- well you have to sum all the four
- quarters in the year
- so these things
- little things working with the quarterly
- financials
- are a little bit more complicated
- so if you want to just get the model
- done i would recommend
- just going with annual it's going to be
- substantially simpler
- but if you want to perform at the
- highest level and have a little bit more
- accurate model
- then it makes sense to go for the
- quarterly financials
- so the last question here is how should
- i approach the write-up
- around the forecasting assumptions
- um so
- there's really two different
- stages as you go through the forecasting
- the one first stage is that
- you just want to be able to predict
- the future in a way that makes sense
- based upon the historical values
- so does you know when you look at a plot
- of
- the forecasted versus the historical
- does it look
- like the historical naturally leads to
- the future forecasted is there a pattern
- there which is being continued
- um so that's kind of the first step in
- the forecasting process
- you want to select the methods for
- each statement item
- such that you see that these historical
- patterns are repeated in the future
- and you can consider that kind of the
- original baseline
- forecast so there will be a write up for
- that component
- in terms of um what
- why did you choose uh the forecasting
- methods
- for each of the line items and that's
- where you're talking about
- historical data and how you had to use
- different methods to fit
- the data um you know such as
- say it was you know very flat and then
- there's a jump and then it's flat again
- um
- it might make more sense to use the
- recent value approach to say well
- it's just going to stay at the new level
- um so you can kind of
- talk through that in the write-up
- well it was flat and then it jumped and
- then it was flat again and so i went
- with the recent value approach because
- it seems like
- that value is going to stay at that
- level in the future
- so that's the first stage of the
- forecasting and then the second stage is
- well i have kind of basically this naive
- forecast
- which is only thinking about the
- historical financials
- and not incorporating any kind of
- information that we have
- about the company's operations in the
- future or
- about economic conditions that are
- changing or other world events
- so the second stage is where you take
- all this qualitative
- information about the company that you
- uncover through your research
- and your your thinking and your analysis
- and you want to incorporate that into
- the forecast
- [Music]
- so you know maybe
- for example just say that the company
- you're analyzing
- is pfizer um
- and your historical data is basically
- pre covered 19 time frame
- um well we can't really just assume that
- the historical is going to predict the
- future now that we've had
- uh you know covid come to the situation
- and kind of
- switch things up a lot for the company
- um so you may want to go in
- and read about the the rollout of their
- vaccine
- um and kind of the distribution plans
- and
- how much revenue they they plan to bring
- in from all this distribution
- and work that into your forecast um
- so it would be good to go and um read
- different disclosures that the company
- has put out such as 10k
- 10q and any other
- disclosures that are relevant
- and see the company's plans see their
- estimates
- and you know critically evaluate their
- estimates
- um to see if you know you think it would
- be different
- and then take that knowledge that you've
- learned about the company
- and work that into your forecast well
- you know now they have all this
- additional
- demand with the vaccine so maybe you're
- going to boost
- the revenue growth for the next couple
- of years
- and that's not something you could ever
- get by just
- looking at the historical data and just
- you know using the time series to
- predict the future
- um so this would be
- more of the bulk of the write-up for
- most people
- is what qualitative information have you
- uncovered about your company
- uh which changes how we should think
- about what's going to happen in the
- future
- um there's not going to be any right or
- wrong answers
- on these things it is ultimately your
- opinion
- about what's going to happen in the
- future uh
- but it should be a logical analysis
- um the uh
- you should be kind of trying to break
- down the news
- or expectations for the future of the
- company
- and trying to analyze how that will
- impact the financial statements
- um and then justifying
- the adjustments that you've made to the
- financial statements
- by that way
- you'll have a little bit on well i chose
- the
- the recent value for this item i chose
- the trend for this item
- et cetera um you could say well you know
- i go with the default for cagger and
- then i've changed it
- for these certain items um and then
- you say but beyond the baseline forecast
- then
- uh i adjusted revenue up
- 10 for the next two years because
- of this vaccine roll out and um you know
- give a lot more detail
- there of why you think 10 is an
- appropriate number
- based on the disclosures um
- and make sure you're incorporating what
- other impacts that's going to have
- on the financial statements well now
- you know they need additional logistics
- to ship all these vaccines around the
- world quickly
- um so their uh you know operating costs
- for those logistics are are going to
- increase uh proportionately
- so you want to just think through
- all of the information that you've
- gathered about your company
- and how that can affect all the
- different statement line items
- and that will be the bulk of your
- write-up around the forecast
- assumptions so
- that's all the questions that i had for
- today
- on project four from the students
- and definitely feel free to um
- send me questions on any topics
- throughout the course
- either by email which you can find the
- email on the
- channel page or you can also feel free
- to comment
- on videos and i can reply there as well
- thanks for listening and see you next
- time