如何按条件计算Adjustment 1调整值，控制分支完成率不超总完成率
2026-6-12
分支利润完成率调整分析
原始数据表格
| 分支编码 | 目标值(A) | 实际利润(B) | 调整项1(C) | 调整项2(D) | 调整后利润(E=B+C+D) | 进度率(F) | 完成率(G=((E-C)/F+C)/A) |
|---|---|---|---|---|---|---|---|
| 100 | 8,031.00 | 3,359.16 | 12.17 | 192.44 | 3,563.77 | 61% | 72.80% |
| 101 | 6,688.00 | 3,029.20 | - | 31.51 | 3,060.71 | 61% | 75.18% |
| 102 | 8,392.00 | 4,319.51 | 0.72 | 27.95 | 4,348.17 | 61% | 85.12% |
| 103 | 4,535.00 | 2,077.66 | - | 13.87 | 2,091.53 | 61% | 75.77% |
| 104 | 2,837.00 | 1,157.93 | 62.03 | 8.87 | 1,228.83 | 61% | 69.75% |
| 105 | 3,523.00 | 1,462.85 | 32.64 | 6.04 | 1,501.52 | 61% | 69.42% |
| 106 | 2,626.00 | 996.31 | 1.38 | 11.53 | 1,009.22 | 61% | 63.10% |
| 107 | 3,154.00 | 1,363.66 | 0.07 | 2.91 | 1,366.63 | 61% | 71.18% |
| 108 | 2,508.00 | 1,021.90 | - | 10.42 | 1,032.32 | 61% | 67.62% |
| 109 | 2,160.00 | 1,099.10 | 76.77 | (178.25) | 997.62 | 61% | 73.59% |
| 110 | 2,323.00 | 871.67 | - | 14.50 | 886.16 | 61% | 62.67% |
| 111 | 2,042.00 | 1,114.87 | - | 14.34 | 1,129.21 | 61% | 90.85% |
| 112 | 2,273.00 | 945.21 | - | 49.05 | 994.26 | 61% | 71.86% |
| 113 | 1,510.00 | 600.87 | - | 13.85 | 614.72 | 61% | 66.88% |
| 114 | 1,229.00 | 491.02 | - | 15.15 | 506.18 | 61% | 67.66% |
| 115 | 634.00 | 156.88 | - | 4.75 | 161.64 | 61% | 41.88% |
| 116 | 378.00 | 74.14 | - | 2.49 | 76.63 | 61% | 33.30% |
| 117 | 255.00 | 27.06 | - | 0.56 | 27.62 | 61% | 17.79% |
| 合计 | 55,098.00 | 24,168.99 | 185.77 | 241.98 | 24,596.74 | 61% | 73.12% |
调整需求
仅对存在调整项1的分支调整该数值，需满足两个条件：
- 分支完成率不超过合计完成率（73.12%）；
- 调整后的调整项1不得为负数。
调整逻辑与计算
基于完成率公式 G = ((E - C)/F + C)/A，代入 E = B + C + D 可推导为：C = G*A - (B+D)/F。以合计完成率73.12%为上限，计算各分支允许的最大调整项1值，同时确保该值≥0；若原分支完成率已低于上限，则无需调整。
各分支调整情况
- 分支100：原完成率72.80% < 73.12%，无需调整，调整项1保持12.17；
- 分支102：原完成率85.12% > 73.12%，计算得允许的最大调整项1为负数，因此调整为0（已达调整项1的最低非负值）；
- 分支104：原完成率69.75% < 73.12%，无需调整，调整项1保持62.03；
- 分支105：原完成率69.42% < 73.12%，无需调整，调整项1保持32.64；
- 分支106：原完成率63.10% < 73.12%，无需调整，调整项1保持1.38；
- 分支107：原完成率71.18% < 73.12%，无需调整，调整项1保持0.07；
- 分支109：原完成率73.59% > 73.12%，计算得允许的最大调整项1约为69.80，调整后完成率刚好为73.12%，满足要求。
调整后数据表格
| 分支编码 | 目标值(A) | 实际利润(B) | 调整项1(C) | 调整项2(D) | 调整后利润(E=B+C+D) | 进度率(F) | 完成率(G=((E-C)/F+C)/A) |
|---|---|---|---|---|---|---|---|
| 100 | 8,031.00 | 3,359.16 | 12.17 | 192.44 | 3,563.77 | 61% | 72.80% |
| 101 | 6,688.00 | 3,029.20 | - | 31.51 | 3,060.71 | 61% | 75.18% |
| 102 | 8,392.00 | 4,319.51 | 0 | 27.95 | 4,347.46 | 61% | 84.93% |
| 103 | 4,535.00 | 2,077.66 | - | 13.87 | 2,091.53 | 61% | 75.77% |
| 104 | 2,837.00 | 1,157.93 | 62.03 | 8.87 | 1,228.83 | 61% | 69.75% |
| 105 | 3,523.00 | 1,462.85 | 32.64 | 6.04 | 1,501.52 | 61% | 69.42% |
| 106 | 2,626.00 | 996.31 | 1.38 | 11.53 | 1,009.22 | 61% | 63.10% |
| 107 | 3,154.00 | 1,363.66 | 0.07 | 2.91 | 1,366.63 | 61% | 71.18% |
| 108 | 2,508.00 | 1,021.90 | - | 10.42 | 1,032.32 | 61% | 67.62% |
| 109 | 2,160.00 | 1,099.10 | 69.80 | (178.25) | 990.65 | 61% | 73.12% |
| 110 | 2,323.00 | 871.67 | - | 14.50 | 886.16 | 61% | 62.67% |
| 111 | 2,042.00 | 1,114.87 | - | 14.34 | 1,129.21 | 61% | 90.85% |
| 112 | 2,273.00 | 945.21 | - | 49.05 | 994.26 | 61% | 71.86% |
| 113 | 1,510.00 | 600.87 | - | 13.85 | 614.72 | 61% | 66.88% |
| 114 | 1,229.00 | 491.02 | - | 15.15 | 506.18 | 61% | 67.66% |
| 115 | 634.00 | 156.88 | - | 4.75 | 161.64 | 61% | 41.88% |
| 116 | 378.00 | 74.14 | - | 2.49 | 76.63 | 61% | 33.30% |
| 117 | 255.00 | 27.06 | - | 0.56 | 27.62 | 61% | 17.79% |
| 合计 | 55,098.00 | 24,168.99 | 178.09 | 241.98 | 24,589.06 | 61% | 73.09% |
内容的提问来源于stack exchange，提问作者hoa tran
相关产品推荐
相关产品推荐
