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类存货——居中的类别常见的分类规范：存货类别品种%金额%A类10%～15%约80%B类20%～30%约15%C类55%～80%约5%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