数据分析与可视化实践- 课件 第5章 商品库存数据分析

第五章商品库存数据分析序言PREFACE

为盘活企业流动资金，加快资金周转，在保障供给的前提下，最大限度地降低压库资金，直接牵动着企业的经营效益。根据对我国众多制造业企业的库存管理情况所作的调查和参考有关资料，发现制造业企业在库存管理方面普遍存在“不能及时获得库存信息、库存信息不够准确、无法及时了解发料和生产用料情况”等。本章系统介绍怎样利用Excel对商品入库、库存、出库情况分析与可视化，以便提高协同工作效率，保护敏感资料，深层挖掘业务数据并运用视觉效果展示。PART01年度工作概述PART02工作完成情况PART03项目成果展示PART04工作不足之处PART05明年工作计划目录CONTENTS产品入库情况分析与可视化1PART01按时间入库数量分析例题例5-1表5-1展示了某企业仓库部分产品的入库数据，请对数据进行整理分类，按入库时间统计各产品入库数量，并用图表形式展现。入库月份01月01月01月01月02月02月02月02月02月02月产品规格2201A2202A2201A2202A2203A2203A2203A2203A2204A2205A入库数量8126185060691908460分析：对于制造企业来说，一件产品可能需要多个原料，同样，一种原料也可能被多个产品使用，这就意味着库存的数据可能是海量的，要从大量的数据里筛选出满足要求的数据，最方便的办法就是数据透视。表5-1：某企业仓库部分产品的入库数据按类别入库数量分析库存管理中为了更好的进行精细化管理，对产品的入库信息登记要尽可能的详细，特别对于一些特殊行业要求就更多，比如产品的生产厂家、出厂日期、批次编号、采购人员信息、采购部门信息等都要登记入表例5-2表5-2是某企业的部分入库信息表，请根据这些数据制作数据透视表，并分别按采购部门、业务员、供应商统计约入库数量及占比情况。表5-2某企业的部分入库信息表存货编码仓库入库日期入库单号入库类别部门业务员供应商制单人审核人数量单价金额GXGY023半成品库2021/1/10000000001材料入库采购部秦胜利上海照明公司张健举李美丽20041.888376.07GXGY024半成品库2021/1/10000000001材料入库采购部秦胜利上海照明公司张健举李美丽10023.932393.16QDJC022半成品库2021/2/200000000009材料入库采购部秦胜利天津气动有限公司张健举李美丽2018.8376.07qdjc023半成品库2021/2/200000000009材料入库采购部秦胜利天津气动有限公司张健举李美丽1330.77330.77QDJC028半成品库2021/2/200000000009材料入库采购部秦胜利天津气动有限公司张健举李美丽2012.82256.41SBCS001半成品库2021/2/160000000010材料入库市场部杜拉拉宁夏输送设备公司张健举李美丽620000120000分析：使用明细表多个维度分析数据，关键点是制作数据透视表选取合适的字段，统计数量对比和占比都是熟悉的操作了按类别入库数量分析按类别入库数量分析计算安全库存、最大库存和最小库存量相关知识（1）安全库存：为了防止不确定性因素（交货期突然延期、临时用量增加、交货误期、突发性设备故障等特殊原因）的发生，满足日常维修备件需求，保证设备的正常运行，生产的顺畅，而保持在采购周期内所有备品备件以及其他一般性消耗材等的物料需求计划外的适量库存量，属动态的库存，也称安全存储量。公式：安全库存=日均需求量*紧急采购周期。日均需求量，是指未来一段时间平均每天的需求数量。紧急采购周期，是订货提前期各环节加急处理或特殊处理的时间总和。（2）最大库存和最小库存：库存不足或库存过剩，均是在订购上、作业上，保管上的疏忽所导致的。为了预防这一现象，一般需先设定必要的库存标准，这个标准就是“最小库量”及“最大库存量”。要预防库存不足缺乏情况，库存量的最低标准就是“最小库存量（安全库存量）”。相对于此，为了不让库存过多，必要的库存标准就是“最大库存量（库存上限数量）”。用这两个标准监控库存状况是必要的。公式：最高库存量(成品)=最高日生产量×最短交付天数+安全系数/天最低库存量(成品)=最低日生产量×最长交付天数+安全系数/天最大库存量=平均日销售（需求）量×最高库存天数最低库存量=安全库存+采购提前期内的消耗量最低库存量=日销售量*到货天数+安全系数/天（3）安全库存不等同于最低库存。为了保证库存处于安全范围内，而又不致产生过多的积压成本，可以设定最低库存、安全库存与最高库存。最低库存可理解为针对确定性的因素和不确定性的因素设定的库存，安全库存可理解为只针对于不确定性的因素设定的库存，最高库存可理解为针对积压成本而设置的库存点。计算安全库存、最大库存和最小库存量例5-3表5-3是某仓库A物料的过去12周也即三个月的实际耗用和平均库存数据。经了解，A物料的采购提前期是10天，紧急采购周期为6天，保质期为30天。试计算该物料的安全库存、最小库存量、最大库存量。并使用可视化视图展示该周平均库存所处水平。表5-3

