Rebates Configuration - Lab 1

Lab Info

Learning Outcomes

At the end of the Lab, you should be able to:

• set up the system to calculate Rebate Agreements results, to support the negotiation phase of the RM.

Figure 1. Sneakpeek of the final Rebate Agreement

Pre-requisites

In order to be able to complete this laboratory exercise, please complete the prerequisites before you proceed.

Provided resources

Review the provided resources, to get familiar with their content

Provided partition with pre-configured

• Product master data table

• Customer master data table

• Datamart with name "Transaction"

Requirement Broad Overview

This is a broad requirement, which gives you solid outline of the story behind all the Rebates configuration/implementation labs.

The customer needs to be able to manage their rebates in Pricefx to be able to reward their customers for performance. The process has several phases:

Phase 1: Prepare Rebate Agreement

1. Sales Manager needs to negotiate the Rebate Agreement – to capture the negotiated parameters/conditions.

2. The system generates the Rebate Records to be used for regular recalculations during the year.

Phase 2: Regular actions during the year

1. Payout (Rebate Records) calculations – to capture the current state of the rebates and to get better estimations.

2. Prepare the Rebate Record for Export and for Allocation

3. Rebate Allocation (of the Rebate Records created from approved Rebate Agreements) – every time new data is loaded into transactions.

4. [OOS] Sales Managers review the report on customer rebates.

   1. During the year they want to be able to see the updates of the estimated rebates per customer (targets, payouts) to be able to discuss with the customer when they’re not compliant.

5. [OOS] Rebate Records (aka "Payouts") approvals

6. [OOS] Export of approved Rebate Records (or Payout Records).

Phase 3: After a year

1. [OOS] Payouts approval of the yearly payouts.

2. Final allocation of the approved rebates.

3. [OOS] Export of approved payouts (Rebate Records).

4. [OOS] Review of last year rebates impact.

   1. For analytics purposes, they need to see the actual rebate paid to the customer reflected/allocated in the transactions (to track the effectiveness, to see the real margin).

Important Assumptions for this Exercise

• You are working on a given testing transactions dataset which has data for several past years, but also future years. (Remember, in a regular project you would have transactional data only until the present day.) Of course for testing, you need to somehow simulate the state of having certain partial dataset (i.e. until "today") in the Datamart. To handle this, use the Calculation Date as indication of "today" – i.e. in your calculation logic you will pretend that there’s nothing in the Datamart after that date. You do not want to physically remove this data from Datamart because you will need to test calculations simulating different calculation dates.

• Whenever the instructions mention "today", use the Calculation/Target date for the actual calculations.

User Story / Requirements for Phase 1: Prepare Rebate Agreement

1. User Story: As a Pricing Manager I want to define fixed rebate with rebate value defined as a percentage (optional: as a monetary value) so I can create a fixed rebate with correct parameter settings.

   • Acceptance Criteria:

     • Fixed rebate can be defined by these parameters: Fixed Percent (optional: Fixed Amount)

2. User Story: As a Pricing Manager I want to select Customers (on header or line item level) and Products (on line item level) so I can choose the correct customers and products for the rebate agreement.

   • Acceptance Criteria:

     • On Rebate Agreement Header Level it allows user to:

       • select the customer group

     • On Line Item (of "fixed" Condition Type) it allows user to

       • select the customer group. The value is inherited from the header, but it’s still possible to change it by user.

       • select the product group

       • enter Fixed rebate percent value

3. [OOS] User Story: As a Pricing Manager I want to Define the payout frequency of rebates with the following options - Monthly, Quarterly, Semi-Annually, Annually so I can discuss different payout frequency options of the rebate agreement with the customer.

   • Acceptance Criteria:

     • Maps rebate logic with an existing Condition type templates. Select: "Monthly", "Quarterly", "Semi-Annually" or "Annually".

     • If left empty, there is the "Payment Period" input in the rebate agreement with the same values.

User Stories Details

Fixed Percentage Rebate means, that the customer will get a certain negotiated percentage of every dollar they pay.

Acceptance Criteria Details

The Pricing Manager who negotiates the rebate conditions is be able to:

• Create a new Rebate Agreement contract

• Add a line of "fixed" Condition Type

• Enter:

  • On agreement level

    • Time period - during which this condition is valid (applies to the whole agreement).

    • Calculation Date – in this exercise we will use it to simulate "today". Specifically, for the agreement negotiation we will treat it as today.

    • Group of Customers - to which the rebate applies.

  • on Line item level

    • Group of Products - to which the negotiated rebate applies.

    • [OOS] Payouts Frequency – Choose among Yearly / Quarterly / Monthly depending on when the customer expects the payments.

    • Rebate % – the size of the negotiated rebate in %.

