3 Ways to Do More with Less in Excel
Jeff Robson
Managing Director, GAICD, NED, International Speaker. Focussed on helping businesses harness the full potential of their data to make smarter, more informed decisions.
Every Business Today is Being Asked to do more with less
... fewer people, shorter time-frames, less capital and resources. Improved productivity is a basic necessity.
To really achieve breakthroughs in efficiency and productivity, we all have to work smarter by using technology better.
In my experience, the biggest opportunities for improved productivity are in the areas of:
-
Data manipulation: converting raw data into useful information.
-
Management reporting: analysing and presenting data.
-
‘What if’ analysis: financial modelling and forecasting.
Spend time analysing information, rather than preparing and collating it.
Let's break it down ...
1. Data Manipulation
This is a huge source of inefficiency, errors and problems.
Data is frequently either exported or manually keyed into Excel, then manually cleaned, reformatted and summarised in some way. Errors are frequent because the processes are so manual: formulas aren't copied, data is re-keyed, and cells are manually linked/re-linked every time the process is run.
These issues are incredibly common in businesses because staff don't realise there are better ways; they don't know that Excel could do most/all the work for them; and they just follow the process they did last month.
This wastes staff time, which costs the business. It's even more costly if management make a bad decision that is based on inaccurate data.
2. Management Reporting
There is an inordinate amount of staff time spent collating, preparing and constantly updating data for reports.
Management often don't realise just how much work goes into making a beautiful report that gives them all the information they need to make a decision.
Yet, there so many ways to speed up these processes:
- Automation through VBA macros so processes can run just by clicking a button
- Linking Excel directly to databases so data, charts and reports can all be refreshed in seconds (or less!)
- Using the power of Excel's formulas to automatically do the work for you, rather than doing lots of manual amendments every month.
Many people don't realise the power of Excel to make their lives easier by saving vast amounts of time, effort and frustration!
It's quite common to reduce the time staff spend on a given process by 90-95% with just a few simple changes in Excel.
3. Scenario planning and ‘what-if’ analysis
Many businesses find it hard to answer the question "What would happen to profit/cash flow/NPV if we change X?"
X could be prices, input costs, salaries, contractors, suppliers, order quantities, premises, a major project ...
Being able to answer these questions is often impossible (since many businesses don't have a solid financial model), or very difficult and time-consuming (since many other businesses have a financial model that's hard to use).
And, what if we also ask “What happens to profit/cash flow/NPV if we’re wrong about X?”. This requires robust sensitivity analysis of key drivers so is imperative to informed decision-making.
Making decisions in the absence of data and proper analysis is a recipe for disaster as you're flying blind.
Well-structured financial models that are easy to use and understand allow decisions to be made with confidence.
These are all the kinds of things that we at Access Analytic help businesses with, every single day of the year.