|
Excel高效财务运用 目录 1. 财务报表设计 2. 应收账款账龄分析 3. 固定资产管理 4. 资金核算 5. 薪酬核算 6. 投资 7. 营运数据统计 8. 视觉化的财报设计 9. 成本分析与控制 10. 敏感性分析 11. 管理仪表盘 1 让报表变得美观易懂 封面与导航设计 封面：标题，logo与主题图片 导航：矩形+超链接 隐藏工作表标签 视觉差 数据的层次感与结构化 方式：字号，边框，反色 运用色彩 CI设计匹配 主色调 功能区标识与用户引导 1.2 规范表单中的数据 有效性 数据-数据有效性 控件 调出开发工具选项卡 Office按钮-excel选项-常用-显示开发工具选项卡 可以使用的控件 组合框 列表框 复选框 单选按钮 1.3 快速消除报表中的四舍五入差异 通常可以使用round函数设置四舍五入 Round(需要设置的单元格，需要设置的小数位数） 快速设置四舍五入效果的方法 将数据设置为带千分位的二位小数的格式 点击Office按钮-excel选项-高级 找到计算此工作簿时 把“将精度设为所显示的精度”勾选即可 2.1 应收账款过期天数计算 =Datedif(开始日期，结束日期，“y”) 第三个参数： “y”：表示年数 “m”：表示月数 “d”：表示天数 “ym”: 表示整年后余下的月份数 “md”: 表示整月后余下的天数 2.2 自定义账龄周期分析 1【插入】选项卡-选择【数据透视表】 2 在“字段列表”工作区中，将过期天数字段拖入行标签，将金额两次拖入数值区域 3 在透视表中的行标签区域内点击鼠标右键，在右键菜单中选择“组合”，将起始于，终止于，步长分别改为：1,120,30 4 光标放在“金额2”的列上右键，选择【值字段设置】，切换为“值显示方式”，并在列表中选择“占同列数据总和的百分比” 3.1 常用折旧函数 直线折旧法计算资产折旧 =SLN（Cost,Satvage,Life） 双倍余额递减折旧法计算资产折旧 =DDB（Cost,Satvage,Life,Year） 年数总和折旧法计算资产折旧 =SYD（Cost,Satvage,Life,Year） 3.2 固定资产查询模板 利用有效性和VLOOKUP函数创建。 选择菜单的制作。【数据】选项卡-【数据有效性】，选择“序列”，在来源的位置选择待选固定资产编号列表。如果该编号不在当前工作表上，excel2007需要为编号列表定义名称，并在来源框中输入“=该名称”来实现，而excel2010则可以直接跨不同工作表去选择待选列表。 3.3 利用vlookup进行查询 VLOOKUP在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。VLOOKUP 中的 V 代表垂直。 VLOOKUP(lookup_value, table_array, col_index_num, range_lookup) Lookup_value 为需要在数组第一列中查找的数值。Lookup_value 可以为数值、引用或文本字符串。也可以理解为：两表共有的索引字段。 Table_array 为需要在其中查找数据的数据表。可以使用对区域或区域名称的引用，例如数据库或列表。必须使得共有字段位于该范围的第一列。 col_ index_num 为需要调转的数据位于第二个参数中定义的范围的第几列。必须为单纯数值。 range_lookup 定义大致匹配或精确匹配。False或0:精确匹配；true或忽略或1；如果无法找到精确匹配的值，那么就查找并匹配比查找值小的最近似的值。 4. 资金核算 4.1 计算分期还款的偿还金额 年金：一系列的等额收支 Pmt(利率，期数，现值，[未来值]，[期初期末]) 4.2 计算分期还贷的本金与利息 本金函数：ppmt(利率，第几期，总期数，现值，[未来值]，[期初期末]) 利息函数：ipmt(利率，第几期，总期数，现值，[未来值]，[期初期末]) 5.1 工龄/年龄/账龄计算 运用透视表组合功能 按照数值大小分组 右键-组合 改变透视表汇总方式 求和 计数 右键-值字段设置-汇总方式 显示数据所占比例 值显示方式：占同列数据总和的百分比 右键-值字段设置-值显示方式 5.2 数据查询 利用vlookup查询信息 跨表查询必须有共有字段 第1个参数： 选择一个要查询其值的对应共有字段 第2个参数： 选择查询范围必须把共有字段作为第一列 模糊查询会匹配比查询值小的最大值 第4个参数设置为1 利用iferror屏蔽错误值 =Iferror(vlookup(),0) 工价查询 当需要根据多个字段进行查询时可以利用合并列 5.3 制作查询模板 利用有效性+vlookup 有效性 选择数据-数据有效性，选择“序列” 列表在当前工作表上可以直接选择 跨表引用列表需要首先为列表定义名称 有效性制作方法：=定义的名称 名称的定义 单元格与单元格区域的名称定义方法 选中单元格或区域，在名称框中输入名称+回车键 公式或常数的名称定义方法 在名称管理器里进行定义 5.4 薪酬核算 利用if自动计算所得税 =MAX((B7-3500)*{0.03,0.1,0.2,0.25,0.3,0.35,0.45}-{0,105,555,1005,2755,5505,13505},0) 自动计算加班费 =IF(ISERROR(VLOOKUP(D2,$L$2:$L$12,1,FALSE)),IF(OR(G2=6,G2=7),'周末加班','工作日加班'),'节假日加班') 隐藏错误提示 =iferror(vlookup()，0) =IF(ISERROR(表达式),'',表达式) 利用模糊查询计算奖金 Vlookup函数第4个参数为1时，是模糊查询，会匹配比目标值小的最近似值。利用该属性可以利用销售员的业绩匹配其所属的提成比例。 5.5 名称定义 名称可以代表一个单元格或者一个单元格区域，或者是常量，公式。 名称的定义：选中需要命名的单元格或区域，在界面左上角名称框中输入名称后回车； 需要注意的是：名称框只能用于定义单元格和单元格区域的名称，公式的名称需要在【公式】【名称管理器】中进行定义。 名称的引用：需要引用某单元格时输入为该单元格定义的名称：＝名称 步骤1：选中需要命名的某个单元格或单元格区域。 步骤2：在左上角名称框输入命名后回车。 删除名称：选择【公式】菜单【名称管理器】；选中需要删除的名称，点击“删除”按钮。 6.1 投资评估模板 评估方法 DCF（discounted cash flow，现金流量折现） 内容：预测未来的现金流量，并折现到现在 指标： 净现值（npv) 内部报酬率（irr) 模板制作 利用有效性制作折旧方法选择框 利用if函数计算折旧金额 6.2 公司估值 估值方法 对公司估值时，不是简单地把未来产生的现金流直接相加，而是选取一个恰当的贴现率，将未来的现金流贴现到现在，然后相加 贴现 未来的现金流需要使用一个贴现率（如银行利率），折算成今天值多少钱，这个折算的过程叫贴现 如何确定贴现率？ 一般使用WACC（加权平均资本成本）作为贴现率 WACC 英文WeightedAverage Cost of Capital的缩写。WACC代表公司整体平均资金成本，可用来衡量一个项目是否值得投资；项目的回报必须不低于WACC。 WACC=（债务/资本）*债务成本*（1-企业所得税税率）+（1-债务/资本）*股权成本 7 多维销售数据分析 数据表结构 数据列表 交叉表 改变透视表视图 利用字段列表工作区 为数据添加分组 工具选项卡-将所选内容分组 选中分组默认名称，输入新的名称可为其重命名 右键-分类汇总可为新的分组求和 对数据按日期进行分析 右键-组合，可将日期组合为年，季度，月等 7.1 营运数据统计 对业务数据进行多维动态分析 选择【插入】【数据透视表】，选择正确的数据范围。 根据分析目标将字段列表里的字段拖入报表筛选，行标签，列标签，数值4个相应区域内。 生成如下的透视表后，将鼠标悬停在想要移动的字段上，鼠标左键按下，将字段拖放到其他区域，生成自己需要的数据显示。 7.2 数据统计 统计变动费用与固定费用 Sumifs的使用 语法： =sumifs(求和区域，条件区域1，条件1，……) 条件区域与条件总是成对出现 在一个字段下取多个值作为条件的语法： Sum(sumifs(求和区域，条件区域，{条件值1，条件值2})) 用sum嵌套sumifs 同一字段下的多个值用{}括起来 计算毛利合计 =sumproduct(销售收入*毛利率） 7.3 多重合并 找到多重合并命令 Excel选项-自定义-所有命令，查找“数据透视表和数据透视图向导”，将其添加到快速工具条 利用多重合并汇总全年工资 点击数据透视表向导命令图标，选择“多重合并计算数据区域” 自定义页字段，选择需要合并的区域 将也字段数目改为1 为其定义名称 重复以上步骤，将所有需合并的区域设置完成即可生成合并后的透视表 可以为字段改名字：点击字段列表工作区中的字段，选择“字段设置”进行修改 改变透视表视图进行分析 7.4 添加计算项比较预算与实际数据 多重合并预算表与实际表 执行3.3的操作合并预算与实际两个表 添加计算项 将预算与实际的上级字段改名为版本 将版本字段放入列标签 将光标放置在版本字段名上，选择透视表工具-选项-公式-计算项 将计算项名称定义为“差异”，公式内容为：=预算-实际，然后点击添加按钮，即可添加差异计算项 去掉合计列 右键-透视表选项-汇总和筛选，取消勾选“显示行总计” 8 视觉化的财报分析 形象展示数据 更容易理解数据关系 是数据分析的重要组成部分 图表分类 静态图表与动态图表 饼图：构成比例关系 折线：趋势变化 柱形：时间序列上的大小比较 条形：非时间序列的大小比较 微型图表：利用函数或条件格式制作，节省报表空间 复合图表：多个图表类型组合 8.1 商业杂志图表制作-1 商业杂志图表解析 类型 色彩 结构 标题 特效 图表类型的选择 选择简单的图表类型 使用主题颜色 页面布局-主题-颜色，选择一种主题颜色 8.2 商业杂志图表制作-2 图表制作原则 信噪比：简洁，重点突出 去掉无关紧要的设计元素 自定义图表色彩 使用colorpix软件获取色彩的RGB值 选中图表元素，右键-设置格式，设置该图表元素的色彩 快速创建图表 将设计好的图表存成模板 图表工具-设计-另存为模板 调用：选择图表类型时，在顶部有“模板”类别，在其中选择自定义的图表模板即可 8.3 指数化图表 应用场景 数据系列的数量级差异较大 制作指数表 将第一期数据设为100 后面期间的数据=数据源当期数据/第一期数据*100 利用指数表数据制作图表 8.4 双坐标图表 使用场景 数据系列差异较大 制作方法 制作二维簇状柱形图 选择图表工具-选项-图表元素选择框，在其中选择数量级小的数据系列 选择“设置所选内容格式”命令 将系列绘制在选项改为“次坐标轴” 8.5 复合饼图 选择复合饼图图表类型 如果出现数据点不在第二绘图区的情况 选中整个数据系列 右键-设置数据系列格式 将第二绘图区包含最后一个值的数值改变成正确的即可 删除图例 添加数据标签 图表工具-布局-数据标签-其他数据标签选项 勾选“类别名称”和“百分比”，将“值”的勾取消 8.6 动态图表 控件的制作 绘制控件 设置数据源区域 设置单元格链接 写查询函数 常用查询函数：index,vlookup,offset,indirect,if Index函数的语法： Index(在哪找，找第几行的数，找第几列的数） 把index函数的位置参数与控件的单元格链接关联 制作图表 把图表与控件摆放好位置或组合在一起 8.7 视觉化的财报分析 制作图表的信噪比原则 我们在做设计的时候，要考虑的不是还能添加一些什么元素，而是要考虑还能不能再减少一些什么元素。 将满意的图表存为模板 如果希望将制作好的图表保存下来供今后调用，选择【另存为模版】；调用时，选择【更改图表类型】，在【模版】类别中选择自己的模版即可。 9.1 控件的使用-1 调出开发工具选项卡：Office按钮-excel选项-选择“在功能区显示开发工具选项卡” 创建组合框控件：开发工具-控件-插入-表单控件，选择组合框控件，按住鼠标左键向右拖拽，绘制出一个控件形状 设置控件：右键点击控件-设置控件格式-控制，设置数据源区域和单元格链接 9.2 控件的使用-2 组合框 开发工具-插入-表单控件 右键-设置控件格式-控制 设置数据源区域 设置单元格链接 单选按钮 开发工具-插入-表单控件，绘制出第一个按钮后，同时按下ctrl+shift+拖拽，复制出其他按钮 修改按钮文字 右键-设置控件格式-控制 设置数据源区域 设置单元格链接 9.3 控件的使用-3 复选框 开发工具-插入-表单控件 右键-设置控件格式-控制 设置单元格链接 按下ctrl+shift+拖拽，复制其他的复选框，并为其他的复选框分别设置其单元格链接 滚动条 开发工具-插入-表单控件 右键-设置控件格式-控制 设置单元格链接 利用公式与单元格链接控制计算表 9.4 动态费用构成比例 首选创建一个组合框控件，并设置其数据源区域和单元格链接 设置其单元格链接为a1单元格 写一个index函数 =index(需要查询的数据范围，a1） 其中，数据范围仅选择数据所在的那一列即可 其第2个参数设置为a1, 使得控件与index查询结果产生联动机制 复制公式到其他列 利用index查询出的数据制作饼图 删除饼图的图例，添加数据标签 图表工具-布局-数据标签-其他数据标签选项，选中类别名称和百分比，将“值”选项去掉 9.5 费用测算与控制 创建模拟运算表进行费用测算 创建费用参数的假设值 创建关联公式到需测算的指标 数据-假设分析-数据表 一个假设值且按列排列：设置“输入引用列的单元格”，选择的单元格为该假设值在计算表中的位置 一个假设值且按行排列：设置“输入引用行的单元格”，选择的单元格为该假设值在计算表中的位置 两个假设值：同时设置“输入引用行的单元格”和“输入引用列的单元格” 9.6 费用与产量相关性分析 相关系数 =correl(费用，产量） 图表方法 绘制折线图 图表工具-布局-图表元素选择框，选择系列“销量” 设置所选内容格式-系列绘制在改为“次坐标轴” 10 敏感性分析 模拟运算表概念 其实是把若干个相同公式的输入生成对应的值简化成 一个公式的输入产生对应的值。 敏感性分析概念 研究和制约利润的有关因素发生某种变化时，利润变化程度的一种分析方法。那些对利润影响大的因素称为敏感因素，反之，称为非敏感因素 一般情况下，影响利润的因素有4个：价格、单位变动成本、销售量和固定成本 敏感系数 敏感系数是反映敏感程度的指标 某因素的敏感系数＝利润变化(％)／该因素变化(％) 10.1 敏感性分析判断标准 敏感系数的绝对值＞1，即当某影响因素发生变化时，利润发生更大程度的变化，该影响因素为敏感因素 敏感系数的绝对值＜1，即利润变化的幅度小于影响因素变化的幅度，该因素为非敏感因素 敏感系数的绝对值＝1，即影响因素变化合导致利润相同程度的变化，该因素亦为非敏感因素 10.2 敏感性分析 设计利润计算表格 利润=（单价-单位变动成本）*销售量-固定成本 为影响利润的几个因素分别设置滚动条 当前值设置为50 单元格链接选择滚动条所在的单元格 利用控件改变预测值 正反向变动1%的公式：=（单元格链接/50-1)/2 正反向变动2%的公式：=单元格链接/50-1 10.3 计算盈亏临界点 创建计算内部报酬率的表格 现金流量表 内部报酬率=Irr(现金流量） 调用单变量求解工具 数据-假设分析-单变量求解 目标单元格：内部报酬率所在的单元格 目标值：0 可变单元格：要计算盈亏临界点的利润影响因素 11 管理仪表盘 仪表盘概念 集中展示 图表化展示 优点 操作简单 便于监控、评估、判断 制作 前台与后台 移动图表 指标分类 袁志刚 电子邮箱： feiren999@qq.com 微信公众号：yuanzhigang8848 新浪博客： http://blog.sina.com.cn/yuanzhigang66 新浪微博： http://weibo.com/1605249383 |
|
|
