贵州荣盛商贸有限公司基于EXCEL进行进销存管理的方案设计

湖南商务职业技术学院毕业设计

目录

1贵州荣盛商贸有限公司简介...................................................................................1

2贵州荣盛商贸有限公司EXCEL进行进销存管理的动机....................................1

3贵州荣盛商贸有限公司进销存管理流程设计.......................................................2

3.1采购业务设计.................................................................................................2

3.1.1建立供应商信息表...............................................................................2

3.1.2建立采购业务表...................................................................................3

3.1.3建立预付货款汇总表...........................................................................5

3.2销售业务设计.................................................................................................5

3.2.1建立客户信息表...................................................................................5

3.2.2建立销售业务表...................................................................................6

3.2.3建立应收账汇总表...............................................................................8

3.3库存管理设计.................................................................................................9

3.3.1建立商品调出调入表...........................................................................9

3.4商品成本计算表设计...................................................................................10

3.4.1建立商品成本计算表.........................................................................10

4总结..........................................................................................................................12

参考资料.....................................................................................................................14

湖南商务职业技术学院毕业设计

贵州荣盛商贸有限公司基于EXCEL进行进销存管理

的方案设计

1贵州荣盛商贸有限公司简介

贵州荣盛商贸有限公司成立于2017年04月13日，注册地位于贵州省铜仁

市万山区义乌城市场A4栋1层25，注册资本为1000万元，属于商品贸易类企

业，公司拥有员工较少，法定代表人为刘兵荣持股比例90%，财务负责人为韦俊，

贵州荣盛商贸有限公司属于四川省古蔺郎酒厂有限公司贵州铜仁地区总代理商，

其代理产品主要为青花郎、红花郎、郎牌特曲、小郎酒，同时也是贵州茅台茅台

酒股份有限公司的茅台迎宾酒（紫款）经销商，主要销售产品有茅台迎宾酒、茅

台王子酒，贵州大曲，仁酒等，公司负责对外销售与推广其旗下产品，属于商品

贸易公司，经营范围包括包装食品、散装食品、烟、酒、日用品、洗护用品、工

艺品、化妆品、等商品的零售与批发，贵州荣盛商贸有限公司在贵州省拥有三家

店面，因当地白酒市场较饱和，竞争非常激烈，公司需不断扩大销路，帮助供应

商不断提高市场占有率，提高利润，同时也要做好公司的进销存管理，资金才能

及时回笼，才能做到持续经营，创造未来。

2贵州荣盛商贸公司采用EXCEL进行进销存管理的动机

截至2021年年底，我国小微企业达到了4800万户，较十年前增长了2.7倍，

随着中小企业行业竞争越来越激烈，且受到疫情影响，从3.5年的平均寿命下滑

到2.6年，且还在持续下降，行业的竞争加剧了商业竞争，在此期间贵州荣盛商

贸公司也深受打击，商品销售困难，销售货款无法收回，库存积压，且货物堆积

无法盘点，导致资金得不到及时的回笼，支付供应商商品款显得十分困难，企业

当前的进销存管理方式没有给企业带来良好的效果，现如今，对于刚刚起步不久

的贵州荣盛商贸有限公司来说购买进销存管理软件无疑给公司造成了资金浪费，

增加了公司的资金压力，但在信息化的时代，企业不仅要依赖于信息化的支持，

更要做到对采购到销售这企业经营过程实施全方位，高标准的实时监控，才能使

企业更好的发展。

企业进销存管理是指企业管理过程中由采购到入库再到销售的动态管理过

程，对企业经营中从采购到销售进行全程跟踪管理，其中涉及到商品采购，销售，

回收货款，支付商品款等业务都可以通过excel技术准确详尽的记录下来，对比

购买进销存软件，节省了人力，物力，财力。

1

湖南商务职业技术学院毕业设计

3贵州荣盛商贸有限公司进销存管理流程设计

系统分成几个相对独立的模块，整个系统采用模块化结构设计。作为应用序

