Excel高效财务管理应用

1、Excel 高效财务管理应用,主讲教师 韩良智 教授 北京科技大学东凌经济管理学院 lzhan,目录,1 运用Excel高效处理数据 2 销售管理 3 营运资金管理 4 成本费用管理 5 财务预测与财务报表分析,1 运用Excel高效处理数据,1.1 文本型数据及其处理方法 1.2 数值型数据及其处理方法 1.3 日期型数据及其处理方法 1.4 时间型数据及其处理方法 1.5 高效整理数据表,1.1 文本型数据及其处理方法,1.1.1 文本型数据的输入及文本运算符 1.1.2 常用文本函数的应用 1.1.3 将文本型数字转化为纯数字,1.1.1 文本型数据的输入及文本运算符,文本型数字的输入

2、设置单元格格式后输入 使用单引号 使用双引号 文本运算符 &符号,1.1.2 常用文本函数的应用,LEFT函数：从左边取指定长度的字符。 RIGHT函数：从右边取指定长度的字符。 MID函数：从指定位置取指定长度的字符。 TEXT函数：将数值转换为指定格式的文本。 ISTEXT函数：检验数据是否为文本。,1.1.3 将文本型数字转化为纯数字,利用智能标记 选择性粘贴 利用VALUE函数 利用公式 删除数字中的特殊字符 【例1-1】 文本型数据的处理,1.2 数值型数据及其处理方法,1.2.1 数值型数据的输入 1.2.2 与数值型数据有关的函数 1.2.3 将小写金额转换为大写 1.2.4 缩

3、位显示数据,1.2.1 数值型数据的输入,输入负数 输入分数 常规数字格式与科学计数法 有效数字位数的限制,1.2.2 与数值型数据有关的函数,MAX函数：求最大值。 MIN函数：求最小值。 AVERAGE函数：求平均数。 ROUND函数：四舍五入。 INT函数：无条件舍去小数，保留整数部分。 【例1-2】处理计算误差,1.2.3 将小写金额转换为大写,使用特殊格式显示大写数字 使用NUMBERSTRING函数 【例1-3】小写金额转换成大写,1.2.4 缩位显示数据,使用自定义数字格式缩位显示数据代码： 缩小1百位 0.00 缩小1千位显示数字： 0.00， 缩小1万位显示数字： 0!.0,

4、 缩小10万位显示数字： 0!.00, 缩小100万位显示数字： 0.00, 缩小1000万位显示数字： 0!.0, 缩小1亿位显示数字： 0!.00, 缩小10亿位显示数字： 0.00,1.3 日期型数据及其处理方法,1.3.1 日期型数据的输入及显示格式 1.3.2 日期型数据的计算 1.3.3 与日期型数据有关的函数 1.3.4 查找与修改非法日期,1.3.1 日期型数据的输入及显示格式,日期型数据的输入 日期型数据的显示格式 常规显示格式 自定义显示格式,1.3.2 日期型数据的计算,日期型数据计算的应用情况 计算应收账款账龄 计算应付账款账龄 计算工龄 设置常规数据格式,1.3.3

5、与日期型数据有关的函数,TODAY函数：返回系统当前日期的序列号。 DATE函数：将三个数字组成一个日期序列号。 YEAR函数：获取指定日期序列号的年份数字。 MONTH函数：获取指定日期序列号的月份数字。 DAY函数：获取指定日期序列号的日数字。 WEEKDAY：获取某日期为星期几的数字。,1.3.4 查找与修改非法日期,查找非法日期 利用“单元格格式”对话框判断 使用ISNUMBER函数判断 设置数据有效性并圈释无效数据 修改非法日期 查找与替换 使用函数 使用分列工具 【例1-4】处理非法日期数据,1.4 时间型数据及其处理方法,1.4.1 时间型数据的输入及显示格式 1.4.2 时间型

6、数据的计算 1.4.3 与时间型数据有关的函数,1.4.1 时间型数据的输入及显示格式,时间型数据的输入 时间型数据的显示格式 常规显示格式 自定义显示格式,1.4.2 时间型数据的计算,【例1-5】计算工作时数 注意跨越午夜的情况,1.4.3 与时间型数据有关的函数,NOW函数：返回当前日期和时间所对应的序列号。 HOUR函数：用于获取时间值的小时数。 MINUTE函数：用于获取时间值的分钟数。 SECOND函数：用于获取时间值的秒数。,1.5 高效整理数据表,1.5.1 快速删除数据表中的所有空行 1.5.2 快速填充数据表中的所有空单元格 1.5.3 利用分列工具高效整理数据 1.5.4

