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

纳税人俱乐部400-650-9936Excel在财务管控中的扩展应用纳税人俱乐部400-650-9936目录1Excel在量本利分析中的应用2Excel在资金时间价值计算中的应用3Excel在长期投资决策中的应用4Excel在投资风险分析中的应用5Excel在日常财务管理中的应用纳税人俱乐部400-650-99361Excel在量本利分析中的应用1.1量本利分析的基本假定1.2混合成本的分解1.3单品种盈亏平衡点的计算1.4多品种盈亏平衡点的计算1.5实现目标利润的单一措施分析1.6目标利润的多因素敏感性分析1.7绘制动态盈亏分析面积图1.8绘制动态盈亏分析折线图纳税人俱乐部400-650-99361.1量本利分析的基本假定•量本利分析–又称本量利分析（CVP分析）–是指对业务量、成本和利润之间的关系进行分析•产销平衡假定–生产量=销售量•成本按性态（习性）分类–变动成本——总额随业务量正比例变动–固定成本——总额不随业务量变动纳税人俱乐部400-650-99361.2混合成本的分解•混合成本–总额随业务量变化，但不成正比例变化。•分解混合成本的常用方法–高低点法–回归分析法•使用相关函数分解混合成本–SLOPE函数：计算回归直线的斜率–INTERCEPT函数：计算回归直线的截距•使用绘图工具分解混合成本–绘制散点图–添加趋势线•【例1-1】分解混合成本纳税人俱乐部400-650-99361.3单品种盈亏平衡点的计算•盈亏平衡点–又称保本点–是指不亏不盈即利润为0的那一点•盈亏平衡点的表示方法–盈亏平衡点销售量（保本点销售量）–盈亏平衡点销售额（保本点销售额）–盈亏平衡点作业率（保本点作业率）纳税人俱乐部400-650-99361.3单品种盈亏平衡点的计算•利润=销售收入－变动总成本－固定成本=销售量×(单价－单位变动成本)－固定成本•当利润=0时，可得：盈亏平衡点销售量=固定成本/(单价－单位变动成本)盈亏平衡点销售额=盈亏平衡点销售量×单价纳税人俱乐部400-650-99361.3单品种盈亏平衡点的计算•其他相关指标：单位边际贡献=单价－单位变动成本边际贡献总额=销售收入－变动总成本=销售量×(单价－单位变动成本)边际贡献率=单位边际贡献/单价=边际贡献总额/销售收入纳税人俱乐部400-650-99361.3单品种盈亏平衡点的计算•其他相关指标：安全边际量=正常销售量－盈亏平衡点销售量安全边际额=正常销售额－盈亏平衡点销售额安全边际率=安全边际量/正常销售量=安全边际额/正常销售额•盈亏临界点作业率=盈亏平衡点销售量/正常销售量=1－安全边际率纳税人俱乐部400-650-99361.3单品种盈亏平衡点的计算【例1-2】单品种盈亏平衡点的计算•直接利用公式计算•运用单变量求解工具–2003:工具/单变量求解–2007:数据/数据工具/假设分析/单变量求解纳税人俱乐部400-650-99361.4多品种盈亏平衡点的计算•多品种盈亏平衡点的计算方法–加权平均法–顺序法–分算法–联合单位法–主要产品法纳税人俱乐部400-650-99361.4多品种盈亏平衡点的计算•加权平均法——以各种产品的预计销售收入占总收入的比重为权数，确定企业加权平均的综合边际贡献率，再计算盈亏平衡点。•【例1-3】多品种盈亏分析—加权平均法–SUM函数：无条件求和–SUMPRODUCT函数:返回数组间对应元素乘积之和–输入数组公式：【Shift+Ctrl+Enter】–单变量求解纳税人俱乐部400-650-99361.4多品种盈亏平衡点的计算•顺序法——按照事先规定的品种顺序，依次用各种产品的边际贡献补偿企业的固定成本，直至全部固定成本由产品的边际贡献补偿完为止，从而得到盈亏平衡点。•【例1-4】多品种盈亏分析—顺序法–IF函数：根据逻辑判断的真假值返回不同的结果–AND函数：进行逻辑与条件的判断–ABS函数：返回数值的绝对值纳税人俱乐部400-650-99361.4多品种盈亏平衡点的计算•分算法——将企业的全部固定成本按一定标准分配给各种产品，然后再对每种产品按单品种的情况分别进行量本利分析。•联合单位法——根据各种产品间产销量的最小比例，将其作为一个联合单位，确定每一联合单位的单价、单位变动成本，进行量本利分析。•主要产品法——在多种产品中，将边际贡献所占比重较大的产品作为主要产品，以该产品为主按单品种方法进行量本利分析。纳税人俱乐部400-650-99361.5实现目标利润的单一措施分析•逐个考虑的单一因素–销售量–单价–单位变动成本–固定成本•【例1-5】实现目标利润的单一措施分析–单变量求解纳税人俱乐部400-650-99361.6目标利润的多因素敏感性分析•目标利润的影响因素–销售量、单价、单位变动成本、固定成本•【例1-6】目标利润的多因素敏感性分析–设置滚动条控件–2003：【视图】/【工具栏】/【窗体】–2007：【开发工具】/【控件】/【表单控件】纳税人俱乐部400-650-99361.7绘制动态盈亏分析面积图盈亏分析面积图050000100000150000200000040080012001600200024002800销售量（件）元盈利区亏损区保本点销售量为:1217纳税人俱乐部400-650-99361.8绘制动态盈亏分析折线图纳税人俱乐部400-650-99362Excel在资金时间价值计算中的应用2.1单笔现金流的终值计算2.2单笔现金流的现值计算2.3年金终值的计算2.4年金现值的计算2.5求解利率2.6名义年利率与有效年利率计算2.7编制分期还款计划表2.8分期还款额的敏感性分析纳税人俱乐部400-650-99362.1单笔现金流的终值计算•单利终值的计算•式中：FS为单利终值；P为现在的一笔资金；iS为单利年利率；n为计息期限。)1(niPniPPFSSS纳税人俱乐部400-650-99362.1单笔现金流的终值计算•复利终值的计算公式或•式中：P为现在的一笔收款或付款；i为复利年利率；n为年限；F为复利终值；FVIFi,n=(1+i)n为复利终值系数。niPF)1(niFVIFPF,纳税人俱乐部400-650-99362.1单笔现金流的终值计算【例2-1】单利与复利终值的对比分析•FV函数——基于固定利率及等额分期付款方式返回某项投资的未来值。公式为：=FV(rate,nper,pmt,pv,type)•建立与复制公式•显示公式与显示计算结果•公式审核纳税人俱乐部400-650-99362.1单笔现金流的终值计算•创建图表单利与复利终值对比分析020000400006000080000100000012345101520304050单利终值复利终值纳税人俱乐部400-650-99362.1单笔现金流的终值计算【例2-2】系列不规则现金流的终值•使用FV函数计算•运用数组公式计算【例2-3】浮动利率情况下的终值计算•FVSCHEDULE函数：基于一系列复利利率计算未来值。公式为=FVSCHEDULE(principal,schedule)式中：principal为现值；schedule为利率数组。纳税人俱乐部400-650-99362.2单笔现金流的现值计算•单利现值式中：P为现值，F为未来值，is为单利年利率，n为期限。•复利现值或：式中：PVIFi,n为复利现值系数)1/(niFPsniFP)1/(niPVIFFP,纳税人俱乐部400-650-99362.2单笔现金流的现值计算【例2-4】单利与复利现值的比较分析•PV函数——基于固定利率及等额分期付款方式返回某项投资的现值。公式为：=PV(rate,nper,pmt,fv,type)•绘制图表【例2-5】系列不规则现金流的现值•使用PV函数计算•运用数组公式计算纳税人俱乐部400-650-99362.3年金终值的计算•年金——每期等额发生的收付款系列–普通年金：每期期末发生（后付年金）–先付年金：每期期初发生（即付年金）–延期年金：一定时期后才开始发生的年金–永续年金：无限期发生的年金纳税人俱乐部400-650-99362.3年金终值的计算【例2-6】年金终值的计算•普通年金的终值计算•先付年金的终值计算•年金终值的选择计算纳税人俱乐部400-650-99362.4年金现值的计算【例2-7】年金现值的计算•普通年金的现值计算•先付年金的现值计算•年金终值或现值的选择计算纳税人俱乐部400-650-99362.5求解利率•在已知现值、终值、年金三个参数中的任意两个参数的情况下，可求解未知的利率•RATE函数——返回未来款项的各期利率。公式为：=RATE(nper,pmt,pv,fv,type,guess)纳税人俱乐部400-650-99362.5求解利率【例2-8】求解利率•假定您有一位刚好1岁的孩子，您希望在17年后资助他60,000元上大学。•如果您目前只有5,000可用来投资，那么您需要找到一个报酬率为多高的投资项目，才能实现您的这个心愿？•如果您每年年末都有2000元可用于投资，那么需要多高的投资报酬率才够用？纳税人俱乐部400-650-99362.6名义年利率与有效年利率计算•若给定的年利率为i，每年计息m次，那么现在的一笔资金P在n年末的终值为：mnmiPF)1(•在这种情况下，给定的年利率i为名义年利率，按每年实际获得的利息计算的年利率为有效年利率。纳税人俱乐部400-650-99362.6名义年利率与有效年利率计算•有效年利率r与名义年利率i之间的关系为：式中m为每年计息次数•连续计息情况下：式中e为自然对数的底数，e2.718281)1(mmir纳税人俱乐部400-650-99362.6名义年利率与有效年利率计算【例2-9】有效年利率与名义年利率的计算•EFFECT函数——计算有效年利率•NOMINAL函数——计算名义年利率–2003：需要首先加载分析工具库•EXP函数——返回e的n次幂纳税人俱乐部400-650-99362.7编制分期还款计划表【例2-10】编制分期还款计划表•还本付息方式–等额本金法：分期等额还本余额计息–等额摊还法：分期等额还本付息•还本付息的频率–按年偿还–按月偿还纳税人俱乐部400-650-99362.7编制分期还款计划表•基于固定利率及等额分期付款方式计算PMT函数：返回投资或贷款的每期付款额。=PMT(rate,nper,pv,fv,type)IPMT函数：返回投资或贷款在某一给定期次内的利息偿还额。=IPMT(rate,per,nper,pv,fv,type)PPMT函数：返回投资或贷款在某一给定期次内的本金偿还。=PPMT(rate,per,nper,pv,fv,type)纳税人俱乐部400-650-99362.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)纳税人俱乐部400-650-99362.8分期等额还款额的敏感性分析【例2-11】分期还款额的模拟运算分析•单因素/双因素敏感性分析•2003：数据/模拟运算表•2007：数据/数据工具/假设分析/数据表【例2-12】分期等额还贷计算器•多因素敏感性分析•使用窗体/表单控件纳税人俱乐部400-650-99363Excel在长期投资决策中的应用3.1财务预测3.2固定资产折旧的计算3.3投资项目的现金流量计算3.4计算平均报酬率3.5计算投资回收期3.6计算净现值3.7计算获利指数3.8计算内部收益率3.9互斥投资方案的比较分析3.10资本限量条件