# Setting the Initial Value for Single Exponential Smoothing and the Value of the Smoothing Constant for Forecasting Using Solver in Microsoft Excel

^{1}

^{2}

^{*}

## Abstract

**:**

## 1. Introduction

## 2. Theoretical Framework

#### 2.1. SES

#### 2.2. Setting the Initial Value for SES

- 1.
- The first observation is used as the initial value of α when using SES [22].By simple algebra, of SES, can also be expressed as${F}_{1}={y}_{1}$, ${F}_{t+1}=\alpha {y}_{t}+(1-\alpha ){F}_{t}$ where ${F}_{1}$ and ${y}_{1}$ are the initial value and the actual value, respectively. The choice is preferable when the level changes rapidly (α close to 1, or 1 − α close to 0).
- 2.
- The average of the first six observations is used for the initial value [23].By simple algebra of SES, can also be expressed as${F}_{1}=\left(\frac{1}{6}{y}_{1}+\frac{1}{6}{y}_{2}+\frac{1}{6}{y}_{3}+\frac{1}{6}{y}_{4}+\frac{1}{6}{y}_{5}+\frac{1}{6}{y}_{6}\right)$, ${F}_{t+1}=\alpha {y}_{t}+(1-\alpha ){F}_{t}$ where ${F}_{1}$ and ${y}_{1},{y}_{2},\dots ,{y}_{6}$ are the initial value and the actual values, respectively.

#### 2.3. Performance Metrics

## 3. The Proposed Method to Set the Initial Value for SES

_{1}) for ${y}_{1}$ than for ${y}_{2},\dots ,{y}_{6}$.

- 1.
- Let ${F}_{1}=\left(\frac{6}{21}{y}_{1}+\frac{5}{21}{y}_{2}+\frac{4}{21}{y}_{3}+\frac{3}{21}{y}_{4}+\frac{2}{21}{y}_{5}+\frac{1}{21}{y}_{6}\right),$ where $\sum _{t=1}^{6}{w}_{t}}=1$.
- 2.
- Use Solver in Microsoft Excel to find the optimal value for α while minimizing MSE.
- 3.
- Provide the initial forecasting value via ${F}_{t+1}=\alpha {y}_{t}+(1-\alpha ){F}_{t}$.
- 4.
- Calculate the MSE.

## 4. Experimental Study

- 1.
- Each time series dataset with a stationary pattern is performed for three initial values by applying methods 1 and 2, and the proposed method are as follows:Method 1: ${F}_{1}={y}_{1}$Method 2: ${F}_{1}=\left(\frac{1}{6}{y}_{1}+\frac{1}{6}{y}_{2}+\frac{1}{6}{y}_{3}+\frac{1}{6}{y}_{4}+\frac{1}{6}{y}_{5}+\frac{1}{6}{y}_{6}\right)$The proposed method: ${F}_{1}=\left(\frac{6}{21}{y}_{1}+\frac{5}{21}{y}_{2}+\frac{4}{21}{y}_{3}+\frac{3}{21}{y}_{4}+\frac{2}{21}{y}_{5}+\frac{1}{21}{y}_{6}\right)$.
- 2.
- For each initial value setting, use the solver in Excel to find the optimal value for α while minimizing the MSE. Hence, we obtained the optimal value for α and the MSE value of each initial value setting.
- 3.
- For each initial value setting, use a grid search with α by varying 0.001, 0.002, …, 1.00 and compute the MSE in each α. With these 1000 conditions, we searched the optimal value for α and obtained the lowest MSE.
- 4.
- The performance of the proposed method compared with the other two initialization methods of the SES method, considering the lowest MSE.

## 5. Results and Discussion

## 6. Conclusions and Remarks

## Author Contributions

## Funding

## Institutional Review Board Statement

## Informed Consent Statement

## Data Availability Statement

## Acknowledgments

## Conflicts of Interest

## References

