1、11 產(chǎn)品生產(chǎn)方案的優(yōu)化設(shè)計(jì) 在限定的生產(chǎn)資源條件下以最低的成本獲取最大的利潤(rùn)，就是企業(yè)生產(chǎn)的主要目的，因此在進(jìn)行產(chǎn)品生產(chǎn)之前，企業(yè)的生產(chǎn)部門需要對(duì)產(chǎn)品的成本、生產(chǎn)時(shí)間、銷售利潤(rùn)及其他方面的因素進(jìn)行進(jìn)行綜合評(píng)估，從而找到一個(gè)能夠使生產(chǎn)成本最小而利潤(rùn)最大的生產(chǎn)方案。1企業(yè)產(chǎn)品生產(chǎn)方案優(yōu)化設(shè)計(jì)效果211.2 任務(wù)分析11.3 產(chǎn)品生產(chǎn)方案優(yōu)化設(shè)計(jì) 拓 展 實(shí) 訓(xùn) 綜 合 實(shí) 踐11.1 任務(wù)情境 目 錄311.1 任務(wù)情境 公司新上三種產(chǎn)品的生產(chǎn)，經(jīng)考察和初步試生產(chǎn)得知：生產(chǎn)產(chǎn)品1、產(chǎn)品2、產(chǎn)品3的成本分別為55元、85元和115元，每生產(chǎn)一件產(chǎn)品的生產(chǎn)時(shí)間分別耗費(fèi)3分鐘、6分鐘、7分鐘，銷售一件

2、產(chǎn)品分別獲利50元、70元、95元?，F(xiàn)在研究決定，企業(yè)為這三種產(chǎn)品最多投入15 000元的原材料經(jīng)費(fèi)，機(jī)器設(shè)備每天運(yùn)轉(zhuǎn)時(shí)間不能超過(guò)15個(gè)小時(shí)，同時(shí)根據(jù)市場(chǎng)需求產(chǎn)品1每天產(chǎn)量不得少于60件，產(chǎn)品2每天產(chǎn)量不得少于45件，產(chǎn)品3每天產(chǎn)量不得少于60件。生產(chǎn)部主任要求剛工作不久的小張立即設(shè)計(jì)出下一步最優(yōu)的生產(chǎn)方案，也就是根據(jù)現(xiàn)有的生產(chǎn)條件和市場(chǎng)需求如何分配三種產(chǎn)品的生產(chǎn)比例才能獲得最大的利潤(rùn)。這么多復(fù)雜的條件，單靠直觀的判斷很難找到最優(yōu)的方案，這可愁壞了小張，他知道公司的小王懂得多，就去請(qǐng)教，小王一看，簡(jiǎn)單！很快就解決了。小王是怎么幫助小張快速解決的呢？讓我們也跟他學(xué)一招吧！4知識(shí)目標(biāo)和能力目標(biāo)知識(shí)

3、目標(biāo)：掌握Excel加載附加分析工具的方法。掌握規(guī)劃求解的使用方法。掌握函數(shù)SUMPRODUCT（）的使用方法。能力目標(biāo)：能夠根據(jù)實(shí)際規(guī)劃問(wèn)題進(jìn)行分析，建立規(guī)劃模型。能夠應(yīng)用規(guī)劃求解的方法求得規(guī)劃問(wèn)題的最優(yōu)解。511.2 任務(wù)分析 這是在生產(chǎn)管理和經(jīng)營(yíng)決策過(guò)程中，如何合理地利用有限的人力、物力、財(cái)力等資源，得到最佳的經(jīng)濟(jì)效果，即達(dá)到產(chǎn)量最高、利潤(rùn)最大、成本最小、資源消耗最少等目標(biāo)的問(wèn)題，這類問(wèn)題可以統(tǒng)稱為規(guī)劃問(wèn)題，解決這樣的規(guī)劃問(wèn)題應(yīng)用Excel的規(guī)劃求解工具，可以方便快捷地得到問(wèn)題的最優(yōu)解。 經(jīng)過(guò)分析，設(shè)計(jì)出這三種產(chǎn)品的最優(yōu)生產(chǎn)方案需要進(jìn)行以下工作：根據(jù)問(wèn)題的描述建立規(guī)劃模型。根據(jù)規(guī)劃模型建