7、 整理数据表综合练习,1.5.1 快速删除数据表中的所有空行,排序法 筛选法 定位法 【例1-6】快速删除所有空行,1.5.2 快速填充数据表中的所有空单元格,快速填充表格中的所有空单元格 将空单元格填充为上一个单元格数据 【例1-7】快速填充数据,1.5.3 利用分列工具高效整理数据,【例1-8】整理银行对账单,整理前,整理后,1.5.4 整理数据表综合练习,快速整理从ERP中导入的数据表 【例1-9】整理数据综合练习,2 销售管理,2.1 销售数据的透视分析 2.2 数据透视表的高级应用 2.3 高效汇总多重区域的销售数据 2.4 使用函数汇总销售额 2.5 绘制销售图表,2.1 销售数据

8、的透视分析,2.1.1 制作数据透视表的基本方法 2.1.2 数据透视表的编辑与更新方法 2.1.3 运用数据透视表多角度分析财务数据 2.1.4 制作与使用数据透视图 【例2-1】销售数据透视分析,2.1.1 制作数据透视表的基本方法,【数据】/【数据透视表和数据透视图】 合理设置数据透视表的布局 相关概念 页字段 行字段 列字段 数据字段,2.1.2 数据透视表的编辑与更新方法,修改字段名称 设置字段的数字格式 取消数据透视表行总计或列总计 取消字段的分类汇总 合并数据标志 设置错误值或空值的显示方式 更新数据透视表的数据,2.1.3 运用数据透视表多角度分析销售数据 修改数据透视表布局

9、显示明细数据 2.1.4 制作与使用数据透视图 在数据清单基础上制作数据透视图 在数据透视表基础上制作数据透视图,2.2 数据透视表的高级应用,2.2.1 通过组合字段创建多维数据汇总表 2.2.2 通过设置字段显示方式创建各种比较分析表 2.2.3 通过自定义设置灵活编制销售数据汇总表,2.2.1 通过组合字段创建多维销售数据汇总表,按不同字段分组查看数据 按日期分组 按地区分组 按商品类别分组,2.2.2 通过设置字段显示方式 创建各种比较分析表,差异或差异百分比 占同行或同列百分比 占总合百分比 累计值显示 纵向差异比较,2.2.3 通过自定义设置 灵活编制数据汇总表,添加自定义计算字段

10、 添加自定义计算项,2.3 使用数据透视表汇总多重区域数据,【例2-2】高效汇总多重区域的销售数据,2.4 使用函数汇总销售数据,【例2-3】按客户汇总销售额 SUMIF函数条件求和,2.5 绘制销售图表,2.4.1 制作组合销售图表 【例2-4】 2.4.2 制作销售预算完成进度图表 【例2-5】 2.4.3 绘制动态销售图表 【例2-6】 INDEX函数返回数组中指定行和列处的元素值。 使用组合框控件,3 营运资金管理,3.1 现金管理 3.2 应收账款管理 3.3 存货管理 3.4 应付账款管理,3.1 现金管理,3.1.1 普通日记账与现金日记账 3.1.2 现金预算表及其合并计算,3

11、.1.1 普通日记账与现金日记账,【例3-1】普通日记账与现金日记账 整理标准数据清单 筛选现金日记账 计算现金余额 设置条件格式,3.1.2 现金预算表及其合并计算,【例3-2】 数据/合并计算 被合并计算的数据区域结构应相同,3.2 应收账款管理,3.2.1 建立应收账款台账 3.2.2 应收账款的排序分析 3.2.3 应收账款的筛选分析 3.2.4 应收账款分类汇总分析 3.2.5 制作应收账款账龄分析表,3.2.1建立应收账款台账,【例3-3】 利用记录单输入和编辑数据 数据/记录单 拆分工作表 窗口/拆分,3.2.2 应收账款的排序分析,【例3-4】 数据/排序 一个关键字的情况 多

12、个关键字的情况,3.2.3 应收账款的筛选分析,【例3-5】 数据/筛选 自动筛选 高级筛选 一个条件的情况 多个条件的情况,3.2.4 应收账款分类汇总分析,【例3-6】 建立分类汇总 数据/分类汇总 查看分类汇总结果 复制分类汇总结果 选择可见单元格 删除分类汇总,3.2.5 制作应收账款账龄分析表,【例3-7】 SUMIF函数根据指定条件对若干单元格求和。 SUMPRODUCT函数将给定的几个数组的对应的元素相乘，并返回乘积之和。,3.3 存货管理,3.3.1 建立存货收发存明细表 3.3.2 存货查询 3.3.3 存货的ABC分类模型 3.3.4 经济订货批量模型,3.3.1 建立存货

