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, depending on the nature of the source system, the data volumes, and the nature of the report. These include:

1. Export data from the source system, and import it, by one of several techniques. This is especially useful if you have a template to automatically populate, such as a regular monthly or weekly report.
2. Extract data directly into Excel from the source database. Depending on version, Excel has standard data links, including links into pivot tables. Add-ins are also available to make the process as easy as possible.
3. Extract data into a database such as Access, where it can be checked and managed, for example to check and repair analysis fields. That database can then be the source into Excel.

Linking directly into the source system helps to maintain a "single version of the truth", but this isn't always practical. The next best thing is to export into a database (data warehouse), especially if multiple sources are involved. This then provides the single version that members of the organisation can access.

If you’d like further help with improving the efficiency and effectiveness of your reporting, do ring me on 01628 632914, or send me an email.

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:

• Cash flow is typically what matters, and this is not the same as the P&L account. There can be significant timing differences. Often costs have to be paid in advance, including capital expenditure, and customers may pay some significant time after a sale.
• Funders, especially banks, like to see projected balance sheets, against which the level of lending is assessed.
• As mentioned above, with Excel it is extremely easy to make a mistake in formulae or links.

To address all these points, it is vital that the forecasting model consists of three main elements:

• Profit and Loss account
• Cash Flow
• Balance Sheet

If these all balance, there can be some comfort in the formulae, although it is still necessary to check the model carefully.

If you would like help in building a successful forecasting model, do ring me on 01628 632914 or send me an email.

.

Escaping Excel Hell – Automating 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 clever electronic paper, suffering from similar drawbacks:

• Excel has no automatic facilities for additions. All formulae have to be set up manually, directly or by copying, and it is easy to make errors
• Excel is a “personal productivity” tool which is not designed for use by more than one person. Whilst it is possible to share and link spreadsheets, again there is considerable possibility of error.
• Data entry does not have an audit trail. Whilst it is convenient that changes can be made easily, no changes will get logged.
• Once you’ve got a database of data, you can produce summaries by creating additional tables or pivot tables, but regular reporting is difficult
• You won’t get any workflow capabilities to automate processes

So Excel can be very useful to generate small-scale systems. But for larger systems, or a small system that has grown, a more appropriate solution is needed.

Sometimes it is possible to find a package that does exactly what’s needed, in the cloud or to buy to use on-premise, such as for a simple order processing system. In other cases a system needs to be customised to do what is required. For financial systems, it may be appropriate to bolt this onto a packaged accounting system, and share aspects such as the customer, supplier and product details.

The first stage is to document exactly what is required, including data structures, basic workflow process and automation sought, reporting needs, data volumes and other key aspects.

Then the market can be assessed, and a short list established for demonstration and/or trials. Selection is an art.

Implementation needs to be handled carefully, to ensure the software works, people know how to use it properly, and initial data is loaded correctly. Any changes to the business and how it operates need especial care.

Camwells has experience of all stages of a range of packaged and bespoke solutions to replace Excel. If you would like to talk further, please ring me on 01628 632914 or send an email.

.

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. Into Excel and manipulate before distribution?

Most people would choose option 1, as that avoids the need for manual manipulation, which takes time and may introduce errors.

But if you want to add commentary to a report, there needs to be the means to do it.

There will also be differences in the graphics available, and the overall ability to format the reports.

So the best direct reporting tools can be magnificent, otherwise is it Excel?

.

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.

Here improvement is shown in green, and deterioration in red. The thicker red line is especially poor performance, and will jump out at anyone reviewing this simple dashboard. This and other styles are achieved using Excel add-ins.

If you like what you see, the add-ins can be purchased at reasonable prices from the foot of the page.

.

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 what’s the latest. Changes are made to two different copies, so some changes get lost.

So you set up a set of spreadsheets which all link together. A right royal spaghetti.

Generating things like sales invoices will be difficult. If you want workflow, you won’t get it. If you want an audit trail of changes, you can’t have one. If you want a range of management reports, that will be tricky.

Sound familiar?

Time to move to a database, in one of three ways:

• Get a packaged application
• Develop and maintain a system in-house
• Get someone to develop a system for you

Each option can be done in-house or using a facility in the cloud.

Camwells has helped businesses make this transition, in areas such as order processing and business forecasting. In one case the new system unlocked purchase savings that doubled profits. We’d be delighted to help you.

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?

That's what I found just before Christmas one year when I walked into a small quoted company's admin room to be greeted as if I were Father Christmas. The team leader had threatened to resign, and the Financial Controller had done their best without making any real progress (lack of time as much as a lack of experience).The Group Finance Director had spoken to a client of mine and had been given my name.

I had previously managed the implementation of an order processing system that had allowed the business to grow 10 fold with minimal increase in staff. This linked sales orders back to back with purchase orders. So when one supplier offered a discount for equipment supplied to certain cusetomers, it was simple to make the claim. £600,000 a year, doubling profits. Now that's a return on investment!

We worked with this second company to define their needs, including resolving disagreements between the senior management that an outsider could do far more effectively. The system implemented not only transformed the admin team, but meant that when the company was taken over, unusually it was this team and this system that was used by the enlarged group. The other team  were made redundant. It could so easily have been the other way around!


There's nothing like happy clients!

.

Escaping Excel Hell – Processes Desperately Seeking Automation

A chat over a pint last night reminded me that many businesses still have lots of Excel-based processes, including reporting, that are inefficient and prone to error.

In many cases highly qualified and expensive “Excel jockeys” are spending most of their time manipulating data. Automation would let them be their job title – “business analysts” - who add proper value to the business.

Sometimes it’s just a matter of replacing Excel with a database or OLAP system, depending on the volumes of data, or writing some macros or VBA. Automation also provides the key benefit of slashing the risks of error that so often plague manual Excel manipulation.

What Excel-based processes are desperately seeking automation in your business?

.

Excel Hell - ways to escape

Over the last few weeks we've looked at various ways to escape Excel Hell.

I hope you find this summary useful:





(1) Replacing Excel

Moving from Excel to a database solution for order processing

Using other tools for budgeting and forecasting


(2) Enhancing Excel

With add-ins or integration

Dashboard gauges


(3) Upgrading to Excel 2010

PowerPivot