In the last section we noted the distinction between “flow” and “stock” items. Stock items are those which accumulate (or reduce) over time through the effect of flows.
The “corkscrew” structure is an important one in models in which there are stock items: It allows one to calculate such items in a transparent and flexible way. In the following we cover two variations of these structures:
In a classical corkscrew structure (in each time period), one has a separate cell for:
Examples of where these structures can be used include:
As an example, using in the variation of the Savings model used earlier in the chapter (i.e. with growth rates for several time periods applied to the salary and the expense items), one could create a corkscrew to calculate the total Savings accumulated up to a particular time period. This is shown in rows 21-24 of the following image.
Note that the corkscrew formulas are created by first “bringing forward” the prior period ending value (i.e. as the starting value for the new period), and the first formula is based on the value of the stock item at the point at which the forecast is to start (assumed her to be $1000 in Savings):
Then, the flow items (Salary and Expenses) are each drawn by direct cell references which contain the results of the calculation of each. For example, Salary is linked into the corkscrew:
Similarly, Expenses are linked, so that the final item in the corkscrew (i.e. the value of the item at the period end) is can be calculated from the other corkscrew items (i.e. based on the starting, Salary and Expenses values in this case):
(Corkscrews also exist in the “accounting” format, in which Expenses are expressed in negative terms, and the corkscrew calculations for the period end values consist only of additions of the items).
The formulas can be built (or copied across) into the other columns, to give:
Note that a corkscrew structure is named due to the dependency paths that link the ending and starting values for consecutive periods (as shown on the left-side of the image). This part of the structure is therefore not a pure “top-to-bottom” flow. However (see right-side of the image), the top-to-bottom principle is respected within each individual time period:
The corkscrew structure is frequently used for calculations relating to fixed assets, such as machinery, plant and equipment.
For example, suppose that – starting with no equipment – we invest $10m per year, and also that within any year, the value of equipment that existed at the beginning of the year is to be reduced by 20% per year, to reflect general wear and tear or depreciation. A corkscrew structure would look like:
Another important use of corkscrews is for loan calculations. The basic principle is illustrated in the following image, which is based on an initial loan amount of $100k, and for which the interest rate per period is 1% (the period could be quarterly or annual, for example). Within each period, interest is charged based on the loan amount at the start of the period. In the model in the image, it is assumed that – at the end of each period – a fixed payment of $20.60k is made. The ending balance on the loan is therefore calculated by adding interest to the starting balance and then subtracting the repayment. (The value of $20.60k has been chosen as a value which ensure that the loan is exactly repaid at the end of period 5).
(A detailed discussion of interest rates, including compounding and the effect of intra-period repayments is covered in the course “II.1 Economics and Financial Evaluation”. Whilst the above description is likely to be intuitive to most readers, there are potential subtleties in such calculations due to various possible approaches to how interest rates are quoted and measured, and the terms associated with loan repayment. These are beyond the scope at this point in the CertFM Program).
The main benefits of using these classical corkscrew structures are:
Exercise: Please build the Loan model shown above from scratch!