Christmas is coming...

Everyone is subscribing to make sure they don't miss
out on their exclusive Secret Santa Gift

Are you all ready for Christmas? If you’re one of those people who have had all of your presents in for weeks, I don’t want to know – it only makes me feel inadequate!

One present I have got ready though is my exclusive Secret Santa gift for my subscribers, which will be emailed out on Christmas day.

If you haven’t already subscribed, do it now in the box on the right of the blog - so Santa doesn't miss you out!

Today's post is a little different, I just wanted to pick up on some of the feedback I received from my post a couple of weeks ago, asking what you wanted to learn from the blog.

Thanks again for the response, it's great to know that so many people are appreciating my posts and to find out what you want to see in the future. After all, a blog is worth nothing without its readers!

Last week I picked up on Bob's request to learn how to prepare a simple cashbook - I hope that did the job for you Bob!

Some other requests, which I think will make their way into future blog posts are:

• A few of you wanted to know more about pulling data into Excel from accounting systems, such as Sage. I have posted on this before, but didn't really cover the detail
• There were a number of requests for more on charts (including sparklines) and pivot tables
• Another popular request was a simple cashflow forecasting spreadsheet

There were also a few things requested that I think I have covered in earlier posts, although they may benefit from a revisit:

• Rolling Graphs
• Dropdown Lists

I also received a couple of requests for more on VBA, however this is an area I intentionally avoid on the blog, as the readership is primarily made up of Excel users rather than programmers. VBA is a massive area in itself, which has much more to do with programming than with Excel. In my experience, most Excel challenges can be handled without VBA, as long as a little creativity is applied.

There are many blogs out there that do a very good job on VBA, for those who do want to read more on it.

I also received a few meatier requests that go beyond a single blog post, and these might be more appropriately addressed in future video training packages, for example:

• Automating the management accounts process
• More depth on my OAP approach

Thanks again for all the feedback, keep it coming! 

Well, if you're are one of those that still haven't subscribed, that’s it for 2012. Unless you go to the top of the blog and subscribe now.

If you have subscribed, you’ll be hearing from me on Christmas Day, with your exclusive Secret Santa Gift.

Either way, have a fantastic Christmas break and I look forward to continuing our journey together in 2013.

Back to the Christmas Shopping!

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".

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".

Excel - What would you like to learn?

How would you like a future blog post to provide help on a subject of your choosing?

I am working on planning subjects for blog posts for the new year and would really appreciate hearing what you would like to learn about (as long as it is Excel-related!).

Just fill in your request in the form below (along with  a couple of other bits of information that will help me to put it into context), and I will use the responses to guide me when planning my future posts.

For those of you who would rather see an Excel Tip in this post - I haven't forgotten you. There is a quick Excel Tip  for you - when you complete the form.




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".