Excel SUM Ranges – Tips to Avoid Devastating Errors

Once bitten, twice shy.  Spreadsheets have always been trying to bite us in the proverbial. Wolves in sheep's clothing.

Here's a real-life example...

Before Excel appeared I was responsible for building the annual budget for a company with some 30 departments, growing rapidly.  All was well. At the last moment, one department had two extra employees added, joining in month 3. The change was made in the spreadsheet.  The budget approved.  Backs slapped.

I was also responsible for the management reporting, and explaining budget variances.  Months 1 and 2 went OK. Then month 3 arrived.  A £5k adverse variance in one department.  Didn’t take long to find. The two new employees had joined as planned, but the budget didn’t include them. Two rows had been added to the department’s budget spreadsheet, but not picked up by the SUM formula. The costs had not been included.

As each month went by my embarrassment increased. Thankfully I didn’t have to refund the difference!

There are many other similar examples in businesses large and small. Lotus was sued for a similar problem in their 1-2-3 spreadsheet, but unsuccessfully. Using spreadsheet software accurately is down to the user.

So how do you stop such common and potentially disastrous errors?

• How to avoid making such errors? 
• How to check none have slipped through?

TIPS

Tip 1: Be aware of the dangers 

Firstly it pays to make sure everyone who uses Excel is aware of this risk and other spreadsheet risks.

Tip 2: Using blank rows at ends of ranges

The SUM range always adjusts if rows or columns are added within the SUM range. So:

• Insert an extra row above (or one column to the left of) the start of the numbers
• Insert an extra row (or column) after the range of numbers
• Use these extra rows/columns to at the start and end of the SUM ranges
• Reduce the height/width of these extra rows/columns (but ESSENTIAL not to hide them)
• Add a bottom border to the extra row (or to right of extra column)
• Only ever insert rows/columns between the pairs of blank rows/columns

There are two advantages over other tips below:

• The SUM can be placed anywhere in the spreadsheet, such as above or to the right, and the SUM will automatically adjust, even when start with 2 rows of numbers:

• If a row or column is added outside the SUM range, a warning is provided by the blank end-of-range rows/columns displaying oddly before adding the new figures:

Tip 3: Visualising the ranges

To provide a further check on a SUM range being  what you expect:

• Click on the cell containing the formula
• Then click anywhere on the formula appearing above the spreadsheet
• See the range highlighted (in blue)

Sadly this doesn’t work with the OFFSET function mentioned below, which is another reason not to use OFFSET.

But this method does also have the advantage of working with many other formulae to highlight the cells being used in the calculation. For example, only this week I copied a section that I had forgotten used a fixed cell (like $B$1) in formulae, when I had expected a new relative cell based on B1. I spotted the error before making the wrong financial decision!

Tip 4: Getting someone else to check it

Checking your own work is always tricky. The more edits are made that have to be re-checked, the more difficult this is.

Any spreadsheet that is of major importance should be checked by someone else. Including every ‘final’ version!



OTHER WAYS OF PREVENTING SUM RANGE ERRORS

There are some other techniques, which are covered here to show their pros and cons:

Tip 5: Automatic Excel feature

With Excel now by far the dominant spreadsheet, you would have hoped Microsoft would have done something to address the SUM range problem.  And they have.  But it is overly-complicated and works oddly:

• Under “Options” there is “Extend Data Range (or List) Formats and Formulas”, which should be ticked (usually by default). 
• Then set up a SUM range IMMEDIATELY BENEATH the bottom of a list of numbers (with no empty rows):
  
  
• Insert a row immediately above, and type in a number. The SUM range automatically expands to pick this up:
  
  
• Likewise if you insert multiple rows provided you enter numbers sequentially in each inserted row from the top. 

This automatic facility works similarly for a SUM across columns.  It even works if you insert the row/column, close the spreadsheet and reopen it.

But the SUM range does NOT automatically expand:

• If you don’t fill in the figures sequentially from the top, or leave a row blank (such as a row used for a sub-title)
  
• If the range started with only 2 rows
  
• The SUM is above, to the side or otherwise not directly below the range

The limitations apply similarly to SUM ranges across columns.

