COGS Part 6 (Inventory)

Quick reference

• 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.