新编Excel在财务中的应用（第4版）课件 项目六-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