- Hyndman, R.J.; Koehler, A.B.; Snyder, R.D.; Grose, S. A state space framework for automatic forecasting using exponential smoothing methods. Int. J. Forecast.
**2002**, 18, 439–454. [Google Scholar] [CrossRef][Green Version] - Chen, F.; Ryan, J.K.; Simchi-Levi, D. The impact of exponential smoothing forecasts on the bullwhip effect. Nav. Res. Logist.
**2000**, 47, 269–286. [Google Scholar] [CrossRef] - Hussain, A.; Rahman, M.; Alam Memon, J. Forecasting electricity consumption in Pakistan: The way forward. Energy Policy
**2016**, 90, 73–80. [Google Scholar] [CrossRef] - De Baets, S.; Harvey, N. Forecasting from time series subject to sporadic perturbations: Effectiveness of different types of forecasting support. Int. J. Forecast.
**2018**, 34, 163–180. [Google Scholar] [CrossRef] - Chukwulozie, O.P.; Chinagorom, N.E.; Chukwuneke, C.J.; Nnodim, C.T. The analysis of cigarette production using double exponential smoothing model. Acad. J. Sci.
**2017**, 7, 293–308. [Google Scholar] - Wongoutong, C. The effect on forecasting accuracy of the Holt-Winters Method when using the incorrect model on a non-stationary time series. Thail. Stat.
**2021**, 19, 565–582. [Google Scholar] - Xie, M.; Hong, G.Y.; Wohlin, C. A study of the exponential smoothing technique in software reliability growth prediction. Qual. Reliab. Eng. Int.
**1997**, 13, 347–353. [Google Scholar] [CrossRef] - Schroeder, R.G.; Goldstein, S.M.; Rungtusanatham, M.J. Operations Management in the Supply Chain: Decisions and Cases; McGraw-Hill/Irwin: Windsor, NJ, USA, 2013. [Google Scholar]
- Jacobs, F.R.; Chase, R.B. Operations and supply chain management. In The Core, 3rd ed.; Chapter 3; McGraw-Hill Higher Education: Columbus, OH, USA, 2013. [Google Scholar]
- Paul, S.K. Determination of exponential smoothing constant to minimize mean square error and mean absolute deviation. Glob. J. Res. Eng.
**2011**, 11, 31–34. [Google Scholar] - Bermúdez, J.D.; Segura, J.V.; Vercher, E. Improving demand forecasting accuracy using nonlinear programming software. J. Oper. Res. Soc.
**2006**, 57, 94–100. [Google Scholar] [CrossRef] - Chopra, S.; Meindl, P.; Kalra, D.V. Supply Chain Management: Strategy, Planning, and Operation; Pearson Education: London, UK, 2013; Volume 232. [Google Scholar]
- Nariman, N.A.; Hamdia, K.; Ramadan, A.M.; Sadaghian, H. Optimum Design of Flexural Strength and Stiffness for Reinforced Concrete Beams Using Machine Learning. Appl. Sci.
**2021**, 11, 8762. [Google Scholar] [CrossRef] - Ravinder, H.V. Determining The Optimal Values Of Exponential Smoothing Constants Does Solver Really Work? Am. J. Bus. Educ.
**2013**, 6, 347–360. [Google Scholar] [CrossRef] - Chopra, S.; Meindl, P. Supply Chain Management: Strategy, Planning, and Operation, 5th ed.; Prentice Hall: Hoboken, NJ, USA, 2013; p. 195, Chapter 7. [Google Scholar]
- Balakrishnan, N.; Render, B.; Stair, R. Managerial Decision Modeling with Spreadsheets, 3rd ed.; Bermudez, J.D., Segura, J.V., Velcher, E., Eds.; Prentice Hall: Hoboken, NJ, USA, 2013. [Google Scholar]
- Ledolter, J.; Abraham, B. Some comments on the initialization of exponential smoothing. J. Forecast.
**1984**, 3, 79–84. [Google Scholar] [CrossRef] - Brown, R.G. Statistical Forecasting for Inventory Control; McGraw-Hill: New York, NY, USA, 1959. [Google Scholar]
- Cohen, G.D. Bayesian adjustment of sales forecasts in multi-item inventory control systems. J. Ind. Eng.
**1966**, 17, 474. [Google Scholar] - Johnson, L.A.; Montgomery, D.C. Operations Research in Production Planning, Scheduling and Inventory Control. J. Oper. Res. Soc.
**1975**, 26, 568–569. [Google Scholar] - Taylor, J.W. Volatility forecasting with smooth transition exponential smoothing. Int. J. Forecast.
**2004**, 20, 273–286. [Google Scholar] [CrossRef] - Makridakis, S.; Wheelwright, S.C.; Hyndman, R.J. Forecasting Methods and Applications; John Wiley & Sons: Hoboken, NJ, USA, 2008. [Google Scholar]
- University of Duke. Averaging and Exponential Smoothing Models. 2017. Available online: https://people.duke.edu/~rnau/Decision411_2007/411avg.htm (accessed on 11 January 2023).
- Cadenas, E.; Jaramillo, O.A.; Rivera, W. Analysis and forecasting of wind velocity in chetumal, quintana roo, using the single exponential smoothing method. Renew Energy
**2010**, 35, 925–930. [Google Scholar] [CrossRef] - Rachmat, R.; Suhartono, S. Comparative Analysis of single exponential smoothing and Holt’s Method for quality of hospital services forecasting in general hospital. Bull. Comput. Sci. Electr. Eng.
**2020**, 1, 80–86. [Google Scholar] [CrossRef] - Wongoutong, C. Improvement of the Holt-Winters Multiplicative Method with a New Initial Value Settings Method. Thail. Stat.
**2021**, 19, 280–293. [Google Scholar] - Sudibyo, N.A.; Iswardani, A.; Septyanto, A.W.; Wicaksono, T.G. Prediksi inflasi di indonesia menggunakan metode moving average, single exponential smoothing dan double exponential smoothing. J. Lebesgue J. Ilm. Pendidik. Mat. Mat. Stat.
**2020**, 1, 123–129. [Google Scholar] - Mas-Machuca, M.; Sainz, M.; Martinez-Costa, C. A review of forecasting models for new products. Intang. Capital.
**2014**, 10, 1–25. [Google Scholar] [CrossRef][Green Version] - Brown, R.G. Smoothing, Forecasting and Prediction of Discrete Time Series; Prentice-Hall: Annapolis, MD, USA, 1962. [Google Scholar]
- Gardner, J.R.; Everette, S. Exponential smoothing: The state of the art—Part II. Int. J. Forecast.
**2006**, 22, 637–666. [Google Scholar] [CrossRef] - Hyndman, R.J.; Athanasopoulos, G. Forecasting: Principles and Practice; OTexts: Online Textbook, 8 May 2018. [Google Scholar]
- Clements, M.; Hendry, D. Forecasting Economic Time Series; Cambridge University Press: Cambridge, UK, 1998. [Google Scholar]
- Makridakis, S.; Hibon, M. The M3-Competition: Results, conclusions and implications. Int. J. Forecast.
**2000**, 16, 451–476. [Google Scholar] [CrossRef] - The R Project for Statistical Computing. 2019. Available online: https://www.r-project.org/ (accessed on 11 January 2023).

