找回密码
 立即注册

QQ登录

只需一步,快速开始

扫一扫,极速登录

【2020冬季挑战赛】某药企销售年报(内含超详细数据清洗和数据分析过程)

HALLO
一.个人基本情况
1.选手简介
小.png
1.1.选手介绍
帆软社区用户名:caihuanglun
职业简介:在国外菲律宾佳达百货里担任信息部主管,主持了多个系统的实施和运维(进销存,OA,HR),也用BI产品构建了企业里多个不同业态的报表体系。
因为疫情原因和在数据分析这条路上遇到瓶颈,20年10月份回国,比起系统实施,更想要往数据分析师这条路走,希望能在国内好好发展这条路~

1.2.参赛初衷
  • 希望能多提升自己数据分析的能力
  • 原来都使用其他BI产品,回国后水土不服,大部分公司都用帆软,刚好能学习
  • 系统实施的经验比较丰富了,但是数据分析这块的经验不足,多点积累,找更适合的工作

2.场景介绍
2.1.业务背景介绍&数据来源
因为12月1号才参赛,手上又有其他事情比较赶,只能选着官方数据2来进行分析。时间真的来不及,很多想法还没实现,但是再过8分钟后就要截止提交了,只能先这样提交了哈哈哈,希望下次能早些参加,多点时间能实现所有数据分析的想法。

2.2.分析思路
拿到的数据进行表自身一致性和不同表间一致性的检查-----进行数据清洗-----分析表间隐藏的信息-----总体数据分析----明细维度分析

二.数据检查和数据清洗
1.数据检查和清洗的方向思考
官方数据提供了两个表:销售明细表,销售季度预算明细表。要去检查这两个表相同的数据(2019,2020 年上半年的销售)是否一致,也要检查表自己本身内部的数据是否有一致性。
(这是一个数据工作者很好的习惯,对于拿到手的数据永远保持怀疑,去检查其一致性)
截图202012090945596488.png
截图202012090945355897.png
截图202012090945112761.png
因为预算表为汇总表,所以是用销售明细表去分组汇总和其核对。

2.销售明细表进行数据清洗

2.1时间跨度检查
19 年是只有上半年的数据,20 年也是只有上半年的数据,所以该销售明细表的销售时间跨度与预算表是一致的。

1

1
        
2.2分组汇总检查
销售明细表和预算表一样的字段的有4个:含税销售额,不含税销售额,总成本,城建税
我们用仪表盘对销售明细表进行分组汇总,然后去和预算表做对比。
截图202012132038465370.png
22.2222.png
发现销售明细表的这4个数据全都是预算表的10000倍,两个表间的一致性有问题。
(1)从犯错的角度,预算表一般不会出错,因为这是总数据,且表里的实际销售和预算销售差不多。
(2)从现实的角度,预算表半年销售额是百万级别的合理;销售明细表是百亿级别,每单流水销售额在1亿级别不合理。
综上所述,认定销售明细表因为一些问题导致所有的金额数据都是正常的10000倍,要进行数据调整。

2.3数据清洗和清洗后检查
进入销售明细表:含税销售额,不含税销售额,总成本=(含税销售单价,不含税销售单价,单位生产成本高)*销售数量
2.png
这3个单价都处于合理的水平,反而销售数量过于大。
所以认定是销售数量是正常的10000倍,所以处理销售数量/10000
进行数据清洗,在销售明细表原表上,对字段销售数量/10000
截图202012132039096675.png
销售数量调整后,进行刷新检查,发现销售明细表和预算表保持完全一致,说明数据调整成功。

3.销售季度预算明细表数据清洗

3.1表内数据一致性检查
含税销售,销售收入,销售成本,税金及附加 这些是确定的,且和调整后的销售明细表核对是完全对应的,要对下列信息进行试算平衡。
毛利=销售收入-销售成本 ,营销线净利润=销售收入-销售成本-变动费用-固定费,各明细列加起来等于总计列,预算进度,同比增长等。
核对后确认表内的信息是一致的。

3.2数据清洗
预算表原来的格式是不能用的,在原表上进行删除和补充的调整
截图202012090957523312.png

3.3清洗后检查
数据清洗后导入BI,根据原本去汇总后与原表对比
3.3.png
检查汇总是完全一致的,明细数据也是一致的

三.分析并获得隐藏信息
我们得到的明确信息是有限的,要用官方给的这两个表格去分析并结合常识,得出一些关键性的信息来帮助我们进行后续的业务分析。(当然,现实情况不建议这样,直接去问业务部门会更快更准确,因为用数据推测出这些信息比较麻烦,且具有偶然性。)

