烟台Excel在财务管控中的扩展应用课件N

1、 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-9936Excel在财务管控中的扩展应用在财务管控中的扩展应用 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-9936目录目录1 Excel在量本利分析中的应用在量本利分析中的应用2 Excel在资金时间价值计算中的应用在资金时间价值计算中的应用3 Excel在长期投资决策中的应用在长期投资决策中的应用4 Excel在投资风险分析中的应用在投资风险分析中的应用5 Excel在日常财务管理中的应用在日常财务管理中的应用 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-99361 Excel在

2、量本利分析中的应用在量本利分析中的应用1.1 量本利分析的基本假定量本利分析的基本假定1.2 混合成本的分解混合成本的分解1.3 单品种盈亏平衡点的计算单品种盈亏平衡点的计算1.4 多品种盈亏平衡点的计算多品种盈亏平衡点的计算1.5 实现目标利润的单一措施分析实现目标利润的单一措施分析1.6 目标利润的多因素敏感性分析目标利润的多因素敏感性分析1.7 绘制动态盈亏分析面积图绘制动态盈亏分析面积图1.8 绘制动态盈亏分析折线图绘制动态盈亏分析折线图 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-99361.1 量本利分析的基本假定量本利分析的基本假定 量本利分析量本利分析 又

3、称本量利分析（又称本量利分析（CVP分析）分析） 是指对业务量、成本和利润之间的关系进行分析是指对业务量、成本和利润之间的关系进行分析 产销平衡假定产销平衡假定 生产量生产量=销售量销售量 成本按性态（习性）分类成本按性态（习性）分类 变动成本变动成本总额随业务量正比例变动总额随业务量正比例变动 固定成本固定成本总额不随业务量变动总额不随业务量变动 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-99361.2 混合成本的分解混合成本的分解 混合成本混合成本 总额随业务量变化，但不成正比例变化。总额随业务量变化，但不成正比例变化。 分解混合成本的常用方法分解混合成本的常用方法

4、 高低点法高低点法 回归分析法回归分析法 使用相关函数分解混合成本使用相关函数分解混合成本 SLOPE函数：计算回归直线的斜率函数：计算回归直线的斜率 INTERCEPT函数：计算回归直线的截距函数：计算回归直线的截距 使用绘图工具分解混合成本使用绘图工具分解混合成本 绘制散点图绘制散点图 添加趋势线添加趋势线 【例例1-1 】分解混合成本分解混合成本 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-99361.3 单品种盈亏平衡点的计算单品种盈亏平衡点的计算 盈亏平衡点盈亏平衡点 又称保本点又称保本点 是指不亏不盈即利润为是指不亏不盈即利润为0的那一点的那一点 盈亏平衡点的

5、表示方法盈亏平衡点的表示方法 盈亏平衡点销售量（保本点销售量）盈亏平衡点销售量（保本点销售量） 盈亏平衡点销售额（保本点销售额）盈亏平衡点销售额（保本点销售额） 盈亏平衡点作业率（保本点作业率）盈亏平衡点作业率（保本点作业率） 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-99361.3 单品种盈亏平衡点的计算单品种盈亏平衡点的计算 利润利润=销售收入销售收入变动总成本变动总成本固定成本固定成本 =销售量销售量(单价单位变动成本单价单位变动成本) 固定成本固定成本 当利润当利润=0时，可得：时，可得： 盈亏平衡点销售量盈亏平衡点销售量 =固定成本固定成本/(单价单位变动成本

6、单价单位变动成本) 盈亏平衡点销售额盈亏平衡点销售额 =盈亏平衡点销售量盈亏平衡点销售量单价单价 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-99361.3 单品种盈亏平衡点的计算单品种盈亏平衡点的计算 其他相关指标：其他相关指标： 单位边际贡献单位边际贡献=单价单位变动成本单价单位变动成本 边际贡献总额边际贡献总额=销售收入销售收入变动总成本变动总成本 =销售量销售量(单价单位变动成本单价单位变动成本) 边际贡献率边际贡献率=单位边际贡献单位边际贡献/单价单价 =边际贡献总额边际贡献总额/销售收入销售收入 纳税人俱乐部纳税人俱乐部 400-650-9936400-650

7、-99361.3 单品种盈亏平衡点的计算单品种盈亏平衡点的计算 其他相关指标：其他相关指标： 安全边际量安全边际量=正常销售量正常销售量盈亏平衡点销售量盈亏平衡点销售量 安全边际额安全边际额=正常销售额正常销售额盈亏平衡点销售额盈亏平衡点销售额 安全边际率安全边际率=安全边际量安全边际量/正常销售量正常销售量 =安全边际额安全边际额/正常销售额正常销售额 盈亏临界点作业率盈亏临界点作业率 =盈亏平衡点销售量盈亏平衡点销售量/正常销售量正常销售量 =1安全边际率安全边际率 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-99361.3 单品种盈亏平衡点的计算单品种盈亏平衡点的计

8、算【例例1-2 】单品种盈亏平衡点的计算单品种盈亏平衡点的计算 直接利用公式计算直接利用公式计算 运用单变量求解工具运用单变量求解工具 2003:工具工具/单变量求解单变量求解 2007:数据数据/数据工具数据工具/假设分析假设分析/单变量求解单变量求解 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-99361.4 多品种盈亏平衡点的计算多品种盈亏平衡点的计算 多品种盈亏平衡点的计算方法多品种盈亏平衡点的计算方法 加权平均法加权平均法 顺序法顺序法 分算法分算法 联合单位法联合单位法 主要产品法主要产品法 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-9

9、9361.4 多品种盈亏平衡点的计算多品种盈亏平衡点的计算 加权平均法加权平均法以各种产品的预计销售收入占总收以各种产品的预计销售收入占总收入的比重为权数，确定企业加权平均的综合边际贡入的比重为权数，确定企业加权平均的综合边际贡献率，再计算盈亏平衡点。献率，再计算盈亏平衡点。 【例例1-3】多品种盈亏分析多品种盈亏分析加权平均法加权平均法 SUM函数：无条件求和函数：无条件求和 SUMPRODUCT函数函数:返回数组间对应元素乘积之和返回数组间对应元素乘积之和 输入数组公式：输入数组公式：【Shift+Ctrl+Enter】 单变量求解单变量求解 纳税人俱乐部纳税人俱乐部 400-650-99

10、36400-650-99361.4 多品种盈亏平衡点的计算多品种盈亏平衡点的计算 顺序法顺序法按照事先规定的品种顺序，依次用各按照事先规定的品种顺序，依次用各种产品的边际贡献补偿企业的固定成本，直至全种产品的边际贡献补偿企业的固定成本，直至全部固定成本由产品的边际贡献补偿完为止，从而部固定成本由产品的边际贡献补偿完为止，从而得到盈亏平衡点。得到盈亏平衡点。 【例例1-4】多品种盈亏分析多品种盈亏分析顺序法顺序法 IF函数：根据逻辑判断的真假值返回不同的结果函数：根据逻辑判断的真假值返回不同的结果 AND函数：进行逻辑与条件的判断函数：进行逻辑与条件的判断 ABS函数：返回数值的绝对值函数：返回

11、数值的绝对值 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-99361.4 多品种盈亏平衡点的计算多品种盈亏平衡点的计算 分算法分算法将企业的全部固定成本按一定标准分配将企业的全部固定成本按一定标准分配给各种产品，然后再对每种产品按单品种的情况分给各种产品，然后再对每种产品按单品种的情况分别进行量本利分析。别进行量本利分析。 联合单位法联合单位法根据各种产品间产销量的最小比例，根据各种产品间产销量的最小比例，将其作为一个联合单位，确定每一联合单位的单价、将其作为一个联合单位，确定每一联合单位的单价、单位变动成本，进行量本利分析。单位变动成本，进行量本利分析。 主要产品法主要

12、产品法在多种产品中，将边际贡献所占比在多种产品中，将边际贡献所占比重较大的产品作为主要产品，以该产品为主按单品重较大的产品作为主要产品，以该产品为主按单品种方法进行量本利分析。种方法进行量本利分析。 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-99361.5 实现目标利润的单一措施分析实现目标利润的单一措施分析 逐个考虑的单一因素逐个考虑的单一因素 销售量销售量 单价单价 单位变动成本单位变动成本 固定成本固定成本 【例例1-5】实现目标利润的单一措施分析实现目标利润的单一措施分析 单变量求解单变量求解 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-9

13、9361.6 目标利润的多因素敏感性分析目标利润的多因素敏感性分析 目标利润的影响因素目标利润的影响因素 销售量、单价、单位变动成本、固定成本销售量、单价、单位变动成本、固定成本 【例例1-6】目标利润的多因素敏感性分析目标利润的多因素敏感性分析 设置滚动条控件设置滚动条控件 2003：【视图视图】/【工具栏工具栏】/【窗体窗体】 2007：【开发工具开发工具】/【控件控件】/【表单控件表单控件】 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-99361.7 绘制动态盈亏分析面积图绘制动态盈亏分析面积图 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-99

14、361.8 绘制动态盈亏分析折线图绘制动态盈亏分析折线图 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-99362 Excel在资金时间价值计算中的应用在资金时间价值计算中的应用2.1 单笔现金流的终值计算单笔现金流的终值计算2.2 单笔现金流的现值计算单笔现金流的现值计算2.3 年金终值的计算年金终值的计算2.4 年金现值的计算年金现值的计算2.5 求解利率求解利率2.6 名义年利率与有效年利率计算名义年利率与有效年利率计算2.7 编制分期还款计划表编制分期还款计划表2.8 分期还款额的敏感性分析分期还款额的敏感性分析 纳税人俱乐部纳税人俱乐部 400-650-99364

