Coding

Excel – Nice to know – SELF REFERENCING CELLS

  • 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