00:03: hey everyone
00:04: nick dear burtis here teaching you
00:06: financial modeling and today i'm going
00:08: to talk about
00:09: how the projects are graded in this
00:12: class
00:13: so uh you know the main grade in the
00:16: course
00:16: is these uh projects and that's going to
00:20: be a lot of the value in the course is
00:22: uh you know doing these in-depth
00:24: projects that try to get
00:26: fairly close to real world projects
00:29: so as far as grading these um projects
00:32: you know there can potentially be uh you
00:35: know things which are specific
00:37: to each project but in general the four
00:40: categories which i'm looking at to grade
00:42: these are
00:44: the accuracy of the model the
00:46: readability
00:47: of the model the formatting of the model
00:50: and how uh closely you have followed the
00:54: template
00:55: as is described in the project
00:58: so before we dive into more specifically
01:02: what each of these categories means
01:04: let's talk about how the grading works
01:07: in general
01:10: so i use a combination of absolute and
01:13: relative grading um so you know if you
01:17: got everything perfectly right as far as
01:19: accuracy in your model
01:21: uh then you're going to receive 100 on
01:24: the model accuracy so that's
01:26: the absolute portion of the grading
01:29: uh the relative portion is uh you know
01:32: say there was some part of the project
01:34: that nearly all of the class got
01:36: incorrect
01:37: well then i'm going to take off a very
01:40: small amount of points
01:41: for getting that part of the project
01:43: incorrect so i actually look
01:45: i you know i look at a bunch of
01:47: different models a bunch of different
01:48: students models
01:50: before i assign any grades so i can
01:52: understand where the common
01:54: issues are and take off less for those
01:58: issues
02:02: and then each category
02:06: is just going to have a percentage
02:07: weight in the project and each project
02:10: has a table telling you
02:11: exactly what are the percentages for
02:13: this project
02:15: but generally model accuracy is going to
02:18: be by far the highest at
02:19: typically around 60 percent and then the
02:22: other categories are
02:23: are ten or twenty percent each
02:26: um so then let's dig into the individual
02:29: categories
02:30: so for model accuracy you know that one
02:32: is is mostly
02:33: straightforward do you get the correct
02:35: result from the model
02:38: now an important thing to consider here
02:40: in the accuracy
02:41: is that we're building models here we're
02:43: not just doing a single
02:45: calculation and so you know i'm going to
02:48: give you
02:48: some inputs into the model and i'm going
02:50: to give you some default values
02:53: of those inputs and you know you get
02:56: the solution from that that's not
02:59: necessarily enough even if you have
03:01: you know the result completely correct
03:03: based on those
03:04: particular inputs your model needs to be
03:07: able to adjust appropriately
03:09: when different inputs are passed as well
03:12: it needs to be able to work
03:13: for any reasonable value for any of the
03:17: inputs and
03:20: you know in order to check this i'm
03:22: basically going in and trying a bunch of
03:23: different
03:24: inputs and making sure that your model
03:27: matches up with what my model would give
03:31: for those same inputs um
03:34: so you know don't just think that you're
03:37: done as soon as you've gotten the
03:38: baseline
03:39: uh answer is correct make sure that you
03:42: can
03:43: actually change around the inputs in the
03:45: model and then your outputs are
03:47: changing in the way that you would
03:49: expect them to change
03:50: from that input changing as well
03:54: so that's an important skill to develop
03:57: i intentionally you know don't give you
03:59: a way
04:00: to directly check that you it's kind of
04:03: indirect and that you have to think
04:05: about your model and how the how each
04:08: input should affect the model
04:10: and does your output actually uh change
04:12: in that way because
04:14: once you get actually on the job and
04:16: you're building out your own models
04:18: you're not going to have any answer
04:19: sheet to check your model
04:22: you just have to think critically about
04:24: what your model should be giving you
04:27: and ensure that it is conforming to that
04:30: so i'm trying to get you closer to the
04:33: real world there
04:36: then thinking next about the model
04:38: readability
04:39: um so this is about how easy for is it
04:42: for me to understand what you're trying
04:45: to do in the model
04:46: and how easily can i navigate through
04:50: the model so thinking about excel models
04:54: are you organizing the model um such
04:57: that each kind of sub
04:59: problem of the model has its own
05:01: dedicated worksheet
05:02: uh with its own inputs and outputs um
05:06: have you separated the inputs and
05:08: outputs appropriately
05:10: [Music]
05:12: do you have clear names for things is it
05:14: clear what each input is and what each
05:16: output is
05:17: and you have you know table headers um
05:20: kind of separating the different
05:22: calculations inputs outputs and
05:24: everything
05:26: and uh you know try to split out your
05:30: calculations
05:31: into multiple calculations so that
05:32: they're more readable
05:34: but if you are going to have a complex
05:35: calculation there better be
05:38: comments explaining that calculation as
05:41: well and then for python
05:47: you know with jupiter we want to
05:50: organize the model into sections the
05:52: each of those sub parts of the model is
05:54: going to be a section
05:56: and you want to have each logical step
05:58: of the model be a function
05:59: and each sub model be a function and the
06:02: entire model be a function
06:04: and all those functions should have uh
06:06: doc strings
06:07: which explain what they're doing uh
06:10: there should be comments or
06:12: uh markdown the jupiter nicely formatted
06:14: text to explain
06:16: uh what's going on in your model the
06:18: inputs should be all at the top
06:20: the outputs main outputs should be at
06:23: the bottom
06:24: um and you shouldn't have super long
06:27: lines going way out
06:29: uh basically if i can't see it on my
06:32: screen
06:33: if it goes past my screen then that's a
06:35: problem
06:37: and you know there are lots of ways to
06:39: break things onto
06:40: multiple lines you can use parentheses
06:43: or you can use a backslash
06:45: definitely get in contact with me if you
06:48: have that issue and you can't figure it
06:49: out
06:50: but google will be helpful for that as
06:51: well um
06:54: and then um are you
06:57: showing intermediate results uh it's
07:00: better if you're kind
07:02: of showing the steps along the way what
07:03: you get from each step
07:05: uh rather than just getting all the way
07:07: to the end you just wrote a bunch of
07:08: code and then you get one answer at the
07:10: end
07:10: it's like very difficult to understand
07:12: what happened in the middle uh if you
07:14: show these intermediate
07:16: results then it becomes a lot more clear
07:20: and then your names of variables
07:21: functions and classes
07:23: do they follow the standard python
07:25: conventions
07:26: so you know all the code that i've shown
07:28: does follow the conventions
07:30: but make sure to take a look at this
07:32: guide
07:33: it will give you all the detail that you
07:36: need to be able to name things
07:37: appropriately
07:41: and then moving on to model formatting
07:43: so model formatting
07:45: is you know about visually how does the
07:46: model look
07:48: um and so for excel you know really
07:51: comes down to
07:53: you know the number formatting the table
07:55: formatting
07:57: and you know having separate formatting
08:00: of the inputs versus the outputs
08:01: uh versus the calculations to make
08:04: things very clear
08:05: what uh belongs where uh you shouldn't
08:08: have like
08:09: you know cells which are too short such
08:11: the text is getting cut off
08:13: and you know things like that it should
08:15: look nice and and be nicely
08:17: laid out and everything
08:20: and then for python it's all about the
08:23: outputs of the model how are they
08:25: formatted
08:26: um so are you applying the appropriate
08:28: number formatting
08:29: to any numbers that you're showing as
08:32: your results
08:33: um are you including a nice sentence to
08:35: explain
08:36: what that result means um
08:40: and then you know we haven't
08:43: covered yet how to do tables and plots
08:45: in python but we will get there
08:48: so when we use those making sure that
08:50: those
08:51: are used when it makes sense to use them
08:53: and that
08:55: they're formatted nicely and that plots
08:58: you know have labels for the axes and
09:01: are
09:01: a reasonable size to be including
09:06: and then the last category here is
09:08: following the template
09:10: um and this is all about you know kind
09:13: of exactly following the instructions of
09:15: the project for matching a certain
09:17: expected structure
09:19: of the project and the reason uh
09:22: i had to make this a grading category is
09:24: because
09:25: at least for the for uh you know a
09:28: majority of the projects
09:29: i have built out um auto grading
09:32: for the project i've built code which
09:34: checks the code
09:37: but it needs to be in that certain
09:38: structure for that code to actually
09:41: work so if you're not following the
09:44: template appropriately i'm going to run
09:46: that
09:46: on your code and it's just going to fail
09:48: and then i'm going to have to go and
09:49: figure out
09:50: where you didn't follow the template
09:52: properly and fix that
09:54: before i can actually get the accuracy
09:56: grade for your project
09:58: so that auto grader is just used for
10:01: accuracy
10:02: and basically i just run it and then it
10:04: tells me if you got everything correct
10:06: or if you got anything incorrect uh then
10:09: it will also let me know
10:11: and if you do get something incorrect
10:13: then i go into your model and i dig into
10:16: it and i figure out exactly where it's
10:17: going
10:18: wrong and i actually fix all the
10:20: mistakes one by one
10:22: until your model is perfect on the
10:24: accuracy and the auto grader can return
10:26: it
10:27: as being perfect accuracy so that way
10:30: i can tell you in the comments exactly
10:33: the points where you went wrong
10:34: in the model as far as the accuracy
10:38: um and give you in a grade uh
10:40: commensurate with that
10:42: um and then um regardless of the
10:44: autograder i'm always going
10:46: through everyone's projects uh line by
10:48: line to check for the readability and
10:50: the formatting purposes
10:53: but anyway for uh following the template
10:56: what is considered here in the grade
10:58: for both excel and python there's often
11:01: going to be a template
11:02: you need to start from so make sure that
11:04: you do indeed start
11:05: from that template um and then thinking
11:09: about excel specifically
11:11: uh you know there's typically gonna be
11:13: uh you know certain inputs
11:15: and certain outputs in the template do
11:17: not move
11:18: any of those inputs or outputs the
11:20: locations of the cells the cell
11:22: references
11:23: should stay the same uh for the inputs
11:26: and outputs
11:27: you can change the formatting if you
11:28: want the visual stuff but
11:30: the the location of the cell the cell
11:32: reference should not change or it is
11:34: going to break the auto
11:36: grader um then
11:39: in python there's a few things
11:42: so we're going to have the in the
11:44: template there's going to be this model
11:46: inputs data class already defined for
11:48: you
11:49: and it's going to be creating an
11:50: instance of that
11:52: class as model underscore data
11:56: so don't change either of these names
11:57: keep it with model inputs keep it with
11:59: modeled data
12:01: um and then don't
12:04: uh rename any of the variables which are
12:07: defined as part of that data class
12:10: if you want to add additional variables
12:11: you can do that they can have any names
12:13: that you want
12:14: but for the ones that are there already
12:16: don't change those names
12:20: and then the inputs should be at the top
12:22: the output should be at the bottom
12:25: and then often the instructions in the
12:27: project are going to say
12:28: you know define this variable and it
12:32: should have
12:32: this inside the variable it should be
12:34: this certain data type and structure
12:36: so make sure that you conform to that
12:38: exactly
12:40: for example uh like the first project is
12:43: going to ask you for
12:44: cash flows uh as a list of numbers
12:47: so make sure that it's actually numbers
12:49: in there and you don't have like
12:50: formatted strings with the dollar signs
12:52: and everything
12:53: in them when you show the outport output
12:56: it's got to be formatted but as far as
12:58: the variable
12:59: that i'm asking you to store it in that
13:02: should match exactly the data type
13:04: of what i'm requesting in the project
13:07: for the autograder to work
13:08: appropriately so that
13:12: wraps up the overview of the grading of
13:15: the
13:15: projects so in the remaining videos
13:19: we're going to discuss
13:20: what's involved in each project thanks
13:23: for listening
13:24: and see you next time

