EXCEL TIP: A simple cashbook - the OAP way

Thanks everyone for your response to last week's post, telling me what you want to learn from the blog. I've had some great input and will be including some of your requests over the coming weeks. If you haven't yet submitted a request, I'd love to hear from you, just enter it here.

One request was from Bob, an accountant:

"I would like to know the simplest way to create a cashbook for my clients that enabled them to record their income and expenditure and be able to have a quick snapshot of their business- a poor man's QuickBooks if you like.
I have tried several times but end up with 100+ worksheets."

I have not seen Bob's spreadsheet, but I have seen many like it and the problem always comes down to the approach taken. Without knowing how to break an Excel job down,  we end up with a complex beast that still doesn't really do the job. This is why I developed my OAP approach which breaks any Excel job down into three steps:

O - Obtain the data
A - Analyse the data
P - Present the results

By breaking the job down like this, each stage can be focused on its purpose - providing the best input for the next stage.

The accountant's idea of a cashbook developed on paper, where all of these parts of the job needed to be done as part of the same "worksheet", otherwise you would be increasing the workload by having to rewrite information. This rewriting costs no time in a spreadsheet, therefore a different approach can, and should, be taken.

O - Obtain the data

For this job, Bob needs a simple data entry sheet where his client can enter each of the transactions, one row per transaction, in one long table, month after month, year after year. This is the best form to capture the data to make it easy to analyse.

The data entry is done in columns A to E. We will discuss columns F to I later in this post.

The following blog posts might be useful in understanding this approach:

99% of Excel users get this wrong - How do you lay out your data?

EXCEL TIP: How to make data entry screens easy to use

The Type and Expense/Income Name columns are dropdowns, Type being a simple dropdown with the choices being Receipt or Payment, the Expense/Income Name column dropdown being driven by an editable list on a separate sheet:

The Name column (A) is used to drive the list. The category column on this sheet is another simple dropdown (Income or Expenditure), which we will use later.

A - Analyse the data

Once the data entry sheet is in this format, we can use formulae in columns F to I, to calculate the additional information we need to provide the numbers that we will ultimately present.

Columns F and G use the YEAR and MONTH functions to strip out the year and month from the date field so that we can filter reports by these values.

Column H is a simple IF statement that returns the value if column B says "Receipt", otherwise it returns the value times minus one:

=IF(B2="Receipt",C2,-C2)

Column I uses VLOOKUP to return the category column for the categories list above.

P - Present the results

Finally, we use a Pivot Table to present the results:

You can now report on the data for any month or a whole year, from the dropdowns at the top and we have three worksheets - one for data entry, one for the report, and one to enter the information for the dropdown lists.

Although this is a very simple cashbook, the same principles can be used to both add additional data entry columns, and different reports - running off the same data list.

As a response to Andrew, another accountant who asked that I provide more examples of the spreadsheets used, you can download this one here.

There is protection on the data entry and list sheets that you will need to turn off to view the formulae, which you can do by selecting Unprotect Sheet from either the Review ribbon, or the Tools Menu (under the Protection subheading), depending on which version of Excel you are using - there is no password.

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".