Price Manager exercises

Zilliant Price Manager Bootcamp

Price Manager exercise files.zip

Price Manager Scenario Basics.pptx

Price Manager Exercises I

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. 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} / {CurrentCost}
        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 ziqUpdatedBy
   • ziqUpdatedDate to ziqUpdatedDate

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

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. 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
5. 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
6. 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 ziqUpdatedBy
   • ziqUpdatedDate to ziqUpdatedDate

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

User-specific Access

Use the platform user administration capabilities to set up the user Penny Paperwhite so that she can only edit the Q2 Cost Change scenario and see the Product Prices workbook, with each included worksheet limited only to the products in the Printer Paper and Books & Pads categories.

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 Paper 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

Create Printer Paper Filter Set

1. Open the Filter Sets workbook and run Update Dropdown
2. Add two new records to the Filter Dimensions worksheet for a new Product Group Named Dimension for the worksheet ids associated with the Price and Cost and List Price Review worksheets
3. Use the dropdown to select the Product Level 1 field in each worksheet
4. Go to the Filter Sets worksheet and add an Paper Products Filter Set associated with the Product Group Named Dimension
5. Run Save Filter Sets
6. Once Save Filter Sets completes, go to the Filter Set Values worksheet and select to Include the following values
   • Printer Paper
   • Books & Pads

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 and Paper Products Filter Set
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

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.