Understanding How KPIs are Calculated when Tactic Costs are Updated Manually
To ensure that the total values of lump sum tactic costs (at different levels of planning hierarchy) are changed only through manual inputs, and not through the nightly batch updates, keep the total value of a KPI distribution fixed by selecting the Fixed Totals option when you configure the KPI.
Required Editions
| Available in: Lightning Experience in Enterprise, Professional, and Unlimited Editions that have Consumer Goods Cloud enabled |
Here’s how the system calculates when the total value of a KPI for which the total value of a KPI distribution is fixed is edited through the UI.
- Checks product hierarchy to identify all the non-obsolete values (values that don’t require any recalculation) available for the KPI. The system stops checking when it finds a manual input at the first level.
- Calculates the remaining effective metric weight by subtracting the weight of all the cells for which it found the manual input.
- Uses the remaining effective value, remaining metric weight, and the cell weight to distribute the KPI value to the remaining cells according to the distribution settings defined for the plan and time hierarchy.
- Calculates the value at the product level by using the remaining effective value to distribute the remaining metric weight and cell weight.
- Calculates the total value by applying the total calculation rule to the weekly or monthly values at the same level.
- Determines the weekly, monthly, and total values for a higher level by applying the aggregation rule to the weekly or monthly values at the product level.
- Records all the effective total values based on these conditions.
- If you don’t provide any manual input, the calculated total value is stored as the effective value.
- If you provide a manual input, it’s stored as the effective value.
- Repeats these steps for each manual input.
- Distribution Order: Planning Hierarchy -> Time
- Distribution Type (Planning Hierarchy): Metric
- Distribution Type (Time): Metric
- Edit Mode: Total
- Aggregation Rule: Sum
- Total Calculation Rule: Sum
- Fixed Totals: Yes
| Metric | Total | Week 1 | Week 2 |
|---|---|---|---|
| Total | 20 | 7 | 13 |
| Brand1 | 12 | 3 | 9 |
| Product1 | 2 | 1 | 1 |
| Product2 | 10 | 2 | 8 |
| Brand2 | 8 | 4 | 4 |
| Product3 | 8 | 4 | 4 |
Enter 1,000 in the KPI Total field. The system calculates the effective values to ensure that your input is reflected correctly.
Metric weight is calculated by dividing the manually input value in the KPI Total field by the metric total. The metric value in each cell is multiplied by the metric weight to arrive at the effective value, as shown in this table.
| Metric | Total | Week 1 | Week 2 |
|---|---|---|---|
| Total | 1000 | 350 | 650 |
| Brand1 | 600 | 150 | 450 |
| Product1 | 100 | 50 | 50 |
| Product2 | 500 | 100 | 400 |
| Brand2 | 400 | 200 | 200 |
| Product3 | 400 | 200 | 200 |
Now, enter 300 in the Product 1 Total field and save the promotion. As there’s an increase in value by 200, the KPI total now becomes 1,200. The total value of 300 is distributed to weeks based on the metric values (150 per week).
To ensure that the KPI Total and Product 1 Total values are unchanged, the system calculates the effective values of other cells. That is, the values except KPI total, Product 1 total, and Product 1 weekly values, are calculated based on this formula.
(Difference of user input values) × [Metric of the cell/(Difference of metrics for user
inputs)]In this example:
- Difference of user input values = (Total - Product1) = (1,200 - 300) = 900
- Difference of metrics with user input = (Difference of metrics of Total and Product1 fields) = (20 - 2) = 18
So, the effective weekly values for Product 3 are:
- Week 1 = 900 × (4/18) = 200
- Week 2 = 900 × (4/18) = 200
This table shows the effective values of all cells.
| KPI | Total | Week 1 | Week 2 |
|---|---|---|---|
| Total | 1200 | 450 | 750 |
| Brand1 | 800 | 250 | 550 |
| Product1 | 300 | 150 | 150 |
| Product2 | 500 | 100 | 400 |
| Brand2 | 400 | 200 | 200 |
| Product3 | 400 | 200 | 200 |
If products 2 and 3 are discontinued, the KPI total changes from 1,200 to 300.
| Total | Week 1 | Week 2 | |
|---|---|---|---|
| Total | 300 | 150 | 150 |
| Brand1 | 300 | 150 | 150 |
| Product1 | 300 | 150 | 150 |
As 1,200 is the manual input, this value must be fixed. Therefore, the system updates the metric, reapplies the effective values, and discards all other manual inputs to ensure that the KPI total is 1,200.
| Total | Week 1 | Week 2 | |
|---|---|---|---|
| Total | 4 | 2 | 2 |
| Brand1 | 4 | 2 | 2 |
| Product1 | 4 | 2 | 2 |
The Product1 weekly values are calculated based on this formula: KPI Total × (Updated metric of the cell/Updated metric of the KPI Total) = 1,200 × (2/4) = 600
This table shows the effective values of all the cells.
| Total | Week 1 | Week 2 | |
|---|---|---|---|
| Total | 1200 | 600 | 600 |
| Brand1 | 1200 | 600 | 600 |
| Product1 | 1200 | 600 | 600 |
