LEMONADE STAND, PART 4: MOVING AVERAGES AND WEIGHTED AVERAGES

/in /by

In Part 3 of our Lemonade Stand series, we demonstrated a technique for creating seasonal forecasts with FileMaker 12. In this part, we’re going to look at how to create moving and weighted averages.

What’s it mean?

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 irregularities that are otherwise fairly steady over time. For example, Skeleton Key records earnings on a weekly basis with a Sunday through Saturday billing period. This means monthly earnings can appear erratic since some months have four Saturdays and some have five. A moving average calculates the average value from a certain number of previous periods. For example, a forecast for July using a three-month moving average would be the average of April, May and June values. The number of periods in your moving average could be shorter or longer depending on the cycle of irregularities. The more periods you include, the smoother the average will be. A weighted average is similar to a moving average except that it applies weights to the periods being averaged. The weights are commonly chosen to consider the most recent data more heavily. This allows the weighted average to respond more quickly when a data series is trending up or down. However, we could also use a weighted average to compensate for the four vs five week months that are the result of our billing cycle. In other words, the weight for a period could be based on any attribute of the period. Let's look at a simple example based on the monthly expenses for our lemonade stand. Here's a chart that combines the actual expenses, a trend line, a three month moving average, and a three month weighted average.

Moving average

FileMaker's ExecuteSQL function makes it very easy to calculate a moving average. From the context of a particular period, we simply need to find the average of the preceding N periods. SQL's AVG aggregate function returns an average of a set of values. So, we simply need to construct a WHERE clause that will return the amount from the preceding N records. The following ExecuteSQL function calculates the average amount from the previous three periods. Notice that the query's WHERE clause needs to reference the current period twice ("period >= (? – 3) AND period < ?"). We pass the period as both the first and second argument to the query (the fourth and fifth parameters of the ExecuteSQL function).

Let( [ $avg_y = ExecuteSQL("SELECT AVG(amount) FROM expenses WHERE period >= (? – 3) AND period < ?";"";"";expenses::period; expenses::period) ]; Round( $avg_y; 2 ) )

Notice that the AVG function properly handles the first three periods where there are fewer than three preceding periods.

Weighted average

The weighted average requires a few more steps, but it's not difficult. Conceptually, we just need to find the values from the previous N periods and the corresponding weights for those periods. Then we do a little basic math with those numbers. In our example, we're finding a weighted average for the previous three periods. We chose to weight the most recent periods more heavily, so we'll use weights of 4 for the previous period, 2 for two periods ago, and 1 for three periods ago. In the following calculation, $prev_1 represents the expense amount from the previous period, $prev_2 represents the expense amount from two periods ago, and so on. In order to properly handle the first three periods for the chart, we need to only include weights for periods that had expenses. Otherwise, our weighted average would be skewed. We do this simply by selecting the weight as a constant for the previous period only when the amount has a value (IS NOT NULL). The three weights are represented by $weight_1, $weight_2, and $weight_3. The $weights variable is simply the total of the three weights. The $weighted_avg variable is where everything is combined together to actually calculate the weighted average for the period. We simply add up the product of each value and its corresponding weight and then divide that sum by the total of the weights. We use a case function to make sure we're not dividing by zero, which could happen for the first period in our chart.

Let( [ $prev_1 = ExecuteSQL("SELECT amount FROM expenses WHERE period = (? – 1)";"";"";expenses::period); $prev_2 = ExecuteSQL("SELECT amount FROM expenses WHERE period = (? – 2)";"";"";expenses::period); $prev_3 = ExecuteSQL("SELECT amount FROM expenses WHERE period = (? – 3)";"";"";expenses::period); $weight_1 = ExecuteSQL("SELECT 4 FROM expenses WHERE period = (? – 1) AND amount IS NOT NULL";"";"";expenses::period); $weight_2 = ExecuteSQL("SELECT 2 FROM expenses WHERE period = (? – 2) AND amount IS NOT NULL";"";"";expenses::period); $weight_3 = ExecuteSQL("SELECT 1 FROM expenses WHERE period = (? – 3) AND amount IS NOT NULL";"";"";expenses::period); $weights = $weight_1 + $weight_2 + $weight_3; $weighted_avg = Case($weights = 0; ""; ($prev_1 * $weight_1 + $prev_2 * $weight_2 + $prev_3 * $weight_3) / ($weights)) ]; Round( $weighted_avg; 2 ) )

Because of the weights we chose, you can see that the weighted average always tracks more closely to the previous value than the simple three-month moving average does. Your choice of weights could be anything based on an attribute of the period. You'd simply need to adjust the SQL queries accordingly. Also, note that if you choose the same value for each of your weights, the moving average and weighted average will be identical. It's important to note that when using any kind of average as a forecasting tool, the average value will never be higher than the largest value in the set and it will never be lower than the smallest value in the set. You'll want to consider this when working with data that has a strong upward or downward trend.

Coming soon

In the next part of our series, we're going to revisit our trend and seasonal forecast examples and look at a few techniques for optimizing the performance of those charts. 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.