Excel财务应用2015讲义.ppt

Excel高效财务数据分析及财务管理应用 袁志刚2015 9湖北武汉 袁志刚 目录 销售管理与分析进销存管理财务模型分析薪酬计算与分析费用分析预算管理融资分析往来分析报表设计链接数据库 2 袁志刚 1 1销售周报分析 计算周次函数Weeknum weeknum 日期 2 2表示一周从星期一开始根据周次统计收入Sumifs函数多条件求和函数 与之同类的还有countifs averageifs除了sumifs sumproduct函数也可用于多条件求和 sumifs 求和区域 条件区域1 条件1 条件区域2 条件2 制作折线图趋势分析 3 袁志刚 1 2客户分布分析 Frequency函数自定义统计区间设定参照值 取区间最小值作为参照值选中与统计区间数量相同的连续单元格输入frequency函数 frequency 数据区域 参照值区域 同时按下ctrl shift enter键最后按下的组合键将创建一个数组公式不可以单独删除数组公式中的任一公式 需要全选后才可删除构成比例分析绘制饼图设置数据标签 4 袁志刚 1 3客户数变动分析 生成透视表选择日期放入行区域右键 创建组 选择年在透视表外输入公式计算客户增长的比例制作图表光标放在透视表内 点击数据透视表工具 选项 数据透视图生成透视图光标放在客户增长率表内 选择 插入 折线图客户增量分析创建透视表 将日期放入行区域 将销量字段放入数值区域右键 创建组 选择年和月光标放在销量列 邮件 值汇总依据 选择计数数据透视表工具 选项 数据透视图 生成折线图 5 袁志刚 1 4基于地图的数据分析 作用形象展示基于地理位置的区域数据分析 容易理解 印象深刻 效果出众 步骤使用地图矢量图 为每一个区域定义名称 一般是该区域名称的拼音 选中区域 在名称框内输入即可 输入宏 开发工具 visualbasic 双击thisworkbook 在右侧代码窗口输入以下代码 Subuser click region name ActiveSheet Shapes Range m1 Value Fill ForeColor SchemeColor xlThemeColorDark1Range m1 Value region nameActiveSheet Shapes region name Fill ForeColor SchemeColor xlThemeColorAccent6EndSub 6 袁志刚 1 4基于地图的数据分析 步骤为每个区域图形指定宏 选中该区域图形 右键 指定宏 输入以下宏名称 thisworkbook user click 区域名称 其中 区域名称为为区域图形定义的名称在m1单元格中输入一个区域的名称代码 比如北京地区的代码为beijing 此时即可点击区域实现变色效果 准备好原始数据写一个vlookup函数对地图上选中的区域数据进行查询 该查询的索引字段为m1单元格 该单元格可以任意指定 根据查询结果制作图表 7 袁志刚 1 5基于地图的色阶分析 步骤准备地图矢量文件为每一个区域图形定义名称 选中图形 在名称框内输入名称 名称设为该区域的拼音为数据设置区间 假设分为以下5个区间 为上面5种色彩所在单元格定义名称 分别设为code1 5为这5个区间设置参照值和颜色代码 8 袁志刚 1 5基于地图的色阶分析 步骤为下图中的3个单元格定义名称 分别为province vbdata vbcode 其中数据和颜色代码为vlookup查询公式 根据省份在数据表中查询数据和所属颜色代码制作控件按钮 开发工具 控件 插入 按钮 指定宏 新建 输入以下宏代码 Sub按钮35 单击 Fori 3To34Range province Value Range data a i ValueActiveSheet Shapes Range province Value SelectSelection ShapeRange Fill ForeColor RGB Range Range vbcode Value Interior ColorNextiRange f8 SelectEndSub 9 袁志刚 1 6复合增长率 概念CAGR CompoundAnnualGrowthRate 一个指标 比如销售收入或净利润 投资回报等 在特定时期内的年度增长率是较长时期内的测算 忽略个别年度的波动 将增长率平滑 反映指标的整体表现 公式 当前数值 基期数值 1 年数 1 10 袁志刚 1 7波士顿矩阵分析 简介波士顿矩阵 BCGMatrix 又称市场增长率 相对市场份额矩阵 是由美国著名的管理学家 波士顿咨询公司创始人布鲁斯 亨德森于20世纪60年代末期首创的 布鲁斯认为决定产品 业务结构的要素可分为2类 市场吸引力与企业实力 在反映市场引力的众多指标 销售增长率 目标市场容量 竞争对手强弱及利润高低 中 销售增长率是最具代表性的综合指标 而在反映企业实力的指标 如市场占有率 技术 设备 资金利用能力中 市场占有率是最能直接显示出企业竞争实力的指标 因此 波士顿矩阵选取的纵坐标与横坐标分别是 销售增长率 及 市场占有率 由以上2个因素相互作用 产生4个不同的象限 划分出4类性质的产品 业务 销售增长率和市场占有率都较高的产品 业务 简称 明星 销售增长率和市场占有率都较低的产品 业务 简称 瘦狗 销售增长率高而市场占有率低的产品 业务 简称 问号 销售增长率低而市场占有率高的产品 业务 简 现金牛 11 问题型业务 现金型业务 瘦狗型业务 明星型业务 高 高 低 低 市场增长 市场份额 1 7波士顿矩阵分析 袁志刚 1 7波士顿矩阵分析 利用散点图制作波士顿矩阵选中表格的数据列 制作散点图 行坐标为市场占有率 纵坐标为增长率选中行坐标轴 设置坐标轴格式 勾选逆序刻度值 将纵坐标交叉选项改为坐标轴值 0 5 将刻度线类型与坐标轴标签设为无选中纵坐标轴 除了不勾选逆序刻度值 其他设置与行坐标轴一样Excel散点图的一个问题是无法为数据添加标签 可以使用一个第三方程序解决 xychartlabels安装此程序后excel会出现一个新的选项卡xychartlabels 选择其中的addlabels命令在其中的selectalabelrange选项中 选择数据表中的表前列即可最后可为坐标轴添加标签或为4个象限添加图片说明 13 袁志刚 2 1存货ABC管理 概念又称巴雷特分析法 按照价值和库存数量的高低 依据一定的分类标准 确定关键的少数和次要的多数 其分清主次 抓住重点的思想广泛应用于存货管理与成本管理 A类物资是指品种少 实物量少而价值高的物资 其成本金额约占70 而实物量不超过20 C类物资是指品种多 实物量多而价值低的物资 其成本金额约占10 而实物量不低于50 B类物资介于A类 C类物资之间 其成本金额约占20 而实物量不超过30 管理表制作步骤计算存货金额及数量所占比例 并按金额降序排列计算存货金额和数量所占累计比例 14 袁志刚 2 1存货ABC管理 管理表制作步骤依据一定标准 进行ABC分类 一般将关键的少数作为A类 次要的多数作为B和C类 对A类存货进行重点管理 制作帕累托图以意大利经济学家pareto命名选择存货名称 金额 累计金额百分比3列制作柱形图选择图表工具 布局 左上角点开图表元素选择框 在其中选择累计金额百分比点击设置所选内容格式按钮 将 系列绘制在 选项由 主坐标轴 改为 次坐标轴 将累计金额百分比数据系列的图表类型改为折线图选中副坐标轴 将最大值改为1选中金额数据系列 右键 设置数据系列格式 将分类间距改为0 15 袁志刚 16 2 2进销存表设计 表 功能表格与数据区域表内的数据可以独立和更方便的进行管理 比如自动扩展数据与公式 添加汇总行 筛选 应用表格格式等 创建与编辑将光标放在表中 选择 插入 表格通过顶部的表格工具 可以对表格进行各种编辑为表格更换样式 表格工具 表格样式 点击即应用新的样式将表格转化为区域 表格工具 转化为区域在表格最后一行下面输入数据 即可自动扩展表格范围 16 袁志刚 2 3进销存管理 数据表设计进出字段用if函数控制显示与否 if b2 c2 Vlookup查询产品价格 vlookup 产品名称 基础信息表 价格所在的列 查询方式 统计库存创建透视表 行设置为产品 列设置为 进出 数值为数量数据透视表工具 域 项目和集 计算项输入名称 库存 公式设置为 进货 发货标识低于安全库存的存货 开始 条件格式 新建规则 突出显示单元格规则 17 袁志刚 3 1杠杆平衡模型分析 经营杠杆 边际贡献 边际贡献 固定成本 假设经营杠杆系数为2 则表明销量增长10 息税前利润增长 2 10 20 即高度的经营杠杆 表示销货量的小幅变动 会引起利润的较大变动 一家公司的固定成本愈高 其企业风险也愈高 故有大量固定成本的公司 即具有较高的营业杠杆 企业一般可通过增加销售额 降低单位变动成本和固定成本等措施来降低经营杠杆和经营风险 财务杠杆是指由于债务的存在而导致每股利润的变动大于息税前利润变动的杠杆效应 财务杠杆系数 息税前利润 息税前利润 资本总额 负债比例 利率 18 袁志刚 3 1杠杆平衡模型分析 复合杠杆复合杠杆是指由于固定成本和固定财务费用的存在而导致的普通股每股利润变动率大于产销量变动率的杠杆效应 复合杠杆系数 经营杠杆系数 财务杠杆系数杠杆平衡模型当其他因素变动时 测算销量的变动文件 选项 自定义功能区 在主选项卡勾选 开发工具 开发工具 控件 插入 选择 数值调节钮 为变量添加调节钮控件右键选择数值调节钮 设置控件格式 设置单元格链接 并使用链接公式使得该单元格链接可以控制变量值 19 袁志刚 3 2盈亏平衡分析 分析内容销量平衡分析单价平衡分析变动成本平衡分析固定成本平衡分析控件应用开发工具 控件 插入 滚动条右键 单元格链接输入变量计算公式 当前值 1 单元格链接 50 1 2 此公式使得变量以每次1 的幅度变化 20 袁志刚 4 1加班计算 返回星期几 Weekday 日期 2 判断加班类型 IF ISERROR VLOOKUP B4 J 2 J 11 1 FALSE IF OR E4 6 E4 7 周末加班 工作日加班 节假日加班 提取不重复姓名 INDEX A 2 A 32 MATCH 0 COUNTIF M 1 M1 A 2 A 32 0 统计加班合计数 SUMPRODUCT A 2 A 32 M2 H 2 H 32 21 袁志刚 4 2薪酬计算与分析 奖金匹配hlookup 水平查询设置奖金比例表 其中的参照值应该取区间的最小值 hlookup 某销售员奖金 奖金比例表 比例所在列 查询方式 其中查询方式应该设置为1 意为模糊查询 工资变动分析将工资与销售收入数据转化为指数 即基期为100 其他期间为 当期数据 基期数据 100制作折线图计算工资与销售收入相关性 correl 工资 销售收入 计算工资与销售收入各自的复合增长率 当期 基期 1 年数 1 22 袁志刚 5 1折旧费用计算 已经计提折旧月份计算 datedif 启用资产日期 today m 其中m表示月份 还可以是y d 分别表示年 天 折旧额计算直线法 sln 原值 残值 使用年限 双倍余额递减法 ddb 原值 残值 使用年限 第几期 年数总和法 syd 原值 残值 使用年限 第几期 23 袁志刚 5 2混合成本分解 制作散点图添加趋势线右键 添加趋势线 选择线性勾选 显示公式 和 显示R平方值 选项公式Y 7 85x 6 其中7 85为变动成本 6为固定成本函数方式也可使用函数计算变动成本与固定成本变动成本 slope 成本 产量 固定成本 intercept 成本 产量 24 袁志刚 5 3动态费用查询 制作控件开发工具 控件 插入 制作组合框和列表框右键 设置控件格式 为其设置数据源区域和单元格链接写公式 将单元格链接里的序号变为具体值 index 参数列表 单元格链接 写公式查询数据 SUMIFS OFFSET 数据 B 3 B 42 0 参数 C 1 数据 B 3 B 42 参数 E 2 数据 A 3 A 42 统计 E8 请参照案例 绘制饼图显示结果 25 袁志刚 5 4拆分科目与余额 分析没有规律 不在同样的位置 没有统一的分隔符不能使用分列工具公式 MID A2 LENB A2 LEN A2 1 LEN A2 Len函数用于返回字符个数 一个汉字作为一个字符Lenb中 一个汉字作为2个字符Mid函数用于分拆文本 mid 需拆分单元格 从第几位拆分 拆分长度 26 袁志刚 5 5费用与产量相关性分析 相关系数 correl 费用 产量 图表方法绘制折线图图表工具 布局 图表元素选择框 选择系列 销量 设置所选内容格式 系列绘制在改为 次坐标轴 27 袁志刚 6 1利润测算 单变量求解数据 模拟分析 单变量求解目标单元格设置为利润所在的单元格目标值设置为目标利润可变单元格设置为要求解的变量 比如销量或价格等点击确定即可看到计算结果 28 袁志刚 6 2添加计算项比较预算与实际数据 多重合并预算表与实际表合并预算与实际两个表添加计算项将预算与实际的上级字段改名为版本将版本字段放入列标签将光标放置在版本字段名上 选择透视表工具 选项 公式 计算项将计算项名称定义为 差异 公式内容为 预算 实际 然后点击添加按钮 即可添加差异计算项去掉合计列右键 透视表选项 汇总和筛选 取消勾选 显示行总计 29 袁志刚 6 3预算模板制作 思路预算制作 跟踪预算 比较分析模板制作色彩的运用 区分和强调 引导作用控件的使用 选择要显示的不同的数据内容图表 直观的展示导航 便利的操作名称的运用 高效的引用 30 袁志刚 7 1加权平均资金成本 加权平均资金成本WACC weightedaveragecostofcapital WACC 债务 资本 债务成本 1 企业所得税税率 1 债务 资本 股权成本控件应用开发工具 控件 滚动条右键 设置控件格式 单元格链接创建公式关联变量与单元格链接Sumproduct用于计算乘积之和 也可以用于计算多条件求和与计数 sumproduct 区域1 区域2 31 袁志刚 7 2贷款还款计算 等额分期还款方式每期偿还金额 pmt 利率 期数 贷款金额 每期偿还的利息 ipmt 利率 第几期 期数 贷款金额 每期偿还的本金 ppmt 利率 第几期 期数 贷款金额 计算利率 rate 期数 每期还款额 贷款额 名义利率与实际利率将名义利率转化为实际利率 effect 名义利率 每年复利期数 将实际利率转化为名义利率 nominal 实际利率 一年内计息次数 32 袁志刚 8 1银行存款余额调节表 思路利用vlookup函数在银行对账单和银行存款日记账之间双向查询 出现 N A即未达账项 然后利用sumproduct函数将未达账项分类求合计 公式银行已收 企业未收款 SUMPRODUCT ISERROR D 3 D 19 TRUE A 3 A 19 0 A 3 A 19 其中 iserror true意味着企业未达 a3 a19 0意味着是企业未收款对账单上的其他项目调节公式以此类推 33 袁志刚 8 2应收账款管理 统计收款金额合计 sumifs 合同金额列 合同编号列 要统计的合同编号 分客户的账龄分析创建透视表 账龄字段放进行区域 金额放进数值区域光标在行标签区域 右键 创建组 将参数设为1 90 30将账龄字段拖进列标签将客户字段拖进行标签此时可以看到每家客户的应收账款分布汇总情况还可双击希望查看明细的行 对明细数据进行查看 34 袁志刚 8 3应收款账龄分析 1 插入 选项卡 选择 数据透视表 2在 字段列表 工作区中 将过期天数字段拖入行标签 将金额两次拖入数值区域3在透视表中的行标签区域内点击鼠标右键 在右键菜单中选择 组合 将起始于 终止于 步长分别改为 1 120 304光标放在 金额2 的列上右键 选择 值字段设置 切换为 值显示方式 并在列表中选择 占同列数据总和的百分比 35 袁志刚 8 4应付账款提醒 思路利用条件格式进行应付款提醒步骤计算到期天数 付款日期 today 选中到期天数列 开始 条件格式 新建规则 只为包含以下内容的单元格设置格式设置单元格值 0 为单元格设置红色填充色设置单元格值 10 为单元格设置蓝色填充色 36 袁志刚 9 1报销单制作 为分公司 部门 币种 支付类型等信息设置有效性在参数表上创建部门列表数据 数据有效性设置有效性条件为 序列 然后在下方的来源框选择参数表上的部门列表提示 excel2010之前的版本不能跨表选择有效性序列 需要为首先为序列定义名称设置填写提示信息数据 数据有效性 切换到 输入信息 标签 填写标题和输入信息保护选中需要填写的单元格 右键 设置单元格格式 保护 取消勾选 锁定 审阅 保护工作表 输入密码 37 袁志刚 9 2名称定义 名称可以代表一个单元格或者一个单元格区域 或者是常量 公式 名称的定义 选中需要命名的单元格或区域 在界面左上角名称框中输入名称后回车 需要注意的是 名称框只能用于定义单元格和单元格区域的名称 公式的名称需要在 公式 名称管理器 中进行定义 名称的引用 需要引用某单元格时输入为该单元格定义的名称 名称步骤1 选中需要命名的某个单元格或单元格区域 步骤2 在左上角名称框输入命名后回车 删除名称 选择 公式 菜单 名称管理器 选中需要删除的名称 点击 删除 按钮 38 袁志刚 9 3报表汇总 快速汇总表 sum 起始表 结束表 需汇总的单元格 要求 各个表结构必须一致利用透视表多重合并计算数据区域汇总调出多重合并计算数据区域功能快捷键 文件 选项 快速访问工具栏 选择所有命令 在列表中找到 数据透视表和数据透视图向导 选到右侧点击快速访问工具栏上的透视表向导按钮 选择 多重合并计算数据区域 选项 选择创建单页字段 逐个选择需合并的表 完成即可 优点 数据可多可少 可在一个视图上选择查看各个表的数据 39 袁志刚 9 4数字大写转化 转化效果公式中文小写公式 numberstring b3 1 中文大写公式 numberstring b3 2 逐字转化 numberstring b3 3 另外的写法中文小写公式 text b3 dbnum1 中文大写公式 text b3 dbnum2 逐字转化 text b3 dbnum1 0 40 袁志刚 9 5应用技巧 格式编号要求 在编号前加N0 字符 并使得编号为8位 不足前面补零 NO TEXT A2 0