Sales Analysis with Coefficient of Determination

The correlation of data can be an extremely worthwhile endeavor for any business analyst. Adding some graphical depiction of your analysis can make it even better.

One of my favorite charts and accompanying functions are Scatterplot (XY) Chart and the Coefficient of Determination function. This quick analysis combo truly should be in your Excel Tool Belt!

Let’s say, for instance, that you want to do some speedy research into the efficacy of your sales advertising. Perhaps you want to know whether it is typically better to spend advertising dollars on Tuesday or Thursday.

First determine your ad effectiveness on Tuesdays by selecting data from those days and making a Scatterplot Chart.  Then do the following:

1. Right-click on one of the data points and
2. Choose Add Trendline
3. Right-click the Trendline and choose Format Trendline
4. Format the Trendline to your preferences and
5. Put a Checkmark next to Display R-squared Value on Chart

 

The R-squared value is your Coefficient of Determination (COD) that will tell you how strong your data on your two axes. In the example above, the COD value is .74 (or 74%) representing a strong correlation between spending advertising dollars on Tuesdays and increased sales.

Then simply do the same for your Thursday data, and compare the size of the CODs. If you get, for example, a Coefficient of Determination of 74% on Tuesday and 41% on Thursday, Hey, it’s a no-brainer!  Spend your money on Tuesdays!

Try using a Scatterplot and Coefficient of Determination sometime when seeking the strength of a correlation of data sets or multiple sets. It is Fast and Effective. Just don’t tell anyone how Easy it was!