0
$\begingroup$

I have a file with sales for one complete year. Multiple customers, month number and total sales (per customer):

Customer | Month | Sales
------------------------
A        | 1     | 100
A        | 2     | 120
A        | 3     | 90
B        | 1     | 50,000
B        | 2     | 49,000
B        | 3     | 51,500

Now, I want to know if there's a pattern in the sales per month (for example, sales go up or down in a specific month).

I cannot average the customers per month because that would lead to only 12 data points (one per month), plus the sales from a customer might be really different for another one.

I thought I could calculate the year average per customer and use that as a base point for the regression analysis.

Is it correct if I do the following? :

  1. Add boolean columns for each month: is_january, is_feb, is_march, ...
  2. Calculate the average for the whole year and for each customer
  3. Reduce all the sales to the same base (e.g., 100), by doing (Total sales of the month)/(year average)

That way I end up with the following:

IsJan | IsFeb | IsMarch | ... | CustomerAvg_base100 | ThisMonthSales_base100
------------------------------------------------------------
1     | 0     | 0       | ... | 100                 | 97
0     | 1     | 0       | ... | 100                 | 116
0     | 0     | 1       | ... | 100                 | 87
1     | 0     | 0       | ... | 100                 | 99.67
0     | 1     | 0       | ... | 100                 | 97.67
0     | 0     | 1       | ... | 100                 | 102.66

Is this correct? Or how should I do it?

BTW, I don't know too much about statistics, only basic linear regression analysis and I am using Excel (but I could learn E-Views if needed). Thanks!

$\endgroup$
  • $\begingroup$ Could you tell us which variable should be the outcome variable and which one(s) should be predictor variable(s) in your regression model? $\endgroup$ – Ruthger Righart May 17 '15 at 19:14
  • $\begingroup$ @RuthgerRighart I want to predict sales or sales variations (%), all the other are predictors. $\endgroup$ – Diego Jancic May 17 '15 at 20:12
  • $\begingroup$ Do you have the same customers every month? i.e. Is there sales data for every customer for every month? $\endgroup$ – Eric Farng May 31 '15 at 21:16
  • $\begingroup$ @ericfarng yes. Not initially but I've filtered out those who didn't purchased every month (either because they dropped or because they were new). Now I only have those that purchased every month. $\endgroup$ – Diego Jancic Jun 1 '15 at 2:38
  • $\begingroup$ Thank you. Also, are the sales in dollars? or is that the count of sales to that customer? $\endgroup$ – Eric Farng Jun 1 '15 at 12:38
2
+100
$\begingroup$

Though you don't say so explicitly in your question, I assume you only have one calendar year of data. Please let us know if this is not the case.


First, I reinterpret your question. You ask:

I want to know if there's a pattern in the sales per month (for example, sales go up or down in a specific month).

I interpret this more specifically:

Q: What is average seasonality in sales? (applicable to individual customers and to total sales)

Interpreted this way, the first thing you need to do is detrend your data, assuming you have basis for doing so. What is the year-over-year growth rate (or negative growth rate) in sales? Maybe you have data to estimate this, or maybe you can estimate it from peer companies, or from the growth rate in the market overall, or in your stage of growth/maturity. If the growth rate is small ($< \pm 5$) then you can probably ignore it. Much larger than that and you'll see a significant difference between January and December just from year-over-year growth trend.

Second, having only one year of data puts you in a weak position to estimate seasonality. Normally, you'd want multiple observations per month, summed over customers.

Third, you can estimate seasonality if you accept certain assumptions and also test the validity of your methods (see "Qualifacations").

You need to assume that any factor that causes higher or lower sales in a given month will affect all customers uniformly. This assumption excludes other (likely) factors: change in customer base, sector- or industry-specific economic sensitivity; and (most important) new sales or marketing programs (discounts, price changes, promotions, channels, etc.) or new product introductions that your company or competitors implement.

Your mean seasonality estimate just is your total sales per customer by month (detrended and normalized so Avg sales per month per customer = 100). You can do this easily with a spreadsheet, assuming linear growth (or decline) during a year.

Next, I suggest that you estimate confidence intervals around this mean estimate, using data for individual customers. I would not be inclined to estimate this separately for each month, as since I don't know if you have enough data points per month (i.e. number of customers). I think it would be just as good and safer to estimate confidence intervals (95% confidence) based on total sales for the year per customer. This is a single cell calculation in Excel. See https://statsmethods.wordpress.com/2013/05/29/confidence-interval-for-a-single-mean/ and https://en.wikipedia.org/wiki/Confidence_interval for general description of the calculation and its interpretation. Here is a link that is specific to Excel: http://blog.excelmasterseries.com/2014/05/confidence-interval-of-population.html .

Intuitively, the lower the correlation between individual customer sales, the wider the confidence intervals will be.

Once you have confidence intervals (Upper Confidence Level UCL and Lower Confidence Level LCL), you can calculate and plot the upper and lower confidence intervals around your mean monthly seasonality simply by multiplying the upper confidence ratio ( UCL / 100) times each month's seasonality, and same for lower confidence ratio. (Again, average per month per customer is normalized to = 100)

Interpretation

Given your limited data (1 year), the actual seasonality is most likely to be somewhere between these upper and lower confidence intervals, with the assumption that the major source of seasonal variation affects all customers uniformly (randomly) and that the year-on-year growth trend will stay constant. ("95% confidence" refers to confidence in the estimation of the limits, and not to the likelihood that the actual seasonality is between these limits).

Qualification

You should also do diagnostic analysis on the composition of your customer base to determine whether total sales per month are determined by a few large customers, or by many roughly equal customers. You can do this by creating a plot of monthly ranked cumulative sales. Translation: Sort customers each month by sales, then calculate in a new column "Cumulative sales", and calculate a rolling sum where each row is the sum of this customer's sales plus the "Cumulative sales" the previous row. You will have 12 distributions, and you can average them to estimate your overall concentration distribution. If your sales are evenly distributed across all customers (unlikely) then you'll see a linear increase. If your sales are highly concentrated, then you'll see a highly curved distribution ("convex") where the first handful of customers are responsible for > 80% of sales.

I mention this as a "qualification" because if your monthly sales are dominated by a few customers (either the same customers, month by month, or different customers), then your seasonality estimate using the above method will not be adequate. Instead, you'll need methods to a) estimate which customers will be in the top percentile each month and b) what their sales might be, in terms of seasonality.

Overall, if I saw sales data that showed 1) skewed distribution of monthly sales per customer and 2) Little correlation month-to-month of which customers were in top X% and 3) only one year of data, THEN I would conclude that we do not have adequate basis for estimating seasonality.

$\endgroup$
  • $\begingroup$ Thank you. That was more or less where I was going but of course you explained it how to do it right :) $\endgroup$ – Diego Jancic Jun 2 '15 at 12:47
2
$\begingroup$

You have a multi-way contingency table Customer $\times$ Month $\times$ Sales and you are asking if conditional on customer, sales are independent of month. You will end up doing a bunch of chi-square tests which test for independence.

My expectation is if you have some holiday bonanza month (like Thanksgiving) that will totally mess up your chi-square results. You can get information if one of these entries is particularly influential by looking at Pearson residuals. Then drop the influential month and try checking for independence.

All this and more is very well explained in Chapter 3 of Categorical Data Analysis by Alan Agresti (I am sure other books have it too..this is the one I happen to use). The first example I found on googling is http://web.ntpu.edu.tw/~cflin/Teach/Cate/06CateUEN05ThreeWayPPT.pdf (coincidentally, the race-death penalty example he uses is from Agresti!).

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.