Retail: Markdown Planning

Summary

As a buyer, you are responsible for managing your price reductions to a monthly markdown budget. But markdown management at your company is not that straightforward. You must also adhere to the company’s pricing policies when considering a new price for your items, including having to use price tiers, percentage off, rounding logic, and special price endings to indicate to store managers how many markdown actions have been taken, or “subs”, on the item.

User Story

As a buyer, I want to spend more time evaluating my items for markdown candidates in Anaplan rather than inefficiently addressing the never-ending data collection exercise I go through every month to manage my markdowns. For this part of the overall markdown planning process, I need to manage my markdown drivers:

• Ability to create price tiers with a low price and a high price.
• Ability to create markdown subs that indicate how deep I want to take a markdown.
• For each intersection of price tier and markdown sub, I need the ability to set the drivers:
  • Percentage Off.
  • Round to the Nearest.
  • Special Price Ending. For example, “8” indicates that I want the price to end with an “8”, like $9.98.

In addition to the drivers, I also want the ability to indicate a “Penny Out” for an item which will automatically take my price to one penny, or $0.01. Also, I want to see the recommended price’s Percentage off so I can compare it to the percentage off driver. Lastly, I want the ability to override the price recommendation to any price I choose.

Success Criteria

I know this is right when I have a UX page that allows me to set my markdown drivers and another UX page that allows me to indicate what level of a markdown I want to take and have Anaplan show me how it arrived at the final price using the respective drivers. I only want to see the details if I have selected an item for markdown. Lastly, I know it’s right if I indicate a “Penny Out” or if I override the recommended price, the final price reflects that decision.

I know its right if Anaplan resolves these conditions as a test script:

Book Retail	% Off	Round To	Price Ending	Penny Out	Override	Final Price
$3.29	25%	$0.25	8			$2.48
$2.99	25%	$0.50	0			$2.00
$0.99	20%	$0.25	0			$0.75
$0.99	20%	$0.25	0	TRUE		$0.01
$0.99	20%	$0.25	0		$0.50	$0.50

Architecture | Model Schema

This solution will use the DISCO methodology and will require three lists, three system modules, a calculation module, an input module, and one planning, or output, module shown below.

Action Steps:

• Step 1 | Create Lists for SKU, Price Tiers, and Markdown Subs.
• Step 2 | Create System Modules for each list.
• Step 3 | Create a Calculation Module that looks up the SKU’s Price Tier.
• Step 4 | Create an input module that accepts the markdown drivers: Percentage Off, Round to Nearest, and Price Endings.
• Step 5 | Create the Markdown Planning Module with the desired functionality to meet business requirements.

Step 1 | Create Lists for SKU, Price Tiers, and Markdown Subs

These lists are rather straightforward.

Create the SKU List.

Create the Price Tier List. In this case, we will create five price tiers. You can create as many as is required for your use case. Especially important note: Add a top level. We will need the top level to determine the SKU’s Price Tier as you will see shortly.

Create the markdown sub list. In this case, we do not need a top level. I’ve added four markdown levels, but feel free to add as many as is required.

Step 2 | Create System Modules for each list

To be thorough, a system module should almost always be created for any list you create. However, in this case, we only need two, one for SKU and for the Price Tiers. There are no properties of the markdown subs we need so you can build a system module optionally.

Create the SKU System Module. This module undoubtedly will have a lot of properties, but the one we need is the book retail and the Price tier to use. BONUS: I’ve including the parsing logic from the transaction module.

The line item “Price Tier to Use” can be calculated: SKU to Price Tier. Tier List[LOOKUP: SKU]

Create the pricing tier system module. The properties we need here are three: We need a list formatted line item for Price Tier, and a lower and upper price range.

Step 3 | Create a Calculation Module that looks up the SKU’s Price Tier

