Using Excel for Markov Process

 Please use the following Notes

Using Markov Process in Fashion Industry

 The Following is a brief introduction to the Markov Processes and how they can be used in the fashion industry. 

Forecasting in Fashion: Guess the Sarees Sold in 25th Week !!!

Forecasting is the application of Predictive Analytics. 

Wherever we have a forecasting problem we would be using a time series data.

1. Time Series data is a data on a response variable, Yt observed at different time points t.  

   1. Yt eg. Sales 

2. Time series Data has the following Components 

   1. Trend Component (Tt) 

      1. Trend is the consistent long-term upward or downward movement of data over a period of time 

   2. Seasonal Component ( St) 

      1. Seasonal Component is the repetitive upwards or downward movement from the trend that occurs within a calendar year.  

         1. Festival, School Holidays, EOSS 

         2. Constant periodicity 

   3. Cyclical Component ( Ct) 

      1. Fluctuation around trend line that happens due to macro economic changes such as recession, unemployment 

         1. They have repetition of more than a year.  

         2. Non Constant Periodicity 

   4. Irregular Component ( J)  

      1. It is the white noise or random uncorrelated changes  

      2. Follow a Normal distribution 

      3. Mean value 0 and constant Variance 

Seasonal Component

Trending Component

Irregular Component

Forecasting Accuracy

Notes: Mean squared Error depicts how much wrong we are in our forecasting. This is given by:

 

Where n is the number of observations. Yi is the Observed Value Y(cap) i is the predicted value 

Ideally it should be 0, else it should be as low as possible. 

 Case 

SareeGhar is a company that sells silk sarees. Ragini is the category manager of sarees. She has data of 24 weeks of saree sales as given below:

1. Plot a line graph of number of sarees sold in first 24 weeks. 

2. Make the y -axis start from 25 to 45, and x-axis from 0-25. the graph should look like:

3. Change the y-axis units to 1 week instead of 5 weeks. 

4. As you can see the peaks are appearing it at 2 weeks, draw a 2-day MA on the graph, is it mirroring the original curve ? What is the Mean Squared Error, can you make it lower. 

5. Now you draw a 4 day Moving average on the graph. Is it better ? What is the MSE you are getting. Can you still do it better ? You should get the following chart

WEIGHTED MOVING AVERAGE

6. Lets add some weights to it. Set up the Sheet to take the weighted average. The sheet should look like

7. Find the optimum weights using Solver. The sum of the weights should be equal to 1. The aim is to minimize the MSE. do it for both 2 day average and 4 day average. 

The most important decision in MA is the number of periods (k): 

1. If k is too small, then the average tens to be more responsive to the recent trends. 

2. If k is large then the forecast is less responsive to recent changes. 

Here value are even, we find the rep value in 2 steps, 

For odd values, there is no issue.  

So moment I get the 5th week data I use it in place of 1st week data. 

It is different from moving average forecast.  

In this case we are doing the MA, for the purpose of finding the rep average for the period 

For 3 period, it is easier, I take an average of Y1, Y2, Y3 and place it against Y2 

8. Forecast it using the Singe Exponential Smoothing test with alpha of 0.1 and 0.9

SINGLE EXPONENTIAL SMOOTHING

We use it for data which is fairly steady with time, with no significant trends- seasonal or cyclic components. 

Here the weights are assigned to the past data, that decline exponentially and more recent observations are assigned higher weights. 

It uses whole of the historic data, unlike MA where only past few observations are used.  

 

Disadvantage 

Increasing alpha makes forecast less sensitive to the data 

It Always lags behind trend as it is based on past observations.  

Forecast Bias and systematic errors occur when the observations exhibit strong trend or seasonal patterns.  

 

Choosing alpha 

 

1. When the data is smooth, we choose higher value of alpha 

2. When the data is highly fluctuating, we choose lower value of alpha 

3. We can use Solver to choose alpha with Constraints 0<alpha<1 

8. Forecast it using the Singe Exponential Smoothing test with alpha of 0.1 and 0.9. Plot it with the original data. Calculate the MSE. The graph should look like:

