Exce审计l培训 高级

1、,EXECEL 审计高级应用,及晓慧,天健会计师事务所（特殊普通合伙）,数据整理,清理不规则数据 调整表布局/结构 数据辅助列 降低/补充数据维度,Excel只能处理符合其规则的数据。同时，为何满足我们数据分析的维度，我们必须将信息记录转换为低维度的数据单元。,数据整理,如何将2017.1.1或20170101转换为2017-1-1,提示：Text函数、mid函数&组合、分列等,如何去除数字中的非数字,如何生成一个序时账的关键字段（进行vlookup）,Word与excel组合配符替换、智能填充,选择合适的序列进行组合，用&函数构造唯一值,清理不规则数据,数据整理,删除重复值,先汇总所有披露的

2、关联往来、关联交易等明细表格，筛选出所有单位名称 删除单位名称重复项， 利用删除重复项功能,案例 根据今年统计的关联方交易和往来发生额的单位汇总，重新统计披露的关联方清单,条件格式标记重复值/删除重复项/countif计数三种方法,6,加序列辅助列，用于排序后恢复原顺序 添加辅助列实现隔行插空白行 辅助列用于制作发生额借贷方向 添加辅助列标记用于筛选,数据整理,辅助列,数据整理,xx科目/明细/单位这种信息明细表，以/分割为多列,使用分列，选择分割符号为“”,增加数据维度,2016/12/23,8,二维表拆分一维表 （ALT+D+P 后双击行列交叉处的合计）,数据整理,降低数据维度,9,连接数

3、据-编辑链接,当将客户报表与我们的报表模板通过连接填好后，复制一份填好的模板，使用“数据”下的“编辑连接”，通过“更改源”批量完成客户其他子公司报表过录到模板的操作,数据整理,10,合并报表底稿中，链接了一家子公司的单体报表，如何使用复制和替换链接的形式实现其他子公司报表建立及更新链接？,数据整理,连接数据-复制替换,自动创建组,手动创建组,选取行/列（一般不选汇总、标题行列） 【数据】-【分级显示】-【创建组】-【创建组】,选取整个表格（或包含多个小计的行/列） 【数据】-【分级显示】-【创建组】-【自动建立分级显示】,快捷键 shift+alt+左右箭头,取消组合 选取相应区域，点击【数据

4、】-【分级显示】-【取消组合】,函数与公式,函数与公式,如何做一个折旧计算表 如何做一个账龄分析计算表 IRR与XIRR及摊余成本法 如何做单变量求解 使用PV计算辞退福利 使用NPV做减值计算,函数与公式,说明 一系列现金流的内部收益率。 规则：现金流可不同，但间隔必须相同。 注：内部收益率是针对包含付款（负值）和收入（正值）的定期投资收到的利率。所以，初始现金流都是负数表示，即算债券等筹资实际利率以贷款方角度讲融资方取得的借款看作自己的投资流出。,IRR(values, guess),Values必需。 数组或单元格的引用，这些单元格包含用来计算内部收益率的数字。 Values 必须包含至

5、少一个正值和一个负值，以计算返回的内部收益率。 IRR 使用值的顺序来说明现金流的顺序。 一定要按您需要的顺序输入支出值和收益值。,Guess可选。 对函数 IRR 计算结果的估计值。,IRR,函数与公式,IRR,函数与公式,说明 一组不一定定期发生的现金流的内部收益率。 规则：现金流可不同，间隔也可不同。,Values必需。 与 dates 中的支付时间相对应的一系列现金流。 首期支付是可选的，并与投资开始时的成本或支付有关。 如果第一个值是成本或支付，则它必须是负值。 所有后续支付都基于 365 天/年贴现。所以XIRR计算出来的是年内部收益率。,Guess可选。 对函数 IRR 计算结果

