COGS

Quick reference

• 00:04 The title of this session sort of gives the game away as to what's next really,
• 00:08 but let's derive it in any case.
• 00:11 Is this getting familiar?
• 00:15 We want an efficient model that goes from A to B like an arrow.
• 00:18 We put our checks in first, model the operational section to begin with,
• 00:23 with some help from working capital adjustments.
• 00:26 We'll need some asset adjustments later, too.
• 00:28 Then we're gonna have to consider financing and tax, and
• 00:31 its impact on financial statements before considering other key outputs.
• 00:36 But essentially what I want to really focus on here,
• 00:38 is the other graphic we derived.
• 00:42 Here, we've completed now revenue, and in order to complete revenue,
• 00:46 we have to sort out the debtors too.
• 00:48 So I've cleaned out revenue and debtors in my little box.
• 00:52 What I've now got to do, if I'm going down the income statement,
• 00:56 remember the income statement is conceptually the smallest of
• 00:59 the three financial statements as it only considers operational elements
• 01:02 while the cash flow also considers financing and investing elements.
• 01:06 The balance sheet basically links in the other two.
• 01:10 So we're working our way down, because this is how we think.
• 01:13 It's like clear the very next thing we're going to consider is cost of goods sold.
• 01:16 So let's consider cost of goods sold.
• 01:21 I've continued tweaking the model.
• 01:23 We've got some assumptions now for cost of goods sold.
• 01:26 In row 30, I have created the percentages inputs
• 01:30 on something called gross margin non-inventory.
• 01:34 Now, you're not going to find this in any textbook.
• 01:38 Sometimes companies come up with jargon, and I wanted to show you an example,
• 01:42 a classic example for that line of such rubbish jargon about how
• 01:47 would you deal with it and we'll talk about it in a moment.
• 01:50 And the working capital of the day's payable is another amounts method and
• 01:54 we have 90 days payable for it in this case.
• 01:56 Great. Then we can actually our bills longer than
• 01:59 we can actually get the money in, this is quite interesting and
• 02:03 not at all realistic, but never mind.
• 02:05 So gross margin non-inventory.
• 02:07 Well there's inventory in our model.
• 02:09 An inventory will actually be part of the direct materials and
• 02:12 therefore will form cost of good sold.
• 02:14 What this is saying is, don't include this in the inventory calculations.
• 02:19 So okay, so let's do what we did before.
• 02:22 I'm going to go to the calculation sheet.
• 02:24 I've already start to producing labeling,
• 02:27 because I don't want to go through all that again.
• 02:29 But I need to bring in my revenue, my costs of goods sold,
• 02:33 margins and so on and so forth.
• 02:34 So the first thing I need to bring in, is my revenue.
• 02:37 Now I can find that sort of here if I scroll back up.
• 02:40 I've actually got it in well originally I produced in there 23 there, J 23.
• 02:46 And I can copy that across.
• 02:48 Now I've already pre-styled these but you might need to do that.
• 02:51 Now a little trick here, I'm gonna widen this column, cuz we need the row totals.
• 02:56 I'm going to refer to this because I know this is row 23.
• 03:00 So I'm actually going to type in here, equals, open speech marks,
• 03:05 row space, close speech marks, that give me a row with text.
• 03:09 But then I'm gonna use the ampersand, that shift 7 on many
• 03:15 QWERTY keyboards, to actually join it up with the row of J23.
• 03:20 And I'll just type in J23 here, I won't go through it, and that gives me row 23.
• 03:26 Because now I know that's where it's coming from.
• 03:28 And it will help if I'm actually trying to follow it.
• 03:31 I go cell style.
• 03:33 And I actually have here a row reference style.
• 03:36 Just say row 23.
• 03:38 So I know that that's going to row 23.
• 03:40 It's not an internal reference, and it's not looking from another sheet.
• 03:43 It's still useful to know that it's from this sheet and
• 03:46 you can go find it easily if you print out the row of column headers.
• 03:52 Now my gross margin comes in from the other sheet, so
• 03:57 that's gonna be my 70% here.
• 03:59 I need to star that.
• 04:02 First of all, as internal reference.
• 04:05 And then I need to start again, so you can put more than one in.
• 04:08 After a number there's a percent.
• 04:11 And copy that across, so therefore my gross profit
• 04:17 should be thus be equal to that, multiply by that.
• 04:23 So therefore my cost of good sold is going to
• 04:26 be the difference between my revenue and my profit.
• 04:29 You see, I'm stepping it up, making it nice and simple, so it's easy to follow.
• 04:33 There you go.
• 04:34 I've got my cost of goods sold in here, piece of cake.
• 04:39 Now, I can do my work in capital, just, we haven't got time to do all this.
• 04:45 What can I do here quickly?
• 04:47 Well, this is what consistency's all about.
• 04:49 Remember consistency?
• 04:51 Watch this.
• 04:52 I'm gonna go back up here and I'm going to go to my actual take this and
• 04:57 queue all here and I'm going to take that and go copy.
• 05:02 In fact I'm going to take all of this and go copy, or thereabouts.
• 05:07 Now they're back here today's payable, I can paste, Ctrl+V.
• 05:11 Now, some of it's not right, cuz my day's payable needs to link back to here.
• 05:16 We'll copy that, of course.
• 05:20 And this isn't right, because that is currently linking to the wrong side.
• 05:24 So I need to make that link to the closing payable on here, right?
• 05:29 Absolute.
• 05:32 And everything else has been calculated, because I have used a consistent formula.
• 05:38 I'm done.
• 05:39 I put dollar signs in here, everything's words, I don't need to go to.
• 05:43 This is the virtue of thinking about spacing and about consistency.
• 05:47 I've bombed through it in seconds.
• 05:49 And the beauty of this is, by doing this nice and quickly, I've plowed through and
• 05:54 I've actually got this working and it's the in my formula above.
• 05:58 It would've been picked up here and then I could've fixed it up.
• 06:00 So I'm sort of reviewing the top section while I do this.
• 06:04 So I've got my control again.
• 06:05 So I can go through again and I can put in my actual cost of goods sold calculations,
• 06:11 so that my balance sheet balances.
• 06:12 And that we'll do next time.

Lesson notes are only available for subscribers.