Locked lesson.
About this lesson
How to design your model so that it separates the inputs and calculations.
Exercise files
Download this lesson’s related exercise files.
Separation of Inputs + Calculations.xlsx12.6 KB Separation of Inputs + Calculations - Solution.xlsx
13 KB
Quick reference
Separation of Inputs + Calculations
Understand the need for inputs, calculations and outputs to be on separate sheets in a workbook.
When to use
When building a financial model, developers need to ensure that inputs, calculations and outputs and kept separate from each other in the workbook.
Instructions
- Inputs, calculations and outputs should all be kept separate when developing a financial model.
- The inputs sheet can be sent to the person who will enter the input data.
- The calculation sheet will have cells linking to the input sheet.
- This sheet is where all the hard work is done.
- The output sheet is generally for third parties or managers to be used for analysis.
- The output sheet links directly from the calculation sheet. No formulas are created in this sheet.
Calculation Order:
- Logic should flow
- From left to right
- From top to bottom
- From front to back
- Why:
- Model is easier to follow
- Speeds calculation time
- But this cannot always be strictly applied
- 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.