To use the markdown drivers, we first must determine what price tier the SKU is using. For additional reading on how to work with price tiers, see this article on tier pricing. Also, take a look at this excellent Level 3 Certification training video on “Working with Ranges, Tiers, or Bands."

Create the calculation module. We will use 3 Booleans and a list formatted line item. The Booleans help us determine if the book price of the SKU is between the ranges. Let’s take a look at the blueprint.

Note the Summary for the list formatted line item is set to 'Last non-blank'.

Now, let’s take a look at the grid. Perfect! It’s always a good idea to unit test though. Pick a few items and make sure the calculation module is selecting the right tier.

Step 4 | Create an input module that accepts the markdown drivers: Percentage Off, Round to Nearest, and Price Endings

We’re getting really close to being ready to test the planning module. We need one more input module which will hold the markdown drivers, percentage off, round to nearest, and price endings.

Create an input module for the markdown drivers. This module uses the price tiers and Markdown Sub lists as dimensions. Optionally, you could add a product dimension if you feel the drivers need to be partitioned further.

To assist the planner, we will add two line items — the low price and high price for the price tier range. Since these only apply to the price tier list, we can create a subsidiary list. This is one of those rare exceptions where a subsidiary list is okay.

We also need the three drivers. Let’s take a look at the blueprint.

Here’s a view of the grid. Note: all line item summaries are turned off.

Step 5 | Create the Markdown Planning Module with the desired functionality to meet business requirements

Now we have the foundation in place to create our planning module.

Build the planning module. There are a lot of line items so let us first start by looking at the blueprint. Note: all line item summaries are turned off. These calculations only apply to the SKU.

Roster:

Planning	Formula
Book Retail	'SYS110 SKU'.Book Retail[LOOKUP: SKU]
Current MD Price
Take MD Sub
Markdown Taken?	ISNOTBLANK(Take MD Sub)
Penny Out?
Percentage Off	Markdown Drivers.Percentage Off[LOOKUP: Take MD Sub, LOOKUP: 'SYS110 SKU'.Price Tier to Use]
Round to Nearest	Markdown Drivers.Round to Nearest[LOOKUP: Take MD Sub, LOOKUP: 'SYS110 SKU'.Price Tier to Use]
Price Suffix	Markdown Drivers.Price Ends With[LOOKUP: Take MD Sub, LOOKUP: 'SYS110 SKU'.Price Tier to Use]
MD Price Not Rounded	Book Retail * (1 - Percentage Off)
MD Price Rounded	MROUND('MD Price Not Rounded', Round to Nearest)
MD Price Suffix	IF Price Suffix = 0 THEN MD Price Rounded ELSE ROUND(MD Price Rounded, 1, UP) - (10 - Price Suffix) / 100
MD Price Recommended	IF Markdown Taken? THEN IF Penny Out? THEN 0.01 ELSE IF MD Price Suffix < 0.01 THEN 0.01 ELSE MD Price Suffix ELSE Book Retail
Override MD Price?
MD Price New
MD Price Final	IF NOT Override MD Price? THEN MD Price Recommended ELSE MD Price New
Percentage Off Final	(Book Retail - MD Price Final) / Book Retail

Here is the logic:

• Lookup up the price tier.
• Obtain the markdown drivers.
• Start by computing the percentage off the book retail.
• Round to the nearest increment.
• Apply a price ending by finding the price nearest that ending.

Time to look at our planning grid. Looks great.

You might have noticed that the cells in this planning grid do not populate unless “Take MD Sub” is selected. Also, notice that the new price for a price override does not appear unless the “Override MD Price?” is checked. You guessed it! Dynamic Cell Access, or DCA. Here are the DCA Drivers.

But wait! Our requirements specifically say UX! Let’s take a look at the page view then.

That’s better. Oh, and we added some conditional formatting. Nice touch!

Conclusion

As a buyer, you will need to manage your markdowns, but you also need a way to adhere to the company guidelines on pricing. Here is a terrific way to manage this process in Anaplan.