Excel数据分析案例一——业绩达成分析

Excel数据分析案例一——业绩达成分析

Excel数据分析案例一——业绩达成分析

• 业绩达成销售分析
• 数据的加工处理
• • ①看板制作
  • ②业绩额完成率表格
• KPI子d图的绘制
• 累计销售额同比、员工销售情况及单月业绩变化图的绘制
• • 累计销售额同比
  • 单月业绩变化
  • 员工销售情况
• 绘制数据汇总表
• 项目总结

业绩达成销售分析

销售明细原始数据中有以下字段

有销售目标表如下

以上两个表格可以提取以下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子d图的绘制

子d图用作查看目标业绩、销售业绩以及销售业绩所处等级，所以需要新表如下

注意：后面三个字段是累加的关系
选择任意单元格点击插入堆积柱形图，将【目标业绩】和【销售业绩】设置为次坐标轴，并将销售业绩改为带数据的折线图
选择折线图设置数据系列格式，选择标记将标记选项设置为内置，类型改为横线，然后美化去掉网格线图例等
适当调小左边坐标轴的边界和单位，显示单位改为【千】

右边坐标轴同理

还要添加最终业绩和超出百分比的文本框，在【绘图数据】中进行计算并插入，最终效果如下

累计销售额同比、员工销售情况及单月业绩变化图的绘制 累计销售额同比

选择年月和同比两列插入带数据标记的折线图，美化后结果如下

单月业绩变化

选择销售业绩、同比环比三列来制作，同比和环比需要设置次坐标轴到同一个坐标轴中，整体美化后结果如下

员工销售情况

根据销售额明晰原始数据做出的数据透视表如下，筛选2018全年在职的员工


插入柱状堆积图，美化后如下图所示

最后的整体效果如下图

绘制数据汇总表

效果如下图，长方形都是在PPT中手动绘制的，先把表格的数字复制过去再进行手动绘制

在Excel中可以通过添加条件格式进行绘制，效果如下

项目总结

①首先对原始数据表格进行处理，分为以下五个维度

• 业绩完成率 - 可以看出员工是否完成本月目标
  业绩完成率=(本月销售额-本月目标)/本月目标
• 环比 - 与上月业绩进行性环比
  (本月业绩-上月业绩)/上月业绩
• 同比 - 与去年同期业绩比较
  (本期业绩-去年同期业绩)/去年同期业绩
• 累计销售额 - 累计到个个月的销售额
  从当年一月一号至当前的销售额，简称YTD
• 累计销售额同比 - 累计销售额与去年同期累计销售额的对比
  (今年累计销售额-去年累计销售额)/去年累计销售额
  得到表格如下，可以用条件格式来对数据情况进行可视化处理
  
  ②从以下四个维度制作数据看板
  业绩额完成率
  主要用于员工KPI展示
  业绩分为一般、良好、优秀三个等级，作为堆积柱状图的背景进行对比
  较细的柱为实际完成的销售业绩
  将目标业绩设置为带数据的折线图并将数据点设置为横线，可以直观看出实际完成销售业绩是否达标
  加入本年度总业绩和与去年对比的增长百分比文本框
  表格整体可以直观反映出各月业绩对比、每月是否完成业绩目标、每月的实际完成业绩处于哪个等级，及本年度销售总额+去年同期相比的增长率
  
  累计销售额同比
  主要用于整体销售情况把握，在时间不变的情况下累计销售额会有一定的浮动，可以进一步控制变量细化分析波动原因
  采用带数据的折线图，可以直观看出累计销售额与去年同期相比是增加还是减少，以及具体减少/增加的百分比，可以看出1月的销售额与去年同期相比减少了29%，2月开始有个较大幅度的提升，说明2月的业绩补上了1月落下的业绩，在实际工作场景中可以分析2月业绩优秀的具体原因
  
  单月业绩变化
  柱状图可以直观看出每月销售业绩的变化趋势与数值对比情况
  环比折线图反映了与上个月对比本月的销售额变化趋势
  同比折线图反映了与去年同期对比本年度销售额是增加还是减少
  可以以月为分析维度，同步分析业绩、同比与环比的关系及波动情况，若环比与去年持平则不需要考虑时间因素，若环比较去年有较大的波动则可以进一步分析增长或减少的原因
  
  员工销售情况
  也是用于对比员工的业绩情况，整体柱长代表该年度的累计销售情况，相同颜色的部分则代表相同月份的员工销售情况
  首先看整体，排除第一位员工只在职了1个月，第五位员工的整体销售业绩加起来过低，价值少，可以考虑转岗优化
  另一方面可以对比各个颜色条的长度与对比，如最后哦一个员工每个月的销售业绩都比其他员工要长，第三位员工黄色也就是第四个阅读的销售业绩明显比其他员工长，可以通过对比进一步分析每位员工每个月业绩优秀或一般的原因
  
  数据汇总表
  汇总了所有的销售数据，期中将销售业绩、同比环比及累计销售额同比进行了可视化处理，可以更直观地看到数据的变化情况