第5章 筹资决策的Excel建模
Excel与财务管理 第5章 筹资决策
【例5-1】长兴公司2005年末的资产负债表
及其他有关数据如图5-1所示，要求预测该 公司2006年需要从外部追加的资金需要量。
5
第五章>>第一节
一、销售百分比法
如果该公司2006年需要从外部追加的 资金全部以长期借款的方式筹资，要求编 制2006年末的预计资产负债表。
图5-1销售百分比法预测资金需要量
11
第二节 长期借款的还本付息
第五章>>第二节
长期借款是指企业向银行或其他非银行
金融机构借入的，使用期超过一年的借 款，主要用于购建固定资产和满足长期 流动资产占用的需要。
12
第五章>>第二节
一、长期借款的偿还方式
企业以长期借款方式筹集的资金属于借
入资金，需要按期还本付息。企业在向 银行或其他非银行金融机构借入款项之 前，除了借贷双方协商确定借款的金额、 借款利率、借款期限等基本条款之外， 还要对还本付息的方式做出选择，在此 基础上做好还本付息规划。 （一）还本付息方式 长期借款的还本付息方式主要有： 一次性偿还法、等额利息法、等额摊还 法及等额本金法等。
20
第五章>>第二节
一、长期借款的偿还方式
问：企业应选择哪种还本付息方式？ 尽管两个借款方案的年利率相同，但由于还本 付息的方式不同，借款利息具有的抵税作用不 相等，可以使企业获得的税收抵免的利益不尽 相同。 因此，可以通过计算比较各个方案的税后成本 现值来判定方案的优劣。 在解答问题之前，先来学习数组公式的应
13
第五章>>第二节
一、长期借款的偿还方式
1．一次性偿还法
一次性偿付法是指在借款到期时一次 性偿还本金和利息的方法。期末一次性偿 还额的计算公式为：
第五章Excel在筹资分析与决策中的应用
第二节
货币时间价值计算的相关函数
一、货币时间价值概念和计算
1.复利终值 2.复利现值
3.年金终值
7．利率、期数的计算
4.年金现值 5.永续年金
6.年金的计算
6．年金的计算 根据上面讨论的年金现值公式或年金终值公式进行推导，便可计算出年金。 （1）普通年金（后付年金）的计算公式：
A=FV/（FV，i，n） A=PV/（PV，i，n） （2）预付年金（先付年金）的计算公式： A=FV/[（FV，i，n+1）-1] A=PV/[（PV．i，n-1）+1]
【例5-4] 某企业租用一设备，租金总额25 000元，年利率为10%，每年年末支付租金，租 期为5年。
每期支付租金，计算公式为：=PMT（10%，10，-25 000），其值为4068.63元。 第2年支付的本金，计算公式为：=PPMT（10%，2，10，-25 000），其值为1725.50元。 第2年支付的利息，计算公式为：=IPMT（8%，2，5，-36 000），其值为2343. 13元。
计算公式为：=NPER（10%，4068，-25000）．其值为5.02年。
5．利率函数RATE（） 语法：RATE（nper, pmt, pv, fv, type, guess） 功能：在已知期数、每期付款及现值的条件下，返回年金的每期利率。RATE为计算年金利 率函数名。 参数：guess是对利率的猜测数。如果guess被省略，将假设它是10%。如果RATE函数无法 收敛，需使用不同的guess猜测数再试一次。
Excel财务建模与应用教程第05章筹资决策模型
（2）资金习性法预测资金需要量模型
①Index函数介绍 函数INDEX()有两种形式：数组和引用。数组形式通常返回数值或数值数组；引用形 式通常返回引用。INDEX(array,Row_num,column_num) 返回数组中指定单元格或 单元格数组的数值。此处主要介绍引用形式。 语法：INDEX(reference,Row_num,column_num,area_num) 功能：返回指定的行与列交叉处的单元格引用。如果引用由不连续的选定区域组成， 可以选择某一连续区域。 参数：Reference 对一个或多个单元格区域的引用。如果为引用输入一个不连续的区 域，必须用括号括起来。如果引用中的每个区域只包含一行或一列，则相应的参数 Row_num 或 column_num 分别为可选项。例如，对于单行的引用，可以使用函数 INDEX(reference,,column_num)。Row_num 引用中某行的行序号，函数从该行返 回一个引用。COLUMN_num 引用中某列的列序号，函数从该列返回一个引用。 Area_num 选择引用中的一个区域，并返回该区域中 Row_num 和 column_num 的 交叉区域。选中或输入的第一个区域序号为 1，第二个为 2，以此类推。如果省略 area_num，函数 INDEX 使用区域 1。
运用SLOPE函数计算的斜率C13=SLOPE(C3:C7,B3:B7)=0.38；
根据回归方程y=a+bx计算的2021年资金需要量G12=C12+C13*B8＝152.43 万元。
直接用TREND函数计算的2021年资金需要量 G13=TREND(C3:C7,B3:B7,B8)=152.43 万元。两者结果相同。
（2）销售百分比法预测资金需要量模型
基于Excel的筹资决策分析模型
企业的筹资决策起着连接金融市场和实业投资市场的桥梁作用。
本文利用Excel ，首先利用单变量模拟运算、双变量模拟运算对筹资决策进行分析，使复杂问题简单化，然后介绍在进行筹资决策分析时使用Excel 的技巧。
下面以“长期借款”为例进行筹资决策分析。
一、利用单变量模拟运算进行分析1.单变量模拟运算是指在公式中可以有一个变量值，只要在单元格中输入此变量值，即可列出该数值变化后的所有计算结果。
例：某公司长期借款金额为500000元，借款年限为10年，借款年利率为10%，每年还款1次。
建立单变量模拟运算模型表，对工作表中相关项目进行格式化，如表1所示。
在单元格B6中输入公式“=B4∗B5”。
利用等额还款函数PMT 计算分期等额还款额。
在单元格B7和D2中分别输入公式“=PMT （B 3/B 5，B 6，B 2）”。
选中单元格区域“C2：D7”，选择“数据”下拉列表“模拟运算表”菜单项弹出“模拟运算表”对话框。
由于将不同的借款金额设置在C 列，所以将借款额变量设置为列变量。
即在“模拟运算表”对话框“输入引用列的单元格”中输入“B 2”，单击“确定”按钮返回工作表中。
在单变量模拟运算模型表中，单元格C2并无任何作用。
在设置列变量时，设置公式的单元格应该位于变量值所在列的右侧，并高于“第1个变量单元格”一行；在设置行变量时，设置公式的单元格应该位于变量值所在行的下一行，并位于“第1个变量单元格”左列。
2.将单变量模拟运算进行拓展，分析不同的年利率对还款额的影响。
将表1中C3~C7中的不同还款额替换为不同年利率，选中单元格区域“C2：D7”，选择“数据”下拉列表“模拟运算表”菜单项，弹出“模拟运算表”对话框。
由于将不同的年利率设置在C 列，所以将年利率作为列变量。
在“模拟运算表”对话框“输入引用列的单元格”中输入“B 3”，单击“确定”按钮返回工作表中。
二、利用双变量模拟运算进行分析双变量模拟运算是指公式中含有两个变量值，能够在一次操作过程中完成多组不同数组的计算。
Excel在筹资决策中的应用培训教材
⑶ 将鼠标指针移到建立的[组合框]控件上， 单击右键，出现快捷菜单，选择[控件设置格 式]，出现[设置空间格式]对话框，选择[控件] 项。
⑷ 在[数据区域]栏中填入“$E$3:$E$4”， 在[单元格链接]栏中填入“$C$5”。
2020/11/10
杜茂宝
28
三、举债筹资与租赁融资的比较分析
㈠ 净现值(NPV)分析的基本原理
到底是以租赁方式获得资产还是借款购买 资产呢？借款购买资产时，借款利息、折旧 等费用可以抵税，租赁资产其支付的租金和 融资租赁设备的折旧也具有低税的作用。因 此，按净现值进行分析，在借款分析模型中 增加贷款分期偿还分析表，在租赁分析模型 中增加租赁分期偿还分析表，分别计算出各 自的税后现金流量，然后把它们变成现值。 然后选择成本现值较小的方案进行筹资。
首先根据题意设计租赁筹资图形接口模 型。在模型设计完成的基础上，分别计算各 种情况下的租金偿付金额。
模型设计过程如下：
2020/11/10
杜茂宝
19
建立如图4-2所示的租赁结构模型
2020/11/10
图4-2 杜茂宝
20
⑴ 单击工具栏上的[视图]按钮，选择[工 具栏]，在[工具栏]菜单中单击[窗体]工具栏。
2020/11/10
杜茂宝
23
1、建立组合框按钮，用来选择所租赁的设备。 具体创建过程见实际操作。
2、从租赁价目表中检索所选设备的租金及支 付租金的方式。所用函数为INDEX()。
㈢ 建立滚动条控制项，将租赁年利率限定在 合理的范围。租赁公司提供的租赁利率一般给出可 供选择范围，如5％～10％。具体创建过程见实际 操作。
Excel在财务中的应用之筹资决策分析(ppt 17页)
筹资决策分析
现金流量价值函数
相关参数意义：
• rate:各期利率 • per:计算的期数 • nper:年金收付的总期数 • pmt:年金 • pv:现值，若省略则默认为0 • fv:终值，若省略则默认为0 • type:年金类型 • guess:估计值
年金终值函数FV()
找 一 个 目 标 ，继续 这样忙 ，就像 省略号 一样， 毫无休 止地忙 下去， 翻开历 史的长 卷，我 们看到 牛顿在 忙着他 的实验 ；爱迪 生在忙 着思考 ；徐霞 客在忙 着记载 游玩； 李时珍 在忙着 编写《 本草纲 目》。 再看那 位以笔 为刀枪 的充满 着朝气 与力量 的文学 泰斗鲁 迅 ， 他 正 忙 着用他 独有的 刀和枪 在不停 地奋斗 。忙是 省略号 ，确定 了一个 目标那 么就一 直忙下 去吧!这 样 的 忙 一定会 忙出生 命灵动 的色彩 。 忙 是惊叹 号。世 界上的 人都在 忙着自 己的事 ，大自 然亦如 此，小 蜜蜂在 忙，以 蜂蜜为 回报。 那么人 呢?居里 夫人的 忙 ，以放 射性元 素的发 现而得 到了圆 满的休 止符； 爱因斯 坦在忙 ，以相 对论的 问世而 画上了 惊叹号 ；李白 的忙， 以那豪 放的诗 歌而有 了很大 的成功 ；张衡 的忙， 因为那 地动仪 的问世 而让世 人仰慕 。每个 人都应 该有效 率的忙 ，而不 是整天 碌碌无 为地白 忙。 人 生 是 有 限 的、短 暂的， 因此， 每个人 都应该 在有限 的生命 里忙出 属于他 的惊叹 号；都 应在有 限的生 命里忙 出他的 人生精 彩篇章 。 忙是 万物、 世界、 人生中 都不可 缺少的 一部分 。作为 这世上 最高级 动物的 我们， 我们在 忙什么 呢?我 们要忙 得有意 义，有 价 值 ， 我 们 要忙出 属于我 们的精 彩。我 们的忙 不能永 远是问 号，而 应是省 略号和 感叹号 。忙就 要忙得 精彩， 忙得不 亦乐乎 。 解剖 ：本文 将生活 中的一 句口头 禅“忙 得不亦 乐乎” 机智翻 新，拟 作标题 ，亮出 一道美 丽的风 景。并 据 Kc )t
Excel表格模板-筹资决策分析模型
所得税率：贴现率：8%期限期初尚欠本金偿还利息150000.005000.00241810.134181.01332801.263280.13422891.522289.15511990.791199.08合计-15949.3765949.3750000.005213189.8710900.727513189.8711990.793913189.879008.861313189.879909.7510等额还款金额偿还本金避13189.878189.8716长期借款分期等额还款模型借款金额 50000年利率 10% 借款期限 5每年还款期数 1还款总期数 5分期等额还款金额￥-13,189.87等额还款筹资决策分析表33%8%9%10% 5%6%7%-7811.7848000-65098.16400000长期借款分期等额还款模型借款金额 500000年利率 10%借款期限 10每年还款期数 1还款总期数 10分期等额还款金额 ￥ -81,372.70 2 3 4 5-265098.04 -180174.27 -137745.02 -112313.56-268902.44 -183604.28 -141005.92 -115487.40-272718.45 -187054.91 -144295.75 -118698.20-276545.89 -190525.83 -147614.06 -121945.35-280384.62 -194016.76 -150960.40 -125228.23-284234.45 -197527.38 -154334.33 -128546.23-288095.24 -201057.40 -157735.40 -131898.74还 款借款年利率￥ -81,372.704%长期借款分期等额还款模型借款金额 500000 年利率 10%借款期限 10每年还款期数 1还款总期数 10分期等额还款金额 ￥ -81,372.70长期借款分期等额还款模型500000借款金额10%年利率10借款期限1每年还款期数10还款总期数￥ -81,372.70分期等额还款金额每期偿还金额￥ -81,372.70不同借款额-81372.70500000 -7323.5445000所得税率：贴现率：8%期限期初尚欠本金偿还利息120000.00800.00217829.44713.18315572.06622.88413224.39528.98510782.81431.31合计-3096.3514852.7811756.4310212970.562441.58174.2970.562539.24142.2970.562257.38235.2970.562347.67205.等额还款金额偿还本金避税2970.562170.56264.长期借款分期等额还款模型借款金额 20000年利率 8%借款期限 4每年还款期数 2还款总期数 8分期等额还款金额￥-2,970.56等额还款筹资决策分析表33%5263.2960686.0848268.83755.4212434.459139.69395.7012794.188707.501379.7311810.1410125.291082.4412107.439611.27避税额净现金流量现值1650.0011539.8710685.07单位： 元-87007.38 -80039.85 -74514.74-96036.20-108157.69-90337.19 -83399.40 -77910.04-99345.26-111459.89-93722.01 -86820.27 -81372.70-102702.75-114803.69-80517.97 -73511.12 -67933.98-89567.51-101681.31-83733.88 -76743.24 -71188.75-92776.61-104897.90 数总 期 8 9 1076-74263.92 -67246.50 -61645.47-83304.81-95380.95-77360.91 -70345.04 -64752.29-86409.91-98508.73单位：元避税额净现金流量现值264.00 2706.562506.07 235.35 2735.212345.00 205.55 2765.012194.95 174.56 2795.992055.14 142.33 2828.221924.84 1021.7913830.9911026.00。
第五章Excel在资金筹集管理中的应用
债券资本成本 权益资本成本 综合资本成本
经营杠杆系数 经营杠杆系数 财务杠杆系数 总杠杆系数
调整现值法 权益流量法 加权平均资本成本法
负债经营风险
边际资本成本
资本成本比较分析法 每股收益分析法 总价值分析法
第四节Excel在财务杠杆利用可行性分析中的应用 财务杠杆利用的可行性分析模型—调整现值法 财务杠杆利用的可行性分析模型—权益流量法 财务杠杆利用的可行性分析模型-加权平均资本成本法
第五节Excel在负债经营风险分析中的应用 负债经营风险分析模型
第六节Excel在边际资本成本规划分析中的应用 边际资本成本规划分析的Excl工作表
序号 1 1.1 1.2 2 2.1
2.2
第五章Excel在资金筹集管理中的应用
节数
应用Βιβλιοθήκη 第一节Excel在资金需要量预测中的应用
利用高低点法预测资金需要量的计算模型
资金习性法
资金需要量预测模型
销售百分比法
第二节Excel在资本成本分析中的应用
不考虑资本时间价值时长期借款的资本成本模型
简单计算
单变量求解方法、IRR
模型
典型实例5-1 典型实例5-2
典型实例5-3
典型实例5-3
典型实例5-4 典型实例5-5 典型实例5-6
典型实例5-7 典型实例5-8 典型实例5-9 典型实例5-10
典型实例5-11 典型实例5-11 典型实例5-11
典型实例5-12
典型实例5-13
典型实例5-14 典型实例5-15 典型实例5-16
函数计算方法、规划求
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
5.4 长期借款筹资决策的Excel模型
5.4.2 长期借款筹资决策模型的设计 4．建立复杂的贷款筹资偿还分析模型 ღ【例5-14】
5.5 租赁筹资分析的Excel模型
1．平均分摊法
5.5.1 租赁筹资概述
R为每次支付的租金；C为租赁设备的购置成本；S为租赁设备预计残值； I为租赁期间利息；F为租赁期间手续费；N为租期
5.5 租赁筹资分析的Excel模型
2．等额年金法 （1）后付租金
A P （ / P / A,i, n）
（2）先付租金
5.5 租赁筹资分析的Excel模型
5.5.2 租赁筹资分析模型的设计 1．租赁筹资分析模型 ღ【例5-15】
5.5 租赁筹资分析的Excel模型
5.5.2 租赁筹资分析模型的设计 2．编制租金摊销计划表 ღ【例5-16】
5.4 长期借款筹资决策的Excel模型
5.4.2 长期借款筹资决策模型的设计 2．运用模拟运算表进行贷款影响因素分析
单变量模拟运算表 ღ【例5-11】 双变量模拟运算表 ღ【例5-12】
5.4 长期借款筹资决策的Excel模型
5.4.2 长期借款筹资决策模型的设计 3．长期借款中最佳贷款方案决策模型 ღ【例5-13】
2．CUMPRINC函数 （1）用途。返回一笔贷款在给定的start_period到end_period期间累计偿还的本金数额。 （2）语法。CUMPRINC(rate, nper, pv, start_period, end_period, type)
5.2 筹资决策模型的相关函数与工具运用
3．FVSCHEDULE函数 （1）用途。基于一系列复利返回本金的未来值。函数FVSCHEDULE用于计算某项投资在变动或可调 利率下的未来值。 （2）语法。FVSCHEDULE (principal, schedule)
金额 60 000 340 000 400 000 600 000 220 000 1 620 000
项目 应付账款 应交税金 长期负债
股本 留存收益 负债及所有者权益合计
金额 200 000 100 000 460 000 800 000
60 000 1 620 000
5.4 长期借款筹资决策的Excel模型
5.6 借款筹资与租赁筹资比较选择的Excel模型
1．租赁筹资
5.6 借款筹资与租赁筹资比较选择租赁筹资比较选择的Excel模型
3. 租赁筹资与借款筹资的比较分析
5.2 筹资决策模型的相关函数与工具运用
5.2.1 相关函数
1. CUMIPMT函数 （1）用途。返回一笔贷款在给定的start_period到end_period期间累计偿还的利息数额。 （2）语法。CUMIPMT(rate, nper, pv, start_period, end_period, type)
5.2 筹资决策模型的相关函数与工具运用
5.2.1 工具与方法
1．数据分析——回归分析
回归分析工具通过对一组观察值使用最 小二乘法直线拟合来执行线性回归分析。
2．单变量求解
3．模拟运算表
。Excel的模拟运算表是利用模拟运算设定的，利用Excel 公式，计算显示运算结果。在Excel中主要可以构造两种模 拟运算表：单变量模拟运算表和双变量模拟运算表。
年度 2012 2013 2014 2015 2016 2017
产销量（万件） 560 600 550 490 650 710
资金需要量（万元） 500 545 480 460 595 625
5.3 筹资数量预测的Excel模型
2．销售百分比法预测模型
项目 货币资金 应收账款
存货 固定资产 无形资产 资产总计
第5章 筹资决策的Excel建模
5.1 筹资决策模型概述 5.2 筹资决策模型的相关函数与工具运用 5.3 筹资数量预测的Excel模型 5.4 长期借款筹资决策的Excel模型 5.5 租赁筹资分析的Excel模型 5.6 借款筹资与租赁筹资比较选择的Excel模型
学习目标
掌握期间累计本金函数CUMIPMT的应用； 掌握期间累计利息函数CUMPRINC的应用； 掌握不规则时间流下的未来值函数FVSCHEDULE的应用； 掌握引用函数INDEX的应用； 掌握回归分析工具、单变量求解、模拟运算表、建立图形控 件等软件工具的使用； 掌握筹资数量预测模型的设计； 掌握长期借款筹资模型的设计； 掌握租赁筹资图形控件模型的设计； 掌握租赁筹资与借款筹资方案比较分析模型的设计和分析。
5.3 筹资数量预测的Excel模型
2．线性回归分析法
y=a+bx
ღ【例5-5】
5.3 筹资数量预测的Excel模型
3．销售百分比法
销售百分比法是根据销售收入与资产负债表和利润表项目之间的比例关系，预测各项目短期资本需要量的方法。
ღ【例5-6】
5.3 筹资数量预测的Excel模型
5.3.2 筹资数量预测模型的设计 1．线性回归分析法预测模型
5.4.1 长期借款筹资决策概述
等额利息法
等额利息法指的是每期期末 按借款利率偿还固定的利息， 到期一次还本
一次性偿付法
一次性偿付法，顾名思义，
是指最后一期偿还本金和利
息（复利计息）
等额利息法 等额本金法 等额偿还法 一次性偿付法
等额本金法
等额本金法则是指每年偿还固定的本金及相应的 利息，用这种方法每年偿还本金的数额都相等， 但每年支付的利息随着年末剩余本金余额的减少 而逐年降低
5.1 筹资决策模型概述
企业筹集资金，是指企业根据其生产经营、对外投资和调整资金结构的需要，通过筹资渠 道和资金市场，运用筹资方式，经济、有效地筹措资金的过程。
1．筹资数量预 测的Excel模型
2．长期借款筹 资决策的Excel 模型
3．租赁筹资决 策的Excel模型
4．借款筹资与 租赁筹资比较选 择的Excel模型
4．在单元格下建立下拉式列表框
【
5.2 筹资决策模型的相关函数与工具运用
1．数据分析——回归分析 【开发工具】→【加载项】 → 【分析工具库】 → 【回归分析】
ღ【例5-1】
5.2 筹资决策模型的相关函数与工具运用
2．单变量求解 3．模拟运算表 （1）单变量模拟运算表
ღ【例5-2】
（2）双变量模拟运算表
4．INDEX函数 （1）数组形式 ① 用途。返回表格或数组中的元素值，此元素由行号和列号的索引值给定。当函数INDEX的第一个 参数为数组常量时，使用数组形式。 ② 语法。INDEX(array, row_num, [column_num]) （2）引用形式。 ① 用途。返回指定的行与列交叉处的单元格引用。如果引用由不连续的选定区域组成，可以选择某 一选定区域。 ② 语法。 INDEX (reference, row_num, [column_num], [area_num])
ღ【例5-3】
5.2 筹资决策模型的相关函数与工具运用
4．在单元格下建立下拉式列表框 【开发工具】 → 【插入】 → 【表单控件】→ 【组合框（窗体控件）】
ღ【例5-4】
5.3 筹资数量预测的Excel模型
5.3.1 筹资数量的预测 1．因素分析法
资本需要量=（上年资本实际平均占用量-不合理平均占用额） ×（1±预测年度销售增减率） ×（1±预测期资本周转速度变动率）
等额偿还法
等额偿还法下，则每年偿还
的本金和利息总额都是相等
的
5.4 长期借款筹资决策的Excel模型
5.4.2 长期借款筹资决策模型的设计
1.相关函数的运用 PMT函数 ღ【例5-7】
CUMPRINC函数 ღ【例5-8】
CUMIPMT函数 ღ【例5-9】
FVSCHEDULE函数 ღ 【例5-10】
(1 k)n
n为设备租赁（以及借款还本付息）年限
5.6 借款筹资与租赁筹资比较选择的Excel模型
2．成本费用现值法
比较NBC和NLC，当NBC大于NLC时，应租赁设备；当 NBC小于NLC时，应借款购买设备。
NBC P n (It Dt ) T n M t (1 T ) S
5.6 借款筹资与租赁筹资比较选择的Excel模型
5.6.1 借款筹资与租赁筹资比较选择的方法 1．增量净现值法
NAL P n Lt (1 T ) (Dt I t ) T n M t (1 T ) S
t 1
(1 i)t
t1 (1 k)t
t1 (1 i)t
t1 (1 k)t
(1 k)n
NLC n Lt (1 T )
t1 (1 i)t
5.6 借款筹资与租赁筹资比较选择的Excel模型
5.6.2 借款筹资与租赁筹资比较选择模型的设计
1．租赁筹资 2. 借款筹资 3. 租赁筹资与借款筹资的比较分析 ღ【例5-17】