What do you use instead of Excel for pulling together reports

Apart from the universal Swiss Army Knife of Excel, what other solutions have people adopted to automate and rationalise monthly reporting drudgery?

'Spreadsheet Hell' is the name for a situation where every month the accountant has to download data from the main accounting system, from sales and contract databases, and other sources; then spend days copying, pasting, sorting, filtering, manually tagging and examining and re-classifying until finally the data is in the shape they really want.

A typical reshaping is changing periods: the main accounts (pick your package - eg Sage) is structured 4-4-5 but one department wants reports by calendar month and another by 13 4-weekly periods.

Another is recoding: the main system has one chart of accounts but another consumer of the reports wants things analysed by different groupings; or one wants reports as accruals, another as cash. Etc Etc

What tools / solutions / BI etc do people do?

I don't mean simply query tools like Crystal Reports, that just gets the data, I mean something to help the slicing and dicing.

TIA

 

Patrick

 

Comments
Andrew Burnett's picture

Pulling together reports

Andrew Burnett | | Permalink

Hi Patrick

I have an Excel package which is designed to do exactly this type of thing. It can take a set of data and manipulate it in almost any way required, before creating the final set of reports. This covers not just basic operations (sorting, merging, selecting, summarising etc) but also carrying out any desired calculations on records or groups of records to generate new data for reporting. As long as the information is there, changing periods should be no problem. Recoding is also straightforward given a table to translate from one set of codes to another. Data can be extracted automatically from any external database which can be accessed from Excel, or can start from Excel tables or csv files if this is simpler.

It can all be set up in Excel without use of VBA, and once set up it runs automatically from any new set of data.

If you are interested I would be delighted to set up an example for you - I'd need a set of data and I'd need to know what manipulation was needed.

My website http://www.logicarchitect.co.uk/cs010 gives more information about the software I use, which is installed as an Excel add-in.

Regards

Andrew

David Taylor's picture

What do you use instead of Excel for pulling together report?

David Taylor | | Permalink

Patrick

I think you may be dismissing Crystal Reports as the tool you require too easily.

In you post you talk about the need to 'slice and dice' which is exactly what OLAP (Online analytical processing) was devised to do.  From SQL Server 2000 and Crystal reports Version 8 (I think) there is the capability to do that and I would suggest might withstand greater research.

Some in fact see BI and OLAP as the same thing.

Attended a seminar on OLAP back 1999 with a guy who devised the whole thing.  It was fascinating but I have forgotten his name.

Dave

Automating Monthly Reporting without a Swiss Army Knife

piersharwood | | Permalink

 Hi Patrick

I think we have just the thing for you. We have an Excel reporting tool called BVxL that can link to any dataset Sage 50/200, SQL etc. via a data manager. BVxL enables you to build dynamic spreadsheets such that changing a parameter such as a date range or department code causes the spreadsheet to refresh with the new data without changing the formatting. You do not have to download new data (your spreadsheet is actually reading the data). Once you have created your month end report deck that's it for ever... no more work, just change the dates/departments. Furthermore you can 'drill down' on any cell to see the transactions making up the balance in the cell.

If you would like to see how it works please click on the link: www.blue-valleys.com/bvxl/bvxldemo.html

If you would like more information please email me at: piers.harwood@blue-valleys.com

Regards

Piers

Sage Excel add-in fo rmanagement accounting

barrysnashall | | Permalink

 “The Analyser” Sage and Excel add-in – transforming management reportingWe’ve come a long way from listing our accounts manually, using our fingers and toes for adding, a blackboard for presentation and a pigeon for sending. We now have accounting software to help us, yet still, a recurring issue for many companies is the inability for that software to produce accurate, timely and meaningful management information, presented in a format that they require.“The Analyser” - a Sage and Excel add-in developed by Trax UK and transforming the way management reports are created.

If you use Sage then you must have The Analyser”, said Kevin Salter (BBS Computing Ltd) of the software, naming it “product of the month if not for the year” on his Accountants PowerTools website.budget data from nominal codes, departments and funds and for any combination of nominal ranges and any date range allowing for monthly or 4-4-5 week combinations, even daily totals if you choose. Additional benefits include the option to add prior year’s data alongside the current year for comparative purposes and to report on single or multiple Sage companies, all in one workbook. Furthermore, totals can be drilled down to show the underlying transactions, without the need to even open the Sage application.

The Analyser functions can be added to any existing management reporting templates or alternatively new spreadsheets can be created. In-depth training is not essential; if you can use Excel then you can use “The Analyser”. Within minutes you will be able to calculate and analyse reports, with the minimum of effort.

At the click of a button, “The Analyser” extracts actual current and

Existing users are now able to report upon their own business characteristics, in a manner not previously possible. An example highlighting this unique capability is a company in the leisure industry, which can now monitor their financial data by seasonal holidays, alongside their traditional monthly reporting, over a rolling five-year period. With this comprehensive information, effective and efficient decision-making, planning and control can be achieved and built upon.

 

