Locked lesson.
About this lesson
How to calculate the OPEX Control Account and input into the Financial Statements.
Exercise files
Download this lesson’s related exercise files.
OPEX Part 3.xlsm103.6 KB OPEX Part 3 - Solution.xlsm
106.8 KB
Quick reference
OPEX Part 3
Understand OPEX.
When to use
When constructing a basic financial model.
Instructions
- The next step in working out Operating Expenditure (OPEX) will be to calculate the control account.
- It is still necessary to enter a control account for Opex even though all expenses are assumed to be paid as incurred. This will make it easier in future in case anything changes.
- The working capital and the control account can be copied and pasted from the COGS section.
- Bring in the ‘Days Payable’ in cell E157 =E53
- Highlight cells J157:N157 and style this as ‘Empty’
- You will notice that the calculations will not need to be performed for the control account due to copying and pasting the same format as the previous control accounts.
- Once the control account has been calculated the balances can be transferred into the Financial Statements.
- Transfer ‘Opex’ as a negative number into the Income Statement:
=-Calculations!J166
- Transfer the ‘Cash Payments’ into the Cash Flow Statement:
=Calculations!J167
- Transfer the ‘Accounts Payable Closing Balance’ into the Balance Sheet:
=Calculations!J168
- 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.