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 of previous thirty-six months from my accounts department. The purpose of this project is to make a spreadsheet and forecast the sales for next twelve months. Here is the first graphical representation of previous three years sales data.
The time series data of sales represents the seasonal effect. The visual inspection is providing the information that, sales from October to March are the higher and 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 the Microsoft Excel’s data analysis tool pack. I used all these dummies in the data for regression analysis. Each dummy is representing the month and, the December remained as default. The first twelve periods of the total thirty-six observations are in the table.
| Dummy Variables | ||||||||||||
| Months | Jan | Feb | Mar | Apr | May | Jun | jul | Aug | Sep | Oct | Nov | Sales |
| 1 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 271 |
| 2 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 259 |
| 3 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 216 |
| 4 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 192 |
| 5 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 189 |
| 6 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 170 |
| 7 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 199 |
| 8 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 211 |
| 9 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 234 |
| 10 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 250 |
| 11 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 257 |
| 12 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 287 |
After creation of dummies, I used the excel tool pack to find out the regression model which shows the seasonal effect. By applying that models 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. And the results of this regression are as follows.
The regression model which minimised 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 regressions results from excel, it is clear from the statistics that without the trend the MSE is higher but in case of trends and seasonal effect 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 them about the seasonal effect on sales. The winter is suitable for more sales, but summer is relatively showing the decrease in sales. I will also work with them for identification of more factors affecting the 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
