## 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:

## 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

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.