Excel在财务管控中的扩展应用

Excel在财务管控中的扩展应用,目录,1Excel在量本利分析中的应用2Excel在资金时间价值计算中的应用3Excel在长期投资决策中的应用4Excel在投资风险分析中的应用5Excel在日常财务管理中的应用,1Excel在量本利分析中的应用,1.1量本利分析的基本假定1.2混合成本的分解1.3单品种盈亏平衡点的计算1.4多品种盈亏平衡点的计算1.5实现目标利润的单一措施分析1.6目标利润的多因素敏感性分析1.7绘制动态盈亏分析面积图1.8绘制动态盈亏分析折线图,1.1量本利分析的基本假定,量本利分析又称本量利分析（CVP分析）是指对业务量、成本和利润之间的关系进行分析产销平衡假定生产量=销售量成本按性态（习性）分类变动成本总额随业务量正比例变动固定成本总额不随业务量变动,1.2混合成本的分解,混合成本总额随业务量变化，但不成正比例变化。分解混合成本的常用方法高低点法回归分析法使用相关函数分解混合成本SLOPE函数：计算回归直线的斜率INTERCEPT函数：计算回归直线的截距使用绘图工具分解混合成本绘制散点图添加趋势线【例1-1】分解混合成本,1.3单品种盈亏平衡点的计算,盈亏平衡点又称保本点是指不亏不盈即利润为0的那一点盈亏平衡点的表示方法盈亏平衡点销售量（保本点销售量）盈亏平衡点销售额（保本点销售额）盈亏平衡点作业率（保本点作业率）,1.3单品种盈亏平衡点的计算,利润=销售收入变动总成本固定成本=销售量(单价单位变动成本)固定成本当利润=0时，可得：盈亏平衡点销售量=固定成本/(单价单位变动成本)盈亏平衡点销售额=盈亏平衡点销售量单价,1.3单品种盈亏平衡点的计算,其他相关指标：单位边际贡献=单价单位变动成本边际贡献总额=销售收入变动总成本=销售量(单价单位变动成本)边际贡献率=单位边际贡献/单价=边际贡献总额/销售收入,1.3单品种盈亏平衡点的计算,其他相关指标：安全边际量=正常销售量盈亏平衡点销售量安全边际额=正常销售额盈亏平衡点销售额安全边际率=安全边际量/正常销售量=安全边际额/正常销售额盈亏临界点作业率=盈亏平衡点销售量/正常销售量=1安全边际率,1.3单品种盈亏平衡点的计算,【例1-2】单品种盈亏平衡点的计算直接利用公式计算运用单变量求解工具2003:工具/单变量求解2007:数据/数据工具/假设分析/单变量求解,1.4多品种盈亏平衡点的计算,多品种盈亏平衡点的计算方法加权平均法顺序法分算法联合单位法主要产品法,1.4多品种盈亏平衡点的计算,加权平均法以各种产品的预计销售收入占总收入的比重为权数，确定企业加权平均的综合边际贡献率，再计算盈亏平衡点。【例1-3】多品种盈亏分析加权平均法SUM函数：无条件求和SUMPRODUCT函数:返回数组间对应元素乘积之和输入数组公式：【Shift+Ctrl+Enter】单变量求解,1.4多品种盈亏平衡点的计算,顺序法按照事先规定的品种顺序，依次用各种产品的边际贡献补偿企业的固定成本，直至全部固定成本由产品的边际贡献补偿完为止，从而得到盈亏平衡点。【例1-4】多品种盈亏分析顺序法IF函数：根据逻辑判断的真假值返回不同的结果AND函数：进行逻辑与条件的判断ABS函数：返回数值的绝对值,1.4多品种盈亏平衡点的计算,分算法将企业的全部固定成本按一定标准分配给各种产品，然后再对每种产品按单品种的情况分别进行量本利分析。联合单位法根据各种产品间产销量的最小比例，将其作为一个联合单位，确定每一联合单位的单价、单位变动成本，进行量本利分析。主要产品法在多种产品中，将边际贡献所占比重较大的产品作为主要产品，以该产品为主按单品种方法进行量本利分析。,1.5实现目标利润的单一措施分析,逐个考虑的单一因素销售量单价单位变动成本固定成本【例1-5】实现目标利润的单一措施分析单变量求解,1.6目标利润的多因素敏感性分析,目标利润的影响因素销售量、单价、单位变动成本、固定成本【例1-6】目标利润的多因素敏感性分析设置滚动条控件2003：【视图】/【工具栏】/【窗体】2007：【开发工具】/【控件】/【表单控件】,1.7绘制动态盈亏分析面积图,1.8绘制动态盈亏分析折线图,2Excel在资金时间价值计算中的应用,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为复利终值系数。,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为利率数组。,2.2单笔现金流的现值计算,单利现值式中：P为现值，F为未来值，is为单利年利率，n为期限。复利现值或：式中：PVIFi,n为复利现值系数,2.2单笔现金流的现值计算,【例2-4】单利与复利现值的比较分析PV函数基于固定利率及等额分期付款方式返回某项投资的现值。公式为：=PV(rate,nper,pmt,fv,type)绘制图表/使用控件【例2-5】系列不规则现金流的现值使用PV函数计算运用数组公式计算,2.3年金终值的计算,年金每期等额发生的收付款系列普通年金：每期期末发生（后付年金）先付年金：每期期初发生（即付年金）延期年金：一定时期后才开始发生的年金永续年金：无限期发生的年金,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可用来投资，那么您需要找到一个报酬率为多高的投资项目，才能实现您的这个心愿？如果您每年年末都有2000元可用于投资，那么需要多高的投资报酬率才够用？,2.6名义年利率与有效年利率计算,若给定的年利率为i，每年计息m次，那么现在的一笔资金P在n年末的终值为：,在这种情况下，给定的年利率i为名义年利率，按每年实际获得的利息计算的年利率为有效年利率。,2.6名义年利率与有效年利率计算,有效年利率r与名义年利率i之间的关系为：式中m为每年计息次数连续计息情况下：式中e为自然对数的底数，e2.71828,2.6名义年利率与有效年利率计算,【例2-9】有效年利率与名义年利率的计算EFFECT函数计算有效年利率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函数：返回投资或贷款在某一给定期次内的本金偿还。=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,type),2.8分期等额还款额的敏感性分析,【例2-11】分期还款额的模拟运算分析单因素/双因素敏感性分析2003：数据/模拟运算表2007：数据/数据工具/假设分析/数据表【例2-12】分期等额还贷计算器多因素敏感性分析使用窗体/表单控件,3Excel在长期投资决策中的应用,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财务预测,常用的线性趋势预测函数INTERCEPT函数求回归直线的截距SLOPE求回归直线的斜率。TREND函数返回一条线性回归拟合线的值。【例3-2】利用相关函数预测,3.1财务预测,利用数据分析工具预测2003：工具/加载宏/分析工具库2007：Excel选项/加载项/转到/分析工具库【例3-3】基于移动平均法的销售预测2003：工具/数据分析/移动平均2007：数据/分析/数据分析/移动平均【例3-4】基于回归分析法的销售预测2003：工具/数据分析/回归2007：数据/分析/数据分析/回归,3.2固定资产折旧的计算,固定资产折旧的计算方法直线折旧法平均年限法工作量法加速折旧法余额递减法双倍余额递减法年数总和法,直线折旧法,平均年限法工作量法,某期折旧额=该期实际工作量每单位工作量折旧额,直线折旧函数与折旧计算模型,直线折旧函数SLN函数SLN函数：返回一项资产每期的直线折旧额。公式为：=SLN(cost,salvage,life)【例3-5】采用直线折旧法计算折旧模型,加速折旧法,余额递减法双倍余额递减法年数总和法,加速折旧函数与折旧计算模型,定率余额递减法折旧函数DB函数DB函数：使用固定余额递减法，计算一笔资产在给定期间内的折旧值。公式为：=DB(cost,salvage,life,period,month)年数总和法折旧计算函数SYD函数SYD函数：返回某项资产按年限总和折旧法计算的某期折旧额。公式为：=SYD(cost,salvage,life,per),加速折旧函数与折旧计算模型,双倍余额递减法DDB函数和VDB函数DDB函数：使用双倍余额递减法或其他指定方法，计算一笔资产在给定期间内的折旧值。公式为=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投资项目的现金流量计算,初始现金流量营业现金流量终结现金流量,3.3投资项目的现金流量计算,初始现金流量固定资产投资流动资产投资其他投资费用原有固定资产的变价净收入,终结现金流量固定资产的残值净收入或变价净收入原来垫支的流动资金回收额,3.3投资项目的现金流量计算,每年营业净现金流量的计算方法：年营业净现金流量=营业收入付现成本所得税年营业现金流量=净利润+折旧=(营业收入付现成本折旧)(1税率)+折旧=营业收入(1税率)付现成本(1税率)+折旧税率=税后营业收入税后付现成本+折旧抵税额,3.3投资项目的现金流量计算,【例3-8】运用SLN函数运用数组公式绘制现金流量图,3.4计算平均报酬率,平均报酬率（ARR）是投资项目寿命周期内平均的年投资报酬率。计算公式为：【例3-9】平均报酬率计算与查询模型,3.4计算平均报酬率,相关函数AVERAGE函数计算给定参数的算术平均值。公式为：=AVERAGE(number1,number2,numberN)ABS函数返回数字的绝对值。公式为：=ABS(number),3.4计算平均报酬率,相关函数MAX函数给定参数表中的最大值。公式为：=MAX(number1,number2,numberN)MIN函数给定参数表中的最小值。公式为：=MIN(number1,number2,numberN)VLOOKUP函数在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。公式为：=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_num)返回引用中指定的单元格。公式为：=INDEX(reference,row_num,column_num,area_num),3.5计算投资回收期,静态投资回收期在不考虑资金时间价值的情况下，用投资项目经营期的净现金流量回收初始投资所用的时间。在初始投资以后未来各期净现金流量相等的情况下：在初始投资以后未来各期净现金流量不相等的情况下：式中：n为累计净现金流量第一次出现正值的年份,3.5计算投资回收期,动态投资回收期在考虑资金时间价值的情况下，用投资项目经营期的净现金流量的现值回收初始投资所用的时间。计算公式为：,式中：n为累计净现金流量现值第一次出现正值的年份,3.5计算投资回收期,相关函数NPER函数基于固定利率及等额分期付款方式，返回某项投资（或贷款）的总期数。公式为:=NPER（rate，pmt，pv，fv，type）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函数返回一组现金流的净现值，这些现金流不一定定期发生。该函数的公式为：=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为对正的净现金流量进行再投资的年收益率；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,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：数据/数据分析/规划求解,4Excel在投资风险分析中的应用,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】投资项目盈亏平衡分析,4.2基于净现值的敏感性分析,影响项目净现值的因素销售量、销售单价单位变动成本、付现固定成本初始投资额、寿命期基准收益率【例4-2】单因素敏感性分析2003：数据/模拟运算表2007：数据/数据工具/假设分析/数据表,4.2基于净现值的敏感性分析,【例4-3】双因素敏感性分析2003：数据/模拟运算表2007：数据/数据工具/假设分析/数据表【例4-4】多因素敏感性分析2003：窗体控件2007：表单控件,4.3基于净现值的情境分析,情境分析乐观情境最可能情境悲观情境【例4-5】基于净现值的情境分析建立名称公式运用方案管理器2003：工具/方案2007：数据/数据工具/假设分析/方案管理器,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】投资风险的处置根据标准离差率的大小确定相应的贴现率相关函数VLOOKUP函数NPV函数,5Excel在日常财务管理中的应用,5.1制作费用报销高效记录表5.2处理计算误差5.3计算收入与利润增长率5.4规范整理与快速核对数据表5.5高效汇总财务数据5.6应付账款到期日计算与提前提醒5.7存货的ABC分类5.8存货经济订货批量计算5.9固定资产台账管理,5.1制作费用报销高效记录表,【例5-1】制作费用报销高效记录表数据有效性设置INDIRECT函数：返回由