9. Forecast it by finding the optimum value of alpha by using solver

10. Plot the graph, it should look like:

Why Smoothing:  

 

Smoothing is usually done to help us better see patterns, trends for example, in time series. Generally smooth out the irregular roughness to see a clearer signal. For seasonal data, we might smooth out the seasonality so that we can identify the trend. Smoothing doesn’t provide us with a model, but it can be a good first step in describing various components of the series. 

DOUBLE EXPOENENTIAL SMOOTHING- HOLT's METHOD

One of the drawbacks of single exponential smoothing is that the model does not do well in the presence of trend. This can be improved by introducing an additional equation for capturing the trend in the time series data. 

Suppose we are given this data about the sale of a merchandise. We would like to forecast the sales of 49,50 and 51st week. 

The first step is to set up the columns, so we write

The first step is to initialize the level and trend for week 37. So we take Level = 321 ( same as actual sale) and Trend as 0. The forecast value is the sum of these values. let us say Alpha is 0.5 and Beta is 0.5

For week 38 level value is given by the formula

So we write that

For Week 38 Trend is given by the formula

So we write that

Now we take the forecast value by the formula

We copy it down and calculate the MAPE

Now we fire up solver and minimize the value of MAPE by changing the value of alpha and beta, with the constraint that value of alpha and Beta is between 0 and 1.

We get the following solution

Now to forecast for 49th , 50yh and 51st Month, we use the formula

So we write the periods as 1, 2 and 3 and apply the formula

The data depicts revenue of sarees across years.

The first step is to plot a time plot.

We see there are seasonal variations but these are of equal width, so additive model would be better. 

So first we set up columns like this:

We take value of 0.1 for all the three smoothing constants alpha, beta and gamma

Now if we look at the graph, the values are after every month, but patterns repeat after every year, so we take the seasonality as 12. 

So we need 12 seasonality factors for initialization. So for that we leave out 12 period of level and trend.

So we calculate the seasonality of jan10 as the actual value of y minus the average of entire year.

So what we are doing here is the difference of the first value over the average, we are attributing to the seasonality.

We copy the formula to the first 12 seasons. As there is no forecast for those. Just copy blank in the forecast as well.

For Dec10, we simply take trend value as 0 and Level value as difference of Dec 10 actual value and Dec 10 seasonality. 

Now from Jan 11 onwards, we start using our formulas. Lets do the level first. We write this formula and copy over. 

Now for Trend we go with this formula

We Copy it over. Now For seasonality

We copy it over. Now forecast is also simple, it is the sum of all the previous  level and trend and the previous same season

We copy it over. 

Then we calculate MAPE 

Then we calculate the average MAPE

We get 0.02 as the MAPE value. Now we fireup solver such that

MAPE: Minimize

Changing Values of : Alpha, Beta and Gamma

Constraints: Alpha beta and gamma are between 0 and 1 inclusive

We have found a solution and MAPE is reduced to 1%

Lets forecast for a year

So we write 1 to 12 in column G

Jan 19 we can simply copy the last forecast

For Feb 19,

We use the Dec 19 Level forecast  + The order of forecast ( which is 2 written on the side) + previous value in the corresponding season.

Lets see the graph now. It is looking to follow the pattern. 

Holt Winter's Multiplicative Model

 Again working on the same data as that of additive model,

We Change the seasonality formula to this, and copy to the first 12 months. 

You can see that seasonality in this case is either above 1 or below 1 indicating peaks and valleys. 

We change the Level formula from Jan 11 as follows:

Instead of subtracting, we divide and copy along.

There is no change in the trend element. In seasonality element, instead of subtracting, we divide

Now Forecasting also we need to make an adjustment as follows

Replicate forecast only till Jan 2019. 

We change the formula of Feb 2019 as follows

Now lets draw plot first then we will optimize

The MAPE we are getting is 0.026.

Now lets power on the solver and optmize the value of alpha, beta and gamma

The MAPE is coming out to be 0.01. The value of alpha, beta and gamma is coming out to be:

Power of a forecasting Model: Theil's Coefficient 

 

1. It is a comparison between Naïve forecasting model and the model developed. 

