Projects¶
Overview of the course projects.
Resources¶
Project 0 - Project Grading Overview¶
Notes¶
The biggest part of the typical project grade is accuracy, but that only represents 60% of the total.
Answers with the baseline model inputs are often provided, so use these to ensure you have built the model correctly. But you cannot rely on these alone. Your model needs to be able to work with any reasonable value for any input and adjust appropriately. After you have matched the baseline result, then start changing around the inputs and ensure that your model responds in a way that is logical and consistent with the change in the input.
I have built out automated grading for the first three projects for the accuracy. It will tell me if everything in your model works properly. In order for this auto-grading to work properly, you need to conform to the expectations of the project, this is why I make following the template a separate grade.
If the auto-grader finds that your model does not work properly, then I go through it in detail to understand where the issues are occurring. I fix the issues one by one until your model works properly. Then I give comments on where exactly you went wrong and take off points appropriately.
Regardless of the auto-grading, I go through everyone’s model line by line to assess the readability and formatting of the model.
Resources¶
Transcript¶
- hey everyone
- nick dear burtis here teaching you
- financial modeling and today i'm going
- to talk about
- how the projects are graded in this
- class
- so uh you know the main grade in the
- course
- is these uh projects and that's going to
- be a lot of the value in the course is
- uh you know doing these in-depth
- projects that try to get
- fairly close to real world projects
- so as far as grading these um projects
- you know there can potentially be uh you
- know things which are specific
- to each project but in general the four
- categories which i'm looking at to grade
- these are
- the accuracy of the model the
- readability
- of the model the formatting of the model
- and how uh closely you have followed the
- template
- as is described in the project
- so before we dive into more specifically
- what each of these categories means
- let's talk about how the grading works
- in general
- so i use a combination of absolute and
- relative grading um so you know if you
- got everything perfectly right as far as
- accuracy in your model
- uh then you're going to receive 100 on
- the model accuracy so that's
- the absolute portion of the grading
- uh the relative portion is uh you know
- say there was some part of the project
- that nearly all of the class got
- incorrect
- well then i'm going to take off a very
- small amount of points
- for getting that part of the project
- incorrect so i actually look
- i you know i look at a bunch of
- different models a bunch of different
- students models
- before i assign any grades so i can
- understand where the common
- issues are and take off less for those
- issues
- and then each category
- is just going to have a percentage
- weight in the project and each project
- has a table telling you
- exactly what are the percentages for
- this project
- but generally model accuracy is going to
- be by far the highest at
- typically around 60 percent and then the
- other categories are
- are ten or twenty percent each
- um so then let's dig into the individual
- categories
- so for model accuracy you know that one
- is is mostly
- straightforward do you get the correct
- result from the model
- now an important thing to consider here
- in the accuracy
- is that we're building models here we're
- not just doing a single
- calculation and so you know i'm going to
- give you
- some inputs into the model and i'm going
- to give you some default values
- of those inputs and you know you get
- the solution from that that's not
- necessarily enough even if you have
- you know the result completely correct
- based on those
- particular inputs your model needs to be
- able to adjust appropriately
- when different inputs are passed as well
- it needs to be able to work
- for any reasonable value for any of the
- inputs and
- you know in order to check this i'm
- basically going in and trying a bunch of
- different
- inputs and making sure that your model
- matches up with what my model would give
- for those same inputs um
- so you know don't just think that you're
- done as soon as you've gotten the
- baseline
- uh answer is correct make sure that you
- can
- actually change around the inputs in the
- model and then your outputs are
- changing in the way that you would
- expect them to change
- from that input changing as well
- so that's an important skill to develop
- i intentionally you know don't give you
- a way
- to directly check that you it's kind of
- indirect and that you have to think
- about your model and how the how each
- input should affect the model
- and does your output actually uh change
- in that way because
- once you get actually on the job and
- you're building out your own models
- you're not going to have any answer
- sheet to check your model
- you just have to think critically about
- what your model should be giving you
- and ensure that it is conforming to that
- so i'm trying to get you closer to the
- real world there
- then thinking next about the model
- readability
- um so this is about how easy for is it
- for me to understand what you're trying
- to do in the model
- and how easily can i navigate through
- the model so thinking about excel models
- are you organizing the model um such
- that each kind of sub
- problem of the model has its own
- dedicated worksheet
- uh with its own inputs and outputs um
- have you separated the inputs and
- outputs appropriately
- [Music]
- do you have clear names for things is it
- clear what each input is and what each
- output is
- and you have you know table headers um
- kind of separating the different
- calculations inputs outputs and
- everything
- and uh you know try to split out your
- calculations
- into multiple calculations so that
- they're more readable
- but if you are going to have a complex
- calculation there better be
- comments explaining that calculation as
- well and then for python
- you know with jupiter we want to
- organize the model into sections the
- each of those sub parts of the model is
- going to be a section
- and you want to have each logical step
- of the model be a function
- and each sub model be a function and the
- entire model be a function
- and all those functions should have uh
- doc strings
- which explain what they're doing uh
- there should be comments or
- uh markdown the jupiter nicely formatted
- text to explain
- uh what's going on in your model the
- inputs should be all at the top
- the outputs main outputs should be at
- the bottom
- um and you shouldn't have super long
- lines going way out
- uh basically if i can't see it on my
- screen
- if it goes past my screen then that's a
- problem
- and you know there are lots of ways to
- break things onto
- multiple lines you can use parentheses
- or you can use a backslash
- definitely get in contact with me if you
- have that issue and you can't figure it
- out
- but google will be helpful for that as
- well um
- and then um are you
- showing intermediate results uh it's
- better if you're kind
- of showing the steps along the way what
- you get from each step
- uh rather than just getting all the way
- to the end you just wrote a bunch of
- code and then you get one answer at the
- end
- it's like very difficult to understand
- what happened in the middle uh if you
- show these intermediate
- results then it becomes a lot more clear
- and then your names of variables
- functions and classes
- do they follow the standard python
- conventions
- so you know all the code that i've shown
- does follow the conventions
- but make sure to take a look at this
- guide
- it will give you all the detail that you
- need to be able to name things
- appropriately
- and then moving on to model formatting
- so model formatting
- is you know about visually how does the
- model look
- um and so for excel you know really
- comes down to
- you know the number formatting the table
- formatting
- and you know having separate formatting
- of the inputs versus the outputs
- uh versus the calculations to make
- things very clear
- what uh belongs where uh you shouldn't
- have like
- you know cells which are too short such
- the text is getting cut off
- and you know things like that it should
- look nice and and be nicely
- laid out and everything
- and then for python it's all about the
- outputs of the model how are they
- formatted
- um so are you applying the appropriate
- number formatting
- to any numbers that you're showing as
- your results
- um are you including a nice sentence to
- explain
- what that result means um
- and then you know we haven't
- covered yet how to do tables and plots
- in python but we will get there
- so when we use those making sure that
- those
- are used when it makes sense to use them
- and that
- they're formatted nicely and that plots
- you know have labels for the axes and
- are
- a reasonable size to be including
- and then the last category here is
- following the template
- um and this is all about you know kind
- of exactly following the instructions of
- the project for matching a certain
- expected structure
- of the project and the reason uh
- i had to make this a grading category is
- because
- at least for the for uh you know a
- majority of the projects
- i have built out um auto grading
- for the project i've built code which
- checks the code
- but it needs to be in that certain
- structure for that code to actually
- work so if you're not following the
- template appropriately i'm going to run
- that
- on your code and it's just going to fail
- and then i'm going to have to go and
- figure out
- where you didn't follow the template
- properly and fix that
- before i can actually get the accuracy
- grade for your project
- so that auto grader is just used for
- accuracy
- and basically i just run it and then it
- tells me if you got everything correct
- or if you got anything incorrect uh then
- it will also let me know
- and if you do get something incorrect
- then i go into your model and i dig into
- it and i figure out exactly where it's
- going
- wrong and i actually fix all the
- mistakes one by one
- until your model is perfect on the
- accuracy and the auto grader can return
- it
- as being perfect accuracy so that way
- i can tell you in the comments exactly
- the points where you went wrong
- in the model as far as the accuracy
- um and give you in a grade uh
- commensurate with that
- um and then um regardless of the
- autograder i'm always going
- through everyone's projects uh line by
- line to check for the readability and
- the formatting purposes
- but anyway for uh following the template
- what is considered here in the grade
- for both excel and python there's often
- going to be a template
- you need to start from so make sure that
- you do indeed start
- from that template um and then thinking
- about excel specifically
- uh you know there's typically gonna be
- uh you know certain inputs
- and certain outputs in the template do
- not move
- any of those inputs or outputs the
- locations of the cells the cell
- references
- should stay the same uh for the inputs
- and outputs
- you can change the formatting if you
- want the visual stuff but
- the the location of the cell the cell
- reference should not change or it is
- going to break the auto
- grader um then
- in python there's a few things
- so we're going to have the in the
- template there's going to be this model
- inputs data class already defined for
- you
- and it's going to be creating an
- instance of that
- class as model underscore data
- so don't change either of these names
- keep it with model inputs keep it with
- modeled data
- um and then don't
- uh rename any of the variables which are
- defined as part of that data class
- if you want to add additional variables
- you can do that they can have any names
- that you want
- but for the ones that are there already
- don't change those names
- and then the inputs should be at the top
- the output should be at the bottom
- and then often the instructions in the
- project are going to say
- you know define this variable and it
- should have
- this inside the variable it should be
- this certain data type and structure
- so make sure that you conform to that
- exactly
- for example uh like the first project is
- going to ask you for
- cash flows uh as a list of numbers
- so make sure that it's actually numbers
- in there and you don't have like
- formatted strings with the dollar signs
- and everything
- in them when you show the outport output
- it's got to be formatted but as far as
- the variable
- that i'm asking you to store it in that
- should match exactly the data type
- of what i'm requesting in the project
- for the autograder to work
- appropriately so that
- wraps up the overview of the grading of
- the
- projects so in the remaining videos
- we're going to discuss
- what's involved in each project thanks
- for listening
- and see you next time
Project 1 - Excel and Python TVM¶
Notes¶
This project is really about cash flow modeling which is a huge part of financial modeling.
This is a classic capital budgeting problem. Buy production capacity, produce units for variable cost, maximize NPV based on selecting the investment in production capacity and unit volume
Leave the bonus for last. It is fairly separate from the rest of the problem. The bonus is really intended for the more advanced students coming in with Python or programming experience who did not have a hard time with the main project to provide some additional learning. Usually very few students complete the bonuses
You are completing the entire project in both Excel and Python separately so we can understand the differences
Please start as early as you can. This project has the highest learning curve and students who have waited to the last minute have ended up struggling in the entire course
Transcript¶
- hey everyone
- nick dear burtis here teaching you
- financial modeling and today
- i'm going to talk about the first
- project in the class
- giving a quick overview on some
- additional material we need to complete
- the project
- as well as introducing the project
- itself
- so this project
- is trying to work in mainly cash flow
- modeling
- is the main work involved in the project
- and cash flow modeling is something you
- need to do in the majority
- of financial models so it's a good area
- to focus on
- first and
- so you know this uses pretty much the
- same concepts
- as the retirement model uh you know it's
- still ultimately a time value of money
- problem but we're going to be
- approaching a capital budgeting setting
- instead of a you know personal finance
- retirement setting
- um so we just need to quickly look at
- some stuff which
- should definitely be review for you uh
- you know it's just some basic
- micro economics which is going to be
- involved
- in this capital budgeting problem
- um so you know we're really just you
- know talking about supply and demand
- here
- which um you know everyone should have
- covered this in their econ
- courses um that
- basically um you know as
- uh you know there's an intersection
- between supply and demand
- um and that's going to be the
- equilibrium pricing quantity
- that's going to be sold for a given
- product
- and then if you have a decrease
- in supply that's going to be this entire
- curve
- shifting to the left then that's going
- to then shift up the price
- and shift down the quantity whereas if
- you get more supply
- then it's going to shift down the price
- and up the quantity
- and the reverse is true for demand
- as demand uh increases pushing the line
- out to the right
- then the price is going to go up as well
- as the quantity
- and then if you push the uh demand down
- then both
- price and quantity are going down
- um so uh when we think about
- the revenue that we're going to get um
- as a producer um it's going to be
- whatever the price was
- times the quantity um
- but it's important to consider that
- you know whatever you produce is not
- necessarily all going to be sold
- it has to match up with whatever the
- demand is
- so the quantity that's actually going to
- get purchased
- is the minimum of the quantity demanded
- and the quantity supplied because
- if there's a ton of production capacity
- but people just don't want the product
- well that low demand for the product
- is going to drive the quantity whereas
- it could be the opposite there's a ton
- of demand demand is really high
- but suppliers just aren't able to
- produce very much
- and so then the supply is going to be
- the constraint here
- and so it's just the minimum of the two
- is ultimately going to be the quantity
- transacted
- and then there's one
- gotcha we got we have to think about
- as we go to complete this project
- ultimately we're going to need to be
- calculating mpv
- in this project and
- npv does work slightly differently
- numpy's mpv works slightly differently
- than the mpv function in excel
- and that difference is just about when
- it considers as the first cash flow that
- you're passing it
- so in excel whatever cash flow
- you pass to it first for the mpv
- function is considered period one
- so if you had um you know whatever you
- have there is going to get ultimately
- discounted
- and determining the present value and if
- you want to have a period zero
- you have to just add that separately
- outside the mpv
- uh conversely uh numpy's mpp function
- treats the first cash flow as period
- zero
- so if you want to have a period zero
- cash flow it would be first
- and then your period one cash flow to be
- second and you just pass them all
- in the list to the mpv function
- um so then if you don't have a period
- zero cash flow your first cash flow is
- coming in period one
- well then you would just want to add a
- zero as the first element in the list
- so that it's considering that second
- item in the list as period one
- so that's the main thing to be careful
- about um otherwise
- uh you know uh it works essentially the
- same
- between the two so then jumping over
- to the description of the project um so
- again this is kind of a classic
- capital budgeting problem where we have
- um you know these uh fixed assets that
- we can buy
- here it's machines and those fixed
- assets uh
- buy us production capacity such that we
- can produce a certain number of units
- um and then producing those units has a
- variable cost
- associated with it um and you can sell
- them to earn a revenue
- on those um and then uh
- also coming into this problem is uh
- thinking about the demand
- as well uh there is going to be
- demand in here and you can never sell uh
- more
- than the demand right the quantity
- purchased is going to be the minimum of
- supply and demand
- and basically you have a choice in each
- year whether you're going to
- buy a machine spend your budget on
- buying a new machine which can up your
- production capacity
- or instead you can spend that money on
- advertising and then advertising is
- going to increase the demand
- for your product um so you know here you
- can control whether you're
- upping the demand or upping the supply
- and you know
- the the point of the model is um you
- know it could be used to find that
- balance of demand and supply
- that's going to maximize your mpv
- um and there's a few uh assumptions that
- we can work in here
- to simplify our model um so one is i'm
- allowing you to assume that
- you know the model just goes to 20 years
- nothing is going to happen
- after 20 years
- and then we can also assume
- that um you know we're paying
- for either this machine or this
- advertising and every single year
- um even after all of the you know the
- project is over
- the machines have shut down zero cash is
- coming in
- just assume that you're still you know
- paying that cost of advertising
- um that's just you know to make it a
- little bit simpler
- on you and then
- you know definitely make sure that
- changing around the inputs the outputs
- are going to change
- like you would think that change in the
- input would cause
- um and another simplifying assumption is
- we can assume that
- um fractional phone sales are fine
- you don't need to do rounding on any
- quantities
- just leave it as the fractional amount
- so then we have these inputs to the
- model here
- you know most of the things we just
- talked about
- the number of phones the machines can
- produce the number of machines that
- you're going to purchase
- the life of the machine the price of
- each phone the scrap value of the
- machine
- uh you know when you sell it or when
- once the machine is out of life you can
- sell it for
- scrap value so that's what this is um
- the price of the machine or you know
- we're making it simple by just making
- the advertising costs the same
- it's just either you spend your budget
- on the machine or you spend it on
- advertising
- in that given year the
- cost of producing a single phone
- variable cost the initial demand
- the demand growth for each advertisement
- that you do
- and the interest rate
- um and then the main outputs we're
- looking for from this model are the cash
- flows year over year
- as well as the mpv of those cash flows
- um and then i have also included a bonus
- problem here
- so you know for the typical student i
- would not
- uh recommend going after the bonus
- problem
- really only if you've already finished
- up your project and you have extra time
- and you want to add additional learning
- that's when you go for the bonus project
- or problem uh because it is
- going to require basically going out on
- your own and learning some stuff which
- we don't have time to cover
- in the course and working an additional
- structure into your existing model
- so this uh part is all about doing the
- optimization
- it's about you know okay now we have
- this model which has all the structure
- now how do we actually find you know
- what should be
- the um number of machines that we
- purchase
- um you know based on um
- the demand and the supply trying to
- maximize our
- mpv um
- and a key thing about this project is
- you are going to submit
- basically two entirely separate
- submissions for this
- one is an excel model and one is a
- python model so you're doing the entire
- model
- end-to-end and both excel and python
- so that's definitely a fair amount of
- work definitely start early
- on this um this project uh
- in the past has been the one where
- people have struggled the most
- just because of the learning curve
- involved in
- um and mainly the python side of just
- getting started over there
- um so for the excel exercise make sure
- you start from the template we'll look
- at that in a moment
- ensure that everything is referenced all
- the way through everything
- should flow from inputs to calculations
- to outputs
- um and be sure not to change the
- location
- of any of the inputs or outputs and
- you'll submit the excel workbook
- for that for python um
- start from the jupiter notebook template
- um
- and i should be able to run the model
- uh from the top to the bottom just
- running all the cells in order
- and get the expected output definitely
- don't
- change the model inputs
- and you need to have
- certain variables defined
- for the auto grader to pick up your
- answers so have the cash flows
- variable defined as you know the years 1
- through 20 cash flows
- which should be numbers it should not be
- formatted strings
- and have the mpv variable defined as the
- number again it should be a number
- of the npv not a formatted string but
- when you show your results you should be
- showing a formatted result so you've got
- to
- be able to handle both to keep the
- numbers in the original variable
- show the formatted
- and then this project has kind of the
- standard grading structure
- uh 60 accuracy 20 readability
- 10 formatting 10 following the template
- and then you can earn up to 5 on the
- bonus
- now what i do for a number of the
- projects including this one is to give
- you some answers so you're not just
- completely in the dark
- on this so if you have these values
- for the inputs
- then these should be the answers that
- you get
- as a result now it's important that
- you know once you've built out your
- model and you're able to match this
- don't just stop there you've got to go
- and start changing around your inputs
- and make sure that things change in the
- way you expect
- if you go and change an input and just
- nothing at all changes
- uh then you know there's probably an
- issue where you've actually hard-coded
- the value somewhere in the calculation
- and it's not actually using the input so
- that would definitely
- cause you to lose points on accuracy
- make sure to check for those
- kinds of mistakes
- so that's the overview of the project
- and then we can look at the templates
- so here is the excel template and so
- you'll see the
- inputs here on the top left on the
- inputs and outputs tab
- so don't change the name of this tab
- don't change
- the location of any of these cells
- but you can mess around with the
- formatting if you don't like the way
- that i have
- formatted it and as you do all your
- calculations make sure you reference
- these cells uh because i will be
- changing these cells around the grading
- and expecting that
- your outputs here change appropriately
- um so then the outputs section is below
- that and you see the yellow highlighted
- cells are where you need to put your
- answers
- um and again those should be referenced
- back from your calculations
- so that everything flows through from
- the inputs to the calculations
- which could be in other tabs back over
- to the outputs
- on this tab um and here we put the mpv
- and then each cash flow one by one
- um again do not move the locations
- of any of these cells
- and then coming over to the
- [Music]
- the python template jupiter notebook
- template
- so you can see here it's got this top
- section
- where you should add your description of
- the model and here you can kind of
- follow this structure
- to also add a table of contents on your
- model that allows you to
- navigate through the different portions
- so make sure to fill that out with your
- sections
- um and then you know set up
- you don't want to remove these but you
- can add additional imports and things if
- you want
- inputs here you don't want to change the
- name of
- this class and you don't want to change
- the name of this
- variable in your final submission
- and so um
- and you also don't want to change the
- names of
- any of these variables either
- [Music]
- so or these either anything that was
- there already you should not change the
- name
- you know you you can add your own
- variables if you want and they can have
- whatever names
- but just don't change any of the names
- of the variables that are there already
- and then here you know i'm you know
- saying what you need to set these
- variables to
- so just make sure that you do actually
- indeed set these variables
- um and that you you know delete this out
- and just put
- uh you know what this variable actually
- is
- in this final cell and it should be all
- linked through such that
- if you change the inputs in your model
- it's going to change
- the values we're seeing for these
- variables as well
- so that's an overview of the first
- project
- so definitely get started early on this
- and please do feel free to ask me
- questions about it
- so thanks for listening and see you next
- time
Project 2 - Probabilistic Loan Pricing¶
Notes¶
The focus of this project is to reinforce probabilistic modeling and to solve a common problem faced by lenders: deciding the loan terms to offer
This project also tests your ability to explore the parameter space and visualize the results, extending the base model
This model typically incorporates internal randomness. This means you will get a different result each time you run your model. You can increase the number of iterations to get a more consistent result, but it will also take longer for the model to run. When checking your answers against the provided solutions, they will not match exactly, but as you run the model multiple times each number should be similar to the reported answers. When you are ready to submit the model, run it with 1000 iterations and be sure to allow time for this
This project, and the remaining projects going forward in the course, may be submitted as an Excel model, a Python model, or a combination model that uses both Python and Excel
It is up to you how you want to structure the model and which tools to use, but this problem is difficult to solve using only Excel. If you want to work in Excel as much as possible, I would recommend building the base deterministic model in Excel then handle the randomness and exploring the parameter space using Python
As always, save the bonus for last, though students have generally found this bonus exercise easier than that of the first project
Resources¶
Transcript¶
- hey everyone
- nick diabetis here teaching you
- financial modeling today
- we're going to be introducing the second
- project
- in the course on probabilistic loan
- pricing
- so this project has a couple of main
- goals for your learning objectives one
- is to have a model where probability
- is really a first-class citizen
- something that we're thinking about
- very deeply within the base model itself
- probability factors into so many
- financial models
- that it's good to hit that early in the
- course
- and then the other main
- area we're testing here in this project
- is
- then to be able to um
- extend the base model in different
- ways so here we're going to build out
- an initial model and we'll have to run
- that model a bunch of times
- to understand all the different ways
- that things could occur
- so let's talk about what this project
- actually is
- so the idea here is
- that you're an analyst within a bank
- and the bank is considering doing a loan
- to a small business so
- this also gets in the whole debt area
- and decisions
- of of lenders
- so the business needs to
- buy a machine they want to borrow money
- to buy the machine
- and then after some amount of time
- they're going to repay that loan
- in full the interest is paid annually
- at a certain rate based off of the
- amount that they borrowed
- and where the probability factors into
- this
- is that the business is not always going
- to be able to pay back the loan there is
- a
- chance that they're going to default on
- the loan
- and not be able to make their payments
- um so the probability of default here
- it starts at an initial probability of
- default and then it decreases over time
- because the business becomes
- more mature year over year and so it's
- less likely that they're going to
- default
- as each year goes by but
- in that final year when the loan is
- actually due the business
- doesn't just have to pay the interest
- payment they have to pay
- the full amount of the loan the
- principal in addition
- to the interest payment so that's a much
- bigger
- obligation for that final year and so
- the final year
- has a separate default probability which
- is going to be higher than their typical
- default probability as overtime would
- save for the interest payments
- and we need to model in
- what happens whether the borrower
- defaults or doesn't default
- and the borrower can default in any
- given year
- that they have to make a payment
- so whenever the borrower defaults then
- they're going to go into bankruptcy and
- we're going to say that the bankruptcy
- process always takes two years
- and then uh after bankruptcy the
- lender is going to collect a certain
- percentage of the original loan amount
- the recovery percentage um
- and so what that means as far as forming
- the cash flows
- uh in the year that the borrower
- defaults then there will be zero
- cash flow in that year it'll be zero
- cash flow
- um the next year and then two years
- after the default we're going to get the
- recovery payment
- as the lender recovery times the
- price of the machine which is equal to
- the loan amount
- um and so that does mean that the amount
- of cash flows
- could be greater than the amount of the
- loan if they defaulted in the last year
- then
- it's going to be two years more than the
- life alone that the final cash flow
- comes
- so this is a fairly realistic kind of
- model to determine basically what
- what terms should the bank offer on the
- loan
- um and so the main question here
- is uh you know you're you're the loan
- analyst
- trying to come up with what are the
- appropriate terms
- for this loan to this bank based on what
- we know about the bank
- um and so
- you're trying to give the loan officer
- all the information to make these
- decisions
- so you want to be able to say with your
- model for any given inputs
- what's the expected irr of the loan
- and what does that irr look like for
- some different interest rates um so
- here we're going to be looking at 30 35
- and 40 interest rates for the loan
- um and then
- it goes even further to say that
- um you know there's always a lot of
- uncertainty in estimating the
- probability of default
- you can never estimate that accurately
- so
- uh the lending officer wants to see what
- everything looks like
- with some different assumptions for
- the initial probability of default so
- uh base case is 20 per year
- but the lending officer also wants you
- to evaluate
- 10 percent and 30 per year
- and then the other factor here
- is how long should the loan be and so
- the lending officer also wants to see
- all the results with 5
- 10 and 20 years
- [Music]
- and important thing about how the
- results should be structured
- is that your model should show all of
- these
- results it shouldn't require going in
- and manually changing around numbers to
- produce these results
- just when i open up the model i should
- be able to see the answers for
- all of these different interest rates
- probabilities defaults and loan lifes
- and then it's also
- part of the main problem to then
- visualize
- your result there should be graphs and
- conditional formatting here
- um so a few notes and assumptions
- we can add into this to make things
- easier
- so first is that you can assume there's
- only going to be 20 years
- of loan life that's the maximum loan
- life
- um if your model can handle more that's
- great
- but um i'm not going to test anything
- longer than 20 years in your model
- but keep in mind that that could be up
- to 22 years of cash flows if the
- borrower defaults in that final year
- um and then as far as the general
- approach to build out the model
- i think it's easiest to go at this with
- an internal
- randomness approach um
- so with the basic structure being that
- you build out
- the base model which um
- just gets you the irr for one set of
- inputs
- and then you're going to um
- run that base model a bunch of different
- times with different
- uh randomly choosing the default cases
- um it is possible to do everything in
- terms of expected value and not actually
- have a random component
- in your model but that does take a lot
- of fairly complicated math
- to make that happen i've only ever seen
- a couple students do that
- and i think it's generally a much more
- difficult way to go about building
- this model and it also doesn't really
- hit
- on a lot of the things that i'm trying
- to test with this model going with the
- internal randomness approach you're
- going to get much more comfortable
- working with a random model and being
- able to extend
- an existing model so that's the approach
- i would recommend in general
- um so when you're doing the internal
- randomness approach
- now you're going to be running the model
- a bunch of times to
- discover what the true expected irr is
- for each set of inputs
- each time it's picking a different
- default case each time you're on the
- model
- so now that it's not a deterministic
- model
- you put the same inputs in and you're
- going to get different outputs each time
- so you've got to keep running it and
- then you've got to take the expected
- value
- across the iterations to get
- an actual estimate of the result
- and the greater number of iterations you
- use the more accurate
- that answer is going to be but the
- longer it's going to take to run your
- model
- so there is a trade-off there and what i
- would recommend
- is as you're building everything out
- keep the number of iterations low
- uh just so you can run everything
- quickly and then
- once you think you've got it right then
- increase the number of iterations
- so you can run it a couple of times and
- check against
- the provided answers
- um so
- and then when you actually submit the
- final you want to run it with a thousand
- iterations before you submit so you can
- get your final answers in there
- and if you've got an excel model or a
- combination model we'll talk more on
- that in a moment
- then it can take a substantial amount of
- time to run your model with all these
- iterations
- um so make sure that
- you're planning for that time and not
- just 11 55 pm you're done
- now let me try and run this thing with a
- thousand iterations oh wait it's taking
- way too long
- um so yeah the next point
- this is a big contrast from the last
- project
- now you have flexibility over the tools
- that you want to use to solve the
- problem
- i'm trying to really emphasize and
- you know build help you build a
- competency
- in uh coming to any arbitrary problem
- and using the tools which are best
- suited for the job
- um and so in some cases that might be
- excel in some cases it might be python
- in some cases it might be a combination
- of the two um so
- same thing for this project and the
- remaining projects
- in the course you're free to implement
- it in the way
- that you like you can submit a pure
- python model
- you can submit a peer excel model or you
- can submit a model which uses both tools
- together
- i am putting one restriction on it here
- that
- if you are submitting a combination
- model then your final answers
- should be in the python side
- which i think in general makes a lot
- more sense anyway
- because the like the most
- straightforward way to combine them is
- to build out
- the base model in excel and then use
- python to run all these different
- iterations on the model
- um so then you already have your results
- in python
- and then i can grade it from your python
- model
- um and
- you know some people really like to uh
- use excel because they're more familiar
- with it
- and python is it's definitely hard to
- learn in the beginning for sure like
- you get through that initial learning
- curve it gets easier
- but people in general are just more
- comfortable with excel at this point
- still i think
- um so you might have the thought cool i
- can use
- either one i'm going to just use excel
- for sure like i'm more comfortable there
- um but you're gonna run into
- issues this this project is
- substantially more difficult
- doing it with purely excel
- um mainly because you're gonna need to
- run your model
- 27 000 times uh and that's changing
- three different inputs together
- because it's three inputs that kind of
- throws out the data table approach
- uh unless you're really hacking it
- together um
- and so this just becomes very difficult
- to do with
- only excel um and so
- if you really are struggling with python
- and
- you just want to do as much as possible
- with excel
- that's fine i would recommend building
- out the base model that gets you an irr
- from the given inputs and then
- using python to run the iterations side
- of this
- because it's really probably not going
- to be more than like 10 lines of code in
- python to do that part of it
- [Music]
- so then you can still work mostly in
- excel and
- use python for something that
- it really excels and um
- though i have seen many students just go
- with pure python
- on this project and i think that's a
- good choice as well
- another thing to note about the fact
- that this is a random model
- or at least if you're going with the
- internal randomness approach
- which the vast majority of students will
- do and should do
- uh is that i'll be providing solutions
- here we'll have them down below
- in this document but when you run your
- model even if
- everything is perfectly correct you're
- not going to match up exactly
- with the solutions because it's random
- the numbers are going to be different
- every time you run it so my
- tip there is to run the model with a
- thousand iterations check it
- against the solutions and basically
- every number should be pretty close
- nothing way off and save your results
- from that
- and then run the whole thing again
- thousand iterations again
- and now check your answers that time
- against the solutions again and
- everything should be close
- again and kind of all three numbers
- you're looking at should not be far from
- each other
- um and that will let you know that
- everything is
- working appropriately at least with the
- baseline
- inputs of course my
- general recommendations still apply that
- you only have the baseline
- solutions given to you and so you need
- to be messing around with different
- inputs in your model and making sure
- that your model can adjust appropriately
- and as with all the projects that have
- templates
- you don't want to move any of the input
- or
- output cells
- um and in python just like with first
- projects you're going to have certain
- variables that need to be defined
- to give your answers
- and then the bonus for this is in two
- parts first part
- is visualization is built into the basic
- problem you need to have some
- visualization to get the full credit
- uh on the accuracy portion readability
- portion
- um but if you do an especially good job
- on visualization
- then you're going to earn a bonus so
- half of that bonus
- comes from doing a really good job on
- visualization
- and the other half is an extension to
- the problem
- where instead of looking at
- 10 20 30 as the
- probabilities of defaults uh
- we want to evaluate
- the probability of default being drawn
- from a normal distribution
- so what do the results look like
- across all the different um
- interest rate and lone life cases but
- with this
- randomly drawn default probability from
- normal distribution with a mean of 20
- and a standard deviation of 5
- and then going further than that you
- want to take a single
- input case so single lone life
- uh single interest rate um
- single default probability and
- look at what the result looks like with
- different number of iterations
- try it with ten hundred thousand
- and you wanna produce the visualizations
- of each summary statistics of each
- um and the idea here
- is you wanna be able to say how precise
- are these estimates
- when i run the model a thousand times
- how far off can i expect to be with my
- result
- when i run it with 10 how far off can i
- expect to be with my result
- um so you want to do that by
- for each number of iterations you're
- going to run that in another
- like meta number of iterations to see
- across these different cases
- with that number of iterations what's
- the distribution
- of the result um
- so the realization part is up to two
- points
- bonus and then uh these things together
- are up to
- three points bonus
- and then we have template files
- for this as well um
- and you have an excel template a python
- template jupyter notebook template
- um again it's up to you whether you want
- to do pure python pure excel or a
- combination
- just whichever tools you choose to use
- use the template
- for that tool so if you have a
- combination model you should be
- submitting
- both parts which are both based off the
- two templates uh
- pure python based off the python
- template pair excel based off the excel
- template
- and just want to emphasize again if you
- do the combo model
- your final answer should be in the
- jupyter notebook on the python side
- and then here you can see the selected
- solutions so it shows for each of the
- different
- interest rate loan life default
- probability what is the expected irr
- across a bunch of iterations looking at
- the different default cases
- um and
- i went a little bit further to help you
- out on this because
- this takes you know running your model a
- bunch of times to check it
- i also gave you for the default
- inputs um what the irr
- is with the different default cases
- so defaulting in year one two three four
- five and no default
- here are the irrs that you would earn in
- each of those cases
- so you should be checking this first
- make sure you can match these numbers
- with the different cases in your model
- um and then once you
- have all the iteration things built out
- then you can check against this solution
- um and the grading is the same as the
- first project
- 60 accuracy 20 readability 10 formatting
- ten percent properly using the template
- and five percent on the bonus
- so that is the overview of the second
- project
- on probabilistic loan pricing thanks for
- listening
- and see you next time
Project 3 - Monte Carlo Cost of Capital¶
Notes¶
The focus of this project is to start working towards the Discounted Cash Flow (DCF) valuation of a stock. The weighted average cost of capital (WACC) is a component of that model
This project also tests your ability to carry out a Monte Carlo simulation
Project 4 will be the full DCF valuation, which means it will include doing this analysis again for a different company. I encourage you to write general functions that will be useful for both, and after this project you can move those functions into a separate file so they can be shared
This is also our first project that requires working with external data
You can feel free to modify any of the data files. If you do modify them, then submit them along with your project. If you did not modify the file, leave it out of the submission
To earn the bonus, complete the project without modifying any data files. I would recommend saving this for last as it will be a substantial challenge. If you plan to do the bonus, as you modify the files for your model, take note of every manual step that you take so you can try to automate it later
Please see the project description for some links to extra resources to help you automate the data cleanup
As with Project 2 and the remaining projects in the course, you are free to submit a Python model, Excel model, or combination Python/Excel model. The only requirement is if you do submit a combination model, the final outputs should be in Python
Resources¶
Transcript¶
- hey everyone
- nick dear bird is here teaching you
- financial modeling today i'm going to
- introduce the third project
- in the course monte carlo cost of
- capital
- so this project is doing two main things
- one it's testing the knowledge of all
- the monte carlo material that we've
- covered
- in the course and the other is
- starting to work towards what's going to
- be the capstone
- model of the class the discounted cash
- flow valuation
- of a stock or dcf model and within the
- dcf model
- you have to determine the cost of
- capital
- for the company the weighted average
- cost of capital or the whack
- and uh that's one part of the model and
- then the other part of the model deals
- with the free cash flows
- so for this third project we're just
- going to focus on the whack
- and then in the final project in the
- course we're going to then
- do the entire dcf model
- so for this project we're all going to
- work on the same company we're all
- going to try to figure out the whack for
- walmart
- and that's the main goal here is to
- figure out the whack
- and so that's going to involve working
- with both the debt
- and the equity finding the costs and the
- weights of debt and equity and you can
- ignore
- preferred stock for the purposes of this
- model
- so not only should you just find the
- whack
- that's kind of the core or base model
- then you also want to run the monte
- carlo simulations on that
- and we want to see all the standard kind
- of outputs that you would have from a
- monte carlo simulation a
- visualization of the probability
- distribution through a histogram
- as well as a table of the percentiles of
- the distribution
- and you want to evaluate which of the
- variables
- has the greatest contribution to the wax
- so you want to do the full analysis
- of the relationship between the inputs
- and the outputs
- so that's the main idea here and
- to go along with this project there are
- a number of data files
- provided so
- you're going to get prices stock prices
- debt information
- um you're going to get the income
- statement and balance sheet
- as well and we'll we'll take a look at
- what these look like
- and so for this project
- um you can feel free to modify these
- data files
- in any way that you want just if you do
- make any modifications
- then submit the modified data files
- along
- with your project if you don't submit
- any of the files i'm going to assume
- that you were using the original file
- and so i'll run it with the original
- file and if it doesn't work because
- it was supposed to be modified and you
- didn't submit that that's going to be an
- issue with your model so make sure
- that you provide any of the data files
- that you modify
- and the bonus on this is to try and do
- the entire
- thing without manually modifying any of
- these data files
- and when thinking about the bonus
- i would as always recommend doing it
- last
- even though it's the first step in the
- process
- i still wouldn't recommend trying to do
- it first
- um so do whatever manual cleanup you
- have to do and if you think you're going
- to do the bonus
- just write down every step that you're
- doing to manually clean up the data
- that you can later go back and try to
- recreate that
- with code instead of doing it manually
- and i provided a number of resources
- here
- which are going to help you complete the
- bonus
- you may not need all of them it depends
- on
- the ways that you go about doing this
- and this stuff for the most part is
- outside of the scope of the course but i
- think it's
- it's valuable uh to know this because
- in the real world data files are often
- going to be messy and you need to clean
- them up
- and you don't want to have to have this
- manual cleanup step every time
- it's definitely room for error in your
- model if you mess up one of the manual
- clean up steps
- and it's just a lot more work to keep
- doing that manually
- so um then i provided a few
- notes um to help guide you along here
- so you've got daily stock prices and so
- that means when you calculate returns
- from those prices that those
- returns are going to be daily as well
- so in order to work with the cap m
- then you're going to need to take the
- annualized risk free rate from the model
- inputs
- and divide it by 252 to get a daily rate
- then you can run the cap m and then at
- the end you'll have
- a daily uh result at the end
- so you can multiply that by 252 at the
- end
- um i already talked about this with
- modifying
- the data files um
- and then we'll look at the debt
- spreadsheet here in a moment but
- the maturity is is missing in some cases
- or
- um it's it just says a year
- if it just says a year then you can
- assume it's december 31st
- of that year if there's a range of
- coupons given for the debt
- then you can just take the midpoint of
- that range and use that
- as the coupon
- if there is not enough information to
- calculate a bond price
- then you can just use the principal
- amount
- instead of trying to calculate a market
- value
- if there's not enough information to
- calculate a price and it doesn't have a
- principal
- then you can just exclude it
- so let's take a look at these data files
- so first one s p 500 prices
- this is the prices on the s p 500 pulled
- straight from yahoo finance
- and the adjusted close column is going
- to be the one that you would want to use
- to get the price
- including any dividends or share splits
- or things like that
- and next we have the walmart debt
- details
- or let me just quickly hit the uh
- walmart
- uh prices first um
- walmart prices same exact thing as the s
- p
- prices just for walmart instead of the s
- p so again
- the adjusted close column is the one
- that you want to use for the price
- um and then that details
- so this is where what you're going to
- use to calculate the market value
- of debt this is pulled from
- s p capital iq and
- you'll see here first there's actually
- two tables
- in here you'll notice that the top
- table is more recent so just use the top
- table
- you can completely ignore the bottom
- table it should not be included it's old
- data
- so just using the top table
- and then this is where i was talking
- about some of the maturities or just
- years you can assume that's december
- 31st
- and then some of the coupons are
- arranged you can just take the midpoint
- of that range
- um and you're going to want to be
- calculating the market value of these
- instruments to try and get at the market
- value of debt
- for walmart
- and then the income statement
- is provided here so this gives you
- all the income statement items and
- quarterly time periods starting with the
- oldest over here
- and the newest all the way on the right
- so you just want to use the newest data
- you don't need to worry about any of
- this
- old data just the newest is what you
- want to use
- same thing coming to the balance sheet
- all the balance sheet items different
- time periods
- again just use the most recent period
- provided
- so those are the data files that are
- provided
- with the project
- here are the inputs you're going to have
- in the project
- and the grading is similar to
- what it has been in the other projects
- accuracy readability formatting
- following the template and a 5
- bonus
- and i gave you some solutions
- with the baseline inputs so you can
- check to make sure that things are
- working appropriately
- and that's what's involved here i'll
- just quickly go ahead
- and show the
- templates um so here is the
- excel template as the baseline inputs
- and the simulation inputs and then
- if you're doing the combine excel python
- model then you can put
- your baseline whack here all the
- intermediate outputs here
- and if you're doing a pure excel model
- then you want to put all your monte
- carlo results here
- so then you'll have each of the wax
- as well as the other inputs that it's
- associated with
- for all 10 000 iterations there
- and then on the python side this
- is the template so it has the
- the model inputs and it has the
- simulation inputs
- and you want to ultimately set these
- variables with the results from
- your baseline model and after the
- simulation you want to set
- the simulation results into the wac
- mcdf
- um and
- so you like just like the last project
- you can submit pure python for your
- excel or a combination model
- um but if you do the combination model
- the one restriction i'm going to put
- is that then your answer your your you
- should be
- ultimately um
- setting these variables in the python
- template and expect to be
- me grading these variables in the python
- template
- um so
- um but other than that you can use any
- of the two tools that you would like for
- whatever parts of the
- project
- and regardless of whatever setup you
- have
- when i run the model it should run the
- monte carlo simulation
- it should not just be a hard-coded
- simulation
- in there that was run by some means that
- i don't know
- um so yeah that's everything
- that's involved here in the project
- feel free to reach out with any
- questions about it
- and thanks for listening i'll see you
- next time
Project 4 - Full DCF Valuation¶
Notes¶
The focus of this project is to complete the full Discounted Cash Flow (DCF) valuation of a stock
The other main focus of the project is to transition you into modeling in the real world where you won’t have the template and answers provided
This is an open-ended project, you get to choose the company you want to value and you can feel free to use Python, Excel, or a combination
Be sure to complete all the extensions to the model including sensitivity analysis, scenario analysis, Monte Carlo simulation, and visualization or you will lose substantial points
Your analysis of the company is important to set the assumptions for the forecasts. You need to research the company and its plans for the future and incorporate your findings into the forecasts
You also need to analyze the results from the model and the extensions, and produce a report of your research and findings from the model
The bonus is similarly unstructured, do a great job in any area(s) of the model to be eligible for the bonus
Resources¶
Transcript¶
- hey everyone this is nick dear burtis
- teaching you financial modeling
- today we're going to be introducing the
- fourth project
- in the course the full discounted cash
- flow valuation of a stock
- so this is uh the main model that we've
- been working towards
- in the course that kind of puts together
- everything that we've
- learned um so
- within this project you're going to pick
- any publicly traded
- uh company that you would like and
- you're going to research
- that company and you're going to create
- a full discounted cash flow evaluation
- of that company's stock
- and it's on you to go and find all of
- the data that you're going to use
- in your model so basically um
- i'm not really giving you anything here
- other than guidelines on
- on what needs to be in the project and
- it's on you to go
- and find all the data um
- find the information about the company
- et cetera so um
- this project um is trying to
- transition you out of the structure of
- the course
- and into uh modeling in the real world
- where you're not gonna have the nice
- templates
- or answers provided
- when you're going and building models on
- the job
- or for personal purposes so
- this project is added in that
- unstructured
- direction that you'll face when doing
- modeling outside of the course
- so within this
- model you're going to have basically
- what was
- project three of estimating the whack
- for the stock rated average cost of
- capital
- and then we're adding on additionally
- the
- free cash flow estimation and
- forecasting part of the model
- and i've noted here that you have to
- forecast the
- financial statements not just the
- um the free cash flows directly
- so you can forecast the free cash flows
- directly but that should be as an extra
- check
- not as the main result in your model
- you do need to forecast at least the
- income statement and balance sheet items
- necessary to calculate
- free cash flows in the future
- and then beyond the base model
- you need to have a few other components
- in here
- so what's important in going throughout
- this valuation
- is that this is our first model which
- has had a lot of
- different information that you can
- incorporate
- about the company and so it's a little
- bit subjective
- in how you pick the assumptions
- for the model and it comes down to your
- opinion
- about the company and your research uh
- on the company so um
- even two people that worked on the same
- company could have completely different
- answers here
- and they both could be perfectly valid
- it just comes down to your
- research and opinions about the company
- and so in order to
- um justify the
- assumptions that you're making you're
- going to have to also give
- a written justification of the
- forecast assumptions um so the best way
- to do this is just to have
- kind of a full write-up on the model
- explaining
- just giving a background on the company
- and your findings from the research
- and going through um
- basically the basic structure of the
- model um
- and talking through all the assumptions
- that you made and why
- and what from your research and opinions
- did you draw on
- in order to uh pick the
- assumptions for the forecasts
- so beyond the base model and that write
- up of the model
- then we also want to have the
- extensions um to the model that we've
- seen throughout the course
- um so scenario sensitivity analysis
- monte carlo simulation visualization we
- want to see all of those things coming
- into
- the model so if you're missing any one
- of these
- then that's going to be a hit to the
- grade so make sure that you complete all
- of these components
- and as far as getting the stock price in
- the
- model you also want to use both the
- perpetuity growth
- and exit multiple approaches and look at
- multiple different accent multiples
- for getting that price so you'll really
- have multiple different prices
- coming out of the model and in order to
- get that to a single price
- for the purposes of these extensions you
- can
- either pick whichever one you think is
- more accurate and justify
- why you think it's more accurate or you
- can take a weighted average
- of the other approaches and use that as
- your
- singular estimate of the stock price
- so those are the main things that are
- involved here
- um and uh for the scenario analysis
- you're thinking about states of the
- economy for that
- um sensitivity you're going to want to
- pick whatever you think are the most
- impactful variables in your
- analysis and similarly for the monte
- carlo simulation
- the more variables the better but
- whatever the most impactful ones are
- should be in there
- the grading is a little bit different
- for this project
- because there's no template and so
- there's no template portion
- of the grade and where that has that
- grade has shifted to
- is instead towards the readability so
- for the other projects the readability
- has been 20
- here it's 30 and that shift
- is mainly because this is a larger and
- more complex model than we've built
- in the previous projects and the
- more uh the bigger the model is and the
- more complex it is
- the more important that readability is
- uh
- for someone to understand and work with
- the model so
- putting a greater emphasis on that here
- um and there are no provided solutions
- because
- everyone's working on a different
- company and also
- even people working on the same company
- can have different valid solutions
- and as far as the bonus in this project
- so the bonus is also similarly less
- structured
- so for the bonus it's basically just if
- you do any parts of the model very well
- you can earn a bonus for those parts of
- the model so it could be the
- visualization it could be
- the write up it could be the structure
- of the model
- how it's organized it could be a
- particularly thorough
- analysis of any part of the model um
- so anything that basically impresses me
- that you've done in the model
- is going to be eligible to earn a bonus
- and
- the bonus could go up to five percent of
- the grade
- so that's an overview of this project
- um so i hope that this is enjoyable for
- everybody since you get to
- choose a company which you are
- interested in learning about and
- analyzing
- and go through this whole process and
- this is a really nice
- full built out model to have as you go
- into
- job interviews you can show off this
- model and all the fancy stuff that
- you've done in it all your thoughtful
- analysis into it
- and that's something that could be
- impressive in an interview setting
- um so that's the overview of this
- project
- so feel free to reach out with any
- questions about it
- and thanks for listening see you next
- time