Do you not write a model code?Can be used to do sales estimates

Product managers often need to predict after the development of business development, and the prediction model can be used. But if you don’t write code, run model, what should I do? In this article, the author combined with three cases, how to make a sales prediction of product managers do not write the model code, let’s take a look.

In order to have a rough assessment and mastering of the future development of the business, it is often necessary to predict the next month, next quarter, next year, etc., often need to predict the model of the model, and make the company to seize the future opportunities to do the pickup policy. So how do you predict the product managers who don’t write code run models?

This article starts with three cases to tell you.

I. Case 1: Excel operation mode to predict

According to the spending prediction of 2021 – 09, 10, 11 and December, in 20021 – 09, 11 and December.

First look at the discrimination map, which is generally browsing whether there is extreme value (if any, exclude) and whether there is association trend between the two variables:

Fitting precautions:

When using Excel fit, try to attempt to all trend lines, select R2 to close close to 1. The closer R2 is close to 1, the better the fitting effect. Try it, find that the polynomial fit effect is better, and “order” increases up, the closer to R2 is close to 1. However, this data is not much, and the polynomial is intended to take two-term or three items to meet the demand, so it can be avoided, and 3 can be taken.

The results of the fitted are as follows, and the polynomial formula is shown in the figure:

Add a column auxiliary column to the data source;

Bring 21, 22, 23, 24, respectively, Y = -0.5908X ^ 3 + 19.576X ^ 2 – 73.697X + 2128.3

Then get the prediction sales of 2021 – 09, 10, 11 and December.

Second, the case two: Tableau operation mode to predict

The data source is as follows:


Step 1: First pull the month, sell line, and make a draw chart. “Month” in the data source is a text format and needs to be changed to the date format.

Step 2: Click “Month” on the column to change it.

Step 3: Click on the line chart to select “Show Trends Line”.

Click on the line chart to select “Edit all trend lines”, you can see that the prediction model is “linear” prediction.

Click on the fold line chart to select the “Description Trend Model”, you can see the “linear” predicted R square value.

Step 4: In accordance with the method of step 3, select “Edit all trend line” sequentially select “Linear”, “Index”, “Polynomial” 2 degree, “Polynomial” 3 degree, and view the corresponding R square value, select the R square The model is closest to 1 to the final predictive model.

It can be seen that “linear” predicts R part:

“Index” predicts R part:

2 degrees “polynomial” predictive R part:

3 degrees “polynomial” predictive R part:

Comprehensive contrast: 3 degree “polynomial” predictive model R is closer to 1, so it is finally taken.

Step 5: The mouse moves to the trend line, you can see the fitting formula as follows, if the case one adds a column auxiliary column to the data source, and the number of the auxiliary column of the predicted month is entered into the formula, you can get prediction sales .

Also explained: Tableau comes with prediction function, but is not used in this case, as follows:

Remove the “precise date” on the month, select the month, click the line chart to select “Show forecast”.

The result is as follows:

Then click on the chart to select “Predictive Description”, which can be found that the automatic prediction model is “index smooth method”, however, the R party in the fitting index related prediction method is not optimal in the fitting of the above steps.

Third, the case three: seasonal sales forecast

The data source is as follows:

Picture of ordinary line charts first, see if there is a significant seasonality.

The following figure shows that the sales volume does present seasonality, in which the second quarter is peak season. Then go to the season to season.

Put the data source through the data perspective table to the following figure:

Calculate the annual mean of each quarter, as shown in the figure below; calculate the overall average of 15 samples, as shown in the figure below; “the annual average” / “total average” calculates “seasonality index”.

Let’s take a summary table.

1 Repeat the calculated “Season Index” to the “Season Index” column in the figure below.

2 go to the season = Sales / Season Index.

3 Fit outline predictive formulas in accordance with the sales volume after “auxiliary column” and “going to season”.

Y = -2.4907x + 2532

The fitted linear formula is because the seasonal factors have been excluded in step 2.

4 Distribute the auxiliary column into the formula and calculate the “linear prediction” column.

5 Put the “Linear Prediction” column with the “Seasonal Adjustment” column by “Season Index”, which is the final predictive sales.

6 Reduce the “Linear Prediction” column to the “Sales” column calculation error, and then calculate the error rate in the “Sales” column.

7 Select the sales volume with the STDEV () function, calculate the overall standard deviation.

The significant horizontal parameter is 0.05, that is, the confidence is 95%; the sample amount is written 15.

Use the confidence () function to calculate the float of float of the faith section, the result is a screenshot.

That is, 95% of the likelihood predicted value is in the range of 195.92 than the true value.It can be seen that the errors are in the normal confidence interval.

8 Subject 16 (auxiliary column) into the formula, predict the sales of 2021q4.

Author: janie liu; public number: Áï notes