The Cash Plug – a Dicey Shortcut
by Anthony Nassar

This article pertains primarily to financial models that are custom-built in spreadsheets and that are prone to data integrity vulnerabilities. This discussion may not be relevant to cash balances derived by robust accounting or financial planning applications that include adequate safeguards protecting data integrity.

As you prepare a budget or a financial plan, you’re tasked with having to project the single most vital sign of your business: its cash position over the life of the plan. A well thought-out model coupled with solid assumptions will help the accuracy of your projections including the cash position. However, the use of the cash plug technique to derive the cash balance could introduce errors or aberrations in the results, and therefore “spoil” an otherwise good financial model.

I will explain what I mean by the cash plug technique using an actual example, but first, let me share with you the methodology I use to derive the cash balance in my financial projections. It is a multi-step process which consists of:

preparing income statement projections over the planning horizon
projecting every component of the balance sheet other than cash over the planning horizon
preparing a statement of cash flow projections over the planning horizon that provide the cash position for every period in the plan
feeding the cash position for every period from the statement of cash flows back into the balance sheet.

To illustrate the above methodology, let’s take the example of a company for which we are trying to project a cash balance as of December 31, 2006. We already have a balance sheet as of September 30, 2006 and have projected the net income for the three months ending on December 31, 2006 to be $83,697.

Using various assumptions for each item on the balance sheet other than cash (accounts receivable, prepaid expenses, accounts payable, etc…) we are able to project their corresponding balance as of December 31, 2006. We now have all the items on the balance sheet as of December 31, 2006, except for the cash balance. Using this data, we can develop a statement of cash flows for the three months ending on December 31, 2006, providing us with an ending cash balance of $152,356 as of December 31, 2006. This cash balance is then fed back into the balance sheet with that same date. Below is an illustration of both the statement of cash flows and balance sheet described in this example.

Please note that when using this methodology, you are not able to have a complete balance sheet as of December 31, 2006 until you have developed the statement of cash flows for the three months ending on December 31, 2006 and derived the ending cash balance first.

So what is the cash plug technique?

Rather than follow the methodology I described above, the cash plug technique is a shortcut which consists of computing the cash balance by subtracting the sum of all the assets on the balance sheet, excluding cash, from the total liabilities and stockholders’ equity. This computation is compliant with the basic formula of a balance sheet: Assets = Liabilities + Equity.

In the above example, the cash plug technique would yield the same cash balance as of December 31, 2006 of $152,356 that we derived previously. So that’s good news. But here’s an example of where things can go wrong. To project Retained Earnings (labeled Accumulated deficit in the example) as of December 31, 2006, you need to add to the Accumulated deficit balance as of September 30, 2006 the net income figure for the three months ending on December 31, 2006. Suppose for example that in making this projection, you inadvertently select in the spreadsheet the wrong cell which contains the figure $60,000 instead of the actual net income figure of $83,697. The Accumulated deficit figure as of December 31, 2006 would then be erroneously projected as ($4,800,585) instead of ($4,776,888), and the cash plug would return the erroneous value of $128,659 instead of $152,356 (see figure below). For the unsuspecting reader, the balance sheet is balanced and the cash balance may look credible. The problem with the cash plug is that any error in one or more items of the balance sheet will automatically trigger an error in the cash plug by design, since all these figures are constrained by the formula: Assets = Liabilities + Equity.

Using the methodology I described at the beginning of this article, and assuming the statement of cash flows is built using the correct net income figure, the error in the Accumulated deficit figure as of December 31, 2006 would be uncovered because the cash balance would be the correct one on one hand, but the balance sheet would be out of balance, pointing to a problem with the data that needs fixing (see figure below).

You may be wondering: what’s the likelihood that one makes the sort of mistake regarding the selection of the incorrect net income cell in the above example? To that I’ll say that when you develop models with hundreds or even thousands of cells, it’s not impossible to inadvertently pick the wrong cell and end up with errors that could affect any balance sheet item, hence triggering problems when using the cash plug. Error is human!

Of course, in the above example, we could have made the same mistake regarding the selection of the incorrect net income cell both in the balance sheet and the statement of cash flows. We would have then ended up with the incorrect cash balance when both the balance sheet and statement of cash flows would seem to be in agreement. To that I’ll say that good models need to have built-in safeguards and validations to ensure that data is reconciled across the spreadsheet. In our example, the mistake would be discovered when the accumulated deficit is reconciled against the cumulative net income or net loss figures in the plan.

In summary, the cash plug is a convenient method to derive the cash balance. Data integrity issues, however, could yield the wrong results when using that method. When developing models, be cognizant of the limitations of the cash plug approach and when using it rely on effective validation techniques to detect any potential errors that could result from its use.

This article was first published in the November 2006 issue of our e-zine, Propel Your Venture.

Show me the list of Articles on this website

Disclaimer: The information in this article (the "Information") is current as of November 2006. The Information is intended solely to illustrate general concepts and guidelines on various business subjects. It may not apply to specific situations. The Information does not constitute accounting, financial, tax, legal or other professional advice. You are urged to consult with a qualified professional who can understand your specific situation and advise you accordingly. No Information creates a warranty. All Information and links to other websites are provided on an ‘as-is’ basis without any warranties, express or implied, including warranties of merchantability or fitness for a particular purpose. In no event shall Venture Momentum, Inc., its authors, publishers, contributors and editors be liable for any indirect, incidental, special, consequential, or punitive damages of any kind whatsoever arising out of your use of this article, the Information, and/or links to other websites regardless of the cause of action.