• Get calculated results on the Line Item for:

  • Sales Forecast - As we are negotiating the agreement in the middle of the year in which the agreement is valid, make the linear forecast (for the full validity period) based on the sales which the customer group has made from the agreement start until today.

  • Rebate Forecast - i.e. how much money we are forecasting to give to the customer, based on the Sales Forecast and the negotiated Rebate %.

  • [OOS] Historical sales summary (from the previous period, i.e. before the rebate).

    • For a given group of customers and products during the last period, so that they can drive the decision about the size of the rebate according to the historical data.

  • [OOS] Chart with historical sales during the previous time period (before the agreement validity) for the selected customer and product group (a bar chart with monthly sub-totals of invoice price).

Test Case

Set up and approve a new Rebate Agreement with the Fixed Rebate line and the following inputs:

• Agreement

  • Description = Rebate on Total Sales

  • Start Date = 2020-01-01

  • End Date = 2020-12-31

  • Payout Date = 2021-01-31

    • [OOS] If the Payouts are done quarterly or monthly, they should have the payout date 30 days after their period validity.

  • Calculation Date = 2020-04-01

  • Customer(s) = Customer Group "APPO AG"

• Line of "Fixed" Condition Type

  • Product(s) = Product Group "Beef"

  • Rebate Percentage % = 2

  • [OOS] Payout Frequency = Quarterly

Implementation Steps for Phase 1: Prepare Rebate Agreement

Setup the Condition Type

Each kind of Rebate has it’s own Condition Type. You could potentially create the Condition Type later, but it must be created before you test the rebate logic from within Pricefx Studio.

1. Navigate to Rebates  Condition Types

2. Add new Condition Type "Bonus On Sales"

   

   Figure 2. Creation of new Condition Type (without logic)

   1. set the Name to "BonusOnSales"

   2. set the Label to "Bonus On Sales"

Implement the calculation logic for the Condition Type

1. In Pricefx Studio, create new logic of type Pricing logic with Formula nature “Rebate (rebateAgreement)”

   1. Name it “BonusOnSales”

   2. Set Validity reasonably in the past (to be able to test for past dates) – e.g. 2000-01-01

2. Read inputs ProductGroup, CustomerGroup and Rebate% from the user. Create elements:

   CustomerGroup

   return api.customerGroupEntry()  

   During InputGeneration it builds the input field. During regular execution it returns the value selected by user, but encapsulated in an object of CustomerGroup type, which is later expected in where clause of query to Datamart. If the value was entered on the Header level, it will be inherited in this field, and the user can override the selection.

   ProductGroup

   return api.productGroupEntry()   

   During InputGeneration it builds the input field. During regular execution it returns the value selected by user, but encapsulated in an object of ProductGroup type, which is later expected in where clause of query to Datamart.

   RebatePct

   final String INPUT_REBATE_PCT = "RebatePct"
   
   if (api.isInputGenerationExecution()) {
       api.userEntry(INPUT_REBATE_PCT)
       api.getParameter(INPUT_REBATE_PCT)
               .setConfigParameter("formatType", "PERCENT")
               .setRequired(true)
               .setLabel("Rebate %")
   } else {
       return input[INPUT_REBATE_PCT]
   }

   AbortOnSyntaxCheck

   if (api.isInputGenerationExecution()) {
       api.abortCalculation()
   }

   1. set the Display Mode of those elements to "Never"

   2. do not set any Calculation Context of those elements

