“CFOs at companies including P.F. Chang’s China Bistro Inc., ABM Industries Inc. and Wintrust Financial Corp. are on a similar drive to reduce how much their finance teams use Excel for financial planning, analysis and reporting.
“Finance chiefs say the ubiquitous spreadsheet software that revolutionized accounting in the 1980s hasn’t kept up with the demands of contemporary corporate finance units. Errors can bloom because data in Excel is separated from other systems and isn’t automatically updated.”
This is according to the article, Stop Using Excel, Finance Chiefs Tell Staffs, which appeared in the November 22, 2017 issue of The Wall Street Journal.
The opinions expressed in that article could not be farther from the truth. But unfortunately, it’s a common opinion that’s encouraged by vendors of Business Intelligence (BI) and Software as a Service (SaaS) products.
The simple fact is that Excel users can work without limits…in two ways.
First, we can work without limits in the amount of data to which our worksheet formulas can link. Every day, many Excel users write worksheet formulas that return values from files containing petabytes (billions of megabytes) of data.
Second, we can work without limits in the number or type of sources that populate our database and to which our worksheet formulas link. Our worksheet formulas can return data originally from…
- many data warehouses,
- many silo systems,
- many ERPs,
- many sources on the web,
- many currencies,
- many budgets and forecast created in Excel,
- many versions of those budgets and forecasts,
- etc.
Worksheet formulas can reference any level of consolidation. And the data is as real-time as the source systems can support.
And also, for budgeting, forecasting, planning, and so on, our worksheet formulas can write to the server…with full security, of course.
I did all these things twenty years ago, and the technology has improved significantly since then.
For more than 20 years, medium and large companies have been using an Excel-centric technology to solve all the Excel problems discussed in the WSJ article.
The solution uses a hub-and-spoke design in which Excel formulas are live-linked to an Excel-friendly Database, as this figure illustrates.
An “Excel-friendly database” is one designed to support the use of Excel formulas…for both reading from the database and writing to it, with full security.
To be clear, when you use this hub-and-spoke strategy, your workbooks contain NO data. This is in sharp contrast to the direction that Microsoft is urging us to take with their Power Pivot technology in Excel.
When we use the hub-and-spoke strategy, our workbooks contain a few settings, perhaps some text, perhaps some charts, and many worksheet formulas linked to data on the hub.
Unfortunately, very few databases are Excel-friendly. In fact, I know of only two and a half of them.
For small applications with only a few megabytes, Excel Tables can work very well as the source of data. Unfortunately, Microsoft offers no way for Excel formulas to write back to Tables, which is why Tables offer only half of a solution. However, Power Query (Data, Get & Transform in Excel 2016) does offer many ways to update Excel Tables with source data.
But Table databases only work in a hub-and-spoke environment if they’re set up properly in separate, database workbooks.
For larger data, the solution is to use one of the two Excel-friendly OLAP products. At least one of them can scale to petabytes—one billion megabytes. For most companies, that’s like having no limit at all.
The Excel Friendly OLAP Products
One of these is IBM’s product, TM1. The other is PARIS Technologies’ family of products, Olation and PowerOLAP. I’ve found PARIS easier to deal with than IBM, and Olation has remarkable features that enhance other OLAP products.
I’ve never seen an ad or article about either product. And customers who use them tend to treat them as a competitive advantage, which gives those customers good reasons not to talk publicly about their use of this technology.
This is why you’ve probably never heard of Excel-friendly OLAP products, nor of TM1, PowerOLAP, or Olation.
All of these products support worksheet functions that return data and metadata from multidimensional OLAP tables, called “cubes,” as illustrated here.
Both products offer several worksheet functions that write data to the database. Some of these support manual data entry (typically for budgeting and planning), and another function allows Excel formulas to write to the database (typically for formula-based forecasting).
Both products also support cube formulas, in which Excel-like formulas in the database can perform automatic calculations across many dimensions in many cubes. So, for example, one cube could contain exchange rates, while formulas in other cubes could support a Currency dimension that allows users to choose which currency they wish to display in Excel or in presentation tools like Tableau or Power BI.
Critically, cube formulas provide the foundation for a business rules engine. For example, a cube formula could define a calculated measure like Gross Profit, which always would reflect the same calculation in every workbook or presentation tool in the company.
At the other end of the scaling spectrum, PARIS offers single-user versions of PowerOLAP. But the last I heard, IBM does not.
Because I’ve found PARIS easier to work with than IBM, I’ve arranged for a free trial of their single-user product, which you can download at the link.
PARIS’s Olation product is a Real-time Data Hub that integrates both OLAP and relational data, in real time. It can integrate data from SAP Business Objects, Microsoft Dynamics, Oracle, SQL Server, SharePoint, Access, salesforce, Epicor, ADP…just about any relational data, in fact. And it also can integrate TM1 and PowerOLAP data.
Olation gives Excel users the ability to link their worksheet formulas to data from all of those relational and OLAP systems.
And because Olation also can deliver real-time OLAP “slices” of cubes as relational tables, it gives presentation products like Power BI, Tableau, QlikView, and arcplan the ability to display any combination of any available data, including plans, forecasts, data from the Web, benchmarks, and so on…all in real time.
To illustrate an Olation-based real-time solution, I know of a financially healthy retailer with more than 2000 stores worldwide, whose executives get currency-translated sales results at any level of consolidation on their mobile devices…in real time.
Not only can the executives see how today’s worldwide sales and gross profits are trending by hour in US Dollars, they could see how a specific SKU’s sales—denominated, say, in Euros—have been doing in their Canadian stores during the past few minutes.
The data flows continuously from cash registers, through their SAP ERP, to their Olation-managed cubes. Excel users with the proper credentials can read the same data, of course. And the company relies heavily on cube formulas to generate rolling forecasts and provide analytics, including their extensive use of test-and-learn.
Excel Problems and Solutions
Now let’s see how the hub-and-spoke strategy can solve the problems described in the WSJ article…
Disparate Systems
Adobe’s finance chief complained that his team struggles keeping track of which jobs have been filled at the software company. It can take days for Excel users to pull data from disparate systems to see which groups are hiring and how salary spending affects the budget.
This problem was solved decades ago.
Both TM1 and PowerOLAP can contain data from those disparate systems. Both would allow Excel formulas to display the numeric or textual data as needed and to update the display at a later period—merely by recalculating the workbook.
And Olation can maintain the data in real time and allow not only Excel but other presentation products to display the data.
Importing and Exporting Data
“I don’t want financial planning people spending their time importing and exporting and manipulating data, I want them to focus on what is the data telling us,” Adobe’s finance chief said.
Exactly! That’s the whole idea of an Excel-friendly database. The data flows into the database and Excel formulas merely link to it for reporting and analysis.
In contrast BI and SaaS systems can support only the data they’re designed to support. And they’re generally not designed to talk to each other. Worse yet, many of the cloud-based systems use multi-tenant databases, which make it difficult or impossible for customers to merge that data with other data.
In effect, many BI and SaaS products have introduced a whole new generation of information silos.
If your other systems can’t link to your SaaS data, is it really your data?
On the other hand, Real-time Data Hubs (RDH) like Olation, are designed to eliminate silos.
For example, do you want to compare your revenue data to data about your ad buys? Or to weather by region? Or to your marketing budget by sales office? Or to the unemployment rate in your major markets? Or to the turnover of your salesforce by customer? Or to the economic health of the major industries to which you sell?
If you can access the data, an RDH can import and manage it easily. And then, one Excel user in Germany could write cube formulas to define measures of performance that compare internal and/or external data to other such data, and then a marketing manager in San Diego could display the current value of those calculated measures in real time on a digital device.
And meanwhile, an analyst in Seattle could monitor trends in those new measures of performance in an Excel or Power BI dashboard…in real time.
Automatic Updates
“Finance chiefs say the ubiquitous spreadsheet software that revolutionized accounting in the 1980s hasn’t kept up with the demands of contemporary corporate finance units. Errors can bloom because data in Excel is separated from other systems and isn’t automatically updated.”
Exactly! That’s why Excel-Friendly OLAP was invented decades ago.
First, Excel isn’t a database program, although Excel Tables can serve as a small database. And second, because Excel formulas can reference Excel-friendly data on the server directly, Excel ALWAYS can be updated automatically. And having live links to the data slashes errors significantly.
In contrast, with their products like PowerPivot, even Microsoft has begun to turn workbooks into silo databases that can’t share their data with other workbooks and that don’t update in real time.
Collaboration
Older versions of Excel don’t allow multiple users to work together in one document, hampering collaboration. There is also a limit to how much data can be pulled into a single document, which can slow down analysis.
Excel users in distant locations have collaborated for decades. But they haven’t shared workbooks, they’ve shared real-time data.
Merely sharing workbooks is a poor substitute for data-sharing. And creating monster workbooks always is a poor practice in any case.
When users share data using Excel-friendly databases, they can see updates in real time. And, with the appropriate permissions, they can update data like budgets and forecasts that other users can see in real time.
By sharing data rather than workbooks, different users can view the same data in different ways.
For example, an office manager could enter her budgets in a workbook designed for that purpose. A department head could monitor summaries by office in real time. And the budget director at Corporate could monitor worldwide budgets at any level of consolidation, in real time.
And meanwhile, a budget analyst on a different continent could update the budgets, as well. If he makes a data-entry error, it’s no problem, because all changes are tracked by user, and can be reversed easily.
P.F. Chang’s finance chief Jim Bell said he switched the company to [a SaaS product] from Excel because it fosters collaboration and cuts down on administrative tasks.
Mr. Bell said he was examining how kitchen staff cuts at the company’s Boston restaurants affected profitability while on a flight from Spokane, Wash., to Phoenix in early October. The company’s northeast regional manager followed along from his office across the country. “If I was trying to do this on a spreadsheet, it just wouldn’t happen,” Mr. Bell said.
I wonder, can P.F Chang’s other systems link to thier new SaaS data in the cloud? If they can do so, they could have seamless, real-time access to the data. Or can users only export different chunks of that data at a time as CSV or Excel files?
In other words, is their new SaaS product another expensive information silo? This is an important question to ask any SaaS vendor about its products.
In contrast, with a Real-time Data Hub design, petabytes of data can be completely integrated and widely shared—with full security.
That is, Mr. Bell could have used Excel, or products like Tableau—both live-inked to cubes on P.F. Chang’s own server. Therefore, he also could have compared performance in the Boston restaurants to any other internal and external data, not just to whatever data one SaaS system happens to contain.
For example, the Federal Reserve Economic Database (FRED) contains the data series, STTMINWGMA, which shows Massachusetts’ minimum wage rates by year since 1968. Perhaps changes in the minimum wage had some bearing on profitability in Boston.
An Excel-friendly database easily could contain that data, along with more than 1000 other economic and population data series about Boston or Suffolk County, for quick analysis in Excel.
Data from Different Financial Systems
Kayla Davis, who runs financial planning at ABM Industries, relied on Excel to pull data from a motley of disparate accounting systems, accumulated over decades of mergers and acquisitions.
Excel-friendly OLAP systems do an excellent job of consolidating data from motley financial systems using disparate charts of accounts.
Years ago, for example, my clients would map their acquisitions’ charts of accounts to a consolidation chart of accounts. Then, with all data in an Excel-friendly OLAP database, their Excel users could report and analyze the data at any level of consolidation, company-wide.
Similarly, one client exported data as CSV files from multiple ancient silo systems using various currencies. The OLAP system imported the files daily, translating and consolidating them in real time. This allowed Excel users to give executives their first daily views of worldwide revenue in US Dollars by product and region.
And all of that happened more than 20 years ago.
Enhanced Excel Power
“Excel just wasn’t designed to do some of the heavy lifting that companies need to do in finance,” said Paul Hammerman, a business applications analyst at Forrester Research Inc…
The newer software connects with existing accounting and enterprise resource management systems, including those made by Oracle Corp. or SAP SE . This lets accountants aggregate, analyze and report data on one unified platform, often without additional training.
The “newer software” appears to be far behind the times!
I was doing the same thing more than 20 years ago. For example, during a conversion to SAP, clients flowed data from both SAP and their previous ERP into an OLAP cube. By having two sets of what was supposed to be the same data, worksheet formulas could compare data from the two systems to ensure that SAP had been set up correctly.
And these days, at least with Olation, worksheet formulas can report and analyze data from virtually any source, in real time.
Also, Excel users can link worksheet formulas to data obtained from external sources, like benchmarks, economic data, the financial results of public companies, weather data, population trends, and so on.
External data like this can serve as a resource for strategic planning and as a way to identify and use leading indicators for forecasts. They can help to explain variances, and they can offer easy ways for companies to monitor the performance of their publicly traded competitors and customers.
Excel Is Getting Smarter
Excel offers nearly 500 worksheet functions, more than any other tool of its kind. And as TechCrunch reported recently, Microsoft Excel is about to get a lot smarter.
As Jared Spataro, Microsoft’s general manager for Office, explained, “you will soon be able to tag a list of company names as — well — company names, for example. Once you’ve done that, Excel can pull in more information about the company from Microsoft’s Bing API, including stock data and market cap, for example.
“Excel can even automatically detect that a list of names is indeed a list of company names or a list of cities, for example, which then allows you to pull in population data, among other things.
“Historically, Excel has always been good at numbers and you can enter in text and use conditional formatting and things like that,” Spataro said. “We are adding the idea that Excel can now recognize data types that are richer than those two.”
And, of course, worksheet formulas in PowerOLAP and TM1 will be able to save to cubes any data that smarter versions Excel can retrieve…in real time.
As Quartz wrote about the TechCrunch article, Microsoft is using Excel as a gateway drug to AI.
“The models could predict future sales numbers given different scenarios,” they wrote, “or stand in for any number of software-as-a-service analytics tools that have become popular in sales and marketing.
“This also means Excel will try to understand the connections between your data as you enter it (like whether the words represent companies or people) rather than just determining if they’re numbers or text.”
However, moving from Excel to BI or SaaS keeps companies from using this power. And it could turn critical systems into information silos, which could cripple performance analysis for decades.
On the other hand, moving to an Excel-friendly OLAP system will help your company enhance the power of all the analytical and presentation products that link to it, including Excel.
Excel is the world’s best tool for analysis. But for your company to use that tool effectively, you need to give Excel formulas the ability to link to limitless amounts of real-time data.
In short, if you’re frustrated with Excel’s shortcomings, don’t replace Excel…enhance it with a hub-and-spoke Excel-friendly database!
