OPEX Part 2

Quick reference

• 00:04 Let's carry on with Operating Expenditure.
• 00:08 Right, so last time out we'd finished off the Assumption section.
• 00:11 We must have put a drop-down box in for the date.
• 00:14 We talked about the method and amounts in growth rates.
• 00:17 Let's get on with Calculations.
• 00:18 So the first thing I need to do is bring in the growth rates so
• 00:22 this is simply going to equal.
• 00:24 Back to the Assumption sheet, this cell here.
• 00:26 So that has to be made an off-sheet reference,
• 00:29 which I've already done the internal reference here.
• 00:33 Now I need a growth rates flag.
• 00:35 Remember the good old Boolean algebra?
• 00:37 So let's have it as a constraint, we'll have it in here.
• 00:40 And I'm going to put in the formula equals open brackets.
• 00:44 This date here made absolute is less than or
• 00:48 equal to the actual data up here,
• 00:52 which will actual make it J dollar 7 times 1.
• 00:57 And copy that across.
• 01:00 So I've got zeros and ones going across here.
• 01:03 So this'll just tell me these amounts for these two periods.
• 01:06 And I'm going to use a percentage for the next rate.
• 01:09 So we can actually even put conditional formatting in here to make that look
• 01:14 nice and pretty.
• 01:15 So first of all, let's just have a dash in here, so to make this comma zero.
• 01:19 And then I'm going to actually say okay, conditional formatting.
• 01:23 We'll have a new rule, and will determine what it's gonna be,
• 01:27 what format only cells that contain cell value.
• 01:30 It's equal to the number one format.
• 01:35 We'll have the number disappear altogether.
• 01:37 So we'll do that good old semi-colon, semi-colon,
• 01:39 semi-colon trick I showed you from the approach of course.
• 01:42 And the fill can be I don't know, let's go for a nice green color shall we?
• 01:46 Click OK.
• 01:48 OK again.
• 01:50 Done.
• 01:50 So we've got this sort of conditional formatting going on.
• 01:54 So next up I'm going to go and
• 01:56 put in my amount of growth rates from my assumptions sheet.
• 01:58 So just bring those in.
• 02:01 Amounts.
• 02:03 Copy it across.
• 02:05 Don't need that far.
• 02:06 Take that, copy it down.
• 02:09 And I would do it normally as an internal reference.
• 02:12 But I'm gonna use a conditional formatting here.
• 02:13 I'm going to gray out cells.
• 02:14 So, I'm going to do it actually slightly differently.
• 02:17 I'm going to use the parameter one.
• 02:19 And then this one needs to be as a percentage as well.
• 02:24 So we've got those in here.
• 02:26 Then I'm going to have conditional formatting.
• 02:29 Now, little trick here.
• 02:30 When you put conditional formatting that's different in two rows.
• 02:32 It's best to actually insert a row to keep them apart to start off with.
• 02:38 So this one is going to be conditional formatted.
• 02:41 So that when this is a zero, it will show.
• 02:43 And when it's a one, it will gray out, right.
• 02:46 So I've got conditional formatting.
• 02:49 New rule, use a formula to determine equals.
• 02:54 The corresponding cell up here, K148, we'll get rid of the dollar signs,
• 02:59 so that as we go across, it'll go L148, M148, etc.
• 03:02 It has to equal 1 for it to be grayed out.
• 03:05 So we go format, go to gray.
• 03:08 The number again, we'll go custom, and we'll gray it out.
• 03:12 So down here you will find the semicolon, semicolon.
• 03:15 You can see all the different ones we've done, but here it is in there.
• 03:19 And click OK, and it's great, that's out.
• 03:22 And similarly here, we want to this to be grayed out when that is 0.
• 03:26 So Alt+O+D is the keyboard shortcut.
• 03:30 New Rule, equals.
• 03:35 This cell, again, we want the dollar signs removed so
• 03:38 when we copy across it will know it has to be equal to zero this time.
• 03:42 Format, Custom.
• 03:45 Again down to the semi-colon stuff.
• 03:49 And the third's gonna be gray again, click OK, OK, apply, OK done.
• 03:56 Now the reason I've put in this blank row is it's a bit of
• 03:59 a glitch in conditional formatting that when you have touching cells
• 04:03 conditional formatting here will somehow rub off down here as well.
• 04:06 That won't happen if you actually kept a moat in there and
• 04:09 I can put it back together afterwards.
• 04:12 That nice and easy?
• 04:14 And then my Opex.
• 04:16 Well, I want to show you with the flags method here.
• 04:19 So I could actually say that if it's not do one thing, if it's one do another.
• 04:24 Well, we're going to use an actual different way of doing it with the flags.
• 04:29 We're gonna say ={1- this flag, so
• 04:33 when that's a 0, I want you to take this number.
• 04:39 Plus, what I want you to do here is then take this number here.
• 04:47 Multiply it by the cell to the left and
• 04:52 then multiply it by one plus the growth rate.
• 04:55 And it's gotta be that cell again.
• 05:00 Closed brackets.
• 05:01 Closed brackets.
• 05:02 Now notice it's gone invisible which is sometimes what happens here is we have to
• 05:05 go back cell style and make it a number again and copy that across.
• 05:11 Then it's all happy.
• 05:13 Brilliantly done.
• 05:14 We've now got our calculations ready.
• 05:16 We can then start to put it all into our control account next time out.

Lesson notes are only available for subscribers.