Locked lesson.
About this lesson
A continuation of an explanation of how to build COGS into the financial statement.
Exercise files
Download this lesson’s related exercise files.
COGS Part 6 (Inventory).xlsm100.2 KB COGS Part 6 (Inventory) - Solution.xlsm
101 KB
Quick reference
COGS Part 6 (Inventory)
Understand COGS.
When to use
When constructing a basic financial model.
Instructions
The next stage in calculating Inventory is calculating Wastage in $:
- Inventory balance Pre-wastage is calculated by subtracting COGS (Row 117) from Inventory balance Pre-COGS transfer (Row 115)
- Wastage (kg) is taken from Row 113
- 00:04 Let's finish off the inventory section of the modeling then.
- 00:07 Last time out, I started calculating the inventory in dollars, for
- 00:12 the corresponding inventory in kilos.
- 00:15 We've got it all sorted out.
- 00:17 And all I had left to do was wastage.
- 00:19 So let's get this line here, row 138 done.
- 00:22 And it's a similar calculation to the one we did for COGS before.
- 00:25 So scrolling up, I need the inventory balance pre-wastage, first of all.
- 00:30 And that's simply going to equal,
- 00:35 nice and easily here, the inventory balance in
- 00:42 row 115 minus the COGS, bang.
- 00:47 The inventory balance in dollars thousand, is simply then going to equal 116.
- 00:56 Minus 180, easy.
- 01:01 And the next one we are going to do, is calculate the wastage.
- 01:06 Which is simply going to be equal to this number here.
- 01:10 And It's exactly the same calculations before.
- 01:12 So that's row 113, so
- 01:15 let's just copy that down, copy, control V.
- 01:20 All right, that's 113, done.
- 01:24 And it's the same formula as we did here.
- 01:26 So cuz it's the same formula, let's just copy then.
- 01:30 Same idea again.
- 01:39 Right, we've got our numbers in here.
- 01:41 So it's simply going to be equal 6, 200ths of 798, 11, 569ths of 26 13 and so on.
- 01:47 And that's going to be a negative number in here.
- 01:50 So equals minus, bring that back up from here.
- 01:56 We finished our control accounts.
- 01:58 Now there are two control accounts altogether here.
- 02:01 Now we could've done the control accounts separately.
- 02:03 And if we'd done them separately.
- 02:05 We've got the cost of goods sold one up here, in rows 86 to 91, and
- 02:09 this one down here in rows 133 to 139.
- 02:12 But what I'm doing is, I'm putting them combined.
- 02:15 Because if you notice, purchases in row 89 here, equals purchases in row 136 here.
- 02:23 That equal and opposite, I'm therefore going to exclude them.
- 02:27 And this is one of the things that often confuses people
- 02:30 when they're building models, is they don't know what to do with the purchases.
- 02:33 And the fact is, you actually just knock it out because it'll always net off.
- 02:38 So I put these two financial control accounts together.
- 02:43 So this one's got altogether one, two, three, four, five lines so
- 02:46 that's four calculations.
- 02:48 Last one is three.
- 02:51 This one here, up here.
- 02:53 If I go back up, this control account has got four lines.
- 02:57 4 minus 1 is 3 calculations, but we've got purchases in it.
- 03:00 So we're only gonna have two.
- 03:01 So it's 3 + 2, 5 references we have to build into the financial statements.
- 03:06 So what are they?
- 03:07 We're gonna take the cash payments and put them into the cash flow statement.
- 03:10 The closing payables here,
- 03:12 goes into accounts payable in the financial statements.
- 03:15 Down here we ignore purchases again.
- 03:17 Cost of goods sold goes into the income statement.
- 03:20 Wastage, goes into the income statement.
- 03:21 And closing inventory into the balance sheet.
- 03:24 Fine let's do it, we've got them.
- 03:26 So start with the two up here.
- 03:27 It will only balance when we've done all five.
- 03:31 So cash payments.
- 03:32 This is inventory, it's direct cost.
- 03:35 So what we're going to do, is go to the cash flow statement,
- 03:38 it's negative already.
- 03:40 Here's a direct cash payment and
- 03:42 we're going to add to it plus, back to our calculations.
- 03:48 Here's our cash payments, copy it across.
- 03:50 As soon as we do this, it doesn't balance which is what we would expect.
- 03:55 Next up balance sheet.
- 03:56 Accounts payable, we need to do.
- 03:57 We've already got one.
- 03:58 We're going to add to this G64 plus, back to our calculations.
- 04:05 This number here.
- 04:06 Now normally when we put that in, we expect it to balance.
- 04:08 But we've knocked out purchases, so it's not going to.
- 04:11 That's fine.
- 04:12 We've still got three to go.
- 04:15 So down in here.
- 04:15 The three we've got to do.
- 04:17 First of all, cost of good sold for the income statement.
- 04:20 So that's going to be another addition.
- 04:22 It's plus this time, cuz it's already negative.
- 04:26 Copy that across.
- 04:28 Still doesnt balance.
- 04:29 Next up, wastage.
- 04:31 Bring that in.
- 04:33 Copy that across, still doesn't balance.
- 04:37 But finally, when we put the inventory into the balance sheet here.
- 04:40 So here it is inventory equals.
- 04:43 Go back to our calculation sheet.
- 04:45 I put this number in here.
- 04:46 Fingers crossed, when we copy that across.
- 04:49 It balances!
- 04:51 And that's how you put inventory as a direct cost,
- 04:55 into the financial statements and make it balance.
- 04:57 It requires two control accounts.
- 04:59 You exclude the actual purchases, because they net off.
- 05:02 And you put everything else through.
- 05:03 And when you the two, simultaneously, it will actually then balance.
- 05:08 Let's see, we're good.
- 05:09 We finished off cost of goods sold.
- 05:10 We've done the non-inventory.
- 05:11 We've done the inventory.
- 05:12 Next up.
Lesson notes are only available for subscribers.