Business Intelligence with Excel

[nishat15-10945]

Excel Is Great for Business Intelligence!
Excel can be the key component of a powerful and inexpensive Business Intelligence (BI) system.
by Charley Kyd, MBA
Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports
With the right access to data, Excel can become an outstanding Business Intelligence (BI) system.

Business Intelligence (BI) is the systematic use of information about your company and its business environment to analyze, report, predict, and manage business performance.

Some would say that this definition describes what Excel has always done for business. But Excel users know that's not true. Historically, Excel has had two significant limitations that have kept it from serving as a true BI tool.

First, Excel isn't known for its ability to generate easy-to-read reports and analyses. But as we show in our Excel Dashboards section, a little user training can eliminate this problem entirely.

Second, however, native Excel handles data poorly.

Virtually all standard "solutions" drop values into spreadsheet cells. This creates many problems: A number or label in a cell has no intelligence. We don't know where it came from. We can't tell if it's been changed manually. We often can't update it easily. We typically can't cause that number to be placed where it's actually needed in our report. And so on.

In short, the way that native Excel brings data into a spreadsheet is not only its greatest limitation as a BI tool, it's the most significant source of  Spreadsheet Hell.

BI systems for Excel not only correct these problems, they add important features. In fact, an Excel BI provides more power for general business management than any other BI system I know.

Let's take a closer look at the key characteristics of an Excel-based BI system.

BI Systems Must be Agile
In 2005, IBM published an excellent whitepaper, The Agile CFO. IBM's own CFO, Mark Loughridge, wrote the introduction.

"As the demands placed on Finance continue to expand," he wrote, "CFOs are challenged with the paradox of delivering a stable, highly effective finance environment while also providing the agility to respond and react to rapidly changing business climates."

Similarly, in his book, Manager's Guide to Strategy, Roger Formisano also expressed the need for agility. "Strategy development is continuous and iterative," he wrote. "[W]e must try and adapt to changing markets and competition."

But typical information systems are as agile as a tomb.

In many companies, if a manager needs a new report she creates a detailed spec then finds a sponsor for the project. She submits a Work Center Request to the Work Center Prioritization Committee that meets every other Tuesday. This committee discusses the request and assigns a project manager who eventually develops a budget, assigns resources, and manages the project queue. Then, three months after the need has passed, she receives a report that only resembles what she needed.

Instead, in smaller companies, she adds the report to a long list of other reports that she must create manually each week or month in Excel.

On the other hand, an agile system allows users—not programmers—to change reports in a few minutes. And it allows users to add virtually any new data in a few minutes. But most important, it allows users to update the reports in seconds.

Most BI systems aren't that agile, but Excel BI can be.


BI Systems Must Integrate and Consolidate Any Data
If data only could be stored as numbers in spreadsheet cells, then consolidation would pose a significant problem for an Excel BI. But in fact, spreadsheet cells that rely on an Excel BI don't contain data, they contain formulas linked to data.

And the data can come from any available source, and from any number of  sources.

In an Excel BI, data typically is stored in a multidimensional (OLAP) database, which is very similar to a group of multidimensional Excel workbooks. Each of these workbook-like containers is called a cube. Most cubes contain formulas that consolidate the data automatically.

Cube formulas can support more sophisticated calculations. For example, they can calculate ratios, do currency translation, discount cash flows, calculate logarithms, and so on...just like a workbook can.

But they can do it in multiple dimensions. Automatically.

Excel users enter spreadsheet formulas that return numbers, text, or calculated values from any cell in any of those cubes. For example, a formula like this...

=GETDATA("Sales", "Units", "Hats", "Store 19", "Oct-2007")

...returns unit sales information consolidated by product line and region for the month shown. And this formula...

=GETDATA("Sales", "Sales USD", "Hats", "Store 19", "Oct-2007 YTD")

...returns year-to-date sales in US dollars for the same product line and region.

As with any Excel formula, of course, OLAP formulas typically reference cells that contain labels like "Units", "Hats", and other arguments. The formulas typically don't contain the arguments themselves. Using this approach, you could replace "Hats" with "Coats" in a cell, and then recalculate, to create a report about coats.

To my knowledge, no Excel BI product has a function named GETDATA. But they all have functions that work about like the GETDATA function shown here.


BI Systems Must Support User Updates
A good BI system should allow users to update its database, with full security, of course. Here are some examples of how this capability is used:
A sales person works on her sales forecast on a laptop as she has time between sales calls. To do so, she relies on detailed sales history that her spreadsheet returns from a cube on her company's server. She has read and write access to her cubes because she's linked to them using the Wi-Fi at a coffee shop.

She can update her forecast as often as she wants before the final deadline, when she will be denied permission to write to the cube.

A manager on the other side of the world enters his budget data into an Excel workbook, which immediately writes the data to the same database over the company's network.

A clerk in the home office manually enters key industry data from a trade publication each week. Several Excel reports compare this data to internal measures performance.

An accountant downloads selected financial data each month from  Yahoo Finance and Google Finance. In addition to interest rates, economic trends, and other key numbers, the data describes the financial condition of the company's competitors, customers, and key vendors. Then he recalculates his spreadsheet to upload the data to the BI database, making it available to managers and Excel users.

An analyst downloads operating data as text from a "data silo" in her company, creates a cube on her local drive to contain the data, and then runs a process to import the text file to her cube. Once she tests the cube for accuracy, she works with the database administrator to upload the cube to the main OLAP database, and then to update the new data automatically on a regular schedule.

BI Systems Must Offer High-Quality Analysis
"Yeah, we offer analysis," standard BI vendors say. But Excel users know that Excel has more than 300 spreadsheet functions, back-solving capability, and the ability to contain virtually any data. No other product does a better job of business analysis than Excel.

BI for Excel can give Excel users easy access to data from virtually any source. If you want to compare sales in Sicily to the price of tea in China, using Australian dollars, you can do it with BI for Excel.

But what if ordinary Excel can't do the job? What if you want to do time series forecasting? Monte Carlo analysis? Decision tree analysis? Non-parametric testing? Fourier analysis? Or some method that's really obscure? With the right add-in, or with a young new-hire who still remembers his college statistics, Excel can handle it.

An Excel spreadsheet can pull data from a cube in the BI database; process it in a workbook; receive the results; process them in some other workbook, and then write the results back to the BI database...all with a simple recalculation of the workbook.

No other BI system provides such analytical power.
source:exceluser.com