4、立工作表加載規(guī)劃求解工具應(yīng)用規(guī)劃求解工具求解分析規(guī)劃求解結(jié)果。611.3產(chǎn)品生產(chǎn)方案優(yōu)化設(shè)計(jì)11.3.3分析規(guī)劃求解結(jié)果11.3.1建立問(wèn)題的規(guī)劃模型11.3.2應(yīng)用規(guī)劃求解求得最優(yōu)生產(chǎn)方案711.3.1 建立問(wèn)題的規(guī)劃模型1.分析問(wèn)題并將生產(chǎn)條件整理如下表所示811.3.1 建立問(wèn)題的規(guī)劃模型2.將約束條件整理如下表所示911.3.1 建立問(wèn)題的規(guī)劃模型3.建立問(wèn)題的規(guī)劃模型 假設(shè)如果在現(xiàn)有條件下獲得最大利潤(rùn)時(shí)產(chǎn)品1、產(chǎn)品2、產(chǎn)品3每天的產(chǎn)量應(yīng)為X1、X2、X3，總利潤(rùn)為S，則可以根據(jù)上表的約束條件建立規(guī)劃模型如下：1011.3產(chǎn)品生產(chǎn)方案優(yōu)化設(shè)計(jì)11.3.1 建立問(wèn)題的規(guī)劃模型11.3.3

5、 分析規(guī)劃求解結(jié)果11.3.2應(yīng)用規(guī)劃求解求得最優(yōu)生產(chǎn)方案1111.3.2 應(yīng)用規(guī)劃求解求得最優(yōu)生產(chǎn)方案1.加載規(guī)劃求解工具步驟1：?jiǎn)螕舸翱谧笊辖堑摹疚募堪粹o，在彈出的菜單中執(zhí)行【選項(xiàng)】命令。1211.3.2 應(yīng)用規(guī)劃求解求得最優(yōu)生產(chǎn)方案1.加載規(guī)劃求解工具步驟2：彈出【Excel選項(xiàng)】對(duì)話框，單擊【加載項(xiàng)】選項(xiàng)，然后在【管理】下拉列表框中的選擇【Excel加載項(xiàng)】選項(xiàng)，單擊【轉(zhuǎn)到】按鈕，如左圖所示，彈出【加載宏】對(duì)話框，如右圖所示。1311.3.2 應(yīng)用規(guī)劃求解求得最優(yōu)生產(chǎn)方案1.加載規(guī)劃求解工具步驟3：在【加載宏】對(duì)話框中勾選【規(guī)劃求解加載項(xiàng)】復(fù)選項(xiàng)，然后單擊【確定】按鈕。加載規(guī)劃求解加

6、載宏后，在【數(shù)據(jù)】選項(xiàng)卡的【分析】選項(xiàng)組中就出現(xiàn)【規(guī)劃求解】命令了，如下圖所示。1411.3.2 應(yīng)用規(guī)劃求解求得最優(yōu)生產(chǎn)方案2.建立工作表步驟1：新建工作簿“產(chǎn)品生產(chǎn)方案優(yōu)化設(shè)計(jì).xlsx”，把Sheet1重命名為“規(guī)劃求解確定最優(yōu)生產(chǎn)方案”。步驟2：輸入規(guī)劃求解的基本項(xiàng)目及約束條件，并對(duì)工作表格式化，添加邊框和底紋修飾使數(shù)據(jù)更清晰，如下圖所示。1511.3.2 應(yīng)用規(guī)劃求解求得最優(yōu)生產(chǎn)方案2.建立工作表步驟3：輸入計(jì)算毛利合計(jì)公式毛利合計(jì)=單位利潤(rùn)產(chǎn)量 在C7單元格中輸入公式：“=C5*C6”，按回車鍵計(jì)算產(chǎn)品1的毛利合計(jì)，如下圖所示。向右拖動(dòng)填充柄，復(fù)制公式到E7單元格，計(jì)算產(chǎn)品2和產(chǎn)品

