EXCEL在财务管理中的应用.ppt

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