Home > BI for Excel > 

Excel Makes a Great BI!

Excel can be the key component of a powerful and inexpensive
Business Intelligence (BI) system.
 

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. It allows users to create new reports in a few minutes or hours. And it allows users to add virtually any new data in a few hours or days. 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.

As this illustration shows, the data can come from any available source, and from any number of  sources.

In an Excel BI, data 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 add (consolidate) the data in each dimension.

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.

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, formulas typically reference cells that contain the labels "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 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 laptop.
  
  When the forecast is done, she connects to her Excel BI over the internet at a coffee shop, and then recalculates her spreadsheet to write her forecast to the BI database at headquarters.
  
  She can update this forecast as often as she wants before the final deadline, when she will no longer have 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, Google Finance, and Swivel. 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 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 Excel BI database, and then to update the BI cube 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, Excel can handle it.

An Excel spreadsheet can pull data from a cube in the BI database; send it to the add-in; receive the results; process them in the spreadsheet, 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.

 

BI Systems Must Integrate Reporting and Analysis

Most BI systems export data as numbers to Excel. BI sales people will tell your IT department that once the data is in Excel "users can do whatever they want with it." And their sales literature will proudly proclaim that yes, indeed, their product "works with" Excel.

This attitude puts Excel users on the path to Spreadsheet Hell.

To do serious analysis in Excel, or in any other product, users need dynamic access to all the data. But numbers in cells aren't dynamic.

More importantly, users need an easy way to transform their analysis into a report. When users must analyze in Excel and then report in a different product, the report typically takes a long time to create...if it's ever created at all.

But with BI for Excel, workbooks are dynamically linked to BI data. To change a sales analysis from Widgets to Gizmos, analysts merely need to replace "Widgets" in the proper cell with "Gizmos", and then recalculate their spreadsheets.

Better yet, it's easy to turn the analysis into a report. An Excel user merely needs to error-check his workbook, clean it up so that it looks nice, and then distribute the results. They never need to talk with the IT Department.

BI Systems Must Display Information Effectively

If a fancy user interface is important to you, then an Excel BI system probably isn't what you're looking for. This is only Excel, remember.

However, if you're looking for useful information presented clearly, then Excel BI is the way to go.

With Excel, you can analyze and report data from any number of sources on one page. You can print standard reports quickly and easily.

With Excel, you can create dashboard reports, as illustrated by this report from Dashboard Reporting With Excel.

BI Systems Must Offer Unlimited Scalability

When IT people talk about scalability, they typically are thinking about the number of seats that a system can support. This is an important consideration, but not the only one.

Certainly, if your BI system can't grow from one user to the entire corporation, it's not very scalable. But additionally...

...If your current BI system can't absorb a new acquisition's BI data within several weeks, it's not very scalable.

...If your current BI system can't contain data from stand-alone ("silo") databases, or from Web-sourced economic data needed for analysis, or from multiple statistical sales forecasts, then it's not very scalable.

...If your current BI system doesn't allow your Corporate Controller to use massive amounts of its data on his laptop from an airplane, then it's not very scalable.

...If your current BI system doesn't allow managers and sales people to upload their Excel-based budgets and forecasts from any Internet connection, then it's not very scalable.

Some BI systems for Excel provide this type of scalability, but we know of no other BI system that does.

Try Excel BI for Yourself, for Free

Through an exclusive arrangement with PARIS Technologies, we've made it possible for you to try Excel BI for yourself. To try Business Intelligence for Excel, click here.