Automated Demand Planning for faster decision-making using Amazon QuickSight

The demand planning team is responsible for ensuring that demand for each of their SKU is measured as accurately as possible. They typically must liaise with the Sales and marketing, finance, and warehouse management teams to submit their reports for production planning. Their work depends on Demand Forecasting models (Link on how businesses can build intelligent demand forecasting models). Demand planners need to look at other aspects like Model accuracy across category/Pareto SKUs, model biases, and Trend signals to take decisions. Collating this information is a tedious job and often consumes much time.

Building an automated dashboard that would calculate this information prior would enable faster decision-making and less time spent on data collection. This blog explains how demand planners can use Amazon QuickSight (AWS’s native Business Intelligence visualization tool) to build a dashboard capturing these KPIs.

Let’s begin with understanding the KPIs and decisions the demand planner would need to take before creating the dashboard. Please note that the dashboard design would depend on the decision and KPIs the business user would want to see, and not the other way around.

*Forecasted Demand & Accuracy: *

1. Overall model accuracy of the previous month – To get perspective on how the model performed last month

• Last three months average forecast accuracy – To get perspective on how the model has been performing consistently and to decide whether the model output is reliable or not
• Forecast accuracy across Categories – To decide which categories they would need to adjust manually or could use the model output directly
• SKU-wise demand and accuracy – Similar decision as above

1. SKUs sales in volume and value – This will help them decide which SKUs to focus on ensuring no-stockout and maintaining required service levels

2. Trend and Biases of the SKUs – to gain a perspective on where the market is headed.

Given that some of the decision above are clear, let’s look at how the data is being captured by the system (Note: the snapshot presented is mock data with only selected information required for the blog is displayed).

These datasets are moved to Amazon Simple Storage Service (S3) bucket (GUI based manual upload) using the reference architecture below and an ETL job is written using AWS Athena. This data is then moved to Amazon QuickSight using a job which is triggered on AWS Athena.

Sample query to join forecast and actual sales data with other master data:

select a.location, c.location_type, c.region, a.item, b.item_description, b.category, b.subcategory, b.brand, b.manufacturer, b.per_unit_price, a.date, a.forecast, d.sales
from forecast_data as a
left join
category_master as b
on a.item=b.item_number
left join
location_master as c
on a.location=b.location
left join
sales_data as d
on a.location=d.location
and a.item=d.item
and a.date=d.date

*Final Design of the QuickSight Dashboard: *

The idea behind the dashboard design is to give an overall view of the trend in demands observed at various levels to the demand planners and help them understand the gaps in their system which needs attention to achieve higher forecast accuracies and optimize their planning.

1. Key KPIs such as sales in volume and value, forecast accuracies and BIAS at different intervals of time are highlighted to give users an overall health check of their forecast models in the background.
2. A trend chart denoting the actual sales Vs forecasted sales helps the user gauge the model performance over a period and understand if the category sales meet the target or not.
3. Tree map consolidating the forecast value (denoted by size) and accuracy (denoted by color, green and red implying high and low accuracy respectively):
4. ABC-XYZ analysis – The SKUs have been distributed between different buckets based on the model accuracy and the sales value.

• XYZ – The SKUs are classified based on the respective accuracies with X being the high accuracy SKUs (>=90%), Y being moderate accuracy SKUs (between 60 & 90%) and the remaining in Z bucket (less than 60% accuracy).
• ABC – The SKUs are classified are classified based on the last 3 month sales value with A being the high selling SKUs, B being the moderate selling SKUs and C being the least selling SKUs.

1. The users can also deep dive into specific SKU related information if and when required.
2. The actions feature available in QuickSight enables interactive dashboard by enabling users filter visuals, attach URL option in just a click of a button on an existing visual.
3. Relevant controls to filter the data at required levels are provided to the users as well.

Key decisions driven by the dashboard:

1. The users can infer is the forecast model running in the background requires tuning or not based on the accuracy and BIAS numbers reflecting in the dashboard. If the accuracy has been consistently low for a certain category/SKU – it’s quite possible that the model running behind needs to be adjusted.
2. The dashboard helps the demand planning team to focus on high value SKUs to improve their forecast accuracy thereby increasing the sales and margins.
3. ABC-XYZ analysis of SKUs enables better planning for SKUs basis the bucket they’re in. For example,

