Excel在财务管控中的扩展应用课件.ppt

1、Excel在财务管控中的扩展应用目录1 Excel在量本利分析中的应用2 Excel在资金时间价值计算中的应用3 Excel在长期投资决策中的应用4 Excel在投资风险分析中的应用5 Excel在日常财务管理中的应用1 Excel在量本利分析中的应用1.1 量本利分析的基本假定1.2 混合成本的分解1.3 单品种盈亏平衡点的计算1.4 多品种盈亏平衡点的计算1.5 实现目标利润的单一措施分析1.6 目标利润的多因素敏感性分析1.7 绘制动态盈亏分析面积图1.8 绘制动态盈亏分析折线图 1.1 量本利分析的基本假定量本利分析又称本量利分析（CVP分析）是指对业务量、成本和利润之间的关系进行分析

2、产销平衡假定生产量=销售量成本按性态（习性）分类变动成本总额随业务量正比例变动固定成本总额不随业务量变动1.2 混合成本的分解混合成本总额随业务量变化，但不成正比例变化。分解混合成本的常用方法高低点法回归分析法使用相关函数分解混合成本SLOPE函数：计算回归直线的斜率 INTERCEPT函数：计算回归直线的截距使用绘图工具分解混合成本绘制散点图添加趋势线【例1-1】分解混合成本1.3 单品种盈亏平衡点的计算盈亏平衡点又称保本点是指不亏不盈即利润为0的那一点盈亏平衡点的表示方法盈亏平衡点销售量（保本点销售量）盈亏平衡点销售额（保本点销售额）盈亏平衡点作业率（保本点作业率）1.3 单品种盈亏平衡点

3、的计算利润=销售收入变动总成本固定成本 =销售量(单价单位变动成本)固定成本当利润=0时，可得：盈亏平衡点销售量 =固定成本/(单价单位变动成本)盈亏平衡点销售额 =盈亏平衡点销售量单价1.3 单品种盈亏平衡点的计算其他相关指标：单位边际贡献=单价单位变动成本 边际贡献总额=销售收入变动总成本 =销售量(单价单位变动成本)边际贡献率=单位边际贡献/单价 =边际贡献总额/销售收入1.3 单品种盈亏平衡点的计算其他相关指标：安全边际量=正常销售量盈亏平衡点销售量 安全边际额=正常销售额盈亏平衡点销售额 安全边际率=安全边际量/正常销售量 =安全边际额/正常销售额盈亏临界点作业率 =盈亏平衡点销售量

4、/正常销售量 =1安全边际率1.3 单品种盈亏平衡点的计算【例1-2】单品种盈亏平衡点的计算直接利用公式计算运用单变量求解工具2003:工具/单变量求解2007:数据/数据工具/假设分析/单变量求解1.4 多品种盈亏平衡点的计算多品种盈亏平衡点的计算方法加权平均法顺序法分算法联合单位法主要产品法 1.4 多品种盈亏平衡点的计算加权平均法以各种产品的预计销售收入占总收入的比重为权数，确定企业加权平均的综合边际贡献率，再计算盈亏平衡点。【例1-3】多品种盈亏分析加权平均法SUM函数：无条件求和SUMPRODUCT函数:返回数组间对应元素乘积之和输入数组公式：【Shift+Ctrl+Enter】单变

5、量求解1.4 多品种盈亏平衡点的计算顺序法按照事先规定的品种顺序，依次用各种产品的边际贡献补偿企业的固定成本，直至全部固定成本由产品的边际贡献补偿完为止，从而得到盈亏平衡点。【例1-4】多品种盈亏分析顺序法IF函数：根据逻辑判断的真假值返回不同的结果AND函数：进行逻辑与条件的判断ABS函数：返回数值的绝对值1.4 多品种盈亏平衡点的计算分算法将企业的全部固定成本按一定标准分配给各种产品，然后再对每种产品按单品种的情况分别进行量本利分析。联合单位法根据各种产品间产销量的最小比例，将其作为一个联合单位，确定每一联合单位的单价、单位变动成本，进行量本利分析。主要产品法在多种产品中，将边际贡献所占比

