Excel財(cái)務(wù)應(yīng)用營(yíng)養(yǎng)配餐問(wèn)題

Excel財(cái)務(wù)應(yīng)用營(yíng)養(yǎng)配餐問(wèn)題隨著現(xiàn)代生活水平的提高，人們?cè)絹?lái)越注重健康問(wèn)題，而科學(xué)的飲食則是保持健康和增強(qiáng)體質(zhì)最簡(jiǎn)單、有效的方法。人們已經(jīng)逐漸意識(shí)到，長(zhǎng)期不良的膳食結(jié)構(gòu)與飲食習(xí)慣會(huì)嚴(yán)重影響身體健康，很多人都是因?yàn)椤俺圆坏梅ā?，而罹患種種疾病，如肥胖、心臟病、高血壓、動(dòng)脈硬化等。因此，作為一家餐館，合理的進(jìn)行營(yíng)養(yǎng)配餐是招攬顧客的最佳手段。營(yíng)養(yǎng)配餐，是指按照人們身體的需要，根據(jù)食品中各種營(yíng)養(yǎng)物質(zhì)的含量，設(shè)計(jì)一天、一周或一個(gè)月的食譜，使人體攝入的蛋白質(zhì)、脂肪、碳水化合物、維生素和礦物質(zhì)等幾大營(yíng)養(yǎng)元素比例合理，即達(dá)到均衡膳食。下面我們利用線(xiàn)性規(guī)劃模型，來(lái)解決餐館的營(yíng)養(yǎng)配餐問(wèn)題。例如，某餐廳開(kāi)發(fā)出一道新菜需要使用草魚(yú)和平菇兩種原料，這兩種原料所包含的碳水化合物、蛋白質(zhì)、脂肪含量以及人體每天所需的攝入量如圖9-19所示，已知草魚(yú)和平菇的成本分別為3元和2元。要求使用線(xiàn)性規(guī)劃計(jì)算如何搭配這兩種原料，才能在成本最小的基礎(chǔ)上，滿(mǎn)足顧客的營(yíng)養(yǎng)要求？根據(jù)已知條件，可以在Excel工作表中建立求解的線(xiàn)性規(guī)劃模型，如圖9-20所示。圖9-19已知條件 圖9-20創(chuàng)建線(xiàn)性規(guī)劃模型然后，分別計(jì)算這兩種原料中，每種營(yíng)養(yǎng)成分的合計(jì)值。選擇碳水化合物合計(jì)值所對(duì)應(yīng)的單元格，即D3單元格，在【編輯欄】中輸入“=SUMPRODUCT(B3:C3,B7:C7)”公式，按Enter鍵即可得出該營(yíng)養(yǎng)成分的合計(jì)值，如圖9-21所示。再向下拖動(dòng)D3單元格右下角的填充柄，將公式填充至D5單元格，如圖9-22所示。圖9-21碳水化合物的合計(jì)值 圖9-22填充公式在創(chuàng)建好的線(xiàn)性規(guī)劃模型中，帶有“茶色”填充格式的單元格區(qū)域?qū)儆诳勺冎?，其默認(rèn)值為0,因此，與其相關(guān)的營(yíng)養(yǎng)成分合計(jì)值也為0。選擇目標(biāo)值所對(duì)應(yīng)的單元格，即F7單元格，在【編輯欄】中輸入“=SUMPRODUCT(B6:C6,B7:C7)”公式，按Enter鍵，即可計(jì)算出該問(wèn)題的目標(biāo)值，如圖9-23所示。J［呷MFEULtgTCB&mC自衛(wèi)72cHiJ［呷MFEULtgTCB&mC自衛(wèi)72cHi*辛罌小新3止方U, jhttr.l典分Fl嫉本也告* J去后聽(tīng) 刊屆后 6比是 3■及圖9-23計(jì)算目標(biāo)值條件和公式的設(shè)置完成之后，選擇F7單元格，單擊【規(guī)劃求解】按鈕，在彈出的【規(guī)劃求解參數(shù)】對(duì)話(huà)框中，選擇【最小值】單選按鈕，設(shè)置【可變單元格】的數(shù)據(jù)范圍為B7:C7,并依次單擊【添加】按鈕，分別添加“$D$3：$D$4>=$F$3:$F$4”和“$D$5<=$F$5”約束，如圖9-24所示。然后，進(jìn)行規(guī)劃求解選項(xiàng)的設(shè)置，并單擊【求解】按鈕，在【規(guī)劃求解結(jié)果】對(duì)話(huà)框中，選擇【報(bào)告】欄中的【敏感性報(bào)告】選項(xiàng)，如圖9-25所示。.iS^SHKiU-IT-可阻是若百的購(gòu)藤或aIt選擇【報(bào)告】欄中的【敏感性報(bào)告】選項(xiàng)，如圖9-25所示。.iS^SHKiU-IT-可阻是若百的購(gòu)藤或aIt他必值1用呼或時(shí)天用hf吳HiC任豆力度0陋圖9-24設(shè)置規(guī)劃求解參數(shù) 圖9-25設(shè)置規(guī)劃求解結(jié)果在設(shè)置線(xiàn)性規(guī)劃的約束時(shí)，由于本例的限制條件不是單一的，而是由兩種不同的約束條件組成的。因此，在本例中，需要添加兩組約束條件。單擊【選項(xiàng)】按鈕，在彈出的【規(guī)劃求解選項(xiàng)】對(duì)話(huà)框中，分別啟用【采用線(xiàn)性 在模型】和【假定非負(fù)】復(fù)選框。 【規(guī)劃求解】對(duì)話(huà)框中，可以選擇生成三種形式的報(bào)告用，戶(hù)既可以只選擇一種報(bào)告也，可以同時(shí)生成三種報(bào)告。其中，每種報(bào)告的功能意義如下：運(yùn)算結(jié)果報(bào)告該類(lèi)形式的報(bào)告可以列出目標(biāo)單元格和可變單元格及其初始值和最終結(jié)約果束、條件以及有關(guān)約束條件的信息。敏感性報(bào)告該報(bào)告提供有關(guān)求解結(jié)果對(duì)【規(guī)劃求解參數(shù)】對(duì)話(huà)框中，目標(biāo)單元格所指定公式的微小變化或約束條件的微小變化的敏感程度信息。但是，含有整數(shù)約束條件的模型不能生成該報(bào)告。對(duì)于非線(xiàn)性模型，該報(bào)告提供遞減梯度和拉格朗日乘數(shù)；對(duì)于線(xiàn)性模型，該報(bào)告中將包含遞減成本、陰影價(jià)格、目標(biāo)式系數(shù)（允許的增量和允許的減量）以及約束右側(cè)的區(qū)域。極限值報(bào)告在該報(bào)告中，可以列出目標(biāo)單元格和可變單元格及其各自的數(shù)值、上下限和目標(biāo)值。下限是在保持其他可變單元格數(shù)值不變并滿(mǎn)足約束條件的情況下，某個(gè)可變單元格可以取到的最小值。而上限是在這種情況下可以取到的最大值。與敏感性報(bào)告相同，含有整數(shù)約束條件的模型不能生成該報(bào)告。單擊【規(guī)劃求解結(jié)果】對(duì)話(huà)框中的【確定】按鈕，即可得到如圖9-26所示的規(guī)劃求解結(jié)果，以及圖9-27所示的敏感性分析報(bào)告。圖9-26規(guī)劃求解結(jié)果 圖9-27敏感性分析報(bào)告根據(jù)圖9-26可以得出結(jié)論：若要在最小成本的基礎(chǔ)上滿(mǎn)足顧客的營(yíng)養(yǎng)需求，只需將2.8份的草魚(yú)與3份的平菇進(jìn)行搭配即可。在Excel中，敏感性報(bào)告描述的是最優(yōu)解的穩(wěn)定性。其中，線(xiàn)性規(guī)劃的敏感性報(bào)告提供了遞減成本、陰影價(jià)格、目標(biāo)式系數(shù)（允許的增量和允許的減量）等重要參數(shù)。在本例中，由于啟用了【采用線(xiàn)性模型】復(fù)選框，因此可以分析比較詳細(xì)的敏感性報(bào)告。“可變單元格”部分在該報(bào)表中，“允許的增量”和“允許的減量”數(shù)據(jù)列，表示對(duì)應(yīng)的約束條件增加或者減少某個(gè)數(shù)值，規(guī)劃