【2022BI数据分析大赛】某线上商城最佳现金持有量分析
某线上商城最佳现金持有量分析
一、选手介绍
1.团队选手
团队名称：振翅高飞
团队组成：三人目前均就职于同一个电商公司的财务部，都有一定数据分析工作基础，都属于BI大数据分析领域的小白，积极认真学习，共同成长。
晨雪：队长，主要从事财务信息化相关工作，主导了选题和分析模型的构建。
周一：主要从事资金营运管理等工作，熟悉业务，为分析模型构建提供了不少思路。
啾啾就啾啾：主要从事资金稽核、账户余额监控等工作，基础数据整理等过程主力军。
2.参赛初衷
团队成员日常工作中或多或少涉及数据分析与展示汇报，一直以来主要使用EXCEL，遇到了电商数据量大、领导对呈报数据的实时性要求不断提高等诸多痛点，非常渴望能够掌握更好的数据分析与展示工具，提升工作效率和质量，完成一名普通财务人员的升级与蜕变。
在了解到公司引入了FineBI后，当即申请了账号。与此同时，看到帆软有这次挑战赛，想借此机会获得实际使用经验，加速学习FineBI的进度，非常希望能够获得帆软BI数据分析大赛对我们的认可，让大家有信心在大数据分析的路上走得更远。
二、作品介绍
1.业务背景
第三方电商平台店铺，需遵照平台规则运营。消费者付款后，资金会到第三方支付机构，例如淘宝店铺的款会到支付宝，等消费者确认收货后，资金才会到商家账户。如果消费者收货后退款，即使商家账户已全部提现，平台还可以从商家开店的押金中给消费者退款。整个资金流转过程，商家是相对被动的，几乎没有最佳现金持有量分析这种“甜蜜的烦恼”。
自建的线上商城，销售收款能直接到企业账户上。资金停留在聚合收款账户上，是没有利息的，按集团资金管理要求，需要尽快提现，用于运营或理财；但若全部提现，客户退款时账户没钱，会导致退款失败，招致投诉，影响销售；所以，线上商城收款账户需要保有一定量的资金。如何科学合理的确定每天应该保留多少钱在账户中？是电商公司财务部资金营运工作需要实际探究的问题，也是我们本次数据分析的目标。
2.分析思路
财务管理中进行企业最佳现金持有量决策的理论模型有三种：成本模型、存货模型、随机模型。实际业务与理论模型的很多假设条件不一致，三种理论模型均无法直接用于本课题，但是可以借鉴其理念，明确分析思路。
（1）基于随机模型的理念，我们需要根据历史数据和现实需要测算出一个现金持有量的控制范围，即下图中的最高控制线（H元）、回归线（R元）、最低控制线（L元）。如果账户余额在H和L之间波动，无需处理；如果账户余额低于最低控制线L，需要充值补充资金到R；如果账户余额高于最高控制线H，需要提现资金回到R。
理论随机模型中H、R、L计算公式，并不贴合本课题，需要基于实际业务重新定义。
（2）线上商城账户保留一定量现金的动机，是确保客户申请退款时，账户有钱进行退款，除此之外也没有别的用途。假设预计退款金额为A元。理论上，如果更在乎资金利息，能够接受一定程度的退款失败风险，下限L可以比A低；如果更在乎退款失败对客户的影响，或者对预计退款金额A不够自信，可以在A的基础上，再预留一定金额，进一步降低退款失败风险，下限L可以比A高。综合得：L=A加减风险偏好金额。
实际业务中，要测算企业的风险偏好数值，是一件非常困难的事情，且我司并无明显风险偏好，考虑到可操作性，本课题构建的模型中：L=A。
（3）理论模型中，回归线R的计算公式会考虑到证券与现金相互转换成本、现金机会成本等，本课题的业务场景下，如果账户余额低于L，让资金余额回归到预计退款金额A即可，即：R=A。
（4）如果账户余额高出L，多出的金额滞留无利息，存在机会成本B；提现需要手续费，存在交易成本C。基于存货模型理念，使机会成本与交易成本相等时所对应的现金持有量是最佳现金持有量，即当B=C时，H=L+B（或H=L+C）。
已知：
1）要估算资金滞留在线上商城收款账户中的机会成本，理论上应该取公司用于运营和理财的收益率，但实务业务中几乎无法测算。兼顾可操作性和合理性，我们选择了我司银行账户最高协定利率（年化1.7%）作为估算机会成本的依据。
2）资金无论在一天中的什么时间提现到指定银行账户，都是第二天开始计息。
3）提现手续费为千分之1，单次保底1元、最高3元，单次最高提现金额为500万。
在年化利率1.7%的条件下，21470.59元1天的利息为1元【1/（1.7%/365）】，64411.76元1天的利息为3元【3/（1.7%/365）】资金比下限L高于21470.59元，才值得用1元手续费进行提现，但此时的实际手续费为3元。资金比下限L高64411.76元，才值得用3元手续费进行提现。如果待提现金额大于500万，需要分多次提现，由于500万日利息为232.88元【500*1.7%/365】远大于3元，所以多次提现也是划算的。
综合得，如果当天账户余额比L高64411.76元以上，就需要进行提现，如果需要提现的金额大于500万，就分多次提现，既H=A+64411.76。
（4）至此，线上商城最佳现金持有量分析的核心，为测算预计退款金额A，基本公式：
预计退款金额A=收款金额D*预计退款率E
收款金额D通过统计订单收款金额得到，是一个实时的不断变动的金额。
预计退款率E可以基于历史退款情况进行测算，基本公式：E=基期退款金额/基期收款金额。直接基于基期所有订单测算一个预计退款率，过于粗糙，需要进一步分析我司商城不同产品、不同来源等维度的订单退款率有无明显差异，如果某维度的退款率有明显差异，需要按该维度分开计算订单收款金额和退款率，然后加总得到预计退款金额A。
3.基础数据的获取与整理
分析需要的数据，来源于线上商城和聚合收款钱包。整理基础数据过程的工作量，不亚于数据分析工作本身。
（1）受系统报表设计和财务账号权限影响，分析需要的字段要从很多个报表中获取，而且很多必要数据并非单独字段，需要导出备注域后自行提取；
（2）受系统的性能影响，每次每个报表导出的数据不能超过一定数量，目标期间的数据无法一次性导出，需要分期分批导出后，再合并。
（3）受业务多样性和系统报表功能影响，无法直接导出需要维度的数据。例如导出的订单明细表中不可避免的会包含招待赠送、货款抵扣等特殊订单，特殊订单占所有订单的比例约20%，一般只支持换货，不支持退货退款，或者完全不产生现金流，如果纳入分析，会导致整体退款率的严重低估。少部分特殊订单有字段可以直接识别，大部分特殊类型特殊订单需要通过与台账进行对账，才能识别出来。
通过导数、合并、对账、筛选等一系列的操作后，获得了订单明细表、收退款流水明细表、店铺类型表、产品类型表：
4.数据分析
使用整理出来的基础数据进行分析，需要对线上商城业务模式和系统特性有较为深入的了解，例如收货时间显示为1970/1/1的订单，并非一定代表消费者未收货，可能只是消费者未在商城系统中点击确认收货。
（1）不同维度退款率分析，模型推导完善
1）单价维度
随着产品单价的提升，退款率呈现微弱上升趋势，但产品单价越高，订单量越小，退款率代表性越差。综合来看，单价对退款率的影响并不显著，后续进行其他维度退款率分析，可以综合考虑订单数量和金额两个维度。
2）优惠维度
按订单有无优惠统计发现，从订单数量上来说有优惠的退款率更低，但是从金额上来说，有优惠的退款率更高，两者矛盾。又从优惠幅度、不同产品有无优惠等维度进行了进一步的分析，均呈现矛盾的状态。线上商城的优惠券退款后大多会退回，有效期内重新下单，可继续使用；此外，商城的主打产品JYKT属于耐消品，所以可能优惠不是商城消费者决策是否退款时的关键因素，优惠对退款率的影响不显著。
3）发货维度
退货退款存在运费成本，管控退货退款率是运营部的绩效指标之一。未发货订单按订单数量统计的退款率是已发货订单的25倍，按订单金额统计的退款率是已发货订单的35倍。
退款订单中未发货订单数量占比90.81%，大多数的退款都发生在未发货阶段。
发货后退款的订单数量分布如上图，在发货当天退款的订单数量对多。随着时间增加，退款订单数量逐渐减少。线上商城发货后，15天未确认收货，系统将自动确认收货；确认收货后，支持15天无理由退货，15天以上原则上只接受换货，退款需要走特殊申请。所以以15天为间隔，对发货后退款订单进行了进一步分析。
分析发现30天以上退款的特殊情况，占全部已发货订单的比例为：91/110527=0.0823%，概率极低，多为特殊偶然情况，未来重复出现的概率低。如果出现，客服需要在电子签审系统发起特殊退款申请，有财务审批节点，财务能够知道待特殊退款金额。普通订单退款在商城系统中也要走一定的流程，订单有一个状态是“待财务退款”，“待财务退款”状态的订单100%需要退款。模型细化：预计退款金额A=SUM（4组已发货30天内有效单收款金额*对应退款率）+待发货预计退款金额+待财务退款金额（普通）+待财务退款金额（特殊）
4）预约时长维度
线上商城支持最长预约2个月，即最长61天。不同预约时长订单数量如下图，由图可知，存在预约的订单，最多是能预约多长时间就预约多长（60天和61天），其次是预约一周内收货，在其次是预约一个月（30天）收款，其他时间预约的消费者很少。所以，预约时长维度可以分为6段进行退款率分析：未预约、预约1-7天，预约8-29天，预约30天，预约31-59天，顶格预约（60天和61天）。
分析预约时长维度退款率时，因为当前还有很多订单处于预约状态，如果基于全量订单统计，退款率会被低估，所以基于预约已经到期的订单进行统计。
分析发现不同预约发货时长的订单，退款率存在的巨大的差异。顶格预约退款率96%，能预约多长预约多长的消费者，可能从最开始就并非诚心购买，几乎到期都会退款。预计退款金额时，必须将预约时长维度纳入考量。由于30天及以上的退款率极高且可能在预约时间内的任意时间退款，因此还要考虑预约期内的退款概率。
顶格预约的订单96%的概率会退款，其在预约期内的分布如上图，0-3天和56-59天退款率较高。预约31-59天的订单80%的概率会退款，其在预约期内的分布如下图，0-3天退款率较高，4天以后退款率波动下降，都比较低。
在不同日期退款的概率并不相同，无法使用平均等方式计算。模型：预计退款金额A=SUM（4组已发货30天内有效单收款金额*对应退款率*当前所处预约阶段退款率）+SUM（6个预约时长分组待发货有效单收款金额*预约时长分组对应退款率*当前所处预约阶段退款率）+待财务退款金额（普通）+待财务退款金额（特殊）
顶格预约的订单，下单后不同时间退款占比如上表。当前所处预约阶段退款率取占比数值，另外五个分组同理。
5）产品维度
按产品大类维度统计发现，KT类产品以49.76%的订单量贡献了82.54%的销售额；KT类和XD类产品订单量占比加起来达84.22%，两者的退款率相对比较接近。其他大类的产品订单数量较小，退款率代表性不够强。
将其他产品大类整合后，整体退款率差异不大，即产品大类对退款率的影响不显著。
按产品品类维度统计发现，KT大类中只有JYKT一个品类，JYKT、HJDQ、CFXD三个品类的订单量占比加起来达83.93%，线上商城销售额高度依赖特定品类产品。其他品类的产品订单数量较小，退款率代表性不够强。将其他产品品类整合后，不同品类退款率差异较大，产品类型对退款率的影响较为显著，但不如预约时长维度差异大。
6）店铺维度
按底层店统计订单数量占比发现，线上商城98.5%的订单属于中国区总店，按订单金额统计退款率为21.63%。其他底层店订单数量都太小，退款率，不具备代表性。其他底层店在店铺类型中都属于O2O店，所以直接进入店铺类型维度分析。
按店铺类型统计发现，QYFX类型的店铺是销售额的主要来源，退款率与其他维度差异达到较大，店铺类型对退款率的影响较为显著，但不如预约时长维度差异大。
预测模型的应用，4月17日预计退款金额A的估算
A=已发货预计退款金额+待发货预计退款金额+待退款金额（普通）+待退款金额（特殊）
（注：由于需要统计的是预计退款金额，退款率统一选择了金额维度退款率。）
1）已发货预计退款金额=SUM（4组已发货30天内有效单收款金额*对应退款率*当前所处预约阶段退款率）
2）待发货预计退款金额=SUM（6个预约时长分组待发货有效单收款金额*对应退款率*当前所处预约阶段退款率）
3）待退款金额（普通）=“待财务退款”状态订单收款金额合计
4）待退款金额（特殊）=财务基于电子签审系统特殊退款申请金额手工填列
4月17日为星期日，申请特殊退款的订单已于4月15日退完，待特殊退款金额为0。
5)4月17日预计退款金额A=1180380.32
5.分析结论与策略建议
（1）具体结论与策略建议
2022年4月17日最低控制线L=A=1180380.32元
2022年4月17日最高控制线H=A+64411.76=1244792.08元
如果4月17日商城聚合收款账户余额小于1180380.32元，需要进行充值，将余额补充到1180380.32元。如果账户余额大于1244792.08元，需要进行提现，将余额提 现至1180380.32元。如果账户余额在1180380.32元和1244792.08元之间，无需进行任何操作。
（2）未来的应用
未来直接连接线上商城和聚合收款钱包系统，数据每天更新，仪表盘则将能够实时显示当天的最低控制线L和最高控制线H，据此进行当天资金运营决策：提现或充值或不操作。未来随着商城的发展，订单数量的增多，不用担心订单数量少退款率代表性不强，可以进一步将模型维度扩充细化。
6.可视化报告
三、参赛总结
1.FinBI工具
FinBI文档详细，通过学习基础教学视频即可入门，对业务人员非常友好，预期未来的工作中，FinBI一定会是我们离不开的工具。
2.参赛总结
三个BI大数据分析领域的小白一起从零开始学习使用FinBI，4个周末加20多个夜晚，最晚的一天我们做到了凌晨2点半，从来没有人说放弃，一起进步，一起成长。另外，特别感谢公司技术部负责FinBI管理的同事们，为我们解决了数据共享等诸多难题。
