Wednesday, April 13, 2011

Is Microsoft Excel an Effective Business Intelligence Tool?

Is Microsoft Excel an Effective Business Intelligence Tool?

Whether or not you like Microsoft, their Excel spread sheet software is widely used.  It places data analysis in end-user hands.  No need to wait for the Technology Department to create a report for you or wait some more time as you find you need new data, or a different sort order, summary or relationship.

Excel is a great tool.  It puts in the hands of capable end-users the ability to organize and summarize one of the most important assets of a company, its data.  That is data analysis, but is this Business Intelligence (BI)?  By some definitions, yes it is.  You see totals, but can you draw conclusions from the reporting?  Can you forecast future trends?

For some organizations, they may only need to know how many widgets were sold over a given time period to a certain sales region.  For other companies trend analysis, seasonal forecasting, data modeling are needed.  I am not indicating that Excel cannot perform these functions.  What I am saying is, should you be doing this in Excel? 

Is Microsoft Excel just a data analysis tool or does have true Business Intelligence (BI)?  Are you using Excel and linking to the original data or are you creating silos of disconnected data?  How much time does it take for you to take the raw data and generate the finished reporting product?  Do not take offense, but are you or your users skilled enough in not only in Excel, but in your ERP software and its repository of information enough to be sure you are accessing and getting the correct information?

Modern Enterprise Resource Planning (ERP) systems have a relational database and sometimes include a data warehousing option, either directly or via a third-party solution.  A third-party solution provider is a trusted supplier for the ERP Company that has detailed knowledge about the data and the relationship it has to operations.  If there are multiple reports needed, these services are good places to start.  It is usually easier to take something that works and tweak it to fit your needs, than start from scratch.  You will have a greater level of confidence that you at least are getting the correct information to analyze.

How can you best use Excel?  Your job depends on it!
·         Link to the original data source and filter out the data you want or do not want.  Do not trust that you have the most recent data.  Do not create isolated islands of information.
·         Create reusable reports and macro functions so the results are repeatable.  You do not want to repeat your efforts every time a request is made.  Creating smaller reusable report calculations that are “Tested” means that you can reuse them again without as much worry.  Make sure you document every step.  It will take more time at first, but in the long-run you will save more time.
·         Consider your audience and decide what type of output, graph, grid and colors to use that best highlights your results.
·         Have some method of cross checking your results.  Always verify your totals to ensure accurate reports.  Think of this like a news reporter having accurate notes and a reliable source.   Make sure you are not doubling or omitting data.  It is better if you find this out, than your prospect, customer or boss.
·         Learn advance time saving features of Excel, like Pivot Tables.  Pivot Tables enable you to quickly change analysis perspective.  For example summarizing sales by customer, then by part number.  A Pivot Table easily allows you to change the axis to be part, then customer.
·         Job security or time waster?  Are you the only one who can perform this reporting function?  How is it repeatable?  Are you now tied to your workstation every month for X-period of time to create this report?
·         There are a number of resources available on Microsoft’s web site as well as virtually limitless selection of books, online courses, and training schools/individuals.  Start with the no-charge ones.  Learn and practice what you can.  That way should you need the fee-paid training, you will know what you do not know.

At Dolvin we work with companies to help them streamline their computer operations, reduce costs and become more efficient.  Utilizing a comprehensive ERP solution is part of that solution.  The developers have extensive knowledge of their database and have numerous pre-build solutions that you can run to get timely information to manage your business.  We concentrate on the back-end so you can work on running your business, not your business system.

We invite you to contact us to find out how we can improve your operations.

No comments:

Post a Comment