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