有较强的可操作性和扩展性。合理的数据流设计，在应用系统设计中，相对独立

的模块间以数据流相连接，使各模块间的耦合性较低，方便系统运行，提高系统

安全性。

3.1采购业务设计

3.1.1.建立供应商信息表

第一步:打开EXCEL，点击新建工作簿，将工作簿命名为“贵州荣盛商贸公

司进销存管理系统”将sheet1重命名为“供应商信息表”，设置“供货商名称”，

“供货商电话”，“商品编码”，“商品单位”，“采购结算方式”等字段，如图3-1

所示。

图3-1创建供应商信息表步骤1

第二步:单元格命名，点击A列，单击“公式”菜单栏下的“名称管理器”

按钮，点击“定义名称”，在弹出的对话框名称栏输入“供应商名称”，点击确认，

完成名称定义。运用同样的方法，将B列命名为“供应商电话”将C,D,E,列命

名为“商品编码”“商品名称”，“单位”，将G列命名为“采购结算方式”，如图3-2

所示。

图3-2创建供应商信息表步骤2

2

湖南商务职业技术学院毕业设计

3.1.2建立采购业务表

采购业务表主要用来记录企业从供应商采购货物的时间，接下来我们在表格

内设置“商品编码”，“商品名称”，“单位”，“采购数量”，“采购单价”，以及“应

付货款”，“采购结算方式”，“未付货款”等字段。

第一步:新增一个工作表，将该工作表命名为“商品采购业务表”，通过“公

式”菜单栏下“名称管理器”功能将单元格命名，方便后期数据制作。如图3-3

所示。

图3-3建立采购业务表步骤1

第二步:设置数据有效性，为了使采购业务表与供应商信息表数据关联，并

且减少工作过程中由于人为输入造成信息的错误，遗漏等问题，将“供应商名称”

“商品编码”“采购结算方式”设置数据有效性。单击“数据”菜单栏下“数据

验证”按钮，如图3-4所示，弹框中来源这一栏中的“供应商名称”来源于“供

应商信息表”中的A列，改为同样的方法设置商品编码和采购计算方式，如图3-4

所示。

图3-4建立采购业务表步骤二

第三步:设置公式，完成日期、摘要、商品名称等字段的填写后，引用VLOOKUP

函数业务日期，摘要，供应商名称，商品编码，商品名称，采购数量，采购单价，

已付款，结算方式等信息需要手动录入，其他项目需要设置公式，更方便计算。

3

湖南商务职业技术学院毕业设计

在D2单元格内输入函数:=VLOOKUP(C2,供应商信息表!“商品编码”，“商品

名称”，“单位”，2，0)。

在E2单元格内输入函数:=VLOOKUP(C2,供应商信息表!“商品编码”，“商品

名称”，“单位”，3，0),然后点击单元格将鼠标拖到单元格右下角待鼠标变成十

字架状向下拖动即可根据录入的商品编码自动生成商品名称和单位。

金额列:在I2单元格内输入公式:=G2*F2,向下拖动则自动计算该商品本次采

购金额。

未付款项列:在L2单元格中输入公式:=I2-J2,向下拖动则自动计算本次采购

未付货款数额。整表如图3-5所示。

图3-5建立采购业务表步骤3

3.1.3建立预付货款汇总表

因公司性质，根据供应商规定，代理商采用先付款后提货的方式采购商品，

所以我们需要先设置预付货款汇总表。

第一步:新建工作表，将该工作表命名为“预付货款汇总表”，设置“供应商

名称”，“供应商电话“，“应付货款”，“预付货款”，“应付账款余额”，等字段分

别填列在A1,B1,C1,D1，E1,单元格中，如图3-6所示。

图3-6建立预付货款汇总表步骤一

第二步:设置数据有效性，对供应商名称项目设置数据有效性，来源选择“供

应商信息表”工作表中的A列。

第三步:设置公式，引用SUMIF函数

4

湖南商务职业技术学院毕业设计