7、3的毛利合計(jì)值。1611.3.2 應(yīng)用規(guī)劃求解求得最優(yōu)生產(chǎn)方案2.建立工作表步驟4：輸入計(jì)算實(shí)際生產(chǎn)成本公式實(shí)際生產(chǎn)成本=產(chǎn)品1每件耗費(fèi)成本產(chǎn)量+產(chǎn)品2每件耗費(fèi)成本產(chǎn)量+產(chǎn)品3每件耗費(fèi)成本產(chǎn)量，選中C15單元格，輸入以下函數(shù)：=SUMPRODUCT(C3:E3,C6:E6)。其功能等價(jià)于公式：=C3*C6+D3*D6+E3*E6。 按Enter鍵計(jì)算實(shí)際生產(chǎn)成本。1711.3.2 應(yīng)用規(guī)劃求解求得最優(yōu)生產(chǎn)方案2.建立工作表步驟4：輸入計(jì)算實(shí)際生產(chǎn)成本公式如圖所示。1811.3.2 應(yīng)用規(guī)劃求解求得最優(yōu)生產(chǎn)方案2.建立工作表步驟5：輸入計(jì)算實(shí)際生產(chǎn)時(shí)間公式實(shí)際生產(chǎn)時(shí)間=產(chǎn)品1每件生產(chǎn)時(shí)間產(chǎn)量+產(chǎn)

8、品2每件生產(chǎn)時(shí)間產(chǎn)量+產(chǎn)品3每件生產(chǎn)時(shí)間產(chǎn)量選中C16單元格，輸入以下函數(shù)：=SUMPRODUCT(C4:E4,C6:E6) 其功能等價(jià)于公式=C4*C6+D4*D6+E4*E6。按Enter鍵計(jì)算實(shí)際生產(chǎn)時(shí)間。1911.3.2 應(yīng)用規(guī)劃求解求得最優(yōu)生產(chǎn)方案2.建立工作表步驟5：輸入計(jì)算實(shí)際生產(chǎn)時(shí)間公式如圖所示2011.3.2 應(yīng)用規(guī)劃求解求得最優(yōu)生產(chǎn)方案2.建立工作表步驟6：輸入計(jì)算利潤(rùn)的公式利潤(rùn)=產(chǎn)品1毛利合計(jì)+產(chǎn)品2毛利合計(jì)+產(chǎn)品3毛利合計(jì)選中E12單元格，插入求和函數(shù)：=SUM(C7:E7)，按Enter鍵計(jì)算利潤(rùn)。步驟7：為了和最優(yōu)解比較利潤(rùn)大小，輸入約束條件中產(chǎn)量最小值即60、45

9、、60作為產(chǎn)量初值，觀察記錄利潤(rùn)結(jié)果為“11850”，如圖所示。2111.3.2 應(yīng)用規(guī)劃求解求得最優(yōu)生產(chǎn)方案3.應(yīng)用規(guī)劃求解，求得利潤(rùn)最大化生產(chǎn)方案 步驟1：選中目標(biāo)單元格C17，切換到【數(shù)據(jù)】選項(xiàng)卡，單擊【分析】選項(xiàng)組下的【規(guī)劃求解】按鈕，如下圖所示，彈出【規(guī)劃求解參數(shù)】對(duì)話框，如下圖所示。2211.3.2 應(yīng)用規(guī)劃求解求得最優(yōu)生產(chǎn)方案3.應(yīng)用規(guī)劃求解，求得利潤(rùn)最大化生產(chǎn)方案步驟2：設(shè)置目標(biāo)函數(shù)2311.3.2 應(yīng)用規(guī)劃求解求得最優(yōu)生產(chǎn)方案3.應(yīng)用規(guī)劃求解，求得利潤(rùn)最大化生產(chǎn)方案步驟3：設(shè)置可變單元格，即決策變量步驟4：設(shè)置約束條件添加實(shí)際成本約束條件，實(shí)際生產(chǎn)成本應(yīng)小于等于生產(chǎn)成本約束。

