Excel規(guī)劃求解在經(jīng)濟(jì)管理中的應(yīng)用

尹聰春

［摘要］ 規(guī)劃求解是在一定的限制條件下，利用科學(xué)方法進(jìn)行運(yùn)算，使對(duì)前景的規(guī)劃達(dá)到最優(yōu)的方法，是現(xiàn)代管理科學(xué)的一種重要手段，是運(yùn)籌學(xué)的一個(gè)分支。Excel規(guī)劃求解在經(jīng)濟(jì)管理的很多方面都有應(yīng)用，但它的應(yīng)用在一般的教材和Excel的幫助中都沒有詳細(xì)的介紹。本文主要介紹用Excel規(guī)劃求解工具來解決經(jīng)濟(jì)管理中的產(chǎn)品組合及投資效益問題，以供企業(yè)的經(jīng)營(yíng)管理者借鑒。

［關(guān)鍵詞］Excel；規(guī)劃求解；經(jīng)濟(jì)管理；應(yīng)用

doi : 10 . 3969 / j . issn . 1673 - 0194 . 2012 . 04. 021

［中圖分類號(hào)］F275［文獻(xiàn)標(biāo)識(shí)碼］A［文章編號(hào)］1673 - 0194（2012）04- 0036- 02

1分析工具及相關(guān)術(shù)語介紹

Ｅxcel規(guī)劃求解是一組命令的組成部分，這些命令有時(shí)也稱作假設(shè)分析工具。它就是使工作表中的某些單元格得到優(yōu)化（最大化或最小化）。優(yōu)化模型包括3部分：目標(biāo)單元格、可變單元格和約束。

目標(biāo)單元格代表目的或目標(biāo)。例如，最大化每月利潤(rùn)、最小化每月成本等?？勺儐卧袷请娮颖砀裰锌梢赃M(jìn)行更改或調(diào)整以優(yōu)化目標(biāo)單元格的單元格。例如，每月每種產(chǎn)品的產(chǎn)量、每月或每年的存款金額等。約束是置于可變單元格中的限制條件。例如，使用的資源不能超過其生產(chǎn)能力,生產(chǎn)的產(chǎn)量大于等于零等。

2案例資料

（1）某計(jì)算機(jī)制造企業(yè)生產(chǎn)鼠標(biāo)、鍵盤和視頻游戲操縱杠。各項(xiàng)數(shù)據(jù)見表1。

每月共提供了 １３ ２００ 個(gè)人工工時(shí)和 ３ ０００ 小時(shí)的機(jī)器使用時(shí)間。該制造企業(yè)如何安排產(chǎn)量能使利潤(rùn)最大？

（2）某企業(yè)現(xiàn)有１００ ０００元準(zhǔn)備存入銀行，可以選擇一年期、二年期和三年期存款，3種存款的年利率分別為２．５００％、２．７１４％、２．９３２％。第３年初和第５年初需要使用現(xiàn)金１０ ０００元和２０ ０００元，第４年初有５０ ０００元的現(xiàn)金收入可以存入銀行。試在Ｅｘｃｅｌ中建立模型，計(jì)算每年年初的到期本金、到期利息和年末現(xiàn)金余額；用規(guī)劃求解工具求解每年年初每種存款的最優(yōu)存款額，使第６年末的現(xiàn)金余額最大；把規(guī)劃求解參數(shù)保存在合適的單元格中。

3模型建立１

3.1 計(jì)算各項(xiàng)指標(biāo)

在ＳＨＥＥＴ １工作表中將已知各項(xiàng)指標(biāo)填入相關(guān)單元格中，并進(jìn)行相關(guān)計(jì)算。如圖１所示。

其中Ｃ７：Ｅ７單元格區(qū)域假設(shè)為１，Ｃ８＝ＳＵＭ（Ｃ７：Ｅ７*Ｃ６：Ｅ６）、Ｆ３＝ＳＵＭ（Ｃ３：Ｅ３*Ｃ７：Ｅ７）、Ｆ４＝ ＝ＳＵＭ（Ｃ４：Ｅ４*Ｃ７：Ｅ７），這里需要用數(shù)組公式進(jìn)行確定。

3.2 進(jìn)行規(guī)劃求解

單擊“工具”菜單上的“規(guī)劃求解”，在彈出的 “規(guī)劃求解參數(shù)” 對(duì)話框中作如圖如圖２的設(shè)置。

在“設(shè)置目標(biāo)單元格”框中單擊，然后選擇利潤(rùn)單元格（單元格 Ｃ８），在“可變單元格”框中單擊，指向區(qū)域 Ｃ７：Ｅ７，該區(qū)域包含各類產(chǎn)品的產(chǎn)量。

添加約束：?jiǎn)螕簟疤砑印卑粹o，在“添加約束”對(duì)話框中，在標(biāo)記為“單元格引用位置”的框中單擊，選擇區(qū)域Ｃ７：Ｅ７，從對(duì)話框中部的列表中選擇“＞＝”，在標(biāo)記為“約束值”的框中單擊，選擇單元格區(qū)域Ｃ５：Ｅ５。在“添加約束”對(duì)話框中單擊“添加”，以輸入需求約束。即可。

同理，再輸入約束條件，Ｆ３：Ｆ４＜＝Ｇ３：Ｇ４。

在“規(guī)劃求解選項(xiàng)”對(duì)話框中輸入所有可變單元格都為非負(fù)值的約束，通過單擊“規(guī)劃求解參數(shù)”對(duì)話框中的“選項(xiàng)”按鈕可打開該對(duì)話框。

選擇“采用線性模型”和“假定非負(fù)”選項(xiàng)，然后單擊“確定”。

注意：選擇“假定非負(fù)”選項(xiàng)可確保規(guī)劃求解只考慮每個(gè)可變單元格都采用非負(fù)值的可變單元格組合。

選擇“采用線性模型”的原因是產(chǎn)品組合問題是一種稱為線性模型的特殊規(guī)劃求解問題。

單擊“規(guī)劃求解選項(xiàng)”對(duì)話框中的“確定”后，返回到主“規(guī)劃求解”對(duì)話框，單擊“求解”按鈕即可，這樣，規(guī)劃求解會(huì)迅速找出最佳解決方案，如圖３所示。需要選擇“保存規(guī)劃求解解決方案”以將最佳解決方案值保留在電子表格中。

4模型建立２

4.1 計(jì)算各項(xiàng)指標(biāo)

在ＳＨＥＥＴ ２工作表中將已知各項(xiàng)指標(biāo)填入相關(guān)單元格中，并進(jìn)行相關(guān)計(jì)算。如圖４所示。

注意：第４年可變單元格為２個(gè)，第５年可變單元格為１個(gè)。自變量總值為１２０ ０００元。

4.2 進(jìn)行規(guī)劃求解

根據(jù)如前所述的方法，在打開的“規(guī)劃求解”對(duì)話框中作如圖５所示的設(shè)置即可。求解的結(jié)果如圖６所示。

到第6年末的本利和為１３６３１７．３８元。這即是最佳的投資方案。

5結(jié)語

利用Ｅｘｃｅｌ規(guī)劃求解工具不僅可以解決經(jīng)濟(jì)管理中的產(chǎn)品組合及投資效益問題，還可以求解資金管理、運(yùn)輸管理、選址規(guī)劃等。

主要參考文獻(xiàn)

［１］劉繼偉，楊樺．Ｅｘｃｅｌ 在財(cái)務(wù)管理中的應(yīng)用［Ｍ］．北京：清華大學(xué)出版社，2010.