在B2单元格内输入函数:=SUMIF(商品采购业务表!$H$2:$H$22,A2,商品采购

业务表!$I$2:$I$22),向下拖动填充，可以得到应付给该供应商的货款。

在C2单元格内输入函数:=SUMIF(商品采购业务表!$H$2:$H$22,A2,商品采购

业务表!$J$2:$J$22),向下填充。

在D2单元格中输入公式:=B2-C2。

整表如图3-7所示。

图3-7建立预付货款汇总表步骤2

3.2销售业务设计

3.2.1建立客户信息表

第一步:在“贵州荣盛商贸有限公司进销存管理”工作簿内新建一张工作表，

重命名为“客户信息表”，和“供应商信息表”一样，填写好客户名称、客户电

话、商品编码、商品名称、单位、销售结算方式，如图3-8所示。

图3-8建立客户信息表步骤一

第二步:单元格命名，选择A列，单击“公式”菜单栏下“名称管理器”按

钮，点击“定义名称”，在弹出的对话框中输入“客户名称”点击确认，完成名

5

湖南商务职业技术学院毕业设计

称定义。运用同样的方法，将B列命名为“客户电话”，C,D,E命名为“商品信

息”，G列命名为“商品结算方式”，如图3-9所示。

图3-9建立客户信息表步骤2

3.2.2建立销售业务表

销售业务表主要用于记录企业零售，批发商品等经济业务，包括销售时间，

客户名称，商品编码，商品名称，单位，销售数量，销售单价，应收货款，实收

货款，销售结算方式，应收账款余额。

第一步:新建一个表格，命名为“商品销售业务表”，填写好项目名称，如图

3-10所示。

图3-10建立销售业务表步骤一

第二步:设置数据有效性，为了使销售业务表与客户信息表数据关联，

并且减少工作过程中由于人为输入造成信息的错误，遗漏等问题，将“客户

名称”，“商品编码”，“销售结算方式”设置数据有效性，单击“数据”菜单栏下

“数据有效性”按钮，如3-11所示，弹框中来源这一栏中的“客户名称”来源

于“客户信息表”中的A列，某些客户属于零散客户，需手动输入，设置“商品

编码”的数据有效性时，来源选择“客户信息表”工作表中C列，设置“销售结

算方式”的数据有效性时，来源选择“客户信息表”中的G列，如图3-11所示。

6

湖南商务职业技术学院毕业设计

图3-11建立销售业务表一步骤二

第三步:设置公式，引用VLOOKUP函数

业务日期，摘要，客户名称，商品编码，商品名称，销售数量，销售单价，

实收货款，销售结算方式等信息需要手动录入，其他项目需要设置公式，更方便

计算。

在D2单元格内输入函数:=VLOOKUP(C2,客户信息表!“商品编码”，“商品名

称”，“单位”，2，0)

在E2单元格内输入函数:)=VLOOKUP(C2,客户信息表!“商品编码”，“商品名

称”，“单位”，3，0)然后点击单元格将鼠标拖到单元格右下角待鼠标变成十字架

状向下拖动即可根据录入的商品编码自动生成商品名称和单位。

应收货款列:在I2单元格内输入公式:=G2*F2,向下拖动则自动计算该商品本

次销售应收金额。

应收账款余额列:在L2单元格中输入公式:=I2-J2,向下拖动则自动计算本次

销售应收货款余数额。整表如图3-12所示。

图3-12建立销售业务表步骤3

3.2.3建立应收账款汇总表

公司属于总代理性质，有些批发给商户的商品形成了应收账款，有些商户可

能拖欠时间过长，需要建立一个应收账款汇总表以便核算和催收货款。

7

湖南商务职业技术学院毕业设计

第一步:在工作簿下新建一个工作表，命名为“应收账款汇总表”，并在该表

中输入客户名称，应收货款，已收货款，应收账款余额项目，如图3-13所示。

图3-13建立应收款账款汇总表步骤1

