Avoid a Liquidity Bottleneck

18 06 2014

To avoid a liquidity bottleneck at the end of a month you can use Apple’s iWork app Numbers. Once you created an appropriate spreadsheet with the necessary information you can predict your account balance more or less accurately (if the rare event of a crashed iOS device doesn’t happen and you are out of warranty claim).

Here is an example to create the spreadsheet using functions implemented in Numbers and some other tricky algorithms. The good news is, it’s synced with all your other devices via iCloud. So the information is available wherever you are.

The components …

What you need is

  • Apple’s iWork app Numbers
    It’s part of Apple’s iWork suite, the pendant to Microsoft’s Office suite with one important difference: it’s optimized for touch screens and seamless to use even on the small screen of an iPhone and all your documents are automatically synced with all your devices. You can even work on your documents if you don’t have access to one of your Apple devices because there is iWork for iCloud with which you can work on documents via browsers like Safari, Google’s Chrome, or Microsoft’s Internet Explorer.
    And it’s free for newly purchased devices.
  • A spreadsheet within Numbers
    which allows to predict the account balance
  • Access to your bank account
    to look up the transactions and the actual balance.

If you are not familiar with programming a spreadsheet don’t hesitate to download my template from my Box account. It’s free and there is no copyright on it. If you downloaded the template you can (easily) adapt it to your needs. The following description might help.

The spreadsheet …

The template contains two essential tables.
The first table is designed to enter the actual account balance, the performed transactions as well as periodical transactions. I call it the Dynamic table

The second table contains the dates for periodical transactions like rents or taxes to be paid in advance. I call it the Static Table.

Here is how it looks like:


And here is the download link

Application File format
Apple Numbers numbers


If you tap on the link and downloaded the file you will see a control ‘Open in Numbers’ for a short time. If the message is gone just tap on the display again.

If you want to work with the template it’s highly recommended to do it with a copy of the original.

The dynamic table …

To predict the approximate liquidity at the end of a month you have to enter the actual account balance and monitor what’s expensed during the month by accessing the transactions with an online banking tool or a browser.


The spreadsheet contains the necessary formulas to add or subtract incoming and outgoing money transfer. For cash payments (food, has, etc.) an approximate amount can be set on a weekly basis.


Every time you get fresh cash from your bank you can reduce the remaining value with a so-called ‘Stepper’ control.
A Checkbox is used for payments on a monthly basis e.g. your health insurance. Just set it to true if the payment is expensed.

The static tables …

The first table is just for calculating a date 30 days prior to the actual date to point to an upcoming payment. The second table is for documentation purposes only and not used in any calculation.

The EOMONTH function returns a date 30 days prior to the actual date.


This the conditional statement which sets the factor to -1 for all months (3, 6, 9, 12) with a due date. It can be easily adapted to other periods resp. months.


This simple conditional statement sets a marker to emphasize upcoming payments.



The kernel statement (image 2) sets the factor to -1 according to due dates.
Factor x Base Value defines the content in the column ‘Value’ which is part of the summarization to get the total liquidity.

The adaption …

You have to adapt the template by entering all the necessary values. A look on your last statements of account helps to find out the values. Some of them will be fixed values others vary and you have to define an average value.

You can use the COPY+PASTE feature to add more lines of similar type. In the context menu select ‘PASTE FORMULAS’ to let numbers adapt the clipboard content.

Summary …

If you run into the red it’s not my fault.
I’m sure the reason is your optimism bias or a shaking finger followed by wrong entries.

Related links …

An invoice created with numbers

If … Then … Else

Thanks for calculating with iNotes4You.



%d bloggers like this: