第七章《Excel在财务管理中的应用》北邮

1、第七章Excel在成本核算中的应用 主要概念品种法是以产品品种为成本核算对象，归集生产费用，计算产品成本的一种方法。品种法是基本方法中最基本的产品成本核算方法。采用这种方法，既不要求按照产品批别计算成本，也不要求按照产品生产步骤计算成本，而只要求按照产品的品种计算产品成本。品种法主要适用于大量大批单步骤生产的企业；在大量大批多步骤生产的企业中，如果企业生产规模较小，而且成本管理上又不要求提供各步骤的成本资料时，也可以采用品种法计算产品成本；企业的辅助生产（如供水、供电、供气等）车间也可以采用品种法计算其劳务的成本。分步法是按照产品的生产步骤归集生产费用，计算产品成本的一种方法。分步法主要适用于

2、大量、大批的多步骤生产。为了加强成本管理，不仅要求按照产品品种归集生产费用，计算产品成本，而且还要求按照产品的生产步骤归集生产费用，计算各步骤产品成本，以提供反映各种产品及其各生产步骤成本计划执行情况的资料。分批法是按照产品批别归集生产费用、计算产品成本的一种方法。按照产品批别组织生产时，生产计划部门要签发生产通知单下达到车间，并通知会计部门。在生产通知单中应对该批生产任务进行编号，称为产品批号或生产令号。会计部门应根据生产计划部门下达的产品批号，也就是产品批别，设立产品成本明细账。分批法适用于分批生产和单件生产，例如精密仪器、专用设备、重型机械和船舶的制造，某些特殊或精密铸件的熔铸，新产品的

3、试制和机器设备的修理，以及辅助生产的工具模具制造等。 知识拓展拓展1： 交互分配法计算的Excel处理一、交互分配法涵义及计算公式交互分配法也称一次交互分配法,是根据企业各辅助生产单位相互提供劳务数量的费用分配率(单位成本),在各辅助生产单位之间进行一次交互分配;再将交互分配后辅助生产单位的全部应分配费用(即交互分配前的待分配费用,加上交互分配转入的应负担费用,减去交互分配转出的费用),按提供劳务的数量,在辅助生产以外的各受益对象之间进行分配。其目的是提高生产成本辅助费用分配的合理性、准确性。相关费用分配的计算公式为:交互分配费用分配率=交互分配前辅助生产单位的待分配费用总额/该辅助生产单位的

4、劳务供应总量;某辅助生产单位应负担的费用=该辅助生产单位的劳务供应总量交互分配费用分配率;对外分配费用分配率=(交互分配前待分配费用总额+交互分配转入费用-交互分配转出费用)/该生产单位供应给辅助生产以外部门的劳务总量;某辅助生产以外部门应负担费用=该生产单位或部门接受的劳务总量对外分配费用分配率。由于在分配费用时都要计算交互分配和对外分配两个费用分配率,进行两次分配,增加了计算的工作量。另一个问题是在进行分配率的计算时,由于存在循环小数的处理问题,如果采用四舍五入的方法,会影响数据处理的精确性。为了较好地解决该问题,可运用Excel软件来处理。该方式的好处就在于可以由数据本身来确定精度,不须

5、考虑数据的定位、舍位问题,简化了数据的运算过程,提高了结果的精确性、合理性。二、交互分配法计算模型的建立启动Excel软件,系统自动地建立一个名为Book1的工作簿。将其存盘在指定位置,并命名为“交互分配法模型”;将其内部命名的Sheet1、Sheet2的工作表,分别重命名为“辅助生产费用汇总表”、“交互分配法”,建立模型簿(如图1)。(一)“辅助生产费用汇总表”的建立在“辅助生产费用汇总表”的工作表中A1单元格内输入表格名:“辅助生产费用汇总表”,并选中A1E1区域,再单击格式“工具栏”中“合并及居中”按钮;在A2单元格中输入“科目名称”,并选中A2B2区域,再单击格式“工具栏”中“合并及居