Contact: Barry Snashall

Telephone: 01227 793200

Email: sales@traxuk.com

Website: www.traxuk.com

 

Yes, they have Sage 50 or Sage 200, but are they taking advantage of its full potential?

Would they benefit from being able to use the versatility of Excel to produce and recalculate management accounts directly from their Sage data, without having to re-key it first? Excelhas been an important development in IT for accountants and financial managers, enabling a clearer vision and analysis of data. Yet still, too few professionals fully understand Excel’scapabilities.

Enter

Trax are a SAGE accredited partner, whose vision was to fill the void between IT and Accounting whilst developing solutions to suit the client’s individual needs. The Analyser was born out of the constraints other report writers place upon the user. It is an impressive yet simple, timesaving into Excel. “

carnmores's picture

you have got it all back to front

carnmores | | Permalink

get a decent accountancy software package from which you can extract all the data easily eg quickbooks

sysmod's picture

Thanks for all the comments

sysmod | | Permalink

When people have spent tens of thousands on accounting packages, they are reluctant to change and would rather paper over the cracks with Excel.

I may be too quick dismissing Crystal, and the other addins suggested, I see them as doing simple extract, merge, sort and summarise operations, and the tasks I observe people doing require more manual work than that.  In some cases the solution would really be to re-code rather than copy/paste/lookup/sort/subtotal.

I've got quite a few ideas to follow up. If I come across a reproducible task (documenting them is the first challenge!) I'll get back on these products.

Thanks, all,

Patrick

-- http://www.sysmod.com

Reporting tool

jason.raikes | | Permalink

As reported by this site's editor yesterday, we at Pendragon Systems have responed to people's love of Excel and built a fully functioned report writer in Excel.  The DBReport tool uses a number of wizards to assist the user in report building so that they are not exposed to having to write SQL.  DBReport supports hierarchical dimensions (using Excel outlining), Excel styles so that you do not have to spend any time formatting the report and drill through to obtain a listing of the postings / records making up a balance.

We believe that the learning curve is too steep for products such as Crystal Reports or Business Objects for all but the largest organisations (who can employ the specialist skills).  Therefore we provide a solution in Excel to enable users to maximise their existing investment in Excel skills.

The tool has been designed by qualified accountants who are reporting experts and fully understand the requirements and the functionality required.  Thus it is very easy to produce presentation quality reports which can be quickly refreshed for different report parameters.

If you would like a demonstration, please call Jason Raikes on 07776 307 037.

Jason Raikes

www.pendragonsystems.com

Attended a seminar on OLAP back 1999 with a guy who devised the

Anonymous | | Permalink

Was it Dr Codd? If so then were you looking at Essbase? I used this and thought it was great. Could easily do what you want.

tcrowhurst's picture

The Excel Horse and cart

tcrowhurst | | Permalink

Carnmores is spot on - people worry so much about the input into an accounting system whilst failing to look at what the output is so choose your accounting solution wisely. 

There is also a distinct difference between analytic and management reporting - in the case of the former, there are so many query tools around that allow you to manipulate and analyse data that you are spoilt for choice as other contributors have noted.

In the case of the latter have a look at Microsoft Management Reporter - the venerable replacement for FRx (yes, I am biased) - it does "proper" management reporting and using different column sets can accomodate 4-4-5 and 30 day periods easily. Email report distribution is a doddle - something that is a pain with Excel.

If you want to do advanced analytics as well as budgeting and planning consider something like OlapOffice (www.olapoffice.com) developed by a CA it's been around for a while and apart from the analytics your can use Excel to post budget submissions back to the SQL tables. 

Sage reporting

Anonymous | | Permalink

 I so enjoyed the unintended humour of tcrowhurst, "people worry so much about the input into an accounting system". Rightly so, me thinks. Garbage in, garbage out, no matter how good your reporting tool.

Alternative to Excel?

phodgson | | Permalink

Yes There is an alternative to Excel !

For those of you who use Sage, MYOB & Quickbooks in particular, My Business Manager is a solution that addresses the need for Financial Reporting and Analysis, in a simple, easy and cost effective manner. It extracts Accounting Data and creates business management reports that your client can easily understand. 

What is particularly useful, is its "Traffic Light" reporting mechanism, the range of available reports, including that of generating an impressive suite of KPI reports. - All of which helps Business leaders make informed choices easier.

Amongst other reports it can produce are:-Actuals vs Budget in Cash Flow, P&L, Balance Sheets

These reports can be presented in an easy to understand graphical Dashboard format, or alternatively in a traditional textual manner. 

Check out  http://www.mbmglobal.com/Public/VideoDemo.aspx or send an e-mail to info@bcams.co.uk for further information

Add comment
Log in or register to post comments
Group: ExcelZone
A gathering place for the Excel community to explore new ideas and techniques and a forum to debate product features and best practices.