00:03: hey everyone
00:04: nick dear burtis here teaching you
00:06: financial modeling and today
00:08: i'm going to talk about the first
00:10: project in the class
00:11: giving a quick overview on some
00:13: additional material we need to complete
00:15: the project
00:16: as well as introducing the project
00:19: itself
00:20: so this project
00:23: is trying to work in mainly cash flow
00:26: modeling
00:27: is the main work involved in the project
00:31: and cash flow modeling is something you
00:33: need to do in the majority
00:35: of financial models so it's a good area
00:38: to focus on
00:39: first and
00:43: so you know this uses pretty much the
00:45: same concepts
00:46: as the retirement model uh you know it's
00:49: still ultimately a time value of money
00:51: problem but we're going to be
00:54: approaching a capital budgeting setting
00:56: instead of a you know personal finance
00:58: retirement setting
01:01: um so we just need to quickly look at
01:03: some stuff which
01:05: should definitely be review for you uh
01:07: you know it's just some basic
01:08: micro economics which is going to be
01:11: involved
01:12: in this capital budgeting problem
01:16: um so you know we're really just you
01:20: know talking about supply and demand
01:21: here
01:22: which um you know everyone should have
01:24: covered this in their econ
01:26: courses um that
01:29: basically um you know as
01:32: uh you know there's an intersection
01:34: between supply and demand
01:36: um and that's going to be the
01:38: equilibrium pricing quantity
01:40: that's going to be sold for a given
01:43: product
01:45: and then if you have a decrease
01:49: in supply that's going to be this entire
01:52: curve
01:52: shifting to the left then that's going
01:56: to then shift up the price
01:58: and shift down the quantity whereas if
02:01: you get more supply
02:02: then it's going to shift down the price
02:05: and up the quantity
02:06: and the reverse is true for demand
02:09: as demand uh increases pushing the line
02:13: out to the right
02:14: then the price is going to go up as well
02:18: as the quantity
02:20: and then if you push the uh demand down
02:23: then both
02:24: price and quantity are going down
02:28: um so uh when we think about
02:32: the revenue that we're going to get um
02:36: as a producer um it's going to be
02:39: whatever the price was
02:40: times the quantity um
02:43: but it's important to consider that
02:47: you know whatever you produce is not
02:49: necessarily all going to be sold
02:51: it has to match up with whatever the
02:54: demand is
02:55: so the quantity that's actually going to
02:57: get purchased
02:59: is the minimum of the quantity demanded
03:02: and the quantity supplied because
03:06: if there's a ton of production capacity
03:09: but people just don't want the product
03:11: well that low demand for the product
03:14: is going to drive the quantity whereas
03:17: it could be the opposite there's a ton
03:18: of demand demand is really high
03:21: but suppliers just aren't able to
03:22: produce very much
03:24: and so then the supply is going to be
03:26: the constraint here
03:28: and so it's just the minimum of the two
03:31: is ultimately going to be the quantity
03:33: transacted
03:36: and then there's one
03:40: gotcha we got we have to think about
03:44: as we go to complete this project
03:45: ultimately we're going to need to be
03:47: calculating mpv
03:48: in this project and
03:52: npv does work slightly differently
03:57: numpy's mpv works slightly differently
04:00: than the mpv function in excel
04:03: and that difference is just about when
04:06: it considers as the first cash flow that
04:09: you're passing it
04:11: so in excel whatever cash flow
04:14: you pass to it first for the mpv
04:16: function is considered period one
04:20: so if you had um you know whatever you
04:23: have there is going to get ultimately
04:24: discounted
04:26: and determining the present value and if
04:28: you want to have a period zero
04:29: you have to just add that separately
04:31: outside the mpv
04:33: uh conversely uh numpy's mpp function
04:36: treats the first cash flow as period
04:39: zero
04:40: so if you want to have a period zero
04:42: cash flow it would be first
04:44: and then your period one cash flow to be
04:45: second and you just pass them all
04:47: in the list to the mpv function
04:51: um so then if you don't have a period
04:53: zero cash flow your first cash flow is
04:55: coming in period one
04:56: well then you would just want to add a
04:58: zero as the first element in the list
05:01: so that it's considering that second
05:04: item in the list as period one
05:09: so that's the main thing to be careful
05:11: about um otherwise
05:13: uh you know uh it works essentially the
05:16: same
05:16: between the two so then jumping over
05:20: to the description of the project um so
05:24: again this is kind of a classic
05:26: capital budgeting problem where we have
05:29: um you know these uh fixed assets that
05:32: we can buy
05:32: here it's machines and those fixed
05:35: assets uh
05:36: buy us production capacity such that we
05:38: can produce a certain number of units
05:40: um and then producing those units has a
05:42: variable cost
05:43: associated with it um and you can sell
05:46: them to earn a revenue
05:48: on those um and then uh
05:52: also coming into this problem is uh
05:55: thinking about the demand
05:56: as well uh there is going to be
05:59: demand in here and you can never sell uh
06:02: more
06:03: than the demand right the quantity
06:05: purchased is going to be the minimum of
06:07: supply and demand
06:08: and basically you have a choice in each
06:10: year whether you're going to
06:13: buy a machine spend your budget on
06:14: buying a new machine which can up your
06:16: production capacity
06:18: or instead you can spend that money on
06:20: advertising and then advertising is
06:22: going to increase the demand
06:23: for your product um so you know here you
06:27: can control whether you're
06:29: upping the demand or upping the supply
06:31: and you know
06:33: the the point of the model is um you
06:35: know it could be used to find that
06:37: balance of demand and supply
06:39: that's going to maximize your mpv
06:43: um and there's a few uh assumptions that
06:47: we can work in here
06:48: to simplify our model um so one is i'm
06:51: allowing you to assume that
06:53: you know the model just goes to 20 years
06:55: nothing is going to happen
06:56: after 20 years
07:00: and then we can also assume
07:03: that um you know we're paying
07:06: for either this machine or this
07:08: advertising and every single year
07:10: um even after all of the you know the
07:13: project is over
07:15: the machines have shut down zero cash is
07:17: coming in
07:18: just assume that you're still you know
07:20: paying that cost of advertising
07:22: um that's just you know to make it a
07:24: little bit simpler
07:25: on you and then
07:29: you know definitely make sure that
07:30: changing around the inputs the outputs
07:32: are going to change
07:33: like you would think that change in the
07:35: input would cause
07:37: um and another simplifying assumption is
07:40: we can assume that
07:41: um fractional phone sales are fine
07:44: you don't need to do rounding on any
07:47: quantities
07:49: just leave it as the fractional amount
07:54: so then we have these inputs to the
07:56: model here
07:58: you know most of the things we just
07:59: talked about
08:01: the number of phones the machines can
08:03: produce the number of machines that
08:05: you're going to purchase
08:06: the life of the machine the price of
08:10: each phone the scrap value of the
08:11: machine
08:12: uh you know when you sell it or when
08:14: once the machine is out of life you can
08:16: sell it for
08:17: scrap value so that's what this is um
08:21: the price of the machine or you know
08:22: we're making it simple by just making
08:24: the advertising costs the same
08:26: it's just either you spend your budget
08:27: on the machine or you spend it on
08:29: advertising
08:30: in that given year the
08:33: cost of producing a single phone
08:36: variable cost the initial demand
08:40: the demand growth for each advertisement
08:43: that you do
08:45: and the interest rate
08:48: um and then the main outputs we're
08:50: looking for from this model are the cash
08:52: flows year over year
08:53: as well as the mpv of those cash flows
08:58: um and then i have also included a bonus
09:01: problem here
09:01: so you know for the typical student i
09:04: would not
09:05: uh recommend going after the bonus
09:07: problem
09:08: really only if you've already finished
09:09: up your project and you have extra time
09:11: and you want to add additional learning
09:13: that's when you go for the bonus project
09:14: or problem uh because it is
09:17: going to require basically going out on
09:19: your own and learning some stuff which
09:21: we don't have time to cover
09:23: in the course and working an additional
09:27: structure into your existing model
09:30: so this uh part is all about doing the
09:33: optimization
09:34: it's about you know okay now we have
09:36: this model which has all the structure
09:38: now how do we actually find you know
09:41: what should be
09:42: the um number of machines that we
09:46: purchase
09:47: um you know based on um
09:51: the demand and the supply trying to
09:52: maximize our
09:54: mpv um
09:58: and a key thing about this project is
10:00: you are going to submit
10:02: basically two entirely separate
10:04: submissions for this
10:05: one is an excel model and one is a
10:08: python model so you're doing the entire
10:11: model
10:11: end-to-end and both excel and python
10:17: so that's definitely a fair amount of
10:18: work definitely start early
10:20: on this um this project uh
10:23: in the past has been the one where
10:25: people have struggled the most
10:27: just because of the learning curve
10:28: involved in
10:30: um and mainly the python side of just
10:33: getting started over there
10:36: um so for the excel exercise make sure
10:39: you start from the template we'll look
10:41: at that in a moment
10:42: ensure that everything is referenced all
10:44: the way through everything
10:46: should flow from inputs to calculations
10:48: to outputs
10:49: um and be sure not to change the
10:51: location
10:52: of any of the inputs or outputs and
10:54: you'll submit the excel workbook
10:56: for that for python um
11:00: start from the jupiter notebook template
11:02: um
11:03: and i should be able to run the model
11:06: uh from the top to the bottom just
11:09: running all the cells in order
11:10: and get the expected output definitely
11:14: don't
11:14: change the model inputs
11:18: and you need to have
11:21: certain variables defined
11:24: for the auto grader to pick up your
11:27: answers so have the cash flows
11:29: variable defined as you know the years 1
11:33: through 20 cash flows
11:34: which should be numbers it should not be
11:36: formatted strings
11:37: and have the mpv variable defined as the
11:41: number again it should be a number
11:43: of the npv not a formatted string but
11:46: when you show your results you should be
11:49: showing a formatted result so you've got
11:52: to
11:53: be able to handle both to keep the
11:54: numbers in the original variable
11:56: show the formatted
12:00: and then this project has kind of the
12:02: standard grading structure
12:03: uh 60 accuracy 20 readability
12:07: 10 formatting 10 following the template
12:10: and then you can earn up to 5 on the
12:13: bonus
12:15: now what i do for a number of the
12:16: projects including this one is to give
12:18: you some answers so you're not just
12:20: completely in the dark
12:21: on this so if you have these values
12:25: for the inputs
12:28: then these should be the answers that
12:31: you get
12:32: as a result now it's important that
12:36: you know once you've built out your
12:37: model and you're able to match this
12:39: don't just stop there you've got to go
12:41: and start changing around your inputs
12:42: and make sure that things change in the
12:44: way you expect
12:45: if you go and change an input and just
12:47: nothing at all changes
12:49: uh then you know there's probably an
12:51: issue where you've actually hard-coded
12:52: the value somewhere in the calculation
12:54: and it's not actually using the input so
12:56: that would definitely
12:58: cause you to lose points on accuracy
13:00: make sure to check for those
13:01: kinds of mistakes
13:04: so that's the overview of the project
13:08: and then we can look at the templates
13:11: so here is the excel template and so
13:14: you'll see the
13:15: inputs here on the top left on the
13:18: inputs and outputs tab
13:19: so don't change the name of this tab
13:21: don't change
13:22: the location of any of these cells
13:26: but you can mess around with the
13:28: formatting if you don't like the way
13:29: that i have
13:30: formatted it and as you do all your
13:34: calculations make sure you reference
13:36: these cells uh because i will be
13:38: changing these cells around the grading
13:40: and expecting that
13:41: your outputs here change appropriately
13:44: um so then the outputs section is below
13:47: that and you see the yellow highlighted
13:49: cells are where you need to put your
13:50: answers
13:51: um and again those should be referenced
13:54: back from your calculations
13:56: so that everything flows through from
13:57: the inputs to the calculations
14:00: which could be in other tabs back over
14:02: to the outputs
14:04: on this tab um and here we put the mpv
14:08: and then each cash flow one by one
14:11: um again do not move the locations
14:14: of any of these cells
14:18: and then coming over to the
14:21: [Music]
14:22: the python template jupiter notebook
14:26: template
14:27: so you can see here it's got this top
14:30: section
14:31: where you should add your description of
14:32: the model and here you can kind of
14:34: follow this structure
14:36: to also add a table of contents on your
14:39: model that allows you to
14:41: navigate through the different portions
14:44: so make sure to fill that out with your
14:45: sections
14:46: um and then you know set up
14:50: you don't want to remove these but you
14:51: can add additional imports and things if
14:53: you want
14:55: inputs here you don't want to change the
14:57: name of
14:59: this class and you don't want to change
15:01: the name of this
15:02: variable in your final submission
15:06: and so um
15:09: and you also don't want to change the
15:10: names of
15:12: any of these variables either
15:15: [Music]
15:17: so or these either anything that was
15:19: there already you should not change the
15:21: name
15:22: you know you you can add your own
15:25: variables if you want and they can have
15:28: whatever names
15:29: but just don't change any of the names
15:31: of the variables that are there already
15:34: and then here you know i'm you know
15:36: saying what you need to set these
15:38: variables to
15:39: so just make sure that you do actually
15:40: indeed set these variables
15:42: um and that you you know delete this out
15:45: and just put
15:46: uh you know what this variable actually
15:49: is
15:50: in this final cell and it should be all
15:51: linked through such that
15:54: if you change the inputs in your model
15:56: it's going to change
15:57: the values we're seeing for these
15:58: variables as well
16:02: so that's an overview of the first
16:06: project
16:07: so definitely get started early on this
16:12: and please do feel free to ask me
16:14: questions about it
16:16: so thanks for listening and see you next
16:19: time

