Excel線(xiàn)性規(guī)劃求解在管理會(huì)計(jì)中的應(yīng)用

楊俏文　劉云

[摘 要] 把Excel計(jì)算工具與管理會(huì)計(jì)業(yè)務(wù)相結(jié)合，有利于提高管理會(huì)計(jì)實(shí)踐工作效率，有利于提高應(yīng)用型課程教學(xué)質(zhì)量，有利于培養(yǎng)學(xué)生動(dòng)腦又動(dòng)手的實(shí)際應(yīng)用能力，有利于實(shí)現(xiàn)應(yīng)用型大學(xué)培養(yǎng)應(yīng)用型人才的目標(biāo)。結(jié)合案例，通過(guò)Excel工具“加載宏”中的數(shù)據(jù)分析庫(kù)線(xiàn)性規(guī)劃求解，應(yīng)用于管理會(huì)計(jì)財(cái)務(wù)決策指標(biāo)邊際貢獻(xiàn)最大化、采購(gòu)與儲(chǔ)存成本最小化、目標(biāo)函數(shù)等于0時(shí)的內(nèi)部收益率的計(jì)算，展示了Excel在解決管理會(huì)計(jì)計(jì)算問(wèn)題的強(qiáng)大功能。它代替了現(xiàn)行的數(shù)學(xué)軟件Lingo、Lindo和Matlab，具有速度快、效率高的特點(diǎn)。

[關(guān)鍵詞] Excel；數(shù)據(jù)分析；線(xiàn)性規(guī)劃求解；決策指標(biāo)計(jì)算

[中圖分類(lèi)號(hào)] F270 [文獻(xiàn)標(biāo)識(shí)碼] A [文章編號(hào)] 1009-6043（2018）10-0131-03

Abstract： Combining Excel calculation tools with management accounting business is beneficial to improving the efficiency of management accounting practice， improving the teaching quality of applied courses， cultivating students' practical ability of using their brains and hands， and realizing the goal of training applied talents in applied universities. Combined with a case， the paper solves the problem of data analysis library linear programming with Excel tool "loading macro"， and applies it to the calculation of internal rate of return when the marginal contribution of management accounting financial decision-making index is maximized， the cost of purchasing and storage is minimized， and the objective function is equal to zero. It shows the powerful function of Excel in solving the problem of management accounting calculation. It takes the place of the existing mathematical software Lingo， Lindo and Matlab， and has the characteristics of high speed and high efficiency.

Key words： Excel， data analysis， linear programming solution， decision-making index calculation

一、研究背景

我們現(xiàn)在所處的時(shí)代是大數(shù)據(jù)、云計(jì)算時(shí)代，每天會(huì)產(chǎn)生大量的數(shù)據(jù)，需要我們?nèi)w類(lèi)、處理、分析、總結(jié)，然后探索數(shù)據(jù)之間的依存規(guī)律，人工計(jì)算已經(jīng)很難高效完成。大數(shù)據(jù)、云計(jì)算的出現(xiàn)，意味著我們可以通過(guò)計(jì)算機(jī)的計(jì)算功能來(lái)實(shí)現(xiàn)大量數(shù)據(jù)的處理分析，通過(guò)數(shù)據(jù)分析總結(jié)經(jīng)濟(jì)事項(xiàng)中發(fā)展的規(guī)律，用數(shù)據(jù)的依存關(guān)系來(lái)總結(jié)過(guò)去，把握現(xiàn)在以及預(yù)測(cè)未來(lái)。

