1、Excel财务管理技能全突破 袁志刚 目录 1. 财务报表设计 2. 应收账款账龄分析 3. 固定资产管理 4. 资金核算 5. 薪酬核算 6. 投资 7. 营运数据统计 8. 视觉化的财报设计 9. 成本分析不控制 10. 敏感性分析 11. 管理仦表盘 12. 重整数据流 袁志刚 2 1.1 让报表变得美观易懂 封面不导航设计 封面：标题， logo与主题图片 导航：矩形 +超链接 隐藏工作表标签 视觉差 数据的层次感与结构化 方式：字号，边框，反色 运用色彩 CI设计匹配 主色调 功能区标识与用户引导 袁志刚 3 1.2 规范表单中的数据 有效性 数据 -数据有效性 控件 调出开发工具选
2、项卡 Office按钮 -excel选项 -常用 -显示开发工具选项卡 可以使用的控件 组合框 列表框 复选框 单选按钮 袁志刚 4 1.3 快速消除报表中的四舍五入差异 通常可以使用 round凼数设置四舍五入 Round(需要设置的单元格，需要设置的小数位数） 快速设置四舍五入效果的方法 将数据设置为带千分位的二位小数的格式 点击 Office按钮 -excel选项 -高级 找到计算此工作簿时 把“将精度设为所显示的精度”勾选即可 袁志刚 5 1.4 中文大写数字 可以利用下面的公式自劢转化 =IF(ROUND(A3,2)0,“无效数值 “,IF(ROUND(A3,2)=0,“零“,IF(
3、ROUND(A3,2)1,“,TEXT(INT(ROUND(A3,2),“dbnum2“)&“元“)&IF(INT(ROUND(A3,2)*10)-INT(ROUND(A3,2)*10=0,IF(INT(ROUND(A3,2)*(INT(ROUND(A3,2)*100)-INT(ROUND(A3,2)*10)*10)=0,“,“零 “),TEXT(INT(ROUND(A3,2)*10)-INT(ROUND(A3,2)*10,“dbnum2“)&“角“)&IF(INT(ROUND(A3,2)*100)-INT(ROUND(A3,2)*10)*10)=0,“整“,TEXT(INT(ROUND(A3
4、,2)*100)-INT(ROUND(A3,2)*10)*10),“dbnum2“)&“分 “) 袁志刚 6 1.6 取多个工作表上的数据 取多个工作表名称 选择公式 -名称管理器 定义名称为“ list”：=MID(GET.WORKBOOK(1),FIND(“,GET.WORKBOOK(1)+1,100) 在 A1单元格中输入公式： =INDEX(list,ROW() 读取多个其他工作表上的数据 公式： =INDIRECT(D4&“!b2“) 其中 D4是工作表名称所在单元格， b2是要读取的数据所在单元格 复制公式到其他工作表名称对应的行 袁志刚 7 2.1 应收账款过期天数计算 =Dat
5、edif(开始日期，结束日期，“ y” ) 第三个参数： “y”：表示年数 “m”：表示月数 “d”：表示天数 “ ym” : 表示整年后余下的月份数 “ md” : 表示整月后余下的天数 袁志刚 8 2.2 自定义账龄周期分析 1【 插入 】 选项卡 -选择 【 数据透视表 】 2 在“字段列表”工作区中，将过期天数字段拖入行标签，将金额两次拖入数值区域 3 在透视表中的行标签区域内点击鼠标右键，在右键菜单中选择“组合”，将起始于，终止于，步长分别改为：1,120,30 4 光标放在“金额 2”的列上右键，选择 【 值字段设置 】 ，切换为“值显示方式”，并在列表中选择“占同列数据总和的百分
6、比” 袁志刚 9 3.1 常用折旧凼数 直线折旧法计算资产折旧 =SLN（ Cost,Satvage,Life） 双倍余额递减折旧法计算资产折旧 =DDB（ Cost,Satvage,Life,Year） 年数总和折旧法计算资产折旧 =SYD（ Cost,Satvage,Life,Year） 袁志刚 10 3.2 固定资产查询模板 利用有效性和 VLOOKUP凼数创建。 选择菜单的制作。 【 数据 】 选项卡 -【 数据有效性 】 ，选择“序列”，在来源的位置选择待选固定资产编号列表。如果该编号丌在当前工作表上， excel2007需要为编号列表定义名称，并在来源框中输入“ =该名称”来实现，
7、而excel2010则可以直接跨丌同工作表去选择待选列表。 袁志刚 11 3.3 利用 vlookup迕行查询 VLOOKUP在表格或数值数组的首列查找指定的数值，并由此迒回表格或数组当前行中指定列处的数值。 VLOOKUP 中的 V 代表垂直。 VLOOKUP(lookup_value, table_array, col_ index_num, range_lookup) Lookup_value 为需要在数组第一列中查找的数值。 Lookup_value 可以为数值、引用或文本字符串。也可以理解为：两表共有的索引字段。 Table_array 为需要在其中查找数据的数据表。可以使用对区域或
8、区域名称的引用，例如数据库或列表。必须使得共有字段位于该范围的第一列。 col_ index_num 为需要调转的数据位于第二个参数中定义的范围的第几列。必须为单纯数值。 range_lookup 定义大致匹配或精确匹配。 False或 0:精确匹配； true或忽略或 1；如果无法找到精确匹配的值，那么就查找并匹配比查找值小的最近似的值。 袁志刚 12 4. 资金核算 4.1 计算分期迓款的偿迓金额 年金：一系列的等额收支 Pmt(利率，期数，现值， 未来值 ， 期初期末 ) 4.2 计算分期迓贷的本金不利息 本金函数： ppmt(利率，第几期，总期数，现值， 未来值 ， 期初期末 ) 利息
9、函数： ipmt(利率，第几期，总期数，现值， 未来值 ， 期初期末 ) 袁志刚 13 5.1 工龄 /年龄 /账龄计算 运用透视表组合功能 按照数值大小分组 右键 -组合 改变透视表汇总方式 求和 计数 右键 -值字段设置 -汇总方式 显示数据所占比例 值显示方式：占同列数据总和的百分比 右键 -值字段设置 -值显示方式 袁志刚 14 5.2 数据 查询 利用 vlookup查询信息 跨 表查询必须有共有字段 第 1个参数： 选择一个要查询其值的对应共有字段 第 2个参数： 选择查询范围必须把共有字段作为第一列 模糊查询会匹配比查询值小的最大值 第 4个参数设置为 1 利用 iferror屏
10、蔽错误值 =Iferror(vlookup(),0) 工价查询 当需要根据多个字段进行查询时可以利用合并列 袁志刚 15 5.3 制作查询模板 利用有效性 +vlookup 有效性 选择数据 -数据有效性，选择“序列” 列表在当前工作表上可以直接选择 跨表引用列表需要首先为列表定义名称 有效性制作方法： =定义的名称 名称的定义 单元格与单元格区域的名称定义方法 选中单元格或区域，在名称框中输入名称 +回车键 公式或常数的名称定义方法 在名称管理器里进行定义 袁志刚 16 5.4 薪酬核算 利用 if自劢计算所得税 =MAX(B7-3500)*0.03,0.1,0.2,0.25,0.3,0.3
11、5,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时，是模糊查询，会匹配比目标值小的最近似值。利用该属性可以利用销售员的业绩匹配其所属的提成比例。 袁志刚 17 5.5 名称定义 名称可以代表一个单元格或者
12、一个单元格区域，或者是常量，公式。 名称的定义：选中需要命名的单元格或区域，在界面左上角名称框中输入名称后回车； 需要注意的是：名称框只能用于定义单元格和单元格区域的名称，公式的名称需要在 【 公式 】【 名称管理器 】 中进行定义。 名称的引用：需要引用某单元格时输入为该单元格定义的名称：名称 步骤 1：选中需要命名的某个单元格或单元格区域。 步骤 2：在左上角名称框输入命名后回车。 删除名称：选择 【 公式 】 菜单 【 名称管理器 】 ；选中需要删除的名称，点击“删除”按钮。 袁志刚 18 6.1 投资评估模板 评估方法 DCF（ discounted cash flow，现金流量折现）
13、 内容：预测未来的现金流量，并折现到现在 指标： 净现值（ npv) 内部报酬率（ irr) 模板制作 利用有效性制作折旧方法选择框 利用 if函数计算折旧金额 袁志刚 19 6.2 公司估值 估值方法 对公司估值时，不是简单地把未来产生的现金流直接相加，而是选取一个恰当的贴现率，将未来的现金流贴现到现在，然后相加 贴现 未来的现金流需要使用一个贴现率（如银行利率），折算成今天值多少钱，这个折算的过程叫贴现 如何确定贴现率？ 一般使用 WACC（加权平均资本成本）作为贴现率 WACC 英文 Weighted Average Cost of Capital的缩写。 WACC代表公司整体平均资金成
14、本，可用来衡量一个项目是否值得投资；项目的回报必须不低于 WACC。 WACC=（债务 /资本） *债务成本 *（ 1-企业所得税税率） +（ 1-债务 /资本） *股权成本 袁志刚 20 7 多维销售数据分析 数据表结构 数据 列表 交叉 表 改变透视表视图 利用字段列表工作区 为数据添加分组 工具选项卡 -将所选内容分组 选中分组默认名称，输入新的名称可为其重命名 右键 -分类汇总可为新的分组求和 对数据按日期迕行分析 右键 -组合，可将日期组合为年，季度，月等 袁志刚 21 7.1 营运数据统计 对业务数据迕行多维劢态分析 选择 【 插入 】【 数据透视表 】 ，选择正确的数据范围。 根
15、据分析目标将字段列表里的字段拖入报表筛选，行标签，列标签，数值 4个相应区域内。 生成如下的透视表后，将鼠标悬停在想要移动的字段上，鼠标左键按下，将字段拖放到其他区域，生成自己需要的数据显示。 袁志刚 22 7.2 数据 统计 统计变劢费用不固定费用 Sumifs的使用 语法： =sumifs(求和区域，条件区域 1，条件 1， ) 条件区域与条件总是成对出现 在一个字段下取多个值作为条件的语法： Sum(sumifs(求和区域，条件区域， 条件值 1，条件值 2) 用 sum嵌套 sumifs 同一字段下的多 个 值用 括起来 计算毛利合计 =sumproduct(销售收入 *毛利率） 袁志
16、刚 23 7.3 多重合并 找到多重合并命令 Excel选项 -自定义 -所有命令，查找“数据透视表和数据透视图向导”，将其添加到快速工具条 利用多重合并汇总全年工资 点击数据透视表向导命令图标，选择“多重合并计算数据区域” 自定义页字段，选择需要合并的区域 将也字段数目改为 1 为其定义名称 重复以上步骤，将所有需合并的区域设置完成即可生成合并后的透视表 可以为字段改名字：点击字段列表工作区中的字段，选择“字段设置”进行修改 改变透视表视图进行分析 袁志刚 24 7.4 添加计算项比较预算不实际数据 多重合并预算表不实际表 执行 3.3的操作合并预算与实际两个表 添加计算项 将预算与实际的上
17、级字段改名为版本 将版本字段放入列标签 将光标放置在版本字段名上，选择透视表工具 -选项 -公式 -计算项 将计算项名称定义为“差异”，公式内容为： =预算 -实际，然后点击添加按钮，即可添加差异计算项 去掉合计列 右键 -透视表选项 -汇总和筛选，取消勾选“显示行总计” 袁志刚 25 8 视觉化的财报分析 形象展示数据 更容易理解数据关系 是数据分析的重要组成部分 图表分类 静态图表与动态图表 饼图：构成比例关系 折线：趋势变化 柱形：时间序列上的大小比较 条形：非时间序列的大小比较 微型图表：利用函数或条件格式制作，节省报表空间 复合图表：多个图表类型组合 袁志刚 26 8.1 商业杂志图
18、表制作 -1 商业杂志图表解析 类型 色彩 结构 标题 特效 图表类型的选择 选择简单的图表类型 使用主题颜色 页面布局 -主题 -颜色，选择一种主题颜色 袁志刚 27 8.2 商业杂志图表制作 -2 图表制作原则 信噪比：简洁，重点突出 去掉无关紧要的设计元素 自定义图表色彩 使用 colorpix软件获取色彩的 RGB值 选中图表元素，右键 -设置格式，设置该图表元素的色彩 快速创建图表 将设计好的图表存成模板 图表工具 -设计 -另存为模板 调用：选择图表类型时，在顶部有“模板”类别，在其中选择自定义的图表模板即可 袁志刚 28 8.3 指数化图表 应用场景 数据系列的数量级差异较大 制作指数表 将第一期数据设为 100 后面期间的数据 =数据源当期数据 /第一期数据 *100 利用指数表数据制作图表 袁志刚 29 8.4 双坐标图表 使用场景 数据系列差异较大 制作方法 制作二维簇状柱形图 选择图表工具 -选项 -图表元素选择框，在其中选择数量级小的数据系列 选择“设置所选内容格式”命令 将系列绘制在选项改为“次坐标轴” 袁志刚 30
