Telus TPM Documentation
This is an old revision of the document!
Old Accrual Process (if not transitioned to the new process, this will be your guide. New accrual process guide is below this section)
Accruals are a record of the expenses that have been incurred as a result of a contract in TPM. The methods for calculating these values can differ by program type. The client can choose to pre-define the calculation method in TPM at the Template level or allow the contract creator to select a method from more than one option.
The accrual process allows the client to use TPM to keep track of what contracts need to be accrued, how to accrue, when to accrue, and finally how much to accrue. The accrual process runs daily to account for daily changes such as new contracts, amended contracts and renewed contracts. It also accounts for new sales data coming in, new claims being processed etc.
The client has to ability to view the daily accruals for informational purposes (or on their desired frequency Weekly/Monthly). There are ITB reports within Tableau or the client may choose to import the data into their system via an outbound report (1069_accrual_detail) from TPM that is available in a full file format or an incremental file format. Either option comes with an accompanying supplemental Sale and Claim detail file to provide the details that were used to build the accrual values if they were calculated using either Sales or Claim data. This allows the client to be able to do their own research into the accrual detail values. The current Contract Types that work with the Accrual process are the following three:
There are three parts to a successful accrual process:
One Time Configuration Set Up (5 Settings)
1. A client setting to enable Forecasting out accruals. For example, if it’s June, we can use sales data to determine your Jan-Jun accruals. If you have this enabled, our process estimates Jul-Dec accruals for you.
2. Client setting allowing client to choose what order to attempt to build accruals in. For example, here it would use Company Price List first, then sales data, then Default Price. This can be found under the Client Settings>Claim Tab.
These 5 items are default settings that a client can change. They are not viewable in the Client Settings UI. Otherwise, nothing to do here to set them up.
Sales data accrual future projections and the claim trailing average use these:
contract_forecast_sales_lookback_period_interval (how big of a slice time to look at)
contract_forecast_sales_lookback_gap (how far back to start looking)
3. Calendar and Fiscal Information (Future UI Enhancement)
To be added to UI under the Accrual Tab (Future UI Enhancement)
Accruals always accrue at the contract/company/product level for a defined timeframe. See below for how the dollars are allocated to companies/products under the contract. Defined Timeframe can be one of these six options:
4. Template level setting telling system what Accrual options should be available when user enters contracts.
NONE – Do not pull in any quantity to accrue, so nothing will be accrued for this contract.
CUSTOM – Indicates that there is a custom accrual method set up for the manufacturer and it's not controlled by any of the accrual types listed.
CONTRACT ESTIMATE – Uses the estimates defined on the contract as the quantity to accrue for. Allocates the estimate evenly over the entire time frame of the contract to determine accrued dollars per period. Allocate each product evenly over the contract period divided evenly by each contractee company (if a group or aggregate).
CLAIM TRAILING AVERAGE – Past periods use actual claim volume/dollars while future periods use an average from a defined trailing period with an optional gap period…e.g. Gap 2 months back and take the previous 6 months claim actuals average. Dollars are allocated to the distributor of each paid claim line item, or if there is no captured distributor, then they will be allocated to the claimant company. There are two database client settings that control this (see client settings section).
Example: For Claim Trailing Average, ACTUAL claim data is used. If the lookback gap is 1 month, and the lookback period is 3 months and we are currently in the month of May, the April claim data is skipped in the calculation and January, February and March volume is averaged.
If the claim data is January 10 cases, February 11 cases, March 11 cases, and April 5 cases, then based on the settings in the example, April is skipped and the average of January, February and March is used which is 11 cases. So the Accrual process would predict an accrual of 11 cases for May using Claim Trailing Average.
CLAIM ACTUALS ONLY – This option will not accrue for future periods, it just accrues based on the claim actuals for the past periods. Dollars are allocated to the distributor of each paid claim line item, or if there is no captured distributor, then they will be allocated to the claimant company.
DISTRIBUTOR SALES – Uses the sales data for a period as the quantity to accrue in the past, then similar to claim trailing average, uses a gapped trailing average to project future periods. Dollars are allocated to the company on the sale unless the contractee is a group with a parent company, in which case they are allocated to the parent company.
Additional Information:
Sale companies can be eligible because they:
a. Belong to the group
b. Are “Agg” companies of companies belonging to the group
c. Are on a row of sales which the client has linked to a (buying) group contractee through the company grouping code field on the sales feed.
For past periods
The quantities on the eligible sales by product and company are used. For future periods, if the client “projects” accruals forward, we take the average quantity for their accrual lookback timeframe (determined by client settings) and project that forward for all future accrual periods.
5. Template level setting telling system how to accrue your lumpsums
Lumpsums are always allocated to the contractee company (evenly split when there are multiple contractees or to the contractee group’s parent company if one is defined).
EVENT START DATE: all dollars for the lumpsum accrue on the first day of the event date range
EVENT END DATE: all dollars for the lumpsum accrue on the last day of the event date range
EVENT RANGE SPREAD: all dollars are spread evenly across each day of the event date range (and then aggregated to the client’s accrual timeframe through reporting)
NONE: we do not generate accruals for this contract – same as picking none for the accrual quantity source for a product-based contract
Using this example we see the contract is for an entire year but the lump sum period is for 3 months. If you choose:
EVENT START DATE: It will accrue $1,200 to 4/1/2019
EVENT END DATE: It will accrue $1,200 to 6/30/2019
EVENT RANGE SPREAD: It will accrue $400 each month for April, May, and June
Per Contract Set Up
Now that you have the 1x settings set up, depending on how you set the template up, when it comes time to build a contract, you’ll see options under the Accrual Quantity Source dropdown. Pick one. That’s pretty much all that’s needed at the contract level.
Per Period (Retain) Set Up
Once you have contracts set up with an Accrual option other than NONE, then the daily Accrual process will run and compile what accruals you have, if any. Each day this process runs and updates.
The client has to ability to view the daily accruals for informational purposes. When the client needs to actually lock down the accruals for their specific periods (monthly etc.), they have the ability to “Retain” that daily accrual amount and lock in the data that produced those amounts. This allows the client to have an audit trail. Plus, next period (monthly etc.) it will not use that data to accrue again.
Within the User Interface, go to Admin>Client Setup>Accrual Admin. It will show the daily “Run ID”. Here you can see the Retained column says False. That is what’s currently accruing but it hasn’t been locked down or “Retained”. This will update every day with a new Accrual Run ID. If it’s time to run your accruals for financials, you can click the Retain button and that now locks in all the data used to build the accruals. You’d move over to Tableau to get your data.
Accrual Tableau Reporting also shows you the Run ID’s but also gives you more info such as the Accrued dollar amounts by RunID, by Month, by Contract.
Outside of the Standard Accrual Reports within Tableau, the client has the ability to build out one that has the needed information/fields they need to complete their financials. The client can run the reports within Tableau and then download the needed data.
Example of a clients output need for accruals…
Calculation Comments:
Clients need to have a Default Item Price in order to determine accruals for two scenarios:
Scenario 1: Rebate Deviated Type = Fixed Price
Scenario 2: Rebate Deviated Value Type = % but no Default Item Price
Terminating Contracts
If a client wants to end a contract for whatever reason such as they feel the spend has met a limit, the accrual process will continue to calculate accruals on a terminated contract through their original end date. The client could also change the end date and move it up on a contract prior to terminating it so that accruals stop as of the termination date.
Our standard reporting source has those terminated contract’s accruals available, but a given client could filter it out of their totals if they desired.
NEW ACCRUAL PROCESS
Accruals are a record of the expenses that have been incurred as a result of a contract in TPM. The methods for calculating these values can differ by program type. The client can choose to pre-define the calculation method in TPM at the Template level or allow the contract creator to select a method from more than one option.
The accrual process allows the client to use TPM to keep track of what contracts need to be accrued, how to accrue, when to accrue, and finally how much to accrue. The accrual process runs daily to account for daily changes such as new contracts, amended contracts and renewed contracts. It also accounts for new sales data coming in, new claims being processed etc.
The client has to ability to view the daily accruals for informational purposes (or on their desired frequency Weekly/Monthly). There are ITB reports within Tableau or the client may choose to import the data into their system via an outbound report (1069_accrual_detail) from TPM that is available in a full file format or an incremental file format. Either option comes with an accompanying supplemental Sale and Claim detail file to provide the details that were used to build the accrual values if they were calculated using either Sales or Claim data. This allows the client to be able to do their own research into the accrual detail values.
Three buckets are used in the accrual reporting:
Estimates – contract setting data
Accruals – contract, claim and/or sales data
Actuals – claim data
What is required to get accruals setup
A mini project is required to get a client set up on the new process and convert their current contracts and templates to the new accrual sources.
There are two parts to a successful accrual process:
Template set up
Reporting setting set up
* ability to perform these actions is limited to users with access to these setting only
TEMPLATE SETUP
Template level settings tell the system what Accrual options should be available when a user creates a contract on a specific template. If only one is chosen per category, the contracts created on that template will default to that choice. If more than one option is selected, the user will have to choose the method during contract creation. If you are not planning on using the estimated (forecasted) values, you can set the Estimate Sources to NONE.
There are 3 different types of settings – Estimates (Forecast), Product Accruals and Lumpsum.
ESTIMATE SOURCES
CONTRACT ESTIMATE – Uses the estimates defined on the contract as the quantity to estimate for. Allocates the estimate evenly over the entire time frame of the contract to determine estimated dollars per period. Estimates are associated to the contractee company (if there is only one) or to the contract in general if the contractee is a group.
CLAIM TRAILING AVERAGE – Past periods use actual claim volume/dollars while future periods use an average from a defined trailing period with an optional gap period…e.g. Gap 2 months back and take the previous 6 months claim actuals average.
If no claim history this selection will accrue nothing until claims are received.
SALE TRAILING AVERAGE – Past periods use actual sales volume/dollars while future periods use an average from a defined trailing period with an optional gap period…e.g. Gap 2 months back and take the previous 6 months sales actuals average.
If no sales history is received this selection will accrue nothing until claims are received.
BUDGET – Client would provide a Budget file (1046_forecast_budget) that would include volume per sku for specific time frames and customers for us to use to calculate values.
NONE** – Do not pull in any quantity to estimate, so nothing will be estimated for this contract.
