Separation of Inputs + Calculations

Quick reference

• 00:04 Now that we have looked at the principles of craft,
• 00:07 we can start to look more about some of the ideas we need to consider in
• 00:12 Excel when actually looking at financial modelling.
• 00:16 One of the things we've already mentioned in the ideas of transparency and
• 00:20 flexibility and consistency, is the idea that the separation of inputs,
• 00:24 calculations, and outputs.
• 00:26 This slide should be familiar to you by now.
• 00:31 We put our data on various sheets throughout a workbook.
• 00:35 The calculation order should be that the logic should flow from left to right and
• 00:38 down the page as we read like a book.
• 00:40 Formulas should follow the rule of thumb.
• 00:42 Keep them simple so that it's easy to follow, speeds the calculation time.
• 00:46 But this can not always be strictly applied.
• 00:50 In particular, we should look at separating out inputs,
• 00:54 calculations, and outputs, but why?
• 00:57 What's the reason behind that?
• 01:00 Let me explain and illustrate with an Excel example.
• 01:05 Here's a very basic Excel example which highlights why keeping inputs,
• 01:10 calculations, and outputs separate might be a good thing.
• 01:13 You can see here, I've got three worksheets in this model
• 01:18 cunningly called Inputs, Calculations, and Outputs.
• 01:23 Not particularly exciting, but let me explain how it sort of works.
• 01:28 I've kept it very simple, concentrating just on sales, so again,
• 01:32 you get an idea of what this is about.
• 01:34 And I've got five Periods, Periods 1 to 5.
• 01:38 In row 8, I have the projected sales in dollars for the first period.
• 01:42 And then I'm growing it in row 9 by a growth rate in periods 2 onwards.
• 01:47 Notice the gray cells, in cells K8 to N8 and J9.
• 01:53 That means these cells are deliberately blank.
• 01:57 There's nothing to be put there that won't get used in the model, makes it clearer.
• 02:02 Let's have a look at the calculation sheet.
• 02:04 This surprises a lot of people because you'll seek to
• 02:07 start off within this section I'm going to highlight here.
• 02:11 It's just taken it from the other worksheet.
• 02:14 See, exactly the same.
• 02:16 But this is the whole idea of consistency.
• 02:19 It makes it easier to link the numbers, so
• 02:22 all of these have just been linked in from the input sheet.
• 02:25 And then, people can follow it on a piece of paper.
• 02:28 And that's the idea.
• 02:31 People can tick it off.
• 02:32 Makes it more easily auditable, more transparent.
• 02:35 People will follow it and trust the model accordingly.
• 02:39 That's the whole idea.
• 02:40 You can have your inputs here, so you can send this to someone else to fill it all
• 02:44 out, and it won't affect your calculations.
• 02:46 Keep it separate.
• 02:48 Total sales, nice and easy here.
• 02:51 I can go through and say, equals if this
• 02:56 first period equals Period 1, let's say.
• 03:03 Then we're gonna have to take this number.
• 03:05 Otherwise, I'm going to take the previous period and
• 03:09 multiply it by 1 plus the growth rate.
• 03:12 Now if you don't quite follow this formula at the moment,
• 03:14 that's not the point of this section.
• 03:15 We're going to be talking about this more later.
• 03:18 All I'm doing here is writing a formula that will grow it by
• 03:22 the particular percentage in each period.
• 03:26 Now, notice for my year on year growth,
• 03:29 maybe it will be clearer if it was in bold.
• 03:31 Make it nice and simple.
• 03:33 Total sales, guess what I'm gonna do?
• 03:35 I'm gonna bring this down again.
• 03:37 Even though it was just here.
• 03:38 This might be further down.
• 03:40 And I'm going to refer to it by actually typing in a formula that, again,
• 03:44 I'll explain at another point.
• 03:45 But showing you the idea of making it simpler for people to follow.
• 03:49 Equals, and then, I put in speech marks because it's text.
• 03:52 Row, and then I'll join it up using the concatenate operator,
• 03:56 the ampersand symbol.
• 03:57 This joins things up, it Sellotapes things together.
• 04:00 And I'm gonna use the row function on this so it will return that row.
• 04:06 Now I could have just typed row 11 here.
• 04:09 But if I insert rows here, you see now it says row 14?
• 04:14 It updates, that's the whole point of using a formula here.
• 04:18 And I can make that bold or whatever so
• 04:20 people can understand where these numbers have come from.
• 04:23 They're referred values.
• 04:26 Then I can work out the actual values here.
• 04:29 And I can just put in equals that,
• 04:34 divided by that, minus 1, and grow that.
• 04:40 And yet again, put one of these here, to highlight the fact look,
• 04:44 there's nothing in the first period cause it's just the first period.
• 04:50 How can I have year on year growth in year 1?
• 04:53 The calculations are all here if someone wants to see them.
• 04:56 Why separate from the outputs?
• 04:57 Well, on the output sheet, I should have this equals and
• 05:03 links to here and copy that across.
• 05:06 And I'm gonna take the growth equals links to here and copy that across.
• 05:11 No real formulas in the output sheet.
• 05:13 Everything takes place in the calculation sheet.
• 05:16 So they can look at that if they want to.
• 05:18 The output sheet can then be sent to people with links broken if necessary.
• 05:22 So they can actually look at it and analyze.
• 05:25 The calculations sheet shows what's come from other sheets, and
• 05:28 I've colored it blue.
• 05:29 But that doesn't happen on the output sheet, because, by definition,
• 05:33 everything's coming from another sheet.
• 05:35 But we keep it simple, all the hard work gets done on the calculations sheet.
• 05:39 That's where I'd expect to find all the formulas.
• 05:41 And then users can go and look if necessary.
• 05:43 Output sheets can be sent to third parties who don't need to know how
• 05:47 the calculations are done necessarily.
• 05:49 It maybe they're confidential, and inputs can go to the people who
• 05:53 need to populate them, keeping it nice and separate and simple.
• 05:57 You may agree, that this is a good way to understand the model quite quickly.

Lesson notes are only available for subscribers.