15、00-650-99362.1 单笔现金流的终值计算单笔现金流的终值计算 单利终值的计算单利终值的计算 式中：式中：FS 为单利终值；为单利终值；P 为现在的一笔资为现在的一笔资金；金；iS为单利年利率；为单利年利率；n 为计息期限。为计息期限。)1 (niPniPPFSSS 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-99362.1 单笔现金流的终值计算单笔现金流的终值计算 复利终值的计算公式复利终值的计算公式 或或 式中：式中：P 为现在的一笔收款或付款；为现在的一笔收款或付款；i 为复利年利率；为复利年利率；n 为年限；为年限；F为复利终值；为复利终值；FVIFi,n

16、=(1+i)n为复利终为复利终值系数。值系数。niPF)1 ( niFVIFPF, 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-99362.1 单笔现金流的终值计算单笔现金流的终值计算【例例2-1】单利与复利终值的对比分析单利与复利终值的对比分析 FV函数函数基于固定利率及等额分期付款方式返回基于固定利率及等额分期付款方式返回某项投资的未来值。公式为：某项投资的未来值。公式为： = FV(rate,nper,pmt,pv,type) 建立与复制公式建立与复制公式 显示公式与显示计算结果显示公式与显示计算结果 公式审核公式审核 纳税人俱乐部纳税人俱乐部 400-650-99

17、36400-650-99362.1 单笔现金流的终值计算单笔现金流的终值计算 创建图表 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-99362.1 单笔现金流的终值计算单笔现金流的终值计算【例例2-2】系列不规则现金流的终值系列不规则现金流的终值 使用使用FV函数计算函数计算 运用数组公式计算运用数组公式计算【例例2-3】浮动利率情况下的终值计算浮动利率情况下的终值计算 FVSCHEDULE函数：基于一系列复利利率计算未函数：基于一系列复利利率计算未来值。公式为来值。公式为 =FVSCHEDULE(principal,schedule) 式中：式中：principal为现

18、值；为现值；schedule为利率数组。为利率数组。 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-99362.2 单笔现金流的现值计算单笔现金流的现值计算 单利现值单利现值 式中：式中：P为现值，为现值，F为未来值，为未来值，is为单利年利率，为单利年利率，n为期限。为期限。 复利现值复利现值 或：或： 式中：式中：PVIFi,n为复利现值系数为复利现值系数)1/(niFPsniFP)1/( niPVIFFP, 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-99362.2 单笔现金流的现值计算单笔现金流的现值计算【例例2-4】单利与复利现值的比较分析单

19、利与复利现值的比较分析 PV函数函数基于固定利率及等额分期付款方式返回基于固定利率及等额分期付款方式返回某项投资的现值。公式为：某项投资的现值。公式为： = PV(rate,nper,pmt,fv,type) 绘制图表绘制图表【例例2-5】系列不规则现金流的现值系列不规则现金流的现值 使用使用PV函数计算函数计算 运用数组公式计算运用数组公式计算 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-99362.3 年金终值的计算年金终值的计算 年金年金每期等额发生的收付款系列每期等额发生的收付款系列 普通年金：每期期末发生（后付年金）普通年金：每期期末发生（后付年金） 先付年金：

20、每期期初发生（即付年金）先付年金：每期期初发生（即付年金） 延期年金：一定时期后才开始发生的年金延期年金：一定时期后才开始发生的年金 永续年金：无限期发生的年金永续年金：无限期发生的年金 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-99362.3 年金终值的计算年金终值的计算【例例2-6】年金终值的计算年金终值的计算 普通年金的终值计算普通年金的终值计算 先付年金的终值计算先付年金的终值计算 年金终值的选择计算年金终值的选择计算 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-99362.4 年金现值的计算年金现值的计算【例例2-7】年金现值的计算年金现

21、值的计算 普通年金的现值计算普通年金的现值计算 先付年金的现值计算先付年金的现值计算 年金终值或现值的选择计算年金终值或现值的选择计算 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-99362.5 求解利率求解利率 在已知现值、终值、年金三个参数中的任在已知现值、终值、年金三个参数中的任意两个参数的情况下，可求解未知的利率意两个参数的情况下，可求解未知的利率 RATE函数函数返回未来款项的各期利率。返回未来款项的各期利率。公式为：公式为：= RATE(nper,pmt,pv,fv,type,guess) 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-99

22、362.5 求解利率求解利率【例例2-8】求解利率求解利率 假定您有一位刚好假定您有一位刚好1岁的孩子，您希望在岁的孩子，您希望在17年后资年后资助他助他60,000元上大学。元上大学。 如果您目前只有如果您目前只有5,000可用来投资，那么您需要找可用来投资，那么您需要找到一个报酬率为多高的投资项目，才能实现您的到一个报酬率为多高的投资项目，才能实现您的这个心愿？这个心愿？ 如果您每年年末都有如果您每年年末都有2000元可用于投资，那么需元可用于投资，那么需要多高的投资报酬率才够用？要多高的投资报酬率才够用？ 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-99362.6

23、名义年利率与有效年利率计算名义年利率与有效年利率计算若给定的年利率为若给定的年利率为i，每年计息，每年计息m次，那么次，那么现在的一笔资金现在的一笔资金P在在n年末的终值为：年末的终值为：mnmiPF)1 ( 在这种情况下，给定的年利率在这种情况下，给定的年利率i为名义年利为名义年利率，按每年实际获得的利息计算的年利率为率，按每年实际获得的利息计算的年利率为有效年利率。有效年利率。 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-99362.6 名义年利率与有效年利率计算名义年利率与有效年利率计算 有效年利率有效年利率r与名义年利率与名义年利率i之间的关系为：之间的关系为：

24、式中式中m为每年计息次数为每年计息次数 连续计息情况下：连续计息情况下： 式中式中e为自然对数的底数，为自然对数的底数，e 2.718281)1 (mmir 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-99362.6 名义年利率与有效年利率计算名义年利率与有效年利率计算【例例2-9】有效年利率与名义年利率的计算有效年利率与名义年利率的计算 EFFECT函数函数计算有效年利率计算有效年利率 NOMINAL函数函数计算名义年利率计算名义年利率 2003：需要首先加载分析工具库：需要首先加载分析工具库 EXP函数函数返回返回 e 的的 n 次幂次幂 纳税人俱乐部纳税人俱乐部 4

25、00-650-9936400-650-99362.7 编制分期还款计划表编制分期还款计划表【例例2-10】编制分期还款计划表编制分期还款计划表 还本付息方式还本付息方式 等额本金法：分期等额还本余额计息等额本金法：分期等额还本余额计息 等额摊还法：分期等额还本付息等额摊还法：分期等额还本付息 还本付息的频率还本付息的频率 按年偿还按年偿还 按月偿还按月偿还 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-99362.7 编制分期还款计划表编制分期还款计划表 基于固定利率及等额分期付款方式计算基于固定利率及等额分期付款方式计算PMT函数：返回投资或贷款的每期付款额。函数：返回投

26、资或贷款的每期付款额。 =PMT(rate,nper,pv,fv,type) IPMT函数：返回投资或贷款在某一给定期次内函数：返回投资或贷款在某一给定期次内的利息偿还额。的利息偿还额。 =IPMT(rate,per,nper,pv,fv,type) PPMT函数：返回投资或贷款在某一给定期次内函数：返回投资或贷款在某一给定期次内的本金偿还。的本金偿还。 =PPMT(rate,per,nper,pv,fv,type) 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-99362.7 编制分期还款计划表编制分期还款计划表 基于固定利率及等额分期付款方式计算基于固定利率及等额分期付

27、款方式计算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) 纳税人俱乐部纳税

