How PowerPivot can help financial reporting

Back in the last millennium, in the late 1980s and 1990s, to 1998, I extended in my then employer's Information Systems the scope of the MENTOR Accounting Suite for the Construction Industry (beautifully designed at relation level by Pete Moran, shame about some of the early, "MAX", upgrades) so that all of the Management Accounting took place within the underlying Informix-SE RDBMS.

_____________

In the core management and statutory accounting for the main Company in the Group, with a then turnover of £25m (now much more) we didn't use a single spreadsheet, Lotus or Excel.

The critical "Costing Statistics" suite of applications ultimately produced a pair of final reports, straight from the database, on the strength of which management decisions were seriously taken. The P&L figures for the statutory accounts also came straight off the database, on selecting a menu item. 

I remember when the then Sales Manager, on receipt of one of the relevant costing reports, turning to the relevant page, evaluating the information given there, picking up the 'phone and initiating our extraction from a loss making contract.

I paraphrase Ludwig Wittgenstein here is saying that "The value of information lies in the use that is made of it". We certainly on that day saved the company an ongoing few bob in losses on that contract; the information supplied was truly valuable, in straight commercial terms.

Indeed, in the reciprocal direction, we (I) developed an invoicing application, again within the same Informix database, that made our main customer of that time, Yorkshire Electricity, wonderfully interested in retaining our services at Contract renewal time.

They used information from our invoicing application, for instance, to chase their own recalcitrent engineers and to evaluate their month end accruals. They didn't want to lose those facilities, thank you very much, when the contract was up for re-awarding. Among other things, my employer's cash-flow took in a period of a few months a huge one-off hike, measured in £Millions - and interest rates were high at that time. Thank you for the (credit) interest.

___________________________

This all took place within the extended database, and without any spreadsheets. All the standard accounting facilities were formalised and supported there; Purchase and Sub-Contractor Accruals, Sales Cut-Off, Stores allocations, wages cut-off, depot allocation, plant and vehicle charging etc..

All of the stages and processing were menu-driven and hugely automated, maximising automated processing and the transfer of information between different relations in the database for onwards processing and review, and giving users bullet-proof forms in which to update values when that was needed manually.

Nobody could overwrite a formula with a value, or wreck the integrity of a block of data, or make any of the other colossal mistakes that Excel invites. 

That "application" was written well into the Codd Relational era; but it still uses pre-relational concepts such as physical addressing, such as in the "column number" aspect of vlookup. 

Both application vendors and the Spreadsheet Provider go to great lengths to perpetuate the use of the "Fortress" model, in which application databases are held out as sacrosanct and inviolable, with the gracious and generous permission granted to download data from the secure RDBMS environment and tip it into the essentially-insecure Excel environment and fiddle with it there, more or less successfully, with a greater or lesser numbers of unwitting errors, sometimes never discovered.

______________________

Codd produced his definitive "Relational Model for Database Management: Version 2" in 1990, and you would be very hard pushed indeed to find an accounting application that does not sit on top of one of the resulting and ensuing RDBMSs. 

So why are so many accountants still fiddling around in Excel?

Yours, George Gretton, 14th December 2012, Afternoon

That is indeed an excellent question and one frequently discussed both on this site and elsewhere.

George, thank you for your excellent recount of the value of the relational databases as a platform for accounting and financial reporting systems. There are more facts and Dates (pun intended) than I care to admit remembering.

Without getting embroiled in the "fiddling around in Excel question" I would just put forward this view. If you are going to use Excel as an analysis and presentation medium then PowerPivot offers a repository and environment where tables can be related and where "calculated columns", using DAX formulae, are more robust than regular worksheet formulae. Of course errors can still be introduced and this is no single version of the truth. So, as always testing by you and by someone else is essential.

I think that the fiddling around in Excel has a lot to do with the desire to be independent of or less reliant on IT support and not doubt at times has shown itself to be a double-edged sword.

Phil "the power" Taylor.

Sorry. I'll get me coat.

....and the day up to now had gone from bad to much, much worse.

I've already been admonished (quite fun, really, J) for my language on this site, so I'll hold back, subject to expressing the honest opinion that there was a right thickwut involved in my bad moring, a "Receptionist" who specialises in Rejection with a Polite Smile that Says "*** **** ********". I have made a written complaint......

Anyway, on reading your two posts, I smiled and laughed. What an improvement.

Zarathustra, I consider it likely that Phil and I can engage in some really worthwhile debate while remaining on electronic speaking terms.

I'm very much in the same mindspace as Phil in his last paragraph, and particularly interested to find a way forward, for the benefit of all accountants. 

So I'll presently be back with a thoroughly constructive response to Phil; as the expression goes, "I'll be back...."

Yours, George Gretton, 17th December 2012, 17:37