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

You may also like