• CZ – Low sales, low accuracy – since their sales and accuracy both are low, maintaining a low service level and minor tweaking of model would suffice.
• AX – High sales, high accuracy – best performing SKUs, no need of intervention. Service levels can however be increased since they’re major drivers of sales.

The dashboard is designed in a manner where maximum decisioning is enabled and the users get to take quick actions based on the insights provided by the dashboard. Demand planning is a crucial part of the supply chain process and the dashboard helps the demand planning team to reduce their time to truth and take immediate corrective actions.

For the readers of the article, I am enclosing the JSON script of the dashboard and link to the dataset which the user can use to re-create these dashboards in their Amazon QuickSight Account for practice.

{'ResponseMetadata': {'RequestId': '8f13c52c-8499-4490-a7c9-eb319097dd20',
  'HTTPStatusCode': 200,
  'HTTPHeaders': {'date': 'Sun, 29 Jan 2023 14:26:12 GMT',
   'content-type': 'application/json',
   'content-length': '1780',
   'connection': 'keep-alive',
   'x-amzn-requestid': '8f13c52c-8499-4490-a7c9-eb319097dd20'},
  'RetryAttempts': 0},
 'Status': 200,
 'Template': {'Arn': 'arn:aws:quicksight:ap-south-1:176660025607:template/UniqueIDTemplate',
  'Name': 'QuickSight Template Test',
  'Version': {'CreatedTime': datetime.datetime(2023, 1, 29, 19, 42, 53, 689000, tzinfo=tzlocal()),
   'VersionNumber': 1,
   'Status': 'CREATION_SUCCESSFUL',
   'DataSetConfigurations': [{'Placeholder': 'Demand Forecast - Data',
     'DataSetSchema': {'ColumnSchemaList': [{'Name': 'Location Type',
        'DataType': 'STRING'},
       {'Name': 'Item', 'DataType': 'INTEGER'},
       {'Name': 'Manufacturer', 'DataType': 'STRING'},
       {'Name': 'Forecast', 'DataType': 'INTEGER'},
       {'Name': 'Item Description', 'DataType': 'STRING'},
       {'Name': 'Category', 'DataType': 'STRING'},
       {'Name': 'Region', 'DataType': 'STRING'},
       {'Name': 'Date', 'DataType': 'DATETIME'},
       {'Name': 'Brand', 'DataType': 'STRING'},
       {'Name': 'Subcategory', 'DataType': 'STRING'},
       {'Name': 'Sales', 'DataType': 'INTEGER'},
       {'Name': 'Per unit Price', 'DataType': 'INTEGER'}]},
     'ColumnGroupSchemaList': []}],
   'Description': '1.0',
   'SourceEntityArn': 'arn:aws:quicksight:ap-south-1:176660025607:analysis/3cea7fe4-8b47-48ec-b8de-0dec71ffe2eb',
   'Sheets': [{'SheetId': 'c312051d-4052-446f-9083-3972c5f22456',
     'Name': 'Demand Forecast View'}]},
  'TemplateId': 'UniqueIDTemplate',
  'LastUpdatedTime': datetime.datetime(2023, 1, 29, 19, 42, 53, 675000, tzinfo=tzlocal()),
  'CreatedTime': datetime.datetime(2023, 1, 29, 19, 42, 53, 675000, tzinfo=tzlocal())},
 'RequestId': '8f13c52c-8499-4490-a7c9-eb319097dd20'}

Author:
Gaurav H Kankaria is the Head of Strategic Partnerships and Engagement Manager (ex- Senior Data Scientist) at Ganit Business Solutions Private Limited. He has over 8+ years of experience designing and implementing solutions to help organizations in the Retail Domain. He is an AWS certified Solution Architect — Professional and Data Analytics — Specialty.

Vaishnavi B is an Data Scientist at Ganit Business Solutions Private Limited. She has over 2+ years of experience in building strategic decision boards and demand forecasting solutions to help the supply chain team of Retail organisations. She is an AWS certified Data Analytics — Specialty.