It is surprising this feature wasn’t implemented to adjust the SUM range as soon as the rows/columns are added, and to also cover 2 original rows.  But it wasn't. So this automatic feature is useful, but potentially fatally flawed.  Best not to rely on it.

Pros: Simple and works automatically most of the time
Cons: Easy to make mistakes when you think it would have worked

Tip 6: Modifying the SUM formula using OFFSET

You can manually achieve what Excel could have done, with a prompt for the syntax (from Excel 2010 onwards).

For a column of figures:

• Set up the formula in the format =SUM(C1: OFFSET(C5,-1,)) where C5 is the Total cell
• Usefully, the column letter automatically adjusts if the formula is copied across columns

For a row of figures across columns:

• Set up the formula in the very similar format =SUM(C1: OFFSET(G1,,-1)) where C5 is the Total cell

The formulae automatically change as soon as the row or column is inserted.

It also works if the total is at the top, or otherwise not immediately below the numbers, though you have to be extra careful with the range definition and subsequently adding rows/columns anywhere near the range.


Sadly “Offset” can slow down recalculations if used extensively in a spreadsheet.   So not perfect, but good if used carefully.

Pros: Reliable method
Cons: Not really any better than using blank rows in Tip 2 above, more difficult to set up, more difficult to check, and can slow down recalculation if used too much

Tip 7: Using 'Tables'


This tip can be useful when values in columns are being summed, i.e. the data is in rows. It doesn't work if cross-casting across columns.

If you insert a "Table" for the initial rows (even just two), you can put a normal SUM function below, above, or to the side of the column of numbers:

Add 2 rows above row 5, and all the totals will automatically adjust:

Add another two rows and put in the sub-heading "NEW JOINERS", and the totals still automatically adjusts:

If you don't initially put a SUM total immediately below the numbers, the other SUMs do NOT adjust unless the row headings are also in a Table column:

As you can see, the totals remain at 100 even after adding another 50. But there are two warnings that the totals are wrong:

•  The new rows are not shaded blue
• There is a little dark blue triangle in the bottom right corner  of the last cell (showing 40)

The problem can be avoided if the row headings are included in the Table:

If you don't want a column heading, set the cell colour to white, and the white writing doesn't show. You can also colour the number cells to a different colour, where you will still see the little dark blue triangle that marks the end of the SUM range:

You can also use Tables for multiple columns. But be careful! SUM ranges that are copied right do not automatically adjust for the columns, as you might expect. You have to set each column total separately:

Ranges have pros but some significant cons. Can be useful if used with care.

Pros: Useful if using Tables as a simple database
Cons: Unrelieable and less useful in financial modelling, as formulae do not copy right properly, and can cause errors if not used carefully. Colouring may not be desirable.

CHECKING SUM RANGES

Assuming you don’t have any Excel auditing software that can assist, there is another way of checking SUM ranges:


Tip 8: Re-calculating SUM

Instead of getting Excel to show the range, highlight the relevant range manually and Excel will display the SUM in the bottom bar. This can be easily compared to the SUM being displayed in the spreadsheet itself. This DOES work with the OFFSET function.

Pros: Quick and simple, and works if use OFFSET
Cons: Only works for SUM (and COUNT and AVERAGE) formulae

IN CONCLUSION

Spreadsheets are powerful but dangerous things. It is easy to make a material error in something as simple and important as adding a range of figures.

1. The first step is to realise there is a danger. 
2. Reduce the risk of error by using Tip 2 by adding blank rows/columns at ends of SUM ranges
3. Review formulae as in tip 3 
4. Use the other tips and techniques when circumstances are appropriate
5. Get someone else to check the spreadsheet model

Microsoft Responds. Or Have They?

News reaches us today that Microsoft have confirmed that all the limitations of Excel for forecasting listed in our recent blog article will be addressed in the next release of Excel. 

Or have they?

EXCEL TIP: Formatting Negative Numbers

Type in the two numbers for Revenue and Cost of Sales, and you'll get this:

Can you spot the negative sign before the Cost of Sales figure? Not always easy.

So let's format all the numbers to be in brackets if they are negative (using Format Cells, Number, Custom, typing in the formatting as shown):

The problem now is the figures don't line up. Just adding a space before the semi-colon, and the numbers look a lot better:

The formatting is set up as follows:

When Excel. When Not Excel.

Excel is rather like a domestic lawnmower. A powerful tool for use in personal situations. Like mowing the lawn, or using pivot tables for a one-off analysis of data.

But would you use your lawnmower to mow a playing field?  Well you could, but it would take you a long time. You might do it once.

Or you could ask your colleagues who have lawnmowers to come and help. But it would be very difficult to make sure all the field was cut, and inevitably there would be much overlap. A lot of expensive people taking a lot of time to do an iffy job.

More likely you would go and buy a larger mower designed for the job.

That’s just like using Excel in any multi-user situation.

Why do people use Excel when a properly-designed multi-user system would be far more effective?  People already have Excel  on their PCs, but that doesn’t make it the right tool.

Here’s a more detailed assessment of the pros and cons of Excel for multi-user budgeting.  Similar principles apply to other business applications.

Indeed if you are using Excel in any “multiple” situation, it’s worth considering an alternative system:

• Multi-user
• Multiple linked spreadsheets
• Multi-currency
• Multi-dimensional (multiple products, entities, departments etc)

So why do people use Excel in such situations when more appropriate solutions are available?

Using Excel for Forecasting – The Pros and Cons

Excel is widely used for forecasting and budgeting in businesses large and small, for a number of very good reasons. Excel is awesome for ad-hoc data analysis.

But for structured and repeatedly-used forecasting models, the limitations of Excel become apparent. These are more of an issue as a business increases in size, with revenue into the millions and higher. Other forecasting software is then worth considering, some of which can be used with Excel.


EXCEL'S STRENGTHS FOR FORECASTING

1. Excel is a very flexible tool, to build forecasting models to suit any business
2. Excel is typically already installed on the PCs of every person involved in forecasting, so is effectively free of charge
3. Every forecasting “contributor” out in the business has at least a basic understanding of how to use Excel
4. Using Excel can avoid the IT department’s involvement, notably to control models that need to be tweaked rapidly
5. Excel has very useful graphical facilities to present the results

There have been recent improvements in Excel relevant to forecasting and reporting, notably PowerQuery and other PowerBI functions. Excel is now at the 2016 version and online in Office 365.

But most of the key issues with using Excel for forecasting remain. So specialist forecasting software can provide significant improvement.


KEY LIMITATIONS OF EXCEL FOR FORECASTING

(1)    Excel is Fundamentally Single-user

Excel is designed to be used as a “personal productivity tool”. Any one spreadsheet file cannot be updated by more than one person simultaneously. Using Excel amongst multiple users in “collaborative forecasting” involves workarounds.

(2)    Drawbacks of Multiple Spreadsheets

A very common workaround is to split the model into multiple files, each to be updated separately and pulled together into a summary.

Correctly maintaining links with the correct spreadsheet version is a major issue.  Furthermore, contributors must be stopped from changing a spreadsheet format even slightly, or it ruins links. However the summary is structured, there’s risk of major error, and significant time needs to be expended to minimise errors.

(3)    No In-built Aggregation

Much of forecasting involves simple aggregation, for example:

• Adding together the sales projections for multiple products
• Adding the staff costs of multiple departments
• Adding multiple companies or business units

Excel has none of this structure built-in.  Formulae or pivot tables need to be set up, including cross-references across sheets.  The bigger the model, the more formulae and complexity, and the more difficult it is to ensure all ranges and links continue to work correctly.  Late nights. Weekends. Missed deadlines.

(4)    Lack of Audit Trail

Most formulae provide no immediate audit trail analysis of each aggregated figure. Pivot tables can help. But manually tracing through the origins of each figure becomes too time-consuming to do regularly.

There is also no audit trail of who made what changes, which can be important in larger situations.

(5)   Lack of an In-Built Database

Excel does not have its own underlying in-built database. There are two workarounds:

• It is possible to link Excel to a customised database, such as Microsoft Access. But this adds considerable complexity.  
• A “data sheet” can be created, but this has to be reliably populated and analysed, and can quickly become too large and unwieldy.

