Excel数据分析案例一——业绩达成分析
- 业绩达成销售分析
- 数据的加工处理
- ①看板制作
- ②业绩额完成率表格
- KPI子弹图的绘制
- 累计销售额同比、员工销售情况及单月业绩变化图的绘制
- 累计销售额同比
- 单月业绩变化
- 员工销售情况
- 绘制数据汇总表
- 项目总结
业绩达成销售分析
销售明细原始数据中有以下字段
有销售目标表如下
以上两个表格可以提取以下5个指标
- 业绩完成率
业绩完成率=(本月销售额-本月目标)/本月目标 - 环比
(本月业绩-上月业绩)/上月业绩 - 同比
(本期业绩-去年同期业绩)/去年同期业绩 - 累计销售额
从当年一月一号至当前的销售额,简称YTD - 累计销售额同比
(今年累计销售额-去年累计销售额)/去年累计销售额
数据的加工处理
①看板制作
选中要绘制的单元格区域 - 对区域进行颜色填充为黑色 - 插入各种不同的颜色模板 - 加入文本框,最终效果如下
②业绩额完成率表格
- 新建一个sheet,命名为【绘图数据】,包含以下字段:
年月:2018年1月~2018年7月的数据
目标业绩:通过原始【销售目标】表格制作数据透视表获得,全选数据插入数据透视表,行为【月份】,值为【销售目标】即可求出每月的销售目标总和,然后将前7个月的数据复制到绘图数据表格中
销售业绩:通过【销售明细原始数据】表格制作数据透视表获得,行设置为【年月】并删除季度,值设置为【销售额】,将2018年7个月的数据复制到绘图数据表格中,点击【数字】中的小数点更改按钮进行四舍五入,只保留整数部分
去年同期销售业绩:2017年1~7月的销售业绩,同样可以通过上面的数据透视表进行复制
上月业绩:每个月份对应减去一个月,所以从2017年12月开始复制
今年业绩YTD:=SUM($C$2:C2),固定C2单元格以后进行求和
去年业绩YTD:同理,用去年同期销售业绩进行sum求和=SUM( $D$2:D2)
经过以上步骤以后得到表格如下图
同比:用今年销售业绩减去去年同期销售业绩除以去年同期销售业绩
=(C2-D2)/D2
环比:当月销售业绩减去上月业绩除以上月业绩,=(C2-E2)/E2
同比YTD:今年业绩YTD减去年业绩YTD除去年业绩YTD,=(F2-G2)/G2
美化百分比小数点以后得到下图的结果
KPI子弹图的绘制
子弹图用作查看目标业绩、销售业绩以及销售业绩所处等级,所以需要新表如下
注意:后面三个字段是累加的关系
选择任意单元格点击插入堆积柱形图,将【目标业绩】和【销售业绩】设置为次坐标轴,并将销售业绩改为带数据的折线图
选择折线图设置数据系列格式,选择标记将标记选项设置为内置,类型改为横线,然后美化去掉网格线图例等
适当调小左边坐标轴的边界和单位,显示单位改为【千】
右边坐标轴同理
还要添加最终业绩和超出百分比的文本框,在【绘图数据】中进行计算并插入,最终效果如下
累计销售额同比
选择年月和同比两列插入带数据标记的折线图,美化后结果如下
单月业绩变化
选择销售业绩、同比环比三列来制作,同比和环比需要设置次坐标轴到同一个坐标轴中,整体美化后结果如下
员工销售情况
根据销售额明晰原始数据做出的数据透视表如下,筛选2018全年在职的员工
插入柱状堆积图,美化后如下图所示
最后的整体效果如下图
效果如下图,长方形都是在PPT中手动绘制的,先把表格的数字复制过去再进行手动绘制
在Excel中可以通过添加条件格式进行绘制,效果如下
①首先对原始数据表格进行处理,分为以下五个维度
- 业绩完成率 - 可以看出员工是否完成本月目标
业绩完成率=(本月销售额-本月目标)/本月目标 - 环比 - 与上月业绩进行性环比
(本月业绩-上月业绩)/上月业绩 - 同比 - 与去年同期业绩比较
(本期业绩-去年同期业绩)/去年同期业绩 - 累计销售额 - 累计到个个月的销售额
从当年一月一号至当前的销售额,简称YTD - 累计销售额同比 - 累计销售额与去年同期累计销售额的对比
(今年累计销售额-去年累计销售额)/去年累计销售额
得到表格如下,可以用条件格式来对数据情况进行可视化处理 - ②从以下四个维度制作数据看板
业绩额完成率
主要用于员工KPI展示
业绩分为一般、良好、优秀三个等级,作为堆积柱状图的背景进行对比
较细的柱为实际完成的销售业绩
将目标业绩设置为带数据的折线图并将数据点设置为横线,可以直观看出实际完成销售业绩是否达标
加入本年度总业绩和与去年对比的增长百分比文本框
表格整体可以直观反映出各月业绩对比、每月是否完成业绩目标、每月的实际完成业绩处于哪个等级,及本年度销售总额+去年同期相比的增长率 - 累计销售额同比
主要用于整体销售情况把握,在时间不变的情况下累计销售额会有一定的浮动,可以进一步控制变量细化分析波动原因
采用带数据的折线图,可以直观看出累计销售额与去年同期相比是增加还是减少,以及具体减少/增加的百分比,可以看出1月的销售额与去年同期相比减少了29%,2月开始有个较大幅度的提升,说明2月的业绩补上了1月落下的业绩,在实际工作场景中可以分析2月业绩优秀的具体原因 - 单月业绩变化
柱状图可以直观看出每月销售业绩的变化趋势与数值对比情况
环比折线图反映了与上个月对比本月的销售额变化趋势
同比折线图反映了与去年同期对比本年度销售额是增加还是减少
可以以月为分析维度,同步分析业绩、同比与环比的关系及波动情况,若环比与去年持平则不需要考虑时间因素,若环比较去年有较大的波动则可以进一步分析增长或减少的原因 - 员工销售情况
也是用于对比员工的业绩情况,整体柱长代表该年度的累计销售情况,相同颜色的部分则代表相同月份的员工销售情况
首先看整体,排除第一位员工只在职了1个月,第五位员工的整体销售业绩加起来过低,价值少,可以考虑转岗优化
另一方面可以对比各个颜色条的长度与对比,如最后哦一个员工每个月的销售业绩都比其他员工要长,第三位员工黄色也就是第四个阅读的销售业绩明显比其他员工长,可以通过对比进一步分析每位员工每个月业绩优秀或一般的原因 - 数据汇总表
汇总了所有的销售数据,期中将销售业绩、同比环比及累计销售额同比进行了可视化处理,可以更直观地看到数据的变化情况