**Figure 1.**An example of the Solver results pane for the time series of S1 with initial value setting with Method 1.

**Figure 2.**A bar chart of the optimal values of α (alpha) using a step search or Solver from Microsoft Excel when setting the initial value using T1 (Method 1), T2 (Method 2), or P (the proposed method) for large, medium, and small time series datasets.

**Figure 3.**Scatter plots of the optimal value for 𝛼 obtained using the step search method and Solver from Microsoft Excel when setting the initial value using (

**a**) Method 1, (

**b**) Method 2, or (

**c**) the proposed method for all 15 time series datasets.

**Figure 4.**Plots of the actual and predicted values when setting the initial value using Method 1, Method 2, and the proposed method; for example, of small, medium, and large time series data with S1, S2, M1, M2, L1, and L2.

**Figure 5.**The lowest MSE using the step search method and Solver from Microsoft Excel to find the optimal value for α after setting the initial value using M1 (Method 1), M2 (Method 2), and P (the proposed method) for large (L1–L5), medium (M1–M5), and small (S1–S5) time series datasets.

**Figure 6.**The frequencies of achieving the lowest MSE when setting the initial value using Method 1, Method 2, and the proposed method.

**Figure 7.**MSE of the three initial value setting methods for 1000 sets of conditions of S1, M1, and L1.

