How to Create a Demand Plan in Retail Link

Learn about:

• Building a Decision Support query in Retail Link to forecast weeks of supply
• How to use this report to create a demand plan
• How to adjust a demand plan

---

We’ve discussed demand planning before and the importance of using machine learning to gain insights. If you recall,

[A] demand forecast asks, “How much does my customer want?” A supply plan asks, “How much product should I make?” And a demand plan seeks to answer both questions, as well as “What should I expect?”

From “Demand Planning” by SupplierWiki

We also went over how to use Walmart’s point-of-sales forecast and supply planning to help avoid out-of-stocks. This article will walk you through how to use Retail Link to create a demand plan.

The essentials of a demand plan

While there is no hard-and-fast method for demand planning, most will include the following components:

• Accurate data: Historic sales performance and forecast accuracy are essential in predicting future production demands. 
• Collaboration: Suppliers must collaborate with their Replenishment Managers to determine what supply levels each party can reasonably maintain, typically expressed in weeks of supply. Without this target, suppliers cannot develop an effective demand plan.
• Data reduction tools: Unfortunately, Retail Link cannot provide a comprehensive demand plan. Suppliers must use tools such as Excel or modeling software to turn the raw data into an actionable plan.
• Implementation and monitoring: Demand planning is not a “set it and forget it” task. It requires constant monitoring and adjustments to new data or insights. At a minimum, suppliers should review and update their demand plan weekly.

A demand plan is just a guess if it does not begin with accurate historical data. Two main pieces of data are historical sales and sales forecast. In other words, what Walmart has sold, and what it is forecasting to sell. 

While suppliers can extract two years of sales data from Decision Support, suppliers should base demand plans on recent sales history. A standard time frame is to include the last four weeks of sales data. 

For forecast data, the Global Replenishment System (GRS) only provides access to 17 weeks of data. With this limited viewpoint, demand planners must review the data weekly, as lead times can be significant, especially for direct import suppliers.

Building the DSS query in Retail Link

The data reduction for a demand planner can take many forms, but suppliers can best extract the relevant data from DSS with the following general report recipe. Rather than following this example verbatim, suppliers should carefully consider their requirements and adjust the details as needed. 

The easiest way to build the report is to use the Store Detail report template. This template is in the Sales and Margin folder in DSS. Click on Store Detail to open the Report Wizard.

In the Columns tab, select the report columns in the following screenshot. Remember, you can right-click on any Report Column to see a brief description of that selection. If you are unsure which folder contains which column, use the Search feature at the top of the report columns list.

DSS Report Wizard – Columns tab for a demand plan

Columns:

• Prime Item Nbr
• Prime Item Desc
• Vendor Stk Nbr
• POS Qty
• Curr Repl Instock %
• Curr Str On Hand Qty
• Curr Str In Transit Qty
• Curr Str In Whse Qty
• Curr Str On Order Qty
• Curr Whse SS Order Qty
• Curr Whse On Hand Qty
• Select the future 17 weeks from the Store Weekly Forecast folder.

On the Items tab, we define the “pool of data” for our query. In our report, we will ask DSS to return only items for which we need to build a demand plan. You can use different Item filters based on your specific needs.

Use the following screenshot as a guide for the Items tab selections.

DSS Report Wizard – Items tab for a demand plan

Select Item Nbr > Is One Of and add the relevant item numbers.

On the Locations tab, we define the “geographic pool of data.” Generally, a demand plan would include all replenishable stores, which we are selecting in this exercise. However, you can adjust this pool of data to include a subset of stores, depending on your specific needs.

DSS Report Wizard – Locations tab for a demand plan

Select All Stores (Without Dotcom).

The Times tab is where you can define the time range for this report. For demand planning, the best practice is to use a recent snapshot of sales data. This range will help when estimating forecast accuracy during the data reduction stage. The preferred time range is the Last 4 Weeks.

DSS Report Wizard – Locations tab for a demand plan

This report requires no selections on the Options tab.

The Submit tab is where you can name, run, save, and schedule this report. 

