COGS Part 4 (Inventory)

Quick reference

• 00:04 Let's continue our discussion on cost of goods sold in particular the inventory
• 00:08 element.
• 00:09 Picking up from where I was last time,
• 00:11 you'll notice I still have not put anything in this cell.
• 00:15 Cell L89 actually can cast off other parts of the control account.
• 00:19 But I haven't for this particular purchase,
• 00:22 which is why I didn't put it into the financial statement.
• 00:26 What goes in there?
• 00:27 Well, you might think, that should be income statement, shouldn't it, Liam?
• 00:30 Well, let's see, shall we?
• 00:32 Let's carry on.
• 00:34 Let's go down to my inventory related section.
• 00:37 So I've got to keep track of what's going on in the bar sheet and
• 00:40 the transfers over to the cost of goods sold.
• 00:42 I buy stuff.
• 00:43 I have it on hand.
• 00:44 So, it's inventoried on my balance sheet.
• 00:46 And then as I use it for in a sale, it will come out of my inventory.
• 00:49 And it will go through a cost of goods sold in my PNL as I'll accrue.
• 00:54 Right, so my purchases have already worked out.
• 00:56 Now, what do I refer to?
• 00:58 Well, I've got equals on where my linking up to.
• 01:01 If I look through here, my first occurrence of purchases is actually in row
• 01:05 71 before it actually goes off sheet.
• 01:08 So I'm going to link to that.
• 01:09 Always best to link to the first occurrence on a sheet.
• 01:12 Because it makes the formula calculate faster and
• 01:15 it's easier for people to follow.
• 01:17 So that's row 71.
• 01:18 And I'm gonna make life easier for people as well.
• 01:20 Draw over the blue box.
• 01:21 We did one of these before.
• 01:22 We had up here citing the row that it comes from.
• 01:26 And we'll do the same thing again here.
• 01:28 And in fact, what I'll do is I will steal this formula.
• 01:31 Copy, remember, we can that.
• 01:34 And I'm gonna put it down here.
• 01:36 Paste, Ctrl+V.
• 01:38 And here it is row 71, so just change that to row 71.
• 01:42 My purchases, well they come from here.
• 01:47 I've already worked those out in row 78 so I'm just going to type it in.
• 01:51 Equals J 78, it's okay to do that believe or not.
• 01:54 Once you actually do click the right reference I'm going to do the same
• 01:58 thing here.
• 01:59 Copy it down and make it J78.
• 02:02 My projected sales are also needed here.
• 02:07 If I scroll up,
• 02:08 I've actually got those calculated right at the top of the page in row 17.
• 02:14 So I'm going to bring those down as well.
• 02:16 Was J17.
• 02:20 Copy that across.
• 02:23 Copy that, drop it.
• 02:26 And the reason we always put the formula in is if we actually insert rows or
• 02:30 columns, this will all update automatically.
• 02:32 We're trying to make life as easy as possible.
• 02:34 There at 71, 78 and 17, see.
• 02:39 Okay, now the amount used per sale comes here from row 100.
• 02:43 So we'll go okay, that's there.
• 02:47 And the projected sales is going to come from here.
• 02:54 Not rocket science, is it?
• 02:56 And then if I couple these again.
• 02:59 We can actually keep following it.
• 03:00 Best not to do too many of these.
• 03:02 I have them put them in first in control accounts because it's from that section.
• 03:06 So, that's 100 and 103.
• 03:09 So, 100, And 103.
• 03:16 It's nice to make it easy for the end user.
• 03:18 And therefore my cost of goods sold is a very complicated calculation.
• 03:22 Got the dotted line to show it's not an addition.
• 03:24 It's actually that multiplied by that.
• 03:30 So I know how much is getting used.
• 03:33 Right, I can't work out my balances pre-wastage, post-wastage, all of this.
• 03:39 I've got to go down and do my little control accounts.
• 03:41 I've got two of them.
• 03:43 I've got the same thing twice, here, and I've also got it here.
• 03:47 One's in kilos and the other's in thousands of dollars.
• 03:50 The thousands of dollars will go into financial statements, but
• 03:53 I need them both.
• 03:54 So my opening inventory's always going to be the same.
• 03:56 It's going to equal the previous period's closing balance, so we'll put those in.
• 04:02 The actual turtle is simply going to be Alt equals of the sums above.
• 04:10 We'll copy that across.
• 04:12 My open box is always gonna be blank for the reasons I explained last time out.
• 04:16 We haven't got any.
• 04:17 My purchases in kilos I've already got that.
• 04:20 I've worked out what my purchases in kilos are here.
• 04:26 My cost of goods sold I've also got in kilos.
• 04:31 If I look through it's here, row 109.
• 04:37 So I need to put that in.
• 04:44 And the next thing I need to do now is work out my wastage.
• 04:49 Well, I can work out my wastage,
• 04:51 because I know what my inventory balance is pre-wastage.
• 04:53 It's going to be the Opening balance plus any Purchases less of Cost Of Goods Sold.
• 04:59 Now I've added it.
• 05:01 See comment, enter the get, maybe to go through.
• 05:03 You're probably clicking on this thing.
• 05:04 Yeah, that makes sense.
• 05:05 That makes sense.
• 05:06 Do you see?
• 05:07 You have to sometimes stand away and think.
• 05:09 Aah!
• 05:09 I've done it wrong.
• 05:10 This should actually be a negative.
• 05:13 Don't completely switch off your brain.
• 05:16 Your purchases are added to the inventory.
• 05:18 Your cost of goods sold removing.
• 05:20 So that is a deduction.
• 05:21 We are not just adding forever and ever and ever.
• 05:23 That makes more sense.
• 05:25 So now my wastage is going to be based on this number.
• 05:29 Now you can assume does more wastage happen before I take the items of cost of
• 05:33 goods sold or after?
• 05:34 That is a judgment call.
• 05:36 It is entirely up to you which you do.
• 05:38 I'm going to assume that my wastage occurs on the balance after I've taken out
• 05:41 the cost of goods sold, so I get the COGS out first at the beginning of the period.
• 05:45 But if it was the end of the period,
• 05:46 you might argue it's just on opening inventory and purchases.
• 05:51 So I will go through now, and say okay, let's put this through for
• 05:55 my actual wastage.
• 05:56 My inventory Preep, which is going to equal sum.
• 06:00 Open brackets, this little lot down here.
• 06:05 Press Enter, and copy that across, and
• 06:09 then I can calculate my wastage, next time.

Lesson notes are only available for subscribers.