28、人俱乐部 400-650-9936400-650-99362.8 分期等额还款额的敏感性分析分期等额还款额的敏感性分析【例例2-11】分期还款额的模拟运算分析分期还款额的模拟运算分析 单因素单因素/双因素敏感性分析双因素敏感性分析 2003：数据：数据/模拟运算表模拟运算表 2007：数据：数据/数据工具数据工具/假设分析假设分析/数据表数据表【例例2-12】分期等额还贷计算器分期等额还贷计算器 多因素敏感性分析多因素敏感性分析 使用窗体使用窗体/表单控件表单控件 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-99363 Excel在长期投资决策中的应用在长期投资决策中的应

29、用3.1 财务预测财务预测3.2 固定资产折旧的计算固定资产折旧的计算3.3 投资项目的现金流量计算投资项目的现金流量计算3.4 计算平均报酬率计算平均报酬率3.5 计算投资回收期计算投资回收期3.6 计算净现值计算净现值3.7 计算获利指数计算获利指数3.8 计算内部收益率计算内部收益率3.9 互斥投资方案的比较分析互斥投资方案的比较分析3.10 资本限量条件下的投资决策资本限量条件下的投资决策 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-99363.1 财务预测财务预测 运用运用Excel进行财务预测的方法进行财务预测的方法 利用绘图工具预测利用绘图工具预测 利用相关

30、函数预测利用相关函数预测 利用数据分析工具预测利用数据分析工具预测 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-99363.1 财务预测财务预测 【例例3-1】利用绘图工具预测利用绘图工具预测 创建散点图创建散点图 添加趋势线添加趋势线 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-99363.1 财务预测财务预测 常用的线性趋势预测函数常用的线性趋势预测函数 INTERCEPT函数函数求回归直线的截距求回归直线的截距 SLOPE求回归直线的斜率。求回归直线的斜率。 TREND函数函数返回一条线性回归拟合线的值。返回一条线性回归拟合线的值。 【例例3-