• Title – The heading that appears above the data results in the Excel document.
• Run Now – Tells DSS to submit the request to the database for processing. In essence, “Go get the specific data I asked for in this query.”
• Save – Adds this report template to My Reports for use in the future.
• Schedule – Instructs DSS to run this report on a recurring basis (weekly, monthly, quarterly, etc.).

DSS Report Wizard – Submit tab for a demand plan

Related Reading: How Do I View My Instocks in Retail Link?

Data reduction

Once downloaded, the Excel sheet contains several aspects required in a demand plan: recent sales history, current inventory ownership, and future sales forecast. However, the DSS report will not return a viable demand plan. There is still plenty of work to do!

The goal of any demand plan is to maintain target levels of supply in three areas: at stores, distribution centers, and the supplier facility. The supplier should calculate these supply levels in terms of weeks of supply and determine the actual targets in concert with the Walmart Replenishment Team. 

To begin analyzing the data, we need to determine a few key metrics:

• Forecast accuracy
• Current total ownership
  • Stores
  • Warehouse
  • Supplier facility
• Target weeks of supply

Forecast accuracy

Forecast accuracy will help the supplier determine the confidence that it can place in the future sales forecast. To calculate forecast accuracy, suppliers need to compare a four-week sales history with the same period’s forecasted sales rate. We obtained a four-week sales history from our DSS report. However, DSS does not provide a historical sales forecast. This data comes from the supplier’s internal reporting.

Many accepted formulas suggest simply dividing the difference between forecast and actual sales by the forecasted sales to calculate forecast accuracy. However, this method does not treat all situations equally. The correct way to calculate forecast accuracy is with the following general steps:

• Determine the absolute value of the difference between forecast and actual sales using the ABS() function in Excel.
• Divide this result by the larger of either the forecast or sales values using the MAX() function in Excel. 

This procedure results in a percentage, or confidence level, that we can apply to the future forecast values as a safety factor. 

Total ownership

Walmart bases purchase orders on sales projections as well as inventory position. If a store sells ten units but has 50 units on hand, the store likely does not need to reorder products from the supplier. Therefore, suppliers must keep track of total corporate ownership when creating a demand plan.

For a demand plan, suppliers should consider three distinct points: stores, warehouses, and the supplier’s facility. Demand planners can calculate the first two data points using the data extracted in the DSS report. The demand planner will need to manually enter the last data point from internal inventory availability reports. The report should only include inventory that is available to ship the next day.

Use the following calculations:

• Store Ownership = 
  • Curr Str On Hand Qty +
  • Curr Str In Transit Qty +
  • Curr Str In Whse Qty +
  • Curr Str On Order Qty
• Warehouse Ownership = 
  • Curr Whse SS Order Qty + 
  • Curr Whse On Hand Qty
• Supplier Ownership = 
  • Manual entry from supplier data

Calculate weeks of supply

Calculate actual weeks of supply by using one of the following methods:

Historical Methodology looks at recent sales history when determining levels of supply.

• Store Weeks of Supply = 
  • Store Ownership ÷ Last Four Weeks POS
• Warehouse Weeks of Supply = 
  • Warehouse Ownership ÷ Last Four Weeks of POS
• Supplier Weeks of Supply =
  • Supplier Ownership ÷ Last Four Weeks of POS

Forecast Methodology looks at future forecasts when determining levels of supply.

• Store Weeks of Supply = 
  • Store Ownership ÷ Next Four Weeks Forecast
• Warehouse Weeks of Supply = 
  • Warehouse Ownership ÷ Next Four Weeks Forecast
• Supplier Weeks of Supply = 
  • Supplier Ownership ÷ Next Four Weeks Forecast

Choosing the correct methodology is dependent on the supplier’s specific situation. There are times when a historic methodology is more appropriate. However, if the demand plan is for highly seasonal items, it might be more appropriate to use forecasted sales data to calculate weeks of supply.

Armed with actual weeks of supply, suppliers will need to collaborate with their Replenishment Team at Walmart to determine optimal inventory levels as a function of weeks of supply. These two data points, actual and optimal, will allow the supplier to move forward in the demand plan process.

The demand plan

Recall that the purpose of a demand plan is identifying when the supplier needs to have inventory available to ship and in what quantities. In the previous section, we determined current ownership levels as well as target weeks of supply. We can now move forward, comparing each future week’s projected inventory, sales, and receipts to the target weeks of supply. 