第二步:对客户名称项目设置数据有效性。

第三步:设置公式，引用SUMIF函数

在B2单元格内输入函数:=SUMIF(商品销售业务表!$H$2:$H$16,A2,商品销售

业务表!$I$2:$I$16),向下拖动填充，可以得到客户欠公司的货款。在C2单元格

内输入函数:=SUMIF(商品销售业务表!$C$2:$C$16,A2,商品采购业务表!$J$2:$J$

16),向下填充。在D2单元格中输入公式:=B2-C2，整表如图3-13所示。

图3-14建立应收账款汇总表步骤2

3.3库存管理设计

3.3.1建立商品调出调入表

商品调出调入表只要用于平时商品出入库的记录，出库主要包含采购，因商

品短缺从公司其他店铺调入商品，出库只主要包括商品出售，因公司其他店铺商

品短缺调出商品。

第一步:打开“贵州荣盛商贸有限公司”工作簿，新建一个工作表，命名为“商

品调出调入表”在表中输入业务日期，摘要，类型，商品名称，数量，摘要字段

可输入上年结转、采购、销售、调入、调出，类型字段可输入出库、入库，采购

的数据可以从“商品采购业务表”中取得，销售的数据可以从“商品销售业务表”

中取得，如图3-15所示。

8

湖南商务职业技术学院毕业设计

图3-15建立商品调出调入表步骤1

第二步:插入数据透视表，将光标放在任意一个单元格，点击插入——>数据

透视透视表——>点击放在现有工作表中，点击确认，将商品名称拖到行标签，

类型放在列标签，数量字段放在值，我们点击设计——总计——仅对行启用，再

将光标放在“出库”字段上，点击数据透视表分析——字段、项目和集——计算

项——名称改成“库存”——公式:=入库-出库——点击“确认”，当“商品调出

调入表”发生变动时，点击数据透视表分析菜单栏下“刷新”按钮，即可对数据

透视表的数据进行更新，得到最新库存数。如图3-16所示。

9

湖南商务职业技术学院毕业设计

图3-16建立商品调出调入表步骤二

3.4商品成本计算表设计

3.4.1建立商品成本计算表

贵州荣盛商贸有限公司存货的发出计价方法采用的是月末一次加权平均法，

所以我们成本计算表也采用月末一次加权平均法计算。

第一步:建立成本计算表。

打开“贵州荣盛商贸有限公司进销存管理”工作簿，新建一个工作表，命名

为“商品成本计算表”，其中包含的项目有:商品编码、商品名称、单位、期初结

存数量、期初结存金额、采购数量、本期增加金额、销售数量、销售成本、期末

结存数量、期末结存金额、单位成本，表中A,B,C,D,E列需手动输入，如图3-17

所示。

图3-17建立成本计算表步骤1

第二步:设置公式

在F2单元格中输入公式:=SUMIFS(商品调出调入表!$E$2:$E$45,商品调出调

入表!$D$2:$D$45,B2,商品调出调入表!$B$2:$B$45,"采购")

10

湖南商务职业技术学院毕业设计

在G2单元格输入公式:=SUMIF(商品采购业务表!$D$2:$G$22,B2,商品采购业

务表!$I$2:$I$22)

在H2单元格输入公式=SUMIFS(商品调出调入表!$E$2:$E$45,商品调出调入

表!$D$2:$D$45,B2,商品调出调入表!$B$2:$B$45,"销售")。后面的商品均可以复

制第2行的公式得到。

第三步:计算成本

单位成本=（等于期初结存金额+本期增加金额）/（期初结存数量+采购数

量），在L2单元格输入公式:=（E2+G2)/(D2+F2)。

销售成本=单位成本*销售数量，在I2单元格输入公式:=L2*H2。

期末结存数量=期初结存数量+采购数量-销售数量，在J2单元格内输入公式:

=D2+F2-H2。

期末结存金额=期末结存数量*单位成本，在K2单元格内输入公式:=L2*J2。

结果如图