Newsletter February 2010
|
- Spreadsheet Tips - Flagging
late payments.
Cashflow is crucial to all businesses. It is important to keep on
top of who owes you what and when it is due. Excel can quickly
identify those customers who have not paid on time. You can follow
the example below using your own data. Alternatively, the sample
data included in this example is available as a free
download.
|
|
Flagging Late Payments
|
|
Using Excel's IF function allows us to quickly identify
whether a row of data matches our criteria, or not, in this
case; Has the bill been paid?
|
|
|
If you have a list of sales orders with different payment dates
and terms, you could spend a lot more time looking through them
manually, instead of letting Excel do the work for you. Fig 1
shows a list of sales orders with a mixture of payment terms.
Some are thirty days, some are seven days and so on. Also, some
are paid and some are not. In this spreadsheet tip, we will show
you a single formula which will show you how to quickly identify
those people who are late at paying you.
|
|
|
Fig 1
|
|
|
|
|
In Fig 1, the orders can be one of three statuses; Paid, Outstanding
or Late. To see if it is paid or not, we first check for a date
in the 'Paid Date' column. If there is no date there, then the
order invoice is still unpaid. We then check the due date
against today's date to determine if it is oustanding or late.
To do this we used a 'nested IF statement'. To start though, a single
IF statement determines whether the payment is late or not.
In cell F2 we write:
|
|
=IF(E2="","Paid or Outstanding","Paid")
|
|
|
In English this reads as:
|
|
IF E2 is blank (empty inverted commas) then the invoice is paid or outstanding,
otherwise (i.e. there is a paid date), the invoice is paid.
|
|
|
Copy this down column F for the rest of the rows to get
the same as Fig 2.
|
|
|
Fig 2
|
|
|
|
|
We now need to differentiate between those unpaid
invoices which are Outstanding, and those which are
Late. To do this, we put another IF statement
inside the current IF statement. This is called 'nesting'.
We amend the formula in cell F2 to read
|
|
=IF(E2="",IF(D2
|
|
|
In English this reads as:
|
|
IF E2 is blank, check if the due date (D2) is before
today (the constant TODAY()). If it is show "LATE", otherwise,
show "Outstanding", and if E2 is not blank show "Paid">
|
|
|
We copy this down the rest of the cells in F colum to get
Fig 3.
|
|
|
Fig 3
|
|
|
|
|
As a final touch, we can use some Conditional Formatting
to draw the eye to LATE orders. First, highlight column F.
Then select 'Conditional Formatting' from the FORMAT menu.
This brings up the 'Conditional Formatting' dialog box.
We enter two Conditions; Bold and Red for 'LATE' and
Grey for 'Paid', as in Fig 4, then click OK.
|
|
|
Fig 4
|
|
|
|
|
We now see that the late unpaid invoices are now flagged up
with a red, bold 'LATE' next to them. Paid invoices are
grey. With a long sales order sheet this will allow you
to immediately see the unpaid orders, as in Fig 5.
|
|
|
Fig 5
|
|
|
|
|
The constant TODAY() will change value according to the
day on which you open the spreadsheet, so the Status of the
orders will update as well. This is a useful tool which you
can keep using into the future, and keep on top of late
payments.
|
|
|
The sample data with formulas and formatting is available as a free
download. Remember
to always virus scan any file you download from the internet.
|
|
| ..back to top |
