NEGATIVE CASH SITUTATION IN YOUR FINANCIAL MODEL DEAL WITH IT IN THESIMPLEST WAY!
When preparing Financial Projections, one of the most important things is projecting the Cash Flow statement. At the end of Cash Flow projections, you may have a positive or even negative cash balance forecasts.
Cash balancing mechanism is a very important step in the preparation of a financial model and often difficult too! As a best practice in Financial Modeling, definitely avoid hard-coding the balancing figure in the Cash & Bank line, just to balance the Balance Sheet! Best practice is to forecast the cash flow statement, and link the closing cash balance from here into your Balance Sheet.
In this article, I have tried to explain in simple manner how to deal with a situation of either negative or surplus cash while preparing a financial model or Financial Projections in MS Excel.
THE SITUATION (NEGATIVE CASH BALANCE BEING PROJECTED)
The cash balance available from the cash flow statement could either be a positive number or a negative number. Now, if it s a positive cash balance then we need to take it to the Assets side on the Balance Sheet. If it s a negative balance then such situation in reality doesn't exist until it has been funded in the form of either a bank overdraft or a cash credit or the promoter funds. That's why a negative cash balance is to be represented on the liability side of the business.
So, in our model, depending upon the sign (+ or -) of the cash balance cell, it should be included on the assets or liabilities side i.e. the assets should get populated when the number is positive and liabilities should get populated when it s negative.
SOLUTION WITH IF FUNCTION IN EXCEL
Now, how to translate this understanding into preparation of model in MS Excel We can make use of IF formula for that. If the number, that is the cash balance (CB) in the sheet, is more than or equal to zero, then assets get populated; otherwise it is zero.
Check-out the video here:
So the formula on the Asset side (Cash & Bank Balance Cell) may look like:
=IF(CB>0,CB,0)
The formula on the Liabilities side (Bank Overdraft Cell) may look like:
=IF(CB<0,-CB,0)
So for example, if the number is Rs.100, assets side will be up by Rs.100 and the liability side value will be zero. We will add one more if formula, wherein if this no. is less than zero then the no. is shown on the liability side but with a changed sign or zero. So, if the balance becomes ( )Rs.100, then the assets become zero and liabilities increase by Rs.100. So that is the simplest way of dealing with positive as well as negative cash balance in your model.
REALITY CHECK AND RELATED COMPLEXITIES!
However, in reality, when you have a surplus cash balance of 100, that generally isn't kept idle by the business. Any finance manager or businessmen with a good business acumen and prudence will invest this cash which will fetch that business a minimum of return on investment.
Similarly when there is a negative cash balance, the source which will provide such funding will not provide such funding for free.
As result, there will be an interest expense on such balance which is similar to an interest income on the surplus cash balance. Both of these situations need to be figured out and dealt with while preparing the detailed advanced financial model.
For more details on Cash Balancing mechanism and advanced financial modeling, do consider signing up our specialized program here
CAclubindia
