Using JMP for Production Planning

[#1]

Introduction

There are many reasons to forecast in a production environment, including production planning, sales forecasts, and raw material usage. The process for creating these forecasts can be labor-intensive and frustrating, resulting in forecasts that aren't updated as often as they should be. These delays can result in missed sales opportunities, missed deliveries, and other high-cost problems. There's an easier way. The Time Series Forecast platform in JMP can easily perform forecasting on a large number of groups at once. These forecasts can be shared with others, and the entire process can be easily automated. The graphic below shows how the entire process can be repeated with just one click.

Case study

I'll use the fictional Red Triangle Widget (RTW) company in this example. RTW has ~250 different products (SKUs) that it manufactures and sells to the public. If its sales forecasts are wrong, then its production plans will be wrong, meaning it will risk either creating too much inventory and tying up cash until the inventory sells, or perhaps worse, missing out on market opportunities.

Currently, RTW’s forecasting process takes an entire week for one person to accomplish. This person gets a database dump from the IT department and must then manually analyze each SKU in the data, creating a best-guess forecast for each SKU. Because this process takes so much time to accomplish, RTW only updates its forecasts once per month. The rest of the month often includes scrambling to meet customer demand, since the forecasts tend to become less useful the further out they are forecasting.

Clearly, RTW needs to streamline this process to avoid wasting one employee's entire week every month, losing uncountable hours fighting against forecasts that are too quickly out of date, and missing opportunities to sell more widgets. It needs to update its forecasts weekly, but the current method takes so much time and labor that weekly updates are impossible without hiring at least one more person. Since RTW doesn’t have the budget to hire additional staff, it just needs a simple, fast, repeatable, automated way to do its sales forecasting. 

Data access

Note: This entire process needs to be automated, so begin by going to File > New > Workflow, then select the Record button to implement all of the necessary automation. If you're unfamiliar with the Workflow Builder, check out either this quick introduction or this more detailed explanation.

Since RTW's IT group was already generating the sales data used for the forecasting process, it makes sense to just ask them for the SQL query they are using and execute it directly in JMP. To do this, use File > New > Database Query. Next, convert the query from JMP's visual method to a Custom SQL Query and paste the SQL from IT in the window. Running the query creates a data table with ~22,000 rows representing all daily widget sales for the last three months.

Data preparation

Because RTW wants to do weekly forecasting, the data should be grouped by week. Also, because the forecast would be negatively affected by using data from partial weeks, any weeks without seven days of data need to be removed.

The Tables > Summary tool is a convenient way to shape the data into a weekly format. Creating both Week and Year columns will be useful later, especially if the forecasts being made span different years.

Using Rows > Row Selection > Select Where and selecting all the rows that don't have seven data points makes it easy (and repeatable for the Workflow Builder) to keep only complete weeks. Delete the selected rows after clicking OK.

One more data task is necessary before creating the forecasts. Create a new column with a formula for the date, based on the year and the week. This is useful because it forces all the data points to be in consistent weekly increments, which is important for forecasting tools.

Forecasting

Now that the data is cleaned and structured properly, Time Series Forecasting in JMP is the easiest part. From Analyze > Specialized Modeling > Time Series Forecast, it’s very straightforward to populate the three fields:

In the window that appears after selecting OK, the platform presents summaries to help you ensure that the data is consistent. After reviewing the summaries, set the number of future time periods you’d like to forecast. Click Run to see the forecasts for all ~250 SKUs.

Note that each SKU has been given an individualized forecasting model specific to the historic data shape for that SKU. More information on the types of models can be found here.

Sharing

Since many people who will use these forecasts aren't particularly interested in the statistics involved, changing the format of the predictions into a simple interactive graph can be helpful. Use the red triangle option to select Save Results, which creates a new data table that includes the forecasts and other data; it can also be used with Graph Builder to create a simple lookup tool.

These reports can be uploaded to JMP Live so that they can be shared with anyone who needs access to the forecasts, even if they don’t have JMP. This is especially useful for sharing forecasts with suppliers or customers. These reports can be linked to or embedded in a web page, as shown in the example below.

Automation

Earlier, I mentioned using Workflow Builder to record the process. Since all the steps were recorded, they only need to be done manually the first time. Whenever the forecast needs to be updated, you only need to click Play, which means that RTW can easily refresh its forecasts each week. The time savings for automating this entire process is enormous.

With the process reduced to a simple click of a button, RTW effectively added 25% of an employee to the team, with no additional cost. The forecasts are now also done weekly instead of monthly, so there's far less intervention work required overall by the whole organization. Overall, using JMP to do forecasting was an absolute win for the team.

Are your Procurement and Scheduling groups already using the Time Series Forecast platform? If not, how much time could they save by automating their forecasts?

Data notes

I used a modified version of data from a 2020 Makridakis forecasting competition found here. The modifications I've done include:

• Subsetting and stacking the most unique (~250) groups in the data set.
• Rounding the data to the nearest integer value.
• Changing the column names and unique group ID values.

Additionally, I made a part-number lookup table so the data will approximate how an end-user would likely see the data represented in a database. Since readers won't have access to the exact database that I used, I included the data in Workflow Builder as data tables. All files are attached.

M4C daily data subset.jmp

SKU Index.jmp

Workflow.jmpflow