Excel电商数据分析与应用 课件 10.2.1库存绩效分析 10.2.2补货模型

产品运营分析10.2.1库存绩效分析&10.2.2补货模型1库存绩效分析2CONTENTS补货模型库存绩效分析PARTONE库存绩效分析库存分析是对企业的库存绩效进行分析的过程，包括库存预警和补货数量的分析，帮助企业提高仓库管理能力，提高库存绩效，降低不良库存。库存绩效分析主要是监控库存量、库存周转率、售罄率和动销率，是库存分析的基本项。存货周转速度越快，存货占用水平越低，流动性越强，存货转化为现金或应收账款的速度就越快，这样会增强企业的短期偿债能力及获利能力。1．库存量2．库存周转率库存量是指仓库中存放货物的数量，一般库存量要根据SKU（最小库存单位）进行统计，商品在正常销售的过程的库存量太大或为零都不是很好的现象。库存周转率是在某一时间段内库存货物周转的次数，是反映库存周转快慢程度的指标。周转率越大表明销售情况越好。在商品保质期及资金允许的条件下，可以适当增加其库存控制目标天数，以保证合理的库存。反之，则可以适当减少其库存控制目标天数。库存周转率计算公式为：库存绩效分析减少存货周转天数可以提高存货周转率，从实际运营来分析，需要减少存货在库时间。例10-8：某企业在2019年Q1的期初库存量为32149，期末库存量为23910，期间销售量为8374，请计算该企业在2019年Q1的库存周转天数和库存周转率。解：2019年Q1可以认为是90天，代入公式：售罄率=2.4万÷3万×100%=80%库存绩效分析3．售罄率售罄率是指一定时间段某种商品的销售占总进货量的比例，售罄率是衡量商品收回销售成本和费用的一个考核指标，便于确定商品销售到何种程度可以进行折扣销售清仓处理的一个合理的标准。计算公式为：售罄率=实际销售成本÷总进货成本×100%售罄率=实际累积销售数量÷总进货数量×100%或者售謦率计算期间通常为一周、一个月或一个季度。例10-9：某企业的某商品在2019年Q1的总进货量是3万，实际销售量是2.4万，计算售罄率。解：售罄率大于80%小于等于100%的为备货命中。库存绩效分析4．动销率动销率是反映阶段期间企业销售商品的占比，反映了企业的商品运营能力，是考验采销部门和运营部门的核心指标之一。商品动销率计算公式为：商品动销率有别于电商的网店动销率，电商的网店动销率只考虑在线销售的品种数，不考虑仓库的总品种数，往往仓库的总品种数要大于在线销售的品种数。电商的网店动销率为：例10-10：某企业在2019年1月的仓库总品种数为131个，其中有98个品种在期间产生了销量，计算动销率。解：补货模型PARTTWO补货模型哪个SKU需要补货？需要补多少货？这是运营或者采购经常遇到的场景。本小节介绍该场景的业务建模，提高采购的工作效率和补货数量命中率（实际售罄误差在15%内为命中）。1．补货业务逻辑补货要考虑现有库存量、未来可能产生的销量、安全库存量和供应链的补货周期，在买家下单时现有库存量能够支撑等待补货的时间。比如补货周期时14天，要提前预留超过14天的库存，在剩余14天库存时就要给工厂下单。补货模型2．补货数量测算例10-11：创建模型计算需要补货的商品和数量。数据采集自商家后台的订单报表和宝贝报表。解：在后台生成订单报表和宝贝报表，如图10-81所示。生成报表后，分别下载订单报表和宝贝报表，报表下载界面如图10-82所示。补货模型另外建立一张库存统计报表，记录现时库存量和补货周期如图10-83所示，库存统计表的“商家编码”对应宝贝报表的“商家编码”，补货周期是指补货所需的天数。补货模型如图10-84所示，打开Excel，在【PowerPivot】选项卡中单击【管理】选项，转到PowerPivot窗口，进入PowerPivot管理界面。如图10-85所示，在PowerPivot窗口的【主页】选项卡中单击【从其他源】选项，进入“表导入向导”对话框，选择【Excel文件】，然后单击【下一步】按钮。图10-84图10-85补货模型在图10-86所示的对话框中，先单击【浏览】按钮选择好文件路径，再勾选复选框【使用第一行作为列标题】，单击【下一步】按钮。注：文中已经把原来下载好的表格从CSV格式改为EXCEL文本格式。如图10-87所示，在宝贝报表中新建汇总的度量值，这个度量值要用于后面的运算。补货模型如图10-88所示，在PowerPivot的【主页】选项卡中单击【关系图视图】建模，将“订单报表”的“订单编号”字段连接到“宝贝报表”的“订单编号”，“宝贝报表”的“商家编码”连接到“库存统计表”的“商家编码”。

回到库存报表，键入公式“=SUMX(FILTER('宝贝报表','宝贝报表'[商家编码]='库存统计表'[商家编码]&&DATEDIFF(RELATED('订单报表'[订单付款时间]),Date(2022,1,30),day)<='库存统计表'[补货周期]),[购买数量的总和])”，求出的近N天销量是基于补货周期的天数计算的销量，结果如图10-89所示。图10-88图10-89SUMX函数说明如下。函数功能：返回为表中每一行计算的表达式之和。函数语法：SUMX（<表名>,<表达式>）DATEDIFF函数说明如下。函数功能：返回两个日期的单位间隔（可指定单位）。函数语法：DATEDIFF（起始时间，结束时间，时间单位）。RELATED函数说明如下。RELATED函数说明如下。函数功能：返回与当前表相关的列的值，应用在表间已经创建的关系，从关系表中查找相关数据。补货模型图10-90图10-91键入公式“=IF(CEILING(DIVIDE([库存],[近N天销量]),1)-1<0,0,CEILING(DIVIDE([库存],[近N天销量]),1)-1)*[补货周期]”，计算出现有的库存可以销售几天，在多少天后需要补货，结果如图10-90所示。键入公式“=IF([多少天后要补货]=0,[近N天销量]-[库存],0)”，计算出最小补货量，最小补货量是指可以灵活调配的补货量，可能并不需要完整的补货周期，结果如图10-91所示。CEILING函数说明如下。函数功能：将数字向上舍入到最接近的整数，或者基数的最接近倍数。函数语法：CEILING（数值，基数）。DIVIDE函数说明如下。函数功能：处理除数为零或者为空的情况。函数语法：DIVIDE（分子，分母）。补货模型图10-92图10-93键入公式“=IF('库存统计表'[多少天后要补货]=0,'库存报表'[最小补货量]+'库存统计'[近N天销量],0)”，计算库存一周期的备货量，至少保留一个备货周期的库存，结果如图10-92所示。如图10-93所示，在PowerPivot【主页】选项卡中单击【数据透视表】选项，在“创建数据透视表”对话框中勾选【新工作表】，单击【确定】按钮，在数据透视表上单击鼠标右键，在菜单中选择【显示字段列表】。补货模型图10-94图10-95如图10-94所示，将“库存统计表”中的字段设置在相应的行和列。如图10-95所示，一张完整的库存监