Home > OLAP and Excel
Map Your Financial Health
With an Excel-Friendly OLAP
Here's an easy way to connect your Excel
spreadsheet to data
required by the DuPont Chart Report.
by Charley Kyd
Because it's so carefully formatted, the
DuPont Chart Report takes some effort to
set up. But the report is easy to maintain if you use an
for the ease of use is that an Excel user can prepare this report once,
and then use it for any month or division for which data exists. In
short, this report acts as a template.
The key design decision is to use a staging worksheet to support the
chart. The figure below shows the top of the sheet that I used.
Below, I use PowerOLAP to illustrate Excel's link to an Excel-friendly
OLAP. But other Excel-friendly OLAP products work
Column A contains labels throughout. The labels in cell A9 and below
specify members of the Account dimension that are used in the report.
Excel relies in part on the contents of cells B1 and B2 to know where
to find data for the report. Formulas in cells B3 through B6 specify the
members I used from the dimensions shown, as illustrated here:
B6: =OLAPMember(Database, "Month", 223)
To choose another month, I double-click on cell B6, choose the month
from a PowerOLAP list, press enter, then recalculate.
The report uses the Beginning Equity value for Net Worth. (For an
explanation, see the articles
Weighing Your Debt Load and
How Fast Is Too Fast?.) This formula calculates that beginning date:
B7: =TEXT(DATE( YEAR(LEFT(B$6,8))-1,
The formula for the beginning balance is:
B9: =OLAPReadWrite(Database, Cube, GLView, Unit,
The formula for the Cash balance is:
B11: =OLAPReadWrite(Database, Cube, GLView,Unit, Department,
Cell B11 is copied down the remainder of the column as needed.
Column C contains the sign of the data in column B, where -1
indicates a credit balance and +1 indicates a debit balance. This
property is associated with members of the Account dimension, and is
returned by a formula like this:
C9: =OLAPReadWrite(Database, Cube, GLView, Unit,
Column D contains the values used in the DuPont Report. Each cell in
this column is named using the adjacent cell in column E. Here's a
This formula adjusts the sign of the data coming from the OLAP's GL
cube. It causes the results to be positive whenever the data has the
expected sign, and negative otherwise.
The report's formulas reference the range
names used in the formulas above. To illustrate:
In each instance, the formulas refer to cells in column D of the data
sheet, as illustrated in the figure above.
The formulas for the totals in the report could use the same
approach. However they use simple logic to check for errors. To
illustrate, the formula for the Current Assets balance is:
Here, if the absolute value of the difference between the report's
value and the cube's value is greater than a penny, the formula returns
"#N/A" as an error value. Otherwise, the formula returns the appropriate
value. Other totals in the report use a similar approach.
This report displays its numeric results using this number format:
Here, the two ending commas tell Microsoft to round the results to
nearest million. One ending comma would round the results to the nearest