第1周第2周第3周第4周第5周第6周第7周第8周第9周第10周第11周第12周实际用量344913773932662326593340136285397633855736535355713350037241平均库存295645930437328839802862967386119289143212313161219585742969162分析：题目需要大量的公式计算，将计算结果使用可视化视图进行展示，将数据放在坐标轴中进行展示并区分区间，之前的例题中采用了辅助行+折线图这种方式，这里我们再试试用散点图，看能否达到同样的效果。库存数据分析与可视化2PART02库存类别占比分析例5-4表5-4展示了某企业三种产品全年各月份的平均库存数据，请根据表中数据分析月度各产品库存占比情况。表5-4某企业产品平均库存金额

一月二月三月四月五月六月七月八月九月十月十一月十二月服装59511192252298598605833051366614056371852866516716126758602配件32002938052814423674421040985545125081136551552644917472833鞋689369531117970170007142837167598173864164066126957117819160854191840分析：不同种类的产品库存金额和数量之间的对应关系并不一致，比如鞋类产品和服饰配件类产品，鞋类可能一个款式要正常销售就需要5-6个常用码数，数量和金额自然占比就大。库存周转率分析

库存周转率是指某时间段的出库总金额（总数量）与该时间段库存平均金额（或数量）的比。是指在一定期间（一年或半年）库存周转的速度。提高库存周转率对于加快资金周转，提高资金利用率和变现能力具有积极的作用。公式：库存周转率=（该期间的出库总金额（数量）/该期间的平均库存金额（数量））×100%库存周转率=（该期间出库总金额（数量）×2/期初库存金额（数量）+期末库存金额（数量））×100%库存周转率分析例5-5表5-是某企业仓库部分库存数据，请根据表中数据计算库存周转率，该企业库存周转率的达标标准为65%，请使用可视化视图（1）分析本年度库存周转率达标情况，（2）分析库存周转率和出库金额之间的相关性。表5-5月份1月2月3月4月5月6月7月8月9月10月11月12月期初库存金额461,685489,255538,022382,755403,306329,665447,030410,624359,119376,272401,921442,653期末库存金额489,255538,022382,755403,306329,665447,030410,624359,119376,272401,921442,653338,520月度出库金额443,760457,360711,972446,518802,661699,638678,624763,660670,174581,792812,923756,188分析：库存周转率是通过公式计算得出的数据，需要对表中数据进行加工，达成率可以通过组合折线图或者组合柱状图实现，库存周转率和出库金额的相关性分析，可以使用组合图来视觉观察，再使用散点图来验证。库存周转率分析出库数据分析与可视化3PART03出库数量分析例5-7表5-7列出了某企业4种产品2022年全年的出库数据，请根据这些数量分析四种产品出库量的环比增长率，并用图表展示。表5-7某企业产品出库数据

1月2月3月4月5月6月7月8月9月10月11月12月A10139059829939459199549429179599211104B150130147111129123134138123164142137C877783904810832638846846832940927860D612541626576565641597518589563534542分析：环比增长率=（（当前周期数量-前一周期数量）/前一周期数量）*100%，表格中并没有直接的数据，需要添加辅助行或者另外制作表格。出库类别数量分析例5-8表5-8列出了某企业2021-2022年三种类型商品的出库量，请根据表中数据分析三种类别商品的出货量的同比增长率和环比增长率。表82021-2022商品出库量数据2021年出库量2022年出库量月份服装鞋饰品

服装鞋饰品1月4517013,9331月1,1894913,5252月921243,9782月8801,2276,7263月1,6572796,0853月3,1247985,6634月2,4174625,3684月3,5511,1203,9985月9137161,9335月1,3704471,5466月2171504,1096月9154181,6977月9463,3026,3967月1,7289226,1058月9902,1706,4008月2,0712,1687,9709月7266406,5659月4,1452,0387,64810月1,4339484,38110月2,8014373,73111月7227263,97011月1,7572253,83912月5355815,60912月9841,6414,620分析：表格中并未给出增长率数据，需要对数据进行加工后进行展示。环比增长率=（（当前周期数量-前一周期数量）/前一周期数量）*100%，同比增长率=（（当前数量-去年同一周期数量）/去年同一周期数量）*100%出库类别趋势预测趋势预测分析法亦称时间序列预测分析法，是根据事业发展的连续性原理，应用数理统计方法将过去的历史资料按时间顺序排列，然后再运用一定的数字模型来预计、推测计划期产（销）量或产（销）额的一种预测方法。由于趋势测计划期间的销售数量或销售金额预测分析法所采取的数学方法不同，又可分为：（1）算术平均法。以过去若干时期的销售量（或销售金额）的算术平均数作为计划期的销售预测数。（2）移动加权平均法。是根据过去若干时间的销售量（或销售金额）按其距计划期的远近分