Excel在财务管理中的应用(第五版)全套教学课件

1、Excel在财务管理中的应用（第五版）目录1 Excel基础知识2 资金时间价值的计算3 内部长期投资决策4 投资项目的风险分析与处置5 证券投资分析与决策6 资本成本与资本结构7 筹资预测与决策分析目录8 流动资产管理9 销售收入管理10 成本费用管理11 利润管理12 财务报表分析与预测13 企业价值评估14 期权定价模型及其应用1 Excel基础知识1.1 中文版Excel 2016概述1.2 管理工作簿1.3 输入和编辑数据1.4 管理工作表1.5 打印管理1.1 中文版Excel 2016 概述1.1.1 Excel 2016的启动与退出1.1.2 Excel 2016的窗口结构1.

2、1.1 Excel 2016的启动与退出Excel 2016的启动与退出单击【开始】菜单中的【Excel】命令双击任意一个扩展名为 .xlsx的Excel文件 Excel 2016的关闭与退出【文件】/【关闭】单击Excel窗口右上角的关闭按钮按【 Ctrl+W 】组合键按【 Alt+F4】组合键1.1.2 Excel 2016的窗口结构快速访问工具栏标题栏功能区公式编辑栏工作表格区滚动条工作表标签状态栏1.2 管理工作簿1.2.1 新建工作簿1.2.2 打开已有工作簿1.2.3 保存工作簿1.2.4 并排比较工作簿1.2.1 新建工作簿新建空白工作簿【文件】/【新建】/【空白工作簿】【 Ct

3、rl+N 】组合键使用模板创建工作簿【文件】/【新建】/【建议的搜索】/【创建】1.2.2 打开已有工作簿【文件】/【打开】【 Ctrl+O 】 组合键双击Excel文件1.2.3 保存工作簿快速访问工具栏/【保存】按钮【文件】/【保存】【 Ctrl+S 】 组合键1.2.4 并排比较工作簿【视图】/【窗口】/【并排查看】再次执行该命令可关闭并排查看1.3 输入和编辑数据1.3.1 选取单元格1.3.2 输入数据的一般方法1.3.3 特殊数据的输入方法1.3.4 编辑数据1.3.1 选取单元格选取单个单元格直接用鼠标选取利用【定位】命令选取 选取单元格区域选取整行或整列 选取全部单元格 选取连

4、续的单元格区域选取不连续的单元格区域 1.3.2 输入数据的一般方法数据的类型文本型数据 数值型数据日期型数据时间型数据输入数据的基本方法输入不同类型数据应注意的问题1.3.3 特殊数据的输入方法输入相同数据自动填充方式按【Ctrl+Enter】组合键批量输入输入序列数据填充序列数据设置序列填充方式【开始】/【编辑】/【填充】在【序列】对话框中设置自定义序列填充【文件】/【选项】/【高级】/【编辑自定义列表】 1.3.4 编辑数据修改数据在公式编辑栏中修改在单元格中修改移动数据利用剪切和粘贴命令利用鼠标拖动复制数据利用复制和粘贴命令利用多重剪贴板工具1.3.4 编辑数据清除单元格内容直接按【D

5、el】键 【开始】 /【编辑】/【清除】 查找与替换【开始】 /【编辑】/【查找和选择】/【查找】按【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】键 切换工作表利用标签滚动按钮使用快捷键操作 插入工作表单击【插入工作表】按钮在快捷菜单中选择【插入】/【工作表】删除工作表【开始】/【

6、单元格】/【删除】/【删除工作表】在快捷菜单中选择【删除】 1.4.1 使用多张工作表移动或复制工作表在快捷菜单中选择【移动或复制工作表】直接用鼠标拖动重命名工作表在快捷菜单中选择【重命名】双击工作表标签后改名为工作表添加链接在快捷菜单中选择【链接】在【插入超链接】对话框中设置1.4.2 调整行、列和单元格插入行、列或单元格【开始】/ 【单元格】 /【插入】在【插入】对话框中选择删除行、列或单元格【开始】/【单元格】/【删除】在【删除】对话框中选择调整行高和列宽【开始】/【单元格】/【格式】【自动调整行高】或【自动调整列宽】指定行高或列宽的精确数值 直接利用鼠标操作1.4.3 设置工作表格式设

7、置单元格格式【开始】/【单元格】/【格式】在【设置单元格格式】对话框中设置自动套用格式【开始】/【样式】/【套用表格格式】使用条件格式 【开始】/【样式】/【条件格式】数据条、色阶、图标集等取消与恢复网格线【视图】/【显示】/【网格线】 1.4.4 保护单元格与工作表保护单元格打开【设置单元格格式】对话框在【保护】选项卡上设置 保护工作表【审阅】/【更改】/【保护工作表】 1.4.5 隐藏与显示隐藏与显示行或列【开始】/【单元格】/【格式】/【可见性】在【隐藏和取消隐藏】子菜单中选择相应的命令或者直接用鼠标拖动隐藏与显示工作表【开始】/【单元格】/【格式】/【可见性】在【隐藏和取消隐藏】子菜单

