- 00:00:01 welcome back in this video we will
- 00:00:04 finally talk about business projects so
- 00:00:07 we will talk about balance sheets income
- 00:00:09 statements cash flows the work the capam
- 00:00:12 no no worries
- 00:00:14 we want the only thing I want to show
- 00:00:17 you is a function named self referencing
- 00:00:21 cell and this might be really helpful if
- 00:00:24 you have to work or create financial
- 00:00:25 models in this video we will not create
- 00:00:28 a highly sophisticated model we will
- 00:00:31 simply create an income statement well
- 00:00:33 to be honest with only three lines
- 00:00:35 revenue cost and profit and then think
- 00:00:39 about the planning of this lines for the
- 00:00:41 next years and for that we have three
- 00:00:43 different scenarios and in connection
- 00:00:46 with two scenarios a cell for instance L
- 00:00:49 might be really helpful sometimes this
- 00:00:52 is it actually let's start now
- 00:00:57 so here I am in an empty excel sheet now
- 00:01:01 this means we will build everything
- 00:01:04 together from scratch now I said that we
- 00:01:06 want to plan our income statement and
- 00:01:09 for that we have three different
- 00:01:11 scenarios or cases now let's start with
- 00:01:14 the case selection right here then so we
- 00:01:17 could write case right there and then
- 00:01:21 right selected right here for example
- 00:01:23 and now could format this cell for
- 00:01:27 example with some border and write one
- 00:01:30 into this so we could write 1 2 3 here
- 00:01:33 in the end could select a specific case
- 00:01:36 let's maybe also format that a little
- 00:01:38 bit that it looks a little bit more
- 00:01:40 beautiful in the end make it white maybe
- 00:01:43 and bold like this now what else do we
- 00:01:48 need well we could again write case
- 00:01:50 right here and total profit now why am I
- 00:01:56 writing this let me increase the width
- 00:01:59 and also format that maybe orange right
- 00:02:02 now now the reason is that we will see
- 00:02:05 case 1 2 3 right here and in this area I
- 00:02:11 want to see the total profit of all the
- 00:02:15 planning periods sort of some of that
- 00:02:17 profit for the selected case so this has
- 00:02:21 not a lot to do with valuation or
- 00:02:23 something and we will also not consider
- 00:02:25 things like discounting I'm aware of
- 00:02:27 that
- 00:02:28 it's just an example how you could use
- 00:02:30 the self referencing cell just keep the
- 00:02:33 demand when we build the rest right now
- 00:02:35 now we cannot build that total profit
- 00:02:38 right now because we don't have an
- 00:02:40 income statement yet now let's create
- 00:02:42 this so we could build that here maybe
- 00:02:45 and name it business plan so it sounds
- 00:02:49 more important and also format it a
- 00:02:51 little bit you see I just want to keep
- 00:02:53 this struct UPS I just want to keep this
- 00:02:56 structured and make it bold also and now
- 00:03:00 we need the years that should be
- 00:03:01 included in that business plan well and
- 00:03:04 this could be 2017 and if in all right
- 00:03:08 equals the previous year plus 1
- 00:03:10 and direct it to the right then we have
- 00:03:13 our planning right here and we could
- 00:03:16 also select that and make it bold maybe
- 00:03:18 like this now what else do we need
- 00:03:22 well the items in our income statement
- 00:03:24 and this could be for revenue cost and
- 00:03:30 the profit as you can see if not a
- 00:03:33 really sophisticated income statement
- 00:03:35 but that's not a problem right here now
- 00:03:38 let's assume that we have a revenue of
- 00:03:40 100,000 in 2017 cost of 60,000 and the
- 00:03:45 profit well this should be then the sum
- 00:03:48 of these two items so 40,000 so that's
- 00:03:52 2017 let's say we know that this is
- 00:03:54 going to be the case but what about the
- 00:03:56 planning periods well right here we need
- 00:03:59 to make assumptions for both revenue and
- 00:04:02 cost so for our revenue assumptions this
- 00:04:07 we could say we have case 1 case 2 and
- 00:04:14 case free so the cases we created up
- 00:04:17 here and that we will select in this
- 00:04:20 cell so C 3 now we can do the same thing
- 00:04:24 for our cost let's say cost as
- 00:04:26 percentage of revenue and we can simply
- 00:04:30 link that to our case 1 2 3 and copy
- 00:04:33 down because it's going to be the same
- 00:04:35 in the end now what else can we do we
- 00:04:39 can format these cells first to make
- 00:04:42 clear that these ourselves may be as
- 00:04:45 presented that these ourselves that are
- 00:04:48 kind of inputs for us where we will make
- 00:04:50 our assumptions and now we have to make
- 00:04:53 some assumptions well first I should be
- 00:04:56 more precise with the name right here
- 00:04:58 and it may be named that revenue growth
- 00:05:00 assumptions because now it's clearer
- 00:05:03 what you want to do right here we want
- 00:05:05 to specify growth rates for our revenue
- 00:05:07 now this could be in case one let's say
- 00:05:10 the optimistic scenario so the great
- 00:05:12 case where we say everything is going to
- 00:05:14 be awesome
- 00:05:15 this could be 10 percent growth in 2018
- 00:05:18 15 in 19 and 20 percent in 2020 so great
- 00:05:24 k-stew could be the more conservative
- 00:05:27 case let's say we have a growth of 8% in
- 00:05:31 the first year 10% in the second year
- 00:05:33 and 12% in the third year all the grade
- 00:05:38 but not as good as case one in k3 this
- 00:05:42 could be the worst case let's say we
- 00:05:44 only have six percent in the first year
- 00:05:46 four in the second and only two in the
- 00:05:48 last year so still growing but not a lot
- 00:05:51 compared to the other cases so these are
- 00:05:53 the assumptions for the revenue growth
- 00:05:55 now what about the cost as percentage of
- 00:05:58 revenue as you can see right here at the
- 00:06:00 moment we have a ratio of sixty percent
- 00:06:03 so 60,000 compared to 100,000 third year
- 00:06:06 let's maybe say that in the best-case
- 00:06:09 case one this ratio decreases so our
- 00:06:12 costs decrease by that so we could say
- 00:06:15 we have cost of fifty five percent in
- 00:06:17 2018
- 00:06:17 50 percent in 2019 and forty five
- 00:06:22 percent in 2020 in case two so the base
- 00:06:26 case so the normal scenario we could
- 00:06:29 also say that we have fifty five percent
- 00:06:31 cost in 2018 but then well we don't have
- 00:06:35 a big decrease we only have 52 percent
- 00:06:38 in 2019 and 50 percent in 2020
- 00:06:41 and in case three or worst-case scenario
- 00:06:44 well the cost are even increasing to
- 00:06:47 let's say 58 percent in 2007 and 2018 to
- 00:06:51 60 percent in 2019 and to 62 percent in
- 00:06:55 2020 so this is it this is now what we
- 00:07:00 did with our assumptions the only thing
- 00:07:02 we have to do right now is we have to
- 00:07:04 incorporate these assumptions in the
- 00:07:07 income statement right here now how can
- 00:07:10 we do this well we have different ways
- 00:07:13 to do it of course but I will use a
- 00:07:15 choose function if you're not sure how
- 00:07:18 the choose function generally works and
- 00:07:21 how you could use it when comparing it
- 00:07:23 to is just take a look at this video
- 00:07:25 in all other cases or if you simply want
- 00:07:28 to see it right now just stay here and
- 00:07:30 create it together with me so we will
- 00:07:33 now select the revenue in 2018 well and
- 00:07:37 what
- 00:07:37 should that be it should simply be
- 00:07:39 equals choose right so we will select
- 00:07:44 now cell c3 as the first value in our
- 00:07:49 syntax so depending on what is displayed
- 00:07:52 right here we now have to specify what
- 00:07:55 should be calculated this means if c3 is
- 00:08:00 equal to 1 which is the case right now
- 00:08:02 by the way then value 1 so again this
- 00:08:06 means value 1 means if this is equal to
- 00:08:08 1 then we want to calculate 100,000
- 00:08:13 multiplied by open bracket 1 plus the
- 00:08:17 growth rate right because this should be
- 00:08:20 the value the revenue increases when
- 00:08:23 comparing it to the previous year now we
- 00:08:25 can close the bracket write a comment
- 00:08:28 and make sure that we fix c3 because
- 00:08:31 this should always refer to that cell
- 00:08:33 and now we can simply copy that once
- 00:08:37 twice and delete a loss come up and now
- 00:08:41 for the second argument value 2 so right
- 00:08:44 here we only have to change D 14 to P 15
- 00:08:49 right here like this and like that
- 00:08:52 because this should be case 2 when we
- 00:08:55 write 2 into cell c3 and the last thing
- 00:08:58 we should change is D 14 so for our
- 00:09:01 value 3 it's okay 3 this should be equal
- 00:09:04 to D 60ml let's eat that and I think
- 00:09:09 with that we should be fine let's see
- 00:09:11 press ENTER and now we forgot a bracket
- 00:09:15 but that's automatically corrected this
- 00:09:16 is fine and if we now drag that formula
- 00:09:19 to the right and copy it then we see it
- 00:09:23 that this worked
- 00:09:24 it can also like a different case so
- 00:09:26 case two for example we see that we have
- 00:09:29 different values because now we have
- 00:09:32 this Grove assumption in our revenue
- 00:09:35 line the same thing is true for K 3
- 00:09:38 which will not incorporate the third
- 00:09:40 case and if we go back to 1 then we have
- 00:09:44 our best case scenario now that's the
- 00:09:47 revenue but what about the cost well we
- 00:09:50 have to apply something
- 00:09:51 actually now let's select cell e 10 now
- 00:09:55 and write equals choose sort of same
- 00:09:59 logic we again select cell c3 and six
- 00:10:02 six f4 now in scenario 1 we simply want
- 00:10:06 to calculate minus because it should be
- 00:10:09 a negative value as you can see right
- 00:10:11 here that's why I write this minus right
- 00:10:14 now so – the revenue times case 1 in our
- 00:10:22 cost assumptions down here now we can
- 00:10:26 again write a comma select the formula
- 00:10:29 copy twice we leave the last command and
- 00:10:33 now simply go to our value 2 right here
- 00:10:36 and replace the 19 with D 20 right here
- 00:10:41 then do this like that and now do the
- 00:10:46 same thing for value free so our third
- 00:10:49 case this means you have to replace the
- 00:10:51 19 with D 21 right here now this should
- 00:10:56 be fine
- 00:10:57 let's now close the bracket and not
- 00:10:58 forget it again and if we press ENTER
- 00:11:00 and drag that formula to the right now
- 00:11:04 then we can see that we now also have
- 00:11:07 our assumption for the cost line for
- 00:11:10 case 1 again if I changed it right here
- 00:11:13 to case 2 then both items change so
- 00:11:16 revenue cost and the same thing is true
- 00:11:18 for case free like this now the only
- 00:11:23 thing we have to do right now is we
- 00:11:24 simply have to take the profit line
- 00:11:26 right here and copy the formula to the
- 00:11:29 right like this well and now we are
- 00:11:33 finally at the point though we will need
- 00:11:35 these cells reference himself and why
- 00:11:39 would we need that well let's think back
- 00:11:42 about that total profit cells right here
- 00:11:44 what basically should be displayed right
- 00:11:47 here is this equals sum of the profit
- 00:11:52 like that as I said we forget about
- 00:11:55 discounting and many other topics right
- 00:11:58 here but that's not the point the point
- 00:12:00 is that if we fix now the rows right
- 00:12:03 here
- 00:12:04 and now copy the formula down well we
- 00:12:08 have the same value in here for all
- 00:12:10 three cases now of course I can change
- 00:12:12 the case now so case one case two okay
- 00:12:16 three and back to one but the problem is
- 00:12:19 that this shouldn't be like that
- 00:12:20 what should be displayed right here are
- 00:12:23 the different profit or the some of the
- 00:12:25 different profits for the selected cases
- 00:12:27 and this is where we can use such as
- 00:12:30 self referencing cell now how can we do
- 00:12:33 that let's simply apply it then to
- 00:12:34 understand it let's select the first
- 00:12:36 case right here if we now go into the
- 00:12:39 formula and right if this if this so the
- 00:12:46 selected case is equal to the case you
- 00:12:51 want to display or right here
- 00:12:52 so the first case and write a comment of
- 00:12:55 course and fix that again well if this
- 00:12:59 is equal to that then yeah of course
- 00:13:02 please give me back to some that we can
- 00:13:04 see right here but if that's not the
- 00:13:07 case so if we select a different case
- 00:13:10 well then simply refer to the value that
- 00:13:14 is already in that cell so you write the
- 00:13:17 formula in cell f3 and in the case that
- 00:13:21 the selected case is not equal to the
- 00:13:24 case of cell f3 so case one then refer
- 00:13:29 to the cell that includes the formula
- 00:13:31 sound strange but let's try it so if you
- 00:13:34 press ENTER now again I forgot the
- 00:13:36 brackets for that now we got that let me
- 00:13:40 now change the case so let's not change
- 00:13:42 case two and what we get right here is a
- 00:13:46 circular reference running and that's
- 00:13:49 one of the things that have to be aware
- 00:13:51 of when you create such a self
- 00:13:53 referencing cell because of course you
- 00:13:55 create a circle right here in that case
- 00:13:57 it's not a problem you only have to make
- 00:14:00 sure two things the first thing is to
- 00:14:02 press ok right here and to see that down
- 00:14:06 here we have a message that we have that
- 00:14:09 circular reference if you don't want
- 00:14:12 that because you know that it's in here
- 00:14:14 you can simply go to file now go to
- 00:14:17 options
- 00:14:19 formulas right here and now enable the
- 00:14:23 iterative calculation if you now press
- 00:14:26 ok you can see if that this warning is
- 00:14:30 not appearing any longer because in
- 00:14:32 other that iterative calculation
- 00:14:34 activated however the problem is if this
- 00:14:37 is a big financial model and you do
- 00:14:39 something like this it's always
- 00:14:41 important to note somewhere that you
- 00:14:43 created that circular reference because
- 00:14:45 otherwise people might wonder what you
- 00:14:47 did right there and in the end they
- 00:14:49 might unable the iterative calculation
- 00:14:51 and find the circles in here so what we
- 00:14:54 could do is we could simply write here
- 00:14:56 cells referencing cell and copy that
- 00:15:01 down for the other two because we will
- 00:15:03 add that of course for the remaining two
- 00:15:04 cells but before we do that let's see
- 00:15:07 what happens now so we created that cell
- 00:15:10 referencing cell right here and if I now
- 00:15:13 select case one then all three cases are
- 00:15:17 the same but if I now select case two
- 00:15:21 then only the other two cells change so
- 00:15:25 the ones where we didn't implement that
- 00:15:26 self referencing cell at the moment and
- 00:15:29 why this is happening once again because
- 00:15:32 in this case we only get a recalculation
- 00:15:35 of the formula if our if condition is
- 00:15:38 true so if our selected case is equal to
- 00:15:41 the case that this cell refers to so
- 00:15:44 case one in all other cases the
- 00:15:47 previously calculated value so in our
- 00:15:49 case the two three six two four zero
- 00:15:52 will simply remainder because the cell
- 00:15:55 retrieve the value that's already in the
- 00:15:57 cell now if we copy this formula down
- 00:16:00 now like this then you see that this
- 00:16:05 looks kind of strange because case one
- 00:16:09 in case three shoulders n value so
- 00:16:11 something seems to be wrong and that's
- 00:16:13 an important thing to make sure that
- 00:16:16 this self referencing cell works
- 00:16:18 correctly you have to recalculate it
- 00:16:21 every time you make a change or in this
- 00:16:24 case when you created formula so if we
- 00:16:26 now select case 1 case 2 and case
- 00:16:31 you can see that maybe we should change
- 00:16:34 that first you can see that now we have
- 00:16:36 different values for all three cases so
- 00:16:40 this is fine and what you can do right
- 00:16:42 now of course is you can select a
- 00:16:44 specific case let's say case – in our
- 00:16:46 case which is fine and now think about
- 00:16:48 your assumptions in your model you could
- 00:16:50 say that you would assume that the
- 00:16:53 revenue growth in the last year in case
- 00:16:54 two is not first percent but maybe
- 00:16:57 fifteen percent then you simply change
- 00:16:59 that like this and the value
- 00:17:02 automatically updates but this is only
- 00:17:05 true because we selected case to know as
- 00:17:07 I just said if we change to growth
- 00:17:10 expectations in case three let's say we
- 00:17:12 have ten percent in the last year now so
- 00:17:16 we change it to ten but nothing changed
- 00:17:18 right here but if we click right here
- 00:17:19 and now select k3 like this then it
- 00:17:24 updates and that's it actually that's
- 00:17:27 what I want to show you of course this
- 00:17:30 is a really really simple and small
- 00:17:32 example but this is a function that you
- 00:17:35 can also apply to bigger financial
- 00:17:37 models only be careful about the
- 00:17:41 functionalities or about the way the
- 00:17:42 formula works now this means that we
- 00:17:45 have that circular reference so you have
- 00:17:47 to make sure to enable the iterative
- 00:17:49 calculation and to leave a note
- 00:17:51 somewhere to make sure you and other
- 00:17:54 people see and remember that
- 00:17:56 self-referencing cells and that formula
- 00:17:58 you created and additionally make sure
- 00:18:01 that each time you change something in
- 00:18:03 your case you have to reselect it right
- 00:18:06 here in our case this is it actually I
- 00:18:09 hope this was interesting for you and
- 00:18:11 that you might be able to apply it in
- 00:18:13 your own financial models so thanks for
- 00:18:16 watching and see you in the next videos
- 00:18:18 bye bye