Search Not Just Numbers

Saturday 1 August 2009

Excel Resources for Accountants

by Glen Feechan

Now that Not Just Numbers has been running for a while, I thought that it might be useful to pull together a number of the Excel resources available on the blog and from the Feechan Consulting Group.

Regular readers will know that I am a big fan of Pivot Tables and you can learn how to set one up with the following videos:

Free Excel 2003 Pivot Tables Video
Free Excel 2007 Pivot Tables Video

If you want to learn about Pivot Tables in more depth, there are two video training courses available from Feechan Consulting:

Introduction to Pivot Tables course
Advanced Pivot Tables Course

Sometimes a pivot table is great for doing the number crunching, but you wish to use the results in a more flexible layout - for example, a management accounts format. To do this you will need the GETPIVOTDATA function:

Excel Tip: Using GETPIVOTDATA (part 1)
Excel Tip: Using GETPIVOTDATA (part 2)

Pivot tables come into their own when analysing large amounts of data, which can come from existing software packages:

Why all finance directors need to know about ODBC

Or be keyed in:

Using Excel Pivot Tables with manual records

Often you need to look up data from elsewhere within a spreadsheet, maybe to add new data to the source data for a pivot table. To do this you use the VLOOKUP function:

Excel Tip: Using VLOOKUP
Learn VLOOKUP with this short, free video tutorial

A couple of other useful tips with many applications are given below:

Excel Tip: Dropdown boxes in Microsoft Excel
Excel Tip: Reporting by year or month

Although I try to avoid macros where possible, sometimes you just need to automate a repetitive task:

Free Video: Record a Macro in Excel

A number of useful functions are discussed above, but here is a useful way to get to grips with an Excel function:

Excel Tip: Use any Excel function in seconds

For a useful application that ties many of these techniques together, have a read of:

Do your management accounts take weeks, days, hours, minutes…or seconds to prepare?

If you still don’t feel you are able to produce the spreadsheet you want, or you don’t have the time, why not get us to do it for you – quickly and cost-effectively with Spreadsheets by Email?

Or if your requirements are more complex, including reporting from your own systems, call Feechan Consulting.

If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates, our monthly ezine and your free report.

Customer service: the role of the back office

What effect poor back-office customer service can have

by Emily Coltman

Some weeks ago I bought a new top from a chain of shops whose nearest branch to me is Newcastle. That’s 50 miles away from my home.

The top developed a fault and so, rather than take it back, I sent it to the chain’s national centre, asking for either a refund or a replacement.

This weekend, the replacement duly arrived, nicely wrapped in tissue paper and sent in a strong cardboard box.

But there wasn’t a letter to accompany it. Just a form written in gobbledygook, of which the only words I could understand were “authorised” and “returned”, and a flyer suggesting I recommend the company’s products to a friend.

If the new garment hadn’t had labels on it, I would have been unsure whether the chain had actually replaced it or just sent the old one back.

I’d say that’s poor customer service – and it would be down to the packaging department at the national centre. Not to a member of the shop-floor staff.

I’d have liked to have seen, in that cardboard box, a personal letter addressed to me, apologising for the fault, explaining that they’d put in a replacement, and assuring me that the replacement didn’t have the same fault and they would replace it or send me a voucher if one developed.
What I received was what I might call generic customer service.

Service where the systems in the back-office behind the sales front line are so rigid and inflexible that the customer ends up feeling like a hot dog. No, I don’t mean that as in a dog panting from this heatwave that Britain is suffering at the moment (hooray for the North of England and cooler weather says this expat Southerner). I mean the identical sausages.

Hugh Williams, in his book “Proper Coffee and Other Ways to Grow Your Business”, describes an occasion where a top industrialist wanted to change his car.

He first of all went to Garage A. He found a suitable car and the garage said that it would get back to him with a trade-in price on his old car. As far as we know, he still hasn’t heard back from it.

The next garage he approached was Garage B and the salesman, who told us the story, said that he took the car to be tested, which was full of petrol, out to the industrialist’s home so that he could try it for a day. The salesman then took the car to be traded-in back to his garage and when he returned it, not only had the old car been valeted but it too had a full tank of petrol.

The next garage to be approached was Garage C. The industrialist drove the car away on a trial run. Within three miles, the petrol light came on and when, a few miles later, he turned the car to go back to the garage, the windscreen washer light also came on.

At this point, the industrialist said to himself, “I can’t be doing with all this inefficiency from Garages A and C. I am definitely going to buy the car from Garage B as it knows how to look after me.”

The same thing as happened to that man at Garage C happened to me when I was looking for my second car. I had to put £10 of petrol in one car I test-drove, just to get it back to the garage. I didn’t buy from them.

This story also highlights the importance of how back-office staff can impact on customer service.

At Garage B, do you think it was the salesman himself who valeted the car and filled both cars up with petrol?

And at Garage C, would it have been the salesmen’s job to see that the cars available for purchase were kept filled with petrol and washer fluid?

No. In both cases it would be the job of the back-office team, the mechanics.

How can an FD help?

So what back-office systems could the finance department put in place to help improve customer service?

Here are a few of my suggestions.

  • First and foremost, remember that your customer is likely to be a layman, not an accountant. That means:
  • Use plain English. Make sure any letters to customers, any documents they receive such as quotes, invoices, statements, remittance advices, etc. etc are written in plain English and clearly understandable.
  • Explain any figures you supply. A layman won’t know what on earth a “debtor days” ratio is. Show how it’s worked out and explain, in plain English of course, what it means.
  • Use graphs, especially bar charts, line graphs and pie charts, to illustrate your figures. A picture is worth a thousand words. And make them colourful – no boring black and white.
  • Remember your customers could be internal as well as external. If you’re supplying a monthly report to the Board of Directors, the Board are your department’s customers. (Honest.)
  • Try and make it fun for your customers to interact with you. I know you probably won’t be able to make like the fish market staff in Fish! and start throwing fish to each other, but hey, if this guy can make the safety briefing on an aeroplane fun…


So yes, it is vitally important to train your front-line staff in good customer service.
But it’s just as important, if not more so, to set up systems for your back-office staff to make sure that they support your customers and make them feel special, too.

Because without good back-office systems, sales are lost. As the car story proves.

If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates, our monthly ezine and your free report.