8、中选择相应的命令1.4.6 划分窗口重排窗口 【视图】/【窗口】/【新建窗口】 【视图】/【窗口】/【全部重排】 拆分窗口【视图】/【窗口】/【拆分】 冻结窗格【视图】/【窗口】/【冻结窗格】 1.5 打印管理1.5.1 页面设置 1.5.2 分页设置1.5.3 打印预览1.5.1 页面设置 设置页面【页面布局】/【页面设置】/【页面设置】 包括页面、页边距、页眉/页脚、工作表设置 打印区域设置【页面布局】/【页面设置】/【打印区域】/【设置打印区域】 1.5.2 分页设置插入分页符【页面布局】/【页面设置】 /【分隔符】/【插入分页符】分页调整【视图】/【工作簿视图】/【分页预览】 完成后执

9、行【普通】命令删除分页符选取整个工作表【页面布局】/【页面设置】/【分隔符】 /【删除分页符】/【重设所有分页符】1.5.3 打印预览【文件】/【打印】可以看到打印预览视图 在【页面设置】对话框中可在各选项卡中选择【打印预览】 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 为现在的一笔资金；

10、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】模型

11、的功能：计算复利终值分析本金、利息和复利终值对计息期限的敏感性绘制本金、利息和复利终值与计息期限之间的关系图分析复利终值对利率变化的敏感性分析复利终值对利率和计息期限变化的敏感性绘制不同利率水平下复利终值与计息期限之间的关系图2.1.2 复利终值计算与分析模型通过【例2-2】学习：FV函数的功能调用函数的方法建立模拟运算表【数据】/【预测】 /【模拟分析】/【模拟运算表】编辑图表FV函数的功能FV函数的功能是基于固定利率及等额分期付款方式，返回某项投资的未来值。公式为： = FV(rate,nper,pmt,pv,type)式中：rate ：各期利率，是一固定值。nper ：总投资（或贷款）期

12、，即该项投资（或贷款）的付款期总数。pmt ：各期所应付给（或得到）的金额，其数值在整个年金期间（或投资期内）保持不变。如果忽略 pmt，则必须包括 pv 参数。pv ：现值，即从该项投资（或贷款）开始计算时已经入账的款项或一系列未来付款当前值的累积和，也称为本金。如果省略 pv，则假设其值为零，并且必须包括 pmt 参数。type ：数字0或1，用以指定各期的付款时间是在期初还是期末，type为0表示期末，type为1表示期初。如果省略 type，则默认其值为零。2.1.3 单利和复利终值选择计算与比较分析模型【例2-3】 模型的功能选择按单利方法或复利方法计算终值对比分析未来30年的单利终

13、值和复利终值绘制单利终值与复利终值的对比分析图 2.1.3 单利和复利终值选择计算与比较分析模型通过【例2-3】学习：IF函数的功能IF函数是一个条件函数，它的功能是根据参数条件的真假，返回不同的结果。其语法格式为： = IF(logical_test,value_if_true,value_if_false)建立数组公式【 Shift+Ctrl+Enter 】组合键2.1.3 单利和复利终值选择计算与比较分析模型组合框控件的使用方法显示【开发工具】选项卡 【文件】/【选项】/ 【Excel选项】对话框【自定义功能区】/【开发工具】组合框控件设置【开发工具】/【控件】/【插入】/【表单控件】

14、/【组合框】设置控件格式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.2.1 单利现值计算与分析模型【例2-5】 建立公式模拟运算表绘制与编辑图表2.2.2 复利现值计算与分析模型复利现值的计算公式： 或：式中： PVIFi,n=

15、称为复利现值系数2.2.2 复利现值计算与分析模型【例2-6】 PV函数的语法格式 = PV(rate,nper,pmt,fv,type) = PV(贴现率,贴现期,年金,终值,年金类型) 2.2.3 单利现值与复利现值的选择计算和比较分析模型【例2-7】模型的功能选择按单利方法或复利方法计算现值 对比分析未来10年的单利现值和复利现值绘制单利现值与复利现值的比较分析图 2.2.4 复利终值系数和复利现值系数选择计算模型【例2-8】使用IF函数使用组合框控件建立数组公式2.3 年金的终值和现值2.3.1 年金终值和现值的计算公式2.3.2 几种不同年金终值和现值的计算模型2.3.3 年金终值和

16、现值选择计算模型2.3.4 年金终值系数表和年金现值系数表选择计算模型2.3.1 年金终值和现值的计算公式年金的种类普通年金先付年金延期年金永续年金普通年金的终值和现值 普通年金终值的计算公式为： 式中： A为年金；F为年金终值；i为年利率； n为期限；FVIFAi,n称为年金终值系数；普通年金的终值和现值 普通年金现值的计算公式为： 式中： A为年金；F为年金终值；i为年利率； n为期限； PVIFAi,n称为年金现值系数 。先付年金的终值和现值先付年金的终值Vn的计算公式为： 或者：先付年金的终值和现值先付年金的现值V0的计算公式为： 或者：延期年金和永续年金的现值延期年金的现值V0的计算