1.原预算表,原来销售明细表的解读
1.1毛利=销售收入-销售成本
1.2营业线净利润=毛利-变动费用-固定费用
1.2.png
1.3不含税销售单价=含税销售单价/(1+发票增值税率)
1.3.png
1.4含税销售额=销售数量*含税销售单价
1.4.png
1.5销售收入=不含税销售额=销售数量*不含税销售单价
1.5.png
1.6销售成本=总成本=销售数量*单位生产成本
1.6.png
1.7发票增值税额=不含税销售额*发票增值税率
1.7.png       
1.8税金及附加=毛利*增值税*12%

2.产品信息的分析
导入BI,从相关的:产品归属业务部门-产品品类-产品名称-产品规格-销售单位-生成厂家这些维度去不断尝试分组汇总来得到信息。
222.png
223.png
得出关键信息:
2.1每个产品只属于一个产品归属业务部门-产品品类(重要!)
2.2每个产品只有一个产品规格,一个销售单位
2.3每个产品可能有不同生产厂家(重要!)
2.333.png
2.3333333.png

2.4产品增值在19年1,2,3月份是16%,之后就是13%,下降了。因此在销售单价没有变化的情况下,不含税销售单价提高了。(重要!)

3.客户和销售人员维度去分析 xx3.png
得到关键信息
3.1 每个客户只属于一个区域
3.2 每个销售人员只属于一个区域 京津:a,b,c   江浙沪:d,e  粤闽桂:f,g,i
3.3 除了客户 AA001 是由 b,c 2个销售人员负责(大客户?),其他所有客户几乎都是由1个销售单独负责。AH

4.销售人员和产品的维度去分析
xxx4.png
销售人员所卖的产品没有特定性

四.总体分析
总销售和利润
xxx四.png
1.含税销售额下降7.65%,而销售收入只下降了5.96% ,这是因为2019年3月份增值税从16%调到13%,所以没有往下分析的必要。
2.销售收入没完成预算,且同比下降5.95%,要从:2.1时间,2.2产品归属部门-产品品类-产品,2.3区域-客户,2.4销售人员这些维度去重点分析,看看是谁拖了后腿。 再从:2.5退货情况 等次要分析。
3.毛利同比下降4.72%,比销售收入下降5.96%幅度低,毛利=销售收入-销售成本,说明是销售成本有所降低,要去分析销售成本的降低。
4.净利润不仅完成预算159%,且同比增长32.96%,净利润=毛利-变动费用-固定费用,所以要从变动费用和固定费的降低去分析。
总成本和费用-总毛利和利润
总成本.png
5.销售收入降低5.96%,而销售成本下降6.96%,再看毛利率0.59%的略微提升,说明成本真的有略微降低,可以从:5.1日期,5.25厂家 去分析。
X5.png
6.变动费用-产品促销费用只用了预算的46.47%,且同比减少44%;固定费用-广告费只用了预算的36.67%,且同比减少了38.89%。这两个数据都属于直接影响销售的投入,且都明细的业务部门的数据。所以将两者放在一起分析,看是否有影响销售收入。
7.税金及附加=(不含税销售单价-单位生产成本)*销售数量*增值税率*12  =毛利*增值税率*12%  。所以其实完全取决于毛利和增值税,不用往下分析。
8.现金产品折扣只用了预算的57.18%,且同比下降了38.43%,大幅度的降低了,这也是会影响销售的,但是因为没有明细维度和明细数据,没得分析。
9.人力成本只用了预算的78.91%,且同比下降了10.5%,没有明细维度和明细数据 ,没得分析

五.明细分析

1.处理数据表(添加列等)
添加表-自助数据集-给销售明细表添加一些方便后面分析的列。如:销售年份,销售月份等等(一般能提前添加一些,但是主要还是会在实际分析的时候去发现需要,才回来添加)
5-1.png

2.各个维度的分析
2.1销售收入-时间分析
添加新指标:
5-2.11.png
5-2.12.png
同比增长金额-月份表
5-2.13.png
20年的2,3月同比超大幅度下降是导致20年销售收入不好的重要原因,主要原因为每单的销售数量大幅度的下降,导致每单的销售金额大幅度下降。
2.2产品归属部门-产品品类-产品
5-2.2.png
业务部门健康的20年收入完成预算任务,且同比增长11.83%,业绩的主要下降还是在非处方药,且3款产品都差不多。
产品促销+广告费对大健康类影响不大,而对非处方药的影响不好说,可能是有很大的影响,特别是产品促销费用这块下降的特别多。
建议:大健康的广告投入可以保持20年的这个低水平来提供利润率,而非处方药的产品促销+广告费要尽量去达到预算。
2.3 2.4 区域-客户分析-销售人员
2.32.4.png
因为一个客户大部分只有一个销售人员负责,所以一起分析。
b销售人员在A客户的销售金额同比上升269.56%,但其实只多提供了4千多的销售额,且b销售人员在B客户的同比销售下降了8.44%,所以b销售人员没有什么突出业绩。
同比上升的区域只有江浙沪,同比上升13.46%。主要依赖E客户和e销售人员,且提供的销售额非常高。
F客户和f销售员同比上升60.35%,提供的销售额也属于中等,潜力巨大
建议: E客户重点维护,e销售人员可以评为最佳业绩。  F客户潜力巨大,重点开发,  f销售人员可评为最佳上升。

