决策分析例子之3：是否接受价格折扣
| |
|
|
|
|
|
| |
打折扣订货门限 |
|
|
300 |
|
| |
折扣百分比 |
|
|
3.0% |
# |
| |
每次订货的订货成本 |
|
|
25 |
|
| |
一件商品保存一年的储存成本 |
17 |
|
| |
年需求量 |
|
|
1000 |
|
| |
名义单价 |
|
|
50 |
|
| |
实际单价 |
|
|
48.5 |
|
| |
|
|
EOQ |
|
|
| |
订货量 |
|
54.2 |
300 |
|
| |
年采购成本 |
|
50000.0 |
48500.0 |
|
| |
年订货成本 |
|
461.0 |
83.3 |
|
| |
年储存成本 |
|
461.0 |
2550.0 |
|
| |
年总成本 |
|
50922.0 |
51133.3 |
|
| |
|
|
|
|
|
| |
接受折扣优惠的利益 |
|
|
-211.38 |
|
| |
|
|
|
|
|
| |
|
|
|
|
|
| |
不接受优惠条件 |
|
|
|
|
| |
|
51133 |
|
| |
10 |
52585 |
|
| |
20 |
51420 |
|
| |
30 |
51088 |
|
| |
40 |
50965 |
|
| |
50 |
50925 |
|
| |
60 |
50927 |
|
| |
70 |
50952 |
|
| |
80 |
50993 |
|
| |
90 |
51043 |
|
| |
100 |
51100 |
|
| |
110 |
51162 |
|
| |
120 |
51228 |
|
| |
130 |
51297 |
|
| |
140 |
51369 |
|
| |
150 |
51442 |
|
| |
300 |
52633 |
|
| |
300 |
51133 |
|
| |
500 |
52800 |
|
| |
|
|
|
| in F9: |
=IF(F11<F3,F8,F8*(1-F4)) |
|
in E13: |
=$F$5*$F$7/E11 |
| in E11: |
=SQRT(2*F5*F7/F6) |
|
in E14: |
=$F$6*E11/2 |
| in E12: |
=F7*F8 |
|
in E15: |
=E12+E13+E14 |
| in F12: |
=F7*F9 |
|
in F17: |
=E15-F15 |
| in C20: |
=IF(F17<=0,"不接受优惠条件","接受优惠条件") |
|
|
|
| in C21: |
="最优订货量为" & IF(F17<=0,E10,F10) |
|
|
|
| |
|
|
|
|
| |
|
|
|
|
| in J18: |
=$F$3-0.0001 |
|
|
|
| in J19: |
=$F$3 |
|
|
|
|