17、公式为：或者：永续年金的现值计算公式为：2.3.2 几种不同年金终值和现值的计算模型【例2-9】FV函数的应用PV函数的应用2.3.3 年金终值和现值选择计算模型【例2-10】使用IF函数使用组合框控件应用FV函数和PV函数2.3.4 年金终值系数表和年金现值系数表选择计算模型【例2-11】使用IF函数使用组合框控件应用FV函数和PV函数应用数组公式2.4 计息周期与终值和现值2.4.1 每年多次计息情况下终值与现值的计算与分析模型2.4.2 名义年利率与有效年利率的计算与分析模型2.4.3 每年多次计息情况下选择计算终值或现值的方法2.4.4 连续复利情况下终值与现值的计算与分析模型 2.4

18、.1 每年多次计息情况下终值与现值的计算与分析模型如果给定的年利率为i，每年计息m次，那么现在的一笔资金P在n年末的终值的计算公式为： 如果给定的年利率为i，每年计息m次，那么n年末的一笔资金F的现值的计算公式为：2.4.1 每年多次计息情况下终值与现值的计算与分析模型【例2-12】使用IF函数使用组合框控件应用FV函数和PV函数2.4.2 名义年利率与有效年利率的计算与分析模型有效年利率r与名义年利率i之间的关系为： 式中：m为每年计息次数2.4.2 名义年利率与有效年利率的计算与分析模型【例2-13】EFFECT函数计算有效年利率 = EFFECT(nominal_rate,npery)N

19、OMINAL函数计算名义年利率 = NOMINAL(effect_rate,npery)式中：nominal_rate为名义年利率； effect_rate为有效年利率； npery为每年的复利计息期数。2.4.3 每年多次计息情况下选择计算终值或现值的方法【例2-14】使用IF函数使用组合框控件使用数值调节钮应用EFFECT函数应用FV函数和PV函数2.4.4 连续复利情况下终值与现值的计算与分析模型连续复利情况下终值的计算公式 连续贴现情况下现值的计算公式其中，e为自然对数的底数，e 2.71828 2.4.4 连续复利情况下终值与现值的计算与分析模型【例2-15】EXP函数返回 e 的

20、n 次幂。语法格式为：=EXP(number)式中：number为底数 e 的指数。 【例2-16】使用EXP和SUM函数使用数值调节钮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 固定资产折旧的计算方法直线折旧法平均年限法工作量法加速折旧法

21、余额递减法双倍余额递减法年数总和法直线折旧法平均年限法工作量法某期折旧额 = 该期实际工作量每单位工作量折旧额 加速折旧法余额递减法加速折旧法双倍余额递减法加速折旧法 年数总和法3.1.2 直线折旧函数与折旧计算模型直线折旧法SLN函数SLN函数的功能是返回一项资产每期的直线折旧额。公式为 = SLN(cost,salvage,life)采用直线折旧法计算折旧模型 【例3-1】 3.1.3 加速折旧函数与折旧计算模型定率余额递减法折旧函数DB函数DB函数的功能是使用固定余额递减法，计算一笔资产在给定期间内的折旧值。公式为：=DB(cost,salvage,life,period,month)年

22、数总和法折旧计算函数SYD函数SYD函数的功能是返回某项资产按年限总和折旧法计算的某期折旧额。公式为：= SYD(cost,salvage,life,per) 3.1.3 加速折旧函数与折旧计算模型双倍余额递减法DDB函数和VDB函数DDB函数的功能是使用双倍余额递减法或其他指定方法，计算一笔资产在给定期间内的折旧值。公式为= DDB(cost, salvage, life, period, factor)当按直线法计算的折旧值大于按余额递减计算的折旧值时，如果希望转换到直线折旧法，应使用 VDB 函数。 VDB函数的功能是使用双倍余额递减法或其他指定的方法，返回指定的任何期间内的资产折旧值。

23、公式为=VDB(cost,salvage,life,start_period,end_period,factor,no_switch)【例3-2】3.1.4 可以选择折旧方法的折旧计算模型【例3-3】使用列表框控件IF函数的应用折旧函数的应用 3.1.5 投资项目的现金流量构成与计算模型现金流量的构成初始现金流量营业现金流量终结现金流量 初始和终结现金流量初始现金流量固定资产投资流动资产投资其他投资费用原有固定资产的变价净收入终结现金流量固定资产的残值净收入或变价净收入原来垫支在各种流动资产上的流动资金回收营业现金流量年营业净现金流量= 营业收入付现经营成本所得税= 净利润+折旧=(营业收入付

24、现经营成本折旧)(1 所得税税率) +折旧=营业收入(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(nu

25、mber1, number 2, number N)ABS函数返回数字的绝对值。公式为：=ABS(number)相关函数MAX函数给定参数表中的最大值。公式为：= MAX(number1, number 2, number N)MIN函数给定参数表中的最小值。公式为：= MIN(number1, number 2, number N)VLOOKUP函数在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。公式为： =VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) 相关函数MATCH函数返回在指

