Example: Calculating KPIs of Subperiod Time Granularity

Example The image shows the available data.

Two different time frames—Promotion and Shipment—are spread across 3 weeks. The data includes two editable KPIs, Shipment Profile and Promotion Volume, that are used to calculate the values of the other KPIs. Shipment Profile is a percentage value that is split across the 3 weeks: 50% in Week 1, 30% in Week 2, and 20% in Week 3. Promotion Volume is the sum of values for all the weeks in the promotion time frame. The third week has a manual input of 12,000.The final promotion volume is 12,000. Both editable KPIs are used to calculate the value of the Sell in Increment KPI.

Based on the time frame, promotion volume, price, weekday share profile, and the percentage of the shipment profile, these parameters are calculated:

• Sell in Revenue
• Sell in Base
• Sell in Increment
• Sell in Volume

The Sell in Revenue KPI uses a subperiod time granularity and is calculated using period time granularity KPIs. The Sell In Revenue (Calc) KPI time frame is Shipment, and the time frame for the Sell in Base (Read), Sell in Increment (Calc), and Sell in Volume (Calc) KPIs is Promotion.

Based on the overlapping time frames and each week’s price value, this time frame includes six subperiods.

• Subperiod 1: Day 1 - Day 2
• Subperiod 2: Day 3 - Day 4
• Subperiod 3: Day 5 - Day 7
• Subperiod 4: Day 8 - Day 10
• Subperiod 5: Day 11 - Day 14
• Subperiod 6: Day 15 - Day 21

Let’s look at the calculation in combinations.

Set 1: Value Type = Money, Time Frame = Shipment, Parameter = Sell in Revenue (Calc)

For this set, here’s how the subperiod values are calculated.

• Because the Shipment time frame starts on Day 3, subperiod 1 is empty.
• The value of each subperiod is calculated as:

  (value of Sell in Volume KPI for the corresponding week) × (value of the weekday share of the subperiod) / (value of the weekday share of the corresponding week that the KPI (of time granularity period) time frame applies to) × (price for the corresponding subperiod)

  Based on this formula, here’s how the value of the Sell in Revenue KPI for each subperiod is calculated.

  Subperiod	Value
  2	5,431.10 × (10 + 20) / (10 + 20 + 20 + 10 + 10) × 1 (Money, Time Scope) = 5,431.10 × (30 / 70) × 1 (Shipment, Parameter) = 2,327.61 (Sell in Revenue)
  3	= 5,431.10 × (20 + 10 + 10) / (10 + 20 + 20 + 10 + 10) × 2 (Money, Time Scope) = 5,431.10 × (40 / 70) × 2 (Shipment, Parameter) = 6,206.97 (Sell in Revenue)
  4	= 3,987.14 × (10 + 20 + 10) / (10 + 20 + 10 + 20 + 20 + 10 + 10) × 2 (Money, Time Scope) = 3,987.14 × (40 / 100) × 2 (Shipment, Parameter) = 3,189.71 (Sell in Revenue)
  5	= 3,987.14 × (20 + 20 + 10 + 10) / (10 + 20 + 10 + 20 + 20 + 10 + 10) × 1 (Money, Time Scope) = 3,987.14 × (60 / 100) × 1 (Shipment, Parameter) = 2,392.28 (Sell in Revenue)
  6	= 2,581.76 × (10 + 20 + 10 + 20 + 20 + 10 + 10) / (10 + 20 + 10 + 20 + 20 + 10 + 10) × 1 (Money, Time Scope) = 2,581.76 × (100 / 100) × 1 (Shipment, Parameter) = 2,581.76 (Sell in Revenue)

  The value of the Sell in Revenue KPI for each week is the sum of the corresponding subperiods.

  • Value of Week 1 = Subperiods 1–3 = 0 + 2,327.61 + 6,206.97 = 8,534.59
  • Value of Week 2 = Subperiods 4–5 = 3,189.71 + 2,392 = 5,582
  • Value of Week 3 = Subperiod 6 = 2,581.76

  The total value of the corresponding period is the sum of the final values of all the weeks (8,534.59 + 5,582 + 2,581.76 = 16,698.35).

Set 2: Value Type = Volume, Time Scope = Shipment, Parameter = Sell in Base (Read)

For this set, here’s how the subperiod values are calculated.

• Because the Shipment time frame starts on Day 3, subperiod 1 is empty.
• Value of each sub period is calculated as:

  (Volume for the corresponding week) × (Value of the weekday share of the sub period) / (Value of the weekday share of the corresponding week for which the KPI (of time granularity period) timeframe is applicable)

Based on this formula, here’s how the value of each subperiod is calculated.

Subperiod	Value
2	= 1,256 × (10 + 20) / (10 + 20 + 10 + 20 + 20 + 10 + 10) = 1,256 × (30 / 100) = 377
3	= 1,256 × (20 + 10 + 10) / (10 + 20 + 10 + 20 + 20 + 10 + 10) = 1,256 × (40 / 100) = 502
4	= 1,256 × (10 + 20 + 10) / (10 + 20 + 10 + 20 + 20 + 10 + 10) = 1,256 × (40 / 100) = 502
5	= 1,256 × (20 + 20 + 10 + 10) / (10 + 20 + 10 + 20 + 20 + 10 + 10) = 1,256 × (40 / 100) = 754
6	= 756 × (10 + 20 + 10 + 20 + 20 + 10 + 10) / (10 + 20 + 10 + 20 + 20 + 10 + 10) = 756 × (100 / 100) = 756

The value of the Sell in Base KPI for each week is the sum of the corresponding subperiods.

• Value of Week 1 = Sub periods 1–3 = 0 + 377 + 502 = 879
• Value of Week 2 = Sub periods 4–5 = 502 + 754= 1,256
• Value of Week 3 = Sub period 6 = 761

The total value of the corresponding period is the sum of the final values of all the weeks (879 + 1,256 + 761 = 2,896).

Part 3: Value Type = Volume, Time Scope = Shipment, Parameter = Sell in Increment (Calc)

Here’s how the Sell in Increment KPI value for each week is calculated.

• Value for each week is calculated as:

  (Difference between the values of the Promotion Volume and Sell in Base for the corresponding week) × (Percentage of shipment profile for the corresponding week)

Based on this formula, here’s how the value of each week is calculated.

Week	Value
1	= (12,000 - 2,896) × (50 / 100) = 9,103.80 × 0.5 = 4551.90
2	= (12,000 - 2,896) × (30 / 100) = 9,103.80 × 0.3 = 2,731.14
3	= (12,000 - 2,896) × (20 / 100) = 9,103.80 × 0.2 = 1,820.76

Set 4: Value Type = Volume, Parameter = Sell in Volume (Calc)

The Sell in Volume KPI is the sum of Sell In Base and Sell in Increment for the corresponding week. The values are calculated as:

• Value of Week 1 = 879 + 4551.90 = 5,431.10
• Value of Week 2 = 1,256 + 2731.14 = 3,987.14
• Value of Week 3 = 761 + 1820.76 = 2,581.76