炎陵娜妹子农业开发有限公司基于Excel进行进销存管理的方案设计

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

目录

1公司简介............................................................1

2Excel在企业进销存管理的介绍........................................1

3Excel基于进销存管理的设计..........................................2

3.1基本资料表模块设计............................................2

3.2采购业务表模块设计............................................4

3.2.1原材料月初数据表设计....................................4

3.2.1原材料采购业务表设计.....................................4

3.2.2应付账款汇总表设计.......................................5

3.3销售业务表模块设计............................................5

3.3.1产品销售业务表设计.......................................6

3.3.2应收账款汇总表...........................................6

3.4库存业务表模块设计............................................7

3.4.1领料清单设计............................................7

3.4.2完工产品入库表设计......................................8

3.4.3材料成本核算表设计......................................9

3.4.4库存材料盘点表设计.....................................10

4总结.......................................................10

参考资料............................................................12

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

炎陵娜妹子农业开发有限公司基于Excel进行进销存

管理的方案设计

1公司简介

炎陵娜妹子农业开发有限公司成立于2013年10月29日，企业地址为湖南

省株洲市炎陵县九龙经济开发区果园周路，其注册资本为200万人民币。企业

的业务包括：饮料制造，食品制造，现制现售饮用水，生鲜乳道路运输，餐饮

服务等。

2Excel在企业进销存管理的介绍

企业的进销存管理是指企业从采购货物，储存货物到销售货物的动态管理。

Excel有助于企业及时了解企业进货，存货，销货的情况，保证有合理的存货，

既不会因为存货不足导致无法供货，也不会因为存货过多引起库存空间被占用，

增加持有成本，存货被积压。同时可以促进销售。Excel在进销存管理中的应用，

可以提高企业的工作效率，并使得人力、物力等成本降低，实现成本效益化。

炎陵娜妹子农业开发有限公司属于中小型企业，目前并没有拥有完整的一

套属于自己的进销存管理系统。如图1所示是关于进销存管理的流程图，公司

根据销售订单去计划生产，合理规划采购的数量并从仓库领料进行生产加工，

完工产品入库之后，再通过仓库出库出去到达客户手中。对于进销存管理系统

的设计，首先建立一套Excel工作簿，其中包含为基本信息表模块，采购业务

表模块，销售业务表模块，库存业务表模块这四个模板。

1

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

图1进销存管理的流程图

3Excel基于进销存管理的设计

3.1基本资料表模块设计

基本信息表主要是介绍炎陵娜妹子农业开发有限公司的客户信息和产品信

息以及原材料基本信息。如图2和图3，4，5所示，进入到Excel中，新建一

个工作簿，将sheet1命名为“基本资料表”，在该工作表中建立一个“客户基

本资料”表和一个“产品基本资料”表以及一个“材料基本资料”表；并在“客

户基本资料”表中设置“客户名称”，“客户地址”，“客户电话号码”，在

“产品基本资料”表中设置“产品名称”，“单位”，“销售单价”；在“材

料基本资料”表中设置“材料名称”，“材料编码”，“单位”。这些资料由

信息部门负责从采购部门、销售部门中获取，并由信息部门经理负责审核。

2

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

图2客户基本资料表

图3产品基本资料

图4材料基本资料

3

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

图5工作表名称

3.2采购业务表模块设计

采购业务表模块包括原材料采购业务表和应付账款汇总表，该模块的设计

主要由采购部门负责，采购人员根据订货需求以及现有存货来对材料进行采购，

采购部经理负责对于采购订单进行确认和审核。而应付账款的管理由财务人员

进行负责并由财务部经理负责审核和确认。

3.2.1原材料月初数据表设计

原材料月初数据表可以一目了然地展现出该公司期初原材料的情况。该表

中包括材料编码，材料名称，单位，月初结存数量，月初单位成本，月初结存

金额。操作人员可以根据上一期期末的原材料结存情况以及库存材料盘点表进

行填写。

图6原材料月初数据表

3.2.1原材料采购业务表设计

原材料采购业务表是反映公司日常采购材料的情况。炎陵娜妹子农业开发

有限公司的水果原料主要是通过农村合作社或者是农民个人进行采购，用Excel

对进销存管理进行设计，为了更好地进行归纳处理没有具体到农民个人。如图7

所示，将工作表命名为“采购业务表”，在该工作表中建立一个“原材料采购

业务表”，包括业务日期，摘要，供应商，材料编码，材料名称，单位，数量，

单价，金额，已付货款，结算方式，未付货款。

原材料采购业务表中的E,F，H这三列的单元格不需要销售人员手动输入，

可以运用VLOOKUP函数自动得出相关信息，具体操作如下：当材料编码输入之

后，系统会自动匹配出材料名称和单位，

原材料采购业务表中的“金额”和“未付货款”可利用以下公式计算得出。

4

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

