You understand how to create a dashboard that shows the WBS costs within a single estimate (I.E. Uniformat Level 1). But how do you create a dashboard that takes into account all the estimates you have loaded into the Comparison View?
In a recent post, I showed you how to use SUMIFS to create a WBS breakdown by a particular WBS category.
At the end of that post, I showed how to add additional criteria to break the costs down even further by the System Name. We will essentially be copying and modifying that logic string to point to a new logic source.
The first step to creating a comparison dashboard is to add a comparison estimate into the Comparison View. Once you have done that, navigate to the Dashboard view, and Save As... the blank dashboard. The reason we save the dashboard out right away is this gives us all the data that exists in the estimate, but we can open it in Excel to take advantage of the full tool-set available in that program.
When you open the dashboard, you'll notice a few new $$ ______ $$ sheets. Specifically, the addition of a comparison estimate led to the creation of the following tabs:
$$Meta Data Comparison$$
$$Direct Cost Comparison$$
$$Indirect Cost Comparison$$
$$WBS Comparison$$
$$Group Level Comparison$$
$$Detail Level Comparison$$
For this example, we want to see the CSI Level 1 Costs across two estimates and compare the deltas.
As a reminder, the SUMIFS formulas reads...
Give me the sum of THESE CELLS if ____ = XYZ, AND ______ = ABC, AND..... etc
Because our example is focusing on a WBS comparison, the $$WBS Comparison$$ sheet will be our source for data.
This is what that data looks like. It lists the Estimate Name, WBS Property *, Values within the Property, and the total cost.
The active WBS Property is determined by the dropdown in the WBS Property Comparison dropdown in the comparison View.
Comparison > WBS Property Cost > Dropdown (Select the active WBS for comparison purposes)
Make CSI Level 1 the active grouping.
Now we simply need to fill in the formulas in the following dashboard...
In Cell B1 and C1, we need to add the name of the estimate as shown in the $$WBS Comparison$$ column A. In this case, the B1 value is "CD Estimate" and C2 is "DD Estimate".
Remember your SUMIFS? Here are the chunks we need...
=SUMIFS(
"Return the sum of these cells...."
'$$WBS Comparison$$'! $D:$D,
"IF this...."
'$$WBS Comparison$$'! $C:$C,
"Equals this..."
$A2,
"AND IF this..."
'$$WBS Comparison$$'! $A:$A,
"Equals this ..."
$B$1
=SUMIFS('$$WBS Comparison$$'! $D:$D,'$$WBS Comparison$$'! $C:$C,$A2,'$$WBS Comparison$$'! $A:$A,$B$1)
Drag this formula down the first column, and the values should all populate. Copy it to the next column, making sure to reference $C$1 at the end of the formula, and drag it down again. I leave the Delta and % change columns as a "Take Home Exam," see if you can figure them out!