2.4 Using Triangles for Time Allocations
Introduction
Often it is necessary to model a set of actions or projects that are initiated at different points in time, and for which each has an effect that lasts for several future time periods.
In the case that each project has its own unique characteristics, then there is little choice but to create a model in which each item or project is tracked individually and explicitly. However, if the future time profile of each action (item or project) is the same from the time-point at which each is launched, then one can use a “triangle” structure to model the effects. Examples of situations where this arises include:
- To calculate the total value of plant and machinery that a company owns, given that the value each individual piece of equipment depreciates in the same way over time (measured from the date of its initial purchase).
- To conduct business planning for a project-based company, in which there are potential projects that have the same profile of investment and future cash flows. This is often the case in business such as pharmaceuticals, oil and gas, or real estate.
Example: Value of Equipment
As mentioned above, triangles are relevant in cases where multiple items need to be tracked over time. Before discussing these in detail, it is convenient to first review the calculations for a single item.
The following image shows the development over time in the value of a piece of equipment. The value decreases (depreciates) as the equipment becomes older. (This could be for accounting reasons, or due to the market price of the item – such as a computer or a car- decreasing over time). In the image, the equipment initially costs $100k. It is assumed to have been bought at the very beginning of 2025 (or very end of 2024). Each year it depreciates in value by a percentage of its initial purchase value. The percentage for each year is shown in row 6. The time-profile of the reduction is not constant, with an assumption that more value is lost in the earlier periods than in later ones. The resulting depreciation in absolute terms is shown in row 5. A corkscrew is used (rows 8-11) to show the value of the equipment at the beginning and end of each period (based on the changes in each period resulting from depreciation):
For additional clarity, the following image illustrates the flow of logic more explicitly:
Note that in the above image, although there is a time axis that shows absolute years (2025, 2026, …), in fact the depreciation percentages (in red font, cells D6:H6) are “generic”. That is, the 40% depreciation (cell D6) corresponds to the percentage that applies in the year of purchase, with 30% (cell E6) being the amount in the second year, and so on. On the other hand, the purchase value (cell C6) is the value at a specific absolute date (i.e. the very end of 2024 or the very beginning of 2025).
In other words, one could represent the first rows more accurately as a range with specific dates that shows the equipment purchases, and a range with generic items that shows the depreciation schedule. This is shown in the following figure, with row 2 containing the specific dates, and row 5 the generic dates:
Note that whilst the generic depreciation schedule can be entered (row 6), the only entries for equipment purchases are for the year 2025 (cell D6). If equipment were purchased in future years, then the depreciation schedule needs to be applied separately for each year i.e. to the purchases made in that year. For example, any equipment bought at the very beginning of 2029 would depreciate by 40% in that year (whilst equipment that had been bought at the beginning of 2025 would depreciate 0% in 2029, having fully depreciated in the earlier years). This can be demonstrated with a “triangular” structure for the depreciation schedules for each year:
Note that the row structure of the triangle (i.e. cells B9:B13) is the transpose of the time axis that contains the absolute dates (D2:H2), whilst the content of the main part of the triangle is the mapping of the generic schedule to the dates.
The above triangle can then be used to create the calculations for the absolute amount of depreciation that occurs in each year (as a result of purchases in that year and in previous years), which can also be added to create the total:
Finally, a corkscrew can be created, which reflects the investment and depreciation each year, to give a net figure for the value of equipement (row 28):
(Note that row 28 is highlighted with green-shading in the above image; this shows the ending value of the equipment. The depreciation figures are shown in row 27; these are taken from those in row calculated from the triangle.)
Example: Generic Business Planning
The same principle can be used for business planning purposes, such as to experiment with future possible scenarios that result from designing a business portfolio made up of generic projects. A “generic” project is one that is not specifically identified but is representative of the types of projects that the business could create or implement if it chose to (such as through internal activities to develop new products, the purchasing of services or acquiring other businesses).
For example, the following image shows (rows 2-3) the cash flow profile of each generic project, whilst the time-specific profile for the number of projects that could be launched is shown in rows 5-6:
The total cash flow profile that results from this can be calculated using triangle structures:
(Note that – although cash flow for a single project is negative in the first year only – for the specific portfolio used, the cash flow is negative for the first three years, since the new projects launched in the second and third years require more investment (negative cash flow in the first year) than is produced by the projects that were launched earlier.)
The Switching of Triangle Inputs
It is clear that from a logical perspective, there is a distinction in the roles between the variable which has a specific-time axis, and that which has a generic time axis. However, from a purely calculatory perspective, the items are identical. That is, if the input values for the generic item are switched with those of the specific item (and vice-versa), then the calculations within the final triangle would be the same (even though no change is made to the formulas within it).
For example, the following image shows the result of simply switching the values of the inputs in row 3 and row 5:
Note that the results for the total cash flow are the same (row 22). This is a calculatory fact, even as the values of the inputs used may not necessarily make sense from a contextual perspective (such as the number of projects being negative, as per cell D3).
The Row-Column Structure of A Triangle and Extending the Time Axis
Whilst the formulas used in the triangle calculations are the same (in the above, only the input values were switched), in fact the number of rows and columns of the triangle need not be the same as each other:
- Each row corresponds to a specific time period.
- Each column – whilst also corresponding to a specific time period – is defined using the specific time period for the row and the generic time axis.
Thus, in principle, for each triangle:
- The number of rows that it contains is equal to the number of specific time periods in the model.
- The number of columns should ideally be sufficient to see the full effect over time of all projects launched within the specific time period.
Therefore, the time axis of the triangles in the earlier models could be extended, for example as:
