Example 1- Question
In row 14 we have the capex spend for the next 7 years. The lifespan of the assets is 3 years as shown in B18 but we may want to change this. Create a depreciation calculation by:
1. In F18 row create a formula that determines the maximum number of periods that Excel needs to go backwards.
2. In F20 row create calculations that adds up the amount of capex spent in the most recent years (based on life span) and determine the depreciation to apply.
Example 2 Question
In row 11 to 20 we have different asset types with different life spans shown in B11 to B20 and the anticipated spend each year (F11 to Y20)
1. In F24 to Y33 create helper cells that will specify how far back Excel should go with the OFFSET function
2. In F37 to Y46, determine the annual depreciation charge.
3. Once you are done, the only error check cell that should indicate a problem should be C45. Why is this not working out?
Example 3 Question
We need to determine the capex requirements of a project which must take into account:
- The initial capex spend
- When the initial capex needs to be replaced
- The depreciations/ wear and tear on the capex
This model has been built with the results at the top (row 12 and 13 are the cash flows that will flow to the rest of the model).
Rows 17 to 21 are some inputs and the detailed capex results.
The suggested process to follow is:
1. In row 28 to 31, create a formula that determines when the capex is spent AND replaced
a. For initial spend date look at the matching input in E17 to E20. This will say when the initial capex is spent
b. The formula must also consider the replacement period (cell G17 to G20) so that we spend the same amount again after each replacement cycle.
2. In row 45 to 48 we need to determine the drawdowns required as we don’t spend it all in one go. Based on the inputs in F17 to F20 we need to split the costs evenly over the months AFTER the anticipated spend date in rows 28 to 31 i.e. if we plan to spend 404 000 in month 2, we will pay for it over 3 months being 134 667 in month 2, 3 and 4. You may need to use some helper cells in row 37 to 40.
3. In row 59 to 62 we need to determine the amount of capex that is depreciable based on the date in F57
4. In rows 67 to 70 we can determine the actual capex spend per month after we are operational.
5. In rows 75 to 78 we can create helper cells to determine the number of cells Excel should look at
6. In rows 83 to 85 we can calculate the depreciation charge per month over the period.
7. Now that all the calculations are done, we need to go back to the top
8. In row 17 to 20 create a formula that determines the cost in inflated terms (the replacement capex will be at higher amounts) by using the inflation index in row 2.
9. In row 12 and 13 link to the correct cells to bring through the total capex and the total wear and tear to be used elsewhere in the model.
To see the full course contents click here.