LEMONADE STAND, PART 3: SEASONAL FORECASTS

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.

LEMONADE STAND, PART 2: MORE FORECASTING AND TREND LINES

In Part 1 of our Lemonade Stand series, we demonstrated how to add a trend line to a chart in FileMaker 12. In this part, we’ll look at extending that technique to use a date field rather than a number field.

Let’s take a look at the final chart and then we’ll dig into the calculations used to create the chart. This chart was generated from the following data in the sales_date table. The only difference from the chart we produced in Part 1 is that period is defined as a date field.

Converting dates to numbers in SQL

In order to use a date for the period, we need to convert the date to a number in each of our SQL queries. In a FileMaker calculation, we could simply use the GetAsNumber() function to convert a date to a number. To get the same result within a SQL query, we can subtract a date from our period value as follows:

period – DATE '0001-01-01' + 1

Here’s the new FileMaker calculation for the Trend series in our chart:

Let( [ n = ExecuteSQL("SELECT COUNT(sales) FROM sales_date";"";""); sum_xy = ExecuteSQL("SELECT SUM((period – DATE '0001-01-01' + 1)*sales) FROM sales_date WHERE sales IS NOT NULL";"";""); sum_x = ExecuteSQL("SELECT SUM((period – DATE '0001-01-01' + 1)) FROM sales_date WHERE sales IS NOT NULL";"";""); sum_y = ExecuteSQL("SELECT SUM(sales) FROM sales_date WHERE sales IS NOT NULL";"";""); sum_xx = ExecuteSQL("SELECT SUM((period – DATE '0001-01-01' + 1)**2) FROM sales_date WHERE sales IS NOT NULL";"";""); avg_y = ExecuteSQL("SELECT AVG(sales) FROM sales_date WHERE sales IS NOT NULL";"";""); avg_x = ExecuteSQL("SELECT AVG((period – DATE '0001-01-01' + 1)) FROM sales_date WHERE sales IS NOT NULL";"";""); B = (sum_xy – (n* avg_x * avg_y)) / (sum_xx – n * avg_x^2); A = avg_y – (B * avg_x); Y = A + (B * GetAsNumber(sales_date::period)) ]; Round( Y;2 ) )

Making the chart title dynamic

You may have noticed that our chart title includes the year. The following calculation can be used to make the title dynamic based on the dates in the period field. We simply get the minimum year and the maximum year and display them appropriately as part of the title.

Let([ minYear = ExecuteSQL("SELECT MIN(YEAR(period)) FROM sales_date";"";""); maxYear = ExecuteSQL("SELECT MAX(YEAR(period)) FROM sales_date";"";""); years = Case(minYear = maxYear; minYear; minYear & "–" & maxYear) ]; years & " Sales by Month" )

Labeling the months

One last thing to note about the chart is that the x-axis labels are displaying the month name rather than the date. To achieve this, simply specify the x-axis data as follows:

Coming soon

In Part 1, we mentioned that sales for our lemonade stand are likely to be seasonal. This may be true for the data you’re working with as well. In Part 3, we’ll take a look at how to calculate a monthly seasonal index from previous years’ data. Here’s what that seasonal index might look like: The seasonal index tells us that historically our sales in March are only about 40% of our monthly average for the year. Likewise, sales in August have been about 190% of the average. We’ll also look at how to combine the seasonal index with the trend to create a new seasonal forecast. The following image shows the chart we’ll create using the historical sales data from the previous three years: While our lemonade stand data has been carefully crafted to illustrate the concepts of trends and seasonality, real-world data rarely makes things so clear. However, the methods we’re exploring in these articles can help to tell an objective story about a set of data and provide a basis for forecasting future results. 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.

LEMONADE STAND, PART 1: FORECASTING AND TREND LINES

Sometimes as software developers we get caught up in the details of collecting, storing, and displaying data. To our clients, this is rarely the ultimate goal. Our clients want to use data to make better decisions that will increase profits, utilize resources more efficiently, and improve lives. This is part one of a series of articles exploring techniques for getting more out of your data. In this series, we’ll show you how to implement a variety of practical forecasting models in FileMaker Pro. A few of the topics we have planned include:

  • Moving averages
  • Weighted averages
  • Exponential smoothing
  • Trend projections
  • Seasonal trend projections

