LEMONADE STAND, PART 3: SEASONAL FORECASTS

/in /by

In Part 1 and Part 2 of our Lemonade Stand series, we demonstrated how to add a trend line to a chart in FileMaker 12. In this part, we’re going to look at how to create a seasonal index from a set of historical data and then use that index to create a forecast based on both the trend and seasonality.

Calculating the seasonal index

To find the seasonal index for a given month we simply divide the average historical sales for the month by the average monthly sales for all months. The period for a seasonal index does not have to be a month. It could be a quarter, a week, a day of the week, or even an hour of the day. The same technique applies. Let’s look at an example for our lemonade stand. The following chart shows the January sales for each of the past three years. Using this data, we can calculate the average January sales as (4+6+8)/3 = 6. If the average monthly sales for the past three years is 12.31 then our seasonal index for January is 6/12.31 = .4874. What this means is that, historically speaking, our January sales have been just 49% of average monthly sales. For another example, let’s look at August sales. Our lemonade stand sales for August for the past three years have been 16, 23, and 31. That gives us an average of (16+23+31)/3 = 23.33. Dividing that average by the average monthly sales (12.31) gives a seasonal index for August of 23.33/12.31 = 1.895. The August heat pays off for our lemonade stand with sales that are nearly 190% of average monthly sales. We can chart the seasonal index for each month by starting with a found set of records for any year. Here’s the calculation for the series in the line chart:

Let( [ avg_y = ExecuteSQL(“SELECT AVG(sales) FROM season_example WHERE sales IS NOT NULL AND MONTH(period) = ?”;””;””; Month(season_example::period)); avg_all = ExecuteSQL(“SELECT AVG(sales) FROM season_example WHERE sales IS NOT NULL”;””;””); season_index = avg_y / avg_all ]; season_index )

Notice that the SQL AVG aggregate function makes it easy to calculate averages for all of the known (IS NOT NULL) sales values. Also notice the use of SQL’s MONTH function to select records where the month of the period matches the month of the current record’s period, which is passed into the first SQL statement as a parameter. Here’s a chart of the seasonal index for a full year. In our example, we’re basing the seasonal index on all of the historical sales data we have. In some cases, it may make more sense to limit the index to just the past few cycles (a cycle is a year in our example). Or, we could use a weighted average to give more weight to more recent years when we calculate the index. We’ll look more at weighted averages in a future article.

Seasonal forecasts

Now that we know how to calculate the seasonal index for each period, it’s time to combine the index with the trend line to generate a seasonal forecast. The following calculation includes all of the variables needed to calculate both the trend line and the seasonal index. The final result simply multiples the trend line value by the seasonal index and rounds the result.

Let( [ $n = ExecuteSQL(“SELECT COUNT(sales) FROM season_example”;””;””); $sum_xy = ExecuteSQL(“SELECT SUM((period – DATE ‘0001-01-01’ + 1)*sales) FROM season_example WHERE sales IS NOT NULL”;””;””); $sum_x = ExecuteSQL(“SELECT SUM((period – DATE ‘0001-01-01’ + 1)) FROM season_example WHERE sales IS NOT NULL”;””;””); $sum_y = ExecuteSQL(“SELECT SUM(sales) FROM season_example WHERE sales IS NOT NULL”;””;””); $sum_xx = ExecuteSQL(“SELECT SUM((period – DATE ‘0001-01-01’ + 1)**2) FROM season_example WHERE sales IS NOT NULL”;””;””); $avg_y = ExecuteSQL(“SELECT AVG(sales) FROM season_example WHERE sales IS NOT NULL”;””;””); $avg_x = ExecuteSQL(“SELECT AVG((period – DATE ‘0001-01-01’ + 1)) FROM season_example WHERE sales IS NOT NULL”;””;””); avg_y_month = ExecuteSQL(“SELECT AVG(sales) FROM season_example WHERE sales IS NOT NULL AND MONTH(period) = ?”;””;””; Month(season_example::period)); season_index = avg_y_month / $avg_y; B = ($sum_xy – ($n* $avg_x * $avg_y)) / ($sum_xx – $n * $avg_x^2); A = $avg_y – (B * $avg_x); Y = A + (B * GetAsNumber(season_example::period)) ]; Round( Y * season_index; 2 ) )

This calculation was used to generate the 2013 Forecast series shown in the following image:

What’s next?

Moving averages and weighted averages are two common methods that can be used to smooth fluctuations in a data series. These techniques are often applied to sets of data with short-term irregularties that are otherwise fairly steady over time. In Part 4, we’ll take a closer look at moving averages and weighted averages. Here’s a chart from one of the examples we’ll be looking at: We’re working on an example file that covers all of the techniques for this series. If you’d like to get a copy of the example file or if you’ve found this article useful and would like to see the series continue, please let us know at info@skeletonkey.com. Greg Lane is VP of Application Development at Skeleton Key and a FileMaker Certified Developer. About Skeleton Key Skeleton Key is an accomplished team of technology consultants who solve business problems. We specialize in the rapid development of custom applications, integrating Macs and PCs in the professional workplace, and providing personalized training. Despite our end-to-end technical skills, we are consultant first and technologist second. We know that you don’t just need technology. You need to know that the technology you choose to deploy will provide the results you desire. Skeleton Key is a Platinum Level FileMaker Business Alliance company, an Authorized FileMaker Trainer, a member of the Apple Consultants Network and a Microsoft Registered Partner.