Exce审计l培训 高级

EXECEL审计高级应用及晓慧天健会计师事务所（特殊普通合伙）主题数据整理函数与公式数据透视表数据分析思维表格美化Audtool使用数据整理清理不规则数据调整表布局/结构数据辅助列降低/补充数据维度Excel只能处理符合其规则的数据。同时，为何满足我们数据分析的维度，我们必须将信息记录转换为低维度的数据单元。数据整理如何将2017.1.1或20170101转换为2017-1-1提示：Text函数、mid函数&组合、分列等如何去除数字中的非数字如何生成一个序时账的关键字段（进行vlookup）Word与excel组合配符替换、智能填充选择合适的序列进行组合，用&函数构造唯一值清理不规则数据数据整理删除重复值先汇总所有披露的关联往来、关联交易等明细表格，筛选出所有单位名称删除单位名称重复项，利用删除重复项功能案例根据今年统计的关联方交易和往来发生额的单位汇总，重新统计披露的关联方清单条件格式标记重复值/删除重复项/countif计数三种方法天健会计师事务所（特殊普通合伙）6加序列辅助列，用于排序后恢复原顺序添加辅助列实现隔行插空白行辅助列用于制作发生额借贷方向添加辅助列标记用于筛选数据整理辅助列数据整理xx科目/明细/单位这种信息明细表，以/分割为多列使用分列，选择分割符号为“\”增加数据维度二维表拆分一维表（ALT+D+P后双击行列交叉处的合计）2016/12/23天健会计师事务所（特殊普通合伙）8数据整理降低数据维度连接数据-编辑链接天健会计师事务所（特殊普通合伙）9当将客户报表与我们的报表模板通过连接填好后，复制一份填好的模板，使用“数据”下的“编辑连接”，通过“更改源”批量完成客户其他子公司报表过录到模板的操作数据整理天健会计师事务所（特殊普通合伙）10合并报表底稿中，链接了一家子公司的单体报表，如何使用复制和替换链接的形式实现其他子公司报表建立及更新链接？数据整理连接数据-复制替换数据整理创建组自动创建组手动创建组选取行/列（一般不选汇总、标题行列）【数据】-【分级显示】-【创建组】-【创建组】选取整个表格（或包含多个小计的行/列）【数据】-【分级显示】-【创建组】-【自动建立分级显示】快捷键shift+alt+左右箭头数据整理创建组取消组合选取相应区域，点击【数据】-【分级显示】-【取消组合】函数与公式函数与公式1.如何做一个折旧计算表2.如何做一个账龄分析计算表3.IRR与XIRR及摊余成本法4.如何做单变量求解5.使用PV计算辞退福利6.使用NPV做减值计算函数与公式说明一系列现金流的内部收益率。规则：现金流可不同，但间隔必须相同。注：内部收益率是针对包含付款（负值）和收入（正值）的定期投资收到的利率。所以，初始现金流都是负数表示，即算债券等筹资实际利率以贷款方角度讲融资方取得的借款看作自己的投资流出。IRR(values,[guess])•Values必需。数组或单元格的引用，这些单元格包含用来计算内部收益率的数字。•Values必须包含至少一个正值和一个负值，以计算返回的内部收益率。•IRR使用值的顺序来说明现金流的顺序。一定要按您需要的顺序输入支出值和收益值。Guess可选。对函数IRR计算结果的估计值。IRR函数与公式IRR函数与公式说明一组不一定定期发生的现金流的内部收益率。规则：现金流可不同，间隔也可不同。•Values必需。与dates中的支付时间相对应的一系列现金流。首期支付是可选的，并与投资开始时的成本或支付有关。如果第一个值是成本或支付，则它必须是负值。•所有后续支付都基于365天/年贴现。所以XIRR计算出来的是年内部收益率。Guess可选。对函数IRR计算结果的估计值。XIRR(values,dates,[guess])Dates必需。与现金流支付相对应的支付日期表。日期可按任何顺序排列。必须是日期格式XIRR函数与公式XIRR函数与公式函数XIRR的计算公式如下：0=𝑃𝑖1+rate𝑑𝑖−𝑑1365𝑁𝑖=1•其中：•di=第i个或最后一个支付日期。•d1=第0个支付日期。•Pi=第i个或最后一个支付金额。通过公式可以看到，折现率中使用了365天，也就是算出来的是年折现率。经测试，由于折现率使用的365天，所以XIRR为实际利率摊余成本后续计量时，利息支付周期应该是365天才适用XIRR函数与公式单变量求解：假设：初始投资50,000年收益率7%投资年限20年计算可得终值193,484.22总收益是：到期值-初始投资=143,484.22单变量求解：要想20年后，5万元的总收益是300%，年收益率应该是多少？操作：【数据】-【模拟分析】-【单变量求解】解释：单变量求解的前提是必须将各参数列在表格中，并用公式计算出来结果。单变量求解功能才能根据表格中的公式进行逆运算，“目标单元格”就是表格中公式计算的结果(B7)，目标值是你对计算结果设定的新结果，根据新结果求解的参数就是可变单元格(B2)函数与公式PV用于根据固定利率计算贷款或投资的现值。注：参数中，期数和利率必须对应。期数中每期是半年，则利率就是半年利率；参数中pmt和FV至少有一个是必需。PV(rate,nper,pmt,[fv],[type])利率终值年终还是年末期数年金函数与公式NPV使用贴现率和一系列未来支出（负值）和收益（正值）来计算一项投资的净现值。NPV(rate,value1,[value2],...)利率现金流现金流要求现金流在时间上必须具有相等间隔，并且都发生在期末。与PV一样，现金流间隔必须与利率计息期匹配，半年现金流则使用半年利率。在间隔不相同的情况下，建议使用XNPV函数。函数与公式在制作折旧计算表的时候，我们要考虑：•折旧计算的关键要素：原值、净残值、使用年限、折旧方法•要计算累计折旧和当期折旧，就需要先计算月折旧额、累计折旧月份和当期折旧月份•考虑特殊情况：当期处置、当期转入在建工程、当期从在建工程转入•如何考虑折旧差异调整：建议测算当期折旧差异，只调当期。因为计算累计折旧差异会有很多因素考虑不到，比如历史上技改暂停折旧等。直接用期初累计折旧和当期应提折旧计算累计折旧见模板文件夹中折旧计算模板折旧计算表函数与公式账龄分析的计算原则：先发生，先收回计算方法：1.按每一笔实际发生日期计算2.按月计算3.按年计算其中，第一种最准确特殊考虑：1.由于使用计算表，有减少发生额就算收回，比如应收账款的贷方发生额就算收回，但存在辅助核算合帐导致贷方发生额、借贷重新录入一次等非真实收回的贷方发生额，都需要手工调整，一般可通过先分析该科目对方科目发现，比如对方科目不是银行存款而是应收账款2.负数发生额可能代表收回或冲账，需要手工处理，可供过筛选发生额正负数或对方科目分析发现账龄分析计算表数据分析思维对照拆分降维假说数据分析思维第一大思维对照拿到数据后，如果数据是独立的，无法进行对比的话，就无法判断，等于无法从数据中读取有用的信息。比如收入审计，看到2016年的总销量，最基本的分析就是对比，与去年或更多历史年份对比，发现差异和规律，66666432688733332013年2014年2015年2016年收入第一大思维对照33332016年收入数据分析思维第一大思维对照对比维度有很多：纵向：不同时期横向：不同企业、同一企业不同部门、不同地区同比、环比；实际与目标；分组分析：根据数据分析对象的特征，按照一定的标志，把数据分析对象划分为不同的部分和类型来进行研究，以展现其内在的联系和规律。如地区、产品等结构分析：指被分析研究总体内部各部分与总体之间进行对比的分析方法，即总体内各部分占总体的比例，属于相对指标。如：成本结构比率变化、单耗等数据分析思维第二大思维拆分分析，就是拆分和解析。因此可见，拆分在数据分析中的重要性。很多事情经过拆分后清晰了。当某个维度可以对比的时候，我们选择第一个思维【比照】。对比后发现问题需要找出原因的时候？或者不可对比。这个时候，【拆分】就登场了收入单价数量我们可以进行不同维度的拆分，拆分后还可以重新组合，从而做多维度的分析数据分析思维第二大思维拆分财务指标拆分，一般是杜邦体系，当业务结构会引起应收账款周转指标的变动时，还可将同一指标按业务结构拆分。比如一家建筑施工企业有工程收入、销售商品收入、设计收入。一般情况下应收工程款的周转期较长，设计收入的周转期较短，当业务结构发生变化时，比如工程收入规模占收入的百分比增加较高，则周转天数会降低。因此，分业务类别计算周转指标及可比期间指标才更有意义。应收账款周转率应收工程款周转率应收关联方工程款周转率应收非关联方工程款周转率应收设计费周转率数据分析思维第二大思维拆分数据分析思维麦肯锡提倡按照不重不漏（MECE，MutuallyExclusiveCollectivelyExhausted相对独立，完全穷尽）的原则，将问题层层拆解成子问题，从而找到问题的根源，并对症下药。MECE对于问题的基本假设是基于简单的因果关系。现实更像是网络的环状结构，而不是简单线性的因果结构。任何一个因素，在系统中都可能和其他因素产生互动关系。一家企业曾经面临着成本上升、收入和利润严重下降等一系列问题。如何改变局面，公司期待着管理层在产品竞争力、降低成本、开发新品等方面做出努力。而实际管理层解决的突破口是：增加公司在安全生产上的投入。因为了解公司运作系统的管理层知道事故、停工、次品率导致了无效人工、浪费成本第二大思维拆分数据分析思维麦肯锡提倡按照不重不漏（MECE，MutuallyExclusiveCollectivelyExhausted）的原则，将问题层层拆解成子问题，从而找到问题的根源，并对症下药。第三大思维降维我们可以进行不同维度的拆分，拆分后还可以重新组合，从而做多维度的分析。在上一等级课程中我们讲了用多重数据透视表双击合计的方式将二维表转为一维表。下面讲2016版本Excel支持的数据转换功能月份A产品B产品C产品1月1,9693,9143,8112月5701,1391,1393月9504,2761,9014月1,9923,9853,9855月5941,1881,1886月1,0294,6332,059右侧是待转换二维表数据分析思维第三大思维降维1、单击待转换数据区域任意单元格，【数据】-【获取和转换】-【从表格】数据分析思维2、这样Excel就会自动将区域转换为【表】，并且弹出【表x查询编辑器】第三大思维降维3、选择作为转换后的行标签的某一列任意单元格，点击【转换】-【逆透视列】-【逆透视其他列】数据分析思维第三大思维降维3、就生成了右侧一维表数据分析思维第三大思维降维4、点击【开始】-【关闭并上载】。就生成了一个如右侧的新表结果数据分析思维顺便提一下增维在理解现有数据之后，为了方便我们进行分析，有目的的对数据进行增维运算。产品收入成本A产品1,9693,914B产品5701,139C产品9504,276产品收入成本毛利A产品1,9693,914-1,944B产品5701,139-570C产品9504,276-3,326增加一个毛利维度数据分析思维第四大思维假设数据分析思维假设是寻找本质问题的过程，收入下降的本质原因不是市场占有率下降，两者都是结果、现象（症状而不是症结）找出真正问题•属于本质性的问题•收入下降了-市场占有率下降（错）先建立假设•市场规模为何下降•价格、消费者偏好、替代品第四大思维假设数据分析思维应收账款周转期增加，为何？问题假设证据结论应收账款周转期变化的主要因素：信用期，信用期可积累应收账款表格病因表格美化图表美化人都喜欢好看的东西，表格好看，看起来舒服还要方便阅读赏心悦目美化表格的目的是更好的表达信息，让表格会说话清晰表达一个好看的表格体现专业素养，表达能力、思路和丢读者需求的了解.体现专业表格病因Excel表格标题表格标题不存在啊字体全文宋体显土气强调标黄底色除了底稿外慎用，只标黄没解释样式样式看不出布局，全文都是网格线产品自制半成品直接人工折旧水电费A产品11,420.455,464.512,145.273,