Management information specialist and Excel detective
Jennifer Deacon
Jennifer is the Management Information Specialist, Excel Detective and Microsoft Trainer at Deaconsulting. Find out more by visiting www.deaconsulting.co.uk
Homepage: http://www.deaconsulting.co.uk
Posts by Jennifer Deacon
Efficiency – saving time is saving money
Jul 11th
By working with you to review your business processes, improve your spreadsheets and provide a more streamlined workflow I can help make you more efficient. Don’t switch off, this really is important stuff – there’s never been a more important time to be working as efficiently and as effectively as you can. Being more efficient might not seem on the face of it to be as important as bringing in the money but time is money said Benjamin Franklin.
Let’s put it another way, if I can save you just a minute on a task that you do twice a day, then over the course of the year that will have saved you a whole working day. All those wasted seconds moving between spreadsheets, retyping in data or copying and pasting may not seem like much but they add up and eat into your core business activity, whatever that might be.
Recently I’ve done some Excel detective work with a client, helping them to improve a process that typically took around 3 hours a month. By making some changes to the spreadsheet and ensuring that they understood a few particular excel commands we’ve dropped that to just 15 minutes. Apart from the fact that they were now no longer dreading the task the time saving is around 4 ½ days a year.
If time is money, what’s your hourly rate? Can you afford for your staff to be spending time unnecessarily on tasks which could be done quicker and more effectively? I’m happy to come in and take a look at your processes, spreadsheets that you use and how they interact with other Microsoft products and your other software and take a look to see if I can help save you money.
A good friend (Leighton, from Maison Jaune) pointed me in the direction of a recent blog post from Seth Godin, a well known American entrepreneur. It’s a short blog post and is on exactly this topic. You can read it here – and then, back to work. Time is money don’t forget…
What’s your Excel problem?
Jun 2nd
One of my goals on this blog is to have some posts on how I can help you take control of your spreadsheets and data, together with real excel hints, tips and guidance which can help you be more effective.
A huge amount of what I use on a daily basis has been gleaned from excel blogs and tutorials and I hope in a small way to add my voice to help people find easy solutions to their spreadsheet problems to enhance their data analysis and management information.
But what do you want to know? What really bugs you in Excel? What have you always wondered how it works? Have you had a spreadsheet problem to be fixed or a dashboard which isn’t doing its job? Any tips you’d like to share yourself? I would love to receive feedback on what you want and need to hear, let me know using the comments below and I’ll try to make sure I cover all suggestions over the coming months.
Spreadsheet Problems and Errors
May 18th
There’s been an interesting discussion in one of my LinkedIn groups about this article in the Financial Times on errors in spreadsheets. Although some years old the article talks about research from the University of Hawaii showing that
“between 78 and 97 per cent of spreadsheets contain “serious material errors” with a potential to devastate the bottom line.”
I wonder, did they use a spread sheet to collate that figure?
In my view it’s the very aspects of Excel that make it so widely used that can lead to problems. It’s the flexibility, the speed in which fairly complex calculations can be done, its accessibility and widespread use. At its best it’s an incredibly powerful tool which shouldn’t be underestimated. I’d say that careless use of business critical spreadsheets is analogous to putting a young inexperienced driver in control of a new, more powerful car and telling them to rely on that car to get around. Whilst they may not have a terrible crash, a minor bump or scratch somewhere along the line is likely unless that driver is given the time and support to get to know and understand the vehicle, along with a good set of documentation for backup.
But don’t panic, of course you shouldn’t abandon Excel entirely! Yes many organisations rely on complex and unstable spreadsheets when there are more appropriate Business Intelligence solutions however Excel can also enhance your business giving you the management information you need in a format and style that suits you. It’s particularly suitable for SMEs, freelancers and micro businesses, helping them working more efficiently and effectively understanding their business. Management Information, KPI reporting, Business Intelligence and Data Analysis may seem complex terms but shouldn’t be reserved for large corporate customers, every business needs to be able to lay their hands on their key data and Excel can help you do that. A properly constructed spreadsheet should be an asset to your business and Excel should never be a burden to be endured!
In coming blog posts I’ll cover various spreadsheet tools and some suggestions of best practice when designing a new workbook which can be used to help you avoid problems, but in the meantime what’s the biggest problem a spreadsheet has ever caused you?
In which I use Excel to go Royal Wedding crazy
Apr 28th
I’m not sure, is it actually the law that every blog or press release today has to include references to the Royal Wedding? According to the media the country – if not the world – is currently gripped by Royal Wedding fever. Whilst I’m not sure that I’m “gripped” I am enjoying some of the tenuous press releases on the subject (Dunkin’ Donuts is a particular favourite) so thought I’d add to the mix by a quick analysis of the guest list.
The Guardian’s datablog has put together a spreadsheet based on the snapshot of the guest list released so far. It’s only a few hundred of the 1900 guests and is a simple file with a single field containing the person invited such as “Prime Minister of Saint Vincent and Grenadines and Mrs. Ralph Gonsalves”. I wanted to pull out the titles of those invited, however as you can see it’s not as simple as pulling it out from a single field as the information is mixed in with other data.
Armed with nothing more than a biscuit and a quick bit of excel magic I did some analysis to pull out the titles of both guests and it turns out that of the published guests just over a quarter (27%) have the title of Mr, Mrs, Miss or Ms. Looking to those with aristocratic titles the largest group is the 24 answering to “Lady”, followed by 13 Governor Generals and a dozen princesses. In fact our own Queen is just one of 6 who can answer to that title, with an equal number of Kings and Arch Bishops.
The spread of titles is something like in the chart below, although it’s worth bearing in mind that this is where it’s important to work closely with the client. For example some titles could fall into two or more categories, and as I’m not up to date with the latest information in Debretts I would always discuss carefully to make sure that any analysis rules are defined at the outset.
Whilst Buckingham Palace have not (yet) called upon my skills as an excel detective, pulling out crucial information tucked away in a single field is often needed. It may be that you have a set of address data and need to pull out some specific information that’s embedded within the fields themselves. Or perhaps you’ve a set of data with order numbers or part names mixed up and need them analysed. Rather than sitting down and spending a few hours trawling through manually there are a host of ways you can pull the data out automatically using Excel or other tools, and I can either do this on your behalf, or work with you and your teams to train them on the functions that they need.
Enjoy the long weekend, I’m off to go and find one of those doughnuts
So what was Shropcamp?
Apr 20th
You can barely move on twitter the last 24 hours for tweets about Shropcamp but what was it? It was billed as an afternoon to discuss “How can we use social media and open data to help service providers to work more effectively at a local level?” But what was it?
It wasn’t a conference
This was an unconference. There was no pre designed agenda, no keynote speakers, no wading through handouts. That said we did get goodie bags (all hail the sponsors MoreOpen, Penval, Beyond Brilliance and UKITA) and arrived to a rather splendid buffet lunch at Harper Adams. Ben Proctor, the dynamo behind the day, briefly welcomed us and then handed over to Andy Mabbett to draw out ideas from the group for workshops. Those interested in presenting – or who had something they wanted to hear about – came up to do a 30 second pitch for the idea and after some discussion the agenda was formed
Shropcamp Agenda (Pic courtesy of www.flickr.com/photos/likeaword/)
It wasn’t full of geeks
It wasn’t full of public sector workers
Naively I’d presumed that the room would be brimming with programmer types, and that those who weren’t hackers or developers would be solely from the public sector but there was a wide range. Perhaps it’s because of the blurring between the lines of social media, technology and open data but there were few hard core hackers around, instead it was a blend of private and public sector, suited businesspeople and jeans and t-shirts, voluntary groups and social enterprises, parish councillors and self-admitted disruptors. There were marketers and web designers, social media experts and press officers. There was not a spreadsheet in sight, this was about new ways of working with data, using it to enhance local services, inform local decision making and supporting communities.
It wasn’t a social media techfest
Admittedly the lack of tweeting was more down to the lack of wifi access for a number of attendees, but whilst social media was discussed it wasn’t pushed to the nth degree. I saw only 2 QR codes (one on my own business card) and discussion was more around how to use these tools for communication rather than waggling the latest technology. (Edited to add that I understand I missed the techy workshop on Social Gaming and QR codes)
So what was it?
As ever it was what you made of it. There was debate, there were interesting discussions, some really interesting initiatives. It was a refreshing change to see people mixing from such different environments and in a relaxed yet energetic way (aided by the glorious sunshine). Personally I would have liked to see more debate as some of the workshops were still slightly too presentation reliant, and would have liked a few rounds of introductions somehow (not stuffy stand ups but some way of knowing who shared your particular interests)
A few of us were discussing Shropcamp at the Coalport Jelly this morning, when Nick Deane pointed the bottom line was that the afternoon was about communication. He’s right I think, when it comes down to it yesterday was about communication and facilitating information sharing. It felt like a vibrant and exciting afternoon with ideas a-plenty, I just hope that some, just some of these ideas grow as a more fertile and friendly environment would be hard to have found.