Excel財務(wù)應(yīng)用營養(yǎng)配餐問題

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