楊俏文　劉云

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

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

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

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

二、理論簡介與應用工具

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

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

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

（二）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.在“加載項”框中，選中“分析工具”旁邊的復選框，然后單擊下方“轉到”按鈕。此時頁面就彈出窗口“加載宏”；

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

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

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

三、Excel線性規(guī)劃求解應用于財務決策指標計算

（一）求解邊際貢獻最大化

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

1.案例資料

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

2.基本理論

3.操作步驟：

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

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

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

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

1.案例資料

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

2.基本理論

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

3.操作步驟

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

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

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

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

（5）點擊“規(guī)劃求解參數(shù)”下方“求解”，窗口彈出“規(guī)劃求解結果”，同時，Excel表中的可變量：經(jīng)濟訂貨批量、訂貨批次、目標函數(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表，并設置了“凈現(xiàn)值NPV”單元格，在B3單元格中輸入：“=B2/（1+$B$4）^B1”，；

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

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

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

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

四、結束語

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

[參考文獻]

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

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

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

[責任編輯：潘洪志]