Introduction
Now if you have been following Video Financial Modelling for a while, you should know what a corkscrew account is. For those of you who haven’t been following us you’re about to find out how corkscrew accounts can help you to model everything from financing facilities, ledgers all the way through to depreciation accounts.
So what is a corkscrew account?
A corkscrew account is simply a method by which the opening balance of the current period is equal to the closing balance of the previous period. This is probably best illustrated by an example.
Say we had a financing facility with a closing balance of $50 at the quarter end 31 Dec 2011. What would be the balance for the period starting 1 Jan 2012? If you guessed $50 then you’d be right. Now let’s ramp it up a notch.
Carrying on from the above example say that you drew $30 from the quarter starting 1 Jan 2012 and ending 31 Mar 2012. What would be the closing balance of the facility if no amortisation or interest capitalisation took place? If you answered $80 ($50 for the opening balance and $30 for the drawdown) you’d be correct.
Now imagine we had the $30 drawdown as per the last paragraph but we also had a repayment of $20 (Note: that it is very unusual to drawdown on a financing facility and amortise it at the same time). What would be the closing balance? It would be $50+$30-$20 = $60. This is represented as corkscrew account in figure 1 below.
Figure 1 – Example of a corkscrew account
Keeping convention to make your life easy
As you can see in Figure 1, Video Financial Modelling has a simple convention when modelling corkscrew accounts. All additions are positive and all deductions are negative. Simple I know, but we have seen many different styles of doing this. Doesn’t it seem logical to have inflows as positive numbers and outflows as negative numbers?
Let’s look at an example
Given the below company net profits and dividends, find the retained earnings opening and closing balances for the period if the retained earnings balance at period 0 is zero.
Ok, so firstly we need to setup a corkscrew account. As with any good corkscrew you need to link the opening balance of the current period with the closing balance of the previous period.
Secondly you can find the closing balance by summing all the rows above. Don’t worry if there are no numbers for the net profits or dividends. They will come soon. The important thing is that you stick to convention and make sure that corkscrew outflows are negative numbers and corkscrew inflows are positive numbers. It is very hard to make a mistake if you follow this convention.
Finally add the net profits and positive numbers and the dividends as negative numbers. Remember the formula for Closing Retained Earnings?
Closing Retained Earnings = Opening Retained Earnings + Net Profits - Dividends
You should come out with the following where the period 10 closing balance is 93.
What else can you apply this concept too?
As mentioned previously you can apply this to many different situations. A few examples come to mind including common ledgers, financing facilities, depreciation accounts. The list goes on and on.
Like this article? Check out the Excel and Financial Modelling Training Courses at Video Financial Modelling.
