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.

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 Excel-friendly OLAP.

The reason 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 similarly.

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, MONTH(LEFT(B$6,8)),1),
          "mmm-yyyy")&" YTD"

The formula for the beginning balance is:

B9:    =OLAPReadWrite(Database, Cube, GLView, Unit, Department,
            LastYear, $A9)

The formula for the Cash balance is:

B11:  =OLAPReadWrite(Database, Cube, GLView,Unit, Department,
            ThisYear, $A11)

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, Department,
           LastYear, $A9)

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 representative formula:

D9:   =B9*C9

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:

D31:   =Cash
D34:   =AR
D37:   =Inventories

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:

H39:   =IF(ABS(D31+D34+D37+D40-CurAssets)>0.01,NA(),CurAssets)

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: #,##0.0,,

Here, the two ending commas tell Microsoft to round the results to nearest million. One ending comma would round the results to the nearest thousand.