DESIGN AND DEVELOPMENT OF SPREADSHEET FOR FERTILIZER DEMAND FORECASTING: A CASE STUDY OF FERTILIZER STORE IN SUKHOTHAI PROVINCE

Abstract

    This research aimed to develop the forecasting application for demand forecasting in ordering chemical fertilizer with Visual Basic for Applications (VBA) at ABC fertilizer store, a case study in Sukhothai province. For the application performance testing, verification, and validation, it used three different forecasting methods, including moving average (MA), single exponential smoothing (SES), and regression analysis (RA). For testing the data, it used five different instances of the fertilizer’s demand, obtained from the case study store, including Sing-Bin 36-0-0, Sing-Bin 46-0-0, Sing-Bin 20-8-8, Mah-bin 46-0-0, and Kratai-bin 16-20-0.

From the application’s performance testing, verification, and validation, it was able to correctly predict all forecasting methods. The regression analysis (RA) had the minimum mean absolute percentage error (MAPE) in forecasting the data set of fertilizers: Sing-Bin 36-0-0; Sing-Bin 46-0-0; and Sing-Bin 20-8-8. To minimize the forecasting error, the case study used Mah-bin 46-0-0 and Kratai-bin 16-20-0, and the appropriate forecasting method should use moving average (MA) and single exponential smoothing (SES). After forecasting each data set with appropriate methods and comparing it with the actual demand, the forecast error was less than 14.55% for all datasets.

Incidentally, in this research, we developed an application for fertilizer demand forecasting. For further study, the flexible data importation should be adaptable to contribute to other forecasting types. Other points of application development may be adding other forecasting methods as well as data distribution analysis functions, which could lead to the appropriate forecasting method and effective forecasting assessment.