Price Manager exercises

Zilliant Price Manager Bootcamp

Price Manager exercise files.zip

Price Manager Scenario Basics.pptx

Price Manager Exercises I

Customer Requirement:
The team needs a streamlined process for reviewing and adjusting product prices and costs. This includes handling price changes based on cost adjustments and different pricing strategies. The solution should facilitate easy updates to pricing, maintain consistency across product data, and allow for scenario-based price revisions, ensuring accurate pricing reflects the latest cost data and business strategies.

Acceptance Criteria
Scenario 1:
As an IQI user, I must be able to review the published master List Price and Cost for each Product as well as see both when the price was last updated and by whom.

Scenario 2:
As a Product Manager in Price Manager, I must be able to

• import new Product Cost data.
• select whether to hold the current List Price, pass the cost change on to the List Price, maintain current Margin based on the new Product Cost, or provide an Override Price for each Product.
• selectively Publish the Cost and Price changes back into the master Product Prices.

PM Exercise I Overview.pptx

Product Price and Cost

1. Create a new Master Prices folder
2. In Master Prices, create a new Product Prices workbook
3. Create a new Price and Cost worksheet from the Product managed data source, including the following fields
   • Product Id
   • List Price
   • Standard Cost
4. Format List Price and Standard Cost as Currency with 2 decimals
5. Create a new UpdatedBy input column
   • Type = String
6. Create a new UpdatedDate input column
   • Type = DateTime
7. Export the Price and Cost worksheet to a csv

Create Cost Change Template

1. Open the csv exported from the Price and Cost worksheet and rename the column headers as follows
   • List Price to Current Price
   • Standard Cost to Current Cost
2. In the Templates folder, create a new Cost Change Template workbook
3. Create a new List Price Review worksheet using the updated csv file
   • Product Id as Business Key
   • Product Id as String
   • Current Price as Float
   • Current Cost as Float
4. Format Current Price and Current Cost as Currency with 2 decimals and make both fields read-only by deselecting the Editable checkbox
5. Create a new Publish input column
   • Type = Boolean
6. Add a New Product Costs worksheet from the New Product Costs.csv file
   • Product Id as Business Key
   • Product Id as String
   • New Cost as Float
7. Format New Cost as Currency with 2 decimals
8. On the List Price Review worksheet, add a relationship to the New Product Costs worksheet
   • Join Type = Inner
   • Join Conditions:
     • Product Id equals Product Id
9. Add the New Cost related column from the New Product Costs worksheet to the List Price Review worksheet and format it as Currency with 2 decimals
10. On the List Price Review worksheet, add a relationship to the Product worksheet in the Input Data workbook
    • Join Type = Inner
    • Join Conditions:
      • Product Id equals Product Id
11. Add the Product Description and Product Level 1 related columns from the Product worksheet to the List Price Review worksheet
12. Create a new Override Price input column
    • Type = Number
    • Display Format = Currency
    • Decimals = 2
13. Create a new Cost Pass Through Strategy input column
    • Type = String
14. In the Templates folder, create a new Template Picklists workbook
15. Create a new Cost Pass Through Picklist worksheet from the Cost Pass Through Picklist.csv file
    • Strategy as Business Key
    • Strategy as String
16. In the List Price Review worksheet, change the Cost Pass Through Strategy column to a dropdown referencing the values from the Cost Pass Through Picklist
    • Entity = Cost Pass Through Picklist
    • Source field = Strategy
17. Create a New Price calculated column on the List Price Review worksheet calculating a new price considering different values entered in the Override Price and Strategy fields
    • Type = Number
    • Display Format = Currency
    • Decimals = 2

CASE
    WHEN {OverridePrice} > 0 THEN {OverridePrice}
    ELSE CASE
        WHEN {CostPassThroughStrategy} = 'Hold Price' THEN {CurrentPrice}
        WHEN {CostPassThroughStrategy} = 'Full Cost Pass Through' THEN {CurrentPrice} +({RW0_NewCost} - {CurrentCost})
        WHEN {CostPassThroughStrategy} = 'Maintain List Margin' THEN {RW0_NewCost} * {CurrentPrice} / NullIF({CurrentCost}, 0)
        ELSE NULL
    END
END

18. Add an Avg Cost Change KPI card calculating the average cost change on the sheet

SUM(([RW0_NewCost] - [CurrentCost]) / [CurrentCost]) / SUM(1)

19. Pin the Product Id field to the left and arrange the remaining fields to support the workflow of a user reviewing the price changes
20. On both the List Price Review and New Product Costs worksheets, Set worksheet settings to default
21. Configure the Source Data for the List Price Review worksheet from the Price and Cost worksheet in the Product Prices workbook
    • Product Id to Product Id
    • List Price to Current Price
    • Standard Cost to Current Cost