Follow these steps to calculate future weeks of supply every week:

Ending Inventory = Starting Inventory – Forecasted Sales – Feature or Promo Projections + Projected Receipts

• Starting Inventory for the first week of the demand plan will be the total current inventory obtained from DSS and supplier resources. For subsequent weeks, the starting inventory will be the ending inventory from the previous week.
• Forecasted Sales are straight from the DSS report for the future 17 weeks.
• Feature or Promo Projections will need to come from manual entries, as the GRS forecast does not include this data.
• Projected Receipts are where the supplier gets to play around with the demand planner. Manually entering values here will automatically update the estimated weeks of supply for that same week. Suppliers should aim to have enough receipts to maintain the total weeks of supply targets established by the Walmart Replenishment Team.
• Ending Inventory is the result.

Knowing the approximate ending inventory every week is a good start. However, suppliers should be thinking of inventory in terms of weeks of supply. To calculate estimated weeks of supply weekly is simple:

 Ending Weeks of Supply = Ending Inventory ÷ Weekly Forecasted Sales

This last data point, ending weeks of supply is precisely the value we are interested in maintaining!

Related Reading: What Is a Supply Plan?

Working example

OK, that was a lot. Suppliers need to understand the theory behind the development of a demand plan. There are many ways to perform data reduction, whether in Excel or via a software solution. However, failure to understand the fundamentals behind the data could result in negative consequences and a failed plan.

Let’s look at developing a demand plan with some real numbers:

Total Ownership

8,000 units

Next Week Forecast

2,300 units

Promo/Features

1,200 units

Supplier Receipts

Unknown

Target Weeks of Supply

3.5 weeks

Sample data for illustration purposes.

Here, the goal is to calculate the weeks of supply at the end of each week, comparing that value to the target weeks of supply. By playing around with the Supplier Receipts, we can see the impact on the final weeks of supply. By adjusting the Supplier Receipts until the actual weeks of supply are greater than the target, the result is the estimated amount of inventory that needs to be available to ship from the facility during that week.

Let’s look at two examples of supplier receipts:

Scenario 1:

Total Ownership

8,000 units on hand

Next Week Forecast

2,300 units

Promo/Features

1,200 units

Supplier Receipts

100 units

Ending Inventory

4,600 units

Ending Weeks of Supply

2.0 weeks

Scenario 1: Supplier Receipts of 100 units

In this scenario, the supplier only receives 100 units, which results in only two ending weeks of supply. Since the target weeks of supply is 3.5, the supplier needed to receive more than 100 units that week to maintain minimum levels of supply throughout the supply chain.

Scenario 2:

Total Ownership

8,000 units on hand

Next Week Forecast

2,300 units

Promo/Features

1,200 units

Supplier Receipts

4,000 units

Ending Inventory

8,500 units

Ending Weeks of Supply

3.7 weeks

Scenario 2: Supplier Receipts of 4,000 units

In this scenario, the supplier receives 4,000 units, which results in 3.7 ending weeks of supply. Since the target weeks of supply is 3.5, this is more than enough inventory to maintain the minimum acceptable inventory without burdening the supply chain with excessive inventory loads.

The demand planner should repeat the same steps for each week for which a sales forecast is available, ensuring that the supplier receipts each week are sufficient to maintain minimum acceptable inventory levels.

In Summary

Quite a bit goes into demand planning. Developing a demand plan is conceptually simple. However, the many steps involved can make the task seem daunting. 

Suppliers should monitor their demand plan weekly, adjusting receipts based on new information or adjusted targets. Constant communication with the Walmart Replenishment Team can significantly reduce store out-of-stock issues due to improper demand planning.

Let SupplyPike do the heavy lifting

With SupplyPike’s analytics solution, Retail Intelligence, we do the heavy lifting of demand planning for you. Using advanced machine learning techniques, we take Walmart’s GRS forecast, compare the data, and create an actionable plan.

Retail Intelligence – SupplyPike Sales Forecast

Adjust your weeks of supply and create an accurate supply plan in seconds:

Retail Intelligence – Supply Plan

And you try it with your data free for 14 days!