(6)    Comparison of Actual to Forecast/Budget Figures 

Actual data can be imported into Excel, but this is complicated to do in the equivalent way to forecasts and budgets at each level of each hierarchy.  Detailed comparison is similarly difficult between actual and forecast, or between versions of forecasts.

(7)    Calculation of Balance Sheet and Cash Flow 

Many forecasts are built with the P&L as the starting point. Cash flow is built using formulae, such as applying debtor days. The balance sheet is produced from both, adding in depreciation and other adjustments driven by formulae.

The beauty of Excel is total flexibility. But keeping control of formulae remains a major headache as the format of the P&L or cash flow sheets change.

(8)    Lack of Documentation

Excel spreadsheet models are notorious for lack of documentation of their logic, especially as they grow. The bigger the model, the more difficult it is for new personnel to understand it.  As time goes by, many Excel forecasting models remain untouched for fear of breaking them.

(9)    Lack of Workflow

Controlling the submission and approval of forecasts becomes a major headache as the number of contributors increases. It can be the biggest single problem in larger businesses.

Excel has no in-built workflow controls. Submissions have to be controlled and approved manually, or by superimposing separate workflow software.

 
ALTERNATIVES FOR FORECASTING

Specialist forecasting and budgeting software can overcome all these issues, and can be affordable for businesses large and small. Depending on the cost, good software will include some or all of:

• A central multi-dimensional database, with each dimension describing entities, time periods, products, etc. These are in multi-level hierarchies such as country/region/area.
• Versions of forecasts, budgets and actuals can be clearly identified
• Automatic and accurate aggregation at each level of the hierarchy, across all dimensions and versions
• Automatic analysis of composition of aggregated figures
• Actual figures can be imported in the same hierarchies as forecasts, allowing easy comparison.  Versions of forecasts can also be easily compared.
• The database and software is multi-user, allowing everyone who updates the figures to do so in parallel in one central easily-controllable place
• In “collaborative forecasting”, quick and automatic generation of templates for contributors’ submissions, which they can’t tamper
• Centralised formulae for producing cash flow and balance sheet, used repetitively and reliably
• With much functionality automated within the software, the need for application documentation is reduced. The model is easier to learn when personnel change, especially if someone has previous experience of the software.
• Workflow in-built to control submissions, approvals, and automating the chase of overdue actions.
• Changes to data can be logged in an audit trail by user.
• Some software uses Excel with an add-on for data templates, whilst some are used intuitively through a browser. This minimises the need for additional software on contributors’ devices, and minimises training.
• Excel can often be easily used for reporting, thereby maintaining its benefits
• Overall providing quicker, easier and more reliable submission control and analysis of the summary position

For larger businesses, a fully-functional system for collaborative forecasting may have an entry level for the software alone of £50,000 or more.  Nonetheless this can be money well spent, given the benefits.

At the other end of the spectrum, small businesses often used to use WinForecast, but this is no longer available or supported. The market is developing for equivalent tools.

In the mid-market there are now affordable solutions, either cheaper versions of the big software, or tools focusing on specific aspects such as controlling submissions.  Larger businesses can potentially re-grade to save money.

Otherwise there can be more efficient and effective ways of using Excel.

One way or another, the forecasting, budgeting and period-end analysis processes can be made quicker, more reliable, and better value for the business.


INTERESTED IN TALKING FURTHER?

Do leave a comment, or call Chris Challis on 07836 774439 to discuss your specific situation and potential solutions.  

When does an Excel spreadsheet system need to be replaced?

Excel is very powerful financial and reporting software. Its graphical tools, pivot tables and macros are just some of Excel’s strengths. It’s also readily available on nearly everyone’s computer.

So it’s no surprise that Excel is often the first software to which people turn to develop a new financial application, in businesses large and small.

But Excel has significant limitations:

• Data storage is simplistic, so does not cater well for real-life situations
• A spreadsheet cannot be updated by more than one person simultaneously, and security is simplistic, so is not suitable for many multi-user situations
• It is difficult to make a spreadsheet system robust, and keep it robust as it evolves.  
• It is easy to make mistakes and inconsistencies in formulae.
• It is also difficult to maintain links between spreadsheets, especially if some are being circulated for completion by other people.
• Data entry is often done manually or semi-manually, and is not subject to validation. So data entry is slow and prone to error. 
• There is no audit trail

