Corkscrew Calculation

What is Corkscrew Calculation?

When hearing the term "corkscrew method" for the first time, we thought it was something related to wine bottles, but instead, it turned out to be a calculation method for balances in the financial sector.

These calculations, also known as roll-forward calculations or corkscrew accounts, enable coherent modeling. In this computation, the ending balance from the prior period is essentially used as the beginning balance for the current period.

Every equilibrium has common characteristics. A definition of those characteristics and a universal model element that may be applied to all balances will be provided.

We require the following elements to represent balances.

The equation is: 

Balance BEG ( Initial balance) + Upward flow – Downward flow = End balance

Where

• Beginning balance: Always equitable to the ending balance from the prior period is the beginning balance for the current period
• Upward flow: A flow causes an increase in the initial balance. For instance, capital expenditures (CapEx), added to the initial PP&E balance, are upward flows in the PP&E calculation
• Downward flow: A flow that brings down the starting balance. For instance, Depreciation is a downward flow in the PP&E calculation because it is deducted from the starting PP&E balance
• Ending balance: These three elements are used to calculate the ending balance. First, every upward flow is added to the starting balance, and every downward flow is deducted from it. The current period's ending balance is carried forward and used as the starting balance for the subsequent period

Understanding Corkscrew Calculations

Corkscrew calculations are a standard modeling element that enables consistent modeling of balances. It takes care of the four elements we've already seen and adds the capability to "inject" a starting balance into the corkscrew at a particular time, such as a "last actuals" balance. 

With Dynamic Arrays, the CORKSCREW function offers an alternative to Excel iterative calculations and circular references for straightforward roll-forward measures.

These computations begin with an opening balance and add positive and negative streams to get a closing balance. This closing balance then rolls forward to form the opening balance for the following column. 

The flows may not depend on the opening balance, or they may. Only simple mathematical operations are permitted when relying on the opening balance (for instance, for calculating interest).

Excel corkscrew can be performed in Dynamic Array utilizing regular (non-spilling) references. In addition, iterative calculations can be used to calculate circular references produced by corkscrew calculations employing dynamic array references.

The calculation, also referred to as roll-forward computation or corkscrew accounts enables the accurate modeling of balances in financial modeling. 

Essentially, the previous period's ending balance is utilized as the current period's beginning balance in this calculation (the ratio is rolled forward).

The ending balance in one period becomes the opening balance in the following period, giving your formulas a type of corkscrew logic. We can imitate this behavior with some imagination by computing the net changes in a balance and accumulating all of these.

How to use corkscrew calculation in Excel?

Given that Microsoft Excel is the primary tool used in financial modeling, it is essential to understand how to include corkscrew computations in an Excel-based economic model.

By following the procedures required to generate the depreciation schedule, we can learn how to make a calculation in Excel:

• Determine the calculation's four components: Depreciation (downward flow), CapEx, PPE Opening Balance, and PPE Closing Balance
•  List each component in a separate column. Use the phrases Plus and Less to denote whether an upward or downward flow adds to or subtracts from the opening balance for each upward and low flow

In this scenario, the model reader can easily understand the reasoning behind the Closing Balance estimates.

Use hardcoded values for the historical periods by opening balance, upward flows, and downward flows. A formula should be used to determine the Closing Balance.

• It's essential to remember that the Opening Balance for the current period is the Ending Balance from the prior period. As a result, each Opening Balance for the periods that have been anticipated must be compared to the Ending Balance for the prior period. Therefore, the Ending Balance for 2018 is also referenced in the Starting Balance for 2019
• In the anticipated periods, avoid hardcoding the upward and downward flows (in our case, capital expenditures and Depreciation)
•  A formula should be used to determine the Closing Balances

Opening stock plus output minus closing stock equals sales, which is the primary formula for this budget. Therefore, the opening store for this month is also the same as the closing stock for the previous month. That is a simple relationship.

example of corkscrew calculation

A corkscrew structure will be used to model a balance whenever possible. To increase efficiency and decrease error and thinking time, we once again aim to carry out tasks consistently.

Because Microsoft Excel is a standard tool in financial modeling, it is essential to understand how to integrate corkscrew calculations into an Excel-based economic model.

Let us assume that we are working with an accounts receivable account. The items used will be labeled as follows:

• Accounts Receivable at the Beginning
• Plus, the revenues, which are Upward flow in this case.
• Fewer expenditures, which is the Downward flow.
• Accounts Receivable Balance

If the ending balance of Accounts Receivable in 2021 equals 500 thousand, this number will represent the opening balance for 2022.

