The classical corkscrew calculations described in the last section are useful to calculate the level of a stock item, where the logic used is that the stock level results from periodic inflows and outflows, and initial value.
There are many situations where it is the stock item that has already been forecast in some other way (i.e. not using the flows). For example, as in the earlier section, the amount owed by customers could have been forecasted using a ratio method (or days’ equivalent). In such cases, one would need to calculate the flow items from the stock levels (for, if each were forecast separately, then the forecasts would be inconsistent with each other, in general). For example, the change in the level of amount owed during a period is equivalent to the value of invoices issued to customers less the values invoices paid by customers within the period.
This calculation approach is sometimes called a reverse or implied corkscrew. The key differences with a classical corkscrew are:
As an example, using the previous model in which the amount owed by customers is derived from sales-days’ equivalent, one could create a (reverse or implied) corkscrew structure, beginning by using the period end values as the first line item (which is already known for all periods, and so can be fully built without needing further inputs, as shown by the vertical dependency arrows):
The second line item is the starting values, which are brought forward from the ending values in a way analogous to a standard corkscrew (see image above).
The third line item is the difference between these two: The change within the period represents the net increase in the amounts owed:
These formulas can be copied across to the other time periods:
Note that the dependency arrows within the implied corkscrew in the above image do not form a single continuous set, but rather are disjointed.
From a visual perspective, the implied corkscrew can be used to create what looks more like a standard corkscrew: In the following image, the rows of the implied corkscrew are structured in the classical order. Note that the calculations are exactly the same (i.e. the ending values are still drawn from the main calculation area, the starting values are brought forward from these, and the net increase is calculated as the difference between them) – the only difference is the layout of the order of the calculations, in which the net increase is calculated from an item which is above it (top-to-bottom) as well as one that is below it (bottom-to-top):
Where the “net flow” figure that is derived from an implied corkscrew is implicitly composed of items that are of the same nature, it may be sufficient to stop the process at this point, and to work only with the net figure. However, in some cases it may be necessary to split the net item into its “inflow and outflow” or “gross” components (or one may simply wish to do so, for extra clarity and transparency). To do so requires additional information or an assumption.
For example, on the assumption that the gross increase in amounts owed by customers is – in each period – equal to the sales invoices issued, then – starting with the raw implied corkscrew (rows 20-23 in the following image), one could set the gross increase in sales invoices (row 25) as being equal to sales revenue (row 4), and then deduced the implied amount that customers must have paid (row 26) in order that the ending amount be as calculated originally:
… then, by assuming the ratio of equipment value to sales revenue (perhaps based on historical ratios), one can forecast the value of the equipment. For example, if the period just ended is typical in terms of capacity utilisation, and the equipment value was $10m, then one could reasonably use an assumption for the ratio of 10% (i.e. the ratio of the values in cell D7 and D3 in the image below), which is multiplied by the Sales in each period, to give the ending value of the plant and equipment.
(Note that generally it may be slightly more logical to forecast equipment value that is required at the beginning of the period, rather than at the end. However, if sales growth is moderate, then equipment levels are likely to remain relative stable too. Also, as long as the ratio used for the sales-scaling is based on historical ending values (rather than starting values), then this estimation process is consistent and self-correcting (for example, the ratio of equipment-to-periodic-sales will be lower for starting values of equipment than it is for ending values, whilst the calculations would then forecast a lower value of equipment, since this would be the forecast of the period-start values). Further, it can be argued that a company would invest in additional equipment only after the need to do so has been proven not before, so that a forecast of the ending value could reflect this internal process).
In the above image, the order of the calculations has a “top-to-bottom” flow, but the visual presentation (ending values before starting values, net capital expenditure after both of these) is unintuitive. Therefore, one could of course alter the presentation, even as this would mean that the flow of calculations is less straightforward. In other words, if this is done, whilst the period start and end values are calculated as a first step (by direct reference and the ration of sales method), the net expenditure is calculated from these two with a mixed direction of flow (see column H):
In the case of net (capital) expenditure on plant and equipment, this is made up of cash and non-cash components: That is, net capital expenditure is “gross capital expenditure, less depreciation”, where gross expenditure is a cash item, but depreciation is not. Therefore, the splitting of the components would be necessary for many purposes (such as to perform cash flow analysis – from which depreciation should be excluded – as well as profitability analysis, where depreciation is included as a cost item, albeit not being cash).
Once again, to split the components requires an additional assumption. For example, if one assumed that the periodic depreciation was 20% of the value of equipment at the period start, then the calculations can be done in “top-to-bottom” format as shown in rows 19-22 (i.e. first the ending and starting values, then depreciation, then the gross capital expenditure):
… or the row order can be rearranged to create a more logical labelling order (even as the calculation of the gross Capital Expenditure becomes mixed-directional):