6、中”按钮;在C2单元格中输入“金额”,依此类推输入如图2所示的文字。然后将所建表格,按一定的方案进行格式设置。(二)“辅助生产费用分配表”的建立一是设置文字格式。在“交互分配法”的工作表中A1单元格内输入表格名:“辅助生产费用分配表”,并选中A1I1区域,再单击格式“工具栏”中“合并及居中”按钮;在A2单元格中输入“劳务部门”,并选中A2B2区域,再单击格式“工具栏”中“合并及居中”按钮,然后再选择“右对齐”;在A3单元格中输入“受益部门”,并选中A2B3区域,再单击格式“工具栏”中“合并及居中”按钮,然后通过“设置单元格格式”将“对齐”方式选择为“垂直对齐”且“靠下”;在C1单元格中输入“供

7、水车间”,依此类推输入如图1所示的文字。然后将所建表格,按一定的方案进行格式设置。二是设置数值公式。文字格式设置好后,以前述计算公式为依据,设置表格中数据公式,以利于建立与“辅助生产费用汇总表”中数值的联动关系并显示结果值。三是设置交互分配公式。如图1,在C5的单元格中,输入“=辅助生产费用汇总表!D9”;在C7的单元格中,输入“=-辅助生产费用汇总表!D6”。在D5的单元格中,输入“=E5/C5”。在E5的单元格中,输入“=辅助生产费用汇总表!C5”;在E6的单元格中,输入“=-H6”;在E7的单元格中,输入“=C7*D5”。在F5的单元格中,输入“=辅助生产费用汇总表!E9”;在F6的单元

8、格中,输入“=-辅助生产费用汇总表!E5”。在G5的单元格中,输入“=H5/F5”。在H5的单元格中,输入“=辅助生产费用汇总表!C6”;在H6的单元格中,输入“=F6*G6”;在H7的单元格中,输入“=-E7”。在I5的单元格中,输入“=E5+H5”。四是设置对外分配公式。如图1,在C8的单元格中,输入“=SUM(C5:C7)”;在C10的单元格中,输入“=辅助生产费用汇总表!D4”;在C11的单元格中,输入“=辅助生产费用汇总表!E4”;在C12的单元格中,输入“=辅助生产费用汇总表!D8”;在C13的单元格中,输入“=SUM(C10:C12)”。在D8的单元格中,输入“=E8/C8”。在

9、E8的单元格中,输入“=SUM(E5:E7)”;在E10的单元格中,输入“=C10*$D$8”;在E11的单元格中,输入“=C11*$D$8”;在E12的单元格中,输入“=C12*$D$8”;在E13的单元格中,输入“=C13*$D$8”。在F8的单元格中,输入“=SUM(F5:F7)”;在F9的单元格中,输入“=辅助生产费用汇总表!E3”;在F10的单元格中,输入“=辅助生产费用汇总表!E4”;在F11的单元格中,输入“=辅助生产费用汇总表!E7”;在F12的单元格中,输入“=辅助生产费用汇总表!E8”;在F13的单元格中,输入“=SUM(F9:F12)”。在G8的单元格中,输入“=H8/F

10、8”。在H8的单元格中,输入“=SUM(H5:H7)”;在H9的单元格中,输入“=F9*$G$8”;在H10的单元格中,输入“=F10*$G$8”;在H11的单元格中,输入“=F11*$G$8”;在E12的单元格中,输入“=F12*$G$8”;在E13的单元格中,输入“=F13*$GH$8”。在I8的单元格中,输入“=E8+H8”;在I9的单元格中,输入“=E9+H9”;在I10的单元格中,输入“=E10+H10”;在I11的单元格中,输入“=E11+H11”;在I12的单元格中,输入“=E12+H12”;在I13的单元格中,输入“=E13+H13”。设置完毕,模型建立,这时在“辅助生产费用分

11、配表”所显示的是处理后的结果值。虽然设置比较繁锁,但在数据的后期处理上,提供了可靠的、快捷的、正确的方式。三、交互分配法模型应用图2所示为某一铅笔生产型企业某月末生产铅笔耗用辅助费用汇总情况表。由于是依据该企业数据来设置模型,故数据公式设置后系统自动生成结果值(如图1)。模型使用的关键,主要是“辅助生产费用汇总表”(如图2)中数据来源问题,有二种获取方法。(一)账表文件转换成Excel文件一是直接输入。利用财务软件能在输出数据时将其保存为Excel类型文件的功能,直接将财务软件数据输入到“交互分配法”工作薄中“辅助生产费用汇总表”,这时模型自动地在“辅助生产费用分配表”中形成计算结果值。二是复

12、制输入。利用财务软件汇总功能形成如图2格式的账表文件(此时的文件不是Excel类型的文件),然后通过“复制”的技术,将账表格式的图表“粘贴”入“交互分配法模型”工作薄中的“辅助生产费用汇总表”内(如图2),即可由模型自动地在“辅助生产费用分配表”中形成计算结果值。(二)手工填入在传统会计方式或电算化会计方式无法形成以上文件时,可采用手工方式。从生产成本明细账薄查抄数据,填入“辅助生产费用汇总表”(如图2)中数据对应的单元格内,则由数据公式可直接在“辅助生产费用汇总表”内(如图1)自动生成计算结果值。模型的设置不仅能处理多种数据源的问题,还能提高数据计算处理的精确性、及时性,使原本较繁杂、专业的

13、数据公式变得直观、易用,从而有效地提高了成本管理的水平,强化了会计的管理职能。拓展2： 利用EXCEL进行企业成本核算分析一、成本核算的步骤企业的生产经营过程也是资产的消耗过程，因此生产经营成本按其经济性质分为劳动对象、劳动手段和活劳动耗费三大类。企业成本核算过程中，在财务软件中应设置“生产成本”一级科目，借方用于归集所发生各项生产费用，贷方登记结转库存商品的成本。在“生产成本”科目下设置“基本生产成本”和“辅助生产成本”两个二级科目。根据成本要素在二级科目下设置三级科目“，直接材料”、“直接人工”、“动力”和“制造费用”用于成本核算。在成本计算过程中，涉及成本的归集和分配，计算完工产品成本和

14、月末在产品成本，进而计算产品的销售成本。在核算中，结合企业自身生产经营特点，利用Excel表进行成本计算和分析，通过表与表之间的链接，使得整个计算过程变得简洁，便于操作。（表1）下面以品种法为例作介绍。企业根据自身生产经营组织形式、产品工艺特点和需要掌握的成本分析资料，将产品分类进行核算。表中以A产品成本核算为例，A产品领用的直接材料从金蝶等财务软件中生产成本-基本生产成本-直接材料科目下取数，财务软件系统一般都有导出成EXCEL电子表格的功能，粘贴在生产费用表中，直接人工、动力、制造费用和辅助生产从财务软件中取数直接计入，也可以采用一定的分配标准分配计入，将本月发生的生产费用归集到生产费用表

15、中，填入下面的生产成本明细账中。（表2）制造部门将产品做好之后，完工入库，月末统计库存商品月报表。报表之间的钩稽关系是：库存商品月报表本期进库数C1=产品成本计算表库存商品产量B2=库存商品明细账入库数B3；库存商品月报表本期出库数D2=库存商品明细账出库数D4=销售成本表销售数量B2。（表3）月末制造部门统计在制品数量，统计每种产品各个工序数量，根据公司耗用原材料和人工工时情况，确定完工程度。完工程度的方法确定之后，一般不得经常变换。（表4）期初结存数等于上期期末结存数，表中领料工费从生产费用表中取数，累计数=期初结存数+本期发生数，第5行结转成本是本月完工入库产品成本，根据产品成本计算表得

16、到。在制品成本=生产费用累计数-本月完工入库成本。同时，编制产品成本计算表，通过生产费用归集，统计库存商品月报表，在制品按照约当产量法计算。（表5）本期发生的生产费用在完工产品和月末在制品之间分配，计算出完工产品成本。（表6）本期库存商品入库数量，取的是库存商品月报表的入库数量，入库金额取的是产品成本计算表F2计算数据，发出商品成本采用加权平均法计算。发出商品成本=（上期库存商品结存金额+本期入库商品金额）/（上期库存商品结存数量+本期入库商品数量）发出商品数量期末结存库存商品金额=期初结存库存商品金额+本期完工入库商品金额-本期出库商品金额这个金额反映在资产负债表存货项目库存商品中。（表7）

17、销售成本中，从财务软件中导出产品销售数据，取得产品销售数量和销售收入，与库存商品月报表发出数量核对，销售成本等于库存商品账中发出商品成本，也是损益表中产品销售成本。产品成本计算过程主要设计7张报表就可完成。二、成本数据分析成本计算好之后，就利用上面EXCEL表，可以灵活方便地对成本进行分析，下面以销售成本结构分析为例进行介绍。（表8）在成本分析中，生产成本基本生产成本科目下设有直接材料、直接人工和制造费用等成本项目，销售成本科目中没有设直接材料、直接人工和制造费用等成本项目，作为管理者特别想知道本期销售产品中，材料、人工、制造费用等占销售收入比重，有利于企业分析各成本要素所占销售比重，比较产品

18、成本变化趋势，便于企业作出成本控制。表8中，期初库存产品成本+本期入库商品成本=本期发出商品成本+期末库存商品成本移项，期初库存产品成本-期末库存商品成本+本期入库商品成本=本期发出商品成本。本期入库商品成本从生产成本明细账中取数，能取到结转的直接材料、直接人工、动力、制造费用和辅助生产的金额。销售成本中含有直接材料=（完工入库的直接材料成本/完工入库商品成本）销售成本同理，可以求出销售成本中直接人工、动力、制造费用和辅助生产成本，销售成本率=销售成本/销售收入，是反映企业成本最重要的财务指标，材料成本率=销售成本中的材料成本/销售收入，同理可求出人工成本率、动力成本率、制造费用成本率和辅助生

19、产成本率。销售成本率=材料成本率+人工成本率+制造费用成本率+动力成本率+辅助生产成本率，分析各成本要素占销售比重，企业每月作出各类产品成本分析表，各期进行对比分析，分析成本变动的原因，制定成本控制的有效方法。可以看出，EXCEL在成本计算和成本分析中具备灵活简便的功能，而基于EXCEL表维护和更新也非常简单，财务人员通过简单的操作就可以从企业生产经营情况出发设计符合需要的成本计算和成本分析表，从而提高财务工作效率。 案例分析案例1：逐步结转分步法Excel模板构建红山公司A产品成本计算采用逐步结转分步法(综合结转方式),A产品的产量记录及有关成本资料如各步骤成本计算单所示。原材料在生产开始时

20、一次投入,各步骤在产品的完工程度为50%,生产费用在完工产品和在产品之间的分配采用约当产量法。A产品的生产分为三个步骤,分别由三个车间进行。第一车间生产甲半成品,将完工的甲半成品直接转给第二车间用于生产乙半成品;第二车间将完工的乙半成品直接转给第三车间用于生产A产品,半成品不通过半成品库收发。表中成本单位均为元,数量单位均为件。(一) 构建各步骤成本计算单模板各生产步骤成本计算单模板与公式如表1与表2所示。说明:各成本计算单中需要填列的原始数据均以加粗字体显示,只要填列或替换必要的原始数据,月末在产品费用分配率、完工产品成本、月末在产品成本等数据均会自动计算填列,从而实现逐步结转分步法(综合结

21、转方式)下,生产成本的自动更新,大大简化计算过程。(二) 构建产品成本还原模板其过程如表3所示。说明:成本还原计算模板中需要填列的原始数据均以加粗字体显示,只要填列或替换必要的原始数据,就会实现产品成本的自动还原计算,大大简化计算过程。通过半成品库收发的情况,在各生产步骤中生产和领用的半成品,可能不是当月生产的。由于以前月份所产半成品与本月所产半成品的成本构成不可能完全一致,甚至会有不小的差距。因此,在各月所产半成品的成本构成变动较大的情况下,按照上述方法进行成本还原,对还原结果的正确性就会有较大的影响。在这种情况下,如果半成品的定额成本或计划成本比较准确,为了提高还原结果的正确性,产成品所耗

22、半成品费用可以按定额成本或计划成本的成本构成进行还原。如果采用该做法,成本还原计算表第4行和第6行按成本项目分列的本月所产半成品的总成本,应改为按成本项目分列的半成品定额单位成本或计划单位成本,以便能更准确地反映各成本项目的构成比例。案例2： 巧用Excel进行成本分步结转炭素公司共三个生产车间，煅烧车间、成型车间及焙烧车间。煅烧车间领用石油焦进行煅烧生产出煅后焦，月末根据煅后焦产量进行交库，作为成型车间的原料。成型车间领用煅后焦、加入破碎残极，最后加入煤沥青进行粘合产出生阳极碳块，最后焙烧车间领用生阳极碳块加入重油等燃料进行焙烧生产阳极碳块。各车间产品成本进行逐步结转，月末在产品按实际加权平均计算，以五月份成本资料为例（单位万元），计算过程如下：(1) 选定Excel的一张工作表，在该表上设计生产成本（煅后焦）数量金额明细账格式，并根据各种生产成本及费用分配表