00:02: hey everyone
00:03: nick diabetis here teaching you
00:04: financial modeling today
00:06: we're going to be introducing the second
00:09: project
00:10: in the course on probabilistic loan
00:13: pricing
00:14: so this project has a couple of main
00:18: goals for your learning objectives one
00:21: is to have a model where probability
00:25: is really a first-class citizen
00:27: something that we're thinking about
00:29: very deeply within the base model itself
00:32: probability factors into so many
00:34: financial models
00:36: that it's good to hit that early in the
00:39: course
00:41: and then the other main
00:45: area we're testing here in this project
00:48: is
00:48: then to be able to um
00:51: extend the base model in different
00:55: ways so here we're going to build out
00:58: an initial model and we'll have to run
01:00: that model a bunch of times
01:02: to understand all the different ways
01:05: that things could occur
01:08: so let's talk about what this project
01:11: actually is
01:13: so the idea here is
01:17: that you're an analyst within a bank
01:21: and the bank is considering doing a loan
01:24: to a small business so
01:28: this also gets in the whole debt area
01:31: and decisions
01:32: of of lenders
01:35: so the business needs to
01:39: buy a machine they want to borrow money
01:41: to buy the machine
01:43: and then after some amount of time
01:44: they're going to repay that loan
01:46: in full the interest is paid annually
01:52: at a certain rate based off of the
01:54: amount that they borrowed
01:58: and where the probability factors into
02:01: this
02:02: is that the business is not always going
02:05: to be able to pay back the loan there is
02:07: a
02:08: chance that they're going to default on
02:10: the loan
02:11: and not be able to make their payments
02:15: um so the probability of default here
02:20: it starts at an initial probability of
02:22: default and then it decreases over time
02:24: because the business becomes
02:26: more mature year over year and so it's
02:29: less likely that they're going to
02:30: default
02:31: as each year goes by but
02:35: in that final year when the loan is
02:36: actually due the business
02:39: doesn't just have to pay the interest
02:42: payment they have to pay
02:43: the full amount of the loan the
02:45: principal in addition
02:47: to the interest payment so that's a much
02:50: bigger
02:50: obligation for that final year and so
02:53: the final year
02:54: has a separate default probability which
02:57: is going to be higher than their typical
03:01: default probability as overtime would
03:04: save for the interest payments
03:09: and we need to model in
03:13: what happens whether the borrower
03:16: defaults or doesn't default
03:19: and the borrower can default in any
03:21: given year
03:23: that they have to make a payment
03:26: so whenever the borrower defaults then
03:29: they're going to go into bankruptcy and
03:32: we're going to say that the bankruptcy
03:34: process always takes two years
03:37: and then uh after bankruptcy the
03:40: lender is going to collect a certain
03:42: percentage of the original loan amount
03:44: the recovery percentage um
03:49: and so what that means as far as forming
03:52: the cash flows
03:54: uh in the year that the borrower
03:56: defaults then there will be zero
03:58: cash flow in that year it'll be zero
04:01: cash flow
04:02: um the next year and then two years
04:06: after the default we're going to get the
04:08: recovery payment
04:09: as the lender recovery times the
04:13: price of the machine which is equal to
04:15: the loan amount
04:17: um and so that does mean that the amount
04:20: of cash flows
04:21: could be greater than the amount of the
04:23: loan if they defaulted in the last year
04:25: then
04:25: it's going to be two years more than the
04:28: life alone that the final cash flow
04:30: comes
04:34: so this is a fairly realistic kind of
04:38: model to determine basically what
04:41: what terms should the bank offer on the
04:44: loan
04:46: um and so the main question here
04:50: is uh you know you're you're the loan
04:53: analyst
04:54: trying to come up with what are the
04:56: appropriate terms
04:58: for this loan to this bank based on what
05:00: we know about the bank
05:03: um and so
05:06: you're trying to give the loan officer
05:09: all the information to make these
05:10: decisions
05:12: so you want to be able to say with your
05:15: model for any given inputs
05:17: what's the expected irr of the loan
05:21: and what does that irr look like for
05:24: some different interest rates um so
05:27: here we're going to be looking at 30 35
05:31: and 40 interest rates for the loan
05:36: um and then
05:39: it goes even further to say that
05:42: um you know there's always a lot of
05:45: uncertainty in estimating the
05:46: probability of default
05:47: you can never estimate that accurately
05:50: so
05:51: uh the lending officer wants to see what
05:54: everything looks like
05:55: with some different assumptions for
05:58: the initial probability of default so
06:02: uh base case is 20 per year
06:06: but the lending officer also wants you
06:08: to evaluate
06:09: 10 percent and 30 per year
06:15: and then the other factor here
06:19: is how long should the loan be and so
06:22: the lending officer also wants to see
06:24: all the results with 5
06:25: 10 and 20 years
06:28: [Music]
06:29: and important thing about how the
06:32: results should be structured
06:34: is that your model should show all of
06:37: these
06:38: results it shouldn't require going in
06:40: and manually changing around numbers to
06:42: produce these results
06:44: just when i open up the model i should
06:46: be able to see the answers for
06:48: all of these different interest rates
06:52: probabilities defaults and loan lifes
06:56: and then it's also
07:00: part of the main problem to then
07:01: visualize
07:03: your result there should be graphs and
07:06: conditional formatting here
07:10: um so a few notes and assumptions
07:14: we can add into this to make things
07:16: easier
07:18: so first is that you can assume there's
07:21: only going to be 20 years
07:23: of loan life that's the maximum loan
07:25: life
07:26: um if your model can handle more that's
07:29: great
07:30: but um i'm not going to test anything
07:33: longer than 20 years in your model
07:37: but keep in mind that that could be up
07:38: to 22 years of cash flows if the
07:41: borrower defaults in that final year
07:45: um and then as far as the general
07:48: approach to build out the model
07:50: i think it's easiest to go at this with
07:52: an internal
07:53: randomness approach um
07:56: so with the basic structure being that
07:59: you build out
08:00: the base model which um
08:03: just gets you the irr for one set of
08:06: inputs
08:07: and then you're going to um
08:11: run that base model a bunch of different
08:13: times with different
08:15: uh randomly choosing the default cases
08:20: um it is possible to do everything in
08:23: terms of expected value and not actually
08:26: have a random component
08:28: in your model but that does take a lot
08:30: of fairly complicated math
08:32: to make that happen i've only ever seen
08:34: a couple students do that
08:36: and i think it's generally a much more
08:38: difficult way to go about building
08:40: this model and it also doesn't really
08:43: hit
08:43: on a lot of the things that i'm trying
08:44: to test with this model going with the
08:46: internal randomness approach you're
08:48: going to get much more comfortable
08:50: working with a random model and being
08:53: able to extend
08:54: an existing model so that's the approach
08:56: i would recommend in general
09:00: um so when you're doing the internal
09:02: randomness approach
09:04: now you're going to be running the model
09:05: a bunch of times to
09:08: discover what the true expected irr is
09:11: for each set of inputs
09:13: each time it's picking a different
09:14: default case each time you're on the
09:16: model
09:19: so now that it's not a deterministic
09:22: model
09:23: you put the same inputs in and you're
09:24: going to get different outputs each time
09:26: so you've got to keep running it and
09:27: then you've got to take the expected
09:28: value
09:30: across the iterations to get
09:33: an actual estimate of the result
09:36: and the greater number of iterations you
09:39: use the more accurate
09:40: that answer is going to be but the
09:43: longer it's going to take to run your
09:45: model
09:45: so there is a trade-off there and what i
09:48: would recommend
09:49: is as you're building everything out
09:51: keep the number of iterations low
09:54: uh just so you can run everything
09:55: quickly and then
09:57: once you think you've got it right then
10:00: increase the number of iterations
10:02: so you can run it a couple of times and
10:03: check against
10:05: the provided answers
10:08: um so
10:12: and then when you actually submit the
10:14: final you want to run it with a thousand
10:16: iterations before you submit so you can
10:18: get your final answers in there
10:22: and if you've got an excel model or a
10:25: combination model we'll talk more on
10:26: that in a moment
10:27: then it can take a substantial amount of
10:30: time to run your model with all these
10:32: iterations
10:33: um so make sure that
10:37: you're planning for that time and not
10:38: just 11 55 pm you're done
10:40: now let me try and run this thing with a
10:42: thousand iterations oh wait it's taking
10:44: way too long
10:47: um so yeah the next point
10:50: this is a big contrast from the last
10:52: project
10:54: now you have flexibility over the tools
10:57: that you want to use to solve the
10:59: problem
11:00: i'm trying to really emphasize and
11:03: you know build help you build a
11:06: competency
11:06: in uh coming to any arbitrary problem
11:11: and using the tools which are best
11:13: suited for the job
11:15: um and so in some cases that might be
11:18: excel in some cases it might be python
11:20: in some cases it might be a combination
11:22: of the two um so
11:26: same thing for this project and the
11:28: remaining projects
11:29: in the course you're free to implement
11:32: it in the way
11:33: that you like you can submit a pure
11:35: python model
11:36: you can submit a peer excel model or you
11:38: can submit a model which uses both tools
11:40: together
11:44: i am putting one restriction on it here
11:46: that
11:47: if you are submitting a combination
11:49: model then your final answers
11:51: should be in the python side
11:54: which i think in general makes a lot
11:56: more sense anyway
11:58: because the like the most
12:00: straightforward way to combine them is
12:01: to build out
12:02: the base model in excel and then use
12:05: python to run all these different
12:06: iterations on the model
12:08: um so then you already have your results
12:11: in python
12:11: and then i can grade it from your python
12:14: model
12:18: um and
12:22: you know some people really like to uh
12:26: use excel because they're more familiar
12:28: with it
12:29: and python is it's definitely hard to
12:32: learn in the beginning for sure like
12:34: you get through that initial learning
12:35: curve it gets easier
12:37: but people in general are just more
12:39: comfortable with excel at this point
12:40: still i think
12:42: um so you might have the thought cool i
12:44: can use
12:45: either one i'm going to just use excel
12:47: for sure like i'm more comfortable there
12:50: um but you're gonna run into
12:53: issues this this project is
12:55: substantially more difficult
12:58: doing it with purely excel
13:01: um mainly because you're gonna need to
13:04: run your model
13:05: 27 000 times uh and that's changing
13:09: three different inputs together
13:11: because it's three inputs that kind of
13:12: throws out the data table approach
13:15: uh unless you're really hacking it
13:16: together um
13:18: and so this just becomes very difficult
13:20: to do with
13:21: only excel um and so
13:25: if you really are struggling with python
13:28: and
13:28: you just want to do as much as possible
13:30: with excel
13:31: that's fine i would recommend building
13:33: out the base model that gets you an irr
13:37: from the given inputs and then
13:41: using python to run the iterations side
13:44: of this
13:45: because it's really probably not going
13:47: to be more than like 10 lines of code in
13:49: python to do that part of it
13:51: [Music]
13:53: so then you can still work mostly in
13:54: excel and
13:56: use python for something that
14:00: it really excels and um
14:04: though i have seen many students just go
14:07: with pure python
14:08: on this project and i think that's a
14:11: good choice as well
14:16: another thing to note about the fact
14:17: that this is a random model
14:19: or at least if you're going with the
14:20: internal randomness approach
14:22: which the vast majority of students will
14:24: do and should do
14:26: uh is that i'll be providing solutions
14:28: here we'll have them down below
14:30: in this document but when you run your
14:33: model even if
14:34: everything is perfectly correct you're
14:36: not going to match up exactly
14:37: with the solutions because it's random
14:39: the numbers are going to be different
14:41: every time you run it so my
14:44: tip there is to run the model with a
14:46: thousand iterations check it
14:48: against the solutions and basically
14:50: every number should be pretty close
14:52: nothing way off and save your results
14:56: from that
14:56: and then run the whole thing again
14:58: thousand iterations again
14:59: and now check your answers that time
15:01: against the solutions again and
15:03: everything should be close
15:04: again and kind of all three numbers
15:07: you're looking at should not be far from
15:08: each other
15:09: um and that will let you know that
15:12: everything is
15:13: working appropriately at least with the
15:16: baseline
15:16: inputs of course my
15:19: general recommendations still apply that
15:21: you only have the baseline
15:23: solutions given to you and so you need
15:25: to be messing around with different
15:27: inputs in your model and making sure
15:29: that your model can adjust appropriately
15:36: and as with all the projects that have
15:39: templates
15:40: you don't want to move any of the input
15:43: or
15:43: output cells
15:47: um and in python just like with first
15:49: projects you're going to have certain
15:50: variables that need to be defined
15:52: to give your answers
16:00: and then the bonus for this is in two
16:02: parts first part
16:04: is visualization is built into the basic
16:07: problem you need to have some
16:08: visualization to get the full credit
16:11: uh on the accuracy portion readability
16:14: portion
16:16: um but if you do an especially good job
16:19: on visualization
16:21: then you're going to earn a bonus so
16:23: half of that bonus
16:26: comes from doing a really good job on
16:29: visualization
16:31: and the other half is an extension to
16:34: the problem
16:34: where instead of looking at
16:38: 10 20 30 as the
16:42: probabilities of defaults uh
16:45: we want to evaluate
16:48: the probability of default being drawn
16:49: from a normal distribution
16:51: so what do the results look like
16:54: across all the different um
16:58: interest rate and lone life cases but
17:01: with this
17:02: randomly drawn default probability from
17:04: normal distribution with a mean of 20
17:07: and a standard deviation of 5
17:11: and then going further than that you
17:14: want to take a single
17:17: input case so single lone life
17:21: uh single interest rate um
17:25: single default probability and
17:28: look at what the result looks like with
17:30: different number of iterations
17:32: try it with ten hundred thousand
17:35: and you wanna produce the visualizations
17:37: of each summary statistics of each
17:39: um and the idea here
17:43: is you wanna be able to say how precise
17:46: are these estimates
17:47: when i run the model a thousand times
17:49: how far off can i expect to be with my
17:51: result
17:52: when i run it with 10 how far off can i
17:54: expect to be with my result
17:56: um so you want to do that by
18:00: for each number of iterations you're
18:02: going to run that in another
18:04: like meta number of iterations to see
18:06: across these different cases
18:08: with that number of iterations what's
18:10: the distribution
18:11: of the result um
18:15: so the realization part is up to two
18:18: points
18:18: bonus and then uh these things together
18:21: are up to
18:22: three points bonus
18:27: and then we have template files
18:30: for this as well um
18:34: and you have an excel template a python
18:37: template jupyter notebook template
18:39: um again it's up to you whether you want
18:42: to do pure python pure excel or a
18:43: combination
18:44: just whichever tools you choose to use
18:47: use the template
18:48: for that tool so if you have a
18:50: combination model you should be
18:51: submitting
18:52: both parts which are both based off the
18:54: two templates uh
18:56: pure python based off the python
18:58: template pair excel based off the excel
19:00: template
19:03: and just want to emphasize again if you
19:06: do the combo model
19:07: your final answer should be in the
19:08: jupyter notebook on the python side
19:14: and then here you can see the selected
19:16: solutions so it shows for each of the
19:18: different
19:19: interest rate loan life default
19:21: probability what is the expected irr
19:24: across a bunch of iterations looking at
19:27: the different default cases
19:30: um and
19:33: i went a little bit further to help you
19:34: out on this because
19:36: this takes you know running your model a
19:38: bunch of times to check it
19:40: i also gave you for the default
19:43: inputs um what the irr
19:47: is with the different default cases
19:50: so defaulting in year one two three four
19:52: five and no default
19:53: here are the irrs that you would earn in
19:56: each of those cases
19:57: so you should be checking this first
19:59: make sure you can match these numbers
20:01: with the different cases in your model
20:03: um and then once you
20:05: have all the iteration things built out
20:07: then you can check against this solution
20:11: um and the grading is the same as the
20:14: first project
20:15: 60 accuracy 20 readability 10 formatting
20:18: ten percent properly using the template
20:21: and five percent on the bonus
20:25: so that is the overview of the second
20:28: project
20:28: on probabilistic loan pricing thanks for
20:32: listening
20:32: and see you next time