22. Copy the workbook id for the Cost Change Template workbook to use the next section

Register Cost Change Template

1. Open the Template Configuration workbook
2. Insert a row to register a new template configuration
   • Template = Cost Change
   • Template Workbook = (workbook id copied in previous step)
3. Select the Process checkbox for the new template configuration and run Extract Template Configuration
4. On the Publish Target worksheet, configure the publish target for the List Price Review worksheet
   • Publish Target Entity = Product
   • Filter =

{"filterName": "Publish","filterExpression": "Publish eq 1"}

5. On the Field worksheet, define the following publish target fields for the List Price Review worksheet
   • ProductId to ProductId
   • RW0_NewCost to StandardCost
   • NewPrice to ListPrice
   • ziqUpdatedBy to UpdatedBy
   • ziqUpdatedDate to UpdatedDate

Q2 Cost Change Scenario

1. Open the Manage Scenarios workbook
2. Select +New Scenario to create a new scenario
   • Scenario Name = Q2 Cost Change
   • Template = Cost Change
   • Click Create
3. Once the scenario builds and is refreshed, drill into the Q2 Cost Change scenario
4. Import records into the New Product Costs worksheet from the New Product Costs - Q2.csv file
5. Review the List Price Review worksheet and apply different values in the worksheet to set a value in the New Price field, selecting some records for Publish
6. In the Scenarios worksheet in the Manage Scenarios workbook, select the Q2 Cost Change scenario and run Publish
7. After the publish completes, review the Product Prices workbook to confirm that the master prices were updated as expected

Price Manager Exercises II

Customer Requirement:
The team needs a streamlined process to review and update eCommerce product prices based on various customer and product attributes. This includes adjusting prices by evaluating margins and costs. The solution should enable efficient price adjustments, scenario-based reviews, and seamless updates to the eCommerce platform.

Acceptance Criteria
Scenario 1:
As an IQI user, I must be able to review the published master Ecommerce Price for all Products by Customer as well as see both when the price was last updated and by whom.

Scenario 2:
As a Product Manager in Price Manager, I must be able to

• review current ECom pricing for Fastener Products (Product Level 1) for all OEM Customers (Customer Type)
• easily identify Customer-Product combos with average Margin below 20% to define a new ECom Price
• selectively publish the changes back into the master ECommerce Prices

PM Exercise II Overview.pptx

ECom Prices

1. In the Master Prices folder, create a new ECommerce workbook
2. Create a new ECom Prices worksheet from the ECom Prices.csv file
   • Customer Id as Business Key
   • Product Id as Business Key
   • Customer Id as String
   • Product Id as String
   • Ecom Price as Float
3. Format Ecom Price as Currency with 2 decimals
4. Create a new UpdatedBy input column
   • Type = String
5. Create a new UpdatedDate input column
   • Type = DateTime
6. On the ECom Prices worksheet, add a relationship to the Product worksheet in the Input Data workbook
   • Join Type = Inner
   • Join Conditions:
     • Product Id equals Product Id
7. On the ECom Prices worksheet, add a relationship to the Customer worksheet in the Input Data workbook
   • Join Type = Inner
   • Join Conditions:
     • Customer Id equals Customer Id
8. Add the following related columns
   • Product Level 1 (from Product)
   • Customer Type (from Customer)

Create ECom Price Review Template

1. In the Templates folder, create a new ECom Price Review Template workbook
2. Create a new ECom Price Review worksheet from the ECom Prices.csv file
   • Customer Id as Business Key
   • Product Id as Business Key
   • Customer Id as String
   • Product Id as String
   • Ecom Price as Float
3. Create a new Publish input column
   • Type = Boolean
4. On the ECom Price Review worksheet, add a relationship to the Product worksheet in the Input Data workbook
   • Join Type = Inner
   • Join Conditions:
     • Product Id equals Product Id
5. On the ECom Price Review worksheet, add a relationship to the Customer worksheet in the Input Data workbook
   • Join Type = Inner
   • Join Conditions:
     • Customer Id equals Customer Id
6. On the ECom Price Review worksheet, add a relationship to the Price and Cost worksheet in the Product Prices workbook
   • Join Type = Inner
   • Join Conditions:
     • Product Id equals Product Id
7. On the ECom Price Review worksheet, add a relationship to the Customer Product Margin worksheet in the Platform Exercises workbook
   • Join Type = Left
   • Join Conditions:
     • Customer Id equals Customer Id
     • Product Id equals Product Id
8. Add the following related columns
   • Product Level 1 (from Product)
   • Customer Type (from Customer)
   • Standard Cost (from Price and Cost)
   • Average Margin (from Customer Product Margin)
