As I was reading through the Excel-l discussion list, this caught my eye:

Wednesday, October 24, 2001 5:14 PM
Subject: PRESENTATION

Good Afternoon all,

I'd like to bounce this idea of you all. I want to be able to relay financial information to financially illiterate people in my organisation. One idea I have seen several times is the "dashboard" ie gauges showing performance against targets. I would like to incorporate this into excel in some way. Any ideas much appreciated

Tony Gee
Financial Controller
Trident International Ltd

Ever the one for a fight, I asked

Send me an example, Tony, and I'll give help you with your quest for management by eye. Three examples will do and they can be simple or extensive data sets. Include actual and budget data and variances if you wish.

Duncan Williamson

OK Duncan

The three key areas of our business are freight forwarding, warehousing and transport and each division has specific KPI's(Key performance indicators).

Freight forwarding	No of jobs (Y-T-D)
Actual	14,265
Budget/Plan	15,500
Average Margin per job
Actual	£126
Budget/Plan	£110
Warehousing	% Occupany
Actual	81%
Budget/Plan	85%
Transport	Revenue per vehicle
Actual	£55,250
Budget/Plan	£45,000

You get the idea Duncan. I've researched on the net and a site at http://www.idashes.net/execdash_web.asp where the sort of thing I'm after is detailed.

Many Thanks
Tony

I asked for a couple of days to think about this and work on it.

Sure, this was an idea I had during one of those incredibly frustrating meetings with the ops people that went something like this "Now tell me again, negative numbers are bad right"!

Thanks

This is my final response.

Dear Tony,

I let your problem rest a while and then put together a series of ideas for you. I have to say there is no way I could program a spreadsheet to provide you with the gauge graphs you pointed me at. However, using my knowledge of accounting and Excel, a bit of research and some lateral thinking, I suppose I really turned your request into a small communications project.

There is scope for some basic graphs/charts for this exercise; but I assume these are no barrier for you and didn't want to waste your time by presenting them. Therefore, I prepared a series of graphs that use additional techniques that then shout out something like NEGATIVE IS ADVERSE IS BAD ... as appropriate ... to help you to explain to your lads what's what!

You will see that each variance will need to be programmed in order to trap whether it is adverse or favourable since the treatment of revenue variances is different to cost variances, as in real life, as you are well aware!

I have used

• IF statements
• REPT statements
• Conditional formatting
• Thermometer graphs
• Pictograms

in my quest to make you happy and get your men (and women) on board.

I hope the file I have attached is useful.

Your feedback is welcomed and I will put my efforts on my web site in one form or another ... I'll attribute the motivation to you of course and will send you a message so that you can see what I've done. Home Page: www.duncanwil.co.uk

Best wishes
Duncan Williamson

And here it is ... the file I prepared

Duncan Williamson
28 October 2001