**Figure 8.**Average accuracy values with MSE for the three initial value setting methods using values of α ranging from 0 to 1 (0.001, 0.002, …, and 1) for the 15 datasets: small (S1–S5), medium (M1–M5), and large (L1–L5).

t | ${\mathit{y}}_{\mathit{t}}$ | ${\mathit{w}}_{\mathit{t}}$ |
---|---|---|

1 | ${y}_{1}$ | 6/21 |

2 | ${y}_{2}$ | 5/21 |

3 | ${y}_{3}$ | 4/21 |

4 | ${y}_{4}$ | 3/21 |

5 | ${y}_{5}$ | 2/21 |

6 | ${y}_{6}$ | 1/21 |

Dataset | M3 Competition Code | Time Period | Size | |
---|---|---|---|---|

Small | S1 | N243 (Y) | 1947–1992 | 46 |

S2 | N127 (Y) | 1947–1993 | 47 | |

S3 | N180 (Y) | 1947–1993 | 47 | |

S4 | N229 (Y) | 1947–1993 | 47 | |

S5 | N721 (Q) | 1984Q1–1994Q4 | 44 | |

Medium | M1 | N1368 (Q) | 1974Q2–1991Q3 | 70 |

M2 | N1449 (Q) | 1990Q1–2007Q1 | 69 | |

M3 | N1470 (Q) | 1990Q1–2007Q1 | 69 | |

M4 | N1472 (Q) | 1990Q1–2007Q1 | 69 | |

M5 | N903 (Q) | 1976Q1–1993Q4 | 72 | |

Large | L1 | N2125 (M) | 1978M1–1989M12 | 144 |

L2 | N1886 (M) | 1979M1–1990M12 | 144 | |

L3 | N2022 (M) | 1981M1–1992M12 | 144 | |

L4 | N2025 (M) | 1981M1–1992M12 | 144 | |

L5 | N2070 (M) | 1982M1–1993M12 | 144 |

Size | Dataset | Method 1 | Method 2 | Proposed | |||
---|---|---|---|---|---|---|---|

Step | Solver | Step | Solver | Step | Solver | ||

Small | S1 | 0.369 | 0.3689 | 0.316 | 0.3162 | 0.323 | 0.3232 |

S2 | 0.349 | 0.3486 | 0.298 | 0.2982 | 0.301 | 0.3013 | |

S3 | 0.683 | 0.6828 | 0.546 | 0.5459 | 0.555 | 0.5383 | |

S4 | 0.797 | 0.7970 | 0.798 | 0.7975 | 0.793 | 0.7932 | |

S5 | 0.261 | 0.2608 | 0.213 | 0.2129 | 0.216 | 0.2161 | |

Medium | M1 | 0.248 | 0.2477 | 0.228 | 0.2281 | 0.230 | 0.2301 |

M2 | 0.244 | 0.2437 | 0.062 | 0.0621 | 0.088 | 0.0884 | |

M3 | 0.098 | 0.0982 | 0.068 | 0.0677 | 0.042 | 0.0420 | |