As a result, many people know of situations where material errors have arisen from using spreadsheets, and occasions where spreadsheet systems have held back the business. Ringing a bell with you?

There comes a time when a spreadsheet system needs to be replaced by a more suitable database system. Excel can be retained for reporting purposes.

BENEFITS OF REPLACEMENT SYSTEMS

Replacement systems use a database to store data centrally and provide a number of standard facilities that automate the system:

• Data can be structured exactly as required
• Systems can automatically and reliably aggregate and consolidate departments, regions, divisions and companies
• Data entry facilities automate the load of data from source systems, which can then be done in more detail if required
• Multi-user systems allow simultaneous use by people in the same department or different parts of the business, with differing levels of access permissions
• Audit trails and other management controls are available

As a result, a replacement system can be far more reliable, quicker and more powerful than the spreadsheet system it replaces. Excel can still be used for reporting, so its strengths can be retained.

Here are some examples where Camwells has helped find and implement replacement systems, working with FTSE250 to smaller private businesses across different industries.
 

(1) GROUP CONSOLIDATION

This FTSE250 housebuilding group has devolved many of the finance functions to regional teams, whilst running a shared service centre for cash transactions.

The Group reporting function was entirely dependent on Excel, which resulted in a number of critical issues, including:

• The process for submission of reports and forecasts from regions was cumbersome and time-consuming for regional and Group staff alike
• Data entry lost the available detail, and there were examples of keying errors
• Where spreadsheets were linked, it was also difficult to control versions, so also prone to error

As a result Group reporting staff were spending most of the time running the spreadsheets rather than analysing the data, such as comparing regional performance. With additional regions being added as the business grew, the spreadsheet system was nearing breaking point.

The work carried out included:

• Understanding current reporting, forecasting and related processes, and opportunities for improvement, by talking to MDs, FDs and accounting staff at regional and Group level
• Producing an agreed specification that was then used to assess available software, principally through software demonstrations and “proof of concept”
• Selecting multi-dimensional software and configuring it to best represent the business, including the import of detailed information from the trading system

The result is a more effective, quicker and more reliable system.

(2) BUDGETING AND FORECASTING

This quoted IT company had a budgeting and forecasting system consisting of a suite of complicated Excel spreadsheets which were sent out to relevant departments. The system had evolved over time so that the controller said “it was held together with sticking plaster”, in particular:

• Linking submitted spreadsheets and controlling versions was difficult and risked material error
• Finding correct versions to compare to actuals was difficult

Again a specification was agreed that would allow the entire budgeting process to be carried out in a centralised system, which would clearly handle different versions. Software was selected that would:

• Allow the company’s overall forecast financial position to be produced reliably at the press of a button, including balance sheet and cash flow projections
• Make the detail of each version easily available to authorised people to review forecasts and compare to actuals

(3) PROJECT ACCOUNTING

This quoted software house had a packaged software system for the basic accounting. But there was no module suitable for their project-based order processing, which involved the sale of their own software, buying in hardware and supplying various associated services.

This was controlled in an integrated suite of Excel spreadsheets. But as they had become so unwieldy and difficult for the order processing team to use, the manager was threatening to resign.

The spreadsheets were analysed and a specification agreed. Various software options were considered including:

• Third party add-on module to existing accounting software
• Custom-written add-on module
• Complete replacement

The decision was taken to keep the existing accounting software, and get an add-on module custom written.

The end result was a system so good, when the business was acquired the system was used for the enlarged group. Unusually the acquired company personnel kept their jobs, from FD to junior clerk. Clearly a most welcome benefit!

(4) EXPENDITURE TRACKING

This privately-owned multinational was developing an offshore oilfield in Africa, which required cost records to be maintained and reported on a cash basis to the national government, alongside normal accruals accounting.