13、收发存明细表,【例3-8】 为整列定义名称 删除网格线 运用条件格式设置边框和字体颜色,3.3.2 存货查询,【例3-9】 VLOOKUP函数在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。 COLUMN函数返回给定引用的列标。,3.3.3 存货的ABC分类模型,存货的分类 A类存货关键的少数 C类存货不重要的多数 B类存货居中的类别 常见的分类标准：,3.3.3 存货的ABC分类模型,【例3-10】 COUNTA函数返回参数列表中非空值的单元格个数。 COUNTIF函数计算区域中满足给定条件的单元格的个数。 绘制两轴线柱形图,3.3.4 经济订货批量模型,存

14、货的相关总成本（总订储费用） 经济订货批量 最佳的订货次数 最低的总订储费用 式中：Q为一次订货批量；D为一定时期存货的需求量；A为一次订货费；P为存货单价；K为存货的存储费率；PK即为单位存储费用。,3.3.4 经济订货批量模型,【例3-11】计算经济订货批量 SQRT函数开平方 建立和使用自定义函数 工具/宏/Visual Basic编辑器 插入/模块 插入/过程,3.4 应付账款管理,3.4.1 应付账款到期日的计算 3.4.2 应付账款到期日提前提醒,3.4.1 应付账款到期日的计算,EDATE函数计算出所指定月数之前或之后的日期 EOMONTH函数计算指定日期到月底的天数 【例3-1

15、2】计算应付账款到期日,3.4.2 应付账款到期日提前提醒,设置条件格式 【例3-13】应付账款到期日提前提醒,4 成本费用管理,4.1 材料费用管理 4.2 工资费用管理 4.3 期间费用管理,4.1 材料费用管理,4.1.1 制作限额领料单 4.1.2 快速编制原材料费用预算,4.1.1 制作限额领料单,【例4-1】制作限额领料单 数据有效性的设置 隐藏行和列 保护单元格和保护工作表 为工作簿设置密码,3.1.2 快速编制原材料费用预算,【例3-2】计算原材料成本 【例3-3】编制材料需求量预算表 MMULT函数返回两数组的矩阵乘积,4.1.2 快速编制原材料费用预算,【例4-2】计算原材

16、料成本 【例4-3】编制材料需求量预算表 MMULT函数返回两数组的矩阵乘积,4.2 工资费用管理,4.2.1 工资和奖金的计算 4.2.2 快速制作工资条,4.2.1 工资和奖金的计算,【例4-4】计算基本工资和工龄工资 根据职称确定基本工资 根据工龄计算工龄工资 【例4-5】计算销售人员年度工资和奖金 HLOOKUP函数在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。 【例4-6】计算个人所得税 使用IF函数计算 建立自定义函数计算,4.2.2 快速制作工资条,【例4-7】快速制作工资条,4.3 期间费用管理,4.3.1 营业费用自动汇总表 4.3.2 费

17、用预算执行情况分析图,4.3.1 营业费用自动汇总表,INDIRECT函数返回由文本字符串指定的引用 ROW函数返回行号 【例4-8】 营业费用自动汇总表,4.3.2 费用预算执行情况分析图,【例4-9】 组合图表 数据系列格式设置,5 财务预测与财务报表分析,5.1 财务预测 5.2 财务报表分析,5.1 财务预测,5.1.1 利用绘图工具预测 5.1.2 利用相关函数预测 5.1.3 利用数据分析工具预测,5.1.1 利用绘图工具预测,【例5-1】 创建散点图 添加趋势线,5.1.2 利用相关函数预测,常用的线性预测函数 INTERCEPT函数求回归直线的截距 SLOPE求回归直线的斜率。

18、 TREND函数返回一条线性回归拟合线的值。 【例5-2】成本预测,更多的预测函数,其他线性预测函数 LINEST函数使用最小二乘法对已知数据进行最佳直线拟合，并返回描述此直线的数组。 FORECAST函数根据已有的数值计算或预测未来值。此预测值为基于给定的 x 值推导出的 y 值。 指数预测函数 LOGEST函数在回归分析中，计算最符合数据的指数回归拟合曲线，并返回描述该曲线的数值数组。 GROWTH函数根据现有的数据预测指数增长值。,5.1.3 利用数据分析工具预测,【例5-3】基于移动平均法的销售预测 工具/数据分析/移动平均 【例5-4】基于回归分析法的销售预测 工具/数据分析/回归,5.2 财务报表分析,5.2.1 制作财务报表分析模型的封面 5.2.2 比较财务报表与共同比报表 5.2.3 财务比率分析 5.2.4 杜邦系统分析 5.2.5