As a result, the Closing Balance from the prior period must be used as the reference point for each Opening Balance in the forecasted periods. A formula should be used to calculate the Closing Balances.

Closing balance= Balance BEG ( Initial balance) + Upward flow – Downward flow 

Another Excel illustration of the calculation. We will clarify how to create a depreciation schedule by going through the following steps:

1. Determine the four main components of the corkscrew calculation. Property, plant, and equipment Opening Balance, Capital Expenditure (upward flow), Depreciation (downward flow), and Property, plant, and equipment Closing Balance are the components in our case
2. List every element in a single column. Use the words Plus and Less to denote whether an upward or downward flow adds to or subtracts from the opening balance for each upward and low flow. In this scenario, the reader of the model will be able to clearly understand the reasoning behind the Closing Balance calculations
3. Utilize predefined numbers for the historical periods for Opening Balance, Upward Flows, and Downward Flows. There should be a formula used to determine the Closing Balance

When to use The Corkscrew Calculation?

The calculations are essentially executed to enable logical and rational calculations/modeling. This calculation can be particularly utilized in building productivity packages and templates.

1. Utilizing the corkscrew builder in the Productivity Pack

To open the Productivity Pack dialog box, press Ctlr+Shift+6.

This keystroke brings up a dialog box where you can access a number of Productivity Pack macros. Due to their lower usage, these have been included in the dialogue rather than receiving a separate keystroke.

• Choose your preferred corkscrew structure: W denotes the initial balance, and Q denotes no initial balance. Let's pretend we want a corkscrew with an initial balance. We'd press W and call the Corkscrew "Accounts Receivable." When you press enter, the macro will add the corkscrew to the active sheet.
• Add the starting balance: The initial balance is used as an input to the corkscrew. You can either enter the value directly here or move it later. Alternatively, you can substitute this with a link to an existing initial balance on the input sheet.
• Replace the placeholder for the timing flag: We want the initial balance of a balance corkscrew to be injected at a specific point in time. In this case, the transaction date or close financial date would be when we would want to recognize the initial balance. As a result, we'd replace the placeholders for the timing flags with a link to the Financial Close date flag.
• Give the upward and downward flows new names: You will need to consider the relevant flows for each balance you model. For example, the upward flow in the case of accounts receivable would be the endorsement of invoices, which we have estimated as revenue from electricity. The money obtained when these invoices are paid would be the download flow.
• Connect any formed flows: We can link revenue in our example because we have already modeled it. However, we would leave this as a placeholder for the time being since we have not yet modeled the cash received.

2. Using corkscrews from templates

Prior to including the automated corkscrew builder in the Productivity Pack, using template corkscrews was our predominant mode. We would copy one of the template corkscrews from the Tmp sheet whenever we needed a new one. 

The [xyz] in the labels would then be replaced by pressing Ctrl+h. 

The majority of financial models have numerous balances. The ability to swiftly assemble a typical corkscrew is crucial.

We advise keeping a "template" or empty corkscrew in your model's Tmp sheet. You can duplicate this template and use it each time you need to put up a balance calculation. You must do the following each time you copy the template:

• Modify the labeling
• Update the following components' links
• Upward movement
• Declining flow
• Opening balance

Conclusion

Corkscrew calculation represents a thorough and useful financial model that provides an easy way to absorb the new information generated.

The calculations ensure a model's consistency and integrity. Furthermore, they offer the model's reader a simple method for absorbing the included information.

Every corkscrew calculation includes the four components listed below:

1. Beginning Balance: The current period's beginning balance is always equal to the previous period's ending balance.
2. Upward flow: A flow that adds to the initial balance. For instance, capital expenditures (CapEx) are upward moves in the PP&E calculation because they are included in the opening PP&E balance.
3. Downward flow: A flow that reduces the starting balance. Depreciation, for example, is a downward flow in the PP&E calculation because it is subtracted from the initial PP&E balance.
4. Closing Balance: These three elements are used to calculate the ending balance. The opening balance is increased by all movements that are upward and decreased by all movements that are downward. The current period's ending balance is then updated and used as the starting balance for the next period.

Financial models that are detailed and meaningful must include corkscrew calculations. The calculations ensure a model's consistency and integrity. Furthermore, they make it simple for the model's reader to absorb the information contained.

Corkscrew accounts are commonly used in financial models in various supporting schedules such as PP&E schedules, accounts payable schedules, and retained earnings.

Free Resources

To continue learning and advancing your career, check out these additional helpful WSO resources:

• Advanced Excel Formulas
• Group in Excel
• How to make a Graph in Excel?
• Scatter Plot
• XNPV Function