Working Capital Adjustments Part 2

Quick reference

• 00:04 Before I actually model these working capital considerations.
• 00:08 In case you aren't watching this straight or flat one, let's do a quick recap.
• 00:11 I derive my standard formula from a graph.
• 00:16 Have I had a look at my control account,
• 00:18 where I assumed I had no debts known to me at the beginning of the period.
• 00:22 I made sales of $1,000 in the period,
• 00:26 I only received cash of $753 meaning I'm still out $247.
• 00:30 So I made a copy of the income statement.
• 00:33 Turned it into a cash flow by adding in a credit period.
• 00:36 Mapping the actual sales, as to when the cash came in.
• 00:40 Realizing therefore that the $1,000 of sales in the period
• 00:44 translates into $753 in the period, and $247 after the cutoff.
• 00:50 Which means my credit period must account for
• 00:53 0.247 of the time, which is in this case 365 days.
• 00:58 That's not always the case in modeling, but
• 01:00 that's what it is in this particular incidence.
• 01:02 You can derive some formulas that will result for both accounts receivable and
• 01:06 days receivable.
• 01:07 By the way, this is the same for payables as well, but
• 01:10 I'm using an receivables example here.
• 01:12 So this case with those figures, my days receivable
• 01:18 calculates as 0.247 times 365 equals 90 days.
• 01:23 Let's go and put all these formulas then into my Excel example from earlier.
• 01:31 It seems like a lifetime ago now, but
• 01:33 let's go back to our revenue calculation that we started last time.
• 01:38 What I have added is a working capital section in rows 25 to 31,
• 01:43 based on the assumptions here in row 23 of this sheet because my days receivable.
• 01:51 I have got to now calculate the rest of the numbers in order to work out how to
• 01:56 translate revenue into cash by working out my closing receivables.
• 02:01 So my days in period I already have, I've got that in row eight.
• 02:06 So just link to that.
• 02:08 And I'll just link it, J$8, so I can copy it down later.
• 02:13 And make sure that is styled as a number.
• 02:19 My closing receivables will therefore be the formula I had before.
• 02:24 Which is going to be my revenue for the actual period,
• 02:28 multiplied by my days receivable, divided by the numbers of
• 02:33 days in the period, some like graphic we showed before.
• 02:38 Again I need to actually go in and get that a little looking nice up here.
• 02:42 So let's go to my cell styles and
• 02:45 I'm going to make it a number again, and copy that across.
• 02:50 So that is going to show me what my closing receivables will be
• 02:58 I've put in a control account as well, so I'm in a nice place here.
• 03:03 Now I need to bring in from my control account my opening balance,
• 03:07 remember it's a running total.
• 03:09 So first of all I've got this off sheet or
• 03:11 this internal reference here, where I have to link back to the opening balance sheet.
• 03:16 So I'm gonna go equals, go back now to my opening balance sheet,
• 03:20 and pick up my accounts receivable number.
• 03:24 Make sure you pick up this number, not that one.
• 03:27 And I'll make that absolute, so we know it's actually going to pick that up.
• 03:31 Then my opening receivable is just simply going to be the previous page
• 03:34 closing balance.
• 03:34 No need for an interest statement here.
• 03:37 My revenue I calculated in Row 23.
• 03:45 And my closing receivables I just calculated in Row 31.
• 03:48 So I'm not doing summation, I'm putting these numbers in.
• 03:52 And therefore my actual cash receipts is going to be the the balance, aw rats!
• 03:59 How annoying is that, when you actually copy and
• 04:02 you get these borders coming in cuz you've forgotten.
• 04:05 You don't do it like that,
• 04:07 let me show you a trick, that's the whole point of this course.
• 04:09 You go copy here, you highlight all of these, and
• 04:13 then you go Paste Special, Alt+E+ S.
• 04:16 And you do in this case, this one, all except borders, Alt+E+S+X.
• 04:22 Especially memorable if you are dyslexic, let's move on.
• 04:28 See, no border lines there.
• 04:30 Then the cash receipts will be the actual balancing figure.
• 04:34 So it's going to equal the total down here minus the sum of the two rows above it.
• 04:42 I want to copy that across, you'll see, lovely, I've now got all my data.
• 04:49 So to recap, my opening receivables is my previous period's balance sheet number,
• 04:54 which is why I have got balance sheet here.
• 04:57 I've got my income statement numbers here.
• 05:01 Here's my cash flow statement numbers here but they won't go in as positive numbers.
• 05:05 And then this, if I get rid of this little thing here by clicking on this down
• 05:09 here to get, there it is, my actual balance sheet, closing balance sheet.
• 05:13 Now do you remember the rules for a control account?
• 05:17 Well, if you don't, I'm about to go through them in the next session.

Lesson notes are only available for subscribers.