SMB Excel-eration

Small to midsized businesses (SMBs) often begin -- and sometimes end -- their quest for business intelligence with Microsoft's ubiquitous Excel. However, with an increasing number of add-ins -- not to mention a long look at what's up with Microsoft Business Solutions -- SMBs' options will multiply.

InformationWeek Staff, Contributor

October 19, 2004

9 Min Read

"Enterprise analytics" is the big buzz these days — especially in big enterprises that have the breadth of IT resources to think big. However, what about SMB analytics? How do organizations below $1 billion in revenue, and especially those in the $50 million to $500 million range, meet their analytic needs?

Put yourself in the position of a typical SMB manager. Your IT resources are limited, budgets are tight, and your employees don't have time to climb steep learning curves. Ideally, you want an analytic solution that's easy to install and maintain, inexpensive to acquire, and somewhat familiar to the people who'll use it. Not surprising then that an analytic solution that leverages Microsoft Excel is likely to be high on your list.

Anecdotally, Excel is considered the world's most popular analytic tool, partly because Excel ships with every copy of Microsoft Office: and Office is a standard component on millions of corporate desktops, whether Mac- or PC-based. Furthermore, a mainstay of business analytics is financial analytics; a recent Yankee group survey found that more than 11 percent of SMBs (in the U.S.) run all their accounting on Excel. Apparently, most of the rest use Intuit's Quicken and QuickBooks or offerings from Sage/Best Software. Some use Microsoft Business Solutions (MBS) and a few use online offerings such as NetSuite.

The alternatives to Excel analytics for SMBs often aren't that compelling: Pony up a lot of cash to get the benefit of an "enterprise-level" analytic suite that your business can grow into but might never use to its full potential. Or stick with the built-in reporting and analytics shipped with your accounting or other line-of-business application. Either way, you're between a rock and a hard place. Or you could go with Microsoft Data Analyzer, a proprietary OLAP front end that inexplicably doesn't run in Excel. Otherwise, Excel analytics using add-in applications offer the only viable alternative.

Excel's Advantages

