杨俏文　刘云

[摘 要] 把Excel计算工具与管理会计业务相结合，有利于提高管理会计实践工作效率，有利于提高应用型课程教学质量，有利于培养学生动脑又动手的实际应用能力，有利于实现应用型大学培养应用型人才的目标。结合案例，通过Excel工具“加载宏”中的数据分析库线性规划求解，应用于管理会计财务决策指标边际贡献最大化、采购与储存成本最小化、目标函数等于0时的内部收益率的计算，展示了Excel在解决管理会计计算问题的强大功能。它代替了现行的数学软件Lingo、Lindo和Matlab，具有速度快、效率高的特点。

[关键词] Excel；数据分析；线性规划求解；决策指标计算

[中图分类号] 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

一、研究背景

我們现在所处的时代是大数据、云计算时代，每天会产生大量的数据，需要我们去归类、处理、分析、总结，然后探索数据之间的依存规律，人工计算已经很难高效完成。大数据、云计算的出现，意味着我们可以通过计算机的计算功能来实现大量数据的处理分析，通过数据分析总结经济事项中发展的规律，用数据的依存关系来总结过去，把握现在以及预测未来。

大数据技术的运用已是大多数行业创新驱动的一个重要因素和核心竞争力的关键因素。而现行的教科书、教学内容安排，一部分是传统的理论介绍，一部分是纯粹的为实训而实训，使教学目标很难实现理论与实践俱佳的效果。通过就Excel软件中线性规划求解在管理会计相关决策指标计算中的应用进行例证，旨在有助于同行们更好地实施教学改革，更好地完善实验课，使学生掌握适应时代发展要求的数据处理技能。同时，把Excel计算工具与会计业务相结合，有利于财务会计实践工作效率的提高，有利于理论联系实际课程教学质量的提高，有利于学生动脑又动手的实际应用能力的提高，有利于应用型大学培养应用型人才目标的实现。

二、理论简介与应用工具

（一）线性规划理论简介

线性规划是数学范畴，在运筹学中研发较早，其方法成熟，应用广泛，有助于人们运用数学方法实施科学管理。在经济活动管理、交通运输筹划、工农业生产计划安排等活动中，运用线性规划方法，提高经济效益、效果方面是不可或缺的方法。线性规划所研究的目标是：在有限条件下，合理、科学安排人力物力等资源，使经济效益、效果达到最好。一般地表现为，求线性目标函数在线性约束条件下的最大值或最小值的问题，统称为线性规划问题。决策变量、约束条件、目标函数是线性规划的三要素。

Excel软件加载宏线性规划求解，不仅可以用于最大值、最小值的规划求解问题，当目标函数设定为0时，它还可以求解未知数的解，即解方程。下文将对Excel中线性规划求解功能，全面、完整地在会计课程相关计算知识点中进行运用，以案例展示。

（二）Excel加载宏中的线性规划求解

运用Excel加载宏求解功能，首先要调出Excel工具，现行的MicrosoftOffice2003版、2007版和2010版，都有线性规划求解工具，只是2003版，从加载宏调出后，在“工具”栏菜单；2007版和2010版在加载宏的线性规划求解在“数据”栏，如下线性规划求解工具的调出以2010版为例。调出线性规划求解步骤：

1.打开Excel页面，点击左上角“文件”，在列菜单中点击“选项”，在选项中点击“加载项”下拉列菜单，然后单击“加载项”，打开“加载宏”对话框；

2.在“加载项”框中，选中“分析工具”旁边的复选框，然后单击下方“转到”按钮。此时页面就弹出窗口“加载宏”；

3.在“加载宏”中选中“规划求解加载项”，点击“确定”，弹出窗口关闭；

4.点击菜单栏上的“数据”，页眉菜单右上角就会出现加载规划求解后“规划求解”的命令；

5.在Excel表格里输入相关数据，并设置好条件、未知数、目标函数，限定条件后，即可打开“规划求解”功能使用。

三、Excel线性规划求解应用于财务决策指标计算

（一）求解边际贡献最大化

在企業的各项管理活动中，例如计划、生产、运输、技术等问题，线性规划是指从各种限制条件的组合中，选择出最为合理的计算方法，建立线性规划模型从而求得最佳结果。

1.案例资料

某公司有A、B两个车间，共同生产甲、乙两种产品，但生产甲、乙产品受到A、B两个车间的加工工时总数的限制。相关资料见表1。要求：作出每种产品每周的生产量应为多大时才可以获得最大收益的决策。

