Excel規(guī)劃求解

《Excel規(guī)劃求解》由會(huì)員上傳分享，免費(fèi)在線閱讀，更多相關(guān)內(nèi)容在行業(yè)資料-天天文庫。

1、□財(cái)會(huì)月刊·全國優(yōu)秀經(jīng)濟(jì)期刊Excel規(guī)劃求解：企業(yè)全球運(yùn)營管理工具耿海利（江西財(cái)經(jīng)大學(xué)會(huì)計(jì)學(xué)院南昌330013）【摘要】隨著全球經(jīng)濟(jì)一體化的深入，企業(yè)運(yùn)營管理方式發(fā)生了很大變化。本文通過一個(gè)實(shí)例，來探討企業(yè)集團(tuán)擁有多個(gè)生產(chǎn)子公司、多個(gè)產(chǎn)品市場并且各個(gè)產(chǎn)品市場價(jià)格不同的情況下，企業(yè)如何使用Excel規(guī)劃求解工具進(jìn)行產(chǎn)品生產(chǎn)、運(yùn)輸和分配決策，以實(shí)現(xiàn)集團(tuán)利潤最大化。【關(guān)鍵詞】規(guī)劃求解企業(yè)集團(tuán)全球運(yùn)營決策敏感性分析在傳統(tǒng)財(cái)務(wù)運(yùn)營管理中，營運(yùn)決策包括確定最佳現(xiàn)最大化目標(biāo)的年生產(chǎn)和運(yùn)輸預(yù)算的決策方案。金持有量、最優(yōu)訂貨批量，或者只是考慮單個(gè)市場的生產(chǎn)二、建

2、立線性數(shù)學(xué)模型與銷售決策。企業(yè)集團(tuán)全球運(yùn)營管理涉及生產(chǎn)、運(yùn)輸、銷1.定義決策變量。下文中，（ii=1，2，3，4）表示工廠，j售等環(huán)節(jié)，需要在實(shí)現(xiàn)集團(tuán)利潤最大化的同時(shí)，解決生產(chǎn)表示市場（j=1，2，3，4）；決策問題可以用圖2表示。所以定什么產(chǎn)品、在哪里生產(chǎn)、生產(chǎn)多少、運(yùn)到哪個(gè)市場等諸多義決策變量為Xij：即在i工廠生產(chǎn)的產(chǎn)品投放到j(luò)市場。問題。顯然，采用傳統(tǒng)的運(yùn)營管理方法會(huì)比較棘手。而Ex?cel提供的規(guī)劃求解工具，不但能非常迅速地求出多種營運(yùn)決策模型的最優(yōu)解，還可以給出敏感性分析報(bào)告，滿足財(cái)務(wù)全球化運(yùn)營管理的需求，有效提高公司決策效率，同時(shí)也

3、能促進(jìn)財(cái)務(wù)人員更多地參與到公司管理決策中。一、問題描述某跨國集團(tuán)在中國和其他地區(qū)設(shè)立了四個(gè)工廠，分圖2決策問題別為A、B、C、D廠，產(chǎn)品主要面向國際市場銷售，分別銷往北京、香港、紐約、東京四個(gè)城市。各個(gè)工廠的單位產(chǎn)品2.確定目標(biāo)函數(shù)。最大利潤=收入-產(chǎn)品變動(dòng)成本-成本、固定成本、產(chǎn)能，各個(gè)市場的銷售價(jià)格和需求量，以其他成本最大利潤=55500（X11+X21+X31+X41）+61100及各個(gè)工廠到每個(gè)市場的運(yùn)輸成本見圖1。（X12+X22+X32+X42）+57800（X13+X23+X33+X43）+62650（X14+X24+X34+X44

4、）-34900（X11+X12+X13+X14）-32200（X21+X22+X23+X24）-38350（X31+X32+X33+X34）-23400（X41+X42+X43+X44）-（500X11+12225X12+9075X13+21450X14+4500X21+……+15150X43+5925X44）。3.列出約束條件。（1）產(chǎn)能約束：X11+X12+X13+X14≤101；X21+X22+X23+X24≤201；X31+X32+X33+X34≤121；X41+X42+X43+X44≤250。（2）需求約束：X11+X21+X31+X4

5、1≤150；X12+X22+X32+X42≤75；X13+X23+X33+X43≤200；X14+X24+X34+X44≤100。（3）非負(fù)約束：Xij≥0。4.最優(yōu)解：最大利潤時(shí)的Xij。圖1集團(tuán)基本運(yùn)營決策數(shù)據(jù)三、數(shù)據(jù)及公式準(zhǔn)備在每個(gè)工廠產(chǎn)能允許同時(shí)最大限度滿足市場需求的1.數(shù)據(jù)輸入：把圖1集團(tuán)公司的決策數(shù)據(jù)輸入新建的情況下，集團(tuán)管理層希望財(cái)務(wù)部給出能夠?qū)崿F(xiàn)集團(tuán)利潤Excel表中，如圖3所示?！酢?10·2014.8下全國中文核心期刊·財(cái)會(huì)月刊□PRODUCT（B2∶E2，B13∶E13）”；計(jì)算變動(dòng)成本，選擇B17單元格，輸入公式“=SUM

6、PRODUCT（F4∶F7，F(xiàn)9∶F12）”；計(jì)算運(yùn)輸成本，在B18單元格輸入公式“=SUMPRODUCT（B4∶E7，B9∶E12）”。SUMPRODUCT（數(shù)組1，數(shù)組2）函數(shù)，可以用來計(jì)算長度相等的行、列或者區(qū)域?qū)?yīng)元素乘積的和，是模型中重要的函數(shù)，在計(jì)算多個(gè)數(shù)據(jù)乘法時(shí)會(huì)非常簡便。5.計(jì)算總利潤。選擇B19單元格，輸入公式“=圖3數(shù)據(jù)準(zhǔn)備相關(guān)公式B16-B17-B8”；在固定成本所在單元格輸入公式“=SUM（G4∶G7）”；計(jì)算凈利潤，選中B21單元格，輸入公式“=B19-B20”。四、規(guī)劃求解使用Excel規(guī)劃求解工具，要求安裝Excel

7、時(shí)選擇“完全安裝”，“工具”菜單下才有“規(guī)劃求解”子菜單。如果安裝Excel時(shí)沒有安裝規(guī)劃求解功能，需要加載“規(guī)劃求解”功能，點(diǎn)擊Excel左上方圖標(biāo)，選擇“Excel選項(xiàng)”，出現(xiàn)Ex?cel選項(xiàng)對話框，選擇左側(cè)“加載項(xiàng)”→“規(guī)劃求解加載項(xiàng)”→“確定”，然后默認(rèn)安裝即可使用規(guī)劃求解功能。1.選中目標(biāo)函數(shù)所在單元格B19，然后選擇“工具”菜圖4規(guī)劃求解參數(shù)設(shè)置單欄下的“規(guī)劃求解”，會(huì)出現(xiàn)規(guī)劃求解參數(shù)對話框，如圖4所示，默認(rèn)即為求目標(biāo)函數(shù)最大值。2.設(shè)置可變單元格：可變單元格一般是決策變量所在區(qū)域，在求解時(shí)會(huì)不斷調(diào)整，直到滿足目標(biāo)函數(shù)最大化。把光標(biāo)移

8、動(dòng)到可變單元格下的選擇框內(nèi)，然后選中“B9∶E12”區(qū)域，松開鼠標(biāo)即可完成設(shè)置。3.添加約束條件：選擇“添加”按鈕，出現(xiàn)添加約束條圖5添