Excel规划求解在代数分配法中的应用

任志

摘 要：通常企业辅助生产费用分配过程复杂，运算量大，一般认为代数分配法是几种分配法中最精确的方法，但其计算中尚存在一定的缺陷及计算复杂等原因，目前这一种方法还没有被广泛的采用。借助于Excel规划求解分析工具，不仅简化运算，提高工作效率，还能保证分配的准确。

关键词：Excel；规划求解；代数分配法

企业辅助生产并不直接作用于企业所销售的产品或服务，但提供辅助生产的耗费构成产品总成本或期间费用的一部分。企业辅助生产车间归集的耗费应当按照受益原则由受益车间或部门承担，分别计入产品成本或期间费用等，由于辅助生产车间经常相互提供产品或劳务，相互之间发生的辅助生产费用是否核算及采用哪种分配方法都会直接影响企业产品成本核算的准确性。企业在至少有两个相互提供产品或劳务的辅助生产车间，且期末没有在产品的情况下，辅助生产费用的分配方法可采用直接分配法、交互分配法、代数分配法、计划成本分配法、顺序分配法等，其中交互分配法和代数分配法都具有交互分配的性质，分配结果较精确，分配过程也比较复杂。在手工计算条件下，采用代数分配法计算工作量大且计算结果容易出错，为克服这方面的不足，可利用Excel规划求解分析工具设计把复杂的数据处理变得简单，提高产品成本核算的准确性和成本管理效率。

一、传统代数分配法的缺陷

代数分配法的原理是通过联立多元一次方程组，求解出各辅助生产产品或劳务的单位成本，以此作为费用分配率进行辅助生产费用分配的一种方法。企业采用包括代数分配法在内的各种辅助生产费用分配方法进行辅助费用分配时，都是把各车间按期归集的辅助生产费用按“谁受益、谁承担”的原则对受益部门进行费用分配，而忽略了辅助車间为自身提供劳务而承担费用的分配。如供气车间为本车间提供动力、机修车间为自身提供修理劳务、供电车间需要消耗本车间提供的电力，不管是哪种分配法包括代数分配法都没有把自身作为收益对象，没有考虑辅助车间消耗自身提供劳务而应承担费用。很显然，这种处理方法与实际是不相符的，但从简化核算工作量角度出发，大多数成本会计教材在介绍这些分配方法时，都没有把辅助车间自身作为受益对象分配该车间归集的辅助生产费用。

虽然这种分配方法计算结果最为精确，但在辅助生产车间较多的情况下，需要联立多元方程组求解未知数，计算过程较为复杂。企业若有多个辅助车间需进行交互分配且采用手工进行求解时，这种方法的使用受到一定的限制，究其原因：一方面要求会计人员有一定的数学基础；另一方面计算工作量非常大，计算结果时常出现错误，影响会计人员工作效率。因此，这种方法在会计实务工作中使用受到一定的限制。

二、规划求解应用实例

借助Excel分析工具，能够克服方程组计算工作量大的不足，极大的提高计算准确性和工作效率。本文通过下列案例加以说明：某企业有供气和运输两个辅助生产车间。2015年3月，在分配费用前，供气车间归集的辅助生产费用为29120元，供气车间供气88000立方米，其中，运输车间耗用8000立方米，基本车间直接用于产品生产耗用60000立方米，基本车间一般性耗用6000立方米，行政管理部门耗用14000立方米。本月运输车间归集的辅助生产费用为26880元，运输车间提供劳务量为42400小时，其中，供气车间耗用2400小时，基本车间耗用30000小时，行政管理部门耗用10000小时。要求采用代数分配法进行辅助生产费用分配（假设运输劳务单价x元/小时，供气单价y元/立方米）。

根据要求列出方程组：29120+2400x=88000y且26880+8000y= 42400x，引入Excel规划求解进行计算。“规划求解”是一组命令的组成部分，这些命令有时也称假设分析工具。使用该项分析工具，可求得工作表上某个单元格中公式的最优值。在Excel2003版本中，通过点击菜单工具-宏-加载宏，加“载规划求解加载项”便可加载。在Excel2007版本中，在Excel选项-加载项-转到Excel加载项，然后加载“规划求解加载项”便可以加载规划求解的宏。本文以Excel2010版本为例，第一步，在文件-选项-自定义功能区-勾选“开发工具”-在“开发工具”选项卡里点击“加载项”-勾选“规划求解加载项”-确定。第二步，建立辅助费用分配率计算表，并进行相应的设置。分别在A1、B1单元格中输入x、y；分别在A2、B2、C2单元格中输入-2400、88000、29120，分别在A3、B3、C3单元格中输入42400、-8000、26880；分别在A5、B5单元格中输入公式：“=SUMPRODUCT（A3：B3，A7：B7）”、“=SUMPRODUCT（A3：B3，A7：B7）”。第三步，运用“规划求解”，选定所求单元格，然后点击“数据”功能选项卡下“规划求解”命令并进行规划求解参数设置。设定目标：$B$5，目标值为26880，通过更改可变单元格设定为$A$5=$C$2及$B$5=$C$3，选择求解方法选定单纯线性规划。求解得出A7、B7单元格分别为0.7、0.35，即辅助生产费用分配率。第四步，根据分配率进行费用分配，建立辅助生产费用分配表，并设置相关计算公式。在辅助生产费用分配表内建立计算关系后，由计算机自动进行费用分配，会计人员只需根据计算结果进行账务处理即可。

三、规划求解意义

使用Excel工具辅助会计核算具有重要的作用，符合当今社会会计实践工作的发展潮流。随着Excel软件知识的普及和会计人员对Excel软件操作能力的提升，Excel工具不仅能与代数分配法结合使用，还能体现在其他的辅助生产费用分配方法中。这不仅提高了辅助生产费用分配结果的精确性，而且可以将会计人员从诸多辅助生产费用分配运算中解放出来，从而提高其成本管理工作的综合能力。另外，对于两个以上相互提供产品或劳务的辅助生产车间的企业，也可以直接利用Excel本功能完成其辅助生产费用分配工作，只需将此代数分配法稍作修改即可使用，这样极大节约了企业开发Excel软件的时间和费用，提高了会计人员工作效率。

参考文献：

张文福.基于Excel改进辅助生产费用代数分配法[J].财会月刊，2013（10）.