Trend and Growth Functions

Data can be analyzed in a lot of different ways.  Excel has dozens of tools and functions that make high-level statistical analysis not only possible, but in many cases fairly easy to use.

Researchers of all types (medicine, anthropology, sociology, entomology are a few of several fields) use statistics extensively.  Larger corporate companies also spend resources on analyzing, reporting, and making business decisions based on statistical analysis.

But for several reasons- limited resources and rarely having a clear evangelist for this type of thorough analysis and understanding are but two- smaller and growing companies are not nearly as diligent in performing such exercises.   And the fact that things are normally not out of control (not yet) or in need of statistical awareness (“Things are fine they way they are.” “We’ve gotten this far doing it how we do it.” makes management reluctant to move these types of agendas to the front burner.

Unfortunately, when that need arises (and if the company grows, it most certainly will), it seldom sneaks up quietly; rather it hits the company over the head, revealing several areas that need this more intense diagnostics in order to get to the next level of success.

Once a decision is made to build into the business a culture of continuous analysis and revisiting of the business’ key drivers, then one of the first things that warrants looking at is Trend.  Trends in a lot of areas: sales, costs, employee retention, support cases, to name a few.

Let’s say it’s 2003 and you run a Pizza Delivery Business.  You are trying to predict the cost of fuel for the next two years.  To Start, you’ve gathered data on Average Prices By Month from 1976 to 2003.  (TrendLine)

1. Sheet: “PriceOfGas-Data” – To remove any anomalies that might distort the picture, you decide to remove the highest and lowest value.  To do this you use the trimmean() function.  It’s inputs are (arrayToAverage,% of items to remove).  Since we don’t typically know the percentage off the top of our head, we usually just take the total number of items we’d like to remove (2- highest and lowest value) and divide it by the total count of values.
2. Sheet: “XYScatterPlot” – Select the two columns (Year, Average), then Insert/Chart/Scatter. A line chart, which charts x values as categorical with equal spacing, would probably work fine here.  But Line Graphs don’t allow the inclusion of Trend Lines, so we’ll use Scatter XY Charts.  More on the differences between the two chart types in a later post, I’m sure, but many other sites explain this important and confusing difference better than I can.
3. Sheet: “XYScatterPlot-TrendLine” To get the trend line (or in statistics/linear regression lingo, the best-fit line), simply right-click on any data point and select “TrendLine” from the drop down window.  You’ll see a R-Squared number of .4099.  Also called the Coefficient of Determination, this number ranges from 0 to 1 where 0 is no correlation between the X and Y Values and 1 is 100% perfect correlation.
4. Sheet: “XYScatterPlot-FC&TrendLine” Finally, if you want to see the values that make up the trend line, you can do the grunt work with the trend formula.  Since the X in this case is linear and categorical (it’s just the years) the simpler form, where you just trend on the Y values, can be used.  We can also use an array formula.  (If you remember, they allow you to work on a range of cells instead of just one at a time; but you must use CTRL-SHIFT-ENTER to make it work.)  So, first A) create a new column for the forecasted data (FC) and select all of the cells in the column.  Then, B) in the address bar type trend function, which in its simplest state takes as its inputs the Actual Y Values with which you are trying to predict future Y values. The function is: =TREND(O4:O31,,) But when you hit CTRL-SHIFT-ENTER, ={TREND(O4:O31,,)} , fancy brackets appear and the array formula works, populating every cell in the FC column.

Linear Regression and one of its tools- Trend Lines- can be a powerful tool.  I invite you to find ways to allow it to help make your business more predictable and manageable.