Modeling Revenue Part 1

Quick reference

• 00:04 So, having had a bit of a background chat about revenue.
• 00:08 And the reason why it's first cab off the rank.
• 00:14 Let's go straight into the calculations.
• 00:18 Time to get going on our revenue calculations, then, and
• 00:21 to do that, I make a slight tweak to that model we've been building up.
• 00:25 So I've now called it Revenue Start, you'll see it's model 05.
• 00:28 Let me show you what I've done.
• 00:30 If I go to the Navigator sheet here,
• 00:33 you'll see that I've added two sheets, assumptions and calculations.
• 00:38 Now, they've basically just been copied in again.
• 00:41 Like I did before.
• 00:42 So you'll see the calculation sheet.
• 00:43 Nothing too special with that.
• 00:44 I've shown you how to do that already.
• 00:47 The assumption sheet, on the other hand, I've done that as well, but
• 00:50 I've also put some assumptions in.
• 00:52 Now, my assumptions are simple.
• 00:56 In a real model, they'd be more complex.
• 00:58 But just think about the concepts I'm trying to get across.
• 01:01 Not necessarily the fact, it's very simple.
• 01:04 Because I'm trying to get the ideas across, because you always step it out and
• 01:08 everything becomes simple when you step it out.
• 01:10 So, these are the assumptions I've used.
• 01:13 The projectiles in rows 17, I’ve had inputs and not to all just numbers.
• 01:18 We call these, the amounts method.
• 01:21 Every single input is an amount.
• 01:23 This is great for when you’re linking to another workbook and then breaking links,
• 01:28 things like that.
• 01:29 Rows 18 and 19, however, are combined.
• 01:31 This is trying to get me my unit price into stages.
• 01:34 First of all, I had my unit price in the first period and
• 01:37 then afterwards I grow it by a percentage.
• 01:39 This is known as the amount and growth rates method, the first period and
• 01:45 then you grow it by a percentage there after.
• 01:47 The working capital we're not to do this time but is there for completeness.
• 01:53 These Assumptions are also amounts, but more on that later.
• 01:57 Now, notice on the calculation sheet I've called it calculations
• 02:01 after I expressly said in the preparation course don't call a sheet calculations.
• 02:06 Because you need to think about the content and purpose, and give the end user
• 02:10 a chance of knowing where to find a calculation rather than have thousands and
• 02:14 thousands of lines, because you just did every calculation on this sheet.
• 02:18 This is a training model.
• 02:19 It's very, very simple, so it's quite small.
• 02:22 So it's okay but what would you actually do
• 02:26 in a bigger model is break up the calculations into different sheets.
• 02:29 So it's easier to manage, easier to navigate, easier to understand.
• 02:33 You might collect revenue calculations, working capital calculations,
• 02:37 death calculations, et cetera, et cetera.
• 02:40 i've got them in one place.
• 02:42 You might be surprise the first thing I'm gonna do in my calculation sheet is
• 02:46 type these assumptions here, go CTRL+C to copy and
• 02:51 I'm just gonna go here and paste them in V.
• 02:55 Bang paste now we shouldn't turn things in.
• 02:58 I'm going to make this link back to here and press Enter.
• 03:03 I'll click this.
• 03:05 Highlight everything that needs to actually be linked back to the other sheet
• 03:09 for consistency so they'll all be in the right place.
• 03:13 And then, I can go paste special formulas either by going to
• 03:17 the Home > Paste > Formulas or keyboard cut Alt+PS, and
• 03:22 I'm going to go down to Formulas and that's press Enter.
• 03:26 That's Alt+E+S+F, bang, done.
• 03:30 That way, everything's linked.
• 03:31 It's more efficient file size wise.
• 03:34 But these now look they're assumptions and they shouldn't be.
• 03:38 We've only enter assumptions once.
• 03:40 So we need to actually highlight to the end user that these cells
• 03:43 are actually internal references.
• 03:45 I do that by going to the home tab,
• 03:47 changing the sub style to internal reference.
• 03:50 If this was printed out on a piece of paper,
• 03:52 you would say I see this is actually linking to another work Worksheet got it.
• 03:59 Right.
• 04:00 So in order to work out my revenue, I'm going to have to calculate
• 04:03 my projected sales and multiply it by my unit price.
• 04:06 But I haven't got my unit price yet,
• 04:07 I've only got the unit price of the first proven the inflation.
• 04:10 So I'm going to first of all work out what my price per unit is.
• 04:16 Now, that's got to be the same currency as this always, so I'm just gonna make
• 04:21 that linked to that and I can then style that as a unit in the usual way.
• 04:28 And that's going to be the following formula.
• 04:30 If it's the first period, it's gonna be the 15, otherwise,
• 04:33 it's gonna take the previous period, which will be the cell to the left in this case,
• 04:36 cell I-21, and multiply it by 1 plus the percentage.
• 04:40 So that will be equals if, the counter, J equals 1, you must be getting used to that
• 04:47 by now is the first period we're going to take that number, make that absolute.
• 04:51 Otherwise, take the previous period and grow it by one plus the actual
• 04:56 inflation rate and I'm deliberately linking to that first cell because it
• 05:00 won't put that in the first period because that is going to link to the unit price.
• 05:05 Up and across though looks horrible so I need to go in here and
• 05:08 go Home > Cell Styles, and I'm actually going to make it instead a number, so
• 05:13 it looks better.
• 05:14 Now a common mistake that gets made with the amount and growth rates method,
• 05:18 is when people do this calculation, they think I should have be linking to this
• 05:22 blank cell I'll link it instead to this cell here and I'll copy that across.
• 05:27 The problem is you can create a circular reference this way and
• 05:30 also you're linking to the wrong periods.
• 05:33 You can spot this quite easily because the last two periods will always be identical
• 05:37 because this period will be linking to a blank cell.
• 05:39 So just watch out for that.
• 05:40 Always scroll across and make sure your calculations make sure.
• 05:43 So don't do it that way.
• 05:45 Make sure it's linking here to this cell.
• 05:48 Now I've got the price per unit.
• 05:49 I can work out my revenue.
• 05:53 This should be in currency.
• 05:56 So that's currency.
• 05:57 Tap that.
• 05:58 Make that a unit as well.
• 05:59 And this is gonna be nice and easy once I've done this.
• 06:04 It's simply going to be my projected sales multiplied by my unit price.
• 06:10 And then I'm going to style this as well as an actual number format.
• 06:16 So when I copy it across, it looks wonderful.
• 06:18 Brilliant, I've got it going with the revenue calculations.
• 06:22 More next time.

Lesson notes are only available for subscribers.