6、的估计值。,XIRR(values, dates, guess),Dates 必需。 与现金流支付相对应的支付日期表。 日期可按任何顺序排列。必须是日期格式,XIRR,函数与公式,XIRR,函数与公式,函数 XIRR 的计算公式如下：,其中： di = 第 i 个或最后一个支付日期。 d1 = 第 0 个支付日期。 Pi = 第 i 个或最后一个支付金额。,通过公式可以看到，折现率中使用了365天，也就是算出来的是年折现率。经测试，由于折现率使用的365天，所以XIRR为实际利率摊余成本后续计量时，利息支付周期应该是365天才适用,XIRR,函数与公式,单变量求解：,假设： 初始投资 50,0

7、00 年收益率 7% 投资年限 20年 计算可得终值 193,484.22 总收益是： 到期值-初始投资= 143,484.22 单变量求解： 要想20年后，5万元的总收益 是300%，年收益率应该是多少？,操作：【数据】-【模拟分析】-【单变量求解】,解释：单变量求解的前提是必须将各参数列在表格中，并用公式计算出来结果。单变量求解功能才能根据表格中的公式进行逆运算，“目标单元格”就是表格中公式计算的结果(B7) ，目标值是你对计算结果设定的新结果，根据新结果求解的参数就是可变单元格(B2),函数与公式,PV,用于根据固定利率计算贷款或投资的现值。 注：参数中，期数和利率必须对应。期数中每期是

8、半年，则利率就是半年利率；参数中pmt和FV至少有一个是必需。,PV(rate, nper, pmt, fv, type),利率,终值,年终还是年末,期数,年金,函数与公式,NPV,使用贴现率和一系列未来支出（负值）和收益（正值）来计算一项投资的净现值。,NPV(rate,value1,value2,.),利率,现金流,现金流,要求现金流在时间上必须具有相等间隔，并且都发生在期末。与PV一样，现金流间隔必须与利率计息期匹配，半年现金流则使用半年利率。在间隔不相同的情况下，建议使用XNPV函数。,函数与公式,在制作折旧计算表的时候，我们要考虑： 折旧计算的关键要素：原值、净残值、使用年限、折旧方

9、法 要计算累计折旧和当期折旧，就需要先计算月折旧额、累计折旧月份和当期折旧月份 考虑特殊情况：当期处置、当期转入在建工程、当期从在建工程转入 如何考虑折旧差异调整：建议测算当期折旧差异，只调当期。因为计算累计折旧差异会有很多因素考虑不到，比如历史上技改暂停折旧等。直接用期初累计折旧和当期应提折旧计算累计折旧 见模板文件夹中折旧计算模板,折旧计算表,函数与公式,账龄分析的计算原则：先发生，先收回 计算方法： 按每一笔实际发生日期计算 按月计算 按年计算 其中，第一种最准确 特殊考虑： 由于使用计算表，有减少发生额就算收回，比如应收账款的贷方发生额就算收回，但存在辅助核算合帐导致贷方发生额、借贷重

10、新录入一次等非真实收回的贷方发生额，都需要手工调整，一般可通过先分析该科目对方科目发现，比如对方科目不是银行存款而是应收账款 负数发生额可能代表收回或冲账，需要手工处理，可供过筛选发生额正负数或对方科目分析发现,账龄分析计算表,数据分析思维,对照 拆分 降维 假说,数据分析思维,第一大思维 对照,拿到数据后，如果数据是独立的，无法进行对比的话，就无法判断，等于无法从数据中读取有用的信息。,比如收入审计，看到2016年的总销量，最基本的分析就是对比，与去年或更多历史年份对比，发现差异和规律，,第一大思维 对照,数据分析思维,第一大思维 对照,对比维度有很多： 纵向 ：不同时期 横向 ：不同企业、

