Example (Cells with constraints)

Values can be top down spread to cells that have constraints set for their limits. Values are prorated according to the ratios of the existing data, just as with unlocked cells. However, once the limit is reached, the cell then behaves like a locked cell, and any amount that exceeds the limit for that cell is divided between the remaining cells (according to their ratio to each other, excluding the value of the cell with the constraint).

In this example, the September 2009 cell is set with an upper limit constraint of 500 (but no lower limit constraint). The initial ratios for July, August, and September are computed as shown:

Initial ratio for July = 1000/(1000 + 600 + 400) = 1000/2000 = .5
Initial ratio for August = 600/(1000 + 600 + 400) = 600/2000 = .3
Initial ratio for September = 400/(1000 + 600 + 400) = 400/2000 = .2


	Quarter 3 2009	July 2009	August 2009	September 2009
Los Angeles	2,000	1,000	600	400

When a new Quarter 3, 2009, value of 3,000 is top down spread, the values to be spread are initially calculated as shown:

Initial calculation of July spread = 3000 * .5 = 1,500
Initial calculation of August spread = 3000 * .3 = 900
Initial calculation of September spread = 3000 * .2 = 600 > 500 limit


	Quarter 3 2009	July 2009	August 2009	September 2009
Los Angeles	3,000	1,563	938	500

Without the constraint, the amount spread to September would be 600. Since 500 is the limit, the excess of 100 must be spread between July and August. To spread this excess, a new ratio is calculated for the remaining cells that excludes September's original amount:

New ratio for July = 1000/(1000 + 600) = 1000/1600 = .625
New ratio for August = 600/(1000 + 600) = 600/1600 = .375
- The excess that is spread is added to the amount initially calculated for the cells with no constraints:
Actual amount spread to July = 1,500 + (100 excess * .625 new ratio) = 1,562.50 = 1,563
Actual amount spread to August = 900 + (100 excess * .375 new ratio) = 937.50 = 938