We are thinking of an addition to a key model in the business process.
Unfortunately, we do not have access to BPI numbers for either of these Cases.
Am I correct in thinking Cse B, below, is the better option. If not why not?
Both cases A and B are described below.
All Line Items (‘Refs) are constant across both Cases apart from –
Case A
Collects in Ref 2 and then Cumulates in Ref 3
Ref’s 2&3 total 93.6M cells and has a total Calculation Effort of 17.55%
Case B.1 & B.2
Cumulates in B.2 and then Colllects in B1
Cumulates in B.2 and Collects in B.1 (there is the addition of a LIS specific to B.2)
Ref 3 in Case B1 totals 38.5M cells and has a Calculation Effort of 0.16%, and
Case B2 total of 45.7M cells, with a Calaculation effort of 14.89%
So, like for like Case B totals 84.2M cells with a Calculation Effort of 15.05%
In summary then –
Case A Cell Count 93.6M cells, Calculation Effort 17.55%
Case BCell Count 84.2M cells, Calculation Effort 15.05%
Case B appears to be both smaller and faster.
But is it the better solution…?
related questions -
Am I right in thinking reducing the Calculation Effort reduces the burden across the whole model?
The Code used is below.
Case A
Ref | Formula | Cell Count | Calculation Effort |
|---|
| | 238,137,732 | |
| | 14 | 0% |
1 | 'DAT02 COINS Actuals'.COINS Actuals * 'SYS04 - Time Controls'.Actuals Multiplier * 'PL2 Cost Group'.COS Multiplier | 2,247,336 | 0.09% |
2 | COLLECT() | 46,781,280 | 0.16% |
3 | CUMULATE('Revenue Collector REP02', 'SYS04 - Time Controls'.'CAL06 Accumulator Reset Month') | 46,781,280 | 17.39% |
4 | CUMULATE('REP99.SITE REVENUE, 'SYS04 - Time Controls'.'CAL06 Accumulator Reset Month') | 3,118,752 | 1.17% |
5 | CUMULATE('CAL098.Total Cost of Sales, 'SYS04 - Time Controls'.'CAL06 Accumulator Reset Month') | 3,118,752 | 1.01% |
6 | Revenue Accumulator / Total Site Revenue | 56,514,528 | 0.52% |
7 | IF Revenue Allocation %[SELECT: 'LIS REP02 Revenue'.SITE REVENUE] <> 0 THEN COS Accumulator * Revenue Allocation %[LOOKUP: 'Map COS > REV'] ELSE COS Accumulator * 'Revenue Allocation 2' | 43,662,528 | 0.76% |
8 | IF 'SYS04 - Time Controls'.'CAL06 Accumulator Reset Month' THEN Cumulative COS Output ELSE Cumulative COS Output - PREVIOUS(Cumulative COS Output) | 35,903,868 | 0.16% |
9 | IF ITEM('LIS REP02 Cost of Sales') <> 'LIS REP02 Cost of Sales'.'COS - Other' THEN 0 ELSE 1 | 9,394 | 0.00% |
| | | |
| | 238,137,732 | 21.26% |
Case B.1
Ref. | Formula | Cell Count | Calculation Effort |
|---|
| | 165,810,078 | |
1 | (this line is a mapping) | 14 | 0.00% |
2 | 'DAT02 COINS Actuals'.COINS Actuals * 'SYS04 - Time Controls'.Actuals Multiplier * 'PL2 Cost Group'.COS Multiplier | 3,923,262 | 0.11% |
3 | COLLECT() | 38,468,430 | 0.16% |
4 | CUMULATE('REP02 Site P&L'.SITE REVENUE, 'SYS04 - Time Controls'.'CAL06 Accumulator Reset Month') | 2,564,562 | 1.08% |
5 | 'Revenue ****.Collect (by Segment)' / 'Total Site Revenue (All segments)' | 46,472,118 | 0.24% |
6 | | 0 | 0.00% |
7 | CUMULATE('CAL09.2 - Site Margin Calc by Month'.Total Cost of Sales, 'SYS04 - Time Controls'.'CAL06 Accumulator Reset Month') | 2,564,562 | 0.91% |
8 | IF Revenue Allocation %[SELECT: 'LIS REP02 REV (CAL06)'.SITE REVENUE] <> 0 THEN COS Accumulator * Revenue Allocation %[LOOKUP: 'MAP COS > REV'] ELSE COS Accumulator * 'Revenue Allocation 2' | 35,903,868 | 0.57% |
9 | IF 'SYS04 - Time Controls'.'CAL06 Accumulator Reset Month' THEN Cumulative COS Output ELSE Cumulative COS Output - PREVIOUS(Cumulative COS Output) | 35,903,868 | 0.15% |
10 | IF ITEM('LIS REP02 Cost of Sales') <> 'LIS REP02 Cost of Sales'.'COS - Other' THEN 0 ELSE 1 | 9,394 | 0.00% |
| | | |
| | 165,810,078 | 3.22% |
Case B.2
Ref | Formula | Cell Count | Calculation Effort |
|---|
| | 45,695,706 | |
1 | CUMULATE('REP99'.'A', 'SYS04 - Time Controls'.'CAL06 Accumulator Reset Month') | 2,564,562 | 1.03% |
2 | CUMULATE('REP99'.'B', 'SYS04 - Time Controls'.'CAL06 Accumulator Reset Month') | 2,564,562 | 0.94% |
3 | CUMULATE('REP99'.'C', 'SYS04 - Time Controls'.'CAL06 Accumulator Reset Month') | 2,564,562 | 1.09% |
4 | CUMULATE('REP99'.'D', 'SYS04 - Time Controls'.'CAL06 Accumulator Reset Month') | 2,564,562 | 1.03% |
5 | CUMULATE('REP99'.'E', 'SYS04 - Time Controls'.'CAL06 Accumulator Reset Month') | 2,564,562 | 1.05% |
6 | CUMULATE('REP99'.'F', 'SYS04 - Time Controls'.'CAL06 Accumulator Reset Month') | 2,564,562 | 0.99% |
7 | CUMULATE('REP99'.'G', 'SYS04 - Time Controls'.'CAL06 Accumulator Reset Month') | 2,564,562 | 1.09% |
8 | CUMULATE('REP99'.'H', 'SYS04 - Time Controls'.'CAL06 Accumulator Reset Month') | 2,564,562 | 0.95% |
9 | CUMULATE('REP99'.'I', 'SYS04 - Time Controls'.'CAL06 Accumulator Reset Month') | 2,564,562 | 0.87% |
10 | CUMULATE('REP99'.'J', 'SYS04 - Time Controls'.'CAL06 Accumulator Reset Month') | 2,564,562 | 1.08% |
11 | CUMULATE('REP99'.'K', 'SYS04 - Time Controls'.'CAL06 Accumulator Reset Month') | 2,564,562 | 1.01% |
12 | CUMULATE('REP99'.'L', 'SYS04 - Time Controls'.'CAL06 Accumulator Reset Month') | 2,564,562 | 0.95% |
13 | CUMULATE('REP99'.'M', 'SYS04 - Time Controls'.'CAL06 Accumulator Reset Month') | 2,564,562 | 0.96% |
14 | CUMULATE('REP99'.'N', 'SYS04 - Time Controls'.'CAL06 Accumulator Reset Month') | 2,564,562 | 0.89% |
15 | A+B+C+D+E+F+G+H+I+J+K+L+M+N | 3,613,638 | 0.07% |
16 | CUMULATE('REP99.O, 'SYS04 - Time Controls'.'CAL06 Accumulator Reset Month') | 2,564,562 | 0.86% |
17 | Ref 16 + O | 3,613,638 | 0.03% |
| | | |
| | 45,695,706 | 14.89% |
