8月份，刘凯老师曾讲过一期《Excel内置BI工具在财务预算、计划、预测分析中的应用》，大家普遍反映很实用，希望老师能分享更多内容。管理会计大师课第25期，刘凯老师再次带来《Excel内置 BI工具在比率分析、占比分析、环比分析、同比分析中的应用》。昨天听过直播课的童鞋纷纷欢呼“非常实用”、“太有帮助了“。
本课内容安排
1、高级会计实务对预算分析/财务分析的要求
2、使用Excel内置BI工具，开展常用的可视化分析
1)使用数据透视表、计算字段值显示方式功能，开展比率分析，占比分析和环比分析
2)基于多表创建数据透视表，并用DAX函数生成的比率分析、占比分析和同比分析
3)常见分析模式对应的数据透视图等多种方式
课程收益三方面
一、使用Excel数据透视表/图开展比率分析、占比分析和环比分析
二、安装使用Excel内置的Power Pivot工具，编辑DAX函数，计算比率分析、占比分和同比分析
三、常见分析模式的可视化图表展现
(IMA中国教育指导委员会2015-2016年度认证讲师 刘凯)
第一部分：高级会计实务对预算分析/财务分析的要求
商务智能BI建模与分析成为未来十年财会人员必备的技能。
通过数据透视表/Power Pivot，所有分析都可以通过多维分析来实现。
2015高级会计师考试《高级会计实务》中要求掌握以下财务预算分析方法：
第二部分：如何开展比率分析、占比分析、环比分析、同比分析?
刘凯老师使用两种不同的工具，对几种数据分析过程展开了演示。本节课，老师介绍的是比较简单的分析模式，更复杂的分析模式在后续管理会计大师课中再请老师讲解。
工具一：传统的数据透视表
主要使用它计算字段，以及值显示方式的功能来实现一些简单分析。
工具二：Power Pivot格式化分析
利用Excel内置BI商务工具创建更复杂的度量值指标进行分析。
两种不同方法，结果是比较类似的，但使用商务智能BI工具可以编制非常复杂的公式，因为在多维分析过程中有的指标比较复杂，需要比较复杂的逻辑，Power Pivot本身给了我们很强的多维度分析的能力，如，计算进销存，传统透视表就无法实现。Power Pivot可以实时多维交互地计算余额、进销存、现金流等，非常方便。
两种分析工具不冲突，成互补关系。
例如，基于Power Pivot的数据模型，可以创建帕累拖分析，基于Power Pivot的数据模型，可以添加一条80%的参考线，同时可以使用值显示方式构造帕累拖分析的累计曲线，可以有一个排序的柱形图，这个场景中可以既使用Power Pivot数据模型的功能，又使用值显示方式的功能。
(以下每种分析的具体演示都是老师对照Excel表格操作的，具体演示细节此处略去N字，只整理主要演示内容、分析方法及思路、主要计算方法等内容，供学习参考。)
Demo1：比率分析
主要演示内容：
ü 使用数据透视表计算字段功能;
ü 创建利润率指标
ü 开展多维度交互分析
学员收获：
掌握Power Pivot的操作界面;
掌握计算列、计算字段的创建;
快速创建仪表盘。
分析方法与实质：
1、比率分析法，通常是以同一期报表中若干重要项目的相关数据相互比较，求出相对比率，用以分析和评价经营活动和经营状况的一种方法，是最基本的分析工具。
2、 比率分析法实质上是将影响经营状况的两个相关因素联系起来，通过计算比率，反映它
们之间的关系，借以评价企业经营状况的一种经营分析方法。
分析思路：
1、在数据集中并未包含利润、利润率字段的情况下，生成2015年各产品子类的利润和利润率报表。
2、按年份，对销售收入和费用开展多维细分和交互筛选。
数据集：
平的 销售表(Excel表格)
主要计算方法：
1、计算列。构造辅助列：利润=销售-费用
2、计算字段。分子/分母是最简单，又特别强大的计算方法。
Demo2：占比分析
ü 使用数据透视表计算字段功能
ü 使用“值显示方式”-列汇总的百分比
ü 使用DIVIDE函数
ü 多维度交互分析
主要收获：
掌握CALCULATE()、ALL()、DIVIDE ()函数的主要用法
快速创建交互式热力图
u 结构占比分析
分析方法与实质：
1、结构分析((Contribution Analysis)的实质，是计算各组成部分所占比重，进而分析某一总体现象的内部结构特征、总体的性质、总体内部结构依时间推移而表现出的变化规律性的统计方法。
2、结构占比分析的实质，从结构上来看，部分(某个数据项)占整体的比例，也称贡献度分析。
分析思路：
1、可以从整体的数据全集中，筛选出数据子集，并进行汇总来获得所需的“组成部分”。
2、通过排序，将饼图最大扇区出现在最合适的位置。
3、”热力图“和”迷你图“在分析时更加紧凑，显示出更多信息。
数据集：
带有销售额/利润额的Power Pivot数据集
主要计算方法：
1、除法：DIVIDE()函数;
2、条件汇总：CACULATE()函数; SUM函数
3、全集：ALL()函数
Demo3：环比分析
主要操作：
使用数据透视表
使用“值显示方式”-差异百分比功能
多维度交互分析
主要收获：
掌握趋势分析和短期增长分析的基本方法
使用创建交互式旋风图&组合图表
u 环比增长分析
分析方法与实质：
1、环比分析是以某期数据和上期的数据进行比较，计算趋势百分比，以观察每年增减变化情况。这里的“某期”可以自定义，但通常是指“月份”，也可以是旬、季度、半年等。
2、环比是针对所要分析的对象所得出的本期数据与上一期的数据相比较，看是否增减变动，两者之间的比较通常用百分比来表示，即环比增长率。
分析思路：
1、按照月份对日期进行分组透视。最终生成的月度环比分析报表，包含逐月的毛利润、利润环比增长率。
2、通过切片器，按国家或者按地区生成交互式旋风图开展分析。
数据集：
销售表、日期表
主要计算方法：
1、环比(变动基础项，上一个)差异：差异(减法)计算：差异=本期-上期
2、环比差异率：环比增长率：(本期-上期)/上期*100%，或者：(本期/上期-1)*100%。
Demo4：同比分析
主要操作：
使用数据透视表和DAX函数
使用SAMEPERIODLASTYEAR 函数或DATEADD 函数
对同比增长率(YoY)多维度交互分析
收获：
掌握季节性分析的基本方法
使用创建折线与柱形图组合图表
u 同比增长分析
分析方法与实质：
1、同比增长率分析，一定程度上消除了季节性因素影响，来获得比较真实的增长程度。例如，今年12月份收入与上一年度12月份收入相比较，就是同比计算，同比计算可以在一定程度上剔除季节性因素(圣诞节，双12大促)的影响。
2、同比，计算的是本期数据与上一年度同一时期的数据相比较。通过沿着时间轴向历史回溯1年，可以获得上年同期的时间段。
分析思路：
1、与A4-结构占比分析相似，但关键是计算上年同期值。然后将差异作为分子，将上年同期值作为分母。
2、除了用条形图以外，实际当期值(柱形图)+同比增长率(折线图)的复合图表，为经典常见的图表类型。
数据集：
销售事实表和日期表
主要计算方法：
1、除法计算：DIVIDE()函数 ;
2、条件汇总计算-CACULATE()函数; SUM()函数
3、 上年同期计算-SAMEPERIEODLASTYEAR()函数
重要建议：
建议安装使用Excel 2013专业高级版，功能比较成熟完善。2016版在编辑DAX函数时不能直接输入中文指标名称，不是非常完善。2010版还需安装插件。
不管是在企业外部的社交环节，还是企业内部的信息系统中，都需要借助数据的可视化来探讨、分析和解决问题。数据可视化分析迎来迅猛增长期，商务智能(BI)建模与分析已成为未来十年财会人员必备的技能。2015高级会计师考试《高级会计实务》也要求财务人士要掌握多种财务预算分析方法。这节课我们必须学，而且要学好。