Let's face it, the biggest advantage Excel has in terms of SMB analytics is that it's there — on the desktop, that is. It's also paid for; most people are generally familiar with it, and some are already spreadsheet jocks. Either way, Excel covers the bases in terms of visualizing, manipulating, publishing, and sharing information. So it's a no-brainer to consider Excel as a foundation for SMB analytics (you can read more about Excel integration in Cindi Howson's BI Scorecard: Excel Integration, available online in the May 2004 archive).

Excel analytic applications operate as add-ins to Excel. They're usually easy to install and integrate fully into Excel's menu, help system, and worksheet/workbook paradigm. Most vendors sell Excel analytic add-ins targeted at SMBs for $299 or less, and they can be bought one at a time; you can download many from the Web. Conversely, vendors who target analytic add-ins at larger businesses usually expect multiseat licenses that push the acquisition cost alone up into thousands or even tens of thousands of dollars — partly required to fund a direct salesperson's visit to your business.

The availability of PivotTables, PivotCharts, and other functionality, as well as the ability to handle real-time data feeds (such as stock price tickers) has enhanced the potential of Excel as an analytic front end. Integration with Microsoft SQL Server Analysis Services "cubes" provides the kind of optimized, multidimensional data source that slicing and dicing analysts depend on. As SQL Server moves down the food chain and is shipped as the database engine for more SMB applications, the ability to better leverage data in SQL Server becomes an important functional asset of Excel.

What Add-Ins Do

Excel analytic add-ins adopt the look, feel, and functionality of their host and manage a wide range of tasks, especially if they're connecting to a multidimensional data source, such as an Analysis Services cube. Add-ins may provide general ad hoc "slice and dice" analytic capabilities, a specific or upgraded analytic function (such as improved data mining or scorecard visualization), or the ability to generate production reports from within Excel. A partial list of capabilities includes:

  • Selecting data hierarchies or dimensions within those hierarchies to construct meaningful data views (for example, selecting specific cities or salespeople within a territory hierarchy to help with sales analysis.)

  • Navigating the data presented by these views using drill down, drill across (from one hierarchy to another), or drill through (from the summary to transaction level data).

  • Visualizing the data presented by these views in the form of tables, hierarchy trees, 2-D and 3-D charts, scorecards, and top-n or bottom-n rankings.

  • Data write-back so that changes made to numbers during the analytic process are written back to the data source from Excel (for example, to increase or decrease budget numbers after they're compared to actuals)

  • Securing the data based on user roles so that people only see what they're entitled to see; also, using simple business rules to highlight exceptions in the data (for example, using a numeric threshold to change the color of the data if a value is above or below the threshold).

  • Creating metadata layers to allow business users to more easily understand, navigate, and select data in complex back-end applications (such as an accounting general ledger module).

  • Formatting information into content packages (reports and report packs) that you can email to other users, access over the Web, or simply share with other Excel users.

The Add-In Circus

Excel analytic add-ins come in all shapes and sizes. All the well-known enterprise-level analytic and reporting vendors, such as Actuate, Business Objects, MicroStrategy, OutlookSoft, and ProClarity provide add-ins to provision data from their applications into Excel. This is proof of Excel's popularity at the desktop — even in larger enterprises, not everyone wants to learn another proprietary user interface. But here, we're concerned with add-ins targeted at SMBs. So, a good place to start is with Microsoft itself, especially as these add-ins are currently provided as free downloads for existing Microsoft Office customers.

Two downloads available now are the Microsoft Office Excel Add-in for SQL Server Analysis Services and the Microsoft Office Tool for eXtensible Business Reporting Language (XBRL). There's also another analytic download — the Microsoft Office Business Scorecards Accelerator — to help with creating key performance indicator scorecards in a Microsoft SharePoint portal. The first lets users of Excel 2002/2003 create and access Analysis Services cubes, select and navigate data sourced from these cubes in Excel, and publish the information in the form of reports. The second allows you to download or import XBRL reports into Excel (and Word) and perform limited analysis of data in XBRL reports — for example, to compare and contrast the XBRL financial statements from the hundreds of U.S. public companies now accessible via EDGAR online.

Figure 1

Microsoft Business Solutions also provides some Excel capabilities for analytics. Financial statements created in Microsoft FRx can be exported to Excel for further analysis or converted into an Excel PivotTable for easier navigation and charting. The reporting and analytic capabilities in Microsoft Axapta, Great Plains, and Navision all allow you to export reports to Excel and data to Analysis Services cubes for access from Excel. General ledger budgeting in these applications also generally allows you to export budget and actual data to Excel and then import it back in to the application.

Microsoft Partner AnalySoft provides another option for Excel analytics from MBS applications. AnalySoft AnalyServer can rapidly create a data warehouse from data stored in Microsoft Navision and then use the XLCubed Excel add-in to manage the data in the warehouse. There are many enterprise-level data warehousing tools that connect to and autocreate warehouses from top-tier ERP systems like SAP and PeopleSoft; alas, few do the job with systems that SMBs actually use. Note, however, that SMBs don't use MBS accounting applications as much as those from Intuit and Sage.

Although you can export reports from Intuit QuickBooks into Excel, surprisingly few Excel add-ins exist for either QuickBooks or Sage products. Synergration's eXcelBooks allows users to pull data directly from QuickBooks into Excel spreadsheets and refresh these data views or reports on demand. Users of Sage Line 500 can use the Sage Business Intelligence add-in (provided by IntelligentApps) to do drill-down analysis, create exception reports, and perform business analysis on data sourced from their financial management applications. There are other offerings, but most appear to be limited solutions built by resellers rather than designed and marketed as commercial solutions.

Users of other SMB accounting systems such as Sage BusinessWorks, Macola 7, and MAS 90 could use Lasata's F9 add-in to pull financial data directly from their accounting systems into Excel. F9 provides a predefined metadata layer to make it easy to find and select the information to pull into Excel from these and other accounting systems so that financial reports can be constructed, manipulated, and published via Excel. F9 has been around a long time and its current owner, Lasata, has just been acquired by U.K.-based Systems Union primarily for its other Excel add-in — VisionXL — which for some time was the only analytic solution available that could deliver analytics on top of the combined ledger structure of the SunSystems SMB accounting suite.

But what if you want to get data into Excel in order to publish it more widely either within your organization or across the Web? Then Infommersion's Xcelcius product may be what you need. Xcelcius is unusual in that it can export data analytic views and reports created in Excel to Microsoft PowerPoint and Outlook or into a Macromedia Flash (.swf) presentation with real-time links to the underlying data to preserve "interactivity" with the data. These capabilities add a new and interesting dimension to report or scorecard publishing by democratizing analytics via the Web.

Redmond Rising

There's a vibrant market in Excel add-ins for SMB analytics. However, I wonder how long this market can continue as Microsoft begins to release its own add-ins and finally starts to leverage the synergy between Excel, SQL Server Analysis Services, and Microsoft Business Solutions (including Microsoft CRM, which is aimed squarely at the SMB market).

The Office suite is clearly positioned and intended to become Microsoft's universal business client on the desktop. Plus, Office's increasing BI capabilities are receiving more attention from SMBs. So, it may not be long before Office comes to symbolize desktop BI, just as it's now the de facto standard for desktop word processing and spreadsheets. In the meantime SMBs should consider helping their own by supporting the SMB vendors of Excel analytics — while they still can.

Stewart McKie is an independent consultant and technology writer specializing in analytic, enterprise resource management, and Web services applications. Reach him via his Web site at

Never Miss a Beat: Get a snapshot of the issues affecting the IT industry straight to your inbox.

You May Also Like

More Insights