9. Format and rename the related columns as follows
   • Average Margin to Margin Last Year as Percentage with 1 decimal
   • Standard Cost to Current Cost as Currency with 2 decimals
10. Create a Margin at Current Cost calculated field

({EcomPrice} - {RW0_StandardCost}) / NULLIF({EcomPrice},0)

11. Add Column Highlighting on the Margin at Current Cost field to highlight all values less than 20%
12. Arrange and format all of the fields in the worksheet to support the workflow of a user reviewing prices; when done, Set worksheet settings to default
13. Configure the Source Data for the ECom Price Review worksheet from the ECom Prices worksheet in the ECommerce workbook
    • Customer Id to Customer Id
    • Product Id to Product Id
    • Ecom Price to Ecom Price
14. Copy the workbook id for the ECom Price Review Template workbook to use the next section

Register ECom Price Review Template

1. Open the Template Configuration workbook
2. Insert a row to register a new Template configuration
   • Template = ECom Price Review
   • Template Workbook = (workbook id copied in previous step)
3. Select the Process checkbox for the new template configuration and run Extract Template Configuration
4. On the Publish Target worksheet, configure the publish target for the Ecom Price Review worksheet
   • Publish Target Entity = EComPrices
   • Filter =

{"filterName": "Publish","filterExpression": "Publish eq 1"}

5. On the Field worksheet, define the following publish target fields for the ECom Price Review worksheet
   • ProductId to ProductId
   • CustomerId to CustomerId
   • EcomPrice to EcomPrice
   • ziqUpdatedBy to UpdatedBy
   • ziqUpdatedDate to UpdatedDate

OEM Fasteners Scenario

1. Open the Manage Scenarios workbook
2. Select +New Scenario to create a new scenario
   • Scenario Name = OEM Fasteners
   • Template = ECom Price Review Template
   • Select Create
3. Once the scenario builds and is refreshed, go to the Load Filters worksheet and add a row to configure a new load filter
   • Scenario Name = OEM Fasteners
   • Worksheet = ECom Prices
   • Load Filters =

RW0_ProductLevel1 eq 'Fasteners' and RW0_CustomerType eq 'OEM'

4. On the Scenarios worksheet, select the OEM Fasteners scenario and run Refresh to refresh the scenario based on the updated load filters
5. When the scenario refresh completes, drill into the OEM Fasteners scenario
6. Review the ECom Price Review worksheet and set new Ecom Price values, selecting some records for Publish
7. In the Scenarios worksheet in the Manage Scenarios workbook, select the OEM Fasteners scenario and run Publish
8. After the publish completes, review the ECommerce workbook to confirm that the prices were updated as expected

ECom Prices Base Workbook

1. Copy the workbook id of the ECommerce workbook
2. Open the Template Configuration workbook and paste in the workbook id to the Base Workbook field associated with the ECom Price Review Template
3. Select Process for the ECom Price Review Template and run Process Base Workbook
4. Once complete, navigate to to the ECommerce workbook to see the added worksheet referencing the related scenarios

Price Manager Exercises III

Customer Requirement:
The team needs to set up user-specific access to ensure that only Product Managers can use price manager scenarios. This ensures that sensitive information is protected while allowing Product Managers to manage and review the appropriate product data for their role.

Acceptance Criteria
Scenario 1:
As an IQI user, I must

• have read only access to review Master Prices
• not be able to access any Scenarios for publishing product prices

Scenario 2:
As a Product Manager, I must

• have read only access to review Master Prices
• have editor access for Scenarios in order to publish updated prices

User-specific Access

Create Product Managers Group

1. Open the Group Permissions workbook and create a new Product Managers group with a default new folder role of No Access
2. Click Save Groups to register the new group
3. Go to the Folders & Workbooks worksheet and configure the Product Managers group so that it has Editor access to the Q2 Cost Change Scenario, Viewer access to the Product Prices workbook, but No Access to all other content

Assign Access to User

1. Open the User Setup workbook
2. Use the dropdowns to assign the Penny Paperwhite user to the newly created Product Managers Group
3. Run Apply All Permissions
4. Login as the Penny Paperwhite user and navigate to the IQ Interface to confirm the desired level of access

Price Manager Challenge Assignment

Customer Requirement

Create a new template to review contract prices (based on the Contract Line Item entity) and run a scenario to increase all of the current contract pricing for customers in the Government and Healthcare industries to have a discount of no more than 50% off of the current list price.

Acceptance Criteria

• As an IQI user, I should have the ability to manually override the New Contract Price within the worksheet
• As an IQI user, when I review the New Contract Price, if no override exists then the price should be automatically calculated to reflect a 50% discount if the current contract price exceeds that threshold
• As an IQI user, the format of the worksheet should be clean, concise, and user-friendly
• As an IQI user, I should be able to publish the New Contract Price to the Contract Line Item entity