11、同一企业不同部门、不同地区 同比、环比；实际与目标； 分组分析：根据数据分析对象的特征，按照一定的标志，把数据分析对象划分为不同的部分和类型来进行研究，以展现其内在的联系和规律。如地区、产品等 结构分析：指被分析研究总体内部各部分与总体之间进行对比的分析方法，即总体内各部分占总体的比例，属于相对指标。如：成本结构比率变化、单耗等,数据分析思维,第二大思维 拆分,分析，就是拆分和解析。因此可见，拆分在数据分析中的重要性。很多事情经过拆分后清晰了。 当某个维度可以对比的时候，我们选择第一个思维【比照】。对比后发现问题需要找出原因的时候？或者不可对比。这个时候，【拆分】就登场了,我们可以进行不同维度

12、的拆分，拆分后还可以重新组合，从而做多维度的分析,数据分析思维,第二大思维 拆分,财务指标拆分，一般是杜邦体系，当业务结构会引起应收账款周转指标的变动时，还可将同一指标按业务结构拆分。 比如一家建筑施工企业有工程收入、销售商品收入、设计收入。 一般情况下应收工程款的周转期较长，设计收入的周转期较短，当业务结构发生变化时，比如工程收入规模占收入的百分比增加较高，则周转天数会降低。因此，分业务类别计算周转指标及可比期间指标才更有意义。,数据分析思维,第二大思维 拆分,数据分析思维,麦肯锡提倡按照不重不漏（MECE，Mutually Exclusive Collectively Exhausted

13、相对独立，完全穷尽）的原则 ，将问题层层拆解成子问题，从而找到问题的根源，并对症下药。,MECE对于问题的基本假设是基于简单的因果关系。,现实更像是网络的环状结构，而不是简单线性的因果结构。任何一个因素，在系统中都可能和其他因素产生互动关系。,一家企业曾经面临着成本上升、收入和利润严重下降等一系列问题。如何改变局面，公司期待着管理层在产品竞争力、降低成本、开发新品等方面做出努力。而实际管理层解决的突破口是：增加公司在安全生产上的投入。因为了解公司运作系统的管理层知道事故、停工、次品率导致了无效人工、浪费成本,第二大思维 拆分,数据分析思维,麦肯锡提倡按照不重不漏（MECE，Mutually E

14、xclusive Collectively Exhausted）的原则 ，将问题层层拆解成子问题，从而找到问题的根源，并对症下药。,第三大思维 降维,我们可以进行不同维度的拆分，拆分后还可以重新组合，从而做多维度的分析。 在上一等级课程中我们讲了用多重数据透视表双击合计的方式将二维表转为一维表。下面讲2016版本Excel支持的数据转换功能,右侧是待转换二维表,数据分析思维,第三大思维 降维,1、单击待转换数据区域任意单元格，【数据】-【获取和转换】-【从表格】,数据分析思维,2、这样Excel就会自动将区域转换为【表】，并且弹出【表x 查询编辑器】,第三大思维 降维,3、选择作为转换后的行标

15、签的某一列任意单元格，点击【转换】-【逆透视列】-【逆透视其他列】,数据分析思维,第三大思维 降维,3、就生成了右侧一维表,数据分析思维,第三大思维 降维,4、点击【开始】-【关闭并上载】。就生成了一个如右侧的新表结果,数据分析思维,顺便提一下 增维,在理解现有数据之后，为了方便我们进行分析，有目的的对数据进行增维运算。,增加一个毛利维度,数据分析思维,第四大思维 假设,数据分析思维,假设 是寻找本质问题的过程，收入下降的本质原因不是市场占有率下降，两者都是结果、现象（症状而不是症结）,第四大思维 假设,数据分析思维,应收账款周转期增加，为何？,问题,假设,证据,结论,应收账款周转期变化的主要

