6Excel在成本计算中的应用（课件）《EXCEL在财务中的应用》（航空工业出版社）

Excel在成本计算中的应用 项目六 1 知识目标： ①掌握前面所学的知识，熟练应用到成本计算模型中。 ②熟练掌握品种法核算产品成本的过程。 ③学会根据品种法核算产品成本的流程设计成本计算模型。 能力目标： ①利用Excel能够设计出适合企业生产特点的成本计算模型，将繁杂的成本核算工作简单化。 ②根据企业生产特点和成本管理上的要求选择合适的产品计算方法。 素养目标： ①通过对企业生产情况的分析，锻炼独立思考、解决实际问题的能力。 ②通过成本核算的讲解，培养忠于职守、精益求精、不弄虚作假的工作态度，以及知错就改的良好品质。 学习目标 冀中职业学院 目录 成本计算模型介绍 任务6.1 冀中职业学院 制作生产费用分配表 任务6.3 制作月初及本月发生费用统计表 任务6.2 制作完工产品成本计算单 任务6.4 构建成本计划模型 任务6.5 6.1.1 企业资料 冀中职业学院 任务6.1 成本计算模型介绍 中远公司是一个单步骤小型工业生产企业，设有一个基本生产车间，大量大批地生产甲乙两种产品。设有供水和机修两个辅助生产车间，为全厂提供劳务，辅助生产车间不单独核算制造费用。月末在产品的完工程度均为50原料在生产开始时一次投入。 有关费用分配方法： (1)甲乙产品共同耗用的材料按定额耗用量比例分配； (2)生产工人工资按生产工时比例分配； (3)制造费用按生产工时比例分配； (4)辅助生产成本按直接分配法分配； (5)产品成本需要在完工产品和月末在产品之间分配采用约当产量法； (6)产品成本按品种法核算。 6.1.1 企业资料 冀中职业学院 任务6.1 成本计算模型介绍 品种法核算程序如图所示。 品种法计算产品成本的六步中，第一步设置明细账和第六步结转完工产品成本，这两步在财务软件中实现。第二步到第五步，需要借助 Excel软件，进行数据计算来实现。下面我们一起来学习，品种法计算产品成本的模型建立及应用。 6.1.2 品种法计算产品成本的模型介绍 冀中职业学院 任务6.1 成本计算模型介绍 1.月初资料 月初在产品的资料包括月初在产品的成本资料和月初在产品的产量资料。“产量”表中， 月初在产品+本月投入-本月完工产品 =月末在产品。 6.1.2 品种法计算产品成本的模型介绍 冀中职业学院 任务6.1 成本计算模型介绍 2.本月发生费用表 根据本月发生的各项生成费用，填写各费用统计表。如图1、图2所示。 图1 图2 6.1.2 品种法计算产品成本的模型介绍 冀中职业学院 任务6.1 成本计算模型介绍 3.本月发生费用分配表 根据各项直接费用的分配方法分配直接费用，包括材料费用分配表、应付职工薪酬分配表、折旧及其他费用分配表，这些表格中都设置有公式，可以根据本月发生费用统计表中的数据自动计算出来，无需要手工计算。 6.1.2 品种法计算产品成本的模型介绍 冀中职业学院 任务6.1 成本计算模型介绍 4.计算本月完工产品实际总成本和单位成本 成本计算单中，所有数据均通过设置公式取数。将生产费用在完工产品和在产品之间，按照约当产量法分配。完工产品成本计算单中，按照产品品种计算出当月完工产品的总成本和单位成本，如图1至图 3所示。 图1 图3 图2 6.2.1 月初产品成本及产量表 冀中职业学院 任务6.2 制作月初及本月发生费用统计表 根据企业生产特点(单步骤小型工业生产企业，大量大批地生产甲乙两种产品)和管理需要，本企业采用品种法核算产品成本。而甲乙产品共同耗用的材料按定额耗用量比例分配，生产工人工资和制造费用按生产工时比例分配。因此，产品生产成本按照成本项目列示，分直接材料、直接人工、制造费用三项。这三项根据月初资料进行填列，成本合计栏需要进行求和公式设置。 6.2.1 月初产品成本及产量表 冀中职业学院 任务6.2 制作月初及本月发生费用统计表 方法一：单击要编辑公式的单元格 E3,单击菜单栏中的“公式”,可以选择“自动求和”,看一下默认的求和区域是不是从 B3:D3 单元格区域，按回车键。 方法二：选择“插入函数”进行合计公式设置。单击要编辑公式的单元格 E3,单击菜单栏中的“插入函数”,在对话框中选择“SUM函数”,单击“确定”,看数据默认区域是否是从B4到 D4,如果正是需要做合计的数据区域，单击“确定”;如果默认数据区域不是需要做合计的数据区域，需要手动重新选择数据区域。 具体做法：将鼠标放置在单元格 B4,单击拖动到 D4 即可，单击“确定”。 最终结果如图所示。 6.2.1 月初产品成本及产量表 冀中职业学院 任务6.2 制作月初及本月发生费用统计表 产量表：月末在产品数量=月初在产品数量+本月投入产品数量-本月完工产品数量。 公式设置具体操作如下： 方法一：单击需要编辑公式的甲产品月末在产品单元格，输入“=”,单击B9单元格，输入“+”,单击C9单元格，输入“-”,单击D9单元格，按回车键，公式设置完毕。 方法二：在编辑栏中编辑公式。单击要编辑公式的单元格，将鼠标放到公式编辑栏中，输入公式“=B9+C9-D9”,公式设置完毕，按回车键。乙产品月末在产品数量计算公式和甲产品的计算公式是一样的，无须手动输入公式，可以单击E9,将鼠标放到 E9单元格的右下角，当鼠标变成黑色的填充柄时，单击鼠标向下拖动即可。 最终结果如图6-2-2所示。 6.2.2 当期发生费用统计表 冀中职业学院 任务6.2 制作月初及本月发生费用统计表 本月发生的各项费用统计表，根据企业费用项目设置。各统计表根据实际发生费用金额进行填列(填充色部分),再做各项费用合计，设置求和公式(无填充色部分),如图1、图2所示。 图1 图2 6.3 制作生产费用分配表 冀中职业学院 任务6.3 制作生产费用分配表 “成本费用分配表”中，包括材料费用分配表、应付职工薪酬分配表、折旧及其他费用分分配表、辅助生产费用分配表和制造费用分配表。这些分配表中的公式设置是一样的。以材料费用分配表中公式的设置为例进行讲解。 生产甲产品耗用的A材料的金额，在前面“本月发生费用”工作表中的“材料费用表”中有原始数据，设置一个表间引用公式，将“材料费用表”中甲产品消耗的A材料的金额，也就是B3单元格中的数据，引用到“材料费用分配表”中的C4单元格中去。具体操作如下。 鼠标放到C4单元格中，输入“=”号，鼠标单击“本月发生费用”工作表中的B3单元格，然后按回车键。设置公式自动取数的好处是，当下个月甲产品消耗 A材料的金额发生变化时，只需要在“材料费用表”中修改相应数据，“材料费用分配表”中的相关数据也会跟着变化，无须手工录入数据，简单且避免了人工录入数据过程中发生的错误。 乙产品消耗 A材料的金额，仍设置数据的表间引用，方法一样，将鼠标放到C5单元格中，输入“=”号，鼠标单击“本月发生费用”工作表中的B4单元格，然后按回车键，将原始数据从“材料费用表”中引用到“材料费用分配表”中。 6.3 制作生产费用分配表 冀中职业学院 任务6.3 制作生产费用分配表 B材料的消耗定额，通过做表间引用，将数据从“材料费用表”中引用过来。乙产品的定额耗用量，可以采用拖动鼠标复制的方式。 编辑 B材料分配率的公式，它等于甲乙两种产品消耗的B材料的总数除以甲乙两种产品定额耗用量总数。甲乙两种产品消耗的B材料的总数在“本月发生费用”工作表图当中，“材料费用表”中的C5单元格，定额耗用量总数是这个“成本费用分配表”工作表中 D6 单元格的数据。具体操作如下。 6.3 制作生产费用分配表 冀中职业学院 任务6.3 制作生产费用分配表 鼠标放到 E6 单元格，输入“=”,鼠标单击“本月发生费用”工作表，单击“材料费用表”中C5单元格，输入“/”,单击“成本费用分配表”,单击D6单元格，按回车键，公式编辑完毕。一定要注意，单击完“本月发生费用”工作表的C5单元格以后，一定要先输入“/”,再回到“成本费用分配表”工作表当中。 甲产品的“分配金额”的公式为定额耗用量乘以分配率；乙产品的“分配金额”可以采用相同的方法。 乙产品的“分配金额”也可以采用倒挤法，小计的“分配金额”(F6单元格，11万元),减去甲产品“分配金额”,就是乙产品的“分配金额”。 甲乙“分配金额”都是用各自的“定额耗用量”乘以“分配率”(E6单元格),可以采用绝对引用的方式，比如，在F4单元格输入公式“=D4*$E$6”,按回车键，甲产品“分配金额”公式编辑完毕。鼠标放在这个单元格右下角，变成黑色填充柄的时候向下拖动，乙产品的“分配金额”公式也有了。 6.4 制作完工产品成本计算单 冀中职业学院 任务6.4 制作完工产品成本计算单 先看一下甲产品的“成本计算单”,甲产品的成本按照直接材料、直接人工和制造费用三个成本项目列示。行分为月初在产品成本、本月生产费用、生产费用合计、约当产量、产量合计、分配率、完成产品成本和月末在产品成本。月初在产品成本是引用的“月初资料”中的“月初在产品成本”表中的数据；本月生产费用是引用的“成本费用分配表”中相关费用项目的数据；生产费用合计是由月初在产品成本加上本月生产费用；约当产量是引用“月初资料”中“月末在产品”的产量数据，因为材料是在生产开始时一次性投入的，所以说在产品消耗材料的约当产量就等于在产品的数量； 直接人工费和制造费用，由企业资料可知，月末在产品完工程度均为50%所以月末在产品消耗的人工费和制造费用的约当产量，就是由月末在产品的数量乘以50%量合计是月末在产品的约当产量加上完工产品产量，即“月初资料”当中的D9单元格；分配率由生产费用总数除以总的产量数；完工产品的成本等于分配率乘以本月的完工产品数量，这个“月初资料”当中的 D9单元格就是本月完工产品的数量；为了避免尾差的出现，“月末在产品成本”采用的是倒挤法，由生产费用合计减去完工产品成本，就是在产品的成本。 6.4 制作完工产品成本计算单 冀中职业学院 任务6.4 制作完工产品成本计算单 再来看一下“完工产品成本计算单”,甲产品完工800件，乙产品完工900件。列项：每种产品的总成本、单位成本、和各成本项目占总成本的比例。横项：按照成本项目，分为直接材料、直接人工和制造费用。总成本是引用的“成本计算单”中的数据；单位成本是用这个总成本除以完工数量，完工产品的数量取自于“月初资料”;各成本项目占比是由各项目成本除以总成本，这就是各项费用的占比。乙产品同样的道理。 6.5 构建成本计划模型 冀中职业学院 任务 6.5 构建成本计划模型 随着生产日益社会化和现代化，企业规模不断扩大，工艺过程愈加复杂，企业经营过程中某一环节或者是某一短暂时期内的生产耗费一旦失去控制，都有可能给企业造成不可挽回的经济损失。因此，为了防止成本费用管理失控，必须科学地预见生产耗费的趋势和程度，以便在此基础上采取有效措施，从而搞好成本管理工作。这就是任务6.5的内容，如何利用 Excel 软件，构建成本计划模型。 我们一起来看一下这个成本计划模型及实际经营成本与计划成本的对比模型。这个模型左边的表，是2019年全年企业经营成本费用的计划表，右边是2019年1~5月份这五个月的实际经营成本费用数据及与计划数据的差异。先看一下左边的计划表，包括全年产销量、月均产量、各项成本费用总额及各项成本费用平均到每单位产品的金额。有些固定费用与产量是没有关系的，是按照月均费用进行的预测，在月均费用列进行列示。表的下面，有产品预计销售单价，进而预测出单位产品的利润。 6.5 构建成本计划模型 冀中职业学院 任务 6.5 构建成本计划模型 接下来，给大家具体介绍一下这个模型的构建。这个企业全年产销基本平衡，所以产量与销量相等，这个产销量是根据企业销售部门全年销售计划，结合企业生产能力预测的。月产销量是全年产销量除以12个月得到的平均数。 各项成本费用，分为固定成本和变动成本。固定成本按照近几个月，月均费用来确定总费用，除以总产量即为单位产品成本。变动成本与产品产量有关，按照近几个月的单位产品消耗成本，乘以总产量，即为总成本。比如，原材料的单位产品消耗成本，是最近三个月实际单位成本的平均数。大家看到 D7单元格，右上角有红色三角，而且很多单元格都有这个红色三角。将鼠标放在这样的单元格上，会出现一个文字批注，这就是给这个单元格添加的批注，注明这个数据是根据什么预测的。原材料总成本由单位产品消耗的材料成本乘以总产销量。包装物跟原材料费用一样，都属于变动成本，跟原材料的预测方法是一样的。 6.5 构建成本计划模型 冀中职业学院 任务 6.5 构建成本计划模型 制造费用，按照费用明细列示，包括工资、维修费、水费、电费、折旧费、天然气费用。工资费用每个月固定不变，按照月均费用列示，总的人工费用由月均费用乘以12个月，单位产品人工费用由总人工费除以全年产销量。维修费和折旧费用，均属于固定成本，跟人工费的预测方法是一样的。而水费，电费、天然气费用都属于变动成本，跟原材料的计划值的确定方法一样，取最近几个月的平均单位成本，进而得出全年总成本。 三大期间费用的计划值也是按照这样的方法确定的。不含税销售单价是由销售部门根据市场情况预测的。产品单价减去单位成本，就是单位产品利润。再看1-5月实际经营数据，按照账上金额进行填列。实际单位产品成本与计划的差额，可以看出哪些费用比计划超支了，哪些费用比计划节约了。各项成本费用差额占总差额的比例，可以看出实际成本偏离计划成本的主要是哪些成本项目。去探究产生偏差的原因是什么,从而采取措施及时纠正。下面是5个月内产品的平均销售单价，根据与预测值的差异，分析产生差异的原因。最后是单位产品的实际利润，由实际单价减去实际单位成本，从而跟账上的利润去比对，以检验模型中数据的准确性。 $$