#18 MS Excel as Business Intelligent Tool

Date: 5 April 2012

ERP- Enterprise resource planning as name suggests it is to plan about the resources in an Enterprise. When we say planning that means forecasting future and making decision that will help an Enterprise to grow at the steady pace. Good decisions which define the fate of an organization can only be made by peeping into the piles of data which have been accumulated by the course of organization’s operation. As a saying goes “One cannot make bricks without mud”, similarly one cannot make good decision if there is no data to work on. Further this data should be used efficiently to make forecasting dissension. One of the advantages of ERP is that it securely builds that heap of data to be used for the future forecast.

Further to utilize the data accumulated by ERP, effective Business intelligence tool is required. There are various tools available in the market that may be good what they claim. But what can be better than the one which is already known to most of the people. Especially in the management teams around the world there are hardly anyone who doesn’t know Microsoft Excel. Yes, MS-Excel can be used as BI tool also as they say “Known devil is better than unknown angel”.

Let’s take an example to see how we can use excel as BI tool. There is a sales head that wants to see the past performance of the company to make various decisions. Let’s see how we can accomplish this in MS-Excel and how we can easily convert data to a graph.

Microsoft Excel shipped with a very handy tool called Microsoft query. Microsoft query give power to the users to extract data from almost any data source. We just need to create an ODBC in the System DSN. In MS Excel “Data” tab there is option to extract data from “Other sources”.

There is an option called “From Microsoft Query”. When you select this option you will get the screen to select the data source.

 

Select your data source and click “OK” to open Microsoft Query screen as bellow:

 

Here in Microsoft query you can enter you SQL statement to extract the sales data. Once you did that you can press “Return Data” button to send the data on you selected excel sheet. The data looks like as follows:

 

Now the same data can easily be converted to graph or any other type of diagram that user wants to represent data in much better way:

 

Now it’s up to the capability of the individual to make it more and more interactive. Above is an easy example, but we can make and design much complex reports as well where user can enter parameters to extract the data that he wants and see it in different formats like table, bar diagram, pie chart etc.

Aggyey