How to generate a Sales Forecast in a file excel with R

Hi All,
I am working in the Demand Planning field for a company that produces pasta.

Our business is based on two models:

  • Make to Order: We have a lead time of 4 weeks from the order receiving date
  • Make to Stock: from the order we have to deliver the orders within 4-7 days.

The challenge is the Make to Stock business, where we have to be ready to ship product, but also to not have an infinite inventory.

I am kind of new to R, but what I'd like to do is a R code that every Friday I can run and generate the requirements for the next months.

My sales history file would be based on Dates (day), SKU and Q.ty, and I'd like to generate the forecast for the next 4 weeks.

Could you please help me or give me an hint on how to go ahead?

Thanks
Luigi

The first step might be to read the data into R. I suggest the following:

The first step should be to confirm the value and utility of the forecast. Make sure that the forecasts can be used to make business decisions. Use the time series models to present some conclusions about the time series (trend, seasonality) to the business and see if they agree to build confidence about their validity.

Hopefully the business has an application in mind. Otherwise you can consider using the forecasts as input to a safety stock calculation to guide inventory. I can recommend the book "Operations Management for Competitive Advantage" by Chase, Jacobs, Aquilano on this topic.

Once you have the confidence to move forward with a software solution, you can plan the software. Ideally the sales data is in a database that can be queried programmatically. You'll need code to query the data, fit the models, make the forecasts, output the results.

You can schedule this process using Microsoft Task Scheduler or cron in Linux. If you have a single script that performs the task, you can call > Rscript make_forecasts.R on a schedule.

A powerful and simple approach is to focus on the data and the math and let someone else manage everything downstream. Write your R code to ultimately save the forecasts in a database. And have someone else worry about making dashboards or ingesting the forecasts into whatever tool they need to make decisions about inventory or supply chain.

2 Likes

Perhaps have a look at the concepts here too: