Sales Forecasting At “James & Sara Dairy Farm”

In this project, there is a detailed analysis of monthly data based on the previous sales of my family’s dairy farm. It is known as James & Sara dairy farm. I have collected data for the previous thirty-six months from my accounts department. The purpose of this project is to make a spreadsheet and forecast the sales for the next twelve months. Here is the first graphical representation of the previous three years’ sales data.

The time series data of sales represents the seasonal effect. The visual inspection provides the information that sales from October to March are higher and the remaining period is relatively lower.

For the further analysis, I tried to make the dummy variables for the statistical review. All this came to my mind after searching some literature on the sales forecast. Many of the studies suggested the creation of dummies for analysis (Ronald Eastman et al. 2009). I made all these dummies by using Microsoft Excel’s data analysis tool pack. I used all these dummies in the data for regression analysis. Each dummy represents the month and, the December remained as default. The first twelve periods of the total thirty-six observations are in the table.

After the creation of dummies, I used the Excel tool pack to find out the regression model, which shows the seasonal effect. By applying that model, here are the results of regression analysis.

The standard model which can minimise the MSE in time series can be written as.

Sale = 279.666 + 3.3333 Jan -15.667 Feb – 40.67 Mar – 65 Apr – 97 May – 131.33 Jun- 136.67 July- 109.33 Aug- 77.667 Sep – 43.67 Oct- 22.33 Nov

After the simple seasonal analysis, I tried to check the seasonal trend analysis by adding the labels of “Months” to successive periods. The results of this regression are as follows.

The regression model which minimized the MSE is as follows.

Sale = 286.33+1.05444₊ 24.3333 Jan +22.333 Feb + 24 Mar + 5 Apr – 30.33 May – 64.667 Jun- 43.333 July- 68.33 Aug- 51.333 Sep – 33.667 Oct- 16.333 Nov

After comparing both regression results from Excel, it is clear from the statistics that without the trend, the MSE is higher, but in the case of trends and seasonal effects, the MSE is relatively lower. So I shall inform my family about the overall sales.

Based on the seasonal and trend analysis here are the sales forecasts for the period thirty-seven to forty-eight (4th year). I shall explain to my family about the steady increase in the sales of dairy products.

By showing all this to my family, I can tell them about their monthly sales for the coming year. I’ll explain to them how it works and how they can forecast their sales after each month. Moreover, I’ll also explain to them about the seasonal effect on sales. The winter is suitable for more sales, but summer is relatively showing a decrease in sales. I will also work with them to identify more factors affecting sales.

Work Cited

J. Ronald Eastman, Florencia Sangermano, Bardan Ghimire, Honglei Zhu, Hao Chen, Neeti Neeti, Yongming Cai, Elia A. Machado & Stefano C. Crema (2009) Seasonal trend analysis of image time series, International Journal of Remote Sensing, 30:10, 2721-2726, DOI: 10.1080/01431160902755338