Excel 高效财务管理应用

Excel高效财务管理应用,主讲教师韩良智教授北京科技大学经济管理学院,目录,1运用Excel高效处理数据2营运资金管理3成本费用管理4销售管理5长期投资与融资决策6财务预测与财务报表分析,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文本型数据的输入及文本运算符,文本型数字的输入设置单元格格式后输入使用单引号使用双引号文本运算符&符号,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缩位显示数据,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,缩小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与日期型数据有关的函数,TODAY函数：返回系统当前日期的序列号。DATE函数：将三个数字组成一个日期序列号。YEAR函数：获取指定日期序列号的年份数字。MONTH函数：获取指定日期序列号的月份数字。DAY函数：获取指定日期序列号的日数字。WEEKDAY：获取某日期为星期几的数字。,1.3.4查找与修改非法日期,查找非法日期利用“单元格格式”对话框判断使用ISNUMBER函数判断设置数据有效性并圈释无效数据修改非法日期查找与替换使用函数使用分列工具【例1-4】处理非法日期数据,1.4时间型数据及其处理方法,1.4.1时间型数据的输入及显示格式1.4.2时间型数据的计算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整理数据表综合练习,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.1现金管理,2.1.1普通日记账与现金日记账2.1.2现金预算表及其合并计算,2.1.1普通日记账与现金日记账,【例2-1】普通日记账与现金日记账整理标准数据清单筛选现金日记账计算现金余额设置条件格式,2.1.2现金预算表及其合并计算,【例2-2】数据/合并计算被合并计算的数据区域结构应相同,2.2应收账款管理,2.2.1建立应收账款台账2.2.2应收账款的排序分析2.2.3应收账款的筛选分析2.2.4应收账款分类汇总分析2.2.5制作应收账款账龄分析表,2.2.1建立应收账款台账,【例2-3】利用记录单输入和编辑数据数据/记录单拆分工作表窗口/拆分,2.2.2应收账款的排序分析,【例2-4】数据/排序一个关键字的情况多个关键字的情况,2.2.3应收账款的筛选分析,【例2-5】数据/筛选自动筛选高级筛选一个条件的情况多个条件的情况,2.2.4应收账款分类汇总分析,【例2-6】建立分类汇总数据/分类汇总查看分类汇总结果复制分类汇总结果选择可见单元格删除分类汇总,2.2.5制作应收账款账龄分析表,【例2-7】SUMIF函数根据指定条件对若干单元格求和。SUMPRODUCT函数将给定的几个数组的对应的元素相乘，并返回乘积之和。,2.3存货管理,2.3.1建立存货收发存明细表2.3.2存货台帐及存货查询2.3.3存货的ABC分类模型2.3.4经济订货批量模型,2.3.1建立存货收发存明细表,【例2-8】为整列定义名称删除网格线运用条件格式设置边框和字体颜色,2.3.2存货查询,【例2-9】VLOOKUP函数在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。COLUMN函数返回给定引用的列标。,2.3.3存货的ABC分类模型,存货的分类A类存货关键的少数C类存货不重要的多数B类存货居中的类别常见的分类标准：,2.3.3存货的ABC分类模型,【例2-10】COUNTA函数返回参数列表中非空值的单元格个数。COUNTIF函数计算区域中满足给定条件的单元格的个数。绘制两轴线柱形图,2.3.3经济订货批量模型,存货的相关总成本（总订储费用）经济订货批量最佳的订货次数最低的总订储费用式中：Q为一次订货批量；D为一定时期存货的需求量；A为一次订货费；P为存货单价；K为存货的存储费率；PK即为单位存储费用。,2.3.4经济订货批量模型,【例2-11】计算经济订货批量SQRT函数开平方建立和使用自定义函数工具/宏/VisualBasic编辑器插入/模块插入/过程,2.4应付账款管理,应付账款到期日的计算EDATE函数计算出所指定月数之前或之后的日期EOMONTH函数计算指定日期到月底的天数应付账款到期日提前提醒设置条件格式【例2-12】应付账款管理,3成本费用管理,3.1成本费用的日常管理3.2制作成本费用汇总表3.3成本费用分析图表,3.1成本费用的日常管理,3.1.1制作限额领料单3.1.2快速编制原材料费用预算3.1.3工资数据管理3.1.4快速制作工资条3.1.5期间费用管理,3.1.1制作限额领料单,【例3-1】制作限额领料单数据有效性的设置隐藏行和列保护单元格和保护工作表为工作簿设置密码,3.1.2快速编制原材料费用预算,【例3-2】计算原材料成本【例3-3】编制材料需求量预算表MMULT函数返回两数组的矩阵乘积,3.1.3工资费用管理,【例3-4】计算基本工资和工龄工资根据职称确定基本工资根据工龄计算工龄工资【例3-5】计算销售人员年度工资和奖金HLOOKUP函数在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。,3.1.3工资费用管理,【例3-6】计算个人所得税使用IF函数计算建立自定义函数计算【例3-7】工资数据管理对单元格区域数据进行相同的修改标示最大值和最小值,3.1.4快速制作工资条,【例3-8】快速制作工资条,3.2期间费用管理,3.2.1费用项目分类过账【例3-9】3.2.1营业费用自动汇总表【例3-10】INDIRECT函数返回由文本字符串指定的引用。ROW函数返回行号,3.3成本费用分析图表,3.3.1费用预算执行情况分析图3.3.2动态费用分析图表,3.3.1费用预算执行情况分析图,【例3-11】组合图表数据系列格式设置,3.3.2动态费用分析图表,【例3-12】可选定项目的费用折线图INDIRECT函数ADDRESS函数CELL函数ROW函数COLUMN函数,4销售管理,4.1销售数据的透视分析4.2数据透视表的高级应用4.3高效汇总多重区域的销售数据4.4使用函数汇总销售额4.5绘制销售图表,4.1销售数据的透视分析,4.1.1制作数据透视表的基本方法4.1.2数据透视表的编辑与更新方法4.1.3运用数据透视表多角度分析财务数据4.1.4制作与使用数据透视图【例4-1】销售数据透视分析,4.1.1制作数据透视表的基本方法,【数据】/【数据透视表和数据透视图】合理设置数据透视表的布局相关概念页字段行字段列字段数据字段,4.1.2数据透视表的编辑与更新方法,修改字段名称设置字段的数字格式取消数据透视表行总计或列总计取消字段的分类汇总合并数据标志设置错误值或空值的显示方式更新数据透视表的数据,4.1.3运用数据透视表多角度分析销售数据修改数据透视表布局显示明细数据4.1.4制作与使用数据透视图在数据清单基础上制作数据透视图在数据透视表基础上制作数据透视图,4.2数据透视表的高级应用,4.2.1通过组合字段创建多维数据汇总表4.2.2通过设置字段显示方式创建各种比较分析表4.2.3通过自定义设置灵活编制销售数据汇总表,4.2.1通过组合字段创建多维销售数据汇总表,按不同字段分组查看数据按日期分组按地区分组按商品类别分组,4.2.2通过设置字段显示方式创建各种比较分析表,差异或差异百分比占同行或同列百分比占总合百分比累计值显示纵向差异比较,4.2.3通过自定义设置灵活编制数据汇总表,添加自定义计算字段添加自定义计算项,4.3高效汇总多重区域销售数据,【例4-2】高效汇总多重区域的销售数据无论拟汇总的数据区域结构是否相同均适用,4.4使用函数汇总销售额,【例4-3】按客户汇总销售额SUMIF函数条件求和,4.4绘制销售图表,4.4.1制作组合销售图表【例4-4】4.4.2制作销售预算完成进度图表【例4-5】4.4.3绘制动态销售图表【例4-6】OFFSET函数以指定的引用为参照系，通过给定偏移量返回新的引用使用单选按钮控件,5长期投资与融资决策,5.1常用财务函数的应用5.2财务规划工具的运用5.3投资风险分析,5.1常用财务函数的应用,5.1.1计算终值的函数5.1.2计算现值的函数5.1.3计算内部收益率的函数5.1.4计算投资期的函数5.1.5计算等值年金的相关函数,5.1.1计算终值的函数,FV函数计算复利终值【例5-1】计算终值公式审核追踪引用单元格追踪从属单元格,5.1.2计算现值的函数,PV函数计算复利现值。NPV函数计算一系列现金流的现值。XNPV计算非固定间隔期发生的一系列现金流的现值。【例5-2】计算现值与净现值【例5-3】制作现值系数表运用数组公式,5.1.3计算内部收益率的函数,RATE函数返回未来款项的各期利率。IRR函数返回由数值代表的一组现金流的内部收益率。XIRR函数返回一组现金流的内部收益率，这些现金流不一定定期发生。【例5-4】计算投资收益率,5.1.4计算投资期的函数,NPER函数基于固定利率及等额分期付款方式，返回某项投资（或贷款）的总期数。【例5-5】计算投资回收期,5.1.5计算年金的相关函数,PMT函数基于固定利率及等额分期付款方式，返回投资或贷款的每期付款额。IPMT函数基于固定利率及等额分期付款方式，返回投资或贷款在某一给定期次内的利息偿还额。PPMT函数基于固定利率及等额分期付款方式，返回投资或贷款在某一给定期次内的本金偿还额。【例5-6】分期等额摊还贷款,5.2财务规划工具的运用,5.2.1模拟运算表工具的运用5.2.2单变量求解工具的运用5.2.3规划求解工具的运用,5.2.1模拟运算表工具的运用,【数据】/【模拟运算表】单变量模拟运算表双变量模拟运算表【例5-7】每期还款额的模拟运算,5.2.2单变量求解工具的运用,在目标单元格中输入公式公式中引用可变单元格【工具】/【单变量求解】【例5-8】实现目标利润的措施分析,5.2.3规划求解工具的运用,在目标单元格中输入公式在相关单元格中建立约束条件公式中引用可变单元格【工具】/【规划求解】【例5-9】投资组合规划,5.3投资项目的综合评价,【例5-10】计算净现金流量绘制现金流量图计算评价指标做出决策结论,6财务预测与财务报表分析,6.1财务预测6.2财务报表分析,6.1财务预测,6.1.1利用相关函数预测6.1.2利用数据分析工具预测6.1.3利用绘图工具预测,6.1.1利用相关函数预测,常用的线性预测函数INTERCEPT函数求回归直线的截距SLOPE求回归直线的斜率。TREND函数返回一条线性回归拟合线的值