Tax Part 8

Quick reference

• 00:04 Let's continue the line item that never seems to end.
• 00:09 It's time to continue with taxation, yay.
• 00:13 Back to our calculations then.
• 00:15 We've calculated at the end of the last period the tax payable for the period.
• 00:20 We've now got to do a little mini control account to actually work out the effects
• 00:23 of the payment delay.
• 00:25 We need to bring in therefore the one year from the actual assumptions.
• 00:29 So the one year delay is here.
• 00:31 Put that in.
• 00:32 And the closing tax payable, we're going to bring this
• 00:35 from the actual opening balance sheet, and tax payable is here.
• 00:40 This cell.
• 00:40 Make sure you click on column I, not column K.
• 00:45 It would be horrendous to make a mistake there and we're so
• 00:49 close to finishing off this model now.
• 00:51 Then this is going to be the previous period's closing balance.
• 00:55 This is going to be the sum of the rows above.
• 00:59 Ctrl+C, highlight these, Paste Special Formulas,
• 01:04 tax payable for the period is going to be that, all looking good.
• 01:10 The problem is the tax paid.
• 01:12 Now if there's just one period,
• 01:13 I can just go equals minus the opening balance as we've done before.
• 01:17 Remember, that makes it nice and easy.
• 01:19 All looking super smart.
• 01:22 But in this particular instance, I can change this number.
• 01:26 So I need to use the offset function to do this.
• 01:29 And I need to restrict it.
• 01:30 It doesn't start linking to things over here like US thousands of dollar or
• 01:34 tax payable for period.
• 01:36 So I'm saying don't calculate anything if it's going to the left somewhere.
• 01:41 So the formula we use is equals minus, IF open brackets,
• 01:46 the counter J$9, just for change.
• 01:48 You must be getting used to that by now,
• 01:51 minus the payment delay which I'll make it $I327.
• 01:55 If that's strictly less than 0, so
• 01:57 if it's asking to displace periods to the left of the first date then put zero.
• 02:02 Otherwise, we're going to take offset, open brackets of J330, this period here.
• 02:09 We're not going to go any rows down, but
• 02:12 we are going to go minus this many columns.
• 02:15 The minus sign, remember, makes it go columns to their left, so
• 02:20 previous periods, close bracket, close bracket.
• 02:23 So press Enter.
• 02:25 I copy that across.
• 02:27 There is my convoluted way of making it one period different.
• 02:29 But you see the value, because if I change this to 2 years and
• 02:33 go back in here you'll see now it's actually moved 2 periods over.
• 02:37 Simple, right?
• 02:40 So I put that back to 1.
• 02:42 Looking good.
• 02:44 Now, this is going to give rise to the calculations for
• 02:50 the closing and opening tax payable down here in my main control account.
• 02:55 But before I do that, I've got to just finish off some work I was doing on my
• 02:59 tax losses because I've got one more line item to put in.
• 03:03 I've done the tax losses memorandum but
• 03:05 I've also got to calculate my movement in tax losses.
• 03:09 Now, they will give rise to a deferred tax asset.
• 03:11 Now, I don't need to change the sign of this like I did with the deferred tax
• 03:14 liability because DTAs are already positive in the balance sheet.
• 03:17 So I'm simply going to equal the sum of the numbers in the middle here,
• 03:23 J341 and 342 copied across.
• 03:26 Just for a change, we're going to need to bring the tax rate in.
• 03:29 So here's my tax rate, bring that down here.
• 03:31 And my movement in DTA is therefore simply going to be equal to
• 03:36 that number multiplied by that number.
• 03:39 And again, that should be a solid line.
• 03:41 That should be a LineCalc.
• 03:44 And we can copy that across.
• 03:46 We've now done our movement in deferred tax asset.
• 03:51 As before with DTLs, this is what we put into our
• 03:54 control account that we're going to build down here.
• 03:57 It's going to go in there.
• 03:58 But we're going to need to put in the closing DTAs for the balance sheet.
• 04:02 So we're going to need to do yet another little mini-control account,
• 04:06 and that's going to come out as follows.
• 04:09 We're going to bring in here equals and
• 04:11 bring in the opening DTA balance which is this number up here.
• 04:15 And we'll make it look like that so
• 04:17 that we have a distinction to get this differently.
• 04:20 This will equal that, copy it across.
• 04:24 And this will be equal to the sum.
• 04:27 Alt equals of the two rows above.
• 04:29 And just for a change we're gonna Copy and Paste Special as Formulas.
• 04:35 The actual movement in DTAs comes in here and
• 04:38 because we're making a profit, it really doesn't look like anything.
• 04:42 So what I'm gonna do is I'm gonna cannibalize this again.
• 04:44 I'm gonna go back up here to show you how this works by putting -200 in here to show
• 04:49 how it's all working.
• 04:50 Do you see that what we've actually got is losses created here of 57
• 04:55 in the first period, because 143 minus 200 is minus 57?
• 05:00 31 if it gets used in the second period and
• 05:02 the remaining 26 in the period thereafter.
• 05:04 There you go.
• 05:08 This actually needs styling as a number.
• 05:10 We then multiply it by the tax rate, gives us our actual deferred tax liabilities.
• 05:17 So that needs styling as a number as well.
• 05:19 And then we keep a running total of our deferred tax, that's it.
• 05:23 So it gets as high as 17, then goes down to 8, then goes down to 0.
• 05:27 That's how it works, that's what we're doing.
• 05:30 So let me put this back, right?
• 05:33 We don't want to have a hard coded number in there.
• 05:35 So it goes back to zero but I just wanted to show you what was working then.
• 05:41 Also by doing that,
• 05:42 do you see I noticed I hadn't actually got some of the cells styled?
• 05:45 And it was only when they became non-zero that you actually saw that
• 05:49 it was working correctly.
• 05:50 It's very important to use dummy numbers sometimes so you're not just building
• 05:55 a model with zeroes everywhere, because you can miss things as a consequence.
• 06:00 Okay, so we've now put in our DTA.
• 06:02 We've put in our DTL.
• 06:03 We've worked out our tax paid and we've done our tax expense.
• 06:06 Guess what?
• 06:07 We're ready to attack this control account next time.
• 06:11 Yay!

Lesson notes are only available for subscribers.