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绘制动态盈亏分析面积图盈亏分析面积图050000100000150000200000040080012001600200024002800销售量（件）元盈利区亏损区保本点销售量为:12171.8绘制动态盈亏分析折线图2Excel在资金时间价值计算中的应用2.1单笔现金流的终值计算2.2单笔现金流的现值计算2.3年金终值的计算2.4年金现值的计算2.5求解利率2.6名义年利率与有效年利率计算2.7编制分期还款计划表2.8分期还款额的敏感性分析2.1单笔现金流的终值计算•单利终值的计算•式中：FS为单利终值；P为现在的一笔资金；iS为单利年利率；n为计息期限。)1(niPniPPFSSS2.1单笔现金流的终值计算•复利终值的计算公式或•式中：P为现在的一笔收款或付款；i为复利年利率；n为年限；F为复利终值；FVIFi,n=(1+i)n为复利终值系数。niPF)1(niFVIFPF,2.1单笔现金流的终值计算【例2-1】单利与复利终值的对比分析•FV函数——基于固定利率及等额分期付款方式返回某项投资的未来值。公式为：=FV(rate,nper,pmt,pv,type)•建立与复制公式•显示公式与显示计算结果•公式审核2.1单笔现金流的终值计算•创建图表单利与复利终值对比分析020000400006000080000100000012345101520304050单利终值复利终值2.1单笔现金流的终值计算【例2-2】系列不规则现金流的终值•使用FV函数计算•运用数组公式计算【例2-3】浮动利率情况下的终值计算•FVSCHEDULE函数：基于一系列复利利率计算未来值。公式为=FVSCHEDULE(principal,schedule)式中：principal为现值；schedule为利率数组。2.2单笔现金流的现值计算•单利现值式中：P为现值，F为未来值，is为单利年利率，n为期限。•复利现值或：式中：PVIFi,n为复利现值系数)1/(niFPsniFP)1/(niPVIFFP,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年末的终值为：mnmiPF)1(•在这种情况下，给定的年利率i为名义年利率，按每年实际获得的利息计算的年利率为有效年利率。2.6名义年利率与有效年利率计算•有效年利率r与名义年利率i之间的关系为：式中m为每年计息次数•连续计息情况下：式中e为自然对数的底数，e2.718281)1(mmir2.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】采用直线折旧法计算折旧模型加速折旧法余额递减法双倍余额递减法年数总和法年固定折旧率该年初固定资产净值某年折旧额预计使用年限某年固定资产帐面净值年折旧率某年固定资产账面净值年折旧额2