Excel在财务中的应用.ppt

Excel在财务中的高级应用 主讲内容 Excel在财务中的应用基础 1 Excel在财务中的应用基础 1 1 代码化 数据标准化 1 2 日期 分数的输入1 3 引用的应用1 4 数据清单1 5 工作表的保护及安全打开 1 1 代码化 数据标准化 以电子计算机为计算工具处理财务数据的基本概念 代码化 对系统原始数据按一定的规律进行编码的处理 数据处理代码化 缩短了数据项的长度 减少数据占用的存储空间 提高会计数据处理的速度和精度 方便计算机进行排序 分类 汇总等操作 输入方式 代码 如 现金科目代码输入方式 1001 1 2 日期 分数的输入 日期在EXCEL中其实质为数字1900 1 1代表1分数与日期输入5 8在EXCEL中被识别为5月8日分数5 8的输入方式 0 空格 5 8 1 3 引用的使用 引用的作用在于标识工作表上的单元格或单元格区域 并指明公式中所使用的数据的位置 通过引用 可以在公式中使用工作表不同部分的数据 或者在多个公式中使用同一个单元格的数值 还可以引用同一个工作簿中不同工作表上的单元格和其他工作簿中的数据 引用不同工作簿中的单元格称为链接 相对引用绝对引用 混合引用 链接名称 1 3 引用的使用 相对引用 在创建公式时 单元格或单元格区域的引用通常是相对于包含公式的单元格的相对位置 随公式位置的改变 其引用的单元格也会相应发生变化 绝对引用 复制公式时Excel不调整引用 如 C 1 包括绝对引用单元格的公式 无论将其复制到什么位置 总是引用特定的单元格 链接 引用不同工作簿中的单元格 每次按F4键时 Excel会在以下组合间切换 绝对列与绝对行 例如 A 1 相对列与绝对行 A 1 绝对列与相对行 A1 以及相对列与相对行 A1 当切换到用户所需的引用时 按回车键确认即可 1 4动态引用 OFFSET函数可以对动态数据进行查询 它以指定的引用为参照系 通过给定偏移量得到新的引用 返回的引用可以为一个单元格或单元格区域 并可以指定返回的行数或列数 其语法格式如下 OFFSET reference rows cols height width 可以看出 该函数最多包含五个参数 后两个参数为可选项 其中 Reference作为偏移量参照系的引用区域 它必须为对单元格或相连单元格区域的引用 否则 函数OFFSET返回错误值 VALUE Rows为相对于偏移量参照系左上角单元格 上 下 偏移的行数 Cols为相对于偏移量参照系左上角单元格 左 右 偏移的列数 Heigh为高度 即所要返回的引用区域的行数 Width为宽度 即所要返回的引用区域的列数 1 4 数据清单 在Excel中 数据库是作为一个数据清单来看待 我们可以理解数据清单就是数据库 在一个数据库中 信息按记录存储 每个记录中包含信息内容的各项 称为字段 Excel提供了一整套功能强大的命令集 使得管理数据清单 数据库 变得非常容易 我们可以完成下列工作 排序 在数据清单中 针对某些列的数据 我们可以用数据菜单中的排序命令来重新组织行的顺序 可以选择数据和选择排序次序 或建立和使用一个自定义排序次序 筛选 可以利用 数据 菜单中的 筛选 命令来对清单中的指定数据进行查找和其它工作 一个经筛选的清单仅显示那些包含了某一特定值或符合一组条件的行 暂时隐藏其它行 数据记录单 一个数据记录单提供了一个简单的方法 让我们从清单或数据库中查看 更改 增加和删除记录 或用你指定的条件来查找特定的记录 自动分类汇总 利用 数据 菜单的 分类汇总 命令 在清单中插入分类汇总行 汇总你所选的任意数据 当你插入了分类汇总后 MicrosoftExcel自动为你在清单底部插入一个 总计 行 Excel数据库表格遵循的准则 建立和使用Excel数据库表格时 用户必须遵循以下的基本准则 1 一个数据库最好单独占据一个工作表 避免将多个数据库放到一个工作表上 2 数据记录紧接在字段名下面 不要使用空白行将字段名和第一条记录数据分开 3 避免在数据库中间放置空白行或空白列 任意两行的内容不能完全相同 4 避免将关键数据放到数据库左右两侧 防止数据筛选时这些数据被隐藏 5 字段名的字体 对齐方式 格式 边框等样式 应当与其他数据的格式相区别 6 条件区域不要放在数据库的数据区域下方 因为用记录单添加数据时 Excel会在原数据库的下边添加数据记录 如果数据库的下边非空 就不能利用记录单添加数据 2 公式的编辑 公式的组成 运算单元 运算符 函数及参数 引用 常数 文本 时间等公式中元素的结构或次序决定了最终的计算结果 Excel中的公式遵循一个特定的语法或次序 最前面是等号 后面是参与计算的元素 运算数 这些参与计算的元素又是通过运算符隔开的 每个运算数可以是不改变的数值 常量数值 单元格或引用单元格区域 标志 名称 或工作表函数 2 1公式中的运算符 1 算术运算符 完成基本的数学运算 2 比较操作符 比较运算符用于比较两个值 当用操作符比较两个值时 结果是一个逻辑值 不是TRUE就是FALSE 3 文本运算符 使用和号 可以将文本连接起来 4 引用操作符 引用运算符的作用是确定在公式中需要参与运算的数据在工作表中所处的位置 可以使用三个运算符 冒号 逗号和空格 2 1公式中的运算符 冒号 区域运算符 对两个单元格之间 包括两个单元格在内的所有单元格参与运算 逗号 联合运算符 可将多个引用合并为一个引用 空格 交叉运算符 它是将同时属于两个引用的单元格区域进行引用 即两个单元格引用相重叠的区域 2 2运算符及其优先级 2 3公式审核及出错检查 1 追踪引用单元格 工具 公式审核 追踪引用单元格 工具 公式审核 取消所有追踪箭头 2 追踪从属单元格 工具 公式审核 追踪从属单元格 工具 公式审核 取消所有追踪箭头 3 公式审核工具栏 工具 公式审核 显示 公式审核 工具栏 2 4常见的公式错误信息 3 函数的运用 3 1函数的语法格式3 2常用函数1 数学函数2 文本函数3 子字符串函数4 逻辑函数5 查找和引用函数6 财务函数 3 1函数的语法格式 函数名 参数1 参数2 参数n 在使用函数时 应注意以下几个问题 1 函数名与其后的括号 之间不能有空格 2 当有多个参数时 参数之间要用逗号 分隔 3 参数部分总长度不能超过1024个字符 4 参数可以是数值 文本 逻辑值 单元格地址或单元格区域地址 也可以是各种表达式或函数 5 函数中的逗号 引号 等都是半角字符 而非全角的中文字符 3 2常用函数介绍 1 求和函数 1 无条件求和SUM函数该函数的功能是计算所选取的单元格区域中所有数值的和 2 条件求和SUMIF函数SUMIF函数的功能是根据指定条件对若干单元格求和 3 SUMPRODUCT函数功能是在给定的几组数组中 将数组间对应的元素相乘 并返回乘积之和 3 DSUM函数功能是对数据库表格进行多条件汇总 3 2常用函数介绍 2 平均值函数 AVERGAE函数功能是计算给定参数的算术平均值 3 最小值与最大值函数 MIN MAX函数MIN函数的功能是给定参数表中的最小值MAX函数的功能是给定参数表中的最大值 3 2常用函数介绍 4 数学函数 1 ABS函数 返回数字的绝对值 2 SQRT函数 计算一个正数的正平方根 3 ROUND函数 返回某个数字按指定位数舍入后的数字 3 2常用函数介绍 5 条件函数IF函数也称条件函数 它根据参数条件的真假 返回不同的结果 6 计数函数COUNT函数 计算给定区域内数值型参数的数目COUNTIF函数 计算给定区域内满足特定条件的单元格的数目 3 2常用函数介绍 7 逻辑函数AND函数 表示逻辑与OR函数 表示逻辑或NOT函数 功能是对参数的逻辑值求反这三个函数一般与IF函数结合使用 3 2常用函数介绍 8 查找函数 1 LOOKUP函数 返回向量 单行区域或单列区域 或数组中的数值 LOOKUP lookup value lookup vector result vector 2 VLOOKUP函数 在表格或数值数组的首列查找指定的数值 并由此返回表格或数组当前行中指定列处的数值 VLOOKUP lookup value table array col index num range lookup 3 HLOOKUP函数 从表格或数值数组的首行查找指定的数值 并由此返回表格或数组当前列中指定行处的数值 HLOOKUP lookup value table array row index num range lookup 3 2常用函数介绍 9 引用函数INDEX函数 返回表格或区域中的数值或对数值的引用 INDEX array row num column num 4 数组公式和数据透析 数组公式数据透析 4 1数组公式 1 数组公式的输入数组公式与一般公式不同之处在于它被括在大括号 中 其输入步骤如下 1 选中一个单元格或者单元格区域 说明 如果数组公式只是返回一个结果 需要选择保存用来保存结果的那一个单元格 如果数组公式返回多个结果 则需要选中需要保留数组公式计算结果的单元格区域 2 按照前面介绍的公式输入规则 输入公式的内容 3 公式输完后 按 Ctrl Shift Enter 组合键结束操作 4 2数据透析 数据透视分析就是从数据库的特定字段中概括信息 从而方便从各个角度查看 分析数据 并可对数据库中的数据进行汇总统计 它在Excel中的实现工具是数据透视表 数据透视表是一种对大量数据快速汇总和建立交叉列表的动态工作表 而数据透视图是一种能够根据数据处理需要 查看部分数据的图表对比效果 有些类似前面介绍的动态图表功能 另外 Excel中还可以根据数据透视表制作不同格式的数据透视报告 数据透析 用2007 发现数据透视表里面没有多重区域合并的选项了 那有什么办法用2007实现多表合并 快捷键ALT D P 调用数据透视表向导 5 应用EXCEL完成财务会计工作 应用EXCEL完成 期初余额 凭证清单 期末余额表 明细账 总账 报表 报表分析等工作 6 财务函数的应用 Excel提供了许多有关财务 投资 偿还 利息及折旧方面的函数 在工作表中运用这些函数可以较松地完成相关的财务运算 或者对其他财务管理软件的运算输出数据进行验证 6 财务函数的应用 6 1投资计算函数6 2折旧计算函数6 3偿还率计算函数 6 1投资计算函数 投资计算函数常见参数说明贷款定额还款期数计算函数NPER贷款分期偿还金额计算函数PMT贷款利息 本金金额计算函数IPMT PPMT投资未来值计算函数FV投资偿还额现值计算函数PV投资定期现金流净现值计算函数NPV投资不定期现金流净现值计算函数XNPV 投资计算函数常见参数说明 Excel投资分析方面的函数 这些函数使用的参数大致相同 意义相近 如下表所示 一 贷款定额还款期数计算函数NPER 1 NPER函数知识介绍1 NPER函数功能 基于固定利率及等额分期付款方式 返回一项投资或贷款的期数 2 语法形式 NPER rate pmt pv fv type 3 参数说明4 注意点 第一所有期数的单位要统一第二所有的入帐用正数表示 支付额用负数表示 一 贷款定额还款期数计算函数NPER 2 实例 超市贷款还款期数预测 实例内容 由于拓展业务需要 欲向银行贷款1 000 000元 根据新风超市目前的经济与经营状况进行判断 再综合其他各种因素考虑 新风超市每月有能力且最适合的还款额为12 000元 若以7 50 的平均年利率作为参考 选择期 月 末付款方式 至少需要多久能还清贷款 一 贷款定额还款期数计算函数NPER 实例操作步骤 步骤1 构造数据模型 如图6 1所示 步骤2 选定B4单元格 在辑栏中输入 NPER B2 12 B3 B1 步骤3 单击 输入 按钮 此时可以这笔贷款方案需要偿还的时间 月 图6 1例6 1数据模型 二 贷款分期偿还金额计算函数PMT 1 PMT函数知识介绍1 PMT函数功能 基于固定利率及等额分期付款方式 返回投资或贷款的每期付款额 2 语法形式 PMT rate nper pv fv type 3 参数说明 二 贷款分期偿还金额计算函数PMT 2 实例2超市贷款分期偿还金额计算 实例内容 君华银行向新风超市提供了四种基于固定利率及等额分期期末付款的贷款方案 还款期数及年利率如表6 2所示 新风超市应该选择哪一种贷款方案比较合适 表6 2君华银行贷款方案表 二 贷款分期偿还金额计算函数PMT 实例操作步骤 步骤1 构造数据模型 如图6 3所示 步骤2 选定D8单元格 在编辑栏中输入 PMT C8 12 B8 12 B 1 步骤3 单击 输入 按钮 此时可以看到第一种贷款方案的每月还款额 图6 3例6 2数据模型 三 贷款利息 本金金额计算函数IPMT PPMT 1 IPMT函数知识介绍1 IPMT函数功能 基于固定利率及等额分期付款方式 返回投资或贷款在某一给定期次内的利息偿还额 2 语法形式 IPMT rate per nper pv fv type 3 参数说明 三 贷款利息 本金金额计算函数IPMT PPMT 2 PPMT函数知识介绍1 PPMT函数功能 基于固定利率及等额分期付款方式 返回投资或贷款在某一给定期次内的本金偿还额 2 语法形式 PPMT rate per nper pv fv type 3 参数说明 三 贷款利息 本金金额计算函数IPMT PPMT 3 实例3超市贷款分期偿还利息与本金金额计算 实例内容 在实例2中新风超市确定贷款方案后 第一年每月偿还的利息和本金分别是多少 三 贷款利息 本金金额计算函数IPMT PPMT 实例操作步骤 步骤1 构造数据模型 如图6 5所示 步骤2 选定B14单元格 在编辑栏中输入 IPMT C 9 12 A14 B 9 12 B 1 步骤3 选定C14单元格 在编辑栏中输入 PPMT C 9 12 A14 B 9 12 B 1 步骤4 用填充柄填充B和C两列 可以看到结果 图6 5例6 3数据模型 四 投资未来值计算函数FV 1 FV函数知识介绍1 FV函数功能 基于固定利率及等额分期付款方式 返回某项投资的未来值 2 语法形式 FV rate nper pmt pv type 3 参数说明 四 投资未来值计算函数FV 2 实例4超市存款投资未来值计算 实例内容 新风超市计划在5年后创办一所新风希望小学 需要资金200 000元 从本年开始筹备资金 打算在每月月初存入5 000元 在整个投资期间内 平均年投资回报率为7 6 3年后这笔存款是否足够创办新风希望小学 四 投资未来值计算函数FV 实例操作步骤 步骤1 构造数据模型 如图6 7所示 步骤2 选定B30单元格 在编辑栏中输入 FV B27 12 B28 12 B29 1 步骤3 单击 输入 按钮 此时可以看到这笔存款3年后的价值 图6 7例6 4数据模型 五 投资偿还额现值计算函数PV 1 PV函数知识介绍1 PV函数功能 返回投资的现值 现值为一系列未来付款当前值的累积和 可以作为一种权衡长期投资的方法 2 语法形式 PV rate nper pmt fv type 3 参数说明 五 投资偿还额现值计算函数PV 2 实例5超市保险投资决策 实例内容 新风超市计划为高级员工提高福利 购买一笔医疗保险 购买的成本为28 000元 该保险可以在今后30年中每月末回报150元 投资年回报率为5 6 新风超市购买这份保险是否划算 五 投资偿还额现值计算函数PV 实例操作步骤 步骤1 构造数据模型 如图6 9所示 步骤2 选定B35单元格 在编辑栏中输入 PV B32 12 B33 12 B34 步骤3 单击 输入 按钮 此时可以看到这份保险的现值 图6 9例6 5数据模型 六 投资定期现金流净现值计算函数NPV 1 NPV函数知识介绍1 NPV函数功能 计算一组定期现金流的净现值 通过使用贴现率以及一系列未来支出 负值 和收入 正值 返回一项投资的净现值 当前纯利润 2 语法形式 NPV rate value1 value2 3 参数说明 Rate是应用于现金流的贴现率 为一固定值 value1 value2 代表1到29笔支出及收入的参数值 value1 value2 所属各期间的长度必须相等 而且支付及收入的时间都发生在期末 如果支付的时间发生在期初 不能作为value参数 而是将收入转换成净现值后再扣除支付金额 六 投资定期现金流净现值计算函数NPV 2 实例6超市收购投资决策 实例内容 新风超市计划收购一小超市 收购成本为500 000元 必须预先付清 经过市场调查 预计今后6年的营业收入可达到110 000元 130 000元 160 000元 180 000元 190 000元和210 000元 每年的贴现率为8 7 这项收购计划是否可行 六 投资定期现金流净现值计算函数NPV 实例操作步骤 步骤1 构造数据模型 如图6 11所示 步骤2 选定B45单元格 在编辑栏中输入 NPV B38 B39 B44 B37 步骤3 单击 输入 按钮 此时可以看到收购小超市投资计划的当前纯利润 图6 11例6 6数据模型 七 投资不定期现金流净现值计算函数XNPV 1 XNPV函数知识介绍1 XNPV函数功能 返回一组现金流的净现值 这些现金流不一定定期发生 2 语法形式 XNPV rate values dates 3 参数说明 rate是应用于现金流的贴现率 为一固定值 dates是与现金流支付相对应的支付日期表 values是与dates中的支付时间相对应的一系列现金流转 七 投资不定期现金流净现值计算函数XNPV 2 实例7超市项目投资决策 实例内容 新风超市计划在2008年5月3日拿出500 000元资金用于某项投资 预计可于如下时间内分4次获取返回资金 2008年12月3日返回120 000元 2009年2月3日返回160 000元 2009年5月3日返回160 000元 2009年12月3日返回90 000元 资金流转折扣为8 2 这项投资是否划算 七 投资不定期现金流净现值计算函数XNPV 实例操作步骤 步骤1 构造数据模型 如图6 13所示 图6 13例6 7数据模型 步骤2 将A50 A54单元格区域格式设置为 日期 结果如图6 14所示 步骤3 选定B55单元格 在编辑栏中输入 XNPV B47 B50 B54 A50 A54 步骤4 单击 输入 按钮 此时可以看到这项投资计划的当前纯利润 图6 14转换日期序列 6 2折旧计算函数 折旧计算函数常见参数说明直线折旧函数SLN固定余额递减折旧函数DB双倍余额递减折旧函数DDB年限总和折旧函数SYD可变余额递减折旧函数VDB 折旧计算函数常见参数说明 Cost 固定资产原值Salvage 固定资产使用年限终了时的估计残值Life 固定资产进行折旧计算的周期总数Period 进行折旧计算的期次 必须与life使用的单位相同 上一页 一 五种折旧计算函数 实例8计算超市货架的折旧金额 实例内容 新风超市计划将现有一批已使用3年的货架转手卖出 3年前的购买价值为50 000元 使用年限为8年 预计报废价值为15 000元 使用直线折旧 固定余额递减折旧 双倍余额递减折旧 年限总和折旧和可变余额递减折旧5种不同的折旧方法计算出这批货架这3年的折旧金额 一 五种折旧计算函数 实例操作步骤 步骤1 构造数据模型 如图6 16所示 图6 16例6 8数据模型 一 五种折旧计算函数 步骤2 选定B7单元格 在编辑栏中输入 SLN B 1 B 2 B 3 步骤3 选定C7单元格 在编辑栏中输入 DB B 1 B 2 B 3 A7 步骤4 选定D7单元格 在编辑栏中输入 DDB B 1 B 2 B 3 A7 步骤5 选定E7单元格 在编辑栏中输入 SYD B 1 B 2 B 3 A7 步骤6 选定F7单元格 在编辑栏中输入 VDB B 1 B 2 B 3 0 A7 2 步骤7 用填充柄工具填充B C D E F列 可以看到结果 一 五种折旧计算函数 SLN函数知识介绍1 SLN函数功能 用直线折旧的计算方法 返回一项资产每期的直线折旧费 2 语法形式 SLN cost salvage life 3 参数说明 一 五种折旧计算函数 3 DB函数知识介绍1 DB函数功能 用固定余额递减的计算方法 返回一项资产在给定期间内的折旧值 用于计算固定利率下的资产折旧金额 2 语法形式 DB cost salvage life period month 3 参数说明 month为第一年的月份数 如省略 则假设为12 一 五种折旧计算函数 4 DDB函数知识介绍1 DDB函数功能 用双倍余额递减法或其他指定计算方法 返回一笔资产在给定期间内的折旧值 2 语法形式 DDB cost salvage life period factor 3 参数说明 factor参数为余额递减速率 也称为折旧因子 如果factor被省略 则假设为2 即双倍余额递减法 这5个参数都必须为正数 一 五种折旧计算函数 5 SYD函数知识介绍1 SYD函数功能 用年限总和折旧计算方法 返回一笔资产在给定期间内的折旧值 2 语法形式 SYD cost salvage life per 3 参数说明 per参数为期间 单位与life相同 一 五种折旧计算函数 6 VDB函数知识介绍1 VDB函数功能 用可变余额递减计算方法 返回一笔资产在给定期间内的折旧值 2 语法形式 VDB cost salvage life star period end period factor no switch 3 参数说明 star period参数为进行折旧计算的起始期间 单位与life相同 End period参数为进行折旧计算的截止期间 单位与life相同 factor参数为余额递减速率 也称为折旧因子 如果factor被省略 则假设为2 即双倍余额递减法 No switch参数指定当折旧值大于余额递减计算值时 是否转用直线折旧法 为一逻辑值 6 3偿还率计算函数 投资偿还率计算函数RATE投资定期现金流内部收益率计算函数IRR投资修正收益率计算函数MIRR 一 投资偿还率计算函数RATE 1 RATE函数知识介绍1 RATE函数功能 返回年金的各期利率 2 语法形式 RATE nper pmt pv fv type guess 3 参数说明 guess参数为预期利率 如果省略 则假设该值为10 一 投资偿还率计算函数RATE 2 实例9银行的贷款偿还率计算 实例内容 新风超市于5年前创建 创建初期向银行贷款200 000元 期限5年 每个月偿还4 000元 这笔贷款银行的年收益率是多少 一 投资偿还率计算函数RATE 实例操作步骤 步骤1 构造数据模型 如图6 18所示 步骤2 选定B4单元格 在编辑栏中输入 RATE B2 12 B3 B1 单击 输入 按钮 步骤3 选定B5单元格 在编辑栏中输入 B4 12 单击 输入 按钮 此时可以看到银行的年收益率 图6 18例6 9数据模型 二 投资定期现金流内部收益率计算函数IRR 按照传统的内部收益率 IRR 法 项目的净现值 NPV 为零时的贴现率 就是IRR 通常 我们以此IRR与市场利率或预定的收益率相比较 对该项目的可行性进行评价 比如 一个简单的两年到期项目 初始投入1000元 第一年净流入500元 第二年600元 市场利率为10 采用传统的IRR法 NPV 1000 500 1 IRR 600 1 IRR 1 IRR 0 由此模型可求出IRR 二 投资定期现金流内部收益率计算函数IRR 1 IRR函数知识介绍1 IRR函数功能 返回由数值代表的一组现金流的内部收益率 2 语法形式 IRR values guess 3 参数说明 Values参数为数组或单元格的引用 包含用来计算返回的内部收益率的数字 Guess参数为对函数IRR计算结果的估计值 二 投资定期现金流内部收益率计算函数IRR 2 实例10超市投资定期现金流内部收益率计算 实例内容 5年来新风超市每年的收入分别为50 000元 70 000元 100 000元 110 000元 150 000元和180 000元 期初投资的200 000元现在内部收益率是多少 二 投资定期现金流内部收益率计算函数IRR 实例操作步骤 步骤1 构造数据模型 如图6 21所示 步骤2 选定B13单元格 在编辑栏中输入 IRR B7 B12 步骤3 单击 输入 按钮 此时可以看到新风超市近5年的内部收益率 图6 21例6 10数据模型 三 投资修正收益率计算函数MIRR 传统的IRR法是建立在以一个 虚拟的再投资利率 再投资的假设基础之上的 随着现代经济系统的波动性日益频繁 传统的IRR法已经很难正确地评价项目的真实可行性 而MIRR法 就是对IRR法的一个简单合理的修正 使之更加有效与真实 三 投资修正收益率计算函数MIRR 初始投入1000元 第一年净流入500元 第二年600元 市场利率为10 采用传统的IRR法 NPV 1000 500 1 IRR 600 1 IRR 1 IRR 0 由此模型可求出IRR 我们对此模型进行一些 变动 但仍以NPV为零 1000 1 IRR 1 IRR 500 1 IRR 600 该等式经济含义为 一个项目 证券 面值为1000元的第二年 末 总价值 可分拆成面值500元一年期的 与面值600元二年期的项目 价值 之和 此时 第一年收到的500元再投资利率应当是市场利率10 而对应的1000元项目的总收益率 R 为 1000 1 R 500 1 10 600 令 1 R 1 MIRR 1 IRR 由此可求出MIRR 对于多期拓展 就可得到 期末收益 期初投资 1 MIRR n n是幂次数 等于投资期数 三 投资修正收益率计算函数MIRR 1 MIRR函数知识介绍1 MIRR函数功能 返回某一连续期间内现金流的修正内部收益率 函数MIRR同时考虑了投资的成本和现金再投资的收益率 2 语法形式 MIRR values finance rate reinvest rate 3 参数说明 Values参数为一个数组或对包含数字的单元格的引用 Finance rate参数为现金流中使用的资金支付的利率 Reinvest rate参数为将现金流再投资的收益率 三 投资修正收益率计算函数MIRR 2 实例11超市投资修正收益率计算 实例内容 5年来 新风超市将每年所获得的收益用于重新投资 每年的收益率为10 那开业5年后的修正收益率是多少 三 投资修正收益率计算函数MIRR 实例操作步骤 步骤1 构造数据模型 如图6 23所示 步骤2 选定B16单元格 在编辑栏中输入 MIRR B7 B12 B5 B15 步骤3 单击 输入 按钮 此时可以看到新风超市近5年的内部修正收益率 图6 23例6 11数据模型 7 应用EXCEL完成管理会计工作 7 1本量利分析7 2回归分析7 3规划求解 7 1本量利分析 量本利分析 即成本 数量 利润分析 是通过数学分析和图示分析等形式对销售数量 销售单价 变动成本 固定成本等因素与利润指标的内在联系进行研究 以协助管理者进行项目规划和期间计划的预测分析方法 1 量本利分析法的基本假设 假设能够将企业的所有成本都按照成本习性精确地划分为变动成本和固定成本 假设销售单价和销售结构在相关范围内保持不变 7 1本量利分析 假设企业的生产能力和生产效率在相关范围内保持不变 假设企业当期的产销量基本平衡 存货水平保持不变 2 量本利的基本关系式利润 销售收入 总成本 销售收入 销售成本 期间费用 边际贡献 固定成本 销售量 销售单价 单位变动成本 固定成本 7 2回归分析 1 一元线性回归模型 2 一元非线性回归模型 7 2回归分析 3 多元线性回归模型 4 多元非线性回归模型例如 柯柏 道格拉斯生产函数式中 L和K分别为劳动力和固定资本 预测函数 1LINEST函数2LOGEST函数3TREND函数4GROWTH函数5FORECAST函数6SLOPE函数7INTERCEPT函数 1LINEST函数 LINEST使用最小二乘法对已知数据进行最佳直线拟合 并返回描述此直线的数组 LINEST known y s known x s const stats 直线的公式为 y mx bory m1x1 m2x2 b 如果有多个区域的x值 2LOGEST函数 在回归分析中 计算最符合数据的指数回归拟合曲线 并返回描述该曲线的数值数组 因为此函数返回数值数组 故必须以数组公式的形式输入 LOGEST known y s known x s const stats 此曲线的公式为 y b m x或y b m1 x1 m2 x2 如果有多个x值 3TREND函数 返回一条线性回归拟合线的值 即找到适合已知数组known y s和known x s的直线 用最小二乘法 并返回指定数组new x s在直线上对应的y值 TREND known y s known x s new x s const 4GROWTH函数 根据现有的数据预测指数增长值 根据现有的x值和y值 GROWTH函数返回一组新的x值对应的y值 可以使用GROWTH工作表函数来拟合满足现有x值和y值的指数曲线 语法GROWTH known y s known x s new x s const 5FORECAST函数 根据已有的数值计算或预测未来值 此预测值为基于给定的x值推导出的y值 已知的数值为已有的x值和y值 再利用线性回归对新值进行预测 可以使用该函数对未来销售额 库存需求或消费趋势进行预测 语法FORECAST x known y s known x s 6SLOPE函数 返回根据known y s和known x s中的数据点拟合的线性回归直线的斜率 斜率为直线上任意两点的重直距离与水平距离的比值 也就是回归直线的变化率 语法SLOPE known y s known x s 7INTERCEPT函数 利用现有的x值与y值计算直线与y轴的截距 截距为穿过已知的known x s和known y s数据点的线性回归线与y轴的交点 当自变量为0 零 时 使用INTERCEPT函数可以决定因变量的值 例如 当所有的数据点都是在室温或更高的温度下取得的 可以用INTERCEPT函数预测在0 C时金属的电阻 语法INTERCEPT known y s known x s 7 3规划求解 在计划管理中 经常会遇到各种规划问题 例如 人力资源的调度 产品生产的安排 运输线路的规划 生产材料的搭配 采购批次的确定等 这类问题有一个共同要求 那就是 如何合理利用各种约束资源实现最佳的经济效益 也就是达到常量最高 利润最大 成本最低 费用最省等目标 这就是本节要解决的在约束条件下寻求目标函数最优的规划问题 一般来讲 这类规划问题都具有如下三个特点 1 所求问题都有单一的目标 如求生产的最低成本 求运输的最佳路线 求产品的最大盈利 求产品周期的最短时间以及求其他目标函数的最佳值等 2 总是有明确的不等式约束条件 比如库存不能低于一定的数量 否则造成原料短缺或产品缺货 生产产品不能超过一定额度 否则会造成商品积压等 3 问题都有直接或间接影响约束条件的一组输入值 7 3规划求解 规划求解工具用于解决复杂的方程求值及各类线性或非线形有约束优化问题 规划问题种类繁多 从数学角度来看 规划问题都有下述共同特征 这些特征也构成了Excel规划求解工具界面中的主要部分 决策变量 每个规划问题都有一组需要求解的未知数 称作决策变量 这组决策变量的一组确定值就代表一个具体的规划方案 在Excel规划求解工具中 可变单元格代表的是决策变量 约束条件 对于规划问题的决策变量通常都有一定的限制条件 称作约束条件 约束条件可以用与决策变量有关的不等式或等式来表示 目标 每个问题都有一个明确的目标 如利润最大或成本最小 目标通常可用与决策变量有关的函数表示 在Excel规划求解工具中 包含公式的目标单元格代表的是目标函数