2.7 Corkscrew Structures
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:
- The “classical” corkscrew, in which flow items are used to calculate stock items.
- “Implied” or “reverse” corkscrews, in which stock items are used to calculate flows. (This is covered in the next section).
“Classical” Corkscrew Structures: Using Flows to Calculate Stocks
In a classical corkscrew structure (in each time period), one has a separate cell for:
- The value of the stock item at the start of the time period.
- The increase in value due to those flow items that tend to have a positive effect on the stock value.
- The decrease due to flow items which tend to have a negative effect.
- The value of the stock item at the end of the time period.
Examples of where these structures can be used include:
- The total Savings that one may be able to make over several years can be calculated from the initial level of Savings, and the Earnings and the Expenses in each year.
- The amount that one owes on a loan taken from a bank can be calculated from the initial loan amount, the periodic interest charges and the periodic repayments made to repay the loan over time (in accordance with the loan terms).
- The value of physical equipment (plant and machinery) that a company possesses could be calculated from a starting value of the equipment (which may be zero), the investment in new equipment in each period, and the reduction in value (for example due to general wear and tear or depreciation) in each period.
- The value of finished goods inventory that a company has can be calculated from the initial value of inventory, the increase in inventory each period (due to production) and the reduction (due to sales of finished goods to customers).
- The value of retained profits (or equity) that a company can be calculated by considering the increases each period (due to profits made by the company) and decreases (for example due to dividends paid to shareholders).
Example (I): Savings Model
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:
Example (II): Plant, Equipment and Capital Expenditure
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:
Example (III): Interest Accumulation and Repayment of Loans
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).
Benefits of Corkscrew Structures
The main benefits of using these classical corkscrew structures are:
- Time clarity. It creates a separation between values (of a stock item) at the beginning of a period and those at the end. If only a single time point were referred to (such as “Quarter 1”), it may be unclear or ambiguous as to whether this is a starting or ending value.
- Nature of flow items. It separates flow items, especially those which are likely to be of a different nature to each other. For example, in the case of the plant and machinery calculations, the capital expenditure is a cash flow item, whereas the wear and tear (depreciation) have no effect on cash flow (although it could be considered an expense which reduces profits and taxes due). The distinction between cash and non-cash items is of course very important for many reasons.
- Integration of model parts. The separation of flow items (which are typically calculated in separate parts of the model from each other) allows these items to be brought together in a step-by-step calculation.
- Flexibility and transparency. The above reasons also lead to the structure being transparent and flexible.
Exercise: Please build the Loan model shown above from scratch!