大數(shù)據(jù)技術(shù)的運(yùn)用已是大多數(shù)行業(yè)創(chuàng)新驅(qū)動(dòng)的一個(gè)重要因素和核心競(jìng)爭(zhēng)力的關(guān)鍵因素。而現(xiàn)行的教科書(shū)、教學(xué)內(nèi)容安排，一部分是傳統(tǒng)的理論介紹，一部分是純粹的為實(shí)訓(xùn)而實(shí)訓(xùn)，使教學(xué)目標(biāo)很難實(shí)現(xiàn)理論與實(shí)踐俱佳的效果。通過(guò)就Excel軟件中線(xiàn)性規(guī)劃求解在管理會(huì)計(jì)相關(guān)決策指標(biāo)計(jì)算中的應(yīng)用進(jìn)行例證，旨在有助于同行們更好地實(shí)施教學(xué)改革，更好地完善實(shí)驗(yàn)課，使學(xué)生掌握適應(yīng)時(shí)代發(fā)展要求的數(shù)據(jù)處理技能。同時(shí)，把Excel計(jì)算工具與會(huì)計(jì)業(yè)務(wù)相結(jié)合，有利于財(cái)務(wù)會(huì)計(jì)實(shí)踐工作效率的提高，有利于理論聯(lián)系實(shí)際課程教學(xué)質(zhì)量的提高，有利于學(xué)生動(dòng)腦又動(dòng)手的實(shí)際應(yīng)用能力的提高，有利于應(yīng)用型大學(xué)培養(yǎng)應(yīng)用型人才目標(biāo)的實(shí)現(xiàn)。

二、理論簡(jiǎn)介與應(yīng)用工具

（一）線(xiàn)性規(guī)劃理論簡(jiǎn)介

線(xiàn)性規(guī)劃是數(shù)學(xué)范疇，在運(yùn)籌學(xué)中研發(fā)較早，其方法成熟，應(yīng)用廣泛，有助于人們運(yùn)用數(shù)學(xué)方法實(shí)施科學(xué)管理。在經(jīng)濟(jì)活動(dòng)管理、交通運(yùn)輸籌劃、工農(nóng)業(yè)生產(chǎn)計(jì)劃安排等活動(dòng)中，運(yùn)用線(xiàn)性規(guī)劃方法，提高經(jīng)濟(jì)效益、效果方面是不可或缺的方法。線(xiàn)性規(guī)劃所研究的目標(biāo)是：在有限條件下，合理、科學(xué)安排人力物力等資源，使經(jīng)濟(jì)效益、效果達(dá)到最好。一般地表現(xiàn)為，求線(xiàn)性目標(biāo)函數(shù)在線(xiàn)性約束條件下的最大值或最小值的問(wèn)題，統(tǒng)稱(chēng)為線(xiàn)性規(guī)劃問(wèn)題。決策變量、約束條件、目標(biāo)函數(shù)是線(xiàn)性規(guī)劃的三要素。

Excel軟件加載宏線(xiàn)性規(guī)劃求解，不僅可以用于最大值、最小值的規(guī)劃求解問(wèn)題，當(dāng)目標(biāo)函數(shù)設(shè)定為0時(shí)，它還可以求解未知數(shù)的解，即解方程。下文將對(duì)Excel中線(xiàn)性規(guī)劃求解功能，全面、完整地在會(huì)計(jì)課程相關(guān)計(jì)算知識(shí)點(diǎn)中進(jìn)行運(yùn)用，以案例展示。

（二）Excel加載宏中的線(xiàn)性規(guī)劃求解

運(yùn)用Excel加載宏求解功能，首先要調(diào)出Excel工具，現(xiàn)行的MicrosoftOffice2003版、2007版和2010版，都有線(xiàn)性規(guī)劃求解工具，只是2003版，從加載宏調(diào)出后，在“工具”欄菜單；2007版和2010版在加載宏的線(xiàn)性規(guī)劃求解在“數(shù)據(jù)”欄，如下線(xiàn)性規(guī)劃求解工具的調(diào)出以2010版為例。調(diào)出線(xiàn)性規(guī)劃求解步驟：

1.打開(kāi)Excel頁(yè)面，點(diǎn)擊左上角“文件”，在列菜單中點(diǎn)擊“選項(xiàng)”，在選項(xiàng)中點(diǎn)擊“加載項(xiàng)”下拉列菜單，然后單擊“加載項(xiàng)”，打開(kāi)“加載宏”對(duì)話(huà)框；

2.在“加載項(xiàng)”框中，選中“分析工具”旁邊的復(fù)選框，然后單擊下方“轉(zhuǎn)到”按鈕。此時(shí)頁(yè)面就彈出窗口“加載宏”；

