In a financial model you need to plan balance sheet positions. There are various techniques to do this, and the most suitable technique depends upon the balance sheet position. For fixed assets and loans, a BASE corkscrew can be ideal.
BASE stands for
- Beginning balance = End balance of prior period
- + Additions
- - Subtractions
- = End balance
For fixed assets, the additions represent CapEx, and the subtractions represent depreciation. Subtractions could also represent sales of assets but as a general rule, we do not plan to dispose of assets, but simply replace them (CapEx) as they become fully depreciated.
For loans, the additions represent loan drawdowns, and the subtractions represent loan repayments. Note that interest does not affect the loan balance (unless you do not pay the interest but add it to the loan balance, a so-called PIK loan = payment in kind). The interest is a cost which flows into the P&L.
So what does a BASE calculation look like in Excel and why is it called a corkscrew?
The screenshot shows an example for fixed assets. I selected the closing balance figure for the first year and used the “Trace Dependents” button repeatedly (this can be found on the Formulas ribbon under “Formulas Auditing”). The zigzag corkscrew effect of the data flows is clearly visible.
