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、业务量、成本和利润之间的关系进行分析 产销平衡假定 生产量=销售量 成本按性态（习性）分类 变动成本总额随业务量正比例变动 固定成本总额不随业务量变动,混合成本 总额随业务量变化，但不成正比例变化。 分解混合成本的常用方法 高低点法 回归分析法 使用相关函数分解混合成本 SLOPE函数：计算回归直线的斜率 INTERCEPT函数：计算回归直线的截距 使用绘图工具分解混合成本 绘制散点图 添加趋势线 【例1-1 】分解混合成本,1.3 单品种盈亏平衡点的计算,盈亏平衡点 又称保本点 是指不亏不盈即利润为0的那一点 盈亏平衡点的表示方法 盈亏平衡点销售量（保本点销售量） 盈亏平衡点销售额（保本点销
3、售额） 盈亏平衡点作业率（保本点作业率）,1.3 单品种盈亏平衡点的计算,利润=销售收入变动总成本固定成本=销售量(单价单位变动成本) 固定成本 当利润=0时，可得：盈亏平衡点销售量=固定成本/(单价单位变动成本)盈亏平衡点销售额=盈亏平衡点销售量单价,1.3 单品种盈亏平衡点的计算,其他相关指标：单位边际贡献=单价单位变动成本边际贡献总额=销售收入变动总成本=销售量(单价单位变动成本)边际贡献率=单位边际贡献/单价=边际贡献总额/销售收入,1.3 单品种盈亏平衡点的计算,其他相关指标：安全边际量=正常销售量盈亏平衡点销售量安全边际额=正常销售额盈亏平衡点销售额安全边际率=安全边际量/正常销售
4、量=安全边际额/正常销售额 盈亏临界点作业率=盈亏平衡点销售量/正常销售量=1安全边际率,1.3 单品种盈亏平衡点的计算,【例1-2 】单品种盈亏平衡点的计算 直接利用公式计算 运用单变量求解工具 2003:工具/单变量求解 2007:数据/数据工具/假设分析/单变量求解,1.4 多品种盈亏平衡点的计算,多品种盈亏平衡点的计算方法 加权平均法 顺序法 分算法 联合单位法 主要产品法,1.4 多品种盈亏平衡点的计算,加权平均法以各种产品的预计销售收入占总收入的比重为权数，确定企业加权平均的综合边际贡献率，再计算盈亏平衡点。 【例1-3】多品种盈亏分析加权平均法 SUM函数：无条件求和 SUMPR
5、ODUCT函数:返回数组间对应元素乘积之和 输入数组公式：【Shift+Ctrl+Enter】 单变量求解,1.4 多品种盈亏平衡点的计算,顺序法按照事先规定的品种顺序，依次用各种产品的边际贡献补偿企业的固定成本，直至全部固定成本由产品的边际贡献补偿完为止，从而得到盈亏平衡点。 【例1-4】多品种盈亏分析顺序法 IF函数：根据逻辑判断的真假值返回不同的结果 AND函数：进行逻辑与条件的判断 ABS函数：返回数值的绝对值,1.4 多品种盈亏平衡点的计算,分算法将企业的全部固定成本按一定标准分配给各种产品，然后再对每种产品按单品种的情况分别进行量本利分析。 联合单位法根据各种产品间产销量的最小比例
6、，将其作为一个联合单位，确定每一联合单位的单价、单位变动成本，进行量本利分析。 主要产品法在多种产品中，将边际贡献所占比重较大的产品作为主要产品，以该产品为主按单品种方法进行量本利分析。,1.5 实现目标利润的单一措施分析,逐个考虑的单一因素 销售量 单价 单位变动成本 固定成本 【例1-5】实现目标利润的单一措施分析 单变量求解,1.6 目标利润的多因素敏感性分析,目标利润的影响因素 销售量、单价、单位变动成本、固定成本 【例1-6】目标利润的多因素敏感性分析 设置滚动条控件 2003：【视图】/【工具栏】/【窗体】 2007：【开发工具】/【控件】/【表单控件】,1.7 绘制动态盈亏分析面
7、积图,1.8 绘制动态盈亏分析折线图,2 Excel在资金时间价值计算中的应用,2.1 单笔现金流的终值计算 2.2 单笔现金流的现值计算 2.3 年金终值的计算 2.4 年金现值的计算 2.5 求解利率 2.6 名义年利率与有效年利率计算 2.7 编制分期还款计划表 2.8 分期还款额的敏感性分析,2.1 单笔现金流的终值计算,单利终值的计算式中：FS 为单利终值；P 为现在的一笔资金；iS为单利年利率；n 为计息期限。,2.1 单笔现金流的终值计算,复利终值的计算公式或 式中：P 为现在的一笔收款或付款；i 为复利年利率；n 为年限；F为复利终值；FVIFi,n=(1+i)n为复利终值系数
8、。,2.1 单笔现金流的终值计算,【例2-1】单利与复利终值的对比分析 FV函数基于固定利率及等额分期付款方式返回某项投资的未来值。公式为：= FV(rate,nper,pmt,pv,type) 建立与复制公式 显示公式与显示计算结果 公式审核,2.1 单笔现金流的终值计算,创建图表,2.1 单笔现金流的终值计算,【例2-2】系列不规则现金流的终值 使用FV函数计算 运用数组公式计算【例2-3】浮动利率情况下的终值计算 FVSCHEDULE函数：基于一系列复利利率计算未来值。公式为=FVSCHEDULE(principal,schedule)式中：principal为现值；schedule为利
9、率数组。,2.2 单笔现金流的现值计算,单利现值式中：P为现值，F为未来值，is为单利年利率，n为期限。复利现值或：式中：PVIFi,n为复利现值系数,2.2 单笔现金流的现值计算,【例2-4】单利与复利现值的比较分析 PV函数基于固定利率及等额分期付款方式返回某项投资的现值。公式为：= PV(rate,nper,pmt,fv,type) 绘制图表/使用控件 【例2-5】系列不规则现金流的现值 使用PV函数计算 运用数组公式计算,2.3 年金终值的计算,年金每期等额发生的收付款系列 普通年金：每期期末发生（后付年金） 先付年金：每期期初发生（即付年金） 延期年金：一定时期后才开始发生的年金 永
10、续年金：无限期发生的年金,2.3 年金终值的计算,【例2-6】年金终值的计算 普通年金的终值计算 先付年金的终值计算 年金终值的选择计算,2.4 年金现值的计算,【例2-7】年金现值的计算 普通年金的现值计算 先付年金的现值计算 年金现值的选择计算,2.5 求解利率,在已知现值、终值、年金三个参数中的任意两个参数的情况下，可求解未知的利率 RATE函数返回未来款项的各期利率。公式为： = RATE(nper,pmt,pv,fv,type,guess),2.5 求解利率,【例2-8】求解利率 假定您有一位刚好1岁的孩子，您希望在17年后资助他60,000元上大学。 如果您目前只有5,000可用来
11、投资，那么您需要找到一个报酬率为多高的投资项目，才能实现您的这个心愿？ 如果您每年年末都有2000元可用于投资，那么需要多高的投资报酬率才够用？,2.6 名义年利率与有效年利率计算,若给定的年利率为i，每年计息m次，那么现在的一笔资金P在n年末的终值为：,在这种情况下，给定的年利率i为名义年利率，按每年实际获得的利息计算的年利率为有效年利率。,2.6 名义年利率与有效年利率计算,有效年利率r与名义年利率i之间的关系为：式中m为每年计息次数连续计息情况下：式中e为自然对数的底数，e 2.71828,2.6 名义年利率与有效年利率计算,【例2-9】有效年利率与名义年利率的计算 EFFECT函数计算
12、有效年利率 NOMINAL函数计算名义年利率 2003：需要首先加载分析工具库 EXP函数返回 e 的 n 次幂,2.7 编制分期还款计划表,【例2-10】编制分期还款计划表 还本付息方式 等额本金法：分期等额还本余额计息 等额摊还法：分期等额还本付息 还本付息的频率 按年偿还 按月偿还,2.7 编制分期还款计划表,基于固定利率及等额分期付款方式计算 PMT函数：返回投资或贷款的每期付款额。=PMT(rate,nper,pv,fv,type) IPMT函数：返回投资或贷款在某一给定期次内的利息偿还额。=PPMT(rate,per,nper,pv,fv,type) PPMT函数：返回投资或贷款在
13、某一给定期次内的本金偿还。=IPMT(rate,per,nper,pv,fv,type),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,typ
14、e),2.8 分期等额还款额的敏感性分析,【例2-11】分期还款额的模拟运算分析 单因素/双因素敏感性分析 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进行财务预
15、测的方法 利用绘图工具预测 利用相关函数预测 利用数据分析工具预测,3.1 财务预测,【例3-1】利用绘图工具预测 创建散点图 添加趋势线,3.1 财务预测,常用的线性趋势预测函数 INTERCEPT函数求回归直线的截距 SLOPE求回归直线的斜率。 TREND函数返回一条线性回归拟合线的值。【例3-2】利用相关函数预测,3.1 财务预测,利用数据分析工具预测 2003：工具/加载宏/分析工具库 2007：Excel选项/加载项/转到/分析工具库 【例3-3】基于移动平均法的销售预测 2003：工具/数据分析/移动平均 2007：数据/分析/数据分析/移动平均 【例3-4】基于回归分析法的销售
16、预测 2003：工具/数据分析/回归 2007：数据/分析/数据分析/回归,3.2 固定资产折旧的计算,固定资产折旧的计算方法 直线折旧法 平均年限法 工作量法 加速折旧法 余额递减法 双倍余额递减法 年数总和法,直线折旧法,平均年限法工作量法,某期折旧额 = 该期实际工作量每单位工作量折旧额,直线折旧函数与折旧计算模型,直线折旧函数SLN函数 SLN函数：返回一项资产每期的直线折旧额。公式为：= SLN(cost,salvage,life)【例3-5】采用直线折旧法计算折旧模型,加速折旧法,余额递减法双倍余额递减法年数总和法,加速折旧函数与折旧计算模型,定率余额递减法折旧函数DB函数 DB函
17、数：使用固定余额递减法，计算一笔资产在给定期间内的折旧值。公式为：=DB(cost,salvage,life,period,month)年数总和法折旧计算函数SYD函数 SYD函数：返回某项资产按年限总和折旧法计算的某期折旧额。公式为： = SYD(cost,salvage,life,per),加速折旧函数与折旧计算模型,双倍余额递减法DDB函数和VDB函数 DDB函数：使用双倍余额递减法或其他指定方法，计算一笔资产在给定期间内的折旧值。公式为= DDB(cost, salvage, life, period, factor)当按直线法计算的折旧值大于按余额递减计算的折旧值时，如果希望转换到直
18、线折旧法，应使用 VDB 函数。 VDB函数：使用双倍余额递减法或其他指定的方法，返回指定的任何期间内的资产折旧值。公式为=VDB(cost,salvage,life,start_period,end_period,factor,no_switch)【例3-6】采用加速折旧法计算折旧模型,折旧选择计算模型,【例3-7】 使用列表框控件 IF函数的应用 加速折旧函数的应用,3.3 投资项目的现金流量计算,初始现金流量 营业现金流量 终结现金流量,3.3 投资项目的现金流量计算,初始现金流量 固定资产投资 流动资产投资 其他投资费用 原有固定资产的变价净收入,终结现金流量 固定资产的残值净收入或变
19、价净收入 原来垫支的流动资金回收额,3.3 投资项目的现金流量计算,每年营业净现金流量的计算方法： 年营业净现金流量=营业收入付现成本所得税 年营业现金流量=净利润+折旧 =(营业收入付现成本折旧)(1税率)+折旧 =营业收入(1税率)付现成本(1税率)+折旧税率 =税后营业收入税后付现成本+折旧抵税额,3.3 投资项目的现金流量计算,【例3-8】 运用SLN函数 运用数组公式 绘制现金流量图,3.4 计算平均报酬率,平均报酬率（ARR） 是投资项目寿命周期内平均的年投资报酬率。计算公式为：【例3-9】平均报酬率计算与查询模型,3.4 计算平均报酬率,相关函数 AVERAGE函数计算给定参数的
20、算术平均值。公式为：= 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) VLOOKUP函数在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。公式为： = VLOOKUP(lookup_value，table_array，c
21、ol_index_num，range_lookup),3.4 计算平均报酬率,相关函数 MATCH函数返回在指定方式下与指定数值匹配的数组中元素的相应位置。公式为： =MATCH(lookup_value,lookup_array,match_type) INDEX函数返回表格或区域中的数值或对数值的引用。该函数有以下两种形式： 返回数组中指定单元格或单元格数组的数值。公式为： = INDEX(array,row_num,column_num) 返回引用中指定的单元格。公式为： =INDEX(reference,row_num,column_num,area_num),3.5 计算投资回收期,
22、静态投资回收期在不考虑资金时间价值的情况下，用投资项目经营期的净现金流量回收初始投资所用的时间。 在初始投资以后未来各期净现金流量相等的情况下：在初始投资以后未来各期净现金流量不相等的情况下：式中：n为累计净现金流量第一次出现正值的年份,3.5 计算投资回收期,动态投资回收期在考虑资金时间价值的情况下，用投资项目经营期的净现金流量的现值回收初始投资所用的时间。计算公式为：,式中：n为累计净现金流量现值第一次出现正值的年份,3.5 计算投资回收期,相关函数 NPER函数基于固定利率及等额分期付款方式，返回某项投资（或贷款）的总期数。公式为: = NPER（rate，pmt，pv，fv，type）
23、 COUNTIF函数计算给定区域内满足特定条件的单元格的数目。公式为： = COUNTIF(range,criteria) 【例3-10】,3.6 计算净现值,净现值的计算公式为：式中：CIt为投资项目第t年的现金流入量，COt为投资项目第t年的现金流出量，NCFt为投资项目第t年的净现金流量，i为贴现率，n为投资项目的寿命期。若初始投资为K，且在投资期初一次投入，则：,3.6 计算净现值,相关函数 NPV函数基于一系列现金流和固定的各期贴现率，返回一项投资的净现值。这里的投资净现值是指未来各期现金流的现值总和。公式为： = NPV(rate,value1,value2,.) XNPV函数返回
24、一组现金流的净现值，这些现金流不一定定期发生。该函数的公式为： =XNPV(rate,values,dates)【例3-11】,3.7 计算获利指数与现值指数,获利指数的计算公式为：式中：PI为获利指数；m为投资期；Kt为投资期内第t年的净现金流出量；其他各符号的含义如前所述。 如果项目的投资额在期初一次性发生，并且初始投资额为K，则：【例3-12】,3.8 计算内部收益率,内部收益率（ IRR ）使投资项目的净现值为零时的贴现率，计算公式为：修正内部收益率（ MIRR ）的计算公式为：式中：CPt为第t年正的净现金流量；CNt为第t年负的净现金流量；n为项目的寿命期；r为对正的净现金流量进行
25、再投资的年收益率；k为对负的净现金流量进行贴现的资金成本率；MIRR为项目的修正内部收益率。,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
26、,guess) 【例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：数
27、据/数据分析/规划求解,4 Excel在投资风险分析中的应用,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为项目的初
28、始投资；n为项目的寿命期；t为年份。 【例4-1】投资项目盈亏平衡分析,4.2 基于净现值的敏感性分析,影响项目净现值的因素 销售量、销售单价 单位变动成本、付现固定成本 初始投资额、寿命期 基准收益率 【例4-2】单因素敏感性分析 2003：数据/模拟运算表 2007：数据/数据工具/假设分析/数据表,4.2 基于净现值的敏感性分析,【例4-3】双因素敏感性分析 2003：数据/模拟运算表 2007：数据/数据工具/假设分析/数据表【例4-4】多因素敏感性分析 2003：窗体控件 2007：表单控件,4.3 基于净现值的情境分析,情境分析 乐观情境 最可能情境 悲观情境 【例4-5】基于净现
29、值的情境分析 建立名称公式 运用方案管理器 2003：工具/方案 2007：数据/数据工具/假设分析/方案管理器,4.4 投资项目的风险度量与处置,度量投资项目风险的有关计算公式：式中：E为投资收益分布的期望值； 为投资收益分布的标准差；V为变差系数（标准离差率）；Kt为第t种情况下的投资收益（率）；Pt为第t种情况出现的概率；n为出现可能情况的种数。,4.4 投资项目的风险度量与处置,【例4-6】投资风险的度量 相关函数 SQRT函数计算一个正数的正平方根。公式为：=SQRT(number) NORMDIST函数返回指定平均值和标准偏差的正态分布函数，公式为： =NORMDIST(x,mea
30、n,standard_dev,cumulative),4.4 投资项目的风险度量与处置,【例4-7】投资风险的处置 根据标准离差率的大小确定相应的贴现率 相关函数 VLOOKUP函数 NPV函数,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函
31、数：返回由文本字符串指定的引用 条件格式设置 ROW函数：返回所引用的单元格的行号 MOD函数：返回两数相除的余数,5.2 处理计算误差,【例5-2】处理计算误差 ROUND函数四舍五入 IF函数逻辑判断 MAX函数取最大值,5.3 计算收入与利润增长率,【例5-3】收入与利润增长率计算 AVERAGE函数：返回参数的算数平均值 GEOMEAN函数：返回正数数组或区域的几何平均值 ABS函数：取绝对值,5.4 规范整理与快速核对数据表,【例5-4】规范整理数据表 数据分列 快速拆分数据 快速删除数据表中的空行 高效填充缺失的数据 【例5-5】快速核对收款数据 使用COUNTIF函数核对 运用数
32、据透视表工具核对,5.5 高效汇总财务数据,【例5-6】编制付款合同执行进度汇总表 定义名称 设置数据有效性 创建列表 运用条件求和函数,5.5 高效汇总财务数据,【例5-7】快速汇总多个工作表的财务数据 使用SUM函数 运用合并计算工具 运用数据透视表工具,5.6 应付账款到期日计算与提前提醒,【例5-8】应付账款到期日计算与提前提醒 计算应付账款到期日 EDATE函数：计算所指定月数之前或之后的日期 EOMONTH函数：计算指定日期到月底的天数 应付账款到期日提前提醒 运用条件格式设置,5.7 存货的ABC分类,存货的分类 A类存货：关键的少数 C类存货：不重要的多数 B类存货：居中的类别
33、 常见的分类标准,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函数：计算两个日期之间间隔的年、月或天数 制作固定资产数据汇总表,