M4 | 0.237 | 0.2368 | 0.051 | 0.0507 | 0.064 | 0.0642 | |

M5 | 0.705 | 0.7049 | 0.705 | 0.7048 | 0.704 | 0.7045 | |

Large | L1 | 0.218 | 0.2177 | 0.135 | 0.1348 | 0.143 | 0.1433 |

L2 | 0.148 | 0.1481 | 0.148 | 0.1478 | 0.145 | 0.1447 | |

L3 | 0.126 | 0.1262 | 0.126 | 0.1257 | 0.122 | 0.1221 | |

L4 | 0.224 | 0.2238 | 0.22 | 0.2195 | 0.219 | 0.2191 | |

L5 | 0.181 | 0.1809 | 0.141 | 0.1415 | 0.145 | 0.1449 |

**Table 4.**The lowest MSE of three initial value setting methods using the optimal value for α from step search and Solver from Microsoft Excel.

Size | Dataset | Method 1 | Method 2 | Proposed | |||
---|---|---|---|---|---|---|---|

Step | Solver | Step | Solver | Step | Solver | ||

Small | S1 | 896,212.77 | 896,212.76 | 886,047.38 | 886,047.36 | 885,070.96 | 885,070.92 |

S2 | 1,450,217.80 | 1,450,217.50 | 1,419,280.76 | 1,419,280.72 | 1,418,605.85 | 1,418,605.71 | |

S3 | 82,963.99 | 82,963.98 | 82,284.51 | 82,284.51 | 81,506.58 | 81,505.96 | |

S4 | 393,051.81 | 393,051.81 | 393,111.26 | 393,111.21 | 392,958.71 | 392,958.71 | |

S5 | 121,187.43 | 121,187.42 | 116,543.72 | 116,543.71 | 116,648.83 | 116,648.82 | |

Medium | M1 | 29,069.60 | 29,069.60 | 28,837.52 | 28,837.52 | 28,735.94 | 28,735.94 |

M2 | 934,245.09 | 934,244.89 | 756,669.78 | 756,669.75 | 765,244.84 | 765,244.02 | |

M3 | 1,649,100.03 | 1,649,099.38 | 1,599,938.85 | 1,599,937.30 | 1,571,685.18 | 1,571,685.15 | |

M4 | 519,353.65 | 519,353.61 | 424,846.72 | 424,846.34 | 434,269.59 | 434,269.50 | |

M5 | 165,837.93 | 165,837.93 | 165,830.64 | 165,830.63 | 165,816.74 | 165,816.72 | |

Large | L1 | 1,516,519.54 | 1,516,519.40 | 1,479,599.21 | 1,479,599.13 | 1,470,586.75 | 1,470,586.56 |

L2 | 1,880,659.84 | 1,880,659.83 | 1,880,401.03 | 1,880,400.76 | 1,877,245.36 | 1,877,244.59 | |

L3 | 555,763.08 | 555,762.96 | 555,644.55 | 555,644.38 | 554,925.61 | 554,925.58 | |

L4 | 28,361.93 | 28,361.93 | 28,278.23 | 28,278.21 | 28,270.66 | 28,270.66 | |

L5 | 53,326.57 | 53,326.57 | 50,839.83 | 50,839.76 | 50,986.27 | 50,986.27 |

**Table 5.**Chi-squared goodness-of-fit test results for comparing the lowest MSE value frequencies of the three initial value setting methods.

Size | Dataset | Method 1 | Method 2 | Proposed | Chi-Squared Value | p-Value |
---|---|---|---|---|---|---|

Small | S1 | 425 | 145 | 430 | 159.65 | <0.0001 |

S2 | 300 | 97 | 603 | 389.05 | <0.0001 | |

S3 | 316 | 264 | 420 | 37.86 | <0.0001 | |

S4 | 155 | 0 | 845 | 1214.15 | <0.0001 | |

S5 | 248 | 223 | 529 | 173.22 | <0.0001 | |