31、2】利用相关函数预测利用相关函数预测 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-99363.1 财务预测财务预测 利用数据分析工具预测利用数据分析工具预测 2003：工具：工具/加载宏加载宏/分析工具库分析工具库 2007：Excel选项选项/加载项加载项/转到转到/分析工具库分析工具库 【例例3-3】基于移动平均法的销售预测基于移动平均法的销售预测 2003：工具：工具/数据分析数据分析/移动平均移动平均 2007：数据：数据/分析分析/数据分析数据分析/移动平均移动平均 【例例3-4】基于回归分析法的销售预测基于回归分析法的销售预测 2003：工具：工具/数据分析数

32、据分析/回归回归 2007：数据：数据/分析分析/数据分析数据分析/回归回归 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-99363.2 固定资产折旧的计算固定资产折旧的计算 固定资产折旧的计算方法固定资产折旧的计算方法 直线折旧法直线折旧法 平均年限法平均年限法 工作量法工作量法 加速折旧法加速折旧法 余额递减法余额递减法 双倍余额递减法双倍余额递减法 年数总和法年数总和法 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-9936直线折旧法直线折旧法平均年限法平均年限法工作量法工作量法预计使用年限预计净残值固定资产原值年折旧额预计总工作量预计净残值固定

33、资产原值每单位工作量折旧额某期折旧额某期折旧额 = 该期实际工作量该期实际工作量每单位工作量折旧额每单位工作量折旧额 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-9936直线折旧函数与折旧计算模型直线折旧函数与折旧计算模型 直线折旧函数直线折旧函数SLN函数函数 SLN函数：返回一项资产每期的直线折旧额。公式为：函数：返回一项资产每期的直线折旧额。公式为： = SLN(cost,salvage,life) 【例例3-5】采用直线折旧法计算折旧模型采用直线折旧法计算折旧模型 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-9936加速折旧法加速折旧法余额递

