Weighted Average Accruals - technical reference

Summary

This page details all possible scenarios for weighted average accrual calculations, data exception handlings and their related technical details. For a non-technical overview of the Weighted Average accrual method, please read this page.

Algorithm Foundation

Based on the available historical data relating to the missing period, there are a few scenarios that need to be considered and each scenario will have a slightly different way of calculating accruals. However all scenarios will have the following as the basis:

PM1 = Daily Usage and Daily Cost of previous Month of the missing month

PM2 = Daily Usage and Daily Cost of same month last year of the missing month

PM3 = Daily Usage and Daily Cost of the month before the same month last year of the missing month

PM4 = Daily Usage and Daily Cost of the month after the missing month

Each month has a weight being assigned:

PM1 and PM4 = 3

PM2 and PM3 = 1

Scenario 1

When period of missing month has invoice after but no invoice before (typically for the beginning of the account open period)

Accrual is based on PM4 only, e.g. Accrual for Mar-17 = PM4 ∗ # of Missing Days in Mar-17, when the following condition is met:

• If PM4 is not null AND PM1 is NULL

Scenario 2

When period of missing data has invoice before but no invoice after (PM1 is available but PM4 is not available):

2.1 Accruals are based on PM1 only when any one of the following condition is met:

• If both PM2 and PM3 are missing, or

• If both PM2 and PM3 have data, and if any one of PM2 and PM3 is outside the +30%/-30% threshold of PM-1, or

• If PM2 is missing and PM3 has data but is outside the +30%/-30% threshold of PM-1, or

• If PM3 is missing and PM2 has data but is outside the +30%/-30% threshold of PM-1

Example: Accrual for Mar-17 = PM1 ∗ # of missing days in Mar-17

2.2 Accruals are based on weighted average of PM1, PM2, PM3 when the following condition is met:

• If both PM2 and PM3 have data, and both of them are within the +30%/-30% threshold of PM-1

Example: Accrual for Mar-17 = ((PM1∗W1+PM2∗W2+PM3∗W3)) / (W1+W2+W3)  ∗ # of missing days in Mar-17

2.3 Accruals are based on weighted average of PM1 and PM3 when the following condition is met:

• If PM2 is missing and PM3 has data and is within the +30%/-30% threshold of PM-1

Example: Accrual for Mar-17 = ((PM1∗W1+PM3∗W3)) / (W1+W3)  ∗ # of missing days in Mar-17

2.4 Accruals are based on weighted average of PM1 and PM2 when the following condition is met:

• If PM3 is missing and PM2 has data and is within the +30%/-30% threshold of PM-1

Example: Accrual for Mar-17 = ((PM1∗W1+PM2∗W2)) / (W1+W2)  ∗ # of missing days in Mar-17

Scenario 3

When period of missing data has invoice after and before (both PM1 and PM4 are available):

At first, calculate PM-C = (PM1 + PM4) / 2, which is the average of PM1 and PM4. The calculation should be done for both daily usage and daily cost.

3.1 Accruals are based on weighted average of PM1 and PM4 when any one of the following condition is met:

• If both PM2 and PM3 are missing, or

• If both PM2 and PM3 have data, and if any one of PM2 and PM3 is outside the +30%/-30% threshold of PM-C, or

• If PM2 is missing and PM3 has data but is outside the +30%/-30% threshold of PM-C, or

• If PM3 is missing and PM2 has data but is outside the +30%/-30% threshold of PM-C

Example: Accrual for Mar-17 = ((PM1∗W1+PM4∗W4))/(W1+W4)  ∗ # of missing days in Mar-17

3.2 Accruals are based on weighted average of PM1, PM2, PM3 and PM4 when the following condition is met:

• If both PM2 and PM3 have data, and if both of them are within the +30%/-30% threshold of PM-C

Example: Accrual for Mar-17 = ((PM1∗W1+PM2∗W2+PM3∗W3+PM4∗W4)) / (W1+W2+W3+W4)  ∗ # of missing days in Mar-17

3.3 Accruals are based on weighted average of PM1, PM4 and PM3 when the following condition is met:

• If PM2 is missing and PM3 has data and is within the +30%/-30% threshold of PM-C

Example: Accrual for Mar-17 = ((PM1∗W1+PM3∗W3+PM4∗W4)) / (W1+W3+W4)  ∗ # of missing days in Mar-17

3.4 Accruals are based on weighted average of PM1, PM4 and PM2 when the following condition is met:

• If PM3 is missing and PM2 has data and is within the +30%/-30% threshold of PM-C

Example: Accrual for Mar-17 = ((PM1∗W1+PM2∗W2+PM4∗W4)) / (W1+W2+W4)  ∗ # of missing days in Mar-17

Scenario 4

When the missing month do not have any data in the month before or after (typical for accounts that loads data quarterly, both PM1 and PM4 are missing):

4.1 Accruals are based on weighted average of PM2 and PM3 when the following condition is met:

1. If both PM2 and PM3 has data

                Example: Accrual for Mar-17 = ((PM2∗W2+PM3∗W3)) / (W2+W3)  ∗ # of missing days in Mar-17

4.2 Accruals are based on PM2 when the following condition is met:

• If PM2 has data but PM3 is missing

Example: Accrual for Mar-17 = PM2 ∗ # of missing days in Mar-17

4.3 Accruals are based on PM3 when the following condition is met:

• If PM3 has data but PM2 is missing

Example: Accrual for Mar-17 = PM3  ∗ # of missing days in Mar-17

4.4 Accruals are based on the last month of data of the Account when the following condition is met:

• If both PM2 and PM3 are missing

Example: Accrual for Mar-17 = Daily average of Jan-17 (last month of data of the Account) * # of missing days in Mar-17