#### danl1988

##### New Member
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]

### Excel Facts

Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

#### danl1988

##### New Member
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!

Replies
1
Views
439
Replies
5
Views
513
Replies
3
Views
155
Replies
2
Views
4K
Replies
1
Views
602

1,171,798
Messages
5,877,584
Members
433,265
Latest member
Umaratnam

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back