Medium | M1 | 285 | 218 | 497 | 127.27 | <0.0001 |

M2 | 348 | 172 | 480 | 143.26 | <0.0001 | |

M3 | 146 | 295 | 559 | 262.47 | <0.0001 | |

M4 | 361 | 234 | 405 | 47.31 | <0.0001 | |

M5 | 363 | 146 | 491 | 182.50 | <0.0001 | |

Large | L1 | 449 | 165 | 386 | 133.47 | <0.0001 |

L2 | 4 | 56 | 940 | 1660.26 | <0.0001 | |

L3 | 66 | 273 | 661 | 547.42 | <0.0001 | |

L4 | 78 | 127 | 795 | 962.71 | <0.0001 | |

L5 | 302 | 221 | 447 | 102.722 | <0.0001 | |

Average | 256.40 | 175.73 | 565.87 |

**Table 6.**The average MSE values for the three initial value setting methods using predetermined α values ranging from 0 to 1 (0.001, 0.002, …, and 1).

Size | Dataset | Method 1 | Method 2 | Proposed |
---|---|---|---|---|

Small | S1 | 984,214.12 | 969,740.55 | 967,322.56 * |

S2 | 1,594,382.33 | 1,569,643.61 | 1,564,879.67 * | |

S3 | 122,698.44 | 105,228.68 | 104,818.88 * | |

S4 | 460,672.15 | 461,335.23 | 456,778.22 * | |

S5 | 146,968.36 | 144,174.95 | 143,896.02 * | |

Medium | M1 | 31,757.83 | 31,338.43 | 30,365.02 * |

M2 | 1,204,852.71 | 1,064,595.04 | 1,062,339.88 * | |

M3 | 2,188,507.09 | 2,166,421.78 | 2,160,496.8 * | |

M4 | 634,169.22 | 584,823.33 | 582,716.40 * | |

M5 | 181,293.72 | 181,455.69 | 178,257.81 * | |

Large | L1 | 1,639,370.09 | 1,619,541.12 | 1,616,325.69 * |

L2 | 2,371,310.64 | 2,371,031.69 | 2,367,682.30 * | |

L3 | 707,504.85 | 707,387.44 | 706,683.96 * | |

L4 | 36,068.72 | 35,952.54 | 35,942.25 * | |

L5 | 69,863.02 | 69,555.56 | 69,277.16 * |

Disclaimer/Publisher’s Note: The statements, opinions and data contained in all publications are solely those of the individual author(s) and contributor(s) and not of MDPI and/or the editor(s). MDPI and/or the editor(s) disclaim responsibility for any injury to people or property resulting from any ideas, methods, instructions or products referred to in the content. |

© 2023 by the authors. Licensee MDPI, Basel, Switzerland. This article is an open access article distributed under the terms and conditions of the Creative Commons Attribution (CC BY) license (https://creativecommons.org/licenses/by/4.0/).

## Share and Cite

**MDPI and ACS Style**

Junthopas, W.; Wongoutong, C. Setting the Initial Value for Single Exponential Smoothing and the Value of the Smoothing Constant for Forecasting Using Solver in Microsoft Excel. *Appl. Sci.* **2023**, *13*, 4328.
https://doi.org/10.3390/app13074328

**AMA Style**

Junthopas W, Wongoutong C. Setting the Initial Value for Single Exponential Smoothing and the Value of the Smoothing Constant for Forecasting Using Solver in Microsoft Excel. *Applied Sciences*. 2023; 13(7):4328.
https://doi.org/10.3390/app13074328

**Chicago/Turabian Style**

Junthopas, Wannaporn, and Chantha Wongoutong. 2023. "Setting the Initial Value for Single Exponential Smoothing and the Value of the Smoothing Constant for Forecasting Using Solver in Microsoft Excel" *Applied Sciences* 13, no. 7: 4328.
https://doi.org/10.3390/app13074328