Please help condense cascade into a single row

[danl1988]

I'm trying to build out a deployment forecast that has two main components: a probability of the deal's timing in a given period, and a % of the deal deployed in a given period.

For example, suppose we're forecasting a deal with 100 devices. If there's a 100% probability it closes in January and 100% of the deal will be deployed in the same period as the deal closes, it's an easy calculation: 100% x 100% x 100 devices = 100 devices deployed in January.

• But suppose there's a 10% chance the deal closes in January, 10% in February, 10% in March...until you reach 10% in October (cumulatively, 100% of timing is accounted for)
• And regardless of when the deal closes, 25% of devices will be deployed in the same month as the deal closes, 25% will be deployed in Month 2, 25% will be deployed in Month 3, and 25% in Month 4.
• The number of devices being deployed in January, then, is calculated the same as above: 10% (chance of closing in January) x 25% (% of devices deployed in Month 1) x 100 = 2.5 devices. Nice and easy.
• The number of devices being deployed in February, though, is the 10% (chance of closing in January) x 25% (% of devices deployed in Month 2) x 100 = 2.5 devices, PLUS 10% (chance of closing deal in Feb) x 25% (% of devices deployed in Month 1) x 100 = 5 devices

You can see how with a long sales and deployment cycle (we do enterprise sales) and many deals this could very quickly get out of hand. I've currently got things built out in a cascading manner in the image below but I'd love to eliminate the whole cascade and generate a formula that achieves the same result in a single cell/row.

Many thanks,

Dan

[/IMG]

 

[danl1988]

To anyone considering this: in the past I've achieved a simpler version of this with a SUM(OFFSET) combination and a single distribution but this is a bit more sophisticated as it involves two probabilistic distributions. I suspect some combination of SUM, OFFSET, and SUMPRODUCT could get the job done but it appears I can't quite figure it out.

But I'm quite confident someone out there has more expertise than I do on the subject so here's hoping you can help!