6、重较大的产品作为主要产品，以该产品为主按单品种方法进行量本利分析。1.5 实现目标利润的单一措施分析逐个考虑的单一因素销售量单价单位变动成本固定成本【例1-5】实现目标利润的单一措施分析单变量求解1.6 目标利润的多因素敏感性分析目标利润的影响因素销售量、单价、单位变动成本、固定成本【例1-6】目标利润的多因素敏感性分析设置滚动条控件2003：【视图】/【工具栏】/【窗体】2007：【开发工具】/【控件】/【表单控件】1.7 绘制动态盈亏分析面积图1.8 绘制动态盈亏分析折线图2 Excel在资金时间价值计算中的应用2.1 单笔现金流的终值计算2.2 单笔现金流的现值计算2.3 年金终值的计算

7、2.4 年金现值的计算2.5 求解利率2.6 名义年利率与有效年利率计算2.7 编制分期还款计划表2.8 分期还款额的敏感性分析 2.1 单笔现金流的终值计算单利终值的计算式中：FS 为单利终值；P 为现在的一笔资金；iS为单利年利率；n 为计息期限。)1(niPniPPFSSS2.1 单笔现金流的终值计算复利终值的计算公式 或 式中：P 为现在的一笔收款或付款；i 为复利年利率；n 为年限；F为复利终值；FVIFi,n=(1+i)n为复利终值系数。niPF)1(niFVIFPF,2.1 单笔现金流的终值计算【例2-1】单利与复利终值的对比分析FV函数基于固定利率及等额分期付款方式返回某项投资

8、的未来值。公式为：=FV(rate,nper,pmt,pv,type)建立与复制公式显示公式与显示计算结果公式审核2.1 单笔现金流的终值计算 创建图表2.1 单笔现金流的终值计算【例2-2】系列不规则现金流的终值使用FV函数计算运用数组公式计算【例2-3】浮动利率情况下的终值计算 FVSCHEDULE函数：基于一系列复利利率计算未来值。公式为 =FVSCHEDULE(principal,schedule)式中：principal为现值；schedule为利率数组。2.2 单笔现金流的现值计算单利现值 式中：P为现值，F为未来值，is为单利年利率，n为期限。复利现值 或：式中：PVIFi,n为

9、复利现值系数)1/(niFPsniFP)1/(niPVIFFP,2.2 单笔现金流的现值计算【例2-4】单利与复利现值的比较分析 PV函数基于固定利率及等额分期付款方式返回某项投资的现值。公式为：=PV(rate,nper,pmt,fv,type)绘制图表【例2-5】系列不规则现金流的现值使用PV函数计算运用数组公式计算2.3 年金终值的计算年金每期等额发生的收付款系列普通年金：每期期末发生（后付年金）先付年金：每期期初发生（即付年金）延期年金：一定时期后才开始发生的年金永续年金：无限期发生的年金2.3 年金终值的计算【例2-6】年金终值的计算普通年金的终值计算先付年金的终值计算年金终值的选择

10、计算2.4 年金现值的计算【例2-7】年金现值的计算普通年金的现值计算先付年金的现值计算年金终值或现值的选择计算2.5 求解利率在已知现值、终值、年金三个参数中的任意两个参数的情况下，可求解未知的利率RATE函数返回未来款项的各期利率。公式为：=RATE(nper,pmt,pv,fv,type,guess)2.5 求解利率【例2-8】求解利率假定您有一位刚好1岁的孩子，您希望在17年后资助他60,000元上大学。如果您目前只有5,000可用来投资，那么您需要找到一个报酬率为多高的投资项目，才能实现您的这个心愿？如果您每年年末都有2000元可用于投资，那么需要多高的投资报酬率才够用？2.6 名义

11、年利率与有效年利率计算若给定的年利率为i，每年计息m次，那么现在的一笔资金P在n年末的终值为：mnmiPF)1(在这种情况下，给定的年利率在这种情况下，给定的年利率i为名义年利为名义年利率，按每年实际获得的利息计算的年利率为率，按每年实际获得的利息计算的年利率为有效年利率。有效年利率。2.6 名义年利率与有效年利率计算有效年利率r与名义年利率i之间的关系为：式中m为每年计息次数连续计息情况下：式中e为自然对数的底数，e 2.718281)1(mmir2.6 名义年利率与有效年利率计算【例2-9】有效年利率与名义年利率的计算EFFECT函数计算有效年利率NOMINAL函数计算名义年利率2003：

12、需要首先加载分析工具库EXP函数返回 e 的 n 次幂2.7 编制分期还款计划表【例2-10】编制分期还款计划表还本付息方式等额本金法：分期等额还本余额计息等额摊还法：分期等额还本付息还本付息的频率按年偿还按月偿还 2.7 编制分期还款计划表基于固定利率及等额分期付款方式计算PMT函数：返回投资或贷款的每期付款额。=PMT(rate,nper,pv,fv,type)IPMT函数：返回投资或贷款在某一给定期次内的利息偿还额。=IPMT(rate,per,nper,pv,fv,type)PPMT函数：返回投资或贷款在某一给定期次内的本金偿还。=PPMT(rate,per,nper,pv,fv,ty

13、pe)2.7 编制分期还款计划表基于固定利率及等额分期付款方式计算CUMIPMT函数：返回一笔贷款在给定的start_period到end_period期间累计偿还的利息数额。公式为：=CUMIPMT(rate,nper,pv,start_period,end_period,type)CUMPRINC函数：返回一笔贷款在给定的start_period到end_period 期间累计偿还的本金数额。公式为：=CUMPRINC(rate,nper,pv,start_period,end_period,type)2.8 分期等额还款额的敏感性分析【例2-11】分期还款额的模拟运算分析单因素/双因素敏

14、感性分析2003：数据/模拟运算表2007：数据/数据工具/假设分析/数据表【例2-12】分期等额还贷计算器多因素敏感性分析使用窗体/表单控件3 Excel在长期投资决策中的应用3.1 财务预测3.2 固定资产折旧的计算3.3 投资项目的现金流量计算3.4 计算平均报酬率3.5 计算投资回收期3.6 计算净现值3.7 计算获利指数3.8 计算内部收益率3.9 互斥投资方案的比较分析3.10 资本限量条件下的投资决策 3.1 财务预测运用Excel进行财务预测的方法利用绘图工具预测利用相关函数预测利用数据分析工具预测3.1 财务预测【例3-1】利用绘图工具预测创建散点图添加趋势线3.1 财务预测

15、常用的线性趋势预测函数INTERCEPT函数求回归直线的截距SLOPE求回归直线的斜率。TREND函数返回一条线性回归拟合线的值。【例3-2】利用相关函数预测3.1 财务预测利用数据分析工具预测2003：工具/加载宏/分析工具库2007：Excel选项/加载项/转到/分析工具库【例3-3】基于移动平均法的销售预测 2003：工具/数据分析/移动平均2007：数据/分析/数据分析/移动平均【例3-4】基于回归分析法的销售预测2003：工具/数据分析/回归2007：数据/分析/数据分析/回归3.2 固定资产折旧的计算固定资产折旧的计算方法直线折旧法平均年限法工作量法加速折旧法余额递减法双倍余额递减

16、法年数总和法直线折旧法平均年限法工作量法预计使用年限预计净残值固定资产原值年折旧额预计总工作量预计净残值固定资产原值每单位工作量折旧额某期折旧额某期折旧额=该期实际工作量该期实际工作量每单位工作量折旧额每单位工作量折旧额 直线折旧函数与折旧计算模型直线折旧函数SLN函数SLN函数：返回一项资产每期的直线折旧额。公式为：=SLN(cost,salvage,life)【例3-5】采用直线折旧法计算折旧模型 加速折旧法余额递减法双倍余额递减法年数总和法年固定折旧率该年初固定资产净值某年折旧额预计使用年限某年固定资产帐面净值年折旧率某年固定资产账面净值年折旧额2预计使用年限累计折旧固定资产原值2)(n

17、固定资产原始价值固定资产预计净残值年固定折旧率1年折旧率预计残值）（固定资产原值年折旧额211）（折旧年限折旧年限已使用年限）（折旧年限年折旧率加速折旧函数与折旧计算模型定率余额递减法折旧函数DB函数DB函数：使用固定余额递减法，计算一笔资产在给定期间内的折旧值。公式为：=DB(cost,salvage,life,period,month)年数总和法折旧计算函数SYD函数SYD函数：返回某项资产按年限总和折旧法计算的某期折旧额。公式为：=SYD(cost,salvage,life,per)加速折旧函数与折旧计算模型双倍余额递减法DDB函数和VDB函数DDB函数：使用双倍余额递减法或其他指定方法

18、，计算一笔资产在给定期间内的折旧值。公式为=DDB(cost,salvage,life,period,factor)当按直线法计算的折旧值大于按余额递减计算的折旧值时，如果希望转换到直线折旧法，应使用 VDB 函数。VDB函数：使用双倍余额递减法或其他指定的方法，返回指定的任何期间内的资产折旧值。公式为=VDB(cost,salvage,life,start_period,end_period,factor,no_switch)【例3-6】采用加速折旧法计算折旧模型折旧选择计算模型【例3-7】使用列表框控件IF函数的应用折旧函数的应用 3.3 投资项目的现金流量计算初始现金流量营业现金流量终结

19、现金流量3.3 投资项目的现金流量计算初始现金流量固定资产投资流动资产投资其他投资费用原有固定资产的变价净收入v终结现金流量终结现金流量固定资产的残值净收固定资产的残值净收入或变价净收入入或变价净收入原来垫支的流动资金原来垫支的流动资金回收额回收额3.3 投资项目的现金流量计算每年营业净现金流量的计算方法：年营业净现金流量=营业收入付现成本所得税年营业净现金流量=净利润+折旧=(营业收入付现成本折旧)(1税率)+折旧=营业收入(1税率)付现成本(1税率)+折旧税率=税后营业收入税后付现成本+折旧抵税额3.3 投资项目的现金流量计算【例3-8】运用SLN函数运用数组公式绘制现金流量图3.4 计算

20、平均报酬率平均报酬率（ARR）是投资项目寿命周期内平均的年投资报酬率。计算公式为：【例3-9】平均报酬率计算与查询模型初始投资年均净现金流量平均报酬率 3.4 计算平均报酬率相关函数AVERAGE函数计算给定参数的算术平均值。公式为：=AVERAGE(number1,number 2,number N)ABS函数返回数字的绝对值。公式为：=ABS(number)3.4 计算平均报酬率相关函数MAX函数给定参数表中的最大值。公式为：=MAX(number1,number 2,number N)MIN函数给定参数表中的最小值。公式为：=MIN(number1,number 2,number N)V

21、LOOKUP函数在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。公式为：=VLOOKUP(lookup_value，table_array，col_index_num，range_lookup)3.4 计算平均报酬率相关函数MATCH函数返回在指定方式下与指定数值匹配的数组中元素的相应位置。公式为：=MATCH(lookup_value,lookup_array,match_type)INDEX函数返回表格或区域中的数值或对数值的引用。该函数有以下两种形式：返回数组中指定单元格或单元格数组的数值。公式为：=INDEX(array,row_num,column_

22、num)返回引用中指定的单元格。公式为：=INDEX(reference,row_num,column_num,area_num)3.5 计算投资回收期静态投资回收期在不考虑资金时间价值的情况下，用投资项目经营期的净现金流量回收初始投资所用的时间。在初始投资以后未来各期净现金流量相等的情况下：在初始投资以后未来各期净现金流量不相等的情况下：式中：n为累计净现金流量第一次出现正值的年份年净现金流量初始投资额静态投资回收期年的净现金流量第年末尚未回收的投资）（静态投资回收期n)1n(1n3.5 计算投资回收期动态投资回收期在考虑资金时间价值的情况下，用投资项目经营期的净现金流量的现值回收初始投资所

23、用的时间。计算公式为：年净现金流量的现值第年末尚未回收的投资）（动态投资回收期n)1n(1n式中：式中：n为累计净现金流量现值第一次出现正值的年份为累计净现金流量现值第一次出现正值的年份3.5 计算投资回收期相关函数NPER函数基于固定利率及等额分期付款方式，返回某项投资（或贷款）的总期数。公式为:=NPER(rate,pmt,pv,fv,type)COUNTIF函数计算给定区域内满足特定条件的单元格的数目。公式为：=COUNTIF(range,criteria)【例3-10】计算投资回收期3.6 计算净现值净现值的计算公式为：式中：CIt为投资项目第t年的现金流入量，COt为投资项目第t年的

24、现金流出量，NCFt为投资项目第t年的净现金流量，i为贴现率，n为投资项目的寿命期。若初始投资为K，且在投资期初一次投入，则：ntttntttntttiNCFiCOiCINPV000)1()1()1(KiNCFNPVnttt1)1(3.6 计算净现值相关函数NPV函数基于一系列现金流和固定的各期贴现率，返回一项投资的净现值。这里的投资净现值是指未来各期现金流的现值总和。公式为：=NPV(rate,value1,value2,.)XNPV函数返回一组现金流的净现值，这些现金流不一定定期发生。该函数的公式为：=XNPV(rate,values,dates)【例3-11】计算净现值3.7 计算获利指

25、数获利指数的计算公式：式中：PI为获利指数；m为投资期；Kt为投资期内第t年的净现金流出量；其他各符号的含义如前所述。如果项目的投资额在期初一次性发生，并且初始投资额为K，则：【例3-12】mtttnmtttiKiNCFPI01)1(/)1(KiNCFPInttt/)1(13.8 计算内部收益率内部收益率（IRR）使投资项目的净现值为零时的贴现率，计算公式为：修正内部收益率（MIRR）的计算公式为：式中：CPt为第t年正的净现金流量；CNt为第t年负的净现金流量；n为项目的寿命期；r为对正的净现金流量进行再投资的年收益率；k为对负的净现金流量进行贴现的资金成本率；MIRR为项目的修正内部收益率

26、。ntttIRRNCFNPV00)1(ntntntkttnrtMIRRPVIFCNFVIFCP00,0)1()(3.8 计算内部收益率相关的函数RATE函数返回未来款项的各期利率。公式为=RATE(nper,pmt,pv,fv,type,guess)IRR函数返回由数值代表的一组现金流的内部收益率。公式为：=IRR(values,guess)MIRR函数返回某一连续期间内现金流的修正内部收益率。公式为：=MIRR(values,finance_rate,reinvest_rate)XIRR函数返回一组现金流的内部收益率，这些现金流不一定定期发生。公式为：=XIRR(values,dates,g

27、uess)【例3-13】3.9 互斥投资方案的比较分析寿命期相同的互斥投资比较【例3-14】使用相关函数计算运用单变量求解工具寿命期不同的互斥方案比较最小公倍寿命法（更新链法）【例3-15】LCM函数：返回整数的最小公倍数。公式为：=LCM(number1,number2,.)等值年金法（净年值法）【例3-16】3.10 资本限量条件下的投资决策建立0-1规划模型3.10 资本限量条件下的投资决策【例3-17】资本限量条件下的投资决策SUMPRODUCT函数：返回数组间对应元素的相乘之和规划求解工具的运用首先需要加载宏2003：工具/规划求解2007：数据/数据分析/规划求解4 Excel在投

28、资风险分析中的应用4.1 投资项目的盈亏平衡分析4.2 基于净现值的敏感性分析4.3 基于净现值的情境分析4.4 投资项目的风险度量与处置 4.1 投资项目的盈亏平衡分析会计盈亏平衡点相关指标的计算公式：安全边际率 =（现有产销量-盈亏平衡点产销量）/现有产销量4.1 投资项目的盈亏平衡分析基于净现值的盈亏平衡点（现值盈亏平衡点）产销量应该能够满足下面的公式：式中：Qt为第t年的产销量；pt为第t年的产品单价；vt为第t年的单位变动成本；Fct 为第t年的付现固定成本；T为所得税税率；Dt为第t年的折旧额；i为项目的基准收益率；I为项目的初始投资；n为项目的寿命期；t为年份。【例4-1】投资项

29、目盈亏平衡分析4.2 基于净现值的敏感性分析影响项目净现值的因素销售量、销售单价单位变动成本、付现固定成本初始投资额、寿命期基准收益率【例4-2】单因素敏感性分析2003：数据/模拟运算表2007：数据/数据工具/假设分析/数据表4.2 基于净现值的敏感性分析【例4-3】双因素敏感性分析2003：数据/模拟运算表2007：数据/数据工具/假设分析/数据表【例4-4】多因素敏感性分析2003：窗体控件2007：表单控件4.3 基于净现值的情境分析情境分析乐观情境最可能情境悲观情境【例4-5】基于净现值的情境分析建立名称公式运用方案管理器2003：工具/方案2007：数据/数据工具/假设分析/方案

30、管理器4.4 投资项目的风险度量与处置度量投资项目风险的有关计算公式：式中：E为投资收益分布的期望值；为投资收益分布的标准差；V为变差系数（标准离差率）；Kt为第t种情况下的投资收益（率）；Pt为第t种情况出现的概率；n为出现可能情况的种数。4.4 投资项目的风险度量与处置【例4-6】投资风险的度量相关函数SQRT函数计算一个正数的正平方根。公式为：=SQRT(number)NORMDIST函数返回指定平均值和标准偏差的正态分布函数，公式为：=NORMDIST(x,mean,standard_dev,cumulative)4.4 投资项目的风险度量与处置【例4-7】投资风险的处置根据标准离差率

31、的大小确定相应的贴现率相关函数VLOOKUP函数PV函数5 Excel在日常财务管理中的应用5.1 制作费用报销高效记录表5.2 处理计算误差5.3 计算收入与利润增长率5.4 规范整理与快速核对数据表5.5 高效汇总财务数据5.6 应付账款到期日计算与提前提醒5.7 存货的ABC分类5.8 存货经济订货批量计算5.9 固定资产台账管理 5.1 制作费用报销高效记录表【例5-1】制作费用报销高效记录表数据有效性设置INDIRECT函数：返回由文本字符串指定的引用 条件格式设置ROW函数：返回所引用的单元格的行号MOD函数：返回两数相除的余数5.2 处理计算误差【例5-2】处理计算误差ROUND

32、函数四舍五入IF函数逻辑判断MAX函数取最大值5.3 计算收入与利润增长率【例5-3】收入与利润增长率计算AVERAGE函数：返回参数的算数平均值GEOMEAN函数：返回正数数组或区域的几何平均值ABS函数：取绝对值5.4 规范整理与快速核对数据表【例5-4】规范整理数据表数据分列快速拆分数据快速删除数据表中的空行高效填充缺失的数据【例5-5】快速核对收款数据使用COUNTIF函数核对运用数据透视表工具核对5.5 高效汇总财务数据【例5-6】编制付款合同执行进度汇总表定义名称设置数据有效性创建列表运用条件求和函数5.5 高效汇总财务数据【例5-7】快速汇总多个工作表的财务数据使用SUM函数运用

33、合并计算工具运用数据透视表工具5.6 应付账款到期日计算与提前提醒【例5-8】应付账款到期日计算与提前提醒计算应付账款到期日EDATE函数：计算所指定月数之前或之后的日期 EOMONTH函数：计算指定日期到月底的天数应付账款到期日提前提醒运用条件格式设置5.7 存货的ABC分类存货的分类A类存货：关键的少数C类存货：不重要的多数B类存货：居中的类别常见的分类标准5.7 存货的ABC分类【例5-9】存货的ABC分类COUNTA函数：返回参数列表中非空值的单元格个数COUNTIF函数：计算区域中满足给定条件的单元格的个数绘制两轴线柱形图5.8 存货经济订货批量计算年订储费用经济订货批量 最佳的订货次数 最低的年订储费用 式中：Q为一次订货批量；D为一定时期存货的需求量；A为一次订货费；P为存货单价；K为存货的存储费率；PK即为单位存储费用。5.8 存货经济订货批量计算【例5-10】计算经济订货批量SQRT函数：开平方建立和使用自定义函数2003：工具/宏/Visual Basic编辑器2003：工具/代码/Visual Basic编辑器插入/模块插入/过程5.9 固定资产台账管理【例5-11】固定资产台账管理登记固定资产台账使用“记录单”录入和编辑数据DATEDIF函数：计算两个日期之间间隔的年、月或天数制作固定资产数据汇总表