10、單擊【約束】文本框旁的【添加】按鈕，彈出【添加約束】對(duì)話框，如下圖所示，單擊【單元格引用位置】文本框，在工作表中選擇C15單元格，在中間的下拉列表中選擇【=】選項(xiàng)，單擊右側(cè)的【約束值】文本框，在工作表中選擇C11單元格。單擊【添加】按鈕，繼續(xù)彈出【添加約束】對(duì)話框，添加其他條件。2411.3.2 應(yīng)用規(guī)劃求解求得最優(yōu)生產(chǎn)方案3.應(yīng)用規(guī)劃求解，求得利潤(rùn)最大化生產(chǎn)方案步驟4：設(shè)置約束條件添加實(shí)際生產(chǎn)時(shí)間約束條件，實(shí)際生產(chǎn)時(shí)間應(yīng)小于等于生產(chǎn)時(shí)間約束。在彈出的【添加約束】對(duì)話框中，按上述方法設(shè)置實(shí)際生產(chǎn)時(shí)間約束條件如下圖所示。然后單擊【添加】按鈕，再次彈出【添加約束】對(duì)話框。2511.3.2 應(yīng)用規(guī)劃

11、求解求得最優(yōu)生產(chǎn)方案3.應(yīng)用規(guī)劃求解，求得利潤(rùn)最大化生產(chǎn)方案步驟4：設(shè)置約束條件添加產(chǎn)量約束條件產(chǎn)品產(chǎn)量應(yīng)大于等于產(chǎn)量約束值且必須為整數(shù)。2611.3.2 應(yīng)用規(guī)劃求解求得最優(yōu)生產(chǎn)方案3.應(yīng)用規(guī)劃求解，求得利潤(rùn)最大化生產(chǎn)方案步驟4：設(shè)置約束條件所有約束條件添加完成后單擊【確定】按鈕，返回【規(guī)劃求解參數(shù)】對(duì)話框，如下圖所示。在【約束】列表框中可以看到所有添加的條件。2711.3.2 應(yīng)用規(guī)劃求解求得最優(yōu)生產(chǎn)方案3.應(yīng)用規(guī)劃求解，求得利潤(rùn)最大化生產(chǎn)方案步驟5：在【規(guī)劃求解參數(shù)】對(duì)話框中，單擊【求解】按鈕，彈出【規(guī)劃求解結(jié)果】對(duì)話框，如下圖所示2811.3.2 應(yīng)用規(guī)劃求解求得最優(yōu)生產(chǎn)方案3.應(yīng)用規(guī)

12、劃求解，求得利潤(rùn)最大化生產(chǎn)方案步驟6：在【規(guī)劃求解結(jié)果】對(duì)話框中，選擇【保留規(guī)劃求解的解】選項(xiàng)，并在【報(bào)告】列表框中選擇【運(yùn)算結(jié)果報(bào)告】選項(xiàng)，單擊【確定】按鈕可以看到規(guī)劃求解后的結(jié)果，如下圖所示。并生成“運(yùn)算結(jié)果報(bào)告1”工作表。2911.3 產(chǎn)品生產(chǎn)方案優(yōu)化設(shè)計(jì)11.3.2 應(yīng)用規(guī)劃求解求得最優(yōu)生產(chǎn)方案11.3.1 建立問(wèn)題的規(guī)劃模型11.3.3 分析規(guī)劃求解結(jié)果3011.3.3 分析規(guī)劃求解結(jié)果1 分析運(yùn)算結(jié)果報(bào)告運(yùn)算結(jié)果報(bào)告1”工作表3111.3.3 分析規(guī)劃求解結(jié)果1 分析運(yùn)算結(jié)果報(bào)告從運(yùn)算結(jié)果報(bào)告1工作表中可以看到：“目標(biāo)單元格（最大值）”初值“11850”，終值“12350”。“可變