These cash records were being collected in Excel, but the use of multiple currencies and the reconciliation of cash and accruals figures was becoming increasingly difficult. One multi-currency accounting database was the objective, together with a sophisticated new purchasing system.

The management’s preference was for a cloud system that could be easily accessed from a variety of locations. After a specification was produced, various cloud options were assessed alongside the on-premise software commonly used in the oil and gas industry.

One multi-currency cloud accounting system was found that would provide the dual cash/accruals accounting more simply than with the on-premise option.

(5) ORDER PROCESSING

This corporate subsidiary was providing IT network systems where all the components were being bought-in on a “back to back” basis, alongside their own services.

Sales order, purchase orders and accounting records were on separate Excel spreadsheets, with no logical link between them. Growth in the business required additional personnel, which required a multi-user system.

The decision had been made to replace the spreadsheets with standard accounting software and a custom-written order processing system.  This now needed to be implemented, and internal people did not have the time and expertise.

The initial system was implemented on time for the start of the new accounting year, and then various improvements made to cater for changes in the business.

One key benefit of a proper database system was that purchases of equipment supplied to specific customers could be clearly identified. This allowed supplier rebates to be claimed, totaling some £600,000 per annum. This literally doubled the company’s profits.

As the MD said, “We couldn’t have done it without you.”



If you know of any similar situations that need to be resolved, do ring Chris Challis on 01628 632914.

Forecasting and Performance Analysis - Alternative Systems

Forward planning and performance monitoring are both important management functions for any business or organisation. This is true at any time, but especially when finance is being raised or banking facilities need to be monitored.

Budgeting and forecasting is a regular activity for most organisations, together with variance analysis against actuals.

But all too often these processes are using systems which are difficult to use and maintain, often using Excel. In many organisations they are the most ineffective practices in management. They take everyone too much time.

In this article I've written for the IT Faculty of the ICAEW, here are some tips for how alternative systems can help, and what to look out for.

Escaping Excel Hell – Cutting Reporting Time

Excel is an excellent presentation tool for reporting. But a key problem is that business analysts spend too much manipulating data, and not enough time analysing. Another issue is the struggle to have a report available by its deadline.

One aspect of this is the time needed to get data accurately from a source system into Excel, which might currently involve re-keying.

There are several solutions to this [...read more...]

Excel Add-ins for Management Reporting

Just a reminder that if you want to use Excel for dashboards or management reporting, there are three useful sets of add-ins:

1. Gauges
2. Sparklines (mini graphs)
3. Traffic Light Charts

Click each link for further details.

Escaping Excel Hell – Tips for Forecasting and Budgeting

When preparing a budget or forecast, especially if extra funds are being sought, the last thing you want is a major error. If it is found by the potential funder it’s one problem. Not spotting it at all is another. Unfortunately it is very easy to make a mistake using Excel, such as missing costs and getting links between sheets incorrect.

Excel is a great tool for one person preparing a budget for a simple business. As things get more complex, other tools are more appropriate to handle aggregation and use by multiple people. Some of these tools use Excel as the user interface, or a grid that looks somewhat like Excel.

Whatever tool is used, it’s important to remember: [...read more...]

Automating Excel Business Processes

Excel is a marvelous software tool. It’s pretty well standard on business PCs, and most people have at least a rudimentary knowledge of how it works.

You can list data in a simple database, and use the range of formulae to add and manipulate data. So Excel (or some other spreadsheet) is often the first tool thought of and used for a business application such as order processing or budgeting.

However it’s worth thinking of Excel as [... read more...]

Escaping Excel Hell - Management Reporting

If you have two choices of how to produce management reports, which would you prefer:

1. Into a web browser direct from the relevant system(s)?
2. [...read more...]

Traffic Light Charts for Excel

We’ve looked before at add-ins for gauges and sparklines, but haven’t looked at “Traffic Light Charts”. These apply colour highlights to trends, as in the example left.

[...read more...]

Developing Better Spreadsheet Models

There are various ways you can suffer in spreadsheet hell, as these real-life examples illustrate.

With Excel now the dominant spreadsheet system, we’ve already looked at how to escape Excel Hell in the articles on these subjects where understanding the limitations of Excel means there are better alternatives:

• For business processes
• For reporting
• For planning and forecasting

But what about for financial modelling? [...read more...]

Escaping Excel Hell – Unlocking Business Processes

You start a new business or activity. This may be within an existing business. The easiest thing to do is to log what happens in a spreadsheet, typically Excel.

The next thing you know is that there are a team of people tripping over each other trying to use the same spreadsheet. Copies are taken and you quickly lose track of  [...read more...]

Escaping Excel Hell – Improving Month-End Reporting

It’s month end. Reports need to be produced. The finance team are ordering in pizza to get them through the evening. Why? The only way they can produce the information that Group and local management require is to download transaction data to a spreadsheet and reanalyse it. Sound familiar?

It’s not just at month-end. The same thing happens at other times of the month, whenever any other reports need to be produced. So-called “analysts” are spending too much of their time as “Excel jockeys” rather than [...read more...]

Escaping Excel Hell – The Ultimate Add-In?

It’s easy to outgrow Excel’s capabilities for a specific application. The only option is often to replace Excel with a system designed properly for the specific job.

There are other occasions where by using an add-in, such as gauges, you can achieve something valuable which you can’t do with Excel alone.

But often it makes sense for familiarity to use Excel linked to a database, for  [...read more...]

Escaping Excel Hell - Planning, Forecasting or Budgeting?

In marketing, there are a number of words that illicit a positive response. "Profit" "Free" and "New" are just three examples

So what reaction do you give to these three words: [...read more...]

Escaping Excel Hell - Unlocking Business Growth

Excel's a great way of making simple lists. So if you are trying to track sales orders or purchase orders, it's tempting to do it in Excel if you only have an accounting system.

Then the business grows a little. Two people are now trying to share a spreadsheet. That proves impractical so it is split into two halves. Then they are pulled together to report. Then a third person is needed ....

Before you know it there is a spider's web of spreadsheets, with links that sometimes work, and a whole raft of manual processes to control the business. Sound familiar? [...read more...]

Management Reporting – “SparkLines” and “Tweets” to Deliver Concise Information (in one cell & in one hundred and forty characters or less).

Senior management want relevant information quickly, clearly and concisely. Graphics are a great way. A picture can paint a thousand words.

A dashboard with graphs can convey so much. But a single screen view? Aren’t a few words needed to explain the “why” to go with the “what”? [...read more...]

Escaping Excel Hell – Using Graphical Add-Ins

If you’re familiar with my articles on Escaping Excel hell, then I’m often suggesting you replace Excel with a proper database-backed system.

This can be for planning/budgeting or a host of other business applications.

But one area where Excel remains extremely useful is for management reporting. Whilst there’s a key role for structured dashboards, Excel also [...read more...]

The gauges are available under a free 30-day trial from the Camwells website.

Escaping Excel Hell - Solutions for Planning and Forecasting

Financial planning is key for any business. Each business is different, so a flexible system is required.

For this reason Excel is often the natural choice for the job. But anything more than a very simple model becomes very difficult to build, and certainly difficult to maintain.

There can be issues with aspects such as: [...read more...]

Escaping Excel Hell - Budgeting

Budgeting. A word that strikes horror into every finance department and every corporate manager.

For smaller businesses budgeting is often a totally alien concept. So sadly is cash flow forecasting. For them a simple Excel spreadsheet could transform their business, giving them forward visibility of issues and the time to avoid therm.  But as they grow ....

Many larger businesses still use Excel. Imagine running 300 cost centres across a £250m business that had changed from manufacturing to product/service projects in just a few short years. I've seen it and helped replace it: [...read more...]

Escaping Excel Hell – Processes Desperately Seeking Automation (Update)

Are your systems trying to pull your business apart? Or are they pushing in the same direction?

A month ago I wrote an article entitled "Escaping Excel Hell – Processes Desperately Seeking Automation". This talked about business analysts not having the time to analyse and add value to the business, as they tend to have to spend too much as "Excel jockeys".[...read more...]

Escaping Excel Hell - Useful Add-Ins

Excel is a powerful business tool, but there are times when it is used when there are much better solutions.

There are also times when third party add-ins can significantly enhance it. [...read more...]