2

We deal with "jobs" - a mini project involving multiple parties, resources and time constraints, etc. Jobs progresses through 4 well defined states from start to end; call them S1, S2, S3 and S4 where S1 is the start of the job and S4 is where we have invoiced the client and the job is closed.

Jobs are for different clients, in different regions and run by different people (project managers) within our company.

Not every job in state S1 will make it to S4 as the job may be cancelled. From historical data we can readily determine for each combination of client/region/project manager the probability of getting from S1 to S4, S2 to S4 and S3 to S4.

Also the length of time from S1 to S4 may differ for each of these combinations so we also know historical average time to go from S1 to S4, S2 to S4 and S3 to S4.

We may have 1000 jobs currently in progress. ie in states S1, S2 or S3. We need to predict how much money we will be invoicing each week in the future.

I don't think is a problem since it is little more than the sum for each future week of current estimated value of the job x the probability that it will get from its current state to S4 and then apply this to a future invoicing week based on the historical average length of time to go from the current state to S4. (be sure to call me out if this is flawed)

You can imagine that this will produce a nice bar chart that will get senior management all excited.

The problem I am facing is that the finance guys (who are actually really smart) would like to have some confidence intervals, margins or error (or standard deviations or the like) that will help them communicate where the numbers are really solid and where they start to become rubbery.

I would imagine that the invoicing for the future few weeks where we are going from S3 to S4 should be highly accurate and the margin of error would be very small yet 3 months out I would like to think that the margin of error could be quite large.

My problem is I do not know what to even "google" to solve this problem. How do I go from the data that I have available to a margin of error?

It is very binary in that a job either makes it to S4 or it doesn't. I remembered the binomial distribution but that does not look appropriate. I started thinking about standard distributions but this does not seem to work each since it does not make sense to have a standard deviation over boolean logic.

One suggestion was that if there is a 50% chance of making it to S4, and the job value is $$1000 then apply $500 to the future invoicing week with $500 either side as a margin of error. On the surface this seems sensible but if there is only a 10% chance of making it S4 then would you attribute $100 to the future invoicing week with a + / -$900 margin of error (or maybe +$900 / -$100). +$900 seems far too high when the probability of getting to S4 is so low and when all of the work in progress is considered the upper bound of the margin of error will be huge and totally unrealistic.

Any tips and pointers would be greatly appreciated. Keep it simple. I is an engineer but my stats were forgotten two beers after the exam.

DJA
  • 143
  • It's pretty wordy for an engineer. Are you asking how to estimate the transition probabilities? What to do with them once you have? So many techniques, so little time. – hardmath Sep 15 '14 at 14:04

1 Answers1

1

There are two areas where you are introducing uncertainty

  1. Estimates of the invoicing probabilities given a starting state. (inferential uncertainty)
  2. The time each stage will take. (inherent randomness)

If you are fairly confident in (1), then you should perform a Monte Carlo simulation of your current book of projects.

  • Set up "bins" of one week in length into the future (however far you want to go out).
  • Model the time in each state (S1, S2, S3...) as exponentially distributed with rate parameters equal to the reciprocal of the average time to complete each stage (which you said you know).

Now, you will want to start your simulation:

  1. Set up each project in its current state at time = 0.
  2. For each project, you will start of with a "culling" based on the conditional probability that the project will proceed to S4 given it is currently at some other state at time = 0. This is done by associating a binary variable with each project. the variable will be 1 if the project will make it to S4 and 0 otherwise.
  3. Any project that got a 0 in step 2 should be eliminated from this run of the simulation.
  4. For each of the remaining projects, calculate the time to completion as the sum of the exponential variables representing the states yet to be completed. E.g., if project 1 (P1) is in S2 at time=0, then you'd draw exponential random variables for the time to complete S2, S3, and S4. The actual distribution used for each time period will be based on the average time to complete that stage (as discussed earlier).
  5. Place the invoice amount for that project into the proper weekly bin for each project.
  6. Repeat 10k - 30k times using a monte carlo package.

This will allow you to generate distributions of the invoice totals for each week going forward. You can select the upper and lower 2.5th percentiles to give your finance folks the central 95% predicted invoice values.

Now, if you are not so confident in the estimates of the completion probabilities, expected times to completion, and invoice amounts, you will need to model these as random variables and include them in your simulation.

  • Thanks. Exactly what I am after. Moreover, I can even explain this in laymen's terms to finance and senior management alike. – DJA Sep 16 '14 at 12:12
  • @DJA, you now have enough reputation to vote this answer up. – JRN Jan 04 '15 at 04:50