13、單元格”三種產(chǎn)品產(chǎn)量的初值和終值?！凹s束”列表中，實(shí)際生產(chǎn)成本未達(dá)到限制值，也就是計(jì)劃投入成本的還剩余425元， 實(shí)際生產(chǎn)時(shí)間已經(jīng)達(dá)到限制值15小時(shí)。根據(jù)以上信息可以得出以下結(jié)論：生產(chǎn)時(shí)間再增加一個(gè)小時(shí)，讓投入的成本達(dá)到限制值則會(huì)使利潤(rùn)得到更大的提高。3211.3.3 分析規(guī)劃求解結(jié)果2修改生產(chǎn)時(shí)間約束條件為16小時(shí)，即960分鐘，重新規(guī)劃求解，結(jié)果如圖所示。可以看到實(shí)際生產(chǎn)時(shí)間只要每天增加21分鐘，每天利潤(rùn)就又增加12700-12350=350元，實(shí)際成本達(dá)到14960，幾乎達(dá)到限制值，這可真正做到了以有限的成本得到最大的利潤(rùn)。33 拓 展 實(shí) 訓(xùn)實(shí)訓(xùn)1：混合飼料的最低成本配方設(shè)計(jì)要求如下：

14、 一家企業(yè)要生產(chǎn)某種混合飼料，規(guī)定所含營(yíng)養(yǎng)成分蛋白質(zhì)至少15%，脂肪至少4.5%，淀粉至少30%，但是維生素D不能超過(guò)10%，由四種原料混合而成，其中原料甲成本每噸480元，原料乙成本每噸40元，原料丙成本每噸300元，原料丁成本每噸450元，每種原料所含的各種營(yíng)養(yǎng)成分如表所示。設(shè)計(jì)生產(chǎn)這種混合飼料的配方，既能保證混合飼料的各營(yíng)養(yǎng)成分含量，又能使成本最低，從而獲取更大的利潤(rùn)。34 拓 展 實(shí) 訓(xùn)實(shí)訓(xùn)1：混合飼料的最低成本配方設(shè)計(jì)35 拓 展 實(shí) 訓(xùn)求解結(jié)果36 拓 展 實(shí) 訓(xùn)實(shí)訓(xùn)2：最低運(yùn)輸成本方案設(shè)計(jì)要求如下： 假設(shè)一物流運(yùn)輸公司把A、B、C三家公司生產(chǎn)的某種產(chǎn)品，分別運(yùn)往青島、北京、上海、

15、大連4個(gè)地區(qū)，三家公司的年產(chǎn)量分別為10000噸、12000噸、和13000噸。這種產(chǎn)品在四個(gè)地區(qū)的需求量分別為5800噸、8000噸、8800噸、和9000噸。假設(shè)從各家公司運(yùn)往四個(gè)地區(qū)的的成本是已知的，設(shè)計(jì)物流公司即能滿足市場(chǎng)需求和產(chǎn)品生產(chǎn)公司的產(chǎn)量要求又能使運(yùn)輸成本最低的運(yùn)輸方案。也就是求物流公司分別將從三家公司運(yùn)輸多少產(chǎn)品到四個(gè)地區(qū)，即能滿足市場(chǎng)需求和產(chǎn)品生產(chǎn)公司的產(chǎn)量要求又能使運(yùn)輸成本最低。37 拓 展 實(shí) 訓(xùn)實(shí)訓(xùn)3：最低運(yùn)輸成本方案設(shè)計(jì)要求如下：38 拓 展 實(shí) 訓(xùn)實(shí)訓(xùn)3：企業(yè)最佳生產(chǎn)方案設(shè)計(jì)要求如下： 某企業(yè)要同時(shí)生產(chǎn)A、B兩種產(chǎn)品，企業(yè)每月的生產(chǎn)能力是50000小時(shí)，生產(chǎn)一件A產(chǎn)品需要10小時(shí)，生產(chǎn)一件B產(chǎn)品需要18小時(shí)。企業(yè)每銷售一件A產(chǎn)品得到利潤(rùn)2000元，每銷售一件B產(chǎn)品得