CAPEX Part 2

Quick reference

• 00:04 Let's start looking at the Modeling Capital Expenditure then.
• 00:08 So back in here, we've got to look at both modeling capital expenditure and
• 00:13 the depreciation and amortization.
• 00:16 Now in our model, we're not going to have any intangible assets, but
• 00:19 it's the same element, you just do it twice.
• 00:21 Let's take a look.
• 00:24 In the preparation cost, we actually looked at the the offset function and
• 00:28 how that would actually help calculate depreciation.
• 00:31 So let's go revisit that example again as a reminder.
• 00:34 We had an economic life, in this case, of four years,
• 00:38 had Capex over 8 years, going from 100 to 800.
• 00:42 We'll use the offset function to transpose these numbers.
• 00:46 And then we actually calculated it going, okay take the minimum of taking
• 00:51 this number here and dividing it by the economic life, and what's left?
• 00:56 100 minus the sum of nothing is 100.
• 00:58 What's the minimum of 25 and 100, is 25.
• 01:01 The minimum of the 100 divided by 40 is 25, and 100 minus 25,
• 01:06 which is 75, is 25, and so on.
• 01:09 This means it's easy if we do 4.71 years or something like that,
• 01:13 it still works, we don't over depreciate.
• 01:16 And we end up with this depreciation group down here, where we have to sum them all,
• 01:20 and it can get pretty large pretty quickly.
• 01:23 What we did though, if you recall, was we actually redid it by having an adjusted
• 01:28 counter, which went from the period 1 to the maximum economic life.
• 01:32 So it went 1, 2, 3, 4, 4, 4, 4, 4, 4, 4, etc.
• 01:36 The depreciation charge is simply the actual capex for
• 01:39 that period divided by the economic life.
• 01:43 And then we use this sum offset approach, which actually generates exactly the same,
• 01:48 depreciate this and this are the same.
• 01:52 And we can use this sum offset approach to avoid the grid and
• 01:56 just do the calculations nice and easily when it's straight line,
• 02:00 which is what we're going to do in this instance.
• 02:05 Let's apply then Capex and straight line depreciation to our modeling example.
• 02:11 Back to our navigator sheet, first of all, and to assumptions.
• 02:15 Here's my Capex and amount version again.
• 02:20 And I've also cited my remaining life of existing assets and my new Capex.
• 02:25 Now it's not unusual for
• 02:27 existing assets to have a longer economic life left the new assets.
• 02:31 Because the existing ones might be the buildings which tend to have 30, 40,
• 02:35 50 year lives.
• 02:36 Whereas, the new Capex could a new widget making machine, whatever that is,
• 02:40 a vehicle fleet, and so on.
• 02:43 That's not unusual.
• 02:45 One of the things I will do here though, I will actually work out what the percentage
• 02:49 is each year that we will apply of appreciation.
• 02:51 Assuming it's straight line,
• 02:54 we will actually prorate it as one over the economic life.
• 02:58 But we may have a division by zero error in here.
• 03:01 So this is why we do it on the assumption sheet,
• 03:03 that's actually capital percentage is.
• 03:05 Do the error check once, and
• 03:06 then we'll just multiply by the percentage there after.
• 03:09 So, I've got to type in equals if, open brackets, this number here is 0,
• 03:16 then nothing, otherwise 1 divided by that number.
• 03:21 Looking good, 20%, and then we'll just copy that.
• 03:26 Guess what this one is?
• 03:28 25%, so I've got our numbers all happily sorted out.
• 03:32 Now we've done those calculations,
• 03:33 we can turn our attention to the calculation sheet.
• 03:36 I've already fed those things through, except for
• 03:39 opening existing assets bounce, which comes from the opening balance sheet.
• 03:44 So that will be equals to the opening balance sheet.
• 03:47 It's not going to be concave, remember, we're gonna keep the blank column for
• 03:50 the trick we employed before.
• 03:52 Make that absolute, done.
• 03:55 And now we can do our calculations.
• 03:57 Now depreciation for the existing assets is simply going to be equal to
• 04:01 the existing assets, multiplied by the depreciation rate each year, which is 20%.
• 04:07 Notice I do this in column I away from the actual time periods, because of the fact
• 04:12 that it actually doesn't relate to a specific one of those periods.
• 04:16 It's a general calculation,
• 04:17 whereas the new assets we'll do them underneath the respective time period.
• 04:21 That's going to equal that number there, multiplied by the annual rate,
• 04:25 made absolute here.
• 04:26 Press enter and copy it across.
• 04:28 Don't forget to star the numbers if you need to.
• 04:31 Now we're going to use sum offset approach to calculate the depreciation.
• 04:35 So we're going to need the new assets depreciation counter.
• 04:38 Which with an economic life of 4 is going to go, 1 2 3 4 4 4 4 4,
• 04:43 so I'm going to go equals the minimum, open brackets, r for
• 04:48 counter up here, j dollar 9, and the economic life here made absolute.
• 04:54 Copy that across we have 1,2, 3, 4, 4, 4, 4.
• 05:01 See I could get depreciation for the existing assets will run out at the end of
• 05:05 the economic life, which, here, is five years.
• 05:07 So I'll put equals F, open brackets, the counter,
• 05:14 Is strictly greater than the economic life made absolute.
• 05:19 Then it is going to be nothing.
• 05:20 Otherwise, it's going to be that value we calculated.
• 05:26 And we will copy that across.
• 05:31 Whereas the actual calculation for
• 05:34 the new assets is going to use a sum offset approach.
• 05:38 As I discussed a few moments ago.
• 05:41 And that's going to be okay.
• 05:43 Some offset, back to the depreciation.
• 05:47 We don't want to move any rows or columns or specify the height as 1.
• 05:52 So we can leave that alone, and
• 05:53 the way it will be minus the actual counter, close bracket, close bracket.
• 05:58 Bizarrely, that will give you the depreciation.
• 06:02 If we then sum them up, opt equals to some materials above, that gives me my
• 06:07 total depreciation, which I can flow into my control account next time out.

Lesson notes are only available for subscribers.