2.基本理论

3.操作步骤：

（1）把数据粘贴于Excel表，设置B6、C6分别为变量x1和x2的单元格；设置单元格B7为目标函数单元格，并输入“=B5*B6+C5*C6”；

（2）点击数据菜单中的“规划求解”，在弹出的规划求解窗口“设置目标”中入“$B$7”（绝对引用），选“最大值”；在“通过更改可变单元格”中，输入“$B$6：$C$6”；在“遵守约束”框旁，点击“添加”，在弹出的添加窗口中依次输入“$B$8”，在不等式符号选项中选择“<=”等值栏直接输入“72”，而后重复添加遵守约束“$B$9”、“<=”、“62”。

（3）点击下方“求解”，Excel表中弹出“规划求解结果”，这时Excel表中的变量和目标函数单元格B6、C6、B7，依次显现出数值：6.67、13、184。当产品甲、乙的产量分别安排为6.67件、13件时，企业的边际贡献最大，最大值为184元，如图4所示。根据实际情况甲产品的产量可以取整数解6件，这时边际贡献的最大值为176元。

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

1.案例资料

某企业每年耗用某种材料3600千克，该材料单位成本10元，单位存储成本为2元，一次订货成本25元，计算最优订货量、年最优订货次数。

2.基本理论

《财务管理》与《管理会计》中存货总成本基本模型：

3.操作步骤

（1）打开Excel表，输入基本数据；

（2）设置B3为变量单元格，即订货批量，B4为目标函数单元格，即存货总成本，输入“=B2*B1/B3+C2*B3/2”；

（3）设置批次单元格D4，单元格引用“=B1/B3”，把B3中的初始变量赋值为1（如图6所示），切不可赋值为0，为0时，目标函数的子项分母中有变量为0，分母为0，分式无意义；

（4）选中B4，打开数据“规划求解”，在弹出的规划求解窗口，输入参数；设置目标中输入“$B$4”，选中“最小值”，在“通过更改可变单元格”中输入“$B$3”遵守约束中，可以空置；图示参照图1；

（5）点击“规划求解参数”下方“求解”，窗口弹出“规划求解结果”，同时，Excel表中的可变量：经济订货批量、订货批次、目标函数（总成本最小值）分别显示为：300、12、600，即每批订货量为300千克，订货批次12次，总的采购和储存成本最小为600元。

（三）求解内部收益率

1.案例资料

某投资项目不同时期的现金净流量如表2所示，计算该项目的内部收益率。

2.基本理论

可以使项目的净现值（NPV）之和为0的折现率，即为内部收益率IRR的值。

3.操作步骤

（1）将表格数据输入Excel表，并设置了“净现值NPV”单元格，在B3单元格中输入：“=B2/（1+$B$4）^B1”，；

（2）B4为变量单元格，在目标函数B5中，输入“=SUM（B3：J3”和函数公式，如图3所示。

（3）将鼠标选中B5，打开数据、线性规划求解，在弹出的规划求解参数窗口的“设置目标”填写“$M$6”，

在目标值栏输入“0”；遵守约束中可不填，图示参照图1。

（4）点击规划求解参数的下方“求解”，窗口置换为规划求解结果窗口，当净现值NPV=-0时，得到IRR=15.4%，如图3所示。

四、结束语

上述案例是运用Excel计算工具加载宏线性规划求解案例，涉及企业按照边际贡献最大安排生产计划的案例、采购与储存财务成本费用最小化的案例、目标函数为0的内部收益率计算案例，涉及不等式、最大值、最小值、单纯性求解、等式解方程等数学基本理论知识点。而Excel软件既可以求最大值、最小值，也可以解不等式、解方程，在计算过程中，代替了现行的数学软件Lingo、Lindo和Matlab，具有速度快、效率高的特点。利用计算机功能，从数据分析库中调出Excel加载宏，把数据处理工具运用到我们的教学实践中，能够促进我们的工作、学习更便捷、更高效地运行。

[参考文献]

[1]温素彬.管理会计-理论、模型、案例[M].北京：机械工业出版社，2014-6.

[2]刘云.内含报酬率解法新探[J].管理会计，1997（40）.

[3]刘云.管理会计中几个主要公式的推导[J].安徽会计管理，1998（5）.

[责任编辑：潘洪志]