00:03: hey everyone
00:04: nick dear bird is here teaching you
00:05: financial modeling today i'm going to
00:08: introduce the third project
00:10: in the course monte carlo cost of
00:12: capital
00:14: so this project is doing two main things
00:18: one it's testing the knowledge of all
00:21: the monte carlo material that we've
00:23: covered
00:23: in the course and the other is
00:26: starting to work towards what's going to
00:28: be the capstone
00:30: model of the class the discounted cash
00:32: flow valuation
00:34: of a stock or dcf model and within the
00:37: dcf model
00:39: you have to determine the cost of
00:42: capital
00:43: for the company the weighted average
00:45: cost of capital or the whack
00:47: and uh that's one part of the model and
00:51: then the other part of the model deals
00:52: with the free cash flows
00:54: so for this third project we're just
00:56: going to focus on the whack
00:59: and then in the final project in the
01:01: course we're going to then
01:03: do the entire dcf model
01:07: so for this project we're all going to
01:10: work on the same company we're all
01:12: going to try to figure out the whack for
01:14: walmart
01:17: and that's the main goal here is to
01:20: figure out the whack
01:22: and so that's going to involve working
01:25: with both the debt
01:26: and the equity finding the costs and the
01:30: weights of debt and equity and you can
01:33: ignore
01:33: preferred stock for the purposes of this
01:36: model
01:39: so not only should you just find the
01:42: whack
01:42: that's kind of the core or base model
01:45: then you also want to run the monte
01:46: carlo simulations on that
01:48: and we want to see all the standard kind
01:51: of outputs that you would have from a
01:53: monte carlo simulation a
01:56: visualization of the probability
01:59: distribution through a histogram
02:00: as well as a table of the percentiles of
02:04: the distribution
02:06: and you want to evaluate which of the
02:09: variables
02:10: has the greatest contribution to the wax
02:12: so you want to do the full analysis
02:14: of the relationship between the inputs
02:16: and the outputs
02:19: so that's the main idea here and
02:23: to go along with this project there are
02:25: a number of data files
02:27: provided so
02:31: you're going to get prices stock prices
02:34: debt information
02:36: um you're going to get the income
02:39: statement and balance sheet
02:41: as well and we'll we'll take a look at
02:43: what these look like
02:47: and so for this project
02:51: um you can feel free to modify these
02:54: data files
02:55: in any way that you want just if you do
02:58: make any modifications
03:00: then submit the modified data files
03:03: along
03:04: with your project if you don't submit
03:07: any of the files i'm going to assume
03:09: that you were using the original file
03:12: and so i'll run it with the original
03:14: file and if it doesn't work because
03:16: it was supposed to be modified and you
03:17: didn't submit that that's going to be an
03:19: issue with your model so make sure
03:21: that you provide any of the data files
03:24: that you modify
03:27: and the bonus on this is to try and do
03:31: the entire
03:32: thing without manually modifying any of
03:34: these data files
03:37: and when thinking about the bonus
03:40: i would as always recommend doing it
03:43: last
03:44: even though it's the first step in the
03:46: process
03:47: i still wouldn't recommend trying to do
03:49: it first
03:51: um so do whatever manual cleanup you
03:55: have to do and if you think you're going
03:56: to do the bonus
03:57: just write down every step that you're
04:00: doing to manually clean up the data
04:02: that you can later go back and try to
04:04: recreate that
04:05: with code instead of doing it manually
04:09: and i provided a number of resources
04:12: here
04:13: which are going to help you complete the
04:15: bonus
04:18: you may not need all of them it depends
04:20: on
04:21: the ways that you go about doing this
04:25: and this stuff for the most part is
04:27: outside of the scope of the course but i
04:29: think it's
04:30: it's valuable uh to know this because
04:34: in the real world data files are often
04:36: going to be messy and you need to clean
04:37: them up
04:38: and you don't want to have to have this
04:40: manual cleanup step every time
04:42: it's definitely room for error in your
04:45: model if you mess up one of the manual
04:47: clean up steps
04:49: and it's just a lot more work to keep
04:51: doing that manually
04:54: so um then i provided a few
04:58: notes um to help guide you along here
05:02: so you've got daily stock prices and so
05:05: that means when you calculate returns
05:06: from those prices that those
05:08: returns are going to be daily as well
05:13: so in order to work with the cap m
05:17: then you're going to need to take the
05:20: annualized risk free rate from the model
05:22: inputs
05:23: and divide it by 252 to get a daily rate
05:26: then you can run the cap m and then at
05:29: the end you'll have
05:30: a daily uh result at the end
05:34: so you can multiply that by 252 at the
05:37: end
05:40: um i already talked about this with
05:42: modifying
05:43: the data files um
05:47: and then we'll look at the debt
05:49: spreadsheet here in a moment but
05:53: the maturity is is missing in some cases
05:55: or
05:56: um it's it just says a year
05:59: if it just says a year then you can
06:01: assume it's december 31st
06:03: of that year if there's a range of
06:07: coupons given for the debt
06:08: then you can just take the midpoint of
06:10: that range and use that
06:12: as the coupon
06:16: if there is not enough information to
06:18: calculate a bond price
06:19: then you can just use the principal
06:22: amount
06:22: instead of trying to calculate a market
06:25: value
06:26: if there's not enough information to
06:27: calculate a price and it doesn't have a
06:29: principal
06:29: then you can just exclude it
06:34: so let's take a look at these data files
06:38: so first one s p 500 prices
06:41: this is the prices on the s p 500 pulled
06:45: straight from yahoo finance
06:48: and the adjusted close column is going
06:50: to be the one that you would want to use
06:52: to get the price
06:53: including any dividends or share splits
06:55: or things like that
06:58: and next we have the walmart debt
07:00: details
07:02: or let me just quickly hit the uh
07:04: walmart
07:05: uh prices first um
07:08: walmart prices same exact thing as the s
07:12: p
07:12: prices just for walmart instead of the s
07:14: p so again
07:16: the adjusted close column is the one
07:18: that you want to use for the price
07:21: um and then that details
07:24: so this is where what you're going to
07:26: use to calculate the market value
07:28: of debt this is pulled from
07:32: s p capital iq and
07:35: you'll see here first there's actually
07:39: two tables
07:40: in here you'll notice that the top
07:44: table is more recent so just use the top
07:47: table
07:47: you can completely ignore the bottom
07:49: table it should not be included it's old
07:51: data
07:52: so just using the top table
07:56: and then this is where i was talking
07:57: about some of the maturities or just
07:59: years you can assume that's december
08:01: 31st
08:02: and then some of the coupons are
08:04: arranged you can just take the midpoint
08:06: of that range
08:08: um and you're going to want to be
08:10: calculating the market value of these
08:11: instruments to try and get at the market
08:13: value of debt
08:14: for walmart
08:21: and then the income statement
08:24: is provided here so this gives you
08:28: all the income statement items and
08:32: quarterly time periods starting with the
08:35: oldest over here
08:36: and the newest all the way on the right
08:39: so you just want to use the newest data
08:42: you don't need to worry about any of
08:44: this
08:44: old data just the newest is what you
08:48: want to use
08:50: same thing coming to the balance sheet
08:53: all the balance sheet items different
08:55: time periods
08:56: again just use the most recent period
08:59: provided
09:03: so those are the data files that are
09:06: provided
09:07: with the project
09:11: here are the inputs you're going to have
09:13: in the project
09:16: and the grading is similar to
09:20: what it has been in the other projects
09:22: accuracy readability formatting
09:24: following the template and a 5
09:26: bonus
09:29: and i gave you some solutions
09:33: with the baseline inputs so you can
09:36: check to make sure that things are
09:37: working appropriately
09:42: and that's what's involved here i'll
09:44: just quickly go ahead
09:46: and show the
09:49: templates um so here is the
09:53: excel template as the baseline inputs
09:56: and the simulation inputs and then
10:01: if you're doing the combine excel python
10:03: model then you can put
10:06: your baseline whack here all the
10:08: intermediate outputs here
10:10: and if you're doing a pure excel model
10:14: then you want to put all your monte
10:15: carlo results here
10:18: so then you'll have each of the wax
10:21: as well as the other inputs that it's
10:24: associated with
10:24: for all 10 000 iterations there
10:31: and then on the python side this
10:34: is the template so it has the
10:39: the model inputs and it has the
10:41: simulation inputs
10:43: and you want to ultimately set these
10:46: variables with the results from
10:48: your baseline model and after the
10:51: simulation you want to set
10:53: the simulation results into the wac
10:58: mcdf
11:00: um and
11:05: so you like just like the last project
11:08: you can submit pure python for your
11:10: excel or a combination model
11:12: um but if you do the combination model
11:15: the one restriction i'm going to put
11:17: is that then your answer your your you
11:20: should be
11:20: ultimately um
11:23: setting these variables in the python
11:25: template and expect to be
11:27: me grading these variables in the python
11:29: template
11:30: um so
11:34: um but other than that you can use any
11:39: of the two tools that you would like for
11:40: whatever parts of the
11:42: project
11:45: and regardless of whatever setup you
11:47: have
11:48: when i run the model it should run the
11:51: monte carlo simulation
11:52: it should not just be a hard-coded
11:54: simulation
11:56: in there that was run by some means that
11:58: i don't know
12:01: um so yeah that's everything
12:05: that's involved here in the project
12:08: feel free to reach out with any
12:10: questions about it
12:12: and thanks for listening i'll see you
12:14: next time

