管理決策模型4-13 一個兩種產(chǎn)品的最優(yōu)產(chǎn)品混合問題

1、機器小時上限68250產(chǎn)品A產(chǎn)品B人工小時上限47250單價（元）11.316.4單位可變成本（元/件）6.310固定成本（元）300700機器小時/件76120人工小時/件8560產(chǎn)品A產(chǎn)品B單價（元）11.316.4單位可變成本（元/件）6.310固定成本（元）300700機器小時/件76120人工小時/件8560機器小時約束直線0898.03in D10: =D9+D4*D8 in D11:產(chǎn)品A產(chǎn)品B568.750in D12:產(chǎn)品銷售數(shù)量279.26391.89需要量可提供量人工小時約束直線機器小時7612068250682500555.88人工小時85604725047250787

2、.50in K2:=G5/D5in J3:單價11.316.4兩條約束直線的交點單位可變成本6.310279.26固定成本300700391.89in J8:J9:=MMULT(MINVERSE(D5:E6),G5:G6)總成本2059.34618.9銷售收益3155.66427.0總利潤利潤1096.31808.12904.36$2904.36 =MAX($F$12)2 =COUNT($D$4:$E$4)FALSE=$F$5:$F$6=$G$5:$G$6100 =100,100,0.000001,0.05,TRUE,FALSE,FALSE,1,1,1,0.001,TRUEC1/a1C1/b1

3、C2/a2C2/b2QaQba1b1C1a2b2C2A BCDEFGH IJKLMNOP123456789101112131415161718192021222324252627282930313233=D4*D7in F5:=D5*$D$4+E5*$E$4=D11-D10 in F12: =D12+E12=G5/E5in K5:=G6/D6in J6:=G6/E6=MMULT(MINVERSE(D5:E6),G5:G6)QRSTUVW123456789101112131415161718192021222324252627282930313233產(chǎn)品A 產(chǎn)品B4443100100020003

4、00040005000in D6:=D3-D4in E10:單價11.316.4082040060080010001200單位可變成本6.3101000-460-880-680-480-280-80固定成本300700單位貢獻56.4總利潤p02500產(chǎn)品B銷量Qb200產(chǎn)品B利潤pb580產(chǎn)品A利潤pa1920產(chǎn)品A銷量Qa444QaQba1b1a2b2Qbp0)(10babbbaaaFFQvpvpQA BCDEF GHIJKLMNOPQRS1234567891011121314151617181920212223242526=E6*E9-E5in E11:=E8-E10in E12:=(E

5、11+D5)/D6)(10babbbaaaFFQvpvpQTUVWXYZ1234567891011121314151617181920212223242526C1/a1C1/b1C2/a2C2/b2兩產(chǎn)品混合問題線性規(guī)劃圖解100080060040020000.00200.00400.00600.00800.001000.00機器小時約束人工小時約束QbQap0 =3100p0 =3100a2 =85p0 =31002903.80產(chǎn)品A產(chǎn)品B2產(chǎn)品銷售數(shù)量279392需要量可提供量TRUE機器小時761206824468250TRUE人工小時85604723547250100單價11.316.4單位可變成本6.310固定成本300700總成本2057.704620.00銷售收益3152.706428.80總利潤利潤1095.001808.802903.80A BCDEFGH IJ12345678910111213=MAX($F$12)=COUNT($D$4:$E$4)=$F$5:$F$6=$G$5:$G$6=$D$4:$E$4=I