3.在“加載宏”中選中“規(guī)劃求解加載項(xiàng)”，點(diǎn)擊“確定”，彈出窗口關(guān)閉；

4.點(diǎn)擊菜單欄上的“數(shù)據(jù)”，頁(yè)眉菜單右上角就會(huì)出現(xiàn)加載規(guī)劃求解后“規(guī)劃求解”的命令；

5.在Excel表格里輸入相關(guān)數(shù)據(jù)，并設(shè)置好條件、未知數(shù)、目標(biāo)函數(shù)，限定條件后，即可打開(kāi)“規(guī)劃求解”功能使用。

三、Excel線(xiàn)性規(guī)劃求解應(yīng)用于財(cái)務(wù)決策指標(biāo)計(jì)算

（一）求解邊際貢獻(xiàn)最大化

在企業(yè)的各項(xiàng)管理活動(dòng)中，例如計(jì)劃、生產(chǎn)、運(yùn)輸、技術(shù)等問(wèn)題，線(xiàn)性規(guī)劃是指從各種限制條件的組合中，選擇出最為合理的計(jì)算方法，建立線(xiàn)性規(guī)劃模型從而求得最佳結(jié)果。

1.案例資料

某公司有A、B兩個(gè)車(chē)間，共同生產(chǎn)甲、乙兩種產(chǎn)品，但生產(chǎn)甲、乙產(chǎn)品受到A、B兩個(gè)車(chē)間的加工工時(shí)總數(shù)的限制。相關(guān)資料見(jiàn)表1。要求：作出每種產(chǎn)品每周的生產(chǎn)量應(yīng)為多大時(shí)才可以獲得最大收益的決策。

2.基本理論

3.操作步驟：

（1）把數(shù)據(jù)粘貼于Excel表，設(shè)置B6、C6分別為變量x1和x2的單元格；設(shè)置單元格B7為目標(biāo)函數(shù)單元格，并輸入“=B5*B6+C5*C6”；

（2）點(diǎn)擊數(shù)據(jù)菜單中的“規(guī)劃求解”，在彈出的規(guī)劃求解窗口“設(shè)置目標(biāo)”中入“$B$7”（絕對(duì)引用），選“最大值”；在“通過(guò)更改可變單元格”中，輸入“$B$6：$C$6”；在“遵守約束”框旁，點(diǎn)擊“添加”，在彈出的添加窗口中依次輸入“$B$8”，在不等式符號(hào)選項(xiàng)中選擇“<=”等值欄直接輸入“72”，而后重復(fù)添加遵守約束“$B$9”、“<=”、“62”。

（3）點(diǎn)擊下方“求解”，Excel表中彈出“規(guī)劃求解結(jié)果”，這時(shí)Excel表中的變量和目標(biāo)函數(shù)單元格B6、C6、B7，依次顯現(xiàn)出數(shù)值：6.67、13、184。當(dāng)產(chǎn)品甲、乙的產(chǎn)量分別安排為6.67件、13件時(shí)，企業(yè)的邊際貢獻(xiàn)最大，最大值為184元，如圖4所示。根據(jù)實(shí)際情況甲產(chǎn)品的產(chǎn)量可以取整數(shù)解6件，這時(shí)邊際貢獻(xiàn)的最大值為176元。

（二）求解采儲(chǔ)成本最小化

1.案例資料

某企業(yè)每年耗用某種材料3600千克，該材料單位成本10元，單位存儲(chǔ)成本為2元，一次訂貨成本25元，計(jì)算最優(yōu)訂貨量、年最優(yōu)訂貨次數(shù)。

2.基本理論

《財(cái)務(wù)管理》與《管理會(huì)計(jì)》中存貨總成本基本模型：

3.操作步驟

（1）打開(kāi)Excel表，輸入基本數(shù)據(jù)；

（2）設(shè)置B3為變量單元格，即訂貨批量，B4為目標(biāo)函數(shù)單元格，即存貨總成本，輸入“=B2*B1/B3+C2*B3/2”；

（3）設(shè)置批次單元格D4，單元格引用“=B1/B3”，把B3中的初始變量賦值為1（如圖6所示），切不可賦值為0，為0時(shí)，目標(biāo)函數(shù)的子項(xiàng)分母中有變量為0，分母為0，分式無(wú)意義；

