This is the theme of the large new
section of ExcelUser, which I've been working on for the past two months.
What a lot of work! But I think you'll be interested in the results.
I'll be the first to admit that ordinary Excel is no kind of BI. But
when you live-link Excel formulas to multidimensional ("OLAP") data, you
turn Excel into a powerful Business Intelligence system.
figure illustrates the idea. Enterprise Excel is my name for this
Strictly speaking, Enterprise Excel offers more potential than a mere BI does. But
"BI" is a convenient way to summarize its capabilities.
What the figure doesn't illustrate is how inexpensive this type of system can
be. The single-user version probably is the only BI that small organizations will be able to afford.
And it's a tool that large organizations can buy out of petty cash to see if it
would be useful in a more extensive application.
To give you a better idea of how Excel works with an Excel-friendly OLAP,
I've created several videos. You can find them at: See Enterprise Excel in Action.
To discuss these ideas more effectively, I've carefully defined Business Intelligence and
invented several other terms:
Intelligence (BI) is the collection of tools and techniques used to
quickly gather, consolidate, analyze, display, and refine data that managers
need to improve business performance.
This is a broader definition than you'll likely find elsewhere. But when I
write that Excel makes a great BI, I wanted to make sure that my definition was
broad enough to be relevant.
OLAP is a term that I invented several years ago. Basically, it's
any OLAP (multidimensional) database product that provides a good number of
spreadsheet formulas that read and write OLAP data.
In most companies, most jackleg data should be maintained in an OLAP
database, not in spreadsheets. This simplifies its use and maintenance, offers
one version of the truth to all Excel users, reduces errors, and makes more useful
data available to more users.
Even if you have no interest in doing Business Intelligence with Excel,
you'll probably be interested in Create an Accordion Report In Excel, Using OLAP Data.
Budget reports are a common example. A marketing department might have 20
accounts in its report; a manufacturing department might have 40; a finance
department might have 30. As you generate each report, its length expands and
contracts like an accordion.
Generally, these reports take a lot of time to create in Excel because of all
the spreadsheet work involved.
No more. The article explains how to create automatic accordion reports using only formulas in
Excel. No VBA is required.
Strategic Planning, External Data, and Excel
For many companies, strategic planning is an occasional exercise that produces
an impressive bound document, which gathers dust on the shelf. If your company
has this problem, you might consider monthly strategic reporting.
Take your SWOT (strengths, weaknesses, opportunities, threats) analysis, for
example. Much of the analysis is based on cold, hard numbers...external data
from sources that are updated periodically. In most companies, this jackleg data
probably is stored in a forgotten spreadsheet saved in some dusty corner of an
analyst's hard drive.
Wouldn't it make more sense to get the updates as they're available and to
save that data in an Enterprise Excel system? Doing so could give many Excel
users easy access to the data. This would give them the opportunity to compare
internal performance with current external measures. And that continual
analytical reporting would breathe life back into your moribund strategic plan.
Just a thought...
Enough for now.