Excel高效财务管理应用

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