（4）選中B4，打開(kāi)數(shù)據(jù)“規(guī)劃求解”，在彈出的規(guī)劃求解窗口，輸入?yún)?shù)；設(shè)置目標(biāo)中輸入“$B$4”，選中“最小值”，在“通過(guò)更改可變單元格”中輸入“$B$3”遵守約束中，可以空置；圖示參照?qǐng)D1；

（5）點(diǎn)擊“規(guī)劃求解參數(shù)”下方“求解”，窗口彈出“規(guī)劃求解結(jié)果”，同時(shí)，Excel表中的可變量：經(jīng)濟(jì)訂貨批量、訂貨批次、目標(biāo)函數(shù)（總成本最小值）分別顯示為：300、12、600，即每批訂貨量為300千克，訂貨批次12次，總的采購(gòu)和儲(chǔ)存成本最小為600元。

（三）求解內(nèi)部收益率

1.案例資料

某投資項(xiàng)目不同時(shí)期的現(xiàn)金凈流量如表2所示，計(jì)算該項(xiàng)目的內(nèi)部收益率。

2.基本理論

可以使項(xiàng)目的凈現(xiàn)值（NPV）之和為0的折現(xiàn)率，即為內(nèi)部收益率IRR的值。

3.操作步驟

（1）將表格數(shù)據(jù)輸入Excel表，并設(shè)置了“凈現(xiàn)值NPV”單元格，在B3單元格中輸入：“=B2/（1+$B$4）^B1”，；

（2）B4為變量單元格，在目標(biāo)函數(shù)B5中，輸入“=SUM（B3：J3”和函數(shù)公式，如圖3所示。

（3）將鼠標(biāo)選中B5，打開(kāi)數(shù)據(jù)、線(xiàn)性規(guī)劃求解，在彈出的規(guī)劃求解參數(shù)窗口的“設(shè)置目標(biāo)”填寫(xiě)“$M$6”，

在目標(biāo)值欄輸入“0”；遵守約束中可不填，圖示參照?qǐng)D1。

（4）點(diǎn)擊規(guī)劃求解參數(shù)的下方“求解”，窗口置換為規(guī)劃求解結(jié)果窗口，當(dāng)凈現(xiàn)值NPV=-0時(shí)，得到IRR=15.4%，如圖3所示。

四、結(jié)束語(yǔ)

上述案例是運(yùn)用Excel計(jì)算工具加載宏線(xiàn)性規(guī)劃求解案例，涉及企業(yè)按照邊際貢獻(xiàn)最大安排生產(chǎn)計(jì)劃的案例、采購(gòu)與儲(chǔ)存財(cái)務(wù)成本費(fèi)用最小化的案例、目標(biāo)函數(shù)為0的內(nèi)部收益率計(jì)算案例，涉及不等式、最大值、最小值、單純性求解、等式解方程等數(shù)學(xué)基本理論知識(shí)點(diǎn)。而Excel軟件既可以求最大值、最小值，也可以解不等式、解方程，在計(jì)算過(guò)程中，代替了現(xiàn)行的數(shù)學(xué)軟件Lingo、Lindo和Matlab，具有速度快、效率高的特點(diǎn)。利用計(jì)算機(jī)功能，從數(shù)據(jù)分析庫(kù)中調(diào)出Excel加載宏，把數(shù)據(jù)處理工具運(yùn)用到我們的教學(xué)實(shí)踐中，能夠促進(jìn)我們的工作、學(xué)習(xí)更便捷、更高效地運(yùn)行。

[參考文獻(xiàn)]

[1]溫素彬.管理會(huì)計(jì)-理論、模型、案例[M].北京：機(jī)械工業(yè)出版社，2014-6.

[2]劉云.內(nèi)含報(bào)酬率解法新探[J].管理會(huì)計(jì)，1997（40）.

[3]劉云.管理會(huì)計(jì)中幾個(gè)主要公式的推導(dǎo)[J].安徽會(huì)計(jì)管理，1998（5）.

[責(zé)任編輯：潘洪志]