COGS Part 3 (Inventory)

Quick reference

• 00:04 The clue in the last section really was that we said ignoring inventory so
• 00:08 you can imagine that we haven't quite finished cost of goods sold yet.
• 00:12 We've got to consider inventory.
• 00:14 Let's take stock of that situation.
• 00:17 Get it?
• 00:18 Just let me be clear not all financial modes contain inventory or stock.
• 00:24 For example, financial modeling, may include just banking,
• 00:28 it may just include debt financing ,it may just include infrastructure and
• 00:33 no inventory, but others will.
• 00:35 And the reason I want to make a big deal of this is some instances people make
• 00:39 a complete and utter pig's ear of it.
• 00:41 They just make a total mess.
• 00:43 And that's because they may not calculate things correctly such as what
• 00:47 are the average cost of the materials used.
• 00:50 It may be a double count.
• 00:51 It may be that they omit things.
• 00:53 And it might be that they just happily let the balance sheet not balance.
• 00:56 So let's go through a simple example.
• 00:59 I'm here in the model.
• 01:00 Let's go to the Navigator sheet first of all and go to Assumptions.
• 01:03 And here in rows 37 to 48, I've actually put in a Purchases and related section.
• 01:10 My Purchases are in row 41.
• 01:11 And notice they're independent of everything.
• 01:14 They're not a formula.
• 01:15 I have just made my purchases as I feel like it.
• 01:18 In other models it may get more sophisticated and
• 01:20 it may be a function of sales and the infantry at hand and things like that.
• 01:24 But I'm keeping it simple stupid here.
• 01:26 But I can't go too simple with price.
• 01:29 Price can vary period to period and this causes problems.
• 01:33 Because if I'm trying to work out the total cost of goods sold for
• 01:36 overall materials or materials consumed.
• 01:39 I've got to work out what is the price of the items I've used?
• 01:42 And if it varies from period to period.
• 01:44 And notice here it varies between $2,800 and $5,000 between 2022 and 2024.
• 01:51 I've got to get the right price of the items I use.
• 01:54 Now, I might call on a method where I actually work it out to see
• 01:57 what I've used and I've labeled them all?
• 01:59 Or it might go last in, last out or I might go first in, first out, first in,
• 02:03 last out or last in, first out.
• 02:05 Now, some of those not allowed for accounting purposes.
• 02:08 Some of them are not allowed legally even if this is what you do operationally?
• 02:13 They're more complicated.
• 02:14 I want to keep this simple.
• 02:16 I'm going to work out just my average price.
• 02:19 And that's easy because if I can keep track of my total purchases in kilos and
• 02:23 what it's cost me in dollars then dividing one by the other gives me
• 02:26 my average price, simple.
• 02:29 Now, the other thing that I need to keep track of here is Amount used per sale.
• 02:33 Because that is what is actually going to be the kilo effect.
• 02:36 And of course Wastage which could also be of last lesson debt things like that,
• 02:40 because that's gonna be a necessary expense to the company too.
• 02:44 Days payable, well I'm going to need that for my creditors so
• 02:47 I think I'm in good shape to go and have a look at my calculation sheet.
• 02:50 Let's go to the next page.
• 02:53 On here, I've actually got my purchases restated in rows 71 to 72.
• 02:58 Just as before, we're keeping it so that you can follow it on a piece of paper.
• 03:01 I'm gonna make it even more basic.
• 03:04 I'm going to bring them in one more time here and here, and
• 03:09 multiply the two together.
• 03:11 Now be careful.
• 03:14 It's not just dividing the two together.
• 03:16 I've also then got to divide it by a thousand cuz I've put this in thousands
• 03:20 of dollars.
• 03:21 This is in kilos, this is in dollars per kilo, this is in dollars thousand.
• 03:25 Just be careful, common mistake that gets made here.
• 03:30 So, rather than use code, I've actually got TH1000.
• 03:35 Press Enter, let me show you where that 1000's come from by coming up here.
• 03:39 If I scroll through my actual name box, all this.
• 03:42 I'm sorry if it goes off screen.
• 03:43 It doesn't matter, we can see what we want just here.
• 03:46 1000 you'll see there is my 1000 here in cell G31, I've given it a range name.
• 03:54 Okay, now,
• 03:55 I'm gonna make that bold and I'm gonna give this a special type of border.
• 03:58 If I go to Cell Styles here, I'm going to actually click on Line Count.
• 04:03 And the reason for that will become clear when I've actually copied it across.
• 04:07 Do you see it's a dotted line?
• 04:09 I try to use in my models a solid line is some of the above.
• 04:14 A dotted line means it's something to do with the lines above but
• 04:17 not necessarily an addition.
• 04:18 It's certainly the case here,
• 04:20 because I have actually multiplied the two numbers together and then divided by 1000.
• 04:25 Right, so I've got here my days payable also referred in there as well.
• 04:30 I can get my days in period from here.
• 04:33 Make sure it's J$8 as before and copy that across so that I can
• 04:38 work out my closing payables in the usual way so let's just bring this down here.
• 04:45 So my Closing payables is going to be equal to my actual purchases for
• 04:51 the period multiplied by my days payable divided by the days in the period.
• 04:58 131, copy that cross, and I've got a little mini control account going here.
• 05:03 So I've got to put this through quickly.
• 05:05 My Opening payables is simply going to be equal to the previous cell.
• 05:08 Copy that to cross.
• 05:10 Sometimes when you refer to a blank cell here, you will get blank, so
• 05:13 you'll have to just restart it that's where the problem is.
• 05:16 This is going to be the sum of the above.
• 05:19 I've got one more cross length of bit, so
• 05:21 that like a border coming in I put a blank cell in for Closing payables.
• 05:26 This is have for my cost a good soul and
• 05:28 if you remember if I go back to my Opening Balance Sheet here.
• 05:32 I've already done, if I scroll down here, Accounts payable,
• 05:35 I refer to that in this section up here.
• 05:39 My Closing payables so far, bring it in a second time, I double count.
• 05:43 So I haven't got it.
• 05:45 My Purchases are going to be equal to this particular calculation here.
• 05:51 I have to work out my Cash payments.
• 05:55 Hang on, I've made a mistake.
• 05:57 Common error people make.
• 05:58 And this is why I'm doing this.
• 06:00 It's to actually show you no haven't stuff stop I actually want to show you,
• 06:02 you gotta be careful when you think through.
• 06:04 Because sometimes you just think I'll just sum everything and be done with it.
• 06:07 No, my Closing payables is actually this line line 94.
• 06:11 So that goes in here.
• 06:13 Made a mistake here.
• 06:15 That's okay, because it's not always a total even though it looks like it.
• 06:21 And this then is simply going to equal the total down here minus the sum of the two
• 06:26 rows above as the tricks we've employed previously.
• 06:30 Right, now, this is all in dollars thousand, and
• 06:33 I've got to actually then put in where it actually links to.
• 06:36 So this is going to link to the Balance Sheet.
• 06:40 This one's going to link to the Cash Flow Statement.
• 06:45 These forms of all credential link to the bottom sheet.
• 06:51 The website won't go.
• 06:52 But we will leave that for a moment.
• 06:54 Let has to think about that at the end of the next couples sessions,
• 06:58 what we are going to a bit more?

Lesson notes are only available for subscribers.