00:03: hey everyone this is nick dear burtis
00:05: teaching you financial modeling
00:06: today we're going to be introducing the
00:09: fourth project
00:10: in the course the full discounted cash
00:12: flow valuation of a stock
00:16: so this is uh the main model that we've
00:19: been working towards
00:20: in the course that kind of puts together
00:23: everything that we've
00:24: learned um so
00:28: within this project you're going to pick
00:31: any publicly traded
00:33: uh company that you would like and
00:36: you're going to research
00:37: that company and you're going to create
00:40: a full discounted cash flow evaluation
00:42: of that company's stock
00:45: and it's on you to go and find all of
00:48: the data that you're going to use
00:50: in your model so basically um
00:53: i'm not really giving you anything here
00:56: other than guidelines on
00:57: on what needs to be in the project and
00:59: it's on you to go
01:00: and find all the data um
01:04: find the information about the company
01:07: et cetera so um
01:11: this project um is trying to
01:14: transition you out of the structure of
01:17: the course
01:18: and into uh modeling in the real world
01:22: where you're not gonna have the nice
01:24: templates
01:26: or answers provided
01:29: when you're going and building models on
01:31: the job
01:32: or for personal purposes so
01:36: this project is added in that
01:39: unstructured
01:40: direction that you'll face when doing
01:43: modeling outside of the course
01:46: so within this
01:50: model you're going to have basically
01:53: what was
01:54: project three of estimating the whack
01:56: for the stock rated average cost of
01:58: capital
01:59: and then we're adding on additionally
02:02: the
02:03: free cash flow estimation and
02:05: forecasting part of the model
02:08: and i've noted here that you have to
02:10: forecast the
02:11: financial statements not just the
02:14: um the free cash flows directly
02:19: so you can forecast the free cash flows
02:22: directly but that should be as an extra
02:24: check
02:24: not as the main result in your model
02:28: you do need to forecast at least the
02:29: income statement and balance sheet items
02:31: necessary to calculate
02:33: free cash flows in the future
02:37: and then beyond the base model
02:41: you need to have a few other components
02:43: in here
02:45: so what's important in going throughout
02:47: this valuation
02:48: is that this is our first model which
02:52: has had a lot of
02:56: different information that you can
02:58: incorporate
02:59: about the company and so it's a little
03:02: bit subjective
03:03: in how you pick the assumptions
03:07: for the model and it comes down to your
03:10: opinion
03:10: about the company and your research uh
03:14: on the company so um
03:19: even two people that worked on the same
03:21: company could have completely different
03:23: answers here
03:24: and they both could be perfectly valid
03:26: it just comes down to your
03:28: research and opinions about the company
03:30: and so in order to
03:32: um justify the
03:36: assumptions that you're making you're
03:38: going to have to also give
03:40: a written justification of the
03:43: forecast assumptions um so the best way
03:47: to do this is just to have
03:49: kind of a full write-up on the model
03:51: explaining
03:53: just giving a background on the company
03:55: and your findings from the research
03:58: and going through um
04:01: basically the basic structure of the
04:03: model um
04:04: and talking through all the assumptions
04:06: that you made and why
04:08: and what from your research and opinions
04:10: did you draw on
04:12: in order to uh pick the
04:15: assumptions for the forecasts
04:18: so beyond the base model and that write
04:21: up of the model
04:23: then we also want to have the
04:27: extensions um to the model that we've
04:29: seen throughout the course
04:31: um so scenario sensitivity analysis
04:35: monte carlo simulation visualization we
04:38: want to see all of those things coming
04:40: into
04:40: the model so if you're missing any one
04:43: of these
04:44: then that's going to be a hit to the
04:46: grade so make sure that you complete all
04:48: of these components
04:53: and as far as getting the stock price in
04:56: the
04:57: model you also want to use both the
04:59: perpetuity growth
05:01: and exit multiple approaches and look at
05:04: multiple different accent multiples
05:06: for getting that price so you'll really
05:08: have multiple different prices
05:10: coming out of the model and in order to
05:13: get that to a single price
05:16: for the purposes of these extensions you
05:19: can
05:20: either pick whichever one you think is
05:22: more accurate and justify
05:24: why you think it's more accurate or you
05:26: can take a weighted average
05:28: of the other approaches and use that as
05:30: your
05:31: singular estimate of the stock price
05:36: so those are the main things that are
05:39: involved here
05:41: um and uh for the scenario analysis
05:44: you're thinking about states of the
05:46: economy for that
05:48: um sensitivity you're going to want to
05:51: pick whatever you think are the most
05:53: impactful variables in your
05:55: analysis and similarly for the monte
05:58: carlo simulation
05:59: the more variables the better but
06:01: whatever the most impactful ones are
06:03: should be in there
06:07: the grading is a little bit different
06:09: for this project
06:11: because there's no template and so
06:13: there's no template portion
06:15: of the grade and where that has that
06:18: grade has shifted to
06:19: is instead towards the readability so
06:21: for the other projects the readability
06:23: has been 20
06:24: here it's 30 and that shift
06:27: is mainly because this is a larger and
06:30: more complex model than we've built
06:32: in the previous projects and the
06:35: more uh the bigger the model is and the
06:38: more complex it is
06:39: the more important that readability is
06:42: uh
06:42: for someone to understand and work with
06:44: the model so
06:46: putting a greater emphasis on that here
06:50: um and there are no provided solutions
06:54: because
06:55: everyone's working on a different
06:56: company and also
06:58: even people working on the same company
06:59: can have different valid solutions
07:04: and as far as the bonus in this project
07:08: so the bonus is also similarly less
07:10: structured
07:12: so for the bonus it's basically just if
07:14: you do any parts of the model very well
07:16: you can earn a bonus for those parts of
07:18: the model so it could be the
07:20: visualization it could be
07:22: the write up it could be the structure
07:25: of the model
07:26: how it's organized it could be a
07:29: particularly thorough
07:31: analysis of any part of the model um
07:34: so anything that basically impresses me
07:36: that you've done in the model
07:38: is going to be eligible to earn a bonus
07:41: and
07:42: the bonus could go up to five percent of
07:45: the grade
07:48: so that's an overview of this project
07:52: um so i hope that this is enjoyable for
07:54: everybody since you get to
07:56: choose a company which you are
07:58: interested in learning about and
08:00: analyzing
08:01: and go through this whole process and
08:04: this is a really nice
08:06: full built out model to have as you go
08:08: into
08:10: job interviews you can show off this
08:12: model and all the fancy stuff that
08:14: you've done in it all your thoughtful
08:16: analysis into it
08:17: and that's something that could be
08:19: impressive in an interview setting
08:23: um so that's the overview of this
08:26: project
08:27: so feel free to reach out with any
08:30: questions about it
08:31: and thanks for listening see you next
08:35: time