2.5退货情况
定义:退货率=退货金额/发货金额
2.51.png 2.52.png
分析产品-厂家的退货率
2.53.png
子公司2 有比较严重的退货问题,
在感冒药1上,子公司2退货率高达2.62%,而子公司1销售量更大的情况下,退货率只有0.06%
在胃药1上,子公司2退货率高达0.26%,而子公司1销售量更大的情况下,退货率只有0.06%
建议:在子公司1产能足够的的情况下,将感冒药1的生产全部移交给,子公司1,并加强子公司2对药品的管控,药品的品质问题不容忽视。

以下的点是能具体再分析的,但是时间来不及了,哈哈哈
5.销售成本降低-日期,厂家,产品(成本同比有略微的降低,可以查明原因,然后看是否能可持续降低)
6.大单来源(每单的总价其实跨度很大,大单是重点)来自哪些区域,客户,产品
7.产品销量-毛利分析(哪些产品是优质产品,大大提高利润)

img_1_1.jpg


21天打卡训练营.jpg

官方数据2:某药企销售年报----报告人:蔡煌伦 (1).pdf

529.56 KB, 下载次数: 315

参与人数 +2 F豆 +100 贡献 +2000 F币 +2000 理由
兔子酱 + 2000 + 2000 恭喜荣获“最佳业务应用奖”
rushwei + 100

查看全部评分

发表于 2020-12-11 09:34:32
少有的实用落地方案!

0 使用道具 举报

发表于 2020-12-16 18:32:11
和热心网友讨论到给出落定方案的问题,让楼主有所感悟,所以跟大家分享下楼主认为我们作为数据分析师应该怎样给出落地方案的看法,希望能给大家一些思路,也希望大家多多指导,让我的想法更为成熟。

一.落地方案
楼主所认为的落地方案,就是能去真正执行的,不能含糊,要非常具有明确性;贴合企业实际状况,在企业目前所处的现状下是可行的。

二.在信息不够的时候给出落地方案
我们大家作为数据分析的工作者,如果是分析自己所在的企业,一般对所在企业的情况都比较了解。或者是作为乙方,在深入调研甲方项目后,也能将情况掌握到一定高度。 这时候结合数据和自身经验去分析,自然能给出落地方案。
但是难免要在不够了解信息下要做出一些方案,比如乙方的售前工作,这时的客户还不是签约客户,没办法花费那么多资源去调研,但是又要去抓住客户的心,需要给一些好的落地方案来彰显我方的水平。或者是比赛,比如这次的比赛,官方数据自然没办法给我们那么多额外信息。

这时候我们该怎么办?楼主认为有这3种办法:
方法1.给出模糊的落地方案(比较不推荐)
方法2.自己去猜测将信息补充上,然后给出落地方案(比较推荐,但非常关键的情况下不建议用)
方法3.根据每一种情况的可能性,给出每一种情况的落地方案(很累,哈哈哈)

三.实际案例
1.在本文2.5退货情况,我们数据有分析到:
子公司2 有比较严重的退货问题
在产品感冒药1上,子公司2退货率高达2.62%,而子公司1销售量更大的情况下,退货率只有0.06%.
在产品胃药1上,子公司2退货率高达0.26%,而子公司1销售量更大的情况下,退货率只有0.06%.
2.其实这时候难给出落地方案,因为缺少很多关键信息,想到的如下
2.1关于退货
退货是否是产品质量问题才产生的退货,还是客户就是想退就退,无关品质问题?
退货率2.62%对药品公司意味着什么?是否是严重问题?政府对药品质量是否有要求?客户在采购协议上是否有要求且会罚款等?
2.2成本问题
在产品感冒药1上,子公司2成本4.88,而子公司1成本4.64
在产品胃药1上,子公司2成本5.78,而子公司1成本5.78(这个是我来不及分析的)
子公司2的单位生产成本并没有比子公司1单位生产成本低。
该公司的单位生产成本是否真的考虑了所有的费用(管理费用,租金等等)?
子公司2有没有因为地方特色有特别的补贴?
2.3工厂现状问题
子公司1是否是已经达到生产极限?
子公司2是否是新建厂房,投入运作时间短?
子公司2相对于子公司1的规模如何,是否已经超过负荷在生产?

