Excel線性規(guī)劃求解在管理會計中的應(yīng)用

楊俏文　劉云

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

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

[中圖分類號] F270 [文獻(xiàn)標(biāo)識碼] A [文章編號] 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ù)據(jù)、云計算時代，每天會產(chǎn)生大量的數(shù)據(jù)，需要我們?nèi)w類、處理、分析、總結(jié)，然后探索數(shù)據(jù)之間的依存規(guī)律，人工計算已經(jīng)很難高效完成。大數(shù)據(jù)、云計算的出現(xiàn)，意味著我們可以通過計算機的計算功能來實現(xiàn)大量數(shù)據(jù)的處理分析，通過數(shù)據(jù)分析總結(jié)經(jīng)濟(jì)事項中發(fā)展的規(guī)律，用數(shù)據(jù)的依存關(guān)系來總結(jié)過去，把握現(xiàn)在以及預(yù)測未來。

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

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

（一）線性規(guī)劃理論簡介

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

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

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

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

1.打開Excel頁面，點擊左上角“文件”，在列菜單中點擊“選項”，在選項中點擊“加載項”下拉列菜單，然后單擊“加載項”，打開“加載宏”對話框；

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

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

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

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

三、Excel線性規(guī)劃求解應(yīng)用于財務(wù)決策指標(biāo)計算

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

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

1.案例資料

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

2.基本理論

3.操作步驟：

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

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

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

（二）求解采儲成本最小化

1.案例資料

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

2.基本理論

《財務(wù)管理》與《管理會計》中存貨總成本基本模型：

3.操作步驟

（1）打開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時，目標(biāo)函數(shù)的子項分母中有變量為0，分母為0，分式無意義；

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

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

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

1.案例資料

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

2.基本理論

可以使項目的凈現(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，打開數(shù)據(jù)、線性規(guī)劃求解，在彈出的規(guī)劃求解參數(shù)窗口的“設(shè)置目標(biāo)”填寫“$M$6”，

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

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

四、結(jié)束語

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

[參考文獻(xiàn)]

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

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

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

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