2
$\begingroup$

I have a pretty basic question, but am pretty far removed from stats and modeling.

I have historical data (daily, weekly, monthly, however I want to slice) for a few years and I want to predict the probability of hitting an end of month target throughout the month. The data follows very similar trends, if you overlay 1 week over any other week in the year, it's practically the same line.

I thought of a few ways to do this, but don't know if they're correct. 1) Using a poisson, I can plug in the historical average values from any day X to end of the month. I also have a target from day X to end of month, and with the historicals, I will know how many successful times that amount has been reached. So using P = (e^-u)(x^u)/x!, u = average historicals, x = # times those historicals exceed the remaining target. 2) since every day has a very similar outcome, I could count how many Mondays, Tuesdays.... are left in the month, calculate an interval in which I'm 99% sure each day will come in between X and Y. Add the lower and upper limits on each day and by the end of the month can I say I'm 99% sure value will be between X1+X2+... and Y1+Y2+...

thoughts and help very much appreciated.

$\endgroup$
5
  • $\begingroup$ What are the actual data? Are they counts of something? Of what? What are your goals here? $\endgroup$ – gung - Reinstate Monica Jul 31 '15 at 16:25
  • $\begingroup$ actual data are counts, all positive. monthly numbers come out to about a million. monday - friday is 35-40k per day and sat/sun is 30kish per day. targets are close to actuals. $\endgroup$ – bramwell2010 Jul 31 '15 at 16:40
  • $\begingroup$ So the idea is, as new information comes in over the course of the month, you want to forecast what the end of month total number of users will be, is that right? $\endgroup$ – gung - Reinstate Monica Jul 31 '15 at 16:47
  • $\begingroup$ not necessarily forecast, but have a probability of hitting X,Y,Z... targets. one of these targets being our own forecasted model result $\endgroup$ – bramwell2010 Jul 31 '15 at 16:52
  • 1
    $\begingroup$ This post is tagged analysis. Read the wiki here for tips on how to improve the post for better clarity and answers. $\endgroup$ – AdamO Aug 5 '15 at 19:30
0
$\begingroup$

Obviously without hard numbers it is not feasible to give a highly specific answer. However, hopefully the following framework will give you what you need.

Let me assume that weekday results are close to normally distributed with a mean of 37.5k and a standard deviation of 2.5k. For simplicity I'll assume the mean for Saturdays and Sundays are 30k with a standard deviation of 2k.

For a month with 22 weekdays and 8 weekend days, the expected mean is the sum of the expected means 22 * 37.5k + 8 *30k = 1,065k. The variance is the sum of the variances 22 * 2.5k^2 + 8 * 2k^2 = 169.5M, the square root of that (standard deviation) is about 13k.

To create a 99% confidence interval, you need about 2.8 standard deviations on either side of the mean - 2.8 * 13k = 36.5k. So given ALL of these assumptions, the 99% interval would be 1065k-36.5k to 1065k+36.k5 or 1,028.5k to 1,101.5k.

To test the threshold, use the CCDF function for a gaussian, 1/2*(1+erf((threshold-mean)/(stdev*sqrt(2)))

For example, a threshold of 1,050k would be surpassed all but about 12.5% of the time.

$\endgroup$
1
  • $\begingroup$ You are assuming no outliers in the historical data . You are assuming no Holiday/event effects . You are assuming that daily patterns are fixed over time and more specifically the 5 working days have a constant/common effect . You are assuming no trends and no level shifts in the history . You are assuming a Gaussian Distribution . If all of these things are true then what you are proposing might be useful. $\endgroup$ – IrishStat Aug 6 '15 at 14:17
0
$\begingroup$

You need to model your data at a daily level to handle this. There are different impacts based on weekdays vs weekends and when you do account for this you will be able to better forecast.

We deliver this probability as part of every forecast we deliver. We deliver a table of forecasts from which you can interpolate your goal and find the % probability.

Post your data to dropbox noting the beginning date of the data and what country the data is from and we can take a look and post results. I am an author of the Software Package, Autobox, and we were asked this question by P&G at a Conference. I have cut and pasted the discussion from slide 45 from our PPT on this.

The 2008 financial crises caught a few companies unable to quickly identify when month end numbers were not going to be met.

Simplistic approaches use a ratio estimate (ie 5 days into the month 30/5 so multiply current month total by 6 to get month end estimate) are simplistic and incorrect. Promotions and day of the week effects are not considered using ratio estimates and need to be modeled at a DAILY level as part of a comprehensive model and forecast which can then be used to determine probabilities of making the month end number.

Autobox reports out a variety of Probabilities which the target can be evaluated against. A summary report can then be used to identify which SKU’s are likely NOT to make the month end number.

$\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.