2. In the Naïve forecasting model, the forecasted value for the next period is the same as the last period's actual value.

It is basically the ratio of : MSE of the forecasted model/ MSE of the Naive Model 

1. A value of U<1 indicates the forecasting model is better than the Naïve forecasting model  

2. At U=0, the model is perfect 

3. A value of U>1 indicates that the forecasting model is no better than the Naive model 

 

10. What is the Power of the forecasting in the last excel sheet. What is the U coefficient that we got. Interpret it. 

A value of 0.54 of U coefficient is good as it is twice better than 1. 

Case I: Seasonal Variation

Before we introduce the seasonal variation, let me introduce you with one more metrics to measure the effectiveness of forecasting. This is called MAPE ( Mean Absolute Percentage Error). It is calculated by

It is one of the popular forecast accuracy measure. Since it is dimensionless, we can use it to compare different models with varying scales.  

Lets consider this data:

We need to forecast how many sarees got sold. Graphing it we should get a pattern like this. 

It is showing me a seasonality pattern of 4 weeks. Lets take a 4 week moving average and calculate. and draw graph.

Pink line is the forecasted line, it is very bad, as it is not capturing seasonality. The MAPE is coming out to be 

19%

 To capture seasonality, we introduce seasonality index.  

We need to capture the seasonal Component into our forecasting. How to do it: 

We Create the 4-week MA as above:

Then we center the MA ( We need to do it for even numbers eg. 4 week, we don’t need to in case of odd no of intervals) 

Now we fill up the Ratios ( S*R Component of Sales Value)= No of sarees sold/ Centered MA 

From there we calculate the Seasonality index.  

 

Seasonality Index  for 1st week of the 4 week cycle= SR component  ( 1st + 5th+9th+13th+…)/ count  of values 

Seasonality Index  for 2nd week of the 4 week cycle= SR component  ( 2nt + 6th+10th+12th+…)/ count  of values 

…. And so on.  

Remember the Average of the Index should be 1. If not 1 we divide each value by the Average SI obtained 

 

Now we use seasonality index to decentralize the data 

 

So Decentralised Sales = Actual Sales/ Seasonality Index 

Lets look at the plot now

 Once I have done that. I need to adjust it back by multiplying it with the  seasonal factor 

Now we make a new Table

Once I have done that. I need to adjust it back by multiplying it with the  seasonal factor 

The MAP when calculated comes out to be 5% instead of 19%. 

It is closely mirroring. It seems that we have done well.

Similarly If you have Trend 

 

Moving average will capture the trend to some extent 

In that case we could de-trend and deseasonalize 

 

In that case you should de-trend and de-seasonalize.  

 

In an exponentially growing organization, capture the trend using regression.  

Case I: Capturing Trend ( Regression)

In this case , we are saying that each of the weeks are different. So time is a variable. We can take base variation as week 1. 

So we set up the table as:

Firing up the regression, we get

Week doesn't turn to be significant, so you can exclude that. 

Interpreting: 52 is the average sale of the first week.

Wk 2 sale is 30.9 more than that of first week 

Wk 3 sales are 20 more than that of week 1

Wk 4 sales are 20.2 more than that of week 1

Lets exclude week and running regression again. 

So first wk: 53, 2nd week, 53+31, 3rd WK= 53+38.66, 4th Week= 53+20.5 

MAPE Turns out to be 4.04%, over there it was 5%. So regression gives you better estimate.  

Case 1: Capturing Trend as Well As Seasonality

Look at the Sales Data 3

plotting the graph we get:

It contains both trend and seasonality. Here the yellow line shows 4 weeks of Moving Average. 

So first we:

1. We Deseasonlize the sales. 

2. Do the trend forecast

3. Reseasonalize the sales. 

After deseasonalization, we get the following curve:

Pink line is deseasonlised sales but it is still showing the trend.  

I take the deaseaonised sales and do regeression to detrend the sales and put it back. 

To capture the trend, rather than using regression we, can use a function called TREND, and complete it using Shift ctrl enter

If we draw the chart now, it follows the values and hence we are able to forecast.