3. Setup calculation of the Sales Forecast:

   As we are negotiating the agreement in the middle of the year in which the agreement is valid, make the linear forecast (for the full validity period) based on the sales which the customer group has made from the startDate until today – i.e. based on the Actual Sales_

   

   “Past” Transactions - before the Agreement

   

   Transactions which falls into the range of the Agreement

   

   Transactions, which we have in the system (because we’re testing “in the past”), but we cannot consider them for calculations, because under normal conditions, they would NOT be in the system yet.

   remember, you normally do NOT have in the Datamart any transactions after Today! The only reason, why we have them is, that we’re testing on the historical dataset and we’re pretending that Today is in the past

   1. Prepare the date ranges - To calculate the ActualSales, we need to make a query to Datamart, and for that we need to know the date range

      Today

      return api.targetDate()

      StartDate

      def startDateAsString = api.currentItem()?.startDate
      if (startDateAsString) {
          return api.parseDate("yyyy-MM-dd", startDateAsString)
      } else {
          return out.Today /* fallback */
      }

      EndDate

      def endDateAsString = api.currentItem()?.endDate
      if (endDateAsString) {
          return api.parseDate("yyyy-MM-dd", endDateAsString)
      } else {
          return out.Today  /* fallback */
      }

      EndDateOrToday

      return out.Today < out.EndDate ? out.Today : out.EndDate

      1. with exception of element "Today", set Display Mode of those elements to Never

      2. do not set any Calculation Context of those elements

   2. Calculate the Actual Sales – create element “ActualSales”:

      Example 1. Code of Element "ActualSales"

      def dmCtx = api.getDatamartContext()
      def dmTable = dmCtx.getDatamart("Transaction")
      
      def query = dmCtx.newQuery(dmTable, true)
      
              .select("SUM(InvoicePrice)", "TotalRevenue")
      
              .where(out.CustomerGroup)
              .where(out.ProductGroup)
      
              .where(
                      Filter.greaterOrEqual("InvoiceDate", out.StartDate),
                      Filter.lessOrEqual("InvoiceDate", out.EndDateOrToday)
              )
      
      def result = dmCtx.executeQuery(query)
      return (result?.getData()?.getValue(0, 0) ?: 0.0)

      1. Ensure the result will be visible for the user.

      2. do not set any Calculation Context of this element

      3. format the result money with € currency

   3. Calculate the Sales Forecast, based on the knowledge of the Actual Sales.

      Example 2. Code of Element "ForecastSales"

      if (out.Today > out.EndDate) {
          return out.ActualSales
      }
      
      if (out.Today < out.StartDate) {
          return null
      }
      
      Integer daysOfLineValidity = out.EndDate - out.StartDate + 1
      Integer daysOfTransactions = out.Today - out.StartDate + 1
      BigDecimal actualSalesPerDay = out.ActualSales / daysOfTransactions
      
      return actualSalesPerDay * daysOfLineValidity

      1. Ensure the result will be visible for the user.

      2. do not set any Calculation Context of this element

      3. format the result money with € currency

4. Calculate the Rebate Forecast - create element “ForecastRebate”. Ensure the result will be visible.

   Rebate Forecast is how much money we are forecasting to give to the customer, based on the sales forecast and the negotiated rebate percentage.

   Example 3. Code of Element "ForecastRebate"

   if (out.ForecastSales != null && out.RebatePct != null) {
       return out.ForecastSales * out.RebatePct
   }

   1. Ensure the result will be visible for the user.

   2. do not set any Calculation Context of this element

   3. format the result money with € currency

5. Test the Logic:

   1. Set all the Parameters

      1. Context must be set to Condition Type, as we’re testing the logic of line item (and line item is of certain Condition Type)

      2. For inspiration of date parameter values, use the Test Case written at the beginning of this Lab.

      3. Remember to click Generate Parameter so that the logic generates the other parameters.

         

      4. Note: you can ignore the Target Date parameter, it will be replaced by value of Calculation Date anyway

      5. for Customer(s), click on the magnifying glass icon, and setup the filter

         

      6. for Product(s), click on the magnifying glass icon, and setup the filetr ProductGroup = Beef

   2. and then Test the logic and verify you get correct results from Datamart.

   3. Do test for different combinations of

      1. Start and End dates

      2. Product and Customer Groups

   to get numbers for verification/comparison of DM queries results returned by your logic, the easiest and quick way to get summaries of Datamart, use the PriceAnalyzer → Data Analyzer → Data Table and simulate the required calculation there as well.

6. Deploy the verified logic to the partition.

Add the logic to the Condition Type

Setup your new logic to the Condition Type.

1. Navigate to RebateManager  Condition Types

2. Add new Condition Type “Bonus On Sales”, set the Pricing Logic to your new logic BonusOnSales

   

   Figure 3. Condition Type, selection of Pricing Logic, which will calculate it

   1. Hint: if you cannot see the newly deployed logic, reload the browser

Verify new Condition Type functionality in user interface

1. Verify the new Condition type by making a new Rebate Agreement

   1. Create new Rebate Agreement

      1. Navigate to RebateManager  Rebate Agreements

      2. Use button New Rebate Agreement

      3. Setup Header Info: Start Date, End Date, Payout Date, Calculation Date and Customer Group according the Test Case.

         

         Figure 4. Screenshot of Rebate Agreement → Header detail screen, with header inputs set

   2. Add new Line Item

      1. Navigate to “Items” section/tab

      2. Select your new Condition Type - either click on the "down" arrow in the input "Browse Items" or click on the "+ Add Items" button next to it

         

         Figure 5. Screenshot of Rebate Agreement → Items screen, adding new line

      3. Select the Bonus On Sales Condition Type

      4. Result: The system will add the line item and immediately calculate it, so you will likely see some exclamation marks, because you haven’t had chance to fill in the inputs.

         

         Figure 6. Screenshot of Rebate Agreement → Items screen, new line added

   3. Setup the inputs for the Line Item

      1. Review the Customer Group inherited from the header

      2. Select the Product Group, and Rebate % according the Test Case and recalculate

      3. Result: there should be no errors, and the required results should be correctly calculated

         

         Figure 7. Screenshot of Rebate Agreement → Items screen, inputs set and recalculated

   4. Save the Rebate Agreement