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

Excel在财务管理中的应用,主讲教师 韩良智 教授 北京科技大学经济管理学院 Tel:绪论,财务管理概述 本课程将学到的Excel知识,财务管理概述,财务管理的研究对象 资金运动 财务关系 财务管理的研究内容 长期投资决策 长期筹资决策 营运资金管理 财务与会计的区别和联系 财务管理的目标,本课程将学到的Excel知识,Excel的基本操作 运用公式 使用函数 创建与编辑图表 排序与分类汇总 模拟运算表,方案分析工具 单变量求解工具 规划求解工具 数据分析工具 数据透视表与数据透视图 录制宏,1 Excel基础知识,1.1 中文版Excel 2003概述 1.2 管理工作簿 1.3 输入和编辑数据 1.4 管理工作表 1.5 打印管理,1.1 中文版Excel 2003 概述,Excel 2003的启动与退出 Excel 2003的窗口结构 使用Excel的帮助系统,1.2 管理工作簿,新建工作簿 打开已有的工作簿 保存工作簿 并排比较工作簿 关闭工作簿,1.3 输入和编辑数据,1.3.1 选取单元格 1.3.2 输入数据的一般方法 1.3.3 特殊数据的输入方法 1.3.4 编辑数据 1.3.5 数据有效性的设置,选取单个单元格 直接用鼠标选取 【编辑】【定位】或F5键 选取单元格区域 选取整行或整列 选取全部单元格 选取连续的单元格区域 拖动鼠标 利用【Shift】键 选取不连续的单元格区域 利用【Ctrl】键,【Ctrl】+方向键 【Ctrl】+ 【Shift】+方向键,1.3.2 输入数据的一般方法,数据的类型 文本型数据 数值型数据 日期型数据 时间型数据,1.3.2 输入数据的一般方法,输入文本型数据的方法 文本运算符：& 有关的函数 TEXT函数：将数值转换为指定格式的文本。 ISTEXT函数：检验是否为文本。 DOLLAR函数：将数值转换为带美元符号的文本。 RMB函数：将数值转换为带人民币符号的文本。 LEFT函数：从左边取指定长度的字符 MID函数：从指定位置取指定长度的字符 RIGHT函数：从右边取指定长度的字符,1.3.2 输入数据的一般方法,输入数值型数据的方法 数值型数据的显示 使用自定义数字格式缩位显示数据，代码如下： 缩小千位显示数字： 0.00， 缩小万位显示数字： 0!.0, 缩小十万位显示数字：0!.00, 缩小百万位显示数字：0.00, 缩小十亿位显示数字：0.00, 使用特殊格式显示大写数字 NUMBERSTRING函数将小写数字转换为大写,1.3.2 输入数据的一般方法,与数值型数据有关的函数 VALUE函数：将代表数字的文本字符串转换成数字。 ISNUMBER函数：检验是否为数字。 INT函数：无条件舍去小数部分，保留整数部分。 ROUND函数：四舍五入。 ROUNDDOWN函数：靠近零值，向下舍入数字。 ROUNDUP函数：远离零值，向上舍入数字。 TRUNC函数：将数字截为整数或保留指定位数的小数。 FLOOR函数：将数字沿绝对值减小的方向无条件向下舍去到最接近的基数倍数。 CEILING函数：将数字沿绝对值增大的方向无条件向上进位到最接近的基数倍数。,1.3.2 输入数据的一般方法,输入日期型数据的方法 日期型数据的显示格式 日期型数据的计算,1.3.2 输入数据的一般方法,与日期型数据有关的函数 TODAY函数：返回系统当前日期的序列号。 快捷方式：Ctrl+; DATE函数：将三个数字组成一个日期序列号。 YEAR函数：获取指定日期序列号的年份数字。 MONTH函数：获取指定日期序列号的月份数字。 DAY函数：获取指定日期序列号的日数字。 WEEKDAY：用户获取某日期为星期几。 DAYS360函数：用于返回两个日期相差的天数（ 一年按360天计）。,1.3.2 输入数据的一般方法,输入时间型数据的方法 时间型数据的显示格式 时间型数据的计算,1.3.2 输入数据的一般方法,与时间型数据有关的函数 NOW函数：返回当前日期和时间所对应的序列号。 快捷方式： Ctrl+; Ctrl+Shift+; HOUR函数：用于获取时间值的小时数。 MINUTE函数：用于获取时间值的分钟数。 SECOND函数：用于获取时间值的秒数。,1.3.3 特殊数据的输入,输入相同数据 采用自动填充方式输入相同数据 采用数组输入方式输入相同数据 输入序列数据 填充序列数据 设置序列填充方式 单击【编辑】 【填充】【序列】 在序列对话框中进行相应的设置 自定义序列填充 【工具】【选项】【自定义序列】,1.3.3 特殊数据的输入,在一行中输入多行数据 按【Alt】和【Enter】组合键 填加批注 单击【插入】菜单中的【批注】命令 或在弹出的快捷菜单中选择【插入批注】命令,1.3.4 编辑数据,修改数据 复制数据 利用复制和粘贴命令 利用多重剪贴板工具 移动数据 利用剪切和粘贴命令 利用鼠标拖动,1.3.4 编辑数据,删除数据 直接按【Del】键 在【编辑】菜单中选择【清除】 查找与替换 【编辑】【查找】，或按【Ctrl+F】组合键 【替换】选项卡，或直接按【Ctrl+H】组合键,1.3.5 数据有效性的设置,【数据】【有效性】 数据介于最小值和最大值区间的情况 只允许输入指定的序列数据的情况 只能输入数字的情况 只能输入文本的情况 圈释无效数据 【视图】【工具栏】【圈释无效数据】,1.4 管理工作表,1.4.1 使用多张工作表 1.4.2 调整行、列和单元格 1.4.3 设置工作表格式 1.4.4 保护单元格与保护工作表 1.4.5 隐藏与显示 1.4.6 划分窗口,1.4.1 使用多张工作表,选定工作表 切换工作表 插入工作表 移动或复制工作表 删除工作表 重命名工作表 为工作表添加链接,1.4.2 调整行、列和单元格,插入行、列或单元格 删除行、列或单元格 调整行高和列宽 设置工作表格式 保护单元格与保护工作表 隐藏与显示 划分窗口,插入行、列或单元格,插入行 一次插入一行：【插入】【行】 一次插入多行 插入列 一次插入一列： 【插入】【列】 一次插入多列 插入单元格 【插入】菜单中选择【单元格】,删除行、列或单元格,在【编辑】菜单中执行【删除】 在快捷菜单中选择【删除】 按【Del】键,调整行高和列宽,调整行高 自动调整行高：【格式】【行】【最适合的行高】 用鼠标调整行高：拖动；双击 指定行高的精确数值：【格式】【行】【行高】 调整列宽 自动调整列宽 ：【格式】【列】【最适合的列宽】 用鼠标调整列宽 ：拖动；双击 指定列宽的精确数值：【格式】【列】【列宽】,设置工作表格式,设置单元格格式 设置的内容 设置数据格式 设置对齐方式 设置字体和字号 设置边框和底纹 设置的方法 利用工具栏上的按钮设置 利用菜单命令进行设置：【格式】【单元格】,设置工作表格式,自动套用格式 【格式】【自动套用格式】 运用条件格式 【格式】【条件格式】 取消与恢复网格线 【工具】【选项】【视图】,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.3 分页设置及调整,分页设置 水平分页设置 选定要插入分页符的行，执行【插入】【分页符】 垂直分页设置 选定要插入分页符的列，执行【插入】【分页符】 交叉分页设置 选定某个单元格，执行【插入】【分页符】,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.2 一笔现金流的复利终值计算与分析模型,复利终值的计算公式 或 式中：P 为现在的一笔收款或付款；i 为复利年利率；n 为年限；F为复利终值；FVIFi,n=(1+i)n称为复利终值系数，它表示现在的1元钱在n年后的价值。,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】 通过本例学习： 组合框控件的设置与使用方法,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.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, 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函数返回在指定方式下与指定数值匹配的数组中元素的相应位置。公式为： =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_ys 和 known_xs 中的数据点拟合的线性回归直线的斜率。公式为： = SLOPE(known_ys,known_xs) INTERCEPT函数利用已知的 x 值与 y 值计算直线与 y 轴的截距。公式为： = INTERCEPT (known_ys,known_xs),5.2.3 股票的系数计算及特征线绘制模型,相关的函数 STDEVP函数返回以参数形式给出的整个样本总体的标准偏差，公式为： = STDEVP(number1,number2,.) COVAR函数返回协方差，即每对数据点的偏差乘积的平均数。公式为： =COVAR(array1,array2) 函数CORREL返回单元格区域 array1 和 array2 之间的相关系数。公式为： = CORREL(array1,array2) 【例5-19】,5.2.4 资本资产定价模型与证券市场线,资本资产定价模型的数学公式为： 式中：R i为证券i 的期望收益率；R F为无风险利率；R m为证券市场投资组合的期望收益率； 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.1 MM模型 无公司税的情况 有公司税的情况 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_ys 和 known_xs 的直线（用最小二乘法），并返回指定数组 new_xs 在直线上对应的 y 值。公式为： = TREND(known_ys,known_xs,new_xs,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函数返回当前日期的序列号。序列号是 Microsoft Excel 日期和时间计算使用的日期-时间代码。如果在输入函数前，单元格的格式为“常规”，则结果将设为日期格式。其公式为： =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为存货的存储费率；PK即为单位存储费用。 【例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 = mx + b 或 y = m1x1 + m2x2 + + b（如果有多个区域的 x 值） 式中，因变量 y 是自变量 x 的函数值。m值是与每个 x 值相对应的系数，b 为常量。注意 y、x 和 m 可以是向量。 此函数的语法格式为： =LINEST(known_ys,known_xs,const,stats),9.1.1 利用相关函数预测销售收入模型,LINEST函数附加回归统计值返回的顺序,9.1.1 利用相关函数预测销售收入模型,FORECAST函数根据已有的数值计算或预测未来值。此预测值