在I3单元格中输入公式“=G3*H3”,并把I3单元格向下填充，在L3单元格中

输入公式“=I3-J3”,并把L3单元格向下填充。

图7原材料采购业务表

3.2.2应付账款汇总表设计

应付账款汇总表中的“应付货款”和“已付货款”是应从原材料采购业务

表中汇总出来。，财务人员可以运用SUMIF函数,具体操作如下：将工作表命名

为“应付账款汇总表”，在B3单元格中输入公式“=SUMIF(采购业务表

$C$3:$C$7,A3,采购业务表$I$3:$I$7)”,并把B3单元格向下填充，在C3单元

格中输入公式“=SUMIF(采购业务表$C$3:$C$7,A3,采购业务表$J$3:$J$7）”，

并把C3单元格向下填充。“应付账款余额”可以采用公式计算得出，在D3单

元格中输入公式“=B3-C3”,并把D3单元格向下填充。当“供应商”由财务人

员手动填写之后，B（应付货款）,C（已付货款）,D（应付账款余额）三列的数

据会自动计算得出。

图8应付账款汇总表

3.3销售业务表模块设计

销售业务表模块包括两个部分，一个是产品销售业务表，一个是应收账款

汇总表，该模块的设计主要销售部门负责，销售人员负责销售合同以及销售订

单的签署，并由销售经理负责审核。若货品从仓库发出且已经确认销售收入之

后仍未收到货款，即形成应收账款，而应收账款的管理由财务部负责，并由财

5

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

务部经理负责审核和确认。

3.3.1产品销售业务表设计

产品销售业务表主要是反映公司在一定时期内日常销售产品的情况。如图9

所示，在工作簿中新建一个工作表命名为“产品销售业务表”，在该表中输入

“业务日期”，“摘要”，“客户”，“产品编码”，“产品名称”，“单位”，

“销售数量”，“销售单位”,“应收货款”，“实收货款”，“应收账款余

额”。

其中A（业务日期）,B(摘要）,C（客户）,D(商品编码）,G（销售数量）,J

（实收货款）这六列的信息由操作人员手动输入，按住键盘的快捷组合键

“Ctrl+A”将表格全部选中，在“开始”里面选择“数值”形式，其他的数据

可通过公式、VLOOKUP函数自动得来。具体操作如下:

单元格E3中输入公式“=VLOOKUP(D3,基本资料表!$F:$J,3,0)”,并把E3

单元格向下填充；

单元格F3中输入公式“=VLOOKUP(D3,基本资料表！$F:$J,4,0)”,或者复

制已经输入公式的E3单元格，粘贴到F3单元格中，将F3单元格公式

“=VLOOKUP(E3,客户产品基本资料表！$F:$J,4,0)”中的“E3”改为“D3”,

“4”改为“5”，并把F3单元格向下填充；

单元格H3中输入公式“=VLOOKUP(D3,基本资料表！$F:$J,5,0)”,并把H3

单元格向下填充；

单元格I3中输入公式“=G3*H3”，并把I3单元格向下填充；

单元格K3中输入公式“=I3-J3”,并把K3单元格向下填充。

图9产品销售业务表

3.3.2应收账款汇总表

为了在表格填写之后，方便财务人员可以更加清晰明了地找出还未全部结

清货款的客户，于是制作一张“应收账款汇总表”，在该表格中包括“客户”，

“应收货款”，“已收账款”，“应收账款余额”，其中A列的数据由财务人

员手动输入，B列和C列以及D列的数据通过公式、SUMIF函数自动得来，具体

6

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

操作如下:

单元格B3中输入公式“=SUMIF（产品销售业务表!$C$3:$C$7,A3,产品销售

业务表！$I$3:$I$7）”,并把B3单元格向下填充；

单元格C3中输入公式“=SUMIF（产品销售业务表!$C$3:$C$7,A3,产品销售

业务表！$J$3:$J$7）”,并把C3单元格向下填充；

D列数据的取得有两种方式，一种是在D3单元格中运用SUMIF函数输入公

式“=SUMIF（产品销售业务表!$C$3:$C$7,A3,产品销售业务表！$K$3:$K$7）”,

另一种是采用计算公式，单元格D3中输入公式“=B3-C3”并把C3单元格向下

填充。若应收账款余额出现了负数，则代表有预收账款的存在，提前收取到对

方的款项。

图10应收账款汇总表

3.4库存业务表模块设计

库存业务表模块包括领料清单设计和完工产品入库表设计，材料成本核算

表设计以及库存材料盘点表设计。其中领料清单由仓库人员进行管理；完工的

产品由生产人员送到仓库，并由仓库人员对产成品负责入库登记，仓库经理对

于产成品入库情况负责审核和确认；材料成本核算表由财务部人员负责核算，

由财务部经理负责审核和确认；库存材料盘点由财务部和仓储部门协同负责，

并由相关负责人进行审核和确认。

3.4.1领料清单设计

领料清单表是反映车间生产部门领用原料的情况。在完工产品入库表后面

新建一个工作表，使其命名为“领料清单”，该工作表包括了业务日期，摘要，

材料编码，材料名称，单位，领用数量。其中“材料名称”，“单位”可以通

过VLOOKUP函数自动得到数据具体操作如下：

单元格D3中输入公式“=VLOOKUP(C3,基本资料表！$L:$M,2,0)”，并把D3

单元格向下填充；

单元格E3输入公式“=VLOOKUP(C3,基本资料表！$L:$M,3,0)”，并把E3

7

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

单元格向下填充,

其他列的数据（业务日期，摘要，材料编码，领用数量）由财务人员手动

输入。领料清单输入的数据如图11所示。

图11领料清单

3.4.2完工产品入库表设计

完工产品入库表反映的是原材料经过加工，并完成制作之后入库的情况。

在应收账款汇总表之后新建一个工作表，将其命名为“完工产品入库表”，包

括“业务日期”，“摘要”，“产品编码”，“产品名称”，“单位”，“入

库数量”，“入库单价”，“入库金额”，其中“业务日期”，“摘要”，“产

品编码”，“入库数量”由操作人员手动输入，其他列的数据可以通过公式和

函数自动得来，具体操作如下：

单元格D3中输入公式“=VLOOKUP(C3,基本资料表！$F:$J,3,0)”,并把D3

单元格向下填充；

单元格E3中输入公式“=VLOOKUP(C3,基本资料表！$F:$J,4,0)”，并把E3

单元格向下填充；

单元格G3输入公式“=VLOOKUP(C3,基本资料表！$F:$J,5,0)”，并把G3

单元格向下填充；

单元格H3中输入公式“=F3*G3”,并把H3单元格向下填充。

完工产品入库表输入结果如图12所示。

8

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

图12完工产品入库表

3.4.3材料成本核算表设计

材料成本核算表是反映原材料订货价格的情况。由于炎陵娜妹子农业开发

有限公司采用月末加权平均法计算材料成本，所以在计算单位成本时，运用月

末加权平均法的公式计算材料成本：

月初结存材料的金额+本月入库材料的金额

月初结存材料的数量+本月入库材料的数量

在原材料出入库表中新建一个工作表，命名为“材料成本核算表”，该表

中包括材料编码，材料名称，月初结存数量，月初结存金额，本月入库数量，

本月增加的金额，发出数量，发出成本，库存数量，月末结存金额，材料单位

成本。具体操作如下：

当输入A列（材料编码）的信息,B（材料名称）列的信息会被系统自动匹

配出来。

C列（月初结存数量）和D列（月初结存金额）的信息操作人员可以手动录

入，也可以通过表间取数得来。单元格中C3输入“=原材料月初数据表！D3”,

在D3单元格输入=“原材料月初数据表!E3”，然后把C3单元格和D3单元格向

下填充；

E列（本月入库数量）和F列（本月增加的金额）以及G列（发出数量）可

以采用SUMIF函数自动得出相关数据。单元格E3中输入公式“=SUMIF(原材料

采购业务表!$E$3:$E$7,B3,原材料采购业务表!$G$3:$G$7)”，在F3单元格输

入公式“=SUMIF(原材料采购业务表!$E$3:$E$7,B3,原材料采购业务

表!$I$3:$I$7)”，单元格G3输入公式“=SUMIF(领料清单!$D$3:$D$10,B3,领

料清单!$F$3:$F$10)”，并把E3,F3,G3单元格向下填充；

发出成本=材料单位成本*发出数量，在H3单元格输入公式“=K3*G3”；

库存数量=（月初结存数量+本月入库数量）-发出数量，在I3单元格中输

入公式“=(C3+E3)-G3”；

9

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

月末结存金额=材料单位成本*库存数量，在J3单元格输入公式

“=K3*I3”;

材料单位成本根据月末加权平均法的公式，在K3单元格输入公式

“=(D3+F3)/(E3+C3)”。

图13材料成本核算表

3.4.4库存材料盘点表设计

库存材料盘点表可以反映原材料的账目和实物是否一致的情况。在材料成

本核算表后面新建一个工作表，使其命名为“库存材料盘点表”，该表中包括

材料编码，材料名称，单位，盘点数量以及备注。具体操作如下：

当操作人员输入A列（材料编码）的信息，系统会自动匹配出B列（材料

名称）和C列（单位）的相关信息。而D列（盘点数量）根据盘点结果输入数

据，E列（备注）是为账实不符时，填写理由，可以在E3单元格输入公式“=IF(D3=

材料成本核算表!I3,"","账实不符")”，若账实相符，则无需填写，若账实不

相符，就会出现“账实不符”的这一标志，并能够在其后面解释背后发生的原

因。

图14库存材料盘点表

4总结