EXCEL在财务管理中的应用课件.pptx

为什么为什么EXCEL对财务人员如此重要对财务人员如此重要财务人员每天面对大量数据和信息，大部分的数据都将通过财务人员每天面对大量数据和信息，大部分的数据都将通过EXCEL处理处理决策和分析需要用数据（事实）说话决策和分析需要用数据（事实）说话EXCEL是帮助财务人员进行数据处理的强大通用工具是帮助财务人员进行数据处理的强大通用工具用好用好EXCEL意味着：意味着：大大减少工作量和劳动强度节省时间去处理更为重要的任务及时快速的决策支持在下属和上司面前树立自己的技术权威致力于财务管理实践，打造卓越财务经理人！提升提升Excel操作水平的快捷方法操作水平的快捷方法掌握财务工作中常用的快捷键掌握财务工作中常用的快捷键掌握财务工作中常用的菜单项掌握财务工作中常用的菜单项熟悉财务工作中常用的函数和公式熟悉财务工作中常用的函数和公式双手操作，键盘鼠标同上阵双手操作，键盘鼠标同上阵模块一：常用函数的运用模块一：常用函数的运用模块二：透视表的运用模块二：透视表的运用模块三：图表的制作模块三：图表的制作模块四：模块四：EXCEL与与PPT的结合运用的结合运用课程安排课程安排模块一：实用函数财务部常用函数介绍财务部常用函数介绍实用案例：实用案例：VLOOKUP的巧用的巧用 实用案例：工资个人所得税计算的技巧实用案例：工资个人所得税计算的技巧 实用案例：补充不完整的数据表实用案例：补充不完整的数据表 实用案例：三招搞定实用案例：三招搞定excel单单多条件求和多条件求和 Year():取出单元格中日期字符的年份 Month()：取出单元格中日期字符的月份 Now():取出当天日期，也可以用函数TODAY()代替想想看，制作应收账款余额表时,日期函数有什么用？Now()Date()?常用函数之日期篇常用函数之日期篇致力于财务管理实践，打造卓越财务经理人！致力于财务管理实践，打造卓越财务经理人！Sum()：数值求和 Sum函数的快捷键是”ALT”+”+”Sumproduct():利用数组进行数值求和 Sumif()：有条件的数值求和 Count():数目求和 Countif():有条件的数目求和常用函数之统计篇常用函数之统计篇致力于财务管理实践，打造卓越财务经理人！致力于财务管理实践，打造卓越财务经理人！Vlookup()：在表格或数值数组的首列查找指定的数值（可返回一列，也可利用数组返回多列，并由此返回表格或数组当前行中指定列处的数值。用于条件定位，是财务工作中使用频率极高的一个函数。如果使用数组的话可以一次找出多列结果.常用函数搜索篇常用函数搜索篇致力于财务管理实践，打造卓越财务经理人！致力于财务管理实践，打造卓越财务经理人！巧用VLOOKUP查找，替换数据编制的基础是从财务软件中取得的应收帐款科编制的基础是从财务软件中取得的应收帐款科编制的基础是从财务软件中取得的应收帐款科编制的基础是从财务软件中取得的应收帐款科目的明细记录清单。要得到所需要的输出格式，目的明细记录清单。要得到所需要的输出格式，目的明细记录清单。要得到所需要的输出格式，目的明细记录清单。要得到所需要的输出格式，我们还需要增加以下四个字段的信息：帐龄，我们还需要增加以下四个字段的信息：帐龄，我们还需要增加以下四个字段的信息：帐龄，我们还需要增加以下四个字段的信息：帐龄，客户名称，销售经理和销售渠道客户名称，销售经理和销售渠道客户名称，销售经理和销售渠道客户名称，销售经理和销售渠道巧用VLOOKUP查找，替换数据 VLOOKUPVLOOKUP的原理就是根据两张表（源表，目标表）共的原理就是根据两张表（源表，目标表）共的原理就是根据两张表（源表，目标表）共的原理就是根据两张表（源表，目标表）共有的索引列来把源表的指定列引用到目标表格上。接下有的索引列来把源表的指定列引用到目标表格上。接下有的索引列来把源表的指定列引用到目标表格上。接下有的索引列来把源表的指定列引用到目标表格上。接下来我们使用来我们使用来我们使用来我们使用VLOOKUPVLOOKUP函数将供应商名称从订单信息表函数将供应商名称从订单信息表函数将供应商名称从订单信息表函数将供应商名称从订单信息表中查找到并填写到当前表格上去。中查找到并填写到当前表格上去。中查找到并填写到当前表格上去。中查找到并填写到当前表格上去。你需要根据哪个索引列查找？确定进行要在源表上进行查找的数据区矩形区域指定引用列在在矩形区域中所处的列数，以索引列为第1列默认为0或FALSE，表示绝对匹配巧用VLOOKUP查找，替换数据 索引列的唯一性：索引列的重复将导致系统只引用首先索引列的唯一性：索引列的重复将导致系统只引用首先索引列的唯一性：索引列的重复将导致系统只引用首先索引列的唯一性：索引列的重复将导致系统只引用首先找到的（通常从上往下的第一个）值，从而丢失数据找到的（通常从上往下的第一个）值，从而丢失数据找到的（通常从上往下的第一个）值，从而丢失数据找到的（通常从上往下的第一个）值，从而丢失数据 绝对引用：引用源表的索引区域时一定记得加绝对引用：引用源表的索引区域时一定记得加绝对引用：引用源表的索引区域时一定记得加绝对引用：引用源表的索引区域时一定记得加$，否则可，否则可，否则可，否则可能无法查到数据能无法查到数据能无法查到数据能无法查到数据 索引列：字符前后无空格，其数据类型两张表必须保持索引列：字符前后无空格，其数据类型两张表必须保持索引列：字符前后无空格，其数据类型两张表必须保持索引列：字符前后无空格，其数据类型两张表必须保持一致，否则查找不到，可以运用一致，否则查找不到，可以运用一致，否则查找不到，可以运用一致，否则查找不到，可以运用TRIMTRIM（）函数解决（）函数解决（）函数解决（）函数解决VLOOKUP使用时容易犯的三大错误巧用VLOOKUP查找，替换数据 连续输入三次连续输入三次连续输入三次连续输入三次VLOOKUPVLOOKUP函数可以完成以函数可以完成以函数可以完成以函数可以完成以上三列，有没有一次搞定的好方法呢？上三列，有没有一次搞定的好方法呢？上三列，有没有一次搞定的好方法呢？上三列，有没有一次搞定的好方法呢？如果需要引用的不是三列，而是十三列，如果需要引用的不是三列，而是十三列，如果需要引用的不是三列，而是十三列，如果需要引用的不是三列，而是十三列，三十列，那么即便一次次的输入三十列，那么即便一次次的输入三十列，那么即便一次次的输入三十列，那么即便一次次的输入VLOOKUPVLOOKUP函数也会让人抓狂，财务人员函数也会让人抓狂，财务人员函数也会让人抓狂，财务人员函数也会让人抓狂，财务人员需要更强大的需要更强大的需要更强大的需要更强大的VLOOKUPVLOOKUP函数使用方法函数使用方法函数使用方法函数使用方法VLOOKUP的进阶使用：一次搞定N列！相对引用和绝对引用相对引用和绝对引用 相对引用、绝对引用是指在公式中使用单元格或单元格区域的地址时，当相对引用、绝对引用是指在公式中使用单元格或单元格区域的地址时，当将公式向旁边复制时，地址是如何变化的。将公式向旁边复制时，地址是如何变化的。具体情况举例说明：具体情况举例说明：1 1、相对引用，复制公式时地址跟着发生变化，如、相对引用，复制公式时地址跟着发生变化，如C1C1单元格有公式：单元格有公式：=A1+B1=A1+B1 当将公式复制到当将公式复制到C2C2单元格时变为：单元格时变为：=A2+B2=A2+B2 当将公式复制到当将公式复制到D1D1单元格时变为：单元格时变为：=B1+C1=B1+C1 2 2、绝对引用，复制公式时地址不会跟着发生变化，如、绝对引用，复制公式时地址不会跟着发生变化，如C1C1单元格有公式：单元格有公式：=$A$1+$B$1=$A$1+$B$1 当将公式复制到当将公式复制到C2C2单元格时仍为：单元格时仍为：=$A$1+$B$1=$A$1+$B$1 当将公式复制到当将公式复制到D1D1单元格时仍为：单元格时仍为：=$A$1+$B$1=$A$1+$B$1 在在Excel表中创建公式时，若使用单元格引用，就意味着这个公式同表中创建公式时，若使用单元格引用，就意味着这个公式同工作薄中的其他单元格连接起来了，当引用单元格中的值发生变化工作薄中的其他单元格连接起来了，当引用单元格中的值发生变化时，公式的值也相应地发生变化。时，公式的值也相应地发生变化。lIf()：可以使用函数 IF 对数值和公式进行条件检测 And()：所有参数的逻辑值为真时，返回 TRUE；只要一个参数的逻辑值为假，即返回 FALSE。lOr()：在其参数组中，任何一个参数逻辑值为 TRUE，即返回 TRUE，否则为FALSE 判断型函数一般不会单独使用，而是与其它类型的函数构成复合函数出现。看上去很简单的函数，用得好就能化腐朽为神奇，节约你大量宝贵的工作时间。常用函数之判断篇常用函数之判断篇致力于财务管理实践，打造卓越财务经理人！致力于财务管理实践，打造卓越财务经理人！l Find()：查找其他文本字符串(within_text)内的文本字符串(find_text)并返回 find_text 的起始位置编号。l Left():从一个文本字符串的左边第一个字符开始，截取指定数目的字符。l Right():从一个文本字符串的右边第一个字符开始，截取指定数目的字符 l Mid():从一个文本字符串的指定位置开始，截取指定数目的字符。l Len():统计文本字符串中字符数目。l Trim():把单元格字符串中前后的空格去除，常用于精确比较前的准备工作常用函数之字符篇常用函数之字符篇致力于财务管理实践，打造卓越财务经理人！致力于财务管理实践，打造卓越财务经理人！实用复合函数例实用复合函数例2补充不完整的数据表补充不完整的数据表 公式法公式法自动宏自动宏致力于财务管理实践，打造卓越财务经理人！致力于财务管理实践，打造卓越财务经理人！其他部门交来的表格留下了大量的空格，相同栏目只是第一行会写名字，如果一个个手工给填上去，几千行数据，得花多少时间啦？Excel能帮我想想办法吗？实用三招搞定EXCEL单/多条件求和汇总 EXCEL中用SUM求和大家都了解。如图所示。但如果我们需要知道区域是华北的A产品销售额是多少？能不能有办法用公式/函数实现呢？致力于财务管理实践，打造卓越财务经理人！致力于财务管理实践，打造卓越财务经理人！第一招：用SUMIF（）单条件求和指定条件所在的区域指定条件所在的区域指定条件，可以是数值或表达式指定条件，可以是数值或表达式要进行汇总的数据区域要进行汇总的数据区域致力于财务管理实践，打造卓越财务经理人！致力于财务管理实践，打造卓越财务经理人！第第1步步第第2步步第第3步步第二招：用EXCEL自带的条件求和向导实现多条件求和第二招：用EXCEL自带的条件求和向导实现多条件求和条件求和向导需要在加载宏中加载方可运用条件求和向导需要在加载宏中加载方可运用致力于财务管理实践，打造卓越财务经理人！致力于财务管理实践，打造卓越财务经理人！第二招：用EXCEL自带的条件求和向导实现多条件求和第三招：用SUM+IF实现多条件求和 求A产品30000和B产品都大于25000的分公司收入合计*代表和，改成+就代表或运用数组和用*/+连接乃是实现多条件求和的关键致力于财务管理实践，打造卓越财务经理人！致力于财务管理实践，打造卓越财务经理人！=SUM(IF($C$2:$C$1430000,IF($D$2:$D$1425000,$F$2:$F$14,0),0)致力于财务管理实践，打造卓越财务经理人！致力于财务管理实践，打造卓越财务经理人！QUESTION：统计出区域在华北并且A产品销售额超过20000元或B产品销售额超过15000元的分公司之销售收入总和。Tips:/+，（），数组的灵活组合与运用是实现多条件求和（求个数）的关键。，（），数组的灵活组合与运用是实现多条件求和（求个数）的关键。致力于财务管理实践，打造卓越财务经理人！致力于财务管理实践，打造卓越财务经理人！记住这三点，N多条件求和也不怕！+号表示条件号表示条件OR/*号表示条件号表示条件AND（）里面的表达式将得到优先运算然后再参与（）外的运算（）里面的表达式将得到优先运算然后再参与（）外的运算换成换成Count将可以统计符合条件的公司数量将可以统计符合条件的公司数量致力于财务管理实践，打造卓越财务经理人！致力于财务管理实践，打造卓越财务经理人！模块一：常用函数的运用模块一：常用函数的运用模块二：透视表的运用模块二：透视表的运用模块三：图表的制作模块三：图表的制作模块四：模块四：EXCEL与与PPT的结合运用的结合运用课程安排课程安排模块二：透视表的应用透视表的基本应用透视表的基本应用透视表在财务报表中的综合运用透视表在财务报表中的综合运用对每个月预算的执行情况，财务部在月结完后都对每个月预算的执行情况，财务部在月结完后都要对外提供管理报表。由于每位部门副总看问题要对外提供管理报表。由于每位部门副总看问题的角度、对报表格式的要求不一致，同样的数据的角度、对报表格式的要求不一致，同样的数据内容（比如应收帐款的分析），财务部总是要做内容（比如应收帐款的分析），财务部总是要做出好几张报表，费时费力。有没有更好的办法来出好几张报表，费时费力。有没有更好的办法来一劳永逸的解决问题呢？一劳永逸的解决问题呢？销售：按供应商排序、汇总销售：按供应商排序、汇总财务：按帐龄排序、汇总财务：按帐龄排序、汇总人力资源：按销售经理排序、汇总人力资源：按销售经理排序、汇总2.1透视表应用案例1.1.打开课堂用沙盘文件目录下的应收帐款余额表文打开课堂用沙盘文件目录下的应收帐款余额表文件，选到应收帐款余额明细表，执行以上操作。件，选到应收帐款余额明细表，执行以上操作。2.1透视表应用案例Step 1-点击插入按钮Step 2-点击数据透视表2.1透视表应用案例Step 3-数据选择区域确认，确保你需要的数据内容都被选上！Step 4-点击确认，一般情况下我们都将透视表放在新工作表内，因此第2部分的选项不需要改动2.1透视表应用案例透视表进阶按钮区透视表字段选择区透视表字段置放区，透视表结果显示区，将字段从选择区拖拽至置放区，则结果显示区发生变化。显示结果。数据透视表选项-显示-经典数据透视表布局2.1透视表应用案例将应收帐款余额拖至此处将帐龄拖至此处将客户名额拖至此处Step4.Step4.按财务的表格要求制作透视表。（试试看将按财务的表格要求制作透视表。（试试看将渠道拖至页字段会出现什么情况？）渠道拖至页字段会出现什么情况？）2.1透视表应用案例将应收帐款余额拖至此处将帐龄拖至此处将客户名额拖至此处Step4.Step4.按财务的表格要求制作透视表。（试试看将按财务的表格要求制作透视表。（试试看将渠道拖至页字段（报表筛选）会出现什么情况？）渠道拖至页字段（报表筛选）会出现什么情况？）2.1透视表应用案例Step5.Step5.表格的雏形初显，还需要进一步加工表格的雏形初显，还需要进一步加工1.1.金额显示为千元级，小数点后两位。金额显示为千元级，小数点后两位。2.2.按总计金额大小降序排列。按总计金额大小降序排列。3.3.帐龄显示应该从左至右升序排列，方符合财务工帐龄显示应该从左至右升序排列，方符合财务工作的习惯。作的习惯。2.1透视表应用案例课堂练习：通过改变数据表的结构完成销售和人课堂练习：通过改变数据表的结构完成销售和人力资源要求的表格。力资源要求的表格。2.1透视表应用案例数据更新和追加数据按钮万一找不到字段列表了，就点它通过增加公式字段来适用不同的报表需要，而不必改变原始数据表小贴士-记牢快捷键，不变应万变多记住一些快捷键操作，比单纯记住菜多记住一些快捷键操作，比单纯记住菜单项上的选择界面要更有效。单项上的选择界面要更有效。Excel2007的界面比之前的版本有了巨大的界面比之前的版本有了巨大的变动，很多菜单项都让人感觉找不到的变动，很多菜单项都让人感觉找不到了，但快捷键没有变。了，但快捷键没有变。“以不变应万变以不变应万变”是应付是应付Excel版本不断版本不断更新的一个好办法。更新的一个好办法。常用的透视表操作常用的透视表操作修改字段名称，数据格式，汇总方式，显示方式，是否隐藏等。是否隐藏，是否分类汇总，排序通过公式新增计算字段设置报表格式数据的更新不会编宏也能让EXCEL自动制作透视表致力于财务管理实践，打造卓越财务经理人！多重区域合并的透视表运用同时对多张相同的表格进行透视表分析同时对多张相同的表格进行透视表分析应注意的事项应注意的事项模块一：常用函数的运用模块一：常用函数的运用模块二：透视表的运用模块二：透视表的运用模块三：图表的制作模块三：图表的制作模块四：模块四：EXCEL与与PPT的结合运用的结合运用课程安排课程安排第一部分Excel图表基本篇饼图的运用 饼图一般多用于某种静态数据分布的表现，反映的只饼图一般多用于某种静态数据分布的表现，反映的只是企业财务状况的一个切面，较之其他图形用得较少，是企业财务状况的一个切面，较之其他图形用得较少，但贵在简要、直观。但贵在简要、直观。直方图的运用直方图可以通过数据系列格式选项分类间距来改变直方的粗细及间距。改变刻度直方图的运用-哪张图中的12月份费用增加比例更大？qq柱状复合图柱状复合图柱状复合图柱状复合图 特别适用于表示预算与实际情况的对比特别适用于表示预算与实际情况的对比qq双轴图双轴图双轴图双轴图如何画曲线图表散点图法趋势线法显示公式第二部分Excel图表提高篇散点图和趋势线的财务预测功能给你所图所示的数据，你能预测不同产量水平下的润滑油费用支出预算吗？散点图和趋势线的财务预测功能动态图表的演示让图表动起来！点哪个按钮就显示哪个地区的形势。让图表动起来！点哪个按钮就显示哪个地区的形势。模块一：常用函数的运用模块一：常用函数的运用模块二：透视表的运用模块二：透视表的运用模块三：图表的制作模块三：图表的制作模块四：模块四：EXCEL的综合运用的综合运用课程安排课程安排EXCEL与PPT的结合运用照相机的使用照相机的使用PPT内嵌内嵌EXCEL表格的处理表格的处理色彩，格式的调配使用色彩，格式的调配使用我们在用Excel处理文件时，如果需要将当前的内容作为图片保存起来的话，通常会选择用截图的方法来实现。可是你知道吗，Excel还提供了一个“照相机”功能，只要用它把要保存的内容“照”下来，再粘贴到另一个页面上就行了 照相机的使用照相机的使用照相机的使用Excel自带的照相机和普通截图功能的区别自带的照相机和普通截图功能的区别用Excel自带的照相机进行截图后，里面的数据可以同步进行更新。也就是说，在Excel截取成图片的部分，如果原Excel数据进行更改的话，图片也会同步进行更改。照相机的使用EXCEL内嵌表格的处理最常用的七个菜单项1.选择性粘贴选择性粘贴(Pastespecial)2.分列分列/文字剖析文字剖析(TextToColumns)3.排序及自定义排序排序及自定义排序(Sort&User-Defined Sort)4.筛选和高级筛选筛选和高级筛选(Filter&Advanced Filter)5.寻找和替代寻找和替代(Find&Replace)6.数据有效性数据有效性(Validity)7.打印预览打印预览(Print Preview)费用报销单的制作界面友好，易于后期管理的表单界面友好，易于后期管理的表单目的目的方法方法填写表格提示内容时的简易提示数据有效性输入信息填写表格不符合要求时的提醒与阻止数据有效性出错警告根据表单设计者的要求进行选择项的输入数据有效性有效性条件设置根据输入者填写的某项内容自动填写其他内容VLOOKUP/SUMIF/IF等公式函数的使用方便表单引用内容的维护和更新引用和链接美观大方单元格的格式设置，表框，图案费用报销单的制作-数据有效性Excel在预算管理中的应用模型第一章 经典自动化预算模板简介1.1 1.1 传统预算编制方法的特点传统预算编制方法的特点1.2 1.2 自动化预算模板现场演示自动化预算模板现场演示1.3 1.3 自动化预算模板编制的优点自动化预算模板编制的优点1.1传统预算编制方法的特点输入：数据收集模板内容简单，格式不统一，申输入：数据收集模板内容简单，格式不统一，申报部门可自行修改，随意性大。（后期的数据更报部门可自行修改，随意性大。（后期的数据更新、维护工作量巨大。）新、维护工作量巨大。）中间整理：较少运用公式、函数、链结，基本仍中间整理：较少运用公式、函数、链结，基本仍停留在将停留在将ExcelExcel当在电子草稿纸使用的阶段。表与当在电子草稿纸使用的阶段。表与表之间很少或无数据联动关系。表之间很少或无数据联动关系。输出：三大表之间很少或无数据联动关系，与数输出：三大表之间很少或无数据联动关系，与数据收集模板之间也无公式或链结联系。据收集模板之间也无公式或链结联系。1.1传统预算编制方法的缺点慢！难以满足企业决策层的快速反应的决策需要，慢！难以满足企业决策层的快速反应的决策需要，不能现场对景境的变化给出模拟结果。不能现场对景境的变化给出模拟结果。累！由于预算表格缺乏自动更新的能力，几个数累！由于预算表格缺乏自动更新的能力，几个数据的更新，可能意味着三大表都要推倒重来一次。据的更新，可能意味着三大表都要推倒重来一次。难！预算参数牵一发而动全身，每次都靠人来判难！预算参数牵一发而动全身，每次都靠人来判断其财务影响，很难做到客观、全面断其财务影响，很难做到客观、全面第一章 经典自动化预算模板简介1.1 1.1 传统预算编制方法的特点传统预算编制方法的特点1.2 1.2 自动化预算模板现场演示自动化预算模板现场演示1.3 1.3 自动化预算模板编制的优点自动化预算模板编制的优点1.2自动化预算模板现场演示情景模拟：财务部负责人带着最新版本情景模拟：财务部负责人带着最新版本的公司预算到高级管理层会议上接受咨的公司预算到高级管理层会议上接受咨询，经讨论，有许多决策参数可能需要询，经讨论，有许多决策参数可能需要改动（包括汇率、利率、原材料涨价幅改动（包括汇率、利率、原材料涨价幅度，人员加薪水平度，人员加薪水平,产品的销量和价格产品的销量和价格，总经理希望能看到按此景境，总经理希望能看到按此景境（Scenario）修正后的模拟结果）修正后的模拟结果,方可继方可继续讨论下去。作为财务部负责人，你能续讨论下去。作为财务部负责人，你能否现场即时提供并分析其影响？还是表否现场即时提供并分析其影响？还是表示要回去研究，下次开会再接着讨论？示要回去研究，下次开会再接着讨论？1.2自动化预算模板现场演示汇率调整前的利润数字1.2自动化预算模板现场演示将参数假设表上的明年汇率预计改为6.0预计净利润从322万变成了310万，下降了12万元！完成这新的预测结果只用了几秒钟！（小提示：看看资产负债表，现金流量表的数字有没有跟着汇率同步更新？合不合理）第一章 经典自动化预算模板简介1.1 1.1 传统预算编制方法的特点传统预算编制方法的特点1.2 1.2 自动化预算模板现场演示自动化预算模板现场演示1.3 1.3 自动化预算模板编制的优点自动化预算模板编制的优点1.3自动化预算编制方法的优点智能化！智能化！预算数据的改变只需数秒至数分钟的预算数据的改变只需数秒至数分钟的时间即可反映到以三大表为首的预算结果上，大时间即可反映到以三大表为首的预算结果上，大大提高了决策效率，体现出财务部的决策支持能大提高了决策效率，体现出财务部的决策支持能力！力！标准化！数据收集模板全部标准化格式，新旧版标准化！数据收集模板全部标准化格式，新旧版本的数据更新瞬间即可完成！省时省力！本的数据更新瞬间即可完成！省时省力！集中化！各类决策参数对财务方方面面的影响在集中化！各类决策参数对财务方方面面的影响在模板设计之初就已全面考虑并设计好。因此后期模板设计之初就已全面考虑并设计好。因此后期使用时不用预测的全面性和准确性！使用时不用预测的全面性和准确性！年度预算模板年度预算模板参数参数/假设表假设表（输入）（输入）成本库成本库（输入）（输入）中间过程计算表中间过程计算表（输入（输入+计算）计算）三大表三大表（自动计算、输出）（自动计算、输出）价格模型价格模型1.2自动化预算模板基本结构第二章 Excel预算编制技巧综述500500强公司财务部强公司财务部EXCELEXCEL预算编制时的五大利器预算编制时的五大利器公式和链接公式和链接标准化数据收集模板标准化数据收集模板透视表透视表复合函数复合函数宏（宏（VBAVBA）第三章基础预算数据的收集与整理3.13.1制作标准化数据收集模板的意义制作标准化数据收集模板的意义3.23.2标准化数据收集模板的制作技巧标准化数据收集模板的制作技巧3.3 3.3 基础预算数据的整理基础预算数据的整理3.43.4 基础预算数据的更新基础预算数据的更新3.1标准化数据收集模板的意义保证数据输入的质量和全面性保证数据输入的质量和全面性便于后期的维护和版本更新便于后期的维护和版本更新利于数据的处理和后期财务分析利于数据的处理和后期财务分析防止填写部门篡改数据和信息泄漏防止填写部门篡改数据和信息泄漏介面友好、填写方便介面友好、填写方便3.2标准化数据收集模板的制作技巧单元格的隐藏和保护单元格的隐藏和保护数据有效性数据有效性VlookupVlookup函数函数常见的预算收集模板种类按部门收集按部门收集 （大部分传统企业采用此形（大部分传统企业采用此形式）式）按项目、产品收集（以项目型、产品事按项目、产品收集（以项目型、产品事业部为主导的公司）业部为主导的公司）综合型综合型 （多见组织架构为矩阵型的大型（多见组织架构为矩阵型的大型公司）公司）综合应用型创建一张汇总表，其后为各部门分表创建一张汇总表，其后为各部门分表各部门分表用链结引用各部门上交的格各部门分表用链结引用各部门上交的格用预算模板用预算模板总表用总表用SUM+数组的方法合并分表数据数组的方法合并分表数据另建一张分部门汇总表。另建一张分部门汇总表。各部门交来的表格分门别类放好，便于追踪。常见的预算收集模板种类按部门收集按部门收集 （大部分传统企业采用此形（大部分传统企业采用此形式）式）按项目、产品收集（以项目型、产品事按项目、产品收集（以项目型、产品事业部为主导的公司）业部为主导的公司）综合型综合型 （多见组织架构为矩阵型的大型（多见组织架构为矩阵型的大型公司）公司）费用汇总表费用汇总表完成后，预算工作下一步的费用汇总表完成后，预算工作下一步的重点一目了然。对那些业务部门也无法重点一目了然。对那些业务部门也无法明确分辨出来的共享成本（或称之为共明确分辨出来的共享成本（或称之为共性费用），需要中间过程计算（分配）性费用），需要中间过程计算（分配）表的设计和应用了。表的设计和应用了。费用汇总表-链结管理部门又改了预算版本，数字又变了，怎部门又改了预算版本，数字又变了，怎么办？需要将之前的工作再做一遍吗？么办？需要将之前的工作再做一遍吗？编辑链接是个好东西！来多少版修改都不怕，几秒钟的事！选中有需要更新版本的预算文件，点击“更改源”按钮选中有部门重交上来的更新版本，点击确定即可立即完成全文件的更新。（记得之后所有相关的透视表也不要忘记同步更新）费用汇总表-链结管理传统的预算前期工作流程传统模式：收集完所有部门预算模板后再开始传统模式：收集完所有部门预算模板后再开始汇总、分析。收集完之前的时间很多在等待中浪汇总、分析。收集完之前的时间很多在等待中浪费掉了。因为无法形成整体印象。费掉了。因为无法形成整体印象。自动链结模式：收集、更新、汇总、分析同步自动链结模式：收集、更新、汇总、分析同步进行，时间利用效率高。进行，时间利用效率高。第三章 预算表格间的链结与联动3.1 3.1 基础数据表与三大表直接联动基础数据表与三大表直接联动3.2 3.2 基础数据表与三大表间接联动基础数据表与三大表间接联动3.3 3.3 三大表之间的联动三大表之间的联动基础数据表自上而下的参数假设表自上而下的参数假设表自下而上的部门预算收集模板自下而上的部门预算收集模板中间过程计算表大部分收集上来的预算材料并不能直接填入三大部分收集上来的预算材料并不能直接填入三大表，而是需要一个整理的过程，如汇总、分类、大表，而是需要一个整理的过程，如汇总、分类、分配、摊销等，把这个过程模板化和标准化，就分配、摊销等，把这个过程模板化和标准化，就形成了中间过程计算表，它是基础数据表与三大形成了中间过程计算表，它是基础数据表与三大表之间的桥梁。表之间的桥梁。典型的中间过程计算表：典型的中间过程计算表：各类费用往产品、项目、事业部进行分摊的表格。各类费用往产品、项目、事业部进行分摊的表格。根据职级标准和人力结构计算工资成本用的表格。根据职级标准和人力结构计算工资成本用的表格。间接法下的营运现金流计算表格间接法下的营运现金流计算表格利息计算表格利息计算表格基本模式输入（基础数据表）整理（中间过程计算表）输出（三大表）成本预算资料的收集料：生产部门提供材料清单（料：生产部门提供材料清单（BOM COST），无需再次分配），无需再次分配工：人力资源部负责收集和提供，间接工：人力资源部负责收集和提供，间接人工也需要用中间过程计算表分配人工也需要用中间过程计算表分配费：分直接费用和间接费用，间接费用费：分直接费用和间接费用，间接费用用模板收集，用中间过程计算表分配用模板收集，用中间过程计算表分配4.1 基础数据表与三大表直接联动 直接用链接进行引用：无须摊销或分配的数据直接用链接进行引用：无须摊销或分配的数据直接用链接进行引用：无须摊销或分配的数据直接用链接进行引用：无须摊销或分配的数据=销售收入销售收入销售收入销售收入!G6*!G6*直接材料成本直接材料成本直接材料成本直接材料成本!$F$37+!$F$37+销售收入销售收入销售收入销售收入!G7*!G7*直接材料成本直接材料成本直接材料成本直接材料成本!$F$37+!$F$37+销售收入销售收入销售收入销售收入!G9*!G9*直接材料成本直接材料成本直接材料成本直接材料成本!$F$41+!$F$41+销售收入销售收入销售收入销售收入!G10*!G10*直接直接直接直接材料成本材料成本材料成本材料成本!$F$40!$F$40“Alt+Alt+”显示公式，能对单元格的公式设置是否正常进行快速的显示公式，能对单元格的公式设置是否正常进行快速的显示公式，能对单元格的公式设置是否正常进行快速的显示公式，能对单元格的公式设置是否正常进行快速的检查。检查。检查。检查。数量X单价4.1 基础数据表与三大表直接联动资产负债表中存在着大量的直接与参数假设表的引用联动公式4.1 基础数据表与三大表直接联动现金流量表中存在着大量的直接与参数假设表的引用联动公式费用表与分摊比例的匹配厂房折旧间接物料水、电、燃料辅助生产人员工资运费生产废品损失厂房面积比机器工时比员工数量比原材料/产品价值比产品数量比收发货次数比A产品B产品4.2 基础数据表与三大表间接联动小刘是一家数码产品销售公司的财务经小刘是一家数码产品销售公司的财务经理，为了提前掌握和分析公司的销售情理，为了提前掌握和分析公司的销售情况和现金流，他要求各销售代表按月更况和现金流，他要求各销售代表按月更新客户的产品订单需求。新客户的产品订单需求。为刺激销售，该公司的产品实行量价台为刺激销售，该公司的产品实行量价台阶的机制，并且根据实际销售情况，该阶的机制，并且根据实际销售情况，该台阶每月还会有更新。台阶每月还会有更新。该工作目前是纯手工计算，工作量非常该工作目前是纯手工计算，工作量非常大，最近管理层要求按星期来更新。大，最近管理层要求按星期来更新。中间过程表案例中间过程表案例中间过程表案例中间过程表案例量价台阶表，每月一更新白色区域是销售代表们输入的预计订单信息彩色区域是财务部为了完成最后的分析报表要判断、填写的内容。中间过程表案例中间过程表案例有没有办法在收集销售订单信息后，在有没有办法在收集销售订单信息后，在10分钟后做出这套销售分析报表呢？分钟后做出这套销售分析报表呢？IF+VLOOKUP=智能化判断取数智能化判断取数透视表透视表=自动更新输入报表自动更新输入报表中间过程计算表的意义u帮助完成从输入到输出的中间过程计算，以免最终的预算表格过于臃肿，难于阅读。u典型中间过程表：营运资金计算表：损益表与现金流量表之间的桥梁利息计算表各类制造费用、管理费用的分摊表投资预算表：同时影响三大表，本身也是输出结果。中间计算表的构建方法第一部分：完成原始数据的引用第一部分：完成原始数据的引用,最好是通过公式最好是通过公式或链接动态的引用数据收集模板上的数据。或链接动态的引用数据收集模板上的数据。第二部分：完成数据的归纳和转换，通过公式和第二部分：完成数据的归纳和转换，通过公式和函数的设定完成，避免手动的调整。函数的设定完成，避免手动的调整。第三部分：对第二部分的数据按业务驱动因素进第三部分：对第二部分的数据按业务驱动因素进行分配、二次分配等操作。其结果以适合引用至行分配、二次分配等操作。其结果以适合引用至三大表的方式排列。三大表的方式排列。中间计算表总结加深对公司业务的理解。行业不同，从加深对公司业务的理解。行业不同，从收集上来的数据资料转化为报表资料的收集上来的数据资料转化为报表资料的过程也不同。过程也不同。将平时用于转化的报表格式固定下来，将平时用于转化的报表格式固定下来，并利用各类复合型函数和透视表实际自并利用各类复合型函数和透视表实际自动化的转换，以节约时间、提高效率。动化的转换，以节约时间、提高效率。4.3 三大表之间的联动联动的前提是对财务三大表之间联动的前提是对财务三大表之间 的内在勾稽联的内在勾稽联系要有深刻的理解，并能用公式量化的加以表达。系要有深刻的理解，并能用公式量化的加以表达。直接联动直接联动间接联动间接联动利润表与各表的勾稽关系u销量：收入表u销售收入：收入表u直接材料成本：直接材料表u采购运费：运费表u可变/固定制造费用：制造费用汇总表u质保费：报价表u折旧：折旧表u技术服务费：技术服务费表u专利权使用费：专利权使用费表u售后服务费：售后服务费表u管理费用：管理费用表u利息：利息表，现金流量表通过它来影响利润表利润表作为利润表作为最终的结果最终的结果输出表，完输出表，完全由链接和全由链接和公式组成，公式组成，不应有任何不应有任何输入数字输入数字资产负债表与各表的勾稽关系u以本年度12月31日的预计资产负债表为基础来编制u流动资产：现金流量表和营运资本表u固定资产：固定资产投资计划表u无形资产：无形资产计划表u流动负债：现金流量表和营运资本表u长期负债：现金流量表u股本：股东股益变动表u留存收益：利润表资产负债表资产负债表除了第一栏除了第一栏的上年度底的上年度底数字为基础数字为基础外，其他各外，其他各栏数字均为栏数字均为链结和公式链结和公式营运资金的预测n通过对应收帐款周转天数（DSO）、应付帐款周转天数（DPO）、存货周转天数（DTO）的预测，模拟出不同假设条件下的企业营运资金状况。n企业存在2.35亿的资金缺口，由于无法从客户和供应商处获得更优惠的付款条件，预计只能通过银行贷款解决，这将大大增加企业的利息费用。n尽管主要原材料的周转天数有望控制在25天的设定目标，但由于备品备件库需要大量的补充，这一计划将使09年的存货周转天数降至约35天。营运资金表营运资金表公司每1元钱营业收入竟然需要1.2元营运资金，应收、应付、存货哪个的帐期影响大？你能快速的完成敏感性分析吗？经典外企经典外企500强预算模板结构强预算模板结构年度预算模板年度预算模板参数参数/假设表假设表（输入）（输入）成本库成本库（输入）（输入）中间过程计算表中间过程计算表（输入（输入+计算）计算）三大表三大表（自动计算、输出）（自动计算、输出）价格模型价格模型参数假设表参数假设表参数假设表宏观经济参数宏观经济参数企业财务假设企业财务假设按成本驱动确定按成本驱动确定的分摊比例表的分摊比例表风险参数风险参数宏观经济参数u汇率u利率、贴息率u