3.给出落地建议

方法1.给出模糊的方案
子公司2 确认有比较严重的退货问题,应继续深入调查情况,看是否有品质问题或者其他,想办法降低退货率。

方法2.自己去猜测将信息补充上,然后给出落地方案
(自己猜测部分: 退货是产品品质问题;药品关乎生死,药品因品质为退货率2.62%属于严重问题;子公司2在所有的成本上没有任何的优势;子公司1还没达到生产极限;子公司2非新建厂房,生产也没负载)
子公司2有严重品控问题,该情况会导致公司品牌严重受损,建议子公司2暂时减少生产,定单先转移到其他子公司进行生产,同时派遣子公司1的相关人员去协助子公司2去整顿生产品质,在子公司2的退货率达标后再慢慢恢复正常生产,恢复正常生产后,也要每月去跟踪退货率。同时将退货率作为子公司的关键指标,严抓。

方法3.根据每一种情况的可能性,给出每一种情况的落地方案(很累,哈哈哈)
如果1:退货不是品质问题,是客户可以随意退货,那要对客户的退货有一定的约束,不能太过分。
如果2:退货是品质问题,退货率2.62%是小问题,子公司1已达到生产极限,子公司2规模比较小,超负荷生产,  那属于负载过头,导致生产品质下降。 需要扩大子公司2的规模。
如果3,如果4.................

四.总结
方法1.给出模糊的落地方案。  显得非常淡薄,只是给出了重要的数据分析结果。 当然,新手的话主要就是在做这一步。
方法2.自己去猜测将信息补充上,然后给出落地方案。很推荐,但是真的需要丰富的经验和一定的敏感度,平时喜欢猜的人会更擅长一些
方法3.根据每一种情况的可能性,给出每一种情况的落地方案。真的太累了,这些只能是思考过程,不该全部给出,这样看得人会很不耐烦,认为没有重点。

编辑于 2020-12-16 18:37  
编辑于 2020-12-16 18:46  
参与人数 +1 F豆 +100 理由
传说哥 + 100 默默地点个赞,然后闪人

查看全部评分

5 使用道具 举报

发表于 2020-12-17 04:46:55
用EXCEL做清洗用帆软做分析。。。。。。。。。。。。。。。

2 使用道具 举报

发表于 2020-12-9 07:21:25 发布于APP客户端
点赞点赞

使用道具 举报

发表于 2020-12-9 15:03:49
现在BI支持表格内放柱状图了?  还是你装了插件?

1 使用道具 举报

发表于 2020-12-11 14:14:07
实用性的报告啊

使用道具 举报

发表于 2020-12-14 10:03:39
实用,专业。

使用道具 举报

发表于 2020-12-14 17:00:44
很细致,思路清晰。我也是用的这个数据集,相比之下就很粗了

1 使用道具 举报

发表于 2020-12-15 15:31:09
大佬!太赞了

1 使用道具 举报

发表于 2020-12-16 11:39:05
666

使用道具 举报

发表于 2020-12-16 16:31:39
66666666666

使用道具 举报

发表于 2020-12-17 10:49:40
这个思路太清晰了,排版也很棒,一目了然,巴适滴板

1 使用道具 举报

发表于 2020-12-17 11:20:05

1 使用道具 举报

发表于 2020-12-21 09:47:16
数据处理那一章3.3节中导入BI的是3.2中的右表? 编辑于 2020-12-21 11:33  

使用道具 举报

发表于 2020-12-21 14:27:20
很棒的文章,过程思路复杂但又清晰,结果多样且又内涵丰富分析信息,学习到了!!

使用道具 举报

发表于 2020-12-21 14:36:17
厉害 思路清晰、实用       收藏学习哈

使用道具 举报

发表于 2020-12-25 10:48:54
有没有原始的excel数据文件和 最终frm文件?

1 使用道具 举报

发表于 2021-2-24 14:52:43
PDF文件的图片看着有些模糊呢,这是为什么呢

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册  

本版积分规则

联系社区管理员|联系帆软|《帆软社区协议》|手机版|帆软社区|Copyright © 帆软软件有限公司 ( 苏ICP备18065767号-7 )

GMT+8, 2021-6-22 14:57 , Processed in 0.188676 second(s), 88 queries , Gzip On.

返回顶部 返回列表