In here we have the years going across roads number five and a capital expenditure program spread over the first four years, and we have a start date of 2022. We also have a sensitivity input currently set at 0%. Under that, we have our calculations. Firstly, we have the years starting in 2022 if this is going to be the first period, we calculate that by saying, give me the difference between the year I'm in and the year in which the program starts, and I'll lock that by putting the dollars on it and then add 1 so that that becomes year one, in which case the next column is year 2, 3, 4, et cetera. If for example, we wanted to show that the program only started in 2024, we simply changed the input and you can see now 2024 is year 1, 2025 will be the second year. 2026 will be the third year, and so forth.

If I want to build in a lookup formula to be able to look up what is the appropriate amount being spent in that year, let's do a lookup equals H lookup

and the first thing we need to say is what are we trying to look up? I'm looking up year one. Where do I wish to find that? In this set of numbers up here, years and total CapEx in rows five and six. I'll fix that. When I find the correct year number, I want Excel to pull out a number out of the second row, the table because that's the amount of CapEx.

I also want to put false on the end so it doesn't give me an approximate match. I only want an exact match and you can see that gives me 10,000 in the first year, copy it through 5,000 in the second three, in the third one in the fourth. So the same numbers that we see above.

But if we change that to a start date of let's say 2024, while 2024 is now year one, and the lookup is picking up 10, 5, 3, and 1 starting in 2024, it's the same numbers just delayed by two years. You can see though that because it is now calculating year 2022 as year minus one and 2023 as year zero, the lookup fails because there isn't a year minus one and there isn't a year zero. We would need something to correct that error. Otherwise it will flow through the remainder of the model.

We could use something like an if error or if we want to be a little bit more precise. If is n/a, and we refer to the number above, so if that is an in a value, which it is right now, convert it to something else like a zero. If it's not, then just use C15.

So that just converts it to a zero, so it eliminates error values.

If we add the sensitivity on, well, all we need to do is say that number, including the error correction times 1 plus the sensitivity percentage.

Right now the sensitivity percentage is zero, so it's giving me precisely the same numbers, but if I said, show me what happens if the CapEx program overruns by 10%, you can see here it's taken each of those years numbers and added 10% onto them. If I say 25%, it does the same increasing each of the year's expenditure by 25%.

I'll reset that back to the numbers that we had at the start. You could also use the index match formula. The index match formula simply says equals index, and then I point to the line that contains the numbers. I would like to try and find, lock that, and then we say, well, which one of those numbers do we want? We want the number that matches the year. I am in to the year numbers across line 5. I want a precise match, so I put zero on the end and I close the brackets, copy that through. Again, if I change the date, say to 2024 so that that becomes year minus one and year zero, the index match, just like the lookup fails because it can't match a number that corresponds to minus one or year zero to fix that, then we would do something very similar to what we did above. We could just actually copy the formula from the previous slide. It says, if C19 is an NA value, just call it zero, otherwise use C19. So it just eliminates the NA values.