基于代数分配法的Excel应用

基于代数分配法的Ｅｘｃｅｌ应用

作者：许长荣

来源：《财会通讯》2008年第06期

代数分配法是工业企业辅助生产费用的一种重要分配方法，其特点是结果精确，最能体现受益原则。采用该分配方法，首先应根据各辅助生产车间相互提供产品和劳务的数量，建立联立方程式并求解，计算出辅助生产产品或劳务的单位成本(分配率)；然后根据分配率计算分配辅助生产费用。代数分配法的实质就是解联立方程组，但如果企业的辅助生产车间较多，则计算工作较繁，因而适用于实现电算化的企业。笔者认为，直接利用EXCEl．提供的函数MINVERSE与MMULT解线性方程组，快速便捷。

【例】假设光明公司有供水、供电、运输、机修四个辅助生产车间，具体资料见表1。

一、代数分配法的计算模型建立

首先，建立基础联立方程组。代数分配法的基本原理是“产出=投入”，对于每一个辅助生产车间而言，产出表现为它提供给其他部门的产品(劳务)的价值，而投入则表现为该车间发生的待分配辅助费用与其所消耗的其他部门产品(劳务)价值之和。假定水、电、运输、机修的单位成本(分配率)分别为xt元，吨、X2元，度、x，元／吨·公里、X4元，小时，根据上面的分析有方程组

最后，将方程组（2）转换成矩阵的形式。

(1)，每个方程等号左边代表产出．右边代表投入。(3)式左边的系数矩阵称为劳务量矩阵，记作A，右边的系数矩阵称待分配金额矩阵，记作B，将水、电、运输、机修单位成本

X1、x：、X，、【4组成的矩阵称为分配率矩阵，记作x，则(3)式可以简记为AX=B，根据矩阵的运算法则，X=A－1B，即：

在手工条件下计算(4)式是非常困难的，需先对劳务量矩阵A求逆，然后再计算其与待分配金额矩阵B的乘积，这也是代数分配法虽然精确但手工条件下却很少使用的原因。

二、代数分配法的Excel求解

首先，建立矩阵数据表。在一张EXCEL．表中输入上述两个矩阵A与B的数据，见表2的A1：E4。其次，运用EXCEL．函数计算式(4)。在EXCEL．数学与三角函数中有两个子函数MINVERSE与MMULT，MINVERSE函数用来对矩阵求逆，基本格式为MIN—

VERSE(array)，．Array是具有相等行数和列数的矩阵(数值数组)，可以用单元格区域表示，如表2中的A1：D4；也可以用数组表示为{74000，－7000，－4000，－400；－10000，26000，－15000，－1200；－2000，－1500，322200，－1000；－3000，－2500，－1200，3600}。一般用单元格区域表示简便直观，但需事先准备一张数据表，如表2。MMULT函数用来计算两

个矩阵的乘积，基本格式为MMULI’(array1，array2)，Arrayl与Array2为进行乘法运算的两个矩阵(数组)。由于劳务量矩阵A是4×4矩阵，故A也是4×4矩阵，待分配金额B是4×1矩阵，所以计算结果AB为4×1矩阵，因而要选中表2中的C6：C9四个单元格，见表2的下半部分。最后输入公式：=MMULT(MINV：ERSE(A1：D4)，E1：E4)，输完公式后按组合键Ctrl+Shift+Enter得到运算结果，见表3的上半部分。

三、辅助费用的分配

由表3可知：水、电、运输、机修的分配率分别为0．235683311、O．892356909、0．628082195、2．914344678，这些关键数据出来后剩下的工作就很简单了。只需在表1的基础上稍做修改，得到一张辅助生产费用分配工作底稿，见表3的下半部分。以供水车间辅助费用分配为例说明。在C13单元格输入计算公式：=IF(B13fI，“一”，ROUND(B13*$c$6，2)。该公式的含义是当B13(劳务耗用量)为空时，分配金额一栏用“一”表示，否则按照B13"$C$6(劳务耗用量＊分配率)来计算并保留两位小数；然后把该公式填充到C21单元格即可。值得注意的是，分配金额的计算公式中的分配率一定要用$c$6表示，不要写成0．235683311，否则会产生一定的计算误差。其余几个辅助生产部门的费用分配计算公式与之类似，不再重复。合计栏J13单元格的计算公式写成“=SUM(C13,E13,G13，113)”并填充至J21，不要写成

“=C13+E13+G13+113”，否则遇到分配金额栏目有“一”时后者无法计算。

参考文献：

[1]苏开华：《EXCEI在代数分配法中的应用》，《云南财贸学院学报》(社会科学版)2006年第2期。