Excel高效财务管理应用

nullExcel 高效财务管理应用Excel 高效财务管理应用主讲教师 韩良智 教授 北京科技大学东凌经济管理学院 lzhan@manage.ustb.edu.cn 目录目录1 运用Excel高效处理数据 2 销售管理 3 营运资金管理 4 成本费用管理 5 财务预测与财务报表分析1  运用Excel高效处理数据1  运用Excel高效处理数据1.1 文本型数据及其处理方法 1.2 数值型数据及其处理方法 1.3 日期型数据及其处理方法 1.4 时间型数据及其处理方法 1.5 高效整理数据表1.1 文本型数据及其处理方法1.1 文本型数据及其处理方法1.1.1 文本型数据的输入及文本运算符 1.1.2 常用文本函数的应用 1.1.3 将文本型数字转化为纯数字1.1.1 文本型数据的输入及文本运算符1.1.1 文本型数据的输入及文本运算符文本型数字的输入 设置单元格格式后输入 使用单引号 使用双引号 文本运算符 &符号1.1.2 常用文本函数的应用1.1.2 常用文本函数的应用LEFT函数：从左边取指定长度的字符。 RIGHT函数：从右边取指定长度的字符。 MID函数：从指定位置取指定长度的字符。 TEXT函数：将数值转换为指定格式的文本。 ISTEXT函数：检验数据是否为文本。1.1.3 将文本型数字转化为纯数字1.1.3 将文本型数字转化为纯数字利用智能标记 选择性粘贴 利用VALUE函数 利用公式 删除数字中的特殊字符 【例1-1】 文本型数据的处理1.2 数值型数据及其处理方法1.2 数值型数据及其处理方法1.2.1 数值型数据的输入 1.2.2 与数值型数据有关的函数 1.2.3 将小写金额转换为大写 1.2.4 缩位显示数据1.2.1 数值型数据的输入1.2.1 数值型数据的输入输入负数 输入分数 常规数字格式与科学计数法 有效数字位数的限制1.2.2 与数值型数据有关的函数1.2.2 与数值型数据有关的函数MAX函数：求最大值。 MIN函数：求最小值。 AVERAGE函数：求平均数。 ROUND函数：四舍五入。 INT函数：无条件舍去小数，保留整数部分。 【例1-2】处理计算误差1.2.3 将小写金额转换为大写1.2.3 将小写金额转换为大写使用特殊格式显示大写数字 使用NUMBERSTRING函数 【例1-3】小写金额转换成大写1.2.4 缩位显示数据 1.2.4 缩位显示数据 使用自定义数字格式缩位显示数据代码： 缩小1百位 0"."00 缩小1千位显示数字： 0.00， 缩小1万位显示数字： 0!.0, 缩小10万位显示数字： 0!.00, 缩小100万位显示数字： 0.00,, 缩小1000万位显示数字： 0!.0,, 缩小1亿位显示数字： 0!.00,, 缩小10亿位显示数字： 0.00,,,1.3 日期型数据及其处理方法1.3 日期型数据及其处理方法1.3.1 日期型数据的输入及显示格式 1.3.2 日期型数据的计算 1.3.3 与日期型数据有关的函数 1.3.4 查找与修改非法日期1.3.1 日期型数据的输入及显示格式1.3.1 日期型数据的输入及显示格式日期型数据的输入 日期型数据的显示格式 常规显示格式 自定义显示格式1.3.2 日期型数据的计算1.3.2 日期型数据的计算日期型数据计算的应用情况 计算应收账款账龄 计算应付账款账龄 计算工龄 设置常规数据格式1.3.3 与日期型数据有关的函数1.3.3 与日期型数据有关的函数TODAY函数：返回系统当前日期的序列号。 DATE函数：将三个数字组成一个日期序列号。 YEAR函数：获取指定日期序列号的年份数字。 MONTH函数：获取指定日期序列号的月份数字。 DAY函数：获取指定日期序列号的日数字。 WEEKDAY：获取某日期为星期几的数字。1.3.4 查找与修改非法日期1.3.4 查找与修改非法日期查找非法日期 利用“单元格格式”对话框判断 使用ISNUMBER函数判断 设置数据有效性并圈释无效数据 修改非法日期 查找与替换 使用函数 使用分列工具 【例1-4】处理非法日期数据1.4 时间型数据及其处理方法1.4 时间型数据及其处理方法1.4.1 时间型数据的输入及显示格式 1.4.2 时间型数据的计算 1.4.3 与时间型数据有关的函数1.4.1 时间型数据的输入及显示格式1.4.1 时间型数据的输入及显示格式时间型数据的输入 时间型数据的显示格式 常规显示格式 自定义显示格式1.4.2 时间型数据的计算1.4.2 时间型数据的计算【例1-5】计算工作时数 注意跨越午夜的情况1.4.3 与时间型数据有关的函数1.4.3 与时间型数据有关的函数NOW函数：返回当前日期和时间所对应的序列号。 HOUR函数：用于获取时间值的小时数。 MINUTE函数：用于获取时间值的分钟数。 SECOND函数：用于获取时间值的秒数。1.5 高效整理数据表1.5 高效整理数据表1.5.1 快速删除数据表中的所有空行 1.5.2 快速填充数据表中的所有空单元格 1.5.3 利用分列工具高效整理数据 1.5.4 整理数据表综合练习1.5.1 快速删除数据表中的所有空行1.5.1 快速删除数据表中的所有空行排序法 筛选法 定位法 【例1-6】快速删除所有空行1.5.2 快速填充数据表中的所有空单元格1.5.2 快速填充数据表中的所有空单元格快速填充中的所有空单元格 将空单元格填充为上一个单元格数据 【例1-7】快速填充数据1.5.3 利用分列工具高效整理数据1.5.3 利用分列工具高效整理数据【例1-8】整理银行对账单整理前整理后1.5.4 整理数据表综合练习1.5.4 整理数据表综合练习快速整理从ERP中导入的数据表 【例1-9】整理数据综合练习2 销售管理 2 销售管理 2.1 销售数据的透视分析 2.2 数据透视表的高级应用 2.3 高效汇总多重区域的销售数据 2.4 使用函数汇总销售额 2.5 绘制销售图表2.1 销售数据的透视分析2.1 销售数据的透视分析2.1.1 制作数据透视表的基本方法 2.1.2 数据透视表的编辑与更新方法 2.1.3 运用数据透视表多角度分析财务数据 2.1.4 制作与使用数据透视图 【例2-1】销售数据透视分析2.1.1 制作数据透视表的基本方法2.1.1 制作数据透视表的基本方法【数据】/【数据透视表和数据透视图】 合理设置数据透视表的布局 相关概念 页字段 行字段 列字段 数据字段2.1.2 数据透视表的编辑与更新方法2.1.2 数据透视表的编辑与更新方法修改字段名称 设置字段的数字格式 取消数据透视表行总计或列总计 取消字段的分类汇总 合并数据标志 设置错误值或空值的显示方式 更新数据透视表的数据null2.1.3 运用数据透视表多角度分析销售数据 修改数据透视表布局 显示明细数据 2.1.4 制作与使用数据透视图 在数据清单基础上制作数据透视图 在数据透视表基础上制作数据透视图2.2 数据透视表的高级应用2.2 数据透视表的高级应用2.2.1 通过组合字段创建多维数据汇总表 2.2.2 通过设置字段显示方式创建各种比较分析表 2.2.3 通过自定义设置灵活编制销售数据汇总表2.2.1 通过组合字段创建多维销售数据汇总表2.2.1 通过组合字段创建多维销售数据汇总表按不同字段分组查看数据 按日期分组 按地区分组 按商品类别分组2.2.2 通过设置字段显示方式 创建各种比较分析表2.2.2 通过设置字段显示方式 创建各种比较分析表差异或差异百分比 占同行或同列百分比 占总合百分比 累计值显示 纵向差异比较2.2.3 通过自定义设置 灵活编制数据汇总表2.2.3 通过自定义设置 灵活编制数据汇总表添加自定义计算字段 添加自定义计算项2.3 使用数据透视表汇总多重区域数据 2.3 使用数据透视表汇总多重区域数据 【例2-2】高效汇总多重区域的销售数据2.4 使用函数汇总销售数据2.4 使用函数汇总销售数据【例2-3】按客户汇总销售额 SUMIF函数——条件求和2.5 绘制销售图表2.5 绘制销售图表2.4.1 制作组合销售图表 【例2-4】 2.4.2 制作销售预算完成进度图表 【例2-5】 2.4.3 绘制动态销售图表 【例2-6】 INDEX函数——返回数组中指定行和列处的元素值。 使用组合框控件3 营运资金管理3 营运资金管理3.1 现金管理 3.2 应收账款管理 3.3 存货管理 3.4 应付账款管理3.1 现金管理3.1 现金管理3.1.1 普通日记账与现金日记账 3.1.2 现金预算表及其合并计算3.1.1 普通日记账与现金日记账3.1.1 普通日记账与现金日记账【例3-1】普通日记账与现金日记账 整理标准数据清单 筛选现金日记账 计算现金余额 设置条件格式3.1.2 现金预算表及其合并计算3.1.2 现金预算表及其合并计算【例3-2】 数据/合并计算 被合并计算的数据区域结构应相同3.2 应收账款管理3.2 应收账款管理3.2.1 建立应收账款台账 3.2.2 应收账款的排序分析 3.2.3 应收账款的筛选分析 3.2.4 应收账款分类汇总分析 3.2.5 制作应收账款账龄分析表 3.2.1建立应收账款台账3.2.1建立应收账款台账【例3-3】 利用记录单输入和编辑数据 数据/记录单 拆分工作表 窗口/拆分3.2.2 应收账款的排序分析3.2.2 应收账款的排序分析【例3-4】 数据/排序 一个关键字的情况 多个关键字的情况3.2.3 应收账款的筛选分析3.2.3 应收账款的筛选分析【例3-5】 数据/筛选 自动筛选 高级筛选 一个条件的情况 多个条件的情况3.2.4 应收账款分类汇总分析3.2.4 应收账款分类汇总分析【例3-6】 建立分类汇总 数据/分类汇总 查看分类汇果 复制分类汇总结果 选择可见单元格 删除分类汇总3.2.5 制作应收账款账龄分析表3.2.5 制作应收账款账龄分析表【例3-7】 SUMIF函数——根据指定条件对若干单元格求和。 SUMPRODUCT函数——将给定的几个数组的对应的元素相乘，并返回乘积之和。3.3 存货管理3.3 存货管理3.3.1 建立存货收发存明细表 3.3.2 存货查询 3.3.3 存货的ABC分类模型 3.3.4 经济订货批量模型3.3.1 建立存货收发存明细表3.3.1 建立存货收发存明细表【例3-8】 为整列定义名称 删除网格线 运用条件格式设置边框和字体颜色3.3.2 存货查询3.3.2 存货查询【例3-9】 VLOOKUP函数——在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。 COLUMN函数——返回给定引用的列标。3.3.3 存货的ABC分类模型3.3.3 存货的ABC分类模型存货的分类 A类存货——关键的少数 C类存货——不重要的多数 B类存货——居中的类别 常见的分类标准：3.3.3 存货的ABC分类模型3.3.3 存货的ABC分类模型【例3-10】 COUNTA函数——返回参数列表中非空值的单元格个数。 COUNTIF函数——计算区域中满足给定条件的单元格的个数。 绘制两轴线柱形图3.3.4 经济订货批量模型3.3.4 经济订货批量模型存货的相关总成本（总订储费用） 经济订货批量 最佳的订货次数 最低的总订储费用 式中：Q为一次订货批量；D为一定时期存货的需求量；A为一次订货费；P为存货单价；K为存货的存储费率；PK即为单位存储费用。3.3.4 经济订货批量模型3.3.4 经济订货批量模型【例3-11】计算经济订货批量 SQRT函数——开平方 建立和使用自定义函数 工具/宏/Visual Basic编辑器 插入/模块 插入/过程3.4 应付账款管理3.4 应付账款管理3.4.1 应付账款到期日的计算 3.4.2 应付账款到期日提前提醒 3.4.1 应付账款到期日的计算3.4.1 应付账款到期日的计算EDATE函数——计算出所指定月数之前或之后的日期 EOMONTH函数——计算指定日期到月底的天数 【例3-12】计算应付账款到期日3.4.2 应付账款到期日提前提醒3.4.2 应付账款到期日提前提醒设置条件格式 【例3-13】应付账款到期日提前提醒4 成本费用管理4 成本费用管理4.1 材料费用管理 4.2 工资费用管理 4.3 期间费用管理4.1 材料费用管理4.1 材料费用管理4.1.1 制作限额领料单 4.1.2 快速编制原材料费用预算 4.1.1 制作限额领料单4.1.1 制作限额领料单【例4-1】制作限额领料单 数据有效性的设置 隐藏行和列 保护单元格和保护工作表 为工作簿设置密码3.1.2 快速编制原材料费用预算3.1.2 快速编制原材料费用预算【例3-2】计算原材料成本 【例3-3】编制材料需求量预算表 MMULT函数——返回两数组的矩阵乘积4.1.2 快速编制原材料费用预算4.1.2 快速编制原材料费用预算【例4-2】计算原材料成本 【例4-3】编制材料需求量预算表 MMULT函数——返回两数组的矩阵乘积4.2 工资费用管理4.2 工资费用管理4.2.1 工资和奖金的计算 4.2.2 快速制作工资条 4.2.1 工资和奖金的计算4.2.1 工资和奖金的计算【例4-4】计算基本工资和工龄工资 根据职称确定基本工资 根据工龄计算工龄工资 【例4-5】计算销售人员年度工资和奖金 HLOOKUP函数——在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。 【例4-6】计算个人所得税 使用IF函数计算 建立自定义函数计算4.2.2 快速制作工资条4.2.2 快速制作工资条【例4-7】快速制作工资条4.3 期间费用管理4.3 期间费用管理4.3.1 营业费用自动汇总表 4.3.2 费用预算执行情况分析图 4.3.1 营业费用自动汇总表4.3.1 营业费用自动汇总表INDIRECT函数——返回由文本字符串指定的引用 ROW函数——返回行号 【例4-8】 营业费用自动汇总表4.3.2 费用预算执行情况分析图4.3.2 费用预算执行情况分析图【例4-9】 组合图表 数据系列格式设置5 财务预测与财务报表分析5 财务预测与财务报表分析5.1 财务预测 5.2 财务报表分析5.1 财务预测5.1 财务预测5.1.1 利用绘图工具预测 5.1.2 利用相关函数预测 5.1.3 利用数据分析工具预测5.1.1 利用绘图工具预测5.1.1 利用绘图工具预测【例5-1】 创建散点图 添加趋势线5.1.2 利用相关函数预测5.1.2 利用相关函数预测常用的线性预测函数 INTERCEPT函数——求回归直线的截距 SLOPE——求回归直线的斜率。 TREND函数——返回一条线性回归拟合线的值。 【例5-2】成本预测更多的预测函数更多的预测函数其他线性预测函数 LINEST函数——使用最小二乘法对已知数据进行最佳直线拟合，并返回描述此直线的数组。 FORECAST函数——根据已有的数值计算或预测未来值。此预测值为基于给定的 x 值推导出的 y 值。 指数预测函数 LOGEST函数——在回归分析中，计算最符合数据的指数回归拟合曲线，并返回描述该曲线的数值数组。 GROWTH函数——根据现有的数据预测指数增长值。5.1.3 利用数据分析工具预测5.1.3 利用数据分析工具预测【例5-3】基于移动平均法的销售预测 工具/数据分析/移动平均 【例5-4】基于回归分析法的销售预测 工具/数据分析/回归5.2 财务报表分析5.2 财务报表分析5.2.1 制作财务报表分析模型的封面 5.2.2 比较财务报表与共同比报表 5.2.3 财务比率分析 5.2.4 杜邦系统分析 5.2.5 综合财务分析 5.2.6 绘制财务指标雷达图5.2.1 制作财务报表分析模型的封面5.2.1 制作财务报表分析模型的封面插入图形对象 插入艺术字 插入超链接 【例6-5】财务报表分析模型5.2.2 比较财务报表与共同比报表5.2.2 比较财务报表与共同比报表资产负债表分析 利润表分析 现金流量表分析 AND函数 ISBLANK函数5.2.3 财务比率分析5.2.3 财务比率分析偿债能力比率 获利能力比率 营运能力比率 成长能力比率5.2.4 杜邦系统分析5.2.4 杜邦系统分析录制宏 查看宏代码 运行宏 使用命令按钮运行宏5.2.5 编制综合评分表5.2.5 编制综合评分表选定评价指标 确定标准评分值 确定行业标准值 计算指标实际值 计算综合得分 做出综合评价5.2.6 绘制财务指标雷达图5.2.6 绘制财务指标雷达图