26、定方式下与指定数值匹配的数组中元素的相应位置。公式为：=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 投资回收期计算模型静态投资回收期在不考虑资金时间价值的情况下，用投资项目经营期的净现金流量回收初始投资所用的时间。 在初始投资以后未

27、来各期净现金流量相等的情况下：3.2.2 投资回收期计算模型在初始投资以后未来各期净现金流量不相等的情况下： 式中：n为累计净现金流量第一次出现正值的年份3.2.2 投资回收期计算模型动态投资回收期在考虑资金时间价值的情况下，用投资项目经营期的净现金流量的现值回收初始投资所用的时间。计算公式为：式中：n为累计净现金流量现值第一次出现正值的年份3.2.2 投资回收期计算模型相关函数NPER函数基于固定利率及等额分期付款方式，返回某项投资（或贷款）的总期数。公式为:= NPER(rate,pmt,pv,fv,type)COUNTIF函数计算给定区域内满足特定条件的单元格的数目。公式为：= COUN

28、TIF(range,criteria)【例3-6】3.2.3 净现值计算与评价模型净现值的计算公式为：式中：CIt为投资项目第t年的现金流入量，COt为投资项目第t年的现金流出量，NCFt为投资项目第t年的净现金流量，i为贴现率，n为投资项目的寿命期。如果项目的投资额在期初一次性发生，并且初始投资额为K，则：3.2.3 净现值计算与评价模型相关函数NPV函数基于一系列现金流和固定的各期贴现率，返回一项投资的净现值。这里的投资净现值是指未来各期现金流的现值总和。公式为：= NPV(rate,value1,value2,.)XNPV函数返回一组现金流的净现值，这些现金流不一定定期发生。该函数的公式

29、为：=XNPV(rate,values,dates)【例3-7】3.2.4 获利指数计算与评价模型获利指数的计算公式为：式中：PI为获利指数；m为投资期；Kt为投资期内第t年的净现金流出量；其他各符号的含义如前所述。如果项目的投资额在期初一次性发生，并且初始投资额为K，则： 【例3-8】3.2.5 内部收益率计算模型内部收益率（ IRR ）使投资项目的净现值为零时的贴现率，计算公式为：3.2.5 内部收益率计算模型修正内部收益率（ MIRR ）的计算公式为： 式中：CPt为第t年正的净现金流量；CNt为第t年负的净现金流量；n为项目的寿命期；r为对正的净现金流量进行再投资的年收益率；k为对负的

