Excel在财务管理中的应用

Excel在财务管理中的应用,主讲教师韩良智教授北京科技大学经济管理学院lzhanTel本课程将学到的Excel知识,Excel的基本操作运用公式使用函数创建与编辑图表排序筛选分类汇总,模拟运算表单变量求解工具规划求解工具数据分析工具数据透视表数据透视图录制宏,1基于Excel的数据处理基本知识,1.1Excel的数据类型及输入方法1.2文本型数据的处理方法1.3数值型数据的处理方法1.4日期型数据的处理方法1.5时间型数据的处理方法,1.1Excel的数据类型及输入方法,文本型数据数值型数据日期型数据时间型数据,1.2文本型数据的处理方法,1.2.1文本运算符及其应用DATE函数：将三个数字组成一个日期序列号。YEAR函数：获取指定日期序列号的年份数字。MONTH函数：获取指定日期序列号的月份数字。DAY函数：获取指定日期序列号的日数字。WEEKDAY：获取某日期为星期几。DAYS360函数：返回两个日期相差的天数（一年按360天计）,1.5时间型数据的处理方法,1.5.1时间型数据的显示格式1.5.2时间型数据的计算1.5.3与时间型数据有关的函数,与时间型数据有关的函数,NOW函数：返回当前日期和时间所对应的序列号。快捷方式：Ctrl+;Ctrl+Shift+;HOUR函数：用于获取时间值的小时数。MINUTE函数：用于获取时间值的分钟数。SECOND函数：用于获取时间值的秒数。,2资金时间价值的计算,2.1一笔现金流的终值与现值2.2年金的终值与现值2.3终值与现值系数表的编制,2.1一笔现金流的终值与现值,2.1.1一笔现金流的终值2.1.2一笔现金流的现值,2.1.1一笔现金流的终值,单利终值复利终值或式中：P为现在的一笔资金；n为计息期限；iS为单利年利率；i为复利年利率；FS为单利终值；F为复利终值；FVIFi,n=(1+i)n称为复利终值系数，它表示现在的1元钱在n年后的价值。,单利终值的计算,【例2-1】输入公式公式审核显示公式与显示计算结果输入序列数据绝对引用与相对引用单元格复制公式,复利终值的计算,【例2-2】调用函数的方法；FV函数的运用FV函数基于固定利率及等额分期付款方式，返回某项投资的未来值。公式为：=FV(rate,nper,pmt,pv,type)式中：rate各期利率，是一固定值。nper总投资（或贷款）期。pmt各期相等的收付金额。pv现值，也称为本金。type数字0或1，用以指定各期的付款时间是在期初还是期末，type为0表示期末，type为1表示期初。如果省略type，则默认其值为零。,2.1.2一笔现金流的现值,单利现值：复利现值：或：式中：F为未来值；n为期限；iS为单利年利率；i为复利年利率；PS为单利现值；P为复利现值；称为复利现值系数,PV函数的功能,PV函数的功能是基于固定利率及等额分期付款方式，返回某项投资的现值，现值为一系列未来付款的当前值的累积和。语法格式=PV(rate,nper,pmt,fv,type)式中各参数的含义如前所述。,现值的计算,【例2-3】PV函数的应用定义名称与建立名称公式插入/名称/定义插入/名称/指定利用名称框定义,2.2年金终值与年金现值,2.2.1年金的种类普通年金先付年金延期年金永续年金2.2.2普通年金的终值与现值2.2.3先付年金的终值与现值,2.2.2普通年金的终值与现值,普通年金终值的计算公式为：普通年金现值的计算公式为：式中：A为年金；F为年金终值；P为普通年金的现值；i为年利率；n为期限；FVIFAi,n称为年金终值系数；PVIFAi,n称为年金现值系数。,普通年金终值计算过程示例,假定年金为1元，年利率为10%，期数为4年,普通年金现值计算过程示例,假定年金为1元，年利率为10%，期数为4年,2.2.3先付年金的终值与现值,先付年金的终值Vn的计算公式为：或者：先付年金的现值V0的计算公式为：或者：,年金终值与现值的计算,【例2-4】通过本例学习：利用FV函数和PV函数计算年金终值和年金现值的方法,2.3终值与现值系数表的编制,2.3.1复制公式的方式2.3.2数组公式的运用方法按【Shift+Ctrl+Enter】组合键【例2-5】,3筹资管理,3.1长期银行借款管理3.2租赁筹资管理3.3流动负债管理,3.1长期银行借款管理,3.1.1银行借款的偿还方式到期一次还本付息分期付息到期还本分期等额还本余额计息分期等额还本付息3.1.2四种还款方式的比较3.1.3还款额的模拟运算分析,3.1.2四种还款方式的比较,【例3-1】IF函数与FV函数嵌套使用PMT函数PPMT函数IPMT函数,相关的函数,PMT函数基于固定利率及等额分期付款方式，返回投资或贷款的每期付款额。=PMT(rate,nper,pv,fv,type）IPMT函数基于固定利率及等额分期付款方式，返回投资或贷款在某一给定期次内的利息偿还额。=IPMT(rate,per,nper,pv,fv,type)PPMT函数基于固定利率及等额分期付款方式，返回投资或贷款在某一给定期次内的本金偿还额。=PPMT(rate,per,nper,pv,fv,type),3.1.3还款额的模拟运算分析,【例3-2】每期还款额的模拟运算通过本例学习：CUMPRINC函数和CUMIPMT函数使用模拟运算表【数据】/【模拟运算表】单变量模拟运算表双变量模拟运算表,相关的函数,CUMPRINC函数返回一笔贷款在给定的期间累计偿还的本金数额。公式为=CUMPRINC（rate，nper，pv，start_period，end_period，type）CUMIPMT函数返回一笔贷款在给定期间累计偿还的利息数额。公式为CUMIPMT（rate，nper，pv，start_period，end_period，type）为了使用以上两个函数需要首先加载分析工具库,3.2租赁筹资管理,3.2.1租金支付方式及计算方法平均分摊法每次应付的租金=（租赁资产的购置成本-租赁资产的预计残值+租赁期间的利息费用+租赁手续费）/租赁期内支付租金的次数等额年金法首先根据利率和手续费率确定综合租费率作为贴现率每期末应付的租金=租赁资产的购置成本/年金现值系数3.2.2租金支付方式选择计算模型,3.2.2租金支付方式选择计算模型,【例3-3】通过本例学习：组合框控件的设置【视图】/【工具栏】/【窗体】,3.3流动负债管理,3.3.1短期借款有效年利率的计算3.3.2应付账款管理模型,3.3.1短期借款有效年利率的计算,如果给定的年利率为i，每年计息m次，那么现在的一笔资金P在n年末的终值的计算公式为：,名义年利率与有效年利率,有效年利率r与名义年利率i之间的关系为：式中：m为每年计息的次数连续计息时：常数e等于2.71828，是自然对数的底数。,名义年利率与有效年利率,【例3-4】EFFECT函数用于计算有效年利率。=EFFECT(nominal_rate,npery)NOMINAL函数用于计算名义年利率。=NOMINAL(effect_rate,npery)式中：nominal_rate为名义年利率；effect_rate为有效年利率；npery为每年的复利计息期数。EXP函数返回e的n次幂。=EXP(number)式中：number为底数e的指数。,3.3.2应付账款管理模型,【例3-5】应付帐款到期日的计算EDATE函数EOMONTH函数应付账款到期日提前提醒运用条件格式：格式/条件格式,4流动资产管理,4.1货币资金管理4.2应收账款管理4.3存货管理,4.1货币资金管理,4.1.1从普通日记账中筛选现金日记账【例4-1】数据/筛选4.1.2货币资金余额的合并计算【例4-2】数据/合并计算,4.2应收及暂付款管理,4.2.1建立应收账款台账4.2.2应收账款排序4.2.3应收账款筛选4.2.4应收账款分类汇总4.2.5编制应收账款账龄分析表,4.2.1建立应收账款台账,【例4-3】利用记录单输入和编辑数据数据/记录单拆分工作表窗口/拆分,4.2.2应收账款排序,【例4-4】数据/排序一个关键字的情况多个关键字的情况,4.2.3应收账款筛选,【例4-5】数据/筛选自动筛选高级筛选一个条件的情况多个条件的情况,4.2.4应收账款分类汇总,【例4-6】建立分类汇总数据/分类汇总查看分类汇总结果删除分类汇总,4.2.5编制应收账款账龄分析表,【例4-7】SUMIF函数根据指定条件对若干单元格求和。=SUMIF(range,criteria,sum_range)SUMPRODUCT函数将给定的几个数组的对应的元素相乘，并返回乘积之和。=SUMPRODUCT(array1,array2,array3,.),4.3存货管理,4.3.1建立存货收发存明细表4.3.2存货台帐及存货查询4.3.3存货的ABC分类模型4.3.4经济订货批量模型,4.3.1建立存货收发存明细表,【例4-8】为整列定义名称删除网格线运用条件格式设置边框和字体颜色利用自定义格式快速输入序列数字,4.3.2存货查询,【例4-9】VLOOKUP函数在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)COLUMN函数返回给定引用的列标。=COLUMN(reference),4.3.3存货的ABC分类模型,常见的分类标准：A类存货：品种数约占10%15%，存货金额约占80%；B类存货：品种数约占20%30%，存货金额约占15%；C类存货：品种数约占55%80%，存货金额约占5%。,4.3.3存货的ABC分类模型,【例4-10】COUNTA函数返回参数列表中非空值的单元格个数。利用函数COUNTA可以计算单元格区域或数组中包含数据的单元格个数。公式为：=COUNTA(value1,value2,.)COUNTIF函数计算区域中满足给定条件的单元格的个数。=COUNTIF(range,criteria)绘制两轴线柱形图,4.3.3经济订货批量模型,存货的相关总成本（总订储费用）经济订货批量最佳的订货次数最低的总订储费用式中：Q为一次订货批量；D为一定时期存货的需求量；A为一次订货费；P为存货单价；K为存货的存储费率；PK即为单位存储费用。,4.3.1经济订货批量模型,【例4-11】SQRT函数开平方规划求解工具/规划求解建立和使用自定义函数工具/宏/VisualBasic编辑器插入/模块插入/过程,5成本费用管理,5.1成本费用预测5.2成本费用的日常管理5.3成本报表的合并计算5.4成本费用分析,5.1成本费用预测,5.1.1利用高低点法预测成本费用【例5-1】MAX、MIN、INDEX函数5.1.2利用回归分析法预测成本费用【例5-2】INTERCEPT、SLOPE、TREND函数,相关的函数,MAX函数给定参数表中的最大值。公式为：=MAX(number1,number2,numberN)MIN函数给定参数表中的最小值。公式为：=MIN(number1,number2,numberN)INDEX函数返回表格或区域中的数值或对数值的引用。该函数有以下两种形式：返回数组中指定单元格或单元格数组的数值。公式为：=INDEX(array,row_num,column_num)返回引用中指定的单元格。公式为：=INDEX(reference,row_num,column_num,area_num),相关的函数,INTERCEPT函数求回归直线的截距。公式为：=INTERCEPT(known_ys,known_xs)SLOPE求回归直线的斜率。公式为：=SLOPE(known_ys,known_xs)TREND函数返回一条线性回归拟合线的值。公式为：=TREND(known_ys,known_xs,new_xs,const),5.2成本费用的日常管理,5.2.1材料费用管理5.2.2工资费用管理5.2.3制造费用和期间费用管理,5.2.1材料费用管理,【例5-3】制作限额领料单数据有效性的设置隐藏行和列保护单元格和保护工作表为工作簿设置密码,5.2.1材料费用管理,【例5-4】计算原材料成本SUMPRODUCT函数【例5-5】编制材料需求量预算表MMULT函数返回两数组的矩阵乘积。结果矩阵的行数与array1的行数相同，矩阵的列数与array2的列数相同。=MMULT(array1,array2),5.2.2工资费用管理,【例5-6】计算个人所得税使用IF函数计算利用数组公式计算利用自定义函数计算,5.2.2工资费用管理,【例5-7】计算基本工资和工龄工资根据职称确定基本工资根据工龄计算工龄工资【例5-8】计算销售人员年度工资和奖金HLOOKUP函数在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。=HLOOKUP(lookup_value,table_array,row_index_num,range_lookup),5.2.2工资费用管理,【例5-9】工资数据管理对单元格区域数据进行相同的修改圈示无效数据标示最大值和最小值【例5-10】快速制作工资条,5.2.3制造费用和期间费用管理,【例5-11】费用项目分类过账【例5-12】管理费用的预算管理将负数显示为正数缩位显示数据运用名称公式拆分与冻结窗口,5.3成本报表的汇总计算,【例5-13】成本报表合并计算【例5-14】营业费用汇总表INDIRECT函数返回由文本字符串指定的引用。ROW函数返回行号【例5-15】带控件的成本汇总表ISERROR函数检测一个值是否为错误值。=INDIRECT(ref_text,a1),5.4成本费用分析,【例5-16】费用折线图在图上显示最大和最小值将图表绘制到PPT文件【例5-17】产品成本动态图表带有单选按钮OFFSET函数以指定的引用为参照系，通过给定偏移量返回新的引用【例5-18】选定月份的费用折线图可选择费用项目INDIRECT、ADDRESS、CELL、ROW、COLUMN函数,6销售收入管理,6.1销售收入预测6.2销售数据的透视分析6.3绘制销售图表,6.1销售收入预测,6.1.1利用相关函数预测销售收入6.1.2利用数据分析工具预测销售收入6.1.3利用绘图工具预测销售收入,6.1.1利用相关函数预测销售收入,相关的函数LINEST函数的功能是使用最小二乘法对已知数据进行最佳直线拟合，并返回描述此直线的数组。LOGEST函数在回归分析中，计算最符合数据的指数回归拟合曲线，并返回描述该曲线的数值数组。FORECAST函数根据已有的数值计算或预测未来值。此预测值为基于给定的x值推导出的y值。GROWTH函数根据现有的数据预测指数增长值。,6.1.2利用数据分析工具预测销售收入,【例6-1】基于移动平均法的销售预测工具/数据分析/移动平均【例6-2】基于回归分析法的销售预测工具/数据分析/回归,6.1.3利用绘图工具预测销售收入,【例6-3】创建散点图添加趋势线,6.2销售数据的透视分析,6.2.1整理数据表6.2.2建立销售数据透视表6.2.3创建销售数据透视图【例6-4】,6.2.1整理数据表,取消合并单元格删除空行和空列删除字符串前后的空格适用合法的日期数据,6.2.2建立销售数据透视表,数据/数据透视表和数据透视图数据透视表的布局和修改修改字段名称设置字段的数字格式取消数据透视表和行或列汇总取消字段的分类汇总合并数据标志设置错误值或空值的显示方式更新数据透视表的数据,数据透视表的高级应用,分组查看数据按日期、地区、商品类别等分组设置字段的显示方式差异、差异百分比占同行、同列、总合百分比累计值显示纵向差异比较添加自定义计算字段或计算项,6.2.3创建销售数据透视图创建数据透视图使用数据透视图编辑数据透视图6.2.4快速合并计算多重区域的销售数据【例6-5】,6.3绘制销售图表,6.3.1组合销售图表【例6-6】6.3.2动态销售图表【例6-7】,7量本利分析,7.1本量利分析【例7-1】单品种保本点的计算【例7-2】多品种保本点的计算【例7-3】绘制盈亏平衡分析图7.2利润敏感性分析【例7-4】7.3确保实现目标利润的措施分析【例7-5】,8财务报表分析模型,8.1资产负债表分析模型8.2利润表分析模型8.3现金流量表分析模型8.4财务比率分析模型8.5杜邦系统分析模型8.6综合财务分析模型8.7绘制财务指标雷达图,8财务报表分析模型,【例8-1】-【例8-7】财务报表分析模型制作模型的封面通过本例学习插入图形对象、插入艺术字、插入超链接AND、ISBLANK等函数制作雷达图录制宏,9长期投资决策,9.1投资项目现金流量的计算9.2评价投资项目的指标与方法9.3几种不同类型的长期投资决策9.4投资项目的风险分析,9.1投资项目现金流量的计算,9.1.1投资项目现金流量的构成与计算9.1.2折旧的计算方法与折旧函数9.1.3绘制现金流量图,9.1.1投资项目的现金流量构成与计算,现金流量的构成初始现金流量营业现金流量终结现金流量,现金流量的概念现金流入量现金流出量现金净流量,初始和终结现金流量,初始现金流量固定资产投资流动资产投资其他投资费用原有固定资产的变价净收入,终结现金流量固定资产的残值净收入或变价净收入原来垫支的流动资金回收额,营业现金流量,年营业现金净流量=营业收入付现成本所得税年营业现金流量=净利润+折旧年营业现金流量=税后收入税后成本+税负减少=营业收入（1税率）付现成本（1税率）+折旧税率其中：折旧税率被称为“折旧抵税额”或“税收档板”,9.1.2折旧的计算方法与折旧函数,直线折旧法SLN函数平均年限法工作量法加速折旧法余额递减法DB函数双倍余额递减法DDB和VDB函数年数总和法SYD函数,9.1.3绘制现金流量图,【例9-1】计算折旧计算净现金流量绘制现金流量图,9.2评价长期投资项目的指标和方法,9.2.1计算平均报酬率9.2.2计算投资回收期9.2.3计算净现值9.2.4计算获利指数9.2.5计算内部收益率,9.2.1计算平均报酬率,平均报酬率的计算公式【例9-2】平均报酬率计算与查询,相关函数,AVERAGE函数ABS函数VLOOKUP函数MATCH函数,9.2.2计算投资回收期,静态投资回收期在不考虑资金时间价值的情况下，用投资项目经营期的净现金流量回收初始投资所用的时间。在初始投资以后未来各期净现金流量相等的情况下，静态投资回收期的计算公式为：在初始投资以后未来各期净现金流量不相等的情况下，静态投资回收期的计算公式为：式中：n为累计净现金流量第一次出现正值的年份,9.2.2计算投资回收期,动态投资回收期在考虑资金时间价值的情况下，用投资项目经营期的净现金流量的现值回收初始投资所用的时间。计算公式为：,式中：n为累计净现金流量现值第一次出现正值的年份,9.2.2计算投资回收期,【例9-3】NPER函数基于固定利率及等额分期付款方式，返回某项投资（或贷款）的总期数。公式为=NPER（rate，pmt，pv，fv，type）COUNTIF函数计算给定区域内满足特定条件的单元格的数目。公式为：=COUNTIF(range,criteria),9.2.3计算净现值,净现值的计算公式为：式中：CIt为投资项目第t年的现金流入量，COt为投资项目第t年的现金流出量，NCFt为投