最小成本方案模型
A B 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 C D E F G H I J K L M N O P Q Min5006763in D7:in D8:产品生产数量750017方案A方案B方案C5001.713250368467631000011500571410214400085000.750.3962510750132500500368467639625107504000850067639605固定成本单位可变成本总成本A-B盈亏平衡点A-B共同成本B-C盈亏平衡点B-C共同成本C-A盈亏平衡点C-A共同成本10000175001150011500115001600027700160001330013300第一转折第二转折点点流动参考线in F12:in F13:36841000036841000075000750018000in E18:in E19:inK9:K10:最省生产方案最小总成本方案B9625第一转第二转折点折点最小成本曲线转折点横坐标最小成本曲线转折点纵坐标368410000676311500最省生产方案为方案BPage 1
最小成本方案模型
R 1 2 =D5+D6*$F$33 =(E5-D5)/(D6-E6)4 5 =(F5-D5)/(D6-F6)6 =F5+F6*F127 8 9 10 11 =MIN(D8,F12)=VLOOKUP(E18,J12 3:N6,5)S T U V W in E11:X =E5+E6*E10in D9:=D5+D6*D8in E10:=(F5-E5)/(E6-F6)=INDEX(D4:F4,MATCH(F16,D7:F7,in F15:0))in F16:=MIN(D7:F7)in C22:=C15 & \为\in F18:=MAX(E10,E18)=VLOOKUP(F18,Jin F19:3:N6,5)inM9:M10:=$F$313 14 =$E$1815 16 17 18 19 20 21 22 inL9:L10:=$F$18Page 2
模型1-9 最小成本方案的确定
