Four Types of Inputs

Quick reference

• 00:04 If we'll carry on with the revenue calculation, it's time for
• 00:08 an interlude because I've just alluded to two of the four types of inputs that
• 00:13 you can actually do when creating a model.
• 00:16 And so I wanted to actually talk about all four,
• 00:18 because I am going to show them during these examples.
• 00:21 No matter how complicated a model gets,
• 00:24 it doesn't matter how sophisticated you go, data entry will always
• 00:28 revert to combinations of one of the following four types.
• 00:33 You'll have either an amounts method, which is just amounts going across,
• 00:36 which we saw.
• 00:37 Percentages, where's something's a percentage of something else,
• 00:41 an amount and growth rate method which we saw or
• 00:44 a combination and I'll explain what that is using the following Excel example.
• 00:50 Let's take a look at the following Excel example.
• 00:54 Here, I have got the four types.
• 00:57 In row four, I have here my amounts.
• 01:02 Nothing difficult here, I just type in my actual amounts.
• 01:06 Very useful for getting data from somewhere else,
• 01:09 we can just go copy paste special values.
• 01:12 Also very useful for breaking links when it's linking to another workbook for
• 01:17 instance.
• 01:17 Percentages is used when you actually want to link
• 01:21 the calculations to something else.
• 01:24 So it might be cost of good sold,
• 01:26 could be calculated as a percentage of revenue for example.
• 01:30 Now, amount and
• 01:32 growth rate method, now notice I really overpronounce the letter t there because
• 01:37 the combinations method is sometimes known as the amounts and growth rates method.
• 01:42 Is a case when the first period, you actually have a number and after that,
• 01:46 you grow it by a percentage.
• 01:48 We've already seen that in the last revenue example.
• 01:50 So here I've got $100, $104, $109.20 using a formula very similar to
• 01:55 the one I used last time except I don't have a period counter here.
• 02:00 So in this case,
• 02:01 I'm checking to see if cell C10, that is the cell to the left, it's blank.
• 02:05 If it is, it picks up the input.
• 02:07 Otherwise, it takes the previous cell and grows it by one plus the growth rate.
• 02:11 The trap in this one is to always make sure you take the percentage in the same
• 02:15 column as the formula.
• 02:16 People sometimes go for the period to the right.
• 02:19 Don't do that and you'll be fine.
• 02:22 And finally, I've got two combinations of combinations here.
• 02:25 Yes, I said that deliberately.
• 02:27 In this first one, the first two rows is the amount and growth rates method again.
• 02:32 But what I've done here is I've put an override in.
• 02:35 Now, where that's useful is when you're doing an initial budget and
• 02:38 then you either do a reforecast or you actually put in your actuals.
• 02:43 And what that will do is, it will calculate using the amount of growth rates
• 02:48 unless you type in a number and it will use that instead.
• 02:51 So the first two periods that goes 100, 104, just like this one does but
• 02:56 then it goes 167 because I've got this number here.
• 02:59 If I delete that, you'll see it reverts 109.20, 115.75 of these two.
• 03:05 Here, the 208 comes in and then the following period, it grows the 208 by 8%.
• 03:11 Formula looks a bit yuck.
• 03:14 What it's saying is, if D14 and so if we just edit this.
• 03:18 D14 is the override number.
• 03:20 If it's not blank, put the D14 number in.
• 03:24 So, put the override number in if that is a value in that row.
• 03:28 Otherwise, the end function is just to actually take the numerical value of C15.
• 03:34 Cell C15 is simply the cell to the left.
• 03:38 If that cell is 0, then take the actual amount in row 12, that's the 100.
• 03:44 Otherwise, take the cell to the left and grow it by one plus the growth rate.
• 03:47 Very similar to the amount and growth rates method.
• 03:50 So that's one simple combination.
• 03:53 Another one, I used data validation to actually have a drop down box to have
• 03:57 the numbers 2 to 7 based on the fact we've got the numbers 1 to 6 here.
• 04:02 I'm going to use the amount until this particular period.
• 04:06 So you see if I select them out until period three, you can put an amount in
• 04:10 the first two periods and then use percentages thereafter.
• 04:13 That's why there can't be a number one,
• 04:15 because I've got to have an amount in the first place.
• 04:17 And I've also that's number seven.
• 04:19 So I can just actually have amounts throughout.
• 04:22 And all that's doing is saying okay,
• 04:24 take the amount if the counter is strictly less than the amount until.
• 04:30 Otherwise, take the cell to the left and grow it by one plus the percentage.
• 04:35 Notice the very good use of conditional formatting here.
• 04:38 You can make more and more sophisticated combinations as you go.
• 04:42 But truthfully, these are all the combinations you need to do, any type of
• 04:46 input you want in a model, no matter how sophisticated or complex it might get.
• 04:51 All right, interlude has now been officially finished.
• 04:56 Let's go back and complete the revenue example, shall we?

Lesson notes are only available for subscribers.