While there are a variety of ways to implement these kinds of algorithms, we’re going to focus on using methods that are easy to understand and implement. We’ll be using FileMaker 12’s ExecuteSQL function extensively because it’s ideal for computing sums and averages from sets of data. Later in the series, we’ll explore ways to optimize performance when working with larger sets of data. One goal of this series is to share HOW to implement these algorithms. However, a more important goal is to look at WHY and WHEN to use these techniques. To make the techniques easier to understand, we’re going to use the classic lemonade stand as a case study. At Skeleton Key (and our sister company Brightsource IT), we’re an “open book” company, so forecasting is ingrained in our culture. We continuously share all of the details about how the business is performing with every employee and we actively involve employees in planning and decision making. We believe that educating everyone in how the business operates and giving our employees incentives to think and act like owners are keys to our success. In this first article, we’ll look at a method for computing a trend line from a set of known values. In this case, our known values will be the monthly sales for the first six months of operating our lemonade stand. The trend line will help us visually see whether sales are growing, stable, or shrinking over time. It also provides a simplistic way to forecast future sales. Here’s the data we’ll be using: Plotting this data with a line chart looks like this: The purpose of a trend line is to plot a straight line that goes through the “center” of the known data points. Obviously a straight line can’t go through all of these points, so what we want to calculate is the straight line that misses all of the points by the least amount. A common way to calculate this is the “least squares method”. The least squares method finds the line that minimizes the total of the vertical distances between the line and each of the known data points. Let’s see what the trend line looks like and then we’ll discuss how it is calculated. A straight line can be expressed as the equation, Y = a + bX, where a is the Y-axis intercept and b is the slope. In our example, X is the month or period and Y is the predicted sales value. In order to keep this article focused on practical application, we’ll skip over some of the details of how a and b can be calculated from the known data points, but suffice it to say there are statistical formulas that make this relatively straightforward. If you’d like to read more about this, I’d recommend checking out this Wikipedia article as a starting point. Here are the pieces we need in order to calculate a and b:

n = the number of periods that have known sales values sum_xy = the total of each period value times its sales value sum_x = the total of the period values sum_xx = the total of each of the period values squared avg_y = the average of the sales values avg_x = the average of the period values

Let’s calculate these manually for our sample data:

n = 6 sum_xy = (1*4) + (2*4) + (3*3) + (4*4) + (5*8) + (6*10) = 137 sum_x = 1 + 2 + 3 + 4 + 5 + 6 = 21 sum_xx = (1*1) + (2*2) + (3*3) + (4*4) + (5*5) + (6*6) = 91 avg_y = (4 + 4 + 3 + 4 + 8 + 10) / 6 = 5.5 avg_x = (1 + 2 + 3 + 4 + 5 + 6) / 6 = 3.5

Each of these can be calculated easily with simple SQL queries using the COUNT, SUM, and AVG aggregate functions. For example, the query to calculate sum_xy is

SELECT SUM(period*sales) FROM sales_number WHERE sales IS NOT NULL

Let’s finish calculating our example manually. The formulas for a and b are:

B = (sum_xy – (n * avg_x * avg_y)) / (sum_xx – (n * avg_x^2)) A = avg_y – (B * avg_x)

Plugging in values from above, we get:

B = (137 – (6 * 3.5 * 5.5)) / (91 – (6 * 3.5^2)) = 1.2286 A = 5.5 – (1.2286 * 3.5) = 1.2

Using the Y = a + bX equation we can then calculate the forecasted sales for July (period 7) as:

Y = 1.2 + (1.2286 * 7) = 9.8

To calculate all of this in FileMaker, we’ll use a Let function to calculate each of the components and then return the final value:

Let( [  	n = ExecuteSQL("SELECT COUNT(sales) FROM sales_number";"";"");  	sum_xy = ExecuteSQL("SELECT SUM(period*sales) FROM sales_number WHERE sales IS NOT NULL";"";"");  	sum_x = ExecuteSQL("SELECT SUM(period) FROM sales_number WHERE sales IS NOT NULL";"";"");  	sum_y = ExecuteSQL("SELECT SUM(sales) FROM sales_number WHERE sales IS NOT NULL";"";"");  	sum_xx = ExecuteSQL("SELECT SUM(period**2) FROM sales_number WHERE sales IS NOT NULL";"";"");  	avg_y = ExecuteSQL("SELECT AVG(sales) FROM sales_number WHERE sales IS NOT NULL";"";"");  	avg_x = ExecuteSQL("SELECT AVG(period) FROM sales_number WHERE sales IS NOT NULL";"";"");  	B = (sum_xy - (n* avg_x * avg_y)) / (sum_xx - n * avg_x^2); 	A = avg_y - (B * avg_x); 	Y = A + (B * sales_number::period)  ]; 	Y )

By extending our found set of records to include periods for the entire year, we can see how the trend line extends into the future. In this first article of our Lemonade Stand series, we’ve explored how to use simple linear regression to calculate a value at any point based on the trend established by a set of known values. This technique can be very useful for projecting values like future sales, inventory levels, or anything that is likely to follow a linear trend. You might expect that sales for a lemonade stand could experience some seasonal variation. In a future article, we’ll explore how to adjust our forecast based on a seasonal index as well as how to calculate the seasonal index values based on historical data from previous years. In Part 2, we’ll take a quick look at how to modify our trend line calculation to work with a period that is a date rather than a number. 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.