OPEX Part 3

Quick reference

• 00:03 Let's have one more go at operating expenditure.
• 00:06 We're gonna put the numbers now into the financial statements.
• 00:10 Returning to our Opex and
• 00:12 cash payments calculations, we are looking at completing this now.
• 00:16 We've already done the Amounts in growth rate calculate, which depends on the cell,
• 00:21 I147.
• 00:21 For instance, if I were to change back on the assumption sheet the linked cell,
• 00:26 make it actual throughout and link it, look, it's actual throughout.
• 00:31 Whereas if I go back here, see June 2023 Until June 2023,
• 00:36 the first three periods, it actually puts in 60, 65, 70,
• 00:40 and then grows it by the percentages, 3%, 2%, looking wonderful.
• 00:45 Now we need to put this into the financial statements.
• 00:47 Now something to notice here,
• 00:49 Row 143 here, all expenses are assumed to be paid as they are incurred.
• 00:54 That means PNL equals cash flow Liam, there's a simple trick here then.
• 00:59 We just take the numbers, the Opex in row 153,
• 01:01 we put it into P&L, and we put it into the cash flow, and we're done.
• 01:07 No, we need to keep it consistent, robust, flexible, and transparent.
• 01:12 Also, what may happen is at a last minute somebody might decide there is a timing
• 01:16 difference so we should still put a control account in anyway.
• 01:20 But that's overkill, tough, do it.
• 01:22 It's simple to do it now, nice and simply and consistency comes to our rescue.
• 01:26 Anyway that have to go and do it at the last minute because studies have shown
• 01:30 where you make changes at the last minute, that's where most errors occur.
• 01:34 So, while you've got the luxury of time as you're building it,
• 01:38 put the actual facility in to be able to make this adjustment if you want now.
• 01:43 So let's go find a control account.
• 01:45 And hey, we'll go for a consistent layout so we can actually build on that.
• 01:50 What's in the cost of goods sold.
• 01:53 So I want to go back to the cost of goods sold section here.
• 01:57 Here's in 41 I'm gonna take from here, working capital Down to that one,
• 02:02 I'll just copy the whole lot, Ctrl C, and take it down here.
• 02:09 And put it in cell D 155, bang.
• 02:13 That looks horrible, but it was zero.
• 02:16 Actually saved days payable, so let's go find that, days payable.
• 02:21 Anything else that needs changing That's not cogs anymore that's going to be opex,
• 02:27 so we'll just take that make that opex, looking good.
• 02:33 Okay now days payable here what's this linking to, a blank cell, that's
• 02:38 right we don't have any days payable assumptions for operating expenditure.
• 02:42 Let's scroll back up here.
• 02:45 Along the assumption sheets there are no days payable,
• 02:49 it's all expenses are assumed to be paid as they are incurred.
• 02:52 This goodbye, you are the weakest link, and
• 02:55 we're going to self-style instead is empty having deleted the contents.
• 02:59 So, we have 0 days payable, but the reason is, if we change our mind the last minute,
• 03:04 you can go possibly in assumptions.
• 03:06 Link it to and it will flow through, on the days and
• 03:10 the period is indeed linking to the days in period.
• 03:14 The closing payables, isn't calculating on the right basis.
• 03:19 It's doing the calculation we want, which is J153, the operating
• 03:23 expenditure multiply by the days payable divided by days in the period.
• 03:27 In fact the days payable is zero.
• 03:29 Does not matter.
• 03:31 And then all these, this is wrong.
• 03:33 Closing variables, that's linking to the opening balance sheet accounts payable.
• 03:37 We've already included that in cost of goods sold, that would be a double count.
• 03:42 When we unzero the opening balance sheet, we're gonna get an error.
• 03:45 So we have to delete that so we don't double count it.
• 03:48 I'll make it a cell start with an empty cell instead.
• 03:51 And then here, does this work?
• 03:54 Yes that's linking to the right one.
• 03:55 Does this work?
• 03:56 This is linking to J153, where's J153?
• 03:59 And that's fine.
• 04:01 Does cash payments work?
• 04:03 It's equal to the total minus the sum of the two rows above, that's fine.
• 04:07 And does this row work?
• 04:09 Yes it's linking to closing payables.
• 04:11 All good.
• 04:12 We've got our control account done.
• 04:14 Thank goodness for consistency.
• 04:15 I actually thought about the spacing.
• 04:17 I copied it down and it all worked brilliantly as a result.
• 04:22 This is what I'm talking about.
• 04:24 If you think about the spacing that we set in the preparation course, and
• 04:27 you apply it consistently, it rewards you.
• 04:30 Not only does it make life easier for the end user, it also makes it easier for you,
• 04:33 because you can copy it.
• 04:35 Now when you copy it, if there's any mistake, you'll see there's a problem, and
• 04:38 you can sort it out there and then unfix the original as well.
• 04:41 It's a win-win situation Now we've got four lines in our Control account,
• 04:46 opening payables, opex cash payments and closing payables.
• 04:50 Open payables is just to restate the previous period's closing payables, so as
• 04:53 always number lines minus 1 is 3, that's how many calculations we need to put in.
• 04:59 Opex goes into the income statement as a negative number, don't forget.
• 05:03 Cash payments goes into the cashflow statement, that's fine.
• 05:06 And when we do that, we'll find it balances but
• 05:08 we still have to put the closing payable in jut to make sure that if
• 05:12 a number changes in days payable in the 157, it will still work.
• 05:16 So let's go do it.
• 05:17 To the income statement then.
• 05:18 Your operating expenditure here in row 18 is equal minus
• 05:22 Back to the calculations so J166, a couple across.
• 05:26 As soon as I do that the error goes off which shows it's linking.
• 05:29 That's fine, that's what we expect.
• 05:32 Now we go to the cash flows statement it's
• 05:35 into the indirect cash payments equals back to the calculation sheet.
• 05:41 Click on this, copy this across on watch out.
• 05:44 When you do that, it now says it's finds its balancing.
• 05:47 Doesn't matter, we've still got to go the Balance Sheet, and
• 05:51 put through our accounts payable.
• 05:53 Make sure you go in here, and you add, even though it's 0 you're adding
• 05:57 at the moment, remember it's the three calculations we must do.
• 05:59 Back to the Calculations sheet.
• 06:01 The closing balance, 168, and copy that across.
• 06:05 It still has the effect of it all works, that's fine.
• 06:10 We've now put in our three calculations, and
• 06:12 we've finished off operating expenditure.
• 06:15 What's next?

Lesson notes are only available for subscribers.