30、净现金流量进行贴现的资金成本率；MIRR为项目的修正内部收益率。3.2.5 内部收益率计算模型相关的函数RATE函数返回未来款项的各期利率。公式为:= RATE(nper,pmt,pv,fv,type,guess)IRR函数返回由数值代表的一组现金流的内部收益率。公式为：=IRR(values,guess)3.2.5 内部收益率计算模型相关的函数MIRR函数返回某一连续期间内现金流的修正内部收益率。公式为：= MIRR(values,finance_rate,reinvest_rate)XIRR函数返回一组现金流的内部收益率，这些现金流不一定定期发生。公式为：=XIRR(values,date

31、s,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.1 寿命期不同的互斥投资方案的选择模型采用更新链法进行决策的模型LCM函数返回整数的最小公倍数。该函数的公式为：=LCM(number1,number2, .)【例3-12】采用净年值法进行决策的模型【例3

32、-13】采用等值年成本法进行决策的模型【例3-14】3.4.2 资金有限额条件下的投资组合决策模型【例3-15】SUMPRODUCT函数是在给定的几个数组中，将数组间对应的元素相乘，并返回乘积之和。公式为： = SUMPRODUCT(array1,array2,array3, .)规划求解工具的运用【文件】/【选项】/【Excel选项】/【加载项】 /【转到】/【加载项】/【规划求解加载项】【数据】/【分析】/【规划求解】3.5 固定资产更新决策3.5.1 是否立即更新固定资产的决策模型【例3-16】3.5.2 固定资产最优更新时机的决策模型【例3-17】3.5.2 固定资产最优更新时机的决策

33、模型等值年成本的计算公式为：式中：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 个别项目的投资风险度量模型有关的计算公式为：式中：E为投资收益分布的期望值； 为投资收益分布的标准差；V为变差系数（标准离差率）；Kt为第t种情况下的投

34、资收益（率）；Pt为第t种情况出现的概率；n为出现可能情况的种数。4.1.1 个别项目的投资风险度量模型有关的函数SQRT函数计算一个正数的正平方根。公式为：=SQRT(number)NORMDIST函数返回指定平均值和标准偏差的正态分布函数，公式为：=NORMDIST(x,mean,standard_dev,cumulative)【例4-1】4.1.2 项目组合的投资风险度量模型项目组合投资收益分布的期望值、标准差和变差系数的计算公式为：式中：EP为项目组合的净现值； 为项目i的净现值期望值；n为项目组合中项目的个数；P为项目组合的标准差；j和k分别为项目j和项目k的标准差；VP为项目组合的

35、变差系数；rj,k为项目j与k之间的相关系数 4.1.2 项目组合的投资风险度量模型【例4-2】MMULT函数：返回两数组的矩阵乘积。其公式为：=MMULT(array1,array2)式中： array1, array2为要进行矩阵乘法运算的两个数组。结果矩阵的行数与array1的行数相同，矩阵的列数与array2的列数相同。 4.2 投资项目的风险分析方法4.2.1 投资项目的盈亏平衡分析模型4.2.2 投资项目的敏感性分析模型4.2.3 投资项目的情境分析模型4.2.4 投资项目的概率分析模型4.2.5 投资项目的模拟分析模型4.2.1 投资项目的盈亏平衡分析模型会计盈亏平衡点的计算公式

36、为：变动成本率=变动成本/销售收入=单位变动成本/单价安全边际率=（现有产销量-盈亏平衡点产销量）/现有产销量4.2.1 投资项目的盈亏平衡分析模型现值盈亏平衡点产销量应该能够满足下面的公式：式中：Qt为第t年的产销量；pt为第t年的产品单价；vt为第t年的单位变动成本；Fct 为第t年的付现固定成本；T为所得税税率；Dt为第t年的折旧额；i为项目的基准收益率；I为项目的初始投资；n为项目的寿命期；t为年份。 【例4-3】4.2.2 投资项目的敏感性分析模型单因素敏感性分析的基本原理一般步骤选定项目评价指标选定拟分析的因素确定各因素变动范围及间距计算各因素值变动后评价指标的数值判断各因素的敏感

37、性强弱单因素变动的临界值的确定4.2.2 投资项目的敏感性分析模型基于净现值的单因素敏感性分析模型【例4-4】使用相关函数运用模拟运算表工具运用单变量求解工具绘制与编辑图表基于净现值的双因素敏感性分析模型【例4-5】运用模拟运算表工具4.2.2 投资项目的敏感性分析模型基于净现值的多因素敏感性分析模型【例4-6】使用滚动条控件【开发工具】/【控件】/【插入】/【表单控件】/【滚动条】建立和使用自定义函数【开发工具】/【代码】/【Visual Basic】/【插入】/【模块】/【插入】/【过程】4.2.3 投资项目的情境分析模型【例4-7】为单元格定义名称【公式】/【定义的名称】/【根据所选内容

38、创建】运用方案管理器【数据】/【预测】/【方案管理器】4.2.4 投资项目的概率分析模型概率分析的基本原理各年现金流完全无关的情况各年现金流完全相关的情况各年现金流部分相关的情况投资项目决策树分析模型的建立【例4-8】【例4-9】4.2.5 投资项目的模拟分析模型相关的函数RANDBETWEEN函数返回位于两个指定数之间的一个随机数。公式为：= RANDBETWEEN(bottom,top)STDEV函数估算样本的标准偏差。标准偏差反映相对于平均值 (mean) 的离散程度。公式为：=STDEV(number1,number2,.)FREQUENCY函数以一列垂直数组返回某个区域中数据的频率分

39、布。公式为：=FREQUENCY(data_array,bins_array)【例4-10】 4.3 风险条件下的投资决策4.3.1 按风险调整贴现率法投资决策模型【例4-11】4.3.2 按风险调整现金流量法投资决策模型【例4-12】4.3.3 通货膨胀风险条件下的投资决策模型4.3.3 通货膨胀风险条件下的投资决策模型通货膨胀条件下的利率和现金流量 名义利率与实际利率之间的关系为：或 4.3.3 通货膨胀风险条件下的投资决策模型通货膨胀条件下的利率和现金流量 在各年通货膨胀率相等的情况下：在各年通货膨胀率不相等的情况下：4.3.3 通货膨胀风险条件下的投资决策模型通货膨胀条件下投资决策的方

40、法剔除法 吸纳法 【例4-13】 【例4-14】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为债券的面值；n为债券的期限；k为债券投资者要求的最低年投资报酬率，或称市场利率。5.1.1 债券估价模型债券价值的计算公式每年付息一次的债券式中：Pb为债券的价值；M为债券的面值；i为债券的票面年利率；n为

41、债券的期限；k为债券投资者要求的最低年投资报酬率，或称市场利率。5.1.1 债券估价模型每年付息m次的债券价值5.1.1 债券估价模型相关函数PRICE计算定期付息面值100元的有价证券的价格PRICEDISC计算折价发行的面值100元的有价证券的价格PRICEMAT计算到期付息的面值100元的有价证券的价格TBILLPRICE计算面值100元的国库券的价格5.1.1 债券估价模型债券估价的基本模型 【例5-1】利用PV函数计算利用PRICE函数计算债券价值与市场利率之间的关系分析模型 【例5-2】定义名称粘贴列表绘制图表5.1.1 债券估价模型债券价值与到期期限之间的关系分析模型 【例5-3

42、】定义名称粘贴列表绘制图表债券价值与利息支付频率及票面年利率之间的关系 分析模型 【例5-4】PV函数单变量模拟运算表5.1.1 债券估价模型债券价值的双因素敏感性分析模型带有调节按钮的债券价值计算模型流通中的债券价值计算模型【例5-5】 【例5-6】 【例5-7】使用相关函数数值调节钮模拟运算表绘制图表5.1.2 债券投资收益的计算模型计算债券投资收益额的相关函数ACCRINT计算定期付息有价证券的应计利息 ACCRINTM 计算到期一次性付息有价证券的应计利息RECEIVED 计算一次性付息有价证券到期收回的金额 COUPNUM 返回成交日（买入日）和到期日（或卖出日）之间的利息应付次数

43、5.1.2 债券投资收益的计算模型计算债券投资收益率的相关函数 YIELD计算定期付息有价证券的收益率 YIELDDISC 计算折价发行的有价证券的年收益率 TBILLYIELD 计算国库券的收益率 TBILLEQ 计算国库券的等效收益率 INTRATE 计算一次性付息证券的利率 DISC 计算有价证券的贴现率 5.1.2 债券投资收益的计算模型利用相关函数计算利用单变量求解工具计算【例5-8】【例5-9】【例5-10】5.1.3 债券投资期限的计算模型利用相关函数计算利用单变量求解工具计算【例5-11】5.1.4 债券久期的计算模型 债券久期的计算公式式中：D为债券的有效期限或麦考利久期；C

44、t为债券各期的现金流（利息或本金）；y为债券的到期收益率；t为任何有现金流的期数；P0为债券的现值。5.1.4 债券久期的计算模型 修正的久期计算公式 修正的久期=麦考利久期/(1+到期收益率) 5.1.4 债券久期的计算模型相关函数 DURATION函数的功能是返回面值为100元的定期付息有价证券的麦考利久期MDURATION函数的功能是返回面值为100元的有价证券的麦考利修正期限 【例5-12】 5.1.5 债券久期的应用模型 利用债券久期预测债券价格的变化相关公式【例5-13】 5.1.5 债券久期的应用模型构造债券投资组合管理的免疫策略 相关公式 【例5-14】使用相关函数运用规划求解

45、工具5.2 股票投资分析5.2.1 股票估价模型5.2.2 股票投资收益与风险的度量模型5.2.3 股票的系数计算及特征线绘制模型5.2.4 资本资产定价模型与证券市场线5.2.5 股票交易数据的获取及投资分析图表的绘制模型5.2.1 股票估价模型零增长股的估价公式式中：V0为股票的现值；D为每期的股利； k为投资者要求的最低投资报酬率。5.2.1 股票估价模型固定增长股的估价公式 式中：D0为现在支付的股利；D1为预计第1年末支付的股利；g为预计的股利增长率；其他符号的含义如前所述。5.2.1 股票估价模型变率增长股的估价公式 式中：g1为前n期的股利增长率；g2为正常时期稳定的股利增长率；

46、n为超常增长的时期数；Dn为超常增长期结束时的股利；其他符号的含义如前所述。5.2.1 股票估价模型定期持有的股票的估价公式式中： Dt为第t期每股股利；n为股票的持有期；Pn为第n期末股票的出售价格；其他符号的含义如前所述。5.2.1 股票估价模型【例5-15】固定增长股价值的计算建立和使用自定义函数单变量模拟运算表 【例5-16】变率增长股价值的计算定期持有的股票价值的计算5.2.2 股票投资收益与风险的度量模型根据历史数据计算股票投资收益率股票投资收益率的计算根据预期数据计算期望收益率及标准差股票投资风险的度量股票投资风险的度量指标包括：标准差、变差系数、 系数根据历史数据计算收益率分布

47、的标准差和变差系数：5.2.2 股票投资收益与风险的度量模型股票投资收益率及标准差的计算 【例5-17】 LN函数AVERAGE函数STDEV函数 【例5-18】SUMPRODUCT函数SQRT函数5.2.3 股票的系数计算及特征线绘制模型特征线方程：式中：特征线回归方程有关参数的计算公式式中各符号的含义 m为市场投资组合收益率的标准差； i为i股票收益率的标准差；Cov(i,m)为 i股票和市场投资组合收益率之间的协方差；r im为i股票和市场投资组合收益率之间的相关系数；Rit为第t期i股票的投资收益率；Rmt为第t期股票市场投资组合的收益率；n为样本数；其他符号的含义如前所述。若将上述公

48、式分母中的(n-1)改成n，则计算得到的是总体的标准差和协方差。在样本容量大的情况下，样本标准差和协方差与总体标准差和协方差近似相等。5.2.3 股票的系数计算及特征线绘制模型相关函数STDEV.S返回样本数据的标准偏差COVARIANCE.S计算样本协方差，即两个数据集中每对数据点的偏差乘积的平均值CORREL返回单元格区域 array1 和 array2 之间的相关系数SLOPE返回根据 known_ys 和 known_xs 中的数据点拟合的线性回归直线的斜率INTERCEPT利用已知的 x 值与 y 值计算直线与 y 轴的截距 【例5-19】5.2.4 资本资产定价模型与证券市场线资本

49、资产定价模型式中：R i为证券i 的期望收益率；R F为无风险利率；R m为证券市场投资组合的期望收益率； i为证券i的风险系数或 系数。5.2.4 资本资产定价模型与证券市场线对于投资组合而言，资本资产定价为： 式中：RP为证券投资组合的期望收益率；P为证券投资组合的风险系数或 系数；n为证券投资组合中证券的种数；wi为第i种证券的投资额占证券投资组合总投资额的比重；其他参数的含义如前所述。 5.2.4 资本资产定价模型与证券市场线【例5-20】使用相关函数运用模拟运算表绘制图表 5.2.5 股票交易数据的获取及投资分析图表的绘制模型股票交易数据的获取方法【数据】/【获取外部数据】/【自We

50、b】/【新建Web查询】【例5-21】绘制K线图和移动平均线【例5-22】5.3 证券投资组合优化决策5.3.1 证券投资组合的收益与风险计算模型5.3.2 证券投资组合的优化决策模型5.3.1 证券投资组合的收益与风险计算模型相关的计算公式P188 【例5-23】SUMPRODUCT函数SQRT函数MMULT函数5.3.2 证券投资组合的优化决策模型使用相关函数利用规划求解工具 【例5-24】6 资本成本与资本结构6.1 资本成本的计算6.2 杠杆作用分析6.3 资本结构理论模型6.1 资本成本的计算6.1.1 债务资本成本的计算模型6.1.2 权益资本成本的计算模型6.1.3 综合资本成本

51、的计算模型6.1.4 边际资本成本规划模型6.1.1 债务资本成本的计算模型债务资本成本计算的相关公式P192-193【例6-1】 利用相关函数运用单变量求解工具6.1.2 权益资本成本的计算模型权益资本成本计算的相关公式P194-195权益资本成本的计算与分析模型使用相关函数运用模拟运算表【例6-2】 【例6-3】 6.1.3 综合资本成本的计算模型相关公式式中：Kw为综合资本成本；Wi为第i种资本占全部资本的权重；Ki为第i种资本的成本；n为资本的种数。 【例6-4】 6.1.4 边际资本成本规划模型相关公式式中：Bi为第i种资本所引起的筹资总额的成本分界点；Fi为第i种资本的成本分界点；

52、Wi为第i种资本在目标资本结构中所占的比重。ROUND函数返回某个数字按指定位数四舍五入后的数字 【例6-5】 6.2 杠杆作用分析6.2.1 本量利之间的关系及经营杠杆 系数的计算与分析模型6.2.2 财务杠杆系数的计算与分析模型6.2.3 总杆杆系数的计算与分析模型6.2.1 本量利之间的关系及经营杠杆系数的计算与分析模型本量利之间的关系息税前利润 = 销售收入变动成本固定经营成本=产销量(单价单位变动成本)固定经营成本保本点销售量 = 固定经营成本 /(单价单位变动成本)保本点销售额 = 保本点销售量单价 6.2.1 本量利之间的关系及经营杠杆系数的计算与分析模型经营杠杆作用式中：DOL

53、为经营杠杆系数；EBIT为息税前利润；Q为销量；为变动量；p为单价；v为单位变动成本；F为固定经营成本；S为销售额；VC为变动总成本。6.2.1 本量利之间的关系及经营杠杆系数的计算与分析模型 【例6-6】TRANSPOSE函数求矩阵的转置矩阵运用模拟运算表工具6.2.2 财务杠杆系数的计算与分析模型财务杠杆作用式中：DFL为财务杠杆系数；I为债务利息；DP为优先股股息；T为所得税税率；EPS为普通股每股利润；N为普通股股份数；其他符号的含义如前所述。6.2.2 财务杠杆系数的计算与分析模型普通股每股利润权益报酬率与投资报酬率式中：R为权益报酬率（净利润/股东权益）；K为投资报酬率（息税前利润

54、/资本总额）；i为债务利率；B为债务资本；S为股东权益；T为所得税税率。 6.2.2 财务杠杆系数的计算与分析模型 【例6-7】 【例6-8】 OR函数进行逻辑或关系的判断运用模拟运算表工具6.2.3 总杆杆系数的计算与分析模型总杠杆作用 或式中：DTL为总杠杆系数；其它符号的含义如前所述。 【例6-9】 6.3 资本结构理论模型6.3.1 MM模型6.3.2 权衡模型6.3.1 MM模型相关公式：无公司税的情况无公司税的情况6.3.1 MM模型相关公式：无公司税的情况无公司税的情况6.3.1 MM模型【例6-10】 【例6-11】使用相关函数运用规划求解工具 6.3.2 权衡模型考虑公司税考

55、虑财务危机成本 相关公式式中：FA为财务危机成本和代理成本的现值；TC D 为债务利息抵税的现值（假设永久负债） 【例6-12】 7 筹资预测与决策分析7.1 长期筹资决策分析7.2 资金需要量预测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 利用比较公司价值法选择筹资方案模

56、型【例7-2】使用相关函数绘制图表计算权益资本成本计算权益的价值计算公司价值计算加权平均资本成本选择最优方案7.1.3 利用每股利润分析法选择筹资方案模型普通股每股利润的计算公式为： 式中：EPS为普通股每股利润；n为普通股股数；EBIT为息税前利润；I为债务利息；DP为优先股股息；T为所得税税率。7.1.3 利用每股利润分析法选择筹资方案模型【例7-3】使用相关函数运用模拟运算表工具绘制图表7.1.4 长期银行借款筹资分析模型长期银行借款的还本付息方式 一次性偿付法 等额利息法 等额本金法 等额摊还法 7.1.4 长期银行借款筹资分析模型相关的函数PMT函数基于固定利率及等额分期付款方式，返

57、回投资或贷款的每期付款额。IPMT函数基于固定利率及等额分期付款方式，返回投资或贷款在某一给定期次内的利息偿还额。PPMT函数基于固定利率及等额分期付款方式，返回投资或贷款在某一给定期次内的本金偿还额。7.1.4 长期银行借款筹资分析模型【例7-4】 【例7-5】使用相关函数定义名称粘贴列表设置组合框控件运用模拟运算表工具7.1.5 租赁筹资决策分析模型每期应付租金计算模型 【例7-6】每期租金计算方法平均分摊法等额年金法（后付等额租金；先付等额租金）使用相关函数租赁设备及租金支付方式选择计算模型【例7-7】 使用相关函数设置组合框控件7.1.5 租赁筹资决策分析模型租赁与借款筹资决策分析模型

58、 【例7-8】 使用相关函数盈亏平衡租金的计算与租赁决策分析模型【例7-9】 求解使租赁设备增量净现值为0的年租金使用相关函数运用单变量求解工具7.2 资金需要量预测7.2.1 利用销售百分比法预测资金需要量模型7.2.2 利用资金习性法预测资金需要量模型7.2.3 利用因果关系法预测资金需要量模型7.2.1 利用销售百分比法预测资金需要量模型企业从外部追加筹措资金的数额：式中：M为外部追加资金需求量；D为股利支付率；S0为基期销售额；S1为计划销售额；(A/S)为基期敏感性资产占销售额百分比；(L/S)为基期敏感性负债占销售额百分比；R为销售净利率；M1为计划期的其他资金需求，即不随销售额成

59、正比例变动的其他资金需要量。【例7-10】 7.2.2 利用资金习性法预测资金需要量模型相关公式单位变动资金=高低点资金占用量之差/高低点产销量之差不变资金总额=高点(或低点)资金占用量单位变动资金高点(或低点)产销量预测期资金需要量=不变资金总额+单位变动资金预测期产销量(或销售额)【例7-11】 7.2.3 利用因果关系法预测资金需要量模型相关函数TREND返回一条线性回归拟合线的值。SLOPE返回一条线性回归拟合线的斜率。INTERCEPT返回一条线性回归拟合线的截距。【例7-12】 8 流动资产管理8.1 现金管理8.2 应收账款管理8.3 存货管理8.1 现金管理8.1.1 现金预算

60、表的编制模型8.1.2 最佳现金余额的确定模型8.1.1 现金预算表的编制模型现金预算表的构成现金收入现金支出净现金流量投资或融资安排期末现金余额【例8-1】 8.1.2 最佳现金余额的确定模型最佳现金余额的确定方法成本分析模式存货模式【例8-2】使用相关函数运用规划求解工具8.2 应收账款管理8.2.1 应收账款信用标准决策模型 8.2.2 应收账款信用条件决策模型8.2.3 应收账款收账政策决策模型8.2.4 应收账款信用政策方案的净现值 计算与决策模型8.2.5 应收账款日常管理模型8.2.1 应收账款信用标准决策模型【例8-3】计算信用标准变化对利润的影响计算信用标准变化对应收账款机会