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:

20140314-185914.jpg

And here is the download link

Application File format
Apple Numbers numbers

Notes

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.

20140314-190023.jpg

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.

20140314-190850.jpg

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.

20140314-190209.jpg

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.

20140314-190303.jpg

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

20140314-190352.jpg

Summarized:

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.





A jazzed up checklist

26 10 2012

If you use Apple’s app ‘Numbers‘ (the easy but still powerful equivalent to Microsoft Excel) you can use a predefined template for a checklist. It’s very simple, maybe too simple.

Here you can see a jazzed up version.
You will get a warning, if the deadline falls within a period (entered in days).

Just download the checklist for free and use it.
The NUMBERS-Document contains a short documentation.

20121008-141900.jpg Jazzed up Checklist
Properties 2012/10/26 EN .NUMBERS iDrive
Alternative Download from Dropbox

20121027-094251.jpg

There are two tables.

  • Table 1 contains today’s date.
  • Table 2 contains your entries for deadlines, the number of days before deadline/warning and your tasks.

The document displays the number of days to deadline and a red point if today’s date and deadline are within the period.

If you have any questions do not hesitate to contact me.

By the way…
The blogger is not liable if you use the checklist and miss your wedding anniversary.








Follow

Get every new post delivered to your Inbox.

Join 183 other followers

%d bloggers like this: