新编Excel在财务中的应用（第4版）课件项目6、7 Excel在进销存管理中的应用、Excel在成本核算中的应用

EXCEL在进销存管理中的应用目录CONTENT01采购与付款业务02销售与收款业务03出入库管理04编制总账和明细账04应收款分析01PARTONE采购与付款业务建立商品列表及期初数据表、采购数据清单1、新建一个

Excel

工作簿，命名为“项目六Excel进销存管理”。2、把Sheet1命名为“商品列表及期初数据”，3、建立材料采购数据清单，根据自身需要设置项目名称这要注意：在输入信息时，为了提高信息输入速度，防止错误发生，可以对部分项目进行有效性设置，例如供应商。）4、利用开始-条件格式-突出显示单元格规则-等于

命令把结清货款的单元格设置背景颜色。5、输入表体内容，使用VLOOKUP函数，内容可通过Sheet1商品列表对应关系自动显示。（商品名称、商品编码、型号、规格等）注意：

VLOOKUP函数建立付款业务数据清单6、建立付款业务数据清单，设置项目名称这要注意：在输入信息时，为了提高信息输入速度，防止错误发生，可以对部分项目进行有效性设置，结算方式主要包括现金支票、转账支票、银行汇票、银行本票、汇兑、信用证等。供应商有美月公司、前进公司，注意设置时序列中各项之间用英文的逗号隔开）7、输入表体内容，“=SUMIF(材料采购业务!K$2:K$25,付款业务!D2,材料采购业务!J$2:J$25)”。注意：

SUMIF函数的熟练使用0PARTtwo销售与收款业务

建立销售业务数据清单1、建立销售业务数据清单

，设置项目名称。注意：在输入信息时，为了提高信息输入速度，防止错误发生，可以对部分项目进行有效性设置，2、设置条件格式

，利用开始-条件格式-突出显示单元格规则-等于

命令把结清货款的单元格设置背景颜色。3、输入表体内容，使用VLOOKUP函数，内容可通过商品列表对应关系自动显示。（商品名称、商品编码、型号、规格、单位信息利用VLOOKUP函数自动获取）注意：VLOOKUP函数的熟练使用4、为方便计算每笔销售业务的销售成本和销售毛利，在销售业务表中增加2列，即“销售成本”“销售毛利”。通过与库存管理的配合使用，可实时获得每笔销售业务的利润。5、为方便计算每笔销售业务的销售成本和销售毛利，在销售业务表中增加2列，即“销售成本”“销售毛利”。通过与库存管理的配合使用，可实时获得每笔销售业务的利润。

建立收款业务业务数据清单1、打开“项目六Excel进销存管理”工作簿，插入一个新的工作表，重命名为“收款业务”2、设置条件格式

，利用开始-条件格式-突出显示单元格规则-等于

命令把结清货款的单元格设置背景颜色。3、输入项目名称4、输入表体内容。直接输入收款日期、结算方式、结算票据号和客户等信息，应收货款和已收货款等信息可通过“产品销售业务”工作表计算得到。在E2单元格中输入函数“=SUMIF(产品销售业务!C2:C9,收款业务!D2,产品销售业务!J2:J9)”，在F2单元格中输入函数“=SUMIF(产品销售业务!C2:C9,收款业务!D2,产品销售业务!K2:K9)”。5、通过计算，收款业务表0PARTTHREE出入库业务

建立完工入库数据清单1、为计算方便，完工入库表的项目名称沿用采购业务的项目名称。新建一个工作表，直接输入完工入库的信息建立车间领用材料数据清单1、直接输入工作表的项目名称和数据，记录材料和商品的收发情况。

建立出库业务总数据清单1、打开“项目六Excel进销存管理”工作簿，插入一个新的工作表，重命名为“出入库业务”。2、输入项目名称。3、输入表体内容。按日期先后输入出入库业务汇总情况，商品名称、型号规格、单位等信息可以用VLOOKUP函数从“商品列表及期初数据”工作表中引入。4、通过计算，生成出入库业务汇总表。

注意：出入库业务汇总数据清单也可通过复制期初数据、材料采购业务、产品销售业务、完工入库、车间领用材料几个数据清单中的数据生成，但粘贴时应注意只粘贴数值。因考虑到这几个数据清单的使用部门不同，所以此处采用直接输入的方式生成出入库业务汇总表。0PARTfour编制总账和明细账

建立库存商品原材料总账1、打开“项目六Excel进销存管理”工作簿，插入一个新的工作表，重命名为“总账”，输入项目名称。在实际工作中，企业可根据自身业务的需要来设置项目名称。2、利用条件格式对存货数据进行监控，“开始”|“条件格式”|“突出显示单元格规则”|“小于”或“大于”命令，对“小于200”的单元格设置背景色为黄色，对“大于2000”的单元格设置为浅红填充色深红色文本，并将条件格式向下复制到其他单元格。3、输入表体内容，除商品名称和规格型号外，所有数据均可利用公式自动生成，熟练使用SUMIF函数。

建立库存商品原材料明细账1、既可使用数据透视表，也可使用筛选的方法。利用数据透视表生成明细账的方法。2、新建工作表，分别命名为“内墙涂料MT-3”“外墙涂料VOC-5A”“粉剂原料FJ-N9100”“粉剂原料FJ-G1080”“固态原料X310-I”

根据实际情况，建立各工作表数据清单列字段名

。3、自动筛选“出入库业务”工作表。打开“出入库业务”工作表，单击“数据”|“筛选”按钮。然后单击“商品名称”右侧的下拉按钮，出现筛选条件，在筛选条件中选中“内墙涂料”复选框，筛选内墙涂料MT-3的信息，把筛选结果复制到“内墙涂料MT-3”工作表中。4、通过计算，生成信息表。

0PARTfive应收账款分析应收账款分析计提坏账准备进行应收账款账龄分析建立应收账款数据清单应收帐款分析

建立应收账款数据清单1、新建一个工作表，重命名为“应收账款”输入表体内容。直接输入公司名称、发票号、应收账款金额、开票日期、收款期限信息，其他数据信息通过函数公式自动生成。

公式：在“到期日”列的F2单元格中输入公式“=D2+E2”；

在“是否到期”列的G2单元格中输入公式“=IF(F2<$N$2,"是","否")”，其中N2单元格存放收款的“当前日期”2、应收账款按账龄进行分类，

公式：

在“未到期”列的H2单元格中输入公式“=IF($N$2-F2<0,C2,"/")”，

在“0～60天”列的I2单元格中输入公式“=IF(AND($N$2-F2>0,$N$2-

F2<=60),C2,"/")”，

在“60～120天”列的J2单元格中输入公式“=IF(AND($N$2F2>60,$N$2-F2<=120),C2,"/")”，

在“121～365天”列的K2单元格中输入公式“=IF(AND($N$2-F2>120,$N$2-F2<=366),C2,"/")”，

在“366天以上”列的L2单元格中输入公式“=IF($N$2-F2>366,C2,"/")”；

第10行为“合计”数据，所以在H10单元格中输入公式“=SUM(H2:H9)”。3、设置单元格的条件格式

建立应收账款数据清单

进行应收账款账龄分析1、插入一新工作表，重命名为“应收账款账龄分析表”，并输入表头信息。2、从“应收账款”工作表中复制相关信息。选中“应收账款”工作表的H2:L2单元格区域后右击，选择“复制”命令。然后在“应收账款账龄分析表”工作表中选中A4单元格后右击，选择“粘贴选项”中的命令，即可把以前横向显示的信息改为纵向显示。3、应收账款”工作表的H10:L10单元格区域后在“应收账款账龄分析表”工作表中选中B4单元格右击，选择“选择性粘贴”命令。在打开的“选择性粘贴”对话框中选中“数值”单选按钮和“转置”复选框即可把各账龄区间的应收账款金额复制过来4、在B9单元格纵向求所有应收账款的和5、在“百分比”列的C4单元格中输入公式“=B4/B$9”，并向下自动填充。得到的应收账款账龄分析表描述了不同时间区域内应收账款所占的百分比。

进行应收账款账龄分析

计提坏账准备1、复制“应收账款账龄分析表”，重命名为“坏账准备计提表”。2、输入每个区间的估计损失率。3、在D4单元格中输入公式“=B4*C4”，并自动填充至D列的其他单元格中。

