Excel在财务管理中的应用.ppt

Excel在财务管理中的应用 韩良智编著清华大学出版社 2009年8月第1版 目录 1Excel基础知识2资金时间价值的计算3内部长期投资决策4投资项目的风险分析与处置5证券投资分析与决策6资本成本与资本结构 7筹资预测与决策分析8流动资产管理9销售收入管理10成本费用管理11利润管理12财务报表分析与预测13企业价值评估 1Excel基础知识 1 1中文版Excel2003概述1 2管理工作簿1 3输入和编辑数据1 4管理工作表1 5打印管理 1 1中文版Excel2003概述 1 1 1Excel2003的启动与退出1 1 2Excel2003的窗口结构 1 1 1Excel2003的启动与退出 Excel2003的启动与退出 开始 程序 MicrosoftExcel 双击任意一个扩展名为 xls的Excel文件Excel2003的退出 文件 退出 单击Excel窗口右上角的关闭命令按钮 双击Excel窗口左上角的控制菜单按钮 按快捷键 Alt F4 1 1 2Excel2003的窗口结构 菜单栏常用工具栏格式工具栏编辑栏工作表标签工作表格区滚动条状态栏任务窗格 1 2管理工作簿 1 2 1新建工作簿新建空白工作簿 单击工具栏上的 新建 按钮使用模板创建工作簿 单击 文件 新建 在 新建工作簿 任务窗格中单击 本机上的模板 1 2 2打开已有的工作簿 文件 打开 1 2 3保存工作簿 文件 保存 或 另存为 1 2 4并排比较工作簿 窗口 与 并排比较 1 2 5关闭工作簿 文件 关闭 1 3输入和编辑数据 1 3 1选取单元格1 3 2输入数据的一般方法1 3 3特殊数据的输入方法1 3 4编辑数据1 3 5数据有效性的设置 1 3 1选取单元格 选取单个单元格直接用鼠标选取采用 定位 命令选取选取单元格区域选取整行或整列选取全部单元格选取连续的单元格区域拖动鼠标利用 Shift 键选取不连续的单元格区域利用 Ctrl 键 1 3 2输入数据的一般方法 数据的类型文本型数据数值型数据日期型数据时间型数据输入数据的基本方法输入几种不同类型的数据应注意的问题 1 3 3特殊数据的输入 输入相同数据采用自动填充方式输入相同数据采用数组输入方式输入相同数据输入序列数据填充序列数据设置序列填充方式单击 编辑 填充 序列 在序列对话框中进行相应的设置自定义序列填充 工具 选项 自定义序列 1 3 4编辑数据 修改数据在公式编辑栏中修改数据在单元格中修改数据移动数据利用剪切和粘贴命令利用鼠标拖动复制数据利用复制和粘贴命令利用多重剪贴板工具 1 3 4编辑数据 清除单元格内容直接按 Del 键在 编辑 菜单中选择 清除 查找与替换 编辑 查找 或按 Ctrl F 组合键 替换 选项卡 或直接按 Ctrl H 组合键为单元格添加批注 插入 批注 单击右键 在快捷菜单中选择 插入批注 命令 1 4管理工作表 1 4 1使用多张工作表1 4 2调整行 列和单元格1 4 3设置工作表格式1 4 4保护单元格与保护工作表1 4 5隐藏与显示1 4 6划分窗口 1 4 1使用多张工作表 选定工作表 鼠标左键 Shift 键和 Ctrl 键切换工作表 标签滚动按钮或 Ctrl PgUp 或 Ctrl PgDn 插入工作表 插入 工作表 移动或复制工作表 编辑 移动或复制工作表 删除工作表 编辑 删除工作表 重命名工作表 单击鼠标右键 在快捷菜单中选择 重命名 为工作表添加链接 单击鼠标右键 在快捷菜单中选择 选择性粘贴 粘贴链接 1 4 2调整行 列和单元格 插入行 列或单元格 插入 行 列 或 单元格 删除行 列或单元格 编辑 删除 按 Del 键调整行高和列宽设置最适合的行高或列宽 格式 行 或列 最适合的行高 或 最适合的列宽 用鼠标调整行高或列宽 拖动 双击指定行高或列宽的精确数值 1 4 3设置工作表格式 设置单元格格式 格式 单元格 自动套用格式 格式 自动套用格式 使用条件格式 格式 条件格式 取消与恢复网格线 工具 选项 视图 1 4 4保护单元格与保护工作表 保护单元格 格式 单元格格式 保护 保护工作表 工具 保护 保护工作表 1 4 5隐藏与显示 隐藏与显示行或列隐藏行或列 格式 行 或 列 隐藏 用鼠标拖动显示行或列 格式 行 或 列 取消隐藏 隐藏与显示工作表隐藏工作表 格式 工作表 隐藏 显示工作表 格式 工作表 取消隐藏 1 4 6划分窗口 重排窗口 窗口 新建窗口 窗口 重排窗口 拆分窗口 窗口 拆分 窗口 取消拆分 冻结窗格 窗口 冻结窗格 窗口 取消冻结窗格 1 5打印管理 1 5 1页面设置1 5 2分页设置1 5 3打印预览1 5 4打印输出 1 5 1页面设置 设置页面包括页面 页边距 页眉 页脚 工作表设置 文件 页面设置 打印区域设置 文件 打印区域 设置打印区域 文件 打印区域 取消打印区域 1 5 2分页设置 水平分页设置选定要插入分页符的行 执行 插入 分页符 垂直分页设置选定要插入分页符的列 执行 插入 分页符 交叉分页设置选定某个单元格 执行 插入 分页符 1 5 2分页设置 分页调整 视图 分页预览 用鼠标单击并拖动分页符至需要的位置完成后执行 视图 普通 删除分页符选取整个工作表 执行 插入 重置所有分页符 1 5 3打印预览 打印预览执行 文件 打印预览 直接单击工具栏上的 打印预览 按钮1 5 4打印输出打印输出单击工具栏上的 打印 按钮执行 文件 打印 直接按 Ctrl P 组合键 2资金时间价值的计算 2 1终值的计算2 2现值的计算2 3年金的终值和现值2 4计息周期与终值和现值 2 1终值的计算 2 1 1一笔现金流的单利终值计算与分析模型2 1 2一笔现金流的复利终值计算与分析模型2 1 3单利与复利终值选择计算与比较分析模型2 1 4复利终值系数计算模型 2 1 1一笔现金流的单利终值计算与分析模型 单利终值的计算式中 FS为单利终值 P为现在的一笔资金 iS为单利年利率 n为计息期限 2 1 1一笔现金流的单利终值计算与分析模型 例2 1 模型的功能 计算单利终值分析本金 利息和单利终值对计息期限的敏感性绘制本金 利息和单利终值与计息期限之间的关系图 2 1 1一笔现金流的单利终值计算与分析模型 通过 例2 1 学习 输入公式显示公式与显示计算结果之间的切换公式审核复制公式绝对引用与相对引用创建图表 2 1 2一笔现金流的复利终值计算与分析模型 复利终值的计算公式或式中 P为现在的一笔收款或付款 i为复利年利率 n为年限 F为复利终值 FVIFi n 1 i n称为复利终值系数 它表示现在的1元钱在n年后的价值 2 1 2一笔现金流的复利终值计算与分析模型 例2 2 模型的功能 计算复利终值分析本金 利息和复利终值对计息期限的敏感性绘制本金 利息和复利终值与计息期限之间的关系图分析复利终值对利率变化的敏感性分析复利终值对利率和计息期限变化的敏感性绘制不同利率水平下复利终值与计息期限之间的关系图 2 1 2一笔现金流的复利终值计算与分析模型 通过 例2 2 学习 FV函数的功能调用函数的方法单变量模拟运算表双变量模拟运算表编辑图表 FV函数的功能 FV函数的功能是基于固定利率及等额分期付款方式 返回某项投资的未来值 公式为 FV rate nper pmt pv type 式中 rate 各期利率 是一固定值 nper 总投资 或贷款 期 即该项投资 或贷款 的付款期总数 pmt 各期所应付给 或得到 的金额 其数值在整个年金期间 或投资期内 保持不变 如果忽略pmt 则必须包括pv参数 pv 现值 即从该项投资 或贷款 开始计算时已经入账的款项或一系列未来付款当前值的累积和 也称为本金 如果省略pv 则假设其值为零 并且必须包括pmt参数 type 数字0或1 用以指定各期的付款时间是在期初还是期末 type为0表示期末 type为1表示期初 如果省略type 则默认其值为零 2 1 3单利与复利终值选择计算与比较分析模型 例2 3 模型的功能选择按单利方法或复利方法计算终值对比分析未来30年的单利终值和复利终值绘制单利终值与复利终值的对比分析图 2 1 3单利与复利终值选择计算与比较分析模型 通过 例2 3 学习 IF函数的功能IF函数是一个条件函数 它的功能是根据参数条件的真假 返回不同的结果 其语法格式为 IF logical test value if true value if false 组合框控件的设置与使用方法 视图 工具栏 窗体 2 1 4复利终值系数计算模型 例2 4 模型的功能计算复利终值系数计算方法直接输入公式或运用FV函数数组公式输入 2 2现值的计算 2 2 1一笔款项的单利现值计算与分析模型2 2 2一笔款项的复利现值计算与分析模型2 2 3单利与复利现值选择计算和比较分析模型2 2 4复利终值和复利现值系数选择计算模型 2 2 1一笔款项的单利现值计算与分析模型 单利现值的计算公式 式中 P为现值 F为未来值 i为单利年利率 n为期限 例2 5 2 2 2一笔款项的复利现值计算与分析模型 复利现值的计算公式 或 式中 PVIFi n 称为复利现值系数 例2 6 PV函数的语法格式 PV rate nper pmt fv type 2 2 3一笔款项的单利现值与复利现值的选择计算和比较分析模型 例2 7 模型的功能选择按单利方法或复利方法计算这笔款项现值对比分析未来10年的单利现值和复利现值绘制单利现值与复利现值的比较分析图 2 3年金的终值和现值 2 3 1年金终值和现值的计算公式2 3 2几种不同年金终值和现值的计算模型2 3 3年金终值和现值选择计算模型2 3 4年金终值和年金现值系数表选择计算模型 2 3 1年金终值和现值的计算公式 年金的种类普通年金先付年金延期年金永续年金 普通年金的终值和现值 普通年金终值的计算公式为 普通年金现值的计算公式为 式中 A为年金 F为年金终值 P为普通年金的现值 i为年利率 n为期限 FVIFAi n称为年金终值系数 PVIFAi n称为年金现值系数 先付年金的终值和现值 先付年金的终值Vn的计算公式为 或者 先付年金的现值V0的计算公式为 或者 延期年金和永续年金的现值 延期年金的现值V0的计算公式为 或者 永续年金的现值计算公式为 几种不同年金终值和现值的计算模型 例2 9 2 4计息周期与终值和现值 2 4 1每年多次计息情况下终值与现值的计算与分析模型2 4 2名义年利率与有效年利率的计算与分析模型2 4 3每年多次计息情况下按不同方法选择计算终值或现值比较分析模型2 4 4连续复利情况下终值与现值的计算与分析模型 2 4 1每年多次计息情况下终值与现值的计算与分析模型 如果给定的年利率为i 每年计息m次 那么现在的一笔资金P在n年末的终值的计算公式为 如果给定的年利率为i 每年计息m次 那么n年末的一笔资金F的现值的计算公式为 例2 12 2 4 2名义年利率与有效年利率的计算与分析模型 有效年利率r与名义年利率i之间的关系为 式中 m为每年计息次数 相关的函数 EFFECT函数 用于计算有效年利率 公式为 EFFECT nominal rate npery NOMINAL函数 用于计算名义年利率 公式为 NOMINAL effect rate npery 式中 nominal rate为名义年利率 effect rate为有效年利率 npery为每年的复利计息期数 为了使用以上两个函数需要首先加载分析工具库 例2 13 3内部长期投资决策 3 1折旧与现金流量的计算3 2长期投资决策的基本方法3 3投资决策基本方法的应用3 4内部长期投资决策的特殊方法3 5固定资产更新决策 3 1折旧与现金流量的计算 3 1 1固定资产折旧的计算方法3 1 2直线折旧函数与折旧计算模型3 1 3加速折旧函数与折旧计算模型3 1 4可以选择折旧方法的折旧计算模型3 1 5投资项目的现金流量构成与计算模型 3 1 1固定资产折旧的计算方法 直线折旧法平均年限法工作量法加速折旧法余额递减法双倍余额递减法年数总和法 直线折旧法 平均年限法工作量法 某期折旧额 该期实际工作量 每单位工作量折旧额 加速折旧法 余额递减法双倍余额递减法年数总和法 3 1 2直线折旧函数与折旧计算模型 直线折旧法 SLN函数SLN函数的功能是返回一项资产每期的直线折旧额 公式为 SLN cost salvage life 采用直线折旧法计算折旧模型 例3 1 3 1 3加速折旧函数与折旧计算模型 定率余额递减法折旧函数 DB函数DB函数的功能是使用固定余额递减法 计算一笔资产在给定期间内的折旧值 公式为 DB cost salvage life period month 年数总和法折旧计算函数 SYD函数SYD函数的功能是返回某项资产按年限总和折旧法计算的某期折旧额 公式为 SYD cost salvage life per 3 1 3加速折旧函数与折旧计算模型 双倍余额递减法 DDB函数和VDB函数DDB函数的功能是使用双倍余额递减法或其他指定方法 计算一笔资产在给定期间内的折旧值 公式为 DDB cost salvage life period factor 当按直线法计算的折旧值大于按余额递减计算的折旧值时 如果希望转换到直线折旧法 应使用VDB函数 VDB函数的功能是使用双倍余额递减法或其他指定的方法 返回指定的任何期间内的资产折旧值 公式为 VDB cost salvage life start period end period factor no switch 折旧计算模型 3 1 3加速折旧函数与折旧计算模型 例3 2 3 1 4可以选择折旧方法的折旧计算模型 例3 3 3 1 5投资项目的现金流量构成与计算模型 现金流量的构成初始现金流量营业现金流量终结现金流量 现金流量的概念现金流入量现金流出量现金净流量 初始和终结现金流量 初始现金流量固定资产投资资产投资其他投资费用原有固定资产的变价净收入 终结现金流量固定资产的残值净收入或变价净收入原来垫支在各种流动资产上的流动资金回收 营业现金流量 年营业现金净流量 营业收入 付现成本 所得税年营业现金流量 净利润 折旧年营业现金流量 税后收入 税后成本 税负减少 营业收入 1 税率 付现成本 1 税率 折旧 税率其中 折旧 税率被称为 折旧抵税额 或 税收档板 投资项目现金流量的计算 区分相关成本与不相关成本增量现金流量沉没成本机会成本附带效应投资项目现金流量计算模型 例3 4 3 2长期投资决策的基本方法 3 2 1平均报酬率计算与查询模型3 2 2投资回收期计算模型3 2 3净现值计算与评价模型3 2 4获利指数计算与评价模型3 2 5内部收益率计算模型 3 2 1平均报酬率计算与查询模型 平均报酬率的计算公式 例3 5 相关函数AVERAGE函数 计算给定参数的算术平均值 公式为 AVERAGE number1 number2 numberN ABS函数 返回数字的绝对值 公式为 ABS number 相关函数 MAX函数 给定参数表中的最大值 公式为 MAX number1 number2 numberN MIN函数 给定参数表中的最小值 公式为 MIN number1 number2 numberN VLOOKUP函数 在表格或数值数组的首列查找指定的数值 并由此返回表格或数组当前行中指定列处的数值 公式为 VLOOKUP lookup value table array col index num range lookup 相关函数 MATCH函数 返回在指定方式下与指定数值匹配的数组中元素的相应位置 公式为 MATCH lookup value lookup array match type INDEX函数 返回表格或区域中的数值或对数值的引用 该函数有以下两种形式 返回数组中指定单元格或单元格数组的数值 公式为 INDEX array row num column num 返回引用中指定的单元格 公式为 INDEX reference row num column num area num 3 2 2投资回收期计算模型 静态投资回收期 在不考虑资金时间价值的情况下 用投资项目经营期的净现金流量回收初始投资所用的时间 在初始投资以后未来各期净现金流量相等的情况下 静态投资回收期的计算公式为 在初始投资以后未来各期净现金流量不相等的情况下 静态投资回收期的计算公式为 式中 n为累计净现金流量第一次出现正值的年份 3 2 2投资回收期计算模型 动态投资回收期 在考虑资金时间价值的情况下 用投资项目经营期的净现金流量的现值回收初始投资所用的时间 计算公式为 式中 n为累计净现金流量现值第一次出现正值的年份 3 2 2投资回收期计算模型 相关函数NPER函数 基于固定利率及等额分期付款方式 返回某项投资 或贷款 的总期数 公式为 NPER rate pmt pv fv type COUNTIF函数 计算给定区域内满足特定条件的单元格的数目 公式为 COUNTIF range criteria 例3 6 3 2 3净现值计算与评价模型 净现值的计算公式为 式中 CIt为投资项目第t年的现金流入量 COt为投资项目第t年的现金流出量 NCFt为投资项目第t年的净现金流量 i为贴现率 n为投资项目的寿命期 3 2 3净现值计算与评价模型 相关函数NPV函数 基于一系列现金流和固定的各期贴现率 返回一项投资的净现值 这里的投资净现值是指未来各期现金流的现值总和 公式为 NPV rate value1 value2 XNPV函数 返回一组现金流的净现值 这些现金流不一定定期发生 该函数的公式为 XNPV rate values dates 例3 7 3 2 4获利指数计算与评价模型 获利指数的计算公式为 式中 PI为获利指数 m为投资期 Kt为投资期内第t年的净现金流出量 其他各符号的含义如前所述 如果项目的投资额在期初一次性发生 并且初始投资额为K 则 例3 8 3 2 5内部收益率计算模型 内部收益率 IRR 使投资项目的净现值为零时的贴现率 计算公式为 修正内部收益率 MIRR 的计算公式为 式中 CPt为第t年正的净现金流量 CNt为第t年负的净现金流量 n为项目的寿命期 r为对正的净现金流量进行再投资的年收益率 k为对负的净现金流量进行贴现的资金成本率 MIRR为项目的修正内部收益率 3 2 5内部收益率计算模型 相关的函数RATE函数 返回未来款项的各期利率 公式为 RATE nper pmt pv fv type guess IRR函数 返回由数值代表的一组现金流的内部收益率 公式为 IRR values guess MIRR函数 返回某一连续期间内现金流的修正内部收益率 公式为 MIRR values finance rate reinvest rate XIRR函数 返回一组现金流的内部收益率 这些现金流不一定定期发生 公式为 XIRR values dates guess 例3 9 3 3投资决策基本方法的应用 3 3 1独立投资项目的综合评价模型 例3 10 3 3 2互斥投资方案的比较分析模型 例3 11 单变量求解工具的运用 3 4内部长期投资决策的特殊方法 3 4 1寿命期不同的互斥投资方案的选择模型3 4 2资金有限额条件下的投资组合决策模型3 4 3举债融资条件下的投资决策模型 3 4 1寿命期不同的互斥投资方案的选择模型 采用更新链法进行决策的模型LCM函数 返回整数的最小公倍数 该函数的公式为 LCM number1 number2 例3 12 采用净年值法进行决策的模型 例3 13 采用等值年成本法进行决策的模型 例3 14 3 4 2资金有限额条件下的投资组合决策模型 SUMPRODUCT函数 是在给定的几个数组中 将数组间对应的元素相乘 并返回乘积之和 公式为 SUMPRODUCT array1 array2 array3 规划求解工具的运用 例3 15 3 4 3举债融资条件下的投资决策模型 举债融资条件下的投资决策方法调整现值法权益流量法加权平均资本成本法 例3 16 3 5固定资产更新决策 3 5 1是否立即更新固定资产的决策模型 例3 17 3 5 2固定资产最优更新时机的决策模型 例3 18 3 5 2固定资产最优更新时机的决策模型 等值年成本的计算公式为 式中 ACt为设备使用t年的等值年成本 I0为设备投资原值 St为设备在第t年末的残值 Cj为设备在第j年的使用费用 t为设备的使用时间 t 1 2 n n为设备的可使用年限 i为资本成本率即贴现率 4投资项目的风险分析与处置 4 1投资风险的度量4 2投资项目的风险分析方法4 3风险条件下的投资决策 4 1投资风险的度量 4 1 1个别项目的投资风险度量模型4 1 2项目组合的投资风险度量模型 4 1 1个别项目的投资风险度量模型 有关的计算公式为 V E式中 E为投资收益分布的期望值 为投资收益分布的标准差 V为变差系数 标准离差率 Kt为第t种情况下的投资收益 率 Pt为第t种情况出现的概率 n为出现可能情况的种数 4 1 1个别项目的投资风险度量模型 有关的函数SQRT函数 计算一个正数的正平方根 公式为 SQRT number NORMDIST函数 返回指定平均值和标准偏差的正态分布函数 公式为 NORMDIST x mean standard dev cumulative 例4 1 4 2投资项目的风险分析方法 4 2 1投资项目的盈亏平衡分析模型4 2 2投资项目的敏感性分析模型4 2 3投资项目的情境分析模型4 2 4投资项目的概率分析模型4 2 5投资项目的模拟分析模型 4 2 1投资项目的盈亏平衡分析模型 会计盈亏平衡点的计算公式为 变动成本率 变动成本 销售收入 单位变动成本 单价安全边际率 现有产销量 盈亏平衡点产销量 现有产销量 4 2 1投资项目的盈亏平衡分析模型 现值盈亏平衡点产销量应该能够满足下面的公式 式中 Qt为第t年的产销量 pt为第t年的产品单价 vt为第t年的单位变动成本 Fct为第t年的付现固定成本 T为所得税税率 Dt为第t年的折旧额 i为项目的基准收益率 I为项目的初始投资 n为项目的寿命期 t为年份 例4 3 4 2 2投资项目的敏感性分析模型 基于净现值的单因素敏感性分析模型 例4 4 基于净现值的多因素敏感性分析模型 例4 5 4 2 3投资项目的情境分析模型 例4 6 为单元格定义名称建立名称公式运用方案管理器 4 2 4投资项目的概率分析模型 各年现金流完全无关的情况各年现金流完全相关的情况各年现金流部分相关的情况 例4 7 例4 8 4 2 5投资项目的模拟分析模型 相关的函数RANDBETWEEN函数 返回位于两个指定数之间的一个随机数 公式为 RANDBETWEEN bottom top STDEV函数 估算样本的标准偏差 标准偏差反映相对于平均值 mean 的离散程度 公式为 STDEV number1 number2 FREQUENCY函数 以一列垂直数组返回某个区域中数据的频率分布 公式为 FREQUENCY data array bins array 例4 9 4 3风险条件下的投资决策 4 3 1按风险调整贴现率法投资决策模型 例4 10 4 3 2按风险调整现金流量法投资决策模型 例4 11 4 3 3通货膨胀风险条件下的投资决策模型 例4 12 5证券投资分析与决策 5 1债券投资分析5 2股票投资分析5 3证券投资组合优化决策 5 1债券投资分析 5 1 1债券估价模型5 1 2债券投资收益的计算模型5 1 3债券投资期限的计算模型5 1 4债券久期的计算模型5 1 5债券久期的应用模型 5 1 1债券估价模型 债券价值的计算公式零息债券的价值每年付息一次的债券式中 Pb为债券的价值 M为债券的面值 i为债券的票面年利率 n为债券的期限 k为债券投资者要求的最低年投资报酬率 或称市场利率 5 1 1债券估价模型 每年付息m次的债券价值PRICE函数 计算定期付息面值100元的有价证券的价格 公式为 PRICE settlement maturity rate yld redemption frequency basis 5 1 1债券估价模型 债券估价的基本模型 例5 1 债券价值与市场利率之间的关系分析模型 例5 2 债券价值与到期期限之间的关系分析模型 例5 3 债券价值与利息支付频率及票面年利率之间的关系分析模型 例5 4 债券价值的双因素敏感性分析模型 例5 5 带有调节按钮的债券价值计算模型 例5 6 5 1 2债券投资收益的计算模型 例5 9 利用RATE函数计算利用单变量求解工具计算 5 1 3债券投资期限的计算模型 例5 11 利用NPER函数计算利用单变量求解工具计算 5 2股票投资分析 5 2 1股票估价模型5 2 2股票投资收益与风险的度量模型5 2 3股票的 系数计算及特征线绘制模型5 2 4资本资产定价模型与证券市场线5 2 5股票交易数据的获取及投资分析图表的绘制模型 5 2 1股票估价模型 零增长股的估价公式式中 V0为股票的现值 D为每期的股利 k为投资者要求的最低投资报酬率 固定增长股的估价公式式中 D0为现在支付的股利 D1为预计第1年末支付的股利 g为预计的股利增长率 其他符号的含义如前所述 5 2 1股票估价模型 变率增长股的估价公式式中 g1为前n期的股利增长率 g2为正常时期稳定的股利增长率 n为超常增长的时期数 Dn为超常增长期结束时的股利 其他符号的含义如前所述 定期持有的股票的估价公式式中 Dt为第t期每股股利 n为股票的持有期 Pn为第n期末股票的出售价格 其他符号的含义如前所述 5 2 1股票估价模型 例5 15 固定增长股价值的计算建立和使用自定义函数单变量模拟运算表 例5 16 变率增长股价值的计算定期持有的股票价值的计算 5 2 2股票投资收益与风险的度量模型 股票投资收益率的计算单期离散收益率连续复利收益率多期平均收益率LN和STDEV函数股票投资风险的度量标准差和变差系数 系数STDEV函数 例5 17 例5 18 5 2 3股票的 系数计算及特征线绘制模型 相关的函数SLOPE函数 返回根据known y s和known x s中的数据点拟合的线性回归直线的斜率 公式为 SLOPE known y s known x s INTERCEPT函数 利用已知的x值与y值计算直线与y轴的截距 公式为 INTERCEPT known y s known x s 5 2 3股票的 系数计算及特征线绘制模型 相关的函数STDEVP函数 返回以参数形式给出的整个样本总体的标准偏差 公式为 STDEVP number1 number2 COVAR函数 返回协方差 即每对数据点的偏差乘积的平均数 公式为 COVAR array1 array2 函数CORREL 返回单元格区域array1和array2之间的相关系数 公式为 CORREL array1 array2 例5 19 5 2 4资本资产定价模型与证券市场线 资本资产定价模型的数学公式为 式中 Ri为证券i的期望收益率 RF为无风险利率 Rm为证券市场投资组合的期望收益率 i为证券i的风险系数或 系数 5 2 4资本资产定价模型与证券市场线 对于多种证券构成的投资组合而言 资本资产定价模型的数学公式为 式中 RP为证券投资组合的期望收益率 P为证券投资组合的风险系数或 系数 n为证券投资组合中证券的种数 wi为第i种证券的投资额占证券投资组合总投资额的比重 其他参数的含义如前所述 例5 20 5 2 5股票交易数据的获取及投资分析图表的绘制模型 股票交易数据的获取方法在 数据 菜单的 导入外部数据 子菜单中单击 新建Web查询 命令绘制K线图和移动平均线 例5 22 5 3证券投资组合优化决策 5 3 1证券投资组合的收益与风险计算模型 例5 23 5 3 2证券投资组合的优化决策模型 例5 24 6资本成本与资本结构 6 1资本成本的计算6 2杠杆作用分析6 3资本结构理论模型 6 1资本成本的计算 6 1 1债务资本成本的计算模型6 1 2权益资本成本的计算模型6 1 3综合资本成本的计算模型6 1 4边际资本成本规划模型 6 2杠杆作用分析 6 2 1本量利之间的关系及经营杠杆系数的计算与分析模型6 2 2财务杠杆系数的计算与分析模型6 2 3总杆杆系数的计算与分析模型 6 2 1本量利之间的关系及经营杠杆系数的计算与分析模型 本量利之间的关系息税前利润 销售收入 变动成本 固定经营成本 产销量 单价 单位变动成本 固定经营成本保本点销售量 固定经营成本 单价 单位变动成本 保本点销售额 保本点销售量 单价 6 2 1本量利之间的关系及经营杠杆系数的计算与分析模型 经营杠杆作用式中 DOL为经营杠杆系数 EBIT为息税前利润 Q为销量 为变动量 p为单价 v为单位变动成本 F为固定经营成本 S为销售额 VC为变动总成本 例6 6 6 2 2财务杠杆系数的计算与分析模型 财务杠杆作用式中 DFL为财务杠杆系数 I为债务利息 DP为优先股股息 T为所得税税率 EPS为普通股每股利润 N为普通股股份数 其他符号的含义如前所述 6 2 2财务杠杆系数的计算与分析模型 普通股每股利润权益报酬率与投资报酬率式中 R为权益报酬率 净利润 股东权益 K为投资报酬率 息税前利润 资本总额 i为债务利率 B为债务资本 S为股东权益 T为所得税税率 例6 8 6 2 3总杆杆系数的计算与分析模型 总杠杆作用或式中 DTL为总杠杆系数 其它符号的含义如前所述 例6 9 6 3资本结构理论模型 6 3 1MM模型无公司税的情况有公司税的情况6 3 2权衡模型考虑公司税考虑财务危机成本 7筹资预测与决策分析 7 1长期筹资决策分析7 2资金需要量预测7 3短期筹资方式分析7 4筹资风险分析 7 1长期筹资决策分析 7 1 1利用比较资本成本法选择筹资方案模型7 1 2利用比较公司价值法选择筹资方案模型7 1 3利用每股利润分析法选择筹资方案模型7 1 4长期银行借款筹资分析模型7 1 5租赁筹资决策分析模型 7 1 1利用比较资本成本法选择筹资方案模型 计算各筹资方案的加权平均资本成本选择加权平均资本成本最低的筹资方案 例7 1 7 1 2利用比较公司价值法选择筹资方案模型 有关的计算公式 式中 KS为普通股的资本成本率 KF为无风险利率 为普通股的 值 Km为市场投资组合期望报酬率 S为普通股的现值 Kd为债务的利率 D为债务的现值 等于面值 T为所得税税率 V为公司的总价值 KW为公司的综合资本成本率 例7 2 7 1 3利用每股利润分析法选择筹资方案模型 普通股每股利润的计算公式为 式中 EPS为普通股每股利润 n为普通股股数 EBIT为息税前利润 I为债务利息 DP为优先股股息 T为所得税税率 例7 3 7 1 4长期银行借款筹资分析模型 长期银行借款的还本付息方式一次性偿付法等额利息法等额本金法等额摊还法 例7 4 相关的函数 PMT函数 基于固定利率及等额分期付款方式 返回投资或贷款的每期付款额 公式为 PMT rate nper pv fv type IPMT函数 基于固定利率及等额分期付款方式 返回投资或贷款在某一给定期次内的利息偿还额 公式为 IPMT rate per nper pv fv type PPMT函数 基于固定利率及等额分期付款方式 返回投资或贷款在某一给定期次内的本金偿还额 公式为 PPMT rate per nper pv fv type 7 1 5租赁筹资决策分析模型 每期应付租金计算模型 例7 6 租赁设备及租金支付方式选择计算模型 例7 7 租赁与借款筹资决策分析模型 例7 8 盈亏平衡租金的计算与租赁决策分析模型 例7 9 7 2资金需要量预测 7 2 1利用销售百分比法预测资金需要量模型7 2 2利用资金习性法预测资金需要量模型7 2 3利用因果关系法预测资金需要量模型 7 2 1利用销售百分比法预测资金需要量模型 企业从外部追加筹措资金的数额 式中 M为外部追加资金需求量 D为股利支付率 S0为基期销售额 S1为计划销售额 A S 为基期敏感性资产占销售额百分比 L S 为基期敏感性负债占销售额百分比 R为销售净利率 M1为计划期的其他资金需求 即不随销售额成正比例变动的其他资金需要量 例7 10 7 2 2利用资金习性法预测资金需要量模型 运用高低点法预测资金需要量的具体步骤是 1 从各期产销量与相应的资金占用量的历史数据中找出产销量最高和最低的两点 2 根据高低两点的数据计算单位变动资金 单位变动资金 高低点资金占用量之差 高低点产销量之差 3 根据高点或低点的数据和单位变动资金计算不变资金总额 不变资金总额 高点资金占用量 单位变动资金 高点产销量或 不变资金总额 低点资金占用量 单位变动资金 低点产销量 4 根据下述模型预测某一时期的资金需要量 预测期资金需要量 不变资金总额 单位变动资金 预测期产销量 或销售额 例7 11 7 2 3利用因果关系法预测资金需要量模型 因果关系预测法中常用的预测方法是回归分析法 TREND函数 返回一条线性回归拟合线的值 即找到适合已知数组known y s和known x s的直线 用最小二乘法 并返回指定数组new x s在直线上对应的y值 公式为 TREND known y s known x s new x s const 例7 12 7 3短期筹资方式分析 7 3 1商业信用筹资分析模型7 3 2短期信用借款的实际年利率计算模型7 3 3短期抵押借款筹资方式分析与决策模型7 3 4其他短期融资方式分析与决策模型 7 4筹资风险分析 7 4 1资本结构风险分析模型7 4 2财务结构风险分析模型 8流动资产管理 8 1现金管理8 2应收账款管理8 3存货管理 8 1现金管理 8 1 1现金预算表的编制模型8 1 2最佳现金余额的确定模型8 1 3加速现金收款决策模型8 1 4资金转移方式决策模型 8 2应收账款管理 8 2 1应收账款信用标准决策模型8 2 2应收账款信用条件决策模型8 2 3应收账款收账政策决策模型8 2 4应收账款信用政策方案的净现值计算与决策模型8 2 5应收账款日常管理模型 8 2 5应收账款日常管理模型 有关的函数TODAY函数 返回当前日期的序列号 序列号是MicrosoftExcel日期和时间计算使用的日期 时间代码 如果在输入函数前 单元格的格式为 常规 则结果将设为日期格式 其公式为 TODAY SUMIF函数的功能是根据指定条件对若干单元格求和 其公式为 SUMIF range criteria sum range 8 2 5应收账款日常管理模型 建立应收账款台账 例8 12 应收账款的排序 筛选及分类汇总分析 例8 13 编制应收账款账龄分析表 例8 14 8 3存货管理 8 3 1基本的经济订货批量模型8 3 2扩展的经济订货批量模型8 3 3保险储备量和再订货点的确定模型8 3 4半成品存货的最优生产批量决策模型8 3 5存货的ABC分类模型 8 3 1基本的经济订货批量模型 存货的相关总成本 总订储费用 经济订货批量最佳的订货次数最低的总订储费用式中 Q为一次订货批量 D为一定时期存货的需求量 A为一次订货费 P为存货单价 K为存货的存储费率 P K即为单位存储费用 例8 15 8 3 5存货的ABC分类模型 常见的分类标准如下 A类存货 品种数约占10 15 存货金额约占80 B类存货 品种数约占20 30 存货金额约占15 C类存货 品种数约占55 80 存货金额约占5 COUNTA函数 返回参数列表中非空值的单元格个数 利用函数COUNTA可以计算单元格区域或数组中包含数据的单元格个数 公式为 COUNTA value1 value2 例8 20 9销售收入管理 9 1销售收入预测9 2销售增长率预测9 3销售数据的透视分析 9 1销售收入预测 9 1 1利用相关函数预测销售收入模型9 1 2利用数据分析工具预测销售收入模型9 1 3利用绘图工具预测销售收入模型 9 1 1利用相关函数预测销售收入模型 相关的函数LINEST函数的功能是使用最小二乘法对已知数据进行最佳直线拟合 并返回描述此直线的数组 直线的公式为 y m x b或y m1x1 m2x2 b 如果有多个区域的x值 式中 因变量y是自变量x的函数值 m值是与每个x值相对应的系数 b为常量 注意y x和m可以是向量 此函数的语法格式为 LINEST known y s known x s const stats 9 1 1利用相关函数预测销售收入模型 LINEST函数附加回归统计值返回的顺序 9 1 1利用相关函数预测销售收入模型 FORECAST函数 根据已有的数值计算或预测未来值 此预测值为基于给定的x值推导出的y值 已知的数值为已有的x值和y值 再利用线性回归对新值进行预测 可以使用该函数对未来销售额 库存需求或消费趋势进行预测 公式为 FORECAST x known y s known x s 9 1 1利用相关函数预测销售收入模型 LOGEST函数 在回归分析中 计算最符合数据的指数回归拟合曲线 并返回描述该曲线的数值数组 此曲线的公式为 或 如果有多个x值 其中因变量y是自变量x的函数值 m值是各指数x的底 而b值是常量值 该函数的语法格式为 LOGEST known y s known x s const stats 9 1 1利用相关函数预测销售收入模型 GROWTH函数 根据现有的数据预测指数增长值 根据现有的x值和y值 GROWTH函数返回一组新的x值对应的y值 可以使用GROWTH工作表函数来拟合满足现有x值和y值的指数曲线 该函数的语法格式为 GROWTH known y s known x s new x s const 例9 1 例9 2 9 1 2利用数据分析工具预测销售收入模型 基于移动平均法的销售预测模型 例9 3 基于回归分析法的销售预测模型 例9 5 9 1 2利用数据分析工具预测销售收入模型 基于指数平滑法的销售预测模型计算公式为 或式中 Ft 1为上期的预测销售数 Ft为新一期 即计划期 的预测销售数 Dt 1为上期的实际销售数 为平滑系数 0 1 为阻尼系数 0 1 1 例9 4 9 1 3利用绘图工具预测销售收入模型 例9 7 9 2销售增长率预测 9 2 1销售增长与外部融资之间的关系分析模型9 2 2内含增长率及其敏感性分析模型9 2 3可持续增长率及其敏感性分析模型 9 3销售数据的透视分析 9 3 1建立销售数据透视表 例9 11 9 3 2绘制销售数据透视图 例9 11 续 10成本费用管理 10 1成本预测10 2成本计划10 3成本计算10 4成本分析 10 1成本预测 10 1 1成本预测的常用方法加权平均法高低点法回归直线法10 1 2成本预测模型的建立 例10 1 10 2成本计划 10 2 1成本计划的编制方法主要商品产品单位成本计划全部商品产品成本计划10 2 2成本计划编制模型的建立 例10 2 10 3成本计算 10 3 1成本计算的主要方法完全成本计算法变动成本计算法10 3 2成本计算模型的建立 例10 3 10 4成本分析 10 4 1成本计划完成情况的分析模型全部商品产品成本计划完成情况的分析可比产品成本计划完成情况的分析主要产品单位成本计划完成情况的分析10 4 2标准成本执行情况的分析模型直接材料费成本差异分析直接人工费成本差异分析变动制造费用成本差异分析固定制造费用成本差异分析 例10 7 例10 8 11利润管理 11 1利润预测11 2确保实现目标利润的措施分析11 3非确定型利润决策11 4本量利分析 11 1利润预测 11 1 1利润预测的一般方法比率预测法经营杠杆系数预测法本量利分析预测法11 1