Software informational articles

Olap, an complementary knowledge over spreadsheets - software


Are Spreadsheets Robbing your Venture of Competitive Advantage?

'90% of "average" companies are not assured that their forecasts and information are precise and reliable'

In a hot study, 81% of FD's cited that their main priority is the exactness of revenue and dividend forecasts while 63% complained of derisory budgeting and forecasting systems .

The contemporary FD is advent under ever-increasing burden from all sides to be the source of more robust, evocative and perfect monetary information. This is determined by a array of factors:

  • Internet knowledge is creating new big business models that command innovative fiscal models

  • The emerging affair ecosystem is creating more contest that requires in order based on dynamic competitive scenario analysis

  • The contemporary accounting scandals and the narrow comeback to those call for a privileged level of data integrity and accuracy.

All stakeholders in the project are requiring more analysis, based on composite models in shorter time periods, with accurateness and the capacity to describe anomalies in the data existing overriding to the booming management of the enterprise.

It is attractive then that a appraisal of 2000 companies on pecuniary best practices by the Hackett Group bare that two-thirds of "world-class" companies and 90% of "average" companies are not assertive that their forecasts and intelligence are exact and reliable. Why?

Consider two major systems from which this data is collected.

  • Multiple ERP systems are used to assemble data for budgeting, forecasting and reporting. The inter-compatibility of these systems can cause inaccuracies.

  • Second, spreadsheets still calm down a major part of the budgeting, forecasting and exposure functions of the finance department.

    There is a developing body of examination presentation the evils allied with using spreadsheets inside the finance department. That may be well and good, spreadsheets may not be the best arrangement to use in the finance department. However, a satisfactory different has not been obtainable for the use of spreadsheets, and as such the do research into the use of spreadsheets is of barely doable value to the finance world at large. The distrust still remains:

    "Can other Technologies change Spreadsheets inside the Finance Department?"

    Why are Spreadsheets used?

    Quite simply, for the reason that they can be. Finance professionals with very barely data of cpu software development, encoding or claim blueprint are able to build center models that can be used to administer the finance function. Also, spreadsheets are extensively used and obtainable surrounded by the project and the bulk of in a row users have contact to and awareness of how to use spreadsheets.

    So, what is the catch with spreadsheets anyway?

    A study by Coopers and Lybrand showed that 90% of all spreadsheets with 150 rows had errors. An added study by KPMG showed 92% of spreadsheets production with tax issues had important errors and 75% had accounting errors.

    In general, the tribulations linked with spreadsheets can be split into two main areas:

    Design, Development, Flexibility and precision of home processes

    It is correctly as most Finance people, who are dependable for mounting and maintained the models, are NOT educated in the conceive and advance of worksheet models that there is an issue. No Economic or IT Boss would allow an categorical and/or inexperienced file executive to arise and assert the vast and center transactional databases that now run Businesses. Yet, when it comes to the conceive and advancement of Management Reporting, Budgeting and Arrangement systems, which are relied upon to cope corporation businesses, this apply is commonplace. The issue here is not that the Finance Area is not financially astute, they are. The issue is that they are not technically taught in the use of Spreadsheets.

    Spreadsheets are inherently inflexible to changes in the blueprint of the models they map. This is due to the approach spreadsheets use to link data, which is on a cell-by-cell basis. The domestic formula structures in print into table models are not dynamic, so if there is a adjustment to the Character of a formula in one sheet, it is not by design cyber- in all the later sheets or workbooks. Every model change, no be of importance how small, has to be manually fake in each exaggerated sheet and/or workbook.

    Further, it is not feasible to adhere to what attitude is being used to drive the model in a spreadsheet. This is as all the formulas that are used to fix and manipulate the data inside the model are hidden. There is a awful lack of intelligibility of the underlying formulae and as a result the method being used to drive the models.

    Data Integrity

    Even although there are issues as described above, these issues are more about the chunk of time essential to develop, argue and adjustment Worksheet Models. If the income are available, then these issues associate to the able use of resource. Of more affair is the integrity of the data being reported.

    Data in Spreadsheets tends to be held in break away workbooks that are dispersed and worked on by a category of users in cold locations. These workbooks are then associated by formula to each other. These links, however, break up the total model. If you alter data in one workbook, there is no way of deliberate whether these changes have been integrated in the full model. This, for the finance branch is the main distinct collapse of Spreadsheets.

    As described above, since the formulas contained by spreadsheets are hidden, it is not achievable to create the appropriateness of these formulas not including a large sum of blue-collar reviewing. Also, as each manual is a branch out entity, just for the reason that one course book is accepted dose not means that all the other workbooks used in the model are correct. Errors inside Spreadsheets are an established drawback for most finance departments, yet this fact is seldom communicated to users.

    OLAP, an another technology

    OLAP, or Online Critical Giving out is a know-how that was termed as such in 1993 by Dr. Codd who false the relational list model. OLAP was used formerly as a exhortation to differentiate it from OLTP (On-Line Transaction Processing). T was replaced by A to accentuate the Critical capabilities of the new know-how as disparate to the transactional capabilities of the relational file technology. Today, OLAP is used as an umbrella term for a range of technologies that used to fall under the terms assessment support, commerce acumen and executive in a row systems among others.

    OLAP uses Dimensions to map the underlying basics of the business. For instance, in a characteristic International FMCG the Dimensions used would be:

    Business Unit: which would map the underlying arrangement of the enterprise, both statutorily form a legal creature point of view used for economic exposure purposes and managerially for monthly exposure purposes that may be from a responsibilities point of view. With spreadsheets, one view is all that can be achieved with one model.

    Product: which would map the coherent make up of the consequence offering. This would comprise Brands, Sub Brands, SKU, Pack Size, Colour and the like. Again, this depth of breakdown would command a large and center worksheet model.

    Geography: This dimension would map the animal geography of the world. It could be used to comply with Segmental Treatment food for Monetary Reporting. It could also be used to classify the currency being used to report.

    Customer: This dimension is of basic consequence in the Sales and Debtors cycle and would map the Customers who buy products.

    Measures: This is the main dimension where data is stored and would customarily control the chief ledger accounts. Also in this dimension would be digest dealings for say, Total Sales, GP and GP%. Non-financial data could be stored in this dimension such as head count. It is doable to use calculations contained by this dimension that rival those obtainable in spreadsheets.

    Period: This dimension would map all the bulletin food for reporting. Month, Quarters, Half's and Years.

    Dimensions, a functional concept

    A key affair of OLAP is its use of Dimensions that are used to model the underlying brass tacks of the enterprise. The relationships contained by these dimensions are represented and manipulated graphically. It is easy to ascertain what makes up 'Total sales' for example, or what geographical regions have been integrated in 'Region 3' See Appear 1.

    Figure 1 - Graphical OLAP Interface

    Also, these relationships are by a long way manipulated. If, for instance, the group efficient so that Italy now falls into County 3 as a replacement for of Borough 1 as shown in Assume 1, it is a be important of dragging and reducing this land into borough 3. See Amount 2. This makes running the models residential using OLAP relativity down-to-earth and intuitive as compared to spreadsheets. Also, changes made are useful to all the important data held surrounded by OLAP database. The table models would have to be in isolation changed.

    Figure 2 - Ease of Supervision Models

    Often, there is more than one way of in place of a relationship. For instance, Gross Profit in the above model is determined by both home and exterior trade. It is achievable to model the atypical ways of deriving Gross Profit as shown in Appear 3. There can also be assorted relationships that are ambitious by big business fundamentals. The 'alternative relationships' are by far modelled. For instance, countries in the geographical dimension can be part of a county as well as a zone. See amount 3.

    Contrast this with the condition if spreadsheets were used to drive this model. It would not be doable to care about all these dimensions simultaneously. Most likely, the data would come in as a hierarchy of associated spreadsheets with each privileged level consolidating and cutting the in a row in the lower level spreadsheets. The sheets lower down in the hierarchy would store in order of less significant geographic regions while the elevated level spreadsheets would control consolidated in sequence of better and superior regions until the top worksheet would join and condense the accomplished data for the whole borough in which the business was operating.

    The spreadsheets would be disconnected, lack lucidity of the whole model and be very arduous to adapt contained by an conventional time frame. The capability to visually map and manipulate hierarchies, as well as be atypical views of relationships among items in dimensions is a clear improvement of OLAP.

    Fast, Fast, Fast

    This is the tenet of most certitude makers today. However, admission to data and in rank compulsory to make decisions tends to be held in transactional systems which the conclusion maker both dose not have contact to or does not understand. This requires the choice maker to application in rank to be all set by the finance department. There is an clear time delay in the turn about of these requests.

    OLAP is a equipment that can be dispersed to many users using a brand of platforms. As there is a free store of data held contained by the OLAP 'Cube', data and in a row can be accessed by many users all together at any rate of their location.

    As the dimensionality and hierarchies map the essentials of the business, analysing data is an intuitive process. It is not crucial to absorb the underlying sources of data and as such in a row becomes understandable and available to a better inhabitants of the enterprise. Managers can key their own data chemical analysis questions not including ceremonial desires to the Finance Department.

    Fixed Configure Coverage versus Drillable Reports

    Spreadsheet gossip are fixed design reports. They cannot act for the data held in them in any other way. If advance assay is required, this assay is not existing contained by the database report. Any added examination will compel a new arrive that will by and large call for a new model to be developed. Ad Hoc arrive wishes for advance assay and investigation are challenging to accomplish in a database environment. With OLAP, as both the underlying big business brass tacks and the data are stored in a definite data store, the capability to analyse data in an Ad Hoc approach is inherent in the technology. Data in the OLAP Cube is stored in an capable conduct expressly tailored to analysis. It is for that reason feasible to analyses data contained by gossip on the fly, 'Drilling Down' or 'Up' to the underlying data which makes up the reported figure. The capacity to drill by means of reported data is even achievable to the transactional level, the last level of analysis.

    The real time data dream

    As soon as an dig out of data is done from the a mixture of ERP systems used inside the enterprise, the data is out of date, as it may have misused since the extraction. The bulk of time spent on the reporting, budgeting and preparation course domino effect from extraction of and ensuing glance of data extracted. Spreadsheets do not lend themselves to real time data extraction and any capability for a worksheet to extort data is definite to a detail data source. A mixture of ERP systems have the functionality of extracting data into spreadsheets but this means that the enterprises flexibility in emergent its inner systems is reduced. There is a self perpetuating cycle, as the ERP approach can extort data to spreadsheets, spreadsheets are used. For the reason that spreadsheets extort data from a detail ERP, that ERP has to be used.

    The description of the large-scale commerce advertise requires real time data, a necessity that spreadsheets fail to live up to. However, as OLAP is a core folder technology, it is able to commune with other databases seamlessly which enables data to be extracted from find systems on demand. The real time data dream is no longer a dream. It is now doable to arrive weekly, daily, even hourly with accurate, eloquent in order clear by a wide range of users with barely or no appreciation of the structures of the underlying data sources.


    There ashes with out a doubt a place for spreadsheets in the finance department. The use of spreadsheets as a tool for compelling the Reporting, Arrangement and Budgeting functions of the venture are however, questionable. It is clear that there are other technologies accessible which suit this affair better.

    There are two main reasons why spreadsheets are still used today and why different technologies uptake in the finance administrative area has been poor.

    Firstly, there is a lack of accepting in the finance branch of the choice technologies free to bestow solutions to troubles encountered. There is also a lack of agreement inside the IT area of the evils being encountered contained by the finance department. One branch does not appreciate the problem, the other area does not be au fait with the solution.

    Secondly, justifications of spend. Acquiring and implementing new technologies requires funding and the remuneration are not certainly quantifiable in pecuniary terms nor are they at once felt. Most projects command hard amount paybacks contained by the year in order to adjust the expenditure. With data accuracy, ease of examination and use as well as aptness as the main compensation of OLAP, it is often challenging to defend the spend.

    The adulthood of world class enterprises today have embraced OLAP as a key equipment in delivering in sequence to assessment makers. The main difficulty that must be asked is not if spreadsheets be supposed to linger the main platform for Reporting, Budgeting and Forecasting. Instead, enterprises must ask themselves how much competitive gain they are all set to lose by means of the use of inaccurate, out of date and inflexible in sequence beforehand different technologies are investigated.

    Shaun Stoltz is the Supervision Administrator of Data C Ltd, a Finance Systems Plan Consultancy. He can be contacted at shaun@data-c. com or ceo@inkorus. com.

    www. gemolap. com
    www. inkorus. com
    www. istrat. co. in

    About The Author

    Having served his Articles in Affair Assertion with PricewaterhouseCoopers, Shaun was identified and qualified as a Computerised In sequence Systems Assessor where he happening his data journey in Technology. All over his career, Shaun has had a keen advantage in Equipment and the solutions and drawbacks that thrive in this field, in the course education such diverse technologies as Nerual Netwroking and Artificail Inteligence Techniques to aloof managment of Networks using PXE protocols; archimed@istrat. co. in

  • Developed by:
    home | site map © 2018