34、减法余额递减法双倍余额递减法双倍余额递减法年数总和法年数总和法年固定折旧率该年初固定资产净值某年折旧额预计使用年限某年固定资产帐面净值年折旧率某年固定资产账面净值年折旧额2预计使用年限累计折旧固定资产原值2)(n固定资产原始价值固定资产预计净残值年固定折旧率1年折旧率预计残值）（固定资产原值年折旧额211）（折旧年限折旧年限已使用年限）（折旧年限年折旧率 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-9936加速折旧函数与折旧计算模型加速折旧函数与折旧计算模型 定率余额递减法折旧函数定率余额递减法折旧函数DB函数函数 DB函数：使用固定余额递减法，计算一笔资产函数：使用固定

35、余额递减法，计算一笔资产在给定期间内的折旧值。公式为：在给定期间内的折旧值。公式为：=DB(cost,salvage,life,period,month) 年数总和法折旧计算函数年数总和法折旧计算函数SYD函数函数 SYD函数：返回某项资产按年限总和折旧法计函数：返回某项资产按年限总和折旧法计算的某期折旧额。公式为：算的某期折旧额。公式为：= SYD(cost,salvage,life,per) 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-9936加速折旧函数与折旧计算模型加速折旧函数与折旧计算模型 双倍余额递减法双倍余额递减法DDB函数和函数和VDB函数函数 DDB函数

36、：使用双倍余额递减法或其他指定方法，计算一函数：使用双倍余额递减法或其他指定方法，计算一笔资产在给定期间内的折旧值。公式为笔资产在给定期间内的折旧值。公式为 = DDB(cost, salvage, life, period, factor) 当按直线法计算的折旧值大于按余额递减计算的折旧值时，当按直线法计算的折旧值大于按余额递减计算的折旧值时，如果希望转换到直线折旧法，应使用如果希望转换到直线折旧法，应使用 VDB 函数。函数。 VDB函数：使用双倍余额递减法或其他指定的方法，返回函数：使用双倍余额递减法或其他指定的方法，返回指定的任何期间内的资产折旧值。公式为指定的任何期间内的资产折旧值。

37、公式为 =VDB(cost,salvage,life,start_period,end_period,factor,no_switch) 【例例3-6】采用加速折旧法计算折旧模型采用加速折旧法计算折旧模型 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-9936折旧选择计算模型折旧选择计算模型【例例3-7】 使用列表框控件使用列表框控件 IF函数的应用函数的应用 折旧函数的应用折旧函数的应用 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-99363.3 投资项目的现金流量计算投资项目的现金流量计算 初始现金流量初始现金流量 营业现金流量营业现金流量 终结现

38、金流量终结现金流量 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-99363.3 投资项目的现金流量计算投资项目的现金流量计算 初始现金流量初始现金流量 固定资产投资固定资产投资 流动资产投资流动资产投资 其他投资费用其他投资费用 原有固定资产的变价原有固定资产的变价净收入净收入v终结现金流量终结现金流量固定资产的残值净收固定资产的残值净收入或变价净收入入或变价净收入原来垫支的流动资金原来垫支的流动资金回收额回收额 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-99363.3 投资项目的现金流量计算投资项目的现金流量计算 每年营业净现金流量的计算方法：每

39、年营业净现金流量的计算方法：年营业净现金流量年营业净现金流量=营业收入付现成本所得税营业收入付现成本所得税年营业净现金流量年营业净现金流量=净利润净利润+折旧折旧=(营业收入付现成本折旧营业收入付现成本折旧)(1税率税率)+折旧折旧=营业收入营业收入(1税率税率)付现成本付现成本(1税率税率) +折旧折旧税率税率=税后营业收入税后付现成本税后营业收入税后付现成本+折旧抵税额折旧抵税额 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-99363.3 投资项目的现金流量计算投资项目的现金流量计算【例例3-8】 运用运用SLN函数函数 运用数组公式运用数组公式 绘制现金流量图绘制现

40、金流量图 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-99363.4 计算平均报酬率计算平均报酬率 平均报酬率（平均报酬率（ARR） 是投资项目寿命周是投资项目寿命周期内平均的年投资报酬率。计算公式为：期内平均的年投资报酬率。计算公式为： 【例例3-9】平均报酬率计算与查询模型平均报酬率计算与查询模型初始投资年均净现金流量平均报酬率 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-99363.4 计算平均报酬率计算平均报酬率相关函数相关函数 AVERAGE函数函数计算给定参数的算术平均值。计算给定参数的算术平均值。公式为：公式为： = AVERAGE(n

41、umber1, number 2, number N) ABS函数函数返回数字的绝对值。公式为：返回数字的绝对值。公式为： =ABS(number) 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-99363.4 计算平均报酬率计算平均报酬率相关函数相关函数 MAX函数函数给定参数表中的最大值。公式为：给定参数表中的最大值。公式为：= MAX(number1, number 2, number N) MIN函数函数给定参数表中的最小值。公式为：给定参数表中的最小值。公式为：= MIN(number1, number 2, number N) VLOOKUP函数函数在表格或数值

42、数组的首列查在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中找指定的数值，并由此返回表格或数组当前行中指定列处的数值。公式为：指定列处的数值。公式为：= VLOOKUP(lookup_value，table_array，col_index_num，range_lookup) 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-99363.4 计算平均报酬率计算平均报酬率相关函数相关函数 MATCH函数函数返回在指定方式下与指定数值匹配返回在指定方式下与指定数值匹配的数组中元素的相应位置。公式为：的数组中元素的相应位置。公式为： =MATCH(lookup_val

43、ue,lookup_array,match_type) INDEX函数函数返回表格或区域中的数值或对数值返回表格或区域中的数值或对数值的引用。该函数有以下两种形式：的引用。该函数有以下两种形式： 返回数组中指定单元格或单元格数组的数值。公式为：返回数组中指定单元格或单元格数组的数值。公式为：= INDEX(array,row_num,column_num) 返回引用中指定的单元格。公式为：返回引用中指定的单元格。公式为：=INDEX(reference,row_num,column_num,area_num) 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-99363.5 计

44、算投资回收期计算投资回收期 静态投资回收期静态投资回收期在不考虑资金时间价值的情况在不考虑资金时间价值的情况下，用投资项目经营期的净现金流量回收初始投资下，用投资项目经营期的净现金流量回收初始投资所用的时间。所用的时间。 在初始投资以后未来各期净现金流量相等的情况下：在初始投资以后未来各期净现金流量相等的情况下： 在初始投资以后未来各期净现金流量不相等的情况下：在初始投资以后未来各期净现金流量不相等的情况下： 式中：式中：n为累计净现金流量第一次出现正值的年份为累计净现金流量第一次出现正值的年份年净现金流量初始投资额静态投资回收期年的净现金流量第年末尚未回收的投资）（静态投资回收期n)1n(1

45、n 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-99363.5 计算投资回收期计算投资回收期 动态投资回收期动态投资回收期在考虑资金时间价值的情况下，在考虑资金时间价值的情况下，用投资项目经营期的净现金流量的现值回收初始投用投资项目经营期的净现金流量的现值回收初始投资所用的时间。计算公式为：资所用的时间。计算公式为：年净现金流量的现值第年末尚未回收的投资）（动态投资回收期n)1n(1n式中：式中：n为累计净现金流量现值第一次出现正值的年份为累计净现金流量现值第一次出现正值的年份 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-99363.5 计算投资回收

46、期计算投资回收期 相关函数相关函数 NPER函数函数基于固定利率及等额分期付款方基于固定利率及等额分期付款方式，返回某项投资（或贷款）的总期数。公式为式，返回某项投资（或贷款）的总期数。公式为:= NPER(rate,pmt,pv,fv,type) COUNTIF函数函数计算给定区域内满足特定条计算给定区域内满足特定条件的单元格的数目。公式为：件的单元格的数目。公式为：= COUNTIF(range,criteria) 【例例3-10】计算投资回收期计算投资回收期 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-99363.6 计算净现值计算净现值 净现值的计算公式为：净现值

47、的计算公式为： 式中：式中：CIt为投资项目第为投资项目第t年的现金流入量，年的现金流入量，COt为投资项目第为投资项目第t年的现金流出量，年的现金流出量，NCFt为投资项目第为投资项目第t年的净现金流量，年的净现金流量，i为为贴现率，贴现率，n为投资项目的寿命期。为投资项目的寿命期。 若初始投资为若初始投资为K，且在投资期初一次投入，则：，且在投资期初一次投入，则：ntttntttntttiNCFiCOiCINPV000)1 ()1 ()1 (KiNCFNPVnttt1)1 ( 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-99363.6 计算净现值计算净现值 相关函数相

48、关函数 NPV函数函数基于一系列现金流和固定的各期贴现率，基于一系列现金流和固定的各期贴现率，返回一项投资的净现值。这里的投资净现值是指未来各返回一项投资的净现值。这里的投资净现值是指未来各期现金流的现值总和。公式为：期现金流的现值总和。公式为：= NPV(rate,value1,value2,.) XNPV函数函数返回一组现金流的净现值，这些现金流不返回一组现金流的净现值，这些现金流不一定定期发生。该函数的公式为：一定定期发生。该函数的公式为：=XNPV(rate,values,dates) 【例例3-11】计算净现值计算净现值 纳税人俱乐部纳税人俱乐部 400-650-9936400-65

49、0-99363.7 计算获利指数计算获利指数 获利指数的计算公式：获利指数的计算公式： 式中：式中：PI为获利指数；为获利指数；m为投资期；为投资期；Kt为投资期内第为投资期内第t年年的净现金流出量；其他各符号的含义如前所述。的净现金流出量；其他各符号的含义如前所述。 如果项目的投资额在期初一次性发生，并且初始投资额如果项目的投资额在期初一次性发生，并且初始投资额为为K，则：，则： 【例例3-12】mtttnmtttiKiNCFPI01)1 (/)1 (KiNCFPInttt/)1 (1 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-99363.8 计算内部收益率计算内部收

50、益率 内部收益率（内部收益率（ IRR ）使投资项目的净现值为零使投资项目的净现值为零时的贴现率，计算公式为：时的贴现率，计算公式为： 修正内部收益率（修正内部收益率（ MIRR ）的计算公式为：）的计算公式为： 式中：式中：CPt为第为第t年正的净现金流量；年正的净现金流量；CNt为第为第t年负的净现金年负的净现金流量；流量；n为项目的寿命期；为项目的寿命期；r为对正的净现金流量进行再投资为对正的净现金流量进行再投资的年收益率；的年收益率；k为对负的净现金流量进行贴现的资金成本率；为对负的净现金流量进行贴现的资金成本率；MIRR为项目的修正内部收益率。为项目的修正内部收益率。ntttIRRN

51、CFNPV00)1 (ntntntkttnrtMIRRPVIFCNFVIFCP00,0)1 ()( 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-99363.8 计算内部收益率计算内部收益率 相关的函数相关的函数 RATE函数函数返回未来款项的各期利率。公式为返回未来款项的各期利率。公式为= RATE(nper,pmt,pv,fv,type,guess) IRR函数函数返回由数值代表的一组现金流的内部收益率。返回由数值代表的一组现金流的内部收益率。公式为：公式为：=IRR(values,guess) MIRR函数函数返回某一连续期间内现金流的修正内部收返回某一连续期间内现金

52、流的修正内部收益率。公式为：益率。公式为：= MIRR(values,finance_rate,reinvest_rate) XIRR函数函数返回一组现金流的内部收益率，这些现金返回一组现金流的内部收益率，这些现金流不一定定期发生。公式为：流不一定定期发生。公式为：=XIRR(values,dates,guess) 【例例3-13】 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-99363.9 互斥投资方案的比较分析互斥投资方案的比较分析 寿命期相同的寿命期相同的互斥投资比较互斥投资比较【例例3-14】 使用相关函数计算使用相关函数计算 运用单变量求解工具运用单变量求解工具

53、 寿命期不同的寿命期不同的互斥方案比较互斥方案比较 最小公倍寿命法（更新链法）最小公倍寿命法（更新链法）【例例3-15】 LCM函数：返回整数的最小公倍数。公式函数：返回整数的最小公倍数。公式为：为：=LCM(number1,number2, .) 等值年金法（净年值法）等值年金法（净年值法） 【例例3-16】 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-99363.10 资本限量条件下的投资决策资本限量条件下的投资决策 建立建立0-1规划模型规划模型 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-99363.10 资本限量条件下的投资决策资本限量条件

54、下的投资决策【例例3-17】资本限量条件下的投资决策资本限量条件下的投资决策 SUMPRODUCT函数：返回数组间对应元函数：返回数组间对应元素的相乘之和素的相乘之和 规划求解工具的运用规划求解工具的运用首先需要加载宏首先需要加载宏 2003：工具：工具/规划求解规划求解 2007：数据：数据/数据分析数据分析/规划求解规划求解 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-99364 Excel在投资风险分析中的应用在投资风险分析中的应用4.1 投资项目的盈亏平衡分析投资项目的盈亏平衡分析4.2 基于净现值的敏感性分析基于净现值的敏感性分析4.3 基于净现值的情境分析基于

55、净现值的情境分析4.4 投资项目的风险度量与处置投资项目的风险度量与处置 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-99364.1 投资项目的盈亏平衡分析投资项目的盈亏平衡分析会计盈亏平衡点相关指标的计算公式：会计盈亏平衡点相关指标的计算公式：安全边际率安全边际率 =（现有产销量（现有产销量-盈亏平衡点产销量）盈亏平衡点产销量）/现有产销量现有产销量 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-99364.1 投资项目的盈亏平衡分析投资项目的盈亏平衡分析 基于净现值的盈亏平衡点（现值盈亏平衡点）产销基于净现值的盈亏平衡点（现值盈亏平衡点）产销量应该

56、能够满足下面的公式：量应该能够满足下面的公式：式中：式中：Qt为第为第t年的产销量；年的产销量；pt为第为第t年的产品单价；年的产品单价；vt为第为第t年的单位变动成本；年的单位变动成本；Fct 为第为第t年的付现固定成本；年的付现固定成本；T为所为所得税税率；得税税率；Dt为第为第t年的折旧额；年的折旧额；i为项目的基准收益率；为项目的基准收益率；I为项目的初始投资；为项目的初始投资；n为项目的寿命期；为项目的寿命期；t为年份。为年份。 【例例4-1】投资项目盈亏平衡分析投资项目盈亏平衡分析 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-99364.2 基于净现值的敏感性

57、分析基于净现值的敏感性分析 影响项目净现值的因素影响项目净现值的因素 销售量、销售单价销售量、销售单价 单位变动成本、付现固定成本单位变动成本、付现固定成本 初始投资额、寿命期初始投资额、寿命期 基准收益率基准收益率 【例例4-2】单因素敏感性分析单因素敏感性分析 2003：数据：数据/模拟运算表模拟运算表 2007：数据：数据/数据工具数据工具/假设分析假设分析/数据表数据表 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-99364.2 基于净现值的敏感性分析基于净现值的敏感性分析 【例例4-3】双因素敏感性分析双因素敏感性分析 2003：数据：数据/模拟运算表模拟运算表

58、 2007：数据：数据/数据工具数据工具/假设分析假设分析/数据表数据表 【例例4-4】多因素敏感性分析多因素敏感性分析 2003：窗体控件：窗体控件 2007：表单控件：表单控件 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-99364.3 基于净现值的情境分析基于净现值的情境分析 情境分析情境分析 乐观情境乐观情境 最可能情境最可能情境 悲观情境悲观情境 【例例4-5】基于净现值的情境分析基于净现值的情境分析 建立名称公式建立名称公式 运用方案管理器运用方案管理器 2003：工具：工具/方案方案 2007：数据：数据/数据工具数据工具/假设分析假设分析/方案管理器方案管

59、理器 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-99364.4 投资项目的风险度量与处置投资项目的风险度量与处置 度量投资项目风险的有关计算公式：度量投资项目风险的有关计算公式： 式中：式中：E为投资收益分布的期望值；为投资收益分布的期望值； 为投资收益分布的标为投资收益分布的标准差；准差；V为变差系数（标准离差率）；为变差系数（标准离差率）；Kt为第为第t种情况下种情况下的投资收益（率）；的投资收益（率）；Pt为第为第t种情况出现的概率；种情况出现的概率；n为出为出现可能情况的种数。现可能情况的种数。 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-

60、99364.4 投资项目的风险度量与处置投资项目的风险度量与处置【例例4-6】投资风险的度量】投资风险的度量 相关函数相关函数SQRT函数函数计算一个正数的正平方根。公式计算一个正数的正平方根。公式为：为：=SQRT(number)NORMDIST函数函数返回指定平均值和标准偏返回指定平均值和标准偏差的正态分布函数，公式为：差的正态分布函数，公式为：=NORMDIST(x,mean,standard_dev,cumulative) 纳税人俱乐部纳税人俱乐部 400-650-9936400-650-99364.4 投资项目的风险度量与处置投资项目的风险度量与处置【例例4-7】投资风险的处置】投资