16、因素：信用期，信用期可积累应收账款,图表美化,标题,表格标题不存在啊,字体,全文宋体显土气,强调,标黄底色除了底稿外慎用，只标黄没解释,样式,样式看不出布局，全文都是网格线,标题,添加主标题，向读者并说明表格含义、数据单位,字体,汉字和数字、标题和正文使用不同字体,强调,使用粗体强调或者好看的底色标注,样式,首尾加粗、去掉框线、去掉合计的底色，表头加单下框线，表尾加上框线和粗下框线,简单概括表格的核心数据（或者观点）,注释,标题,与内容相同大小的白色字体不突出,字体,全文一种字体，大大小相同,强调,主题、标题都看不出强调,样式,框线、底色、字体颜色很多却看不出布局,字体,没有运用背景或图片表达

17、，对比不鲜明,背景： 1、背景图片内容不能过于丰富，会对表格信息的呈现带来干扰，有“喧宾夺主”之嫌。可以添加一个灰色的透明蒙版来弱化背景。 2、蒙版仅仅只是设置了统一的透明度，感觉比较单调，没有灵气而且灰色的力量感不够强。可以利用一个渐变的黑色透明蒙版，设置滑块为不同的透明度。 表格： 1、表格表头和主体的区分还不够明显。只对表头采用明艳的颜色即可，主体部分统一采用同一种浅色。 2、其实行列之间通过间距已经可以很清晰地分割开来，表格的线段太多余，可以把这些线段都删除。 3、表格就不一定要设置为完全不透明的填充。通过将表格主体的颜色设置为透明，并将文字填充为白色，既可以清晰地显示表格内容，又可以

18、和背景完美地融合在一起。,美化要点,表格布局,表格结构,样式,观点明确,美化要点,表格布局,表格布局合理 先了解表格要表达的观点，不要多和少与要表达观点相关的数据。避免冗余和看不懂。 根据要表达的观点和已有数据、根据已有数据要进一步分析的数据来设计表格的布局，布局应该反映信息的维度、数据的逻辑、计算和阅读的顺序等 重要的以及与多个字段关联的字段在前 如：数量在前，单位消耗人工、折旧、材料在后 相关或同类的数据应排列在一起 哪些字段作为在行，哪些字段在列 适当的和均匀的行款和列高 对其方式,美化要点,美国设计师 Robin Williams 在其著作写给大家看的设计书中将设计原则归纳为： 亲密性

19、 对齐 重复 对比,表格结构,美化要点,表格结构,对比 对比的基本思想是：要避免页面上的元素太过相似。如果元素（字体、颜色、大小、信息含义）不同，那就让它截然不同。要引人注目，对比通常是重要的因素。,重复 让设计中的视觉要素在整个作品中重复出现，可以重复颜色、空间关系、字体、大小等。这样一来增加条理性，统一性,对齐 任何东西在页面上都不能随意安放。每个元素都应该和页面上的另一个元素有某种视觉联系。这样能建立一种清晰、清爽的外观,亲密性 彼此相关的项应当靠近，归组在一起。如果多个项互相之间存在亲密性，他们就会成为一个视觉单元，而不是孤立的元素。有助于组织信息，减少混乱，提供清晰的结构。,美化要点

20、,对齐 任何东西在页面上都不能随意安放。每个元素都应该和页面上的另一个元素有某种视觉联系。这样能建立一种清晰、清爽的外观,表格结构,美化要点,表格结构,重复 让设计中的视觉要素在整个作品中重复出现，可以重复颜色、空间关系、字体、大小等。这样一来增加条理性，统一性,运用 可以使用【开始】-【样式】-【套用表格样式】，重复行底色,美化要点,对比 对比的基本思想是：要避免页面上的元素太过相似。如果元素（字体、颜色、大小、信息含义）不同，那就让它截然不同。要引人注目，对比通常是重要的因素。,运用： 字体字号：使用强调（更大、更粗）的字体字号 底色：使用底色与其他类别区分 缩进：缩进可以体现层次（将净利润调节为经营活动的现金流量就是） 标题有几种方式： 加粗并使用或同时使用不同字体 与内容增大间距（插行、调整高度） 使用底色（深底色则标题字体颜色用浅色）,表格结构,美化要点,亲密性 彼此相关的项应当靠近，归组在一起。