《烟台excel在财务管控中的扩展应用课件n》由会员分享，可在线阅读，更多相关《烟台excel在财务管控中的扩展应用课件n（91页珍藏版）》请在金锄头文库上搜索。
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的那一点 盈亏平衡点的表示方法 盈亏平衡点销售量（保本点销售量） 盈
3、亏平衡点销售额（保本点销售额） 盈亏平衡点作业率（保本点作业率）,1.3 单品种盈亏平衡点的计算,利润=销售收入变动总成本固定成本 =销售量(单价单位变动成本) 固定成本 当利润=0时，可得： 盈亏平衡点销售量 =固定成本/(单价单位变动成本) 盈亏平衡点销售额 =盈亏平衡点销售量单价,1.3 单品种盈亏平衡点的计算,其他相关指标： 单位边际贡献=单价单位变动成本 边际贡献总额=销售收入变动总成本 =销售量(单价单位变动成本) 边际贡献率=单位边际贡献/单价 =边际贡献总额/销售收入,1.3 单品种盈亏平衡点的计算,其他相关指标： 安全边际量=正常销售量盈亏平衡点销售量 安全边际额=正常销售额
4、盈亏平衡点销售额 安全边际率=安全边际量/正常销售量 =安全边际额/正常销售额 盈亏临界点作业率 =盈亏平衡点销售量/正常销售量 =1安全边际率,1.3 单品种盈亏平衡点的计算,【例1-2 】单品种盈亏平衡点的计算 直接利用公式计算 运用单变量求解工具 2003:工具/单变量求解 2007:数据/数据工具/假设分析/单变量求解,1.4 多品种盈亏平衡点的计算,多品种盈亏平衡点的计算方法 加权平均法 顺序法 分算法 联合单位法 主要产品法,1.4 多品种盈亏平衡点的计算,加权平均法以各种产品的预计销售收入占总收入的比重为权数，确定企业加权平均的综合边际贡献率，再计算盈亏平衡点。 【例1-3】多品
5、种盈亏分析加权平均法 SUM函数：无条件求和 SUMPRODUCT函数:返回数组间对应元素乘积之和 输入数组公式：【Shift+Ctrl+Enter】 单变量求解,1.4 多品种盈亏平衡点的计算,顺序法按照事先规定的品种顺序，依次用各种产品的边际贡献补偿企业的固定成本，直至全部固定成本由产品的边际贡献补偿完为止，从而得到盈亏平衡点。 【例1-4】多品种盈亏分析顺序法 IF函数：根据逻辑判断的真假值返回不同的结果 AND函数：进行逻辑与条件的判断 ABS函数：返回数值的绝对值,1.4 多品种盈亏平衡点的计算,分算法将企业的全部固定成本按一定标准分配给各种产品，然后再对每种产品按单品种的情况分别进
6、行量本利分析。 联合单位法根据各种产品间产销量的最小比例，将其作为一个联合单位，确定每一联合单位的单价、单位变动成本，进行量本利分析。 主要产品法在多种产品中，将边际贡献所占比重较大的产品作为主要产品，以该产品为主按单品种方法进行量本利分析。,1.5 实现目标利润的单一措施分析,逐个考虑的单一因素 销售量 单价 单位变动成本 固定成本 【例1-5】实现目标利润的单一措施分析 单变量求解,1.6 目标利润的多因素敏感性分析,目标利润的影响因素 销售量、单价、单位变动成本、固定成本 【例1-6】目标利润的多因素敏感性分析 设置滚动条控件 2003：【视图】/【工具栏】/【窗体】 2007：【开发工
7、具】/【控件】/【表单控件】,1.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 为年
8、限；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,sc
9、hedule) 式中：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 年金终值的计算,年金每期等额发生的收付款系列 普通年金：每期期末发生（后付年金） 先付年金
10、：每期期初发生（即付年金） 延期年金：一定时期后才开始发生的年金 永续年金：无限期发生的年金,2.3 年金终值的计算,【例2-6】年金终值的计算 普通年金的终值计算 先付年金的终值计算 年金终值的选择计算,2.4 年金现值的计算,【例2-7】年金现值的计算 普通年金的现值计算 先付年金的现值计算 年金终值或现值的选择计算,2.5 求解利率,在已知现值、终值、年金三个参数中的任意两个参数的情况下，可求解未知的利率 RATE函数返回未来款项的各期利率。公式为： = RATE(nper,pmt,pv,fv,type,guess),2.5 求解利率,【例2-8】求解利率 假定您有一位刚好1岁的孩子，您
11、希望在17年后资助他60,000元上大学。 如果您目前只有5,000可用来投资，那么您需要找到一个报酬率为多高的投资项目，才能实现您的这个心愿？ 如果您每年年末都有2000元可用于投资，那么需要多高的投资报酬率才够用？,2.6 名义年利率与有效年利率计算,若给定的年利率为i，每年计息m次，那么现在的一笔资金P在n年末的终值为：,在这种情况下，给定的年利率i为名义年利率，按每年实际获得的利息计算的年利率为有效年利率。,2.6 名义年利率与有效年利率计算,有效年利率r与名义年利率i之间的关系为： 式中m为每年计息次数 连续计息情况下： 式中e为自然对数的底数，e 2.71828,2.6 名义年利率
12、与有效年利率计算,【例2-9】有效年利率与名义年利率的计算 EFFECT函数计算有效年利率 NOMINAL函数计算名义年利率 2003：需要首先加载分析工具库 EXP函数返回 e 的 n 次幂,2.7 编制分期还款计划表,【例2-10】编制分期还款计划表 还本付息方式 等额本金法：分期等额还本余额计息 等额摊还法：分期等额还本付息 还本付息的频率 按年偿还 按月偿还,2.7 编制分期还款计划表,基于固定利率及等额分期付款方式计算 PMT函数：返回投资或贷款的每期付款额。 =PMT(rate,nper,pv,fv,type) IPMT函数：返回投资或贷款在某一给定期次内的利息偿还额。 =IPMT
13、(rate,per,nper,pv,fv,type) PPMT函数：返回投资或贷款在某一给定期次内的本金偿还。 =PPMT(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 期间累计偿还的本金数额。公式为： =CUMPR
14、INC(rate,nper,pv,start_period,end_period,type),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 互斥投资方案的比较分析
15、 3.10 资本限量条件下的投资决策,3.1 财务预测,运用Excel进行财务预测的方法 利用绘图工具预测 利用相关函数预测 利用数据分析工具预测,3.1 财务预测,【例3-1】利用绘图工具预测 创建散点图 添加趋势线,3.1 财务预测,常用的线性趋势预测函数 INTERCEPT函数求回归直线的截距 SLOPE求回归直线的斜率。 TREND函数返回一条线性回归拟合线的值。 【例3-2】利用相关函数预测,3.1 财务预测,利用数据分析工具预测 2003：工具/加载宏/分析工具库 2007：Excel选项/加载项/转到/分析工具库 【例3-3】基于移动平均法的销售预测 2003：工具/数据分析/移
16、动平均 2007：数据/分析/数据分析/移动平均 【例3-4】基于回归分析法的销售预测 2003：工具/数据分析/回归 2007：数据/分析/数据分析/回归,3.2 固定资产折旧的计算,固定资产折旧的计算方法 直线折旧法 平均年限法 工作量法 加速折旧法 余额递减法 双倍余额递减法 年数总和法,直线折旧法,平均年限法 工作量法,某期折旧额 = 该期实际工作量每单位工作量折旧额,直线折旧函数与折旧计算模型,直线折旧函数SLN函数 SLN函数：返回一项资产每期的直线折旧额。公式为： = SLN(cost,salvage,life) 【例3-5】采用直线折旧法计算折旧模型,加速折旧法,余额递减法 双倍余额递减法 年数总和法,加速折旧函数与折旧计算模型,定率余额递减法折旧函数DB函数 DB函数：使用固定余额递减法，计算一笔资产在给定期间内的折旧值。公式为：=DB(cost,salvage,life,period,month) 年数