EXCEL在成本核算中的应用目录CONTENT01基础数据表的制作02材料费用及动力费用的分配03职工薪酬费用的分配04折旧费用的分配05辅助生产费用的分配06制造费用的分配07生产费用在完工产品和在产品之间的分配01PARTONE基础数据表的制作一、建立基础数据和费用分配表基本思路：1）成本核算涉及的某项基础数据编制独立的表格，可方便每月根据企业费用发生的实际情况进行修改；2）费用分配表根据基础数据表编制，利用引用从基础数据表取数，然后利用公式进行费用的自动分配。一、建立基础数据和费用分配表建立基础数据和费用分配表格是指初次使用时，应根据企业成本核算的程序编制与成本核算有关的基础数据及费用分配表格，如材料耗用、材料费用分配表、电费情况、动力费用分配表、职工薪酬费用、职工薪酬费用分配等表格。一、建立基础数据和费用分配表步骤

1打开Excel软件，建立新的工作簿。步骤

2把工作表重命名为“材料耗用”。步骤

3增加其余所需的工作表。二、输入与成本核算有关的基础数据步骤

1在“材料耗用”工作表的单元格中分别输入行列标题信息。步骤

2对相应单元格进行合并，并设置居中。步骤

3按照表7.3所示数据输入当月各种材料的耗用信息。步骤

4使用自动调整列宽命令，把表格内容调整为合适的列宽，二、输入与成本核算有关的基础数据步骤

5表头行背景设置浅蓝色。步骤

6完成表格边框的绘制。步骤

7依据步骤1到步骤6的方法，完成其他工作表的设置01PARTTWO材料费用及动力费用的分配一、编制材料费用分配表和动力费用分配表1）直接材料的耗用，能分清受益产品的，采用直接追溯法，计入相应产品的直接材料成本成本项目；直接材料的耗用不能分清受益产品的，采用分摊法，分配计入相应产品成本的直接材料成本项目。2）间接材料的耗用，即车间一般性耗用的机物料等计入车间的制造费用，月末再转入产品成本的制造费用成本项目。3）行政部门和销售部门耗用的材料，计入管理费用和销售费用，月末转入本年利润。材料费用分配与动力费用分配相似一、编制材料费用分配表和动力费用分配表1．表头设置步骤

1设置“材料费用分配表”工作表的格式及行列标题。步骤

2表头行背景设置浅蓝色。步骤

3完成表格边框的绘制。2．应借科目设置输入应借科目信息。二、进行材料费用的分配和动力费用的分配1．基础数据取数公式的设置步骤

1从“材料耗用”工作表取数，并使用SUM求和。步骤

2使用复制粘贴或填充柄工具输入其他需要取数及求和单元格。步骤

3分配计入的材料费用总额和分配标准使用公式来取数。二、进行材料费用的分配和动力费用的分配2．分配率、分配额、合计数公式的设置步骤

1计算分配计入2种产品材料费用的金额。步骤

2计算合计列的合计数。步骤

3补充完成剩余合计数单元格的计算。步骤

4调整表格的列宽。二、进行材料费用的分配和动力费用的分配01PARTTHREE职工薪酬费用的分配一、编制职工薪酬费用分配表1）职工薪酬费用的分配是按照员工所在部门或服务的对象进行费用分配。包括支付给职工的工资、职工保险、公积金等职工福利费用的计算与分配。2）分配逻辑：生产工人的薪酬，一般不能分清具体哪些工人生产哪种产品，所以一般采用分摊法，直接按一定的标准分配计入相应产品的直接人工成本项目；车间管理人员的薪酬一般计入车间的制造费用，月末再转入产品的制造费用成本项目；行政部门和销售部门人员的工资计入管理费用和销售费用，月末转入本年利润。一、编制职工薪酬费用分配表1．表头设置步骤

1设置“职工新城费用分配表”工作表的格式及行列标题。步骤

2表头行背景设置浅蓝色。步骤

3完成表格边框的绘制。2．应借科目设置输入应借科目信息。二、进行职工薪酬费用的分配1．基础数据取数公式的设置步骤

1从前面的“职工薪酬费用”工作表中取数。步骤

2输入工时分配标准和，相关福利费用计提比例。二、进行职工薪酬费用的分配2．职工薪酬费用分配率、分配额、合计数公式的设置步骤

1计算分配标准生产工时总额，计算分配率，完成分配计入金额的计算。步骤

2计算工资合计。将直接计入部分和分配计入部分相加。步骤3完成剩余合计单元格的填充和计算。二、进行职工薪酬费用的分配3．职工福利费用的计算使用混合引用，利用复制粘贴工具完成职工福利费用的自动计算。4．剩余合计内容的输入步骤

1完成“小计”行公式的输入。步骤2完成“合计”行公式的输入。步骤3完成“合计”列公式的输入。步骤4调整表格的列宽。01PARTFOUR折旧费用的分配一、编制折旧费用计算表1）折旧费用是按照固定资产的使用部门进行费用分配的。2）分配逻辑：生产车间固定资产的折旧费一般计入车间的制造费用，月末再转入产品成本的制造费用成本项目；行政部门和销售部门固定资产的折旧费，计入管理费用和销售费用，月末转入本年利润。一、编制折旧费用计算表1．折旧费用计算表结构的设置步骤

1将“固定资产情况”工作表的相关单元格区域复制到“折旧费用计算表”工作表的区域。步骤

2对“折旧费用计算表”工作表的列进行调整，然后绘制边框。步骤

3完成折旧费用计算表结构的设置。一、编制折旧费用计算表2．完成本月折旧额的计算步骤

1计算当月各类资产的月折旧率。步骤

2计算本月计提折旧各类资产的原值。步骤

3算各部门当月每类资产的月折旧额。二、编制折旧费用分配表1．利用数据透视表进行折旧费用的分配步骤

1把折旧费用计算表的所有数据区域选为需要分析的数据。步骤

2设置数据透视表的行标签、列标签及值。步骤

3对数据透视表显示结果进行调整。步骤4进行标签排列顺序的改变。二、编制折旧费用分配表2．对折旧费用分配表进行格式编辑步骤

1设置表头和行标题。步骤

2设置显示效果，包括调整列宽，设置表头颜色，设置边框，设置表体颜色。步骤

3对数据透视表显示结果进行调整。01PARTFIVE辅助生产费用的分配一、编制辅助生产成本明细账1）辅助生产费用的核算，首先应该将各辅助生产车间在提供服务过程中所发生的各项耗费用进行归集，然后再将归集的费用按受益部门（或产品）的受益量进行合理分配。2）就北方机械公司来说：首先，应按辅助生产车间编制辅助生产成本明细账，对辅助生产费用进行归集。然后，根据辅助生产费用明细账归集的结果，按照辅助生产车间对外服务的情况，编制辅助生产费用分配表，将辅助生产费用进行分配。一、编制辅助生产成本明细账步骤

1设置“辅助生产成本明细账”标题。步骤

2完成机修车间“辅助生产成本明细账”表头设置。步骤

3输入当月辅助生产费用的业务信息。步骤4自动调整列宽。步骤5填充表头颜色。步骤6绘制边框。步骤7完成供水车间辅助生产成本明细账”表头设置。一、编制辅助生产成本明细账步骤8利用单元格的引用，完成辅助生产成本明细账中明细数据的填制。步骤9调整表格的列宽。二、编制辅助生产成本分配表步骤

1设置“辅助生产成本分配表”的表头信息。步骤

2输入文字单元格信息。步骤

3填充表头颜色。步骤4绘制边框。步骤5引入辅助生产费用基础数据。步骤6计算分配率，据以计算相关费用金额。步骤7调整表格列宽。01PARTSIX制造费用的分配一、编制制造费用明细账1）制造费用是按车间归集的，未专设成本项目的生产费用，平时在制造费用账户进行归集，到了期末则要转入各种产品的生产成本中制造费用成本项目。如果生产车间只生产一种产品，制造费用直接全部转入该产品生产成本即可。但如果生产车间同时生产多种产品，则制造费用就要采用合理的分配方法，分配计入各种产品的生产成本。2）就北方机械公司而言，该企业有2种产品。因此，首先应按基本生产车间编制制造费用明细账，对制造费用进行归集；然后根据制造费用明细账归集的结果，按照各种产品生产工时的标准编制制造费用分配表，将制造费用在各种产品之间进行分配。一、编制制造费用明细账步骤

1设置“制造费用明细账”标题及表头。步骤

2输入制造费用业务信息。步骤

3自动调整列宽。步骤4填充表头颜色。步骤5绘