Question: How are calculations performed for the Goal Seek feature?
Answer: The following examples illustrate how calculations are performed for various goal seek rules. Each of these examples is set up for a product category that includes three products (Product A, Product B, and Product C).
A goal seek rule is created with the following settings:
Measure: Amount
Currency: US Dollars (default general ledger currency)
Value: 5000.00
The following data exists for the products:
|
Product |
Purchase Unit of Measure |
Unit Cost |
Past Due Period Quantity |
Period 1 Quantity |
Total Quantity |
Minimum Purchase Quantity |
Purchase Increment Quantity |
|
A |
Case12 |
120.00 |
10 |
15 |
25 |
10 |
5 |
|
B |
Case12 |
135.00 |
4 |
16 |
20 |
4 |
4 |
|
C |
Case12 |
143.00 |
10 |
12 |
22 |
10 |
2 |
The goal seek calculations are performed as follows:
|
Product |
|
Past Due Period Amount |
Period 1 Amount |
Allocation Percentage |
Allocation Amount |
Unit Cost |
Calculated Allocation Quantity |
Calculated Allocated Quantity Rounded for Minimum Purchase/Purchase Increment |
Prior Period Quantity |
Initial Goal Seek Quantity |
Initial Order Amount |
Multiple x Unit Cost |
Reduce Goal Seek Quantity |
Revised Goal Seek Quantity |
Revised Order Amount |
|
A |
|
1200.00 |
1800.00 |
31.71% |
580.00 |
120.00 |
4.84 |
5 |
10 |
15 |
1800.00 |
600.00 |
|
15 |
1800.00 |
|
B |
|
540.00 |
2160.00 |
38.05% |
696.00 |
135.00 |
5.16 |
8 |
4 |
12 |
1620.00 |
540.00 |
|
12 |
1620.00 |
|
C |
|
1430.00 |
1716.00 |
30.23% |
553.00 |
143.00 |
3.87 |
4 |
10 |
14 |
2002.00 |
286.00 |
(2) |
12 |
1716.00 |
|
|
Total |
3170.00 |
5676.00 |
100.00% |
1830.00 |
|
|
|
|
|
5422.00 |
|
|
|
5136.00 |
|
|
Cumulative Remainder Goal Seek Amount |
1830.00 |
(3846.00) |
|
|
|
|
|
|
Initial Excess |
422.00 |
|
|
Revised Excess |
136.00 |
|
|
Allocation Amount |
|
1830.00 |
|
|
|
|
|
|
|
|
|
|
|
|
The order amounts are calculated as follows:
The total past due amount for the three products is 3,170.00, which is less than the 5,000.00 amount for the goal seek rule by 1,830.00. So, the software continues to the next period (Period 1).
The sum of the total amounts for Period 1 and the past due period is greater than the amount for the goal seek rule, so allocation is performed based on the amounts for Period 1.
The allocation percentages are calculated based on the amounts for Period 1.
The allocation percentages are used to calculate new allocation amounts, which total the 1,830.00 amount needed for the goal seek rule.
The allocation quantities are calculated as the allocation amounts divided by the unit costs.
The allocation quantities are rounded to meet the minimum purchase and purchase increment quantities for each product.
The initial goal seek quantities are calculated as the sum of the rounded quantities and the prior period quantities.
The initial order amounts are calculated based on the unit cost and initial goal seek quantities.
The initial excess is calculated as the sum of the initial order amounts minus the amount for the goal seek rule.
The purchase increment quantity is multiplied by the unit cost for each product. These amounts are then compared to the initial excess to find the largest of these values that can be reduced to minimize excess.
The revised goal seek quantities are calculated.
The revised order amounts are calculated based on the revised goal seek quantities.
A goal seek rule is created with the following settings:
Measure: Amount
Currency: Canadian Dollars (default general ledger currency is US Dollars)
Value: 5000
The following data exists for the products:
|
Product |
Purchase Unit of Measure |
Unit Cost |
Exchange Rate |
CND Unit Cost |
Past Due Period Quantity |
Period 1 Quantity |
Total Quantity |
Minimum Purchase Quantity |
Purchase Increment Quantity |
|
A |
Case12 |
125.00 |
0.977 |
122.13 |
10 |
20 |
30 |
10 |
5 |
|
B |
Case12 |
135.00 |
0.977 |
131.90 |
4 |
12 |
16 |
4 |
4 |
|
C |
Case12 |
143.00 |
0.977 |
139.71 |
10 |
20 |
30 |
10 |
2 |
The goal seek calculations are performed as follows:
|
Product |
|
Past Due Period Amount |
Period 1 Amount |
Allocation Percentage |
Allocation Amount |
Unit Cost |
Calculated Allocation Quantity |
Calculated Allocated Quantity Rounded for Minimum Purchase/Purchase Increment |
Prior Period Quantity |
Initial Goal Seek Quantity |
Initial Order Amount |
Multiple x Unit Cost |
Reduce Goal Seek Quantity |
Revised Goal Seek Quantity |
Revised Order Amount |
|
A |
|
1221.25 |
2443.00 |
35.82% |
664.00 |
122.13 |
5.44 |
5 |
10 |
15 |
1831.88 |
611.00 |
|
15 |
1831.88 |
|
B |
|
527.58 |
1583.00 |
23.21% |
430.00 |
131.90 |
3.26 |
4 |
4 |
8 |
1055.16 |
528.00 |
|
8 |
1055.16 |
|
C |
|
1397.11 |
2794.00 |
40.97% |
760.00 |
139.71 |
5.44 |
6 |
10 |
16 |
2235.38 |
279.00 |
|
16 |
2235.38 |
|
|
Total |
3145.94 |
6819.00 |
100.00% |
1854.00 |
|
|
|
|
|
5122.41 |
|
|
|
5122.41 |
|
|
Cumulative Remainder Goal Seek Amount |
1854.06 |
(4965.00) |
|
|
|
|
|
|
Initial Excess |
122.41 |
|
|
Revised Excess |
122.41 |
|
|
Allocation Amount |
|
1854.06 |
|
|
|
|
|
|
|
|
|
|
|
|
The order amounts are calculated as follows:
The total past due amount for the three products is 3,145.94, which is less than the 5,000.00 amount for the goal seek rule by 1,854.06. So, the software continues to the next period (Period 1).
The sum of the total amounts for Period 1 and the past due period is greater than the amount for the goal seek rule, so allocation is performed based on the amounts for Period 1.
The allocation percentages are calculated based on the amounts for Period 1.
The allocation percentages are used to calculate new allocation amounts, which total the 1,854.06 amount needed for the goal seek rule.
The allocation quantities are calculated as the allocation amounts divided by the unit costs.
The allocation quantities are rounded to meet the minimum purchase and purchase increment quantities for each product.
The initial goal seek quantities are calculated as the sum of the rounded quantities and the prior period quantities.
The initial order amounts are calculated based on the unit cost and initial goal seek quantities.
The initial excess is calculated as the sum of the initial order amounts minus the amount for the goal seek rule.
The purchase increment quantity is multiplied by the unit cost for each product. These amounts are then compared to the initial excess to find the largest of these values that can be reduced to minimize excess. In this case, no reduction is made.
A goal seek rule is created with the following settings:
Measure: Amount
Currency: US Dollars
Value: 5000
A cost matrix is set up for Product A.
The following data exists for the products:
|
Product |
Purchase Unit of Measure |
Unit Cost |
Past Due Period Quantity |
Period 1 Quantity |
Total Quantity |
Minimum Purchase Quantity |
Purchase Increment Quantity |
|
A |
Case12 |
140.00 |
10 |
15 |
25 |
10 |
5 |
|
B |
Case12 |
135.00 |
4 |
16 |
20 |
4 |
4 |
|
C |
Case12 |
143.00 |
10 |
12 |
22 |
10 |
2 |
The goal seek calculations are performed as follows:
|
Product |
|
Past Due Period Amount |
Period 1 Amount |
Allocation Percentage |
Allocation Amount |
Unit Cost |
Calculated Allocation Quantity |
Calculated Allocated Quantity Rounded for Minimum Purchase/Purchase Increment |
Prior Period Quantity |
Initial Goal Seek Quantity |
Initial Order Amount |
Multiple x Unit Cost |
Reduce Goal Seek Quantity |
Revised Goal Seek Quantity |
Revised Unit Cost (Cost Matrix) |
Revised Order Amount |
Add Goal Seek Quantity |
Revised Goal Seek Quantity |
Revised Order Amount |
|
A |
|
1400.00 |
2100.00 |
35.14% |
573.00 |
140.00 |
4.09 |
5 |
10 |
15 |
2100.00 |
700.00 |
|
15 |
125.00 |
1875.00 |
|
15 |
1875.00 |
|
B |
|
540.00 |
2160.00 |
36.14% |
589.00 |
135.00 |
4.36 |
8 |
4 |
12 |
1620.00 |
540.00 |
(4) |
8 |
135.00 |
1080.00 |
|
8 |
1080.00 |
|
C |
|
1430.00 |
1716.00 |
28.71% |
468.00 |
143.00 |
3.27 |
4 |
10 |
14 |
2002.00 |
286.00 |
|
14 |
143.00 |
2002.00 |
2 |
16 |
2288.00 |
|
|
Total |
3370.00 |
5976.00 |
100.00% |
1630.00 |
|
|
|
|
|
5722.00 |
|
|
|
|
4957.00 |
|
|
5243.00 |
|
|
Cumulative Remainder Goal Seek Amount |
1630.00 |
(4346.00) |
|
|
|
|
|
|
Initial Excess |
722.00 |
|
|
|
Revised Excess |
(43.00) |
|
|
243.00 |
|
|
Allocation Amount |
|
1630.00 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
The order amounts are calculated as follows:
The total past due amount for the three products is 3,370.00, which is less than the 5,000.00 amount for the goal seek rule by 1,630.00. So, the software continues to the next period (Period 1).
The sum of the total amounts for Period 1 and the past due period is greater than the amount for the goal seek rule, so allocation is performed based on the amounts for Period 1.
The allocation percentages are calculated based on the amounts for Period 1.
The allocation percentages are used to calculate new allocation amounts, which total the 1630.00 amount needed for the goal seek rule.
The allocation quantities are calculated as the allocation amounts divided by the unit costs. For product A, the unit cost is determined by the cost matrix based on the quantity for the past due period.
The allocation quantities are rounded to meet the minimum purchase and purchase increment quantities for each product.
The initial goal seek quantities are calculated as the sum of the rounded quantities and the prior period quantities.
The initial order amounts are calculated based on the unit cost and initial goal seek quantities.
The initial excess is calculated as the sum of the initial order amounts minus the amount for the goal seek rule.
The purchase increment quantity is multiplied by the unit cost for each product. These amounts are then compared to the initial excess to find the largest of these values that can be reduced to minimize excess.
The unit costs are updated based on the revised goal seek quantity and the cost matrix for product A.
The revised order amount is calculated based on the revised unit cost and revised goal seek quantity.
The revised excess is calculated as the sum of the revised order amounts minus the amount for the goal seek rule. Now there is a deficit, so the software must add an additional quantity.
The additional quantity that increases the order amount by the smallest amount is calculated, and new revised goal seek quantities are calculated.
The revised order amounts are calculated based on the revised goal seek quantities.
A goal seek rule is created with the following settings:
Measure: Volume
Volume Unit of Measure: Cubic Meters
Value: 2000
The following data exists for the products:
|
Product |
Purchase Unit of Measure |
Unit Volume |
Past Due Period Quantity |
Period 1 Quantity |
Total Quantity |
Minimum Purchase Quantity |
Purchase Increment Quantity |
|
A |
Case12 |
47.5 |
10 |
15 |
25 |
10 |
5 |
|
B |
Case12 |
86.4 |
4 |
16 |
20 |
4 |
4 |
|
C |
Case12 |
22.08 |
10 |
12 |
22 |
10 |
2 |
The goal seek calculations are performed as follows:
|
Product |
|
Past Due Period Volume |
Period 1 Volume |
Allocation Percentage |
Allocation Volume |
Unit Volume |
Calculated Allocation Quantity |
Calculated Allocated Quantity Rounded for Minimum Purchase/Purchase Increment |
Prior Period Quantity |
Initial Goal Seek Quantity |
Initial Order Volume |
Multiple x Unit Volume |
Reduce Goal Seek Quantity |
Revised Goal Seek Quantity |
Revised Order Volume |
|
A |
|
475.00 |
712.50 |
30.19% |
289.00 |
47.50 |
6.09 |
10 |
10 |
20 |
950.00 |
237.50 |
(5) |
15 |
712.50 |
|
B |
|
345.60 |
1382.40 |
58.58% |
562.00 |
86.40 |
6.50 |
8 |
4 |
12 |
1036.80 |
345.60 |
|
12 |
1036.80 |
|
C |
|
220.80 |
264.96 |
11.23% |
108.00 |
22.08 |
4.87 |
6 |
10 |
16 |
353.28 |
44.16 |
(4) |
12 |
264.96 |
|
|
Total |
1041.40 |
2359.86 |
100.00% |
959.00 |
|
|
|
|
|
2340.08 |
|
|
|
2014.26 |
|
|
Cumulative Remainder Goal Seek Volume |
958.60 |
(1401.26) |
|
|
|
|
|
|
Initial Excess |
340.08 |
|
|
Revised Excess |
14.26 |
|
|
Allocation Volume |
|
958.60 |
|
|
|
|
|
|
|
|
|
|
|
|
The order volumes are calculated as follows:
The total past due volume for the three products is 1,041.40 m³, which is less than the 2,000 m³ for the goal seek rule by 958.60 m³. So, the software continues to the next period (Period 1).
The sum of the total volumes for Period 1 and the past due period is greater than the volume for the goal seek rule, so allocation is performed based on the volumes for Period 1.
The allocation percentages are calculated based on the volumes for Period 1.
The allocation percentages are used to calculate new allocation volumes, which total the 959.00 m³ needed for the goal seek rule.
The allocation quantities are calculated as the allocation amounts divided by the unit volumes.
The allocation quantities are rounded to meet the minimum purchase and purchase increment quantities for each product.
The initial goal seek quantities are calculated as the sum of the rounded quantities and the prior period quantities.
The initial order volumes are calculated based on the unit volume and initial goal seek quantities.
The initial excess is calculated as the sum of the initial order volumes minus the volume for the goal seek rule.
The purchase increment quantity is multiplied by the unit volume for each product. These volumes are then compared to the initial excess to find the largest of these values that can be reduced to minimize excess. A quantity for both product A and product C is calculated.
The revised goal seek quantities are calculated.
The revised order volumes are calculated based on the revised goal seek quantities.
A goal seek rule is created with the following settings:
Measure: Net Weight
Net Weight Unit of Measure: Kilograms
Value: 3000
The following data exists for the products:
|
Product |
Purchase Unit of Measure |
Unit Net Weight |
Past Due Period Quantity |
Period 1 Quantity |
Total Quantity |
Minimum Purchase Quantity |
Purchase Increment Quantity |
|
A |
Case12 |
9.0 |
10 |
15 |
25 |
10 |
5 |
|
B |
Case12 |
7.2 |
4 |
16 |
20 |
4 |
4 |
|
C |
Case12 |
168 |
10 |
12 |
22 |
10 |
2 |
The goal seek calculations are performed as follows:
|
Product |
|
Past Due Period Net Weight |
Period 1 Net Weight |
Allocation Percentage |
Allocation Net Weight |
Unit Net Weight |
Calculated Allocation Quantity |
Calculated Allocated Quantity Rounded for Minimum Purchase/Purchase Increment |
Prior Period Quantity |
Initial Goal Seek Quantity |
Initial Order Net Weight |
Multiple x Unit Net Weight |
Reduce Goal Seek Quantity |
Revised Goal Seek Quantity |
Revised Order Net Weight |
|
A |
|
90.00 |
135.00 |
5.96% |
72.00 |
9.00 |
7.95 |
10 |
10 |
20 |
180.00 |
45.00 |
|
20 |
180.00 |
|
B |
|
28.80 |
115.20 |
5.08% |
61.00 |
7.20 |
8.48 |
8 |
4 |
12 |
86.40 |
28.80 |
8 |
20 |
144.00 |
|
C |
|
1680.00 |
2016.00 |
88.96% |
1069.00 |
168.00 |
6.36 |
6 |
10 |
16 |
2688.00 |
336.00 |
|
16 |
2688.00 |
|
|
Total |
1798.80 |
2266.20 |
100.00% |
1201.00 |
|
|
|
|
|
2954.40 |
|
|
|
3012.00 |
|
|
Cumulative Remainder Goal Seek Net Weight |
1201.20 |
(1065.00) |
|
|
|
|
|
|
Initial Excess |
(45.60) |
|
|
Revised Excess |
12.00 |
|
|
Allocation Net Weight |
|
1201.20 |
|
|
|
|
|
|
|
|
|
|
|
|
The order net weights are calculated as follows:
The total past due net weight for the three products is 1,798.80 kg, which is less than the 3,000.00 kg for the goal seek rule by 1,201.20 kg. So, the software continues to the next period (Period 1).
The sum of the total net weights for Period 1 and the past due period is greater than the net weight for the goal seek rule, so allocation is performed based on the net weights for Period 1.
The allocation percentages are calculated based on the net weights for Period 1.
The allocation percentages are used to calculate new allocation net weights, which total the 1201.20 kg needed for the goal seek rule.
The allocation quantities are calculated as the allocation net weight divided by the unit net weight.
The allocation quantities are rounded to meet the minimum purchase and purchase increment quantities for each product.
The initial goal seek quantities are calculated as the sum of the rounded quantities and the prior period quantities.
The initial order net weights are calculated based on the unit net weights and initial goal seek quantities.
The initial excess is calculated as the sum of the initial order net weights minus the net weights for the goal seek rule. There is a deficit.
The purchase increment quantity is multiplied by the unit net weight for each product. These net weights are then compared to the initial excess to find the largest of these net weights that can be increased to reach the net weight for the goal seek rule.
The revised goal seek quantities are calculated.
The revised order net weights are calculated based on the revised goal seek quantities.
A goal seek rule is created with the following settings:
Measure: Gross Weight
Gross Weight Unit of Measure: Kilograms
Value: 35,000
The following data exists for the products:
|
Product |
Purchase Unit of Measure |
Unit Gross Weight |
Past Due Period Quantity |
Period 1 Quantity |
Total Quantity |
Minimum Purchase Quantity |
Purchase Increment Quantity |
|
A |
Case12 |
1100.00 |
10 |
15 |
25 |
10 |
5 |
|
B |
Case12 |
1300.00 |
4 |
16 |
20 |
4 |
4 |
|
C |
Case12 |
1200.00 |
10 |
12 |
22 |
10 |
2 |
The goal seek calculations are performed as follows:
|
Product |
|
Past Due Period Gross Weight |
Period 1 Gross Weight |
Allocation Percentage |
Allocation Gross Weight |
Unit Gross Weight |
Calculated Allocation Quantity |
Calculated Allocated Quantity Rounded for Minimum Purchase/Purchase Increment |
Prior Period Quantity |
Initial Goal Seek Quantity |
Initial Order Gross Weight |
Multiple x Unit Gross Weight |
Reduce Goal Seek Quantity |
Revised Goal Seek Quantity |
Revised Order Gross Weight |
|
A |
|
11000.00 |
16500.00 |
31.91% |
2170.00 |
1100.00 |
1.97 |
5 |
10 |
15 |
16500.00 |
5500.00 |
(5) |
10 |
11000.00 |
|
B |
|
5200.00 |
20800.00 |
40.23% |
2736.00 |
1300.00 |
2.10 |
4 |
4 |
8 |
10400.00 |
5200.00 |
|
8 |
10400.00 |
|
C |
|
12000.00 |
14400.00 |
27.85% |
1894.00 |
1200.00 |
1.58 |
2 |
10 |
12 |
14400.00 |
2400.00 |
|
12 |
14400.00 |
|
|
Total |
28200.00 |
51700.00 |
100.00% |
6800.00 |
|
|
|
|
|
41300.00 |
|
|
|
35800.00 |
|
|
Cumulative Remainder Goal Seek Gross Weight |
6800.00 |
(44900.00) |
|
|
|
|
|
|
Initial Excess |
6300.00 |
|
|
Revised Excess |
800.00 |
|
|
Allocation Gross Weight |
|
6800.00 |
|
|
|
|
|
|
|
|
|
|
|
|
The order gross weights are calculated as follows:
The total past due gross weight for the three products is 28,200.00 kg, which is less than the 35,000.00 kg for the goal seek rule by 6,800.00 kg. So, the software continues to the next period (Period 1).
The sum of the total gross weights for Period 1 and the past due period is greater than the gross weight for the goal seek rule, so allocation is performed based on the gross weights for Period 1.
The allocation percentages are calculated based on the gross weights for Period 1.
The allocation percentages are used to calculate new allocation gross weights, which total the 6,800.00 kg needed for the goal seek rule.
The allocation quantities are calculated as the allocation gross weights divided by the unit gross weights.
The allocation quantities are rounded to meet the minimum purchase and purchase increment quantities for each product.
The initial goal seek quantities are calculated as the sum of the rounded quantities and the prior period quantities.
The initial order gross weights are calculated based on the unit gross weights and initial goal seek quantities.
The initial excess is calculated as the sum of the initial order gross weights minus the gross weight for the goal seek rule.
The purchase increment quantity is multiplied by the unit gross weight for each product. These gross weights are then compared to the initial excess to find the largest of these gross weights that can be reduced to minimize excess.
The revised goal seek quantities are calculated.
The revised order gross weights are calculated based on the revised goal seek quantities.
A goal seek rule is created with the following settings:
Measure: Units
Value: 50.00
The following data exists for the products:
|
Product |
Purchase Unit of Measure |
Unit Equivalent |
Past Due Period Quantity |
Period 1 Quantity |
Total Quantity |
Minimum Purchase Quantity |
Purchase Increment Quantity |
|
A |
Case6 |
1.00 |
10 |
15 |
25 |
10 |
5 |
|
B |
Case12 |
1.00 |
4 |
16 |
20 |
4 |
4 |
|
C |
Case24 |
2.00 |
10 |
12 |
22 |
10 |
2 |
The goal seek calculations are performed as follows:
|
Product |
|
Past Due Period Units |
Period 1 Units |
Allocation Percentage |
Allocation Units |
Unit Equivalent |
Calculated Allocation Quantity |
Calculated Allocated Quantity Rounded for Minimum Purchase/Purchase Increment |
Prior Period Quantity |
Initial Goal Seek Quantity |
Initial Order Units |
Multiple x Unit Equivalent |
Reduce Goal Seek Quantity |
Revised Goal Seek Quantity |
Revised Order Units |
|
A |
|
10.00 |
15.00 |
27.27% |
4.00 |
1.00 |
4.00 |
5 |
10 |
15 |
15.00 |
5.00 |
(5) |
10 |
10.00 |
|
B |
|
4.00 |
16.00 |
29.09% |
5.00 |
1.00 |
5.00 |
8 |
4 |
12 |
12.00 |
4.00 |
|
12 |
12.00 |
|
C |
|
20.00 |
24.00 |
43.64% |
7.00 |
2.00 |
3.50 |
4 |
10 |
14 |
28.00 |
4.00 |
|
14 |
28.00 |
|
|
Total |
34.00 |
55.00 |
100.00% |
16.00 |
|
|
|
|
|
55.00 |
|
|
|
50.00 |
|
|
Cumulative Remainder Goal Seek Units |
16.00 |
(39.00) |
|
|
|
|
|
|
Initial Excess |
5.00 |
|
|
Revised Excess |
0.00 |
|
|
Allocation Units |
|
16.00 |
|
|
|
|
|
|
|
|
|
|
|
|
The revised order units are calculated as follows:
The total past due units for the three products is 34.00 units, which is less than the 50.00 units for the goal seek rule by 16.00 units. So, the software continues to the next period (Period 1).
The sum of the total units for Period 1 and the past due period is greater than the units for the goal seek rule, so allocation is performed based on the units for Period 1.
The allocation percentages are calculated based on the units for Period 1.
The allocation percentages are used to calculate new allocation units, which total the 16.00 units needed for the goal seek rule.
The allocation quantities are calculated as the allocation units divided by the unit equivalent.
The allocation quantities are rounded to meet the minimum purchase and purchase increment quantities for each product.
The initial goal seek quantities are calculated as the sum of the rounded quantities and the prior period quantities.
The initial order units are calculated based on the unit equivalent and initial goal seek quantities.
The initial excess is calculated as the sum of the initial order units minus the units for the goal seek rule.
The purchase increment quantity is multiplied by the unit equivalent for each product. These values are then compared to the initial excess to find the largest of these values that can be reduced to minimize excess.
The revised goal seek quantities are calculated.
The revised order units are calculated based on the revised goal seek quantities. In this case, there is no excess.
A goal seek rule is created with the following settings:
Measure: Container
Container: Container X; this container type has no minimum requirements, but has a maximum weight requirement of 59,040 kilograms and 2,390 cubic meters
The following data exists for the products:
|
Product |
Purchase Unit of Measure |
Minimum Purchase Quantity |
Purchase Increment Quantity |
Unit Volume |
Unit Weight |
Units |
|
A |
Case12 |
10 |
5 |
47.5 |
1100 |
1 |
|
B |
Case12 |
4 |
4 |
86.4 |
1300 |
1 |
|
C |
Case12 |
10 |
2 |
22.08 |
1200 |
1 |
The purchase increment measurements for the products are as follows:
|
Product |
Volume |
Weight |
Units |
|
A |
237.5 |
5500.0 |
5.0 |
|
B |
345.6 |
5200.0 |
4.0 |
|
C |
44.2 |
2400.0 |
2.0 |
The data for the past due period is as follows:
|
Product |
Past Due Period Quantity |
|
Volume |
Weight |
Units |
|
A |
10 |
|
475.0 |
11000.0 |
10.0 |
|
B |
4 |
|
345.6 |
5200.0 |
4.0 |
|
C |
10 |
|
220.8 |
12000.0 |
10.0 |
|
|
|
Total |
1041.4 |
28200.0 |
24.0 |
|
|
|
Container Maximum for Goal Seek Rule |
2390.0 |
59040.0 |
|
|
|
|
Remainder Goal Seek Values |
1348.6 |
30840.0 |
|
The data for Period 1 is as follows:
|
Product |
First Period Quantity |
|
Volume |
Weight |
Units |
|
A |
15 |
|
712.5 |
16500.0 |
15.0 |
|
B |
16 |
|
1382.4 |
20800.0 |
16.0 |
|
C |
12 |
|
265.0 |
14400.0 |
12.0 |
|
|
|
Total |
2359.9 |
51700.0 |
43.0 |
|
|
|
Prior Period Totals |
1041.4 |
28200.0 |
24.0 |
|
|
|
All Periods Total |
3401.3 |
79900.0 |
67.0 |
|
|
|
Container Maximum for Goal Seek Rule |
2390.0 |
59040.0 |
|
|
|
|
Cumulative Remainder Goal Seek Values |
(1011.3) |
(20860.0) |
|
The volume allocation is calculated as follows:
|
Product |
|
Volume Allocation Percentage |
Allocation Volume |
Calculated Allocation Quantity |
Minimum Purchase/Purchase Increment Rounded Down Quantity |
|
A |
|
30.19% |
407.2 |
8.57 |
5.00 |
|
B |
|
58.58% |
790.0 |
9.14 |
8.00 |
|
C |
|
11.23% |
151.4 |
6.86 |
6.00 |
|
|
Total |
100% |
1348.6 |
|
|
The gross weight allocation is calculated as follows:
|
Product |
|
Weight Allocation Percentage |
Allocation Weight |
Calculated Allocation Quantity |
Minimum Purchase/Purchase Increment Rounded Down Quantity |
|
A |
|
31.91% |
9842.55 |
8.95 |
5.00 |
|
B |
|
40.23% |
12407.58 |
9.54 |
8.00 |
|
C |
|
27.85% |
8589.86 |
7.16 |
6.00 |
|
|
Total |
100% |
30840.00 |
|
|
The first iteration of goal seek calculations is as follows:
|
Product |
|
Minimum Purchase/Purchase Increment Rounded Down Quantity |
Extra Volume |
Extra Weight |
|
A |
|
5 |
237.5 |
5500.0 |
|
B |
|
8 |
691.2 |
10400.0 |
|
C |
|
6 |
132.48 |
7200.0 |
|
|
Total |
|
1061.2 |
23100.0 |
|
|
Prior Period Total |
|
1041.4 |
28200.0 |
|
|
All Periods Total |
|
2102.6 |
51300.0 |
|
|
Container Maximum for Goal Seek Rule |
|
2390.0 |
59040.0 |
|
|
Cumulative Remainder Goal Seek Values |
|
287.4 |
7740.0 |
The second iteration of goal seek calculations is as follows:
|
Product |
|
Increment |
Extra Volume |
Extra Weight |
|
A |
|
5 |
237.5 |
5500.0 |
|
B |
|
|
|
|
|
C |
|
|
|
|
|
|
Total |
|
237.5 |
5500 |
|
|
Prior Total |
|
2102.6 |
51300.0 |
|
|
New Total |
|
2340.1 |
56800.0 |
|
|
Container Maximum for Goal Seek Rule |
|
2390.0 |
59040.0 |
|
|
Cumulative Remainder Goal Seek Values |
|
49.9 |
2240.0 |
The third iteration of goal seek values is as follows:
|
Product |
|
Increment |
Extra Volume |
Extra Weight |
|
A |
|
|
|
|
|
B |
|
|
|
|
|
C |
|
2 |
44.2 |
2400.0 |
|
|
Total |
|
44.2 |
2400.0 |
|
|
Previous Total |
|
2340.1 |
56800.0 |
|
|
Total |
|
2384.2 |
59200.0 |
|
|
Container Maximum for Goal Seek Rule |
|
2390.0 |
59040.0 |
|
|
Remainder Goal Seek Values |
|
5.76 |
(160.00) |
The final goal seek calculations are as follows:
|
Product |
|
Quantity |
Volume |
Weight |
|
A |
|
20 |
950.0 |
22,000.0 |
|
B |
|
12 |
1036.8 |
15600.0 |
|
C |
|
16 |
353.3 |
19200.0 |
|
|
Total |
|
2340.1 |
56800.0 |
The goal seek quantities are calculated as follows:
The total past due period volume and weight are calculated for the three products. Both totals are less than the maximum values for the container selected for the goal seek rule. So, the software continues to the next period (Period 1).
The sum of the total volumes for Period 1 and the past due period is greater than the maximum volume for the container for the goal seek rule. This is also true for weights for both periods, so allocation is performed based on the values for Period 1.
The allocation percentages are calculated based on both the volumes and weights for Period 1.
The allocation percentages are used to calculate new allocation volumes and weights, which total the values needed for the goal seek rule.
The allocation quantities for the volume are calculated as the allocation volumes divided by the unit volumes.
The allocation quantities for the gross weight are calculated as the allocation weights divided by the unit gross weights.
All allocation quantities are rounded to meet the minimum purchase and purchase increment quantities for each product.
The allocation quantity for each product is calculated, and the extra volume and gross weight for these quantities is calculated.
The calculated volumes and gross weights are added to the prior period volumes and gross weights, and the results are the first iteration of goal seek values.
The first iteration excess is calculated as the first iteration values minus the values for the goal seek rule. This is a deficit.
The purchase increment quantity is multiplied by the volume and gross weight for each product. These values are then compared to the first iteration excess to find the largest of these values that can be increased to reach the goal seek values.
The second iteration goal seek values are calculated. There is still a deficit for both values.
The purchase increment quantity is multiplied by the volume and gross weight for each product. These values are then compared to the second iteration excess to find the largest of these values that can be increased to reach the goal seek values.
The third iteration goal seek values are calculated. There is now an excess gross weight, so the software does not use the volume and gross weight from the third iteration.
The final goal seek quantities are calculated based on the quantities from the past due period, the first iteration, and the second iteration.
Question: How do the Past Due Allowed to Exceed Goal Seek Limits and Include Open Purchase Orders settings affect goal seek calculations?
Answer: These settings affect calculations as follows:
|
Past Due Allowed to Exceed Goal Seek Limits Check Box |
Include Open Purchase Orders Check Box |
Description |
|
Selected |
Selected |
The system looks at open purchase order quantities, then past due quantities, and then quantities that are not past due. This includes all past due and open purchase order quantities in the goal seek calculation, regardless of whether the total exceeds the goal seek rule. The actual forecast quantity should not exceed the goal seek rule. |
|
Cleared |
Selected |
The system looks at open purchase order quantities, then past due quantities, and then quantities that are not past due. This includes all open purchase order quantities. The past due and actual forecast quantities should not exceed the goal seek rule. |
|
Selected |
Cleared |
The system looks at past due quantities, and then quantities that are not past due. The quantities that are not past due should not exceed the goal seek rule. Open purchase orders are ignored when using these settings. |
|
Cleared |
Cleared |
The system looks at past due quantities, and then quantities that are not past due. The goal seek rule is not exceeded. Open purchase orders are ignored when using these settings. |
The following is an example of how these settings change calculations:
A goal seek rule is created with the following settings:
Measure: Units
Value: 100.00
The following data exists for the products:
|
Product |
|
Purchase Unit of Measure |
Open Purchase Order Quantity |
Past Due Period Quantity |
Period 1 Quantity |
Period 2 Quantity |
Period 3 Quantity |
Total Quantity |
|
A |
|
Each |
25 |
50 |
10 |
15 |
|
100 |
|
B |
|
Each |
10 |
40 |
20 |
20 |
|
90 |
|
C |
|
Each |
|
30 |
30 |
15 |
|
75 |
|
|
Total |
|
35 |
120 |
60 |
50 |
0 |
265 |
The goal seek calculations are performed as follows:
|
Product |
|
Both Check Boxes Selected |
Include Open Purchase Orders Check Box Selected and Past Due Allowed to Exceed Goal Seek Limits Check Box Cleared |
Include Open Purchase Orders Check Box Cleared and Past Due Allowed to Exceed Goal Seek Limits Check Box Selected |
Both Check Boxes Cleared |
|
A |
|
75 |
53 |
50 |
42 |
|
B |
|
50 |
31 |
40 |
33 |
|
C |
|
30 |
16 |
30 |
25 |
|
|
Total |
155 |
100 |
120 |
100 |