Excel在会计与财务中的应用 第2版 课件 第9章 筹资管理

9.1Excel在资金需要量预测的应用9.2Excel在资本成本计算中的应用第9章筹资管理内容简介：本章主要讲解如何利用Excel进行资金管理，主要包括Excel对资金需要量进行预测，和通过利用Excel函数对资本成本进行计算，从而帮你你做出最优资本决策。学习目标：通过本章学习，学生应该掌握财务管理中资金需要量预测和资本成本的基础上，熟练使用Excel函数、Excel建模，和其自带的选项卡上的功能，如预测工作表。第9章Excel在资金管理中的应用2026/1/1429.1Excel在资金需要量预测中的应用9.1.1因素分析法9.1.2销售百分比法9.1.3回归直线法9.1.4高低点法2026/1/1439.1.1因素分析法1.因素分析法因素分析法又称分析调整法，是以有关项目基期年度的平均资金需要量为基础，根据预测年度的生产经营任务和资金周转加速的要求，进行分析调整，来预测资金需要量的一种方法。这种方法计算简便，容易掌握，但预测结果不太精确。它通常用于品种繁多、规格复杂、资金用量较小的项目。例如：小型超市、卖场等。因素分析法的计算公式如下：资金需要量=（基期资金平均占用额-不合理资金占用额）*（1+预测期销售增长率）*（1-预测期资金周转速度增长率）2026/1/1449.1.1因素分析法2.知识链接Average函数AVERAGE(number1,number2…)

具体步骤：1、单击要输入函数的单元格。2、单击“插入函数”按钮，选择AVERAGE函数，弹出“函数参数”对话框。3、指定参数，单击“确定”按钮。4、按住单元格右下角的填充手柄向下拖动至单元格进行复制.2026/1/1459.1.1因素分析法3.例题B传媒公司五年来的年度资金占用额如表9.1所示，经分析，其中不合理部分200万元，预计本年度销售增长5%，资金周转加速2%，则本年度的资金需要量是多少？2026/1/1469.1.1因素分析法步骤1：根据历史资金需求量利用AVERAGE函数计算出资金平均占用额。步骤2：根据集团的实际情况预测销售增长率，可以依据历史数据的平均值，也可以根据老员工的经验进行预测得到预测销售增长率。步骤3：根据集团的实际情况预测资金周转速度，可以依据历史数据的平均值，也可以根据老员工的经验进行预测得到预测资金周转速度。2026/1/147步骤4：根据公式：资金需要量=（基期资金平均占用额-不合理资金占用额）*（1+预测期销售增长率）*（1-预测期资金周转速度增长率），来计算如图9.2所示：2026/1/1489.1.2销售百分比法1.销售百分比法销售百分比法是假设资产和负债与销售额之间存在稳定的百分比关系，根据这个假设预计外部资金需求量的方法。因为企业销售规模扩大时，要相应的增加流动资产；如果销售规模增加很多，还必须增加长期资产。为取得扩大销售所需增加的资产，企业需要筹措资金。这些资金，一部分来自随销售收入同比例增加的流动负债，还有一部分来自预测期的留存收益，另一部分通过外部筹资取得。资金各个项目与销售收入之间的依存关系,并结合销售收入的增长情况来预测计划期企业需要从外部追加筹措资金的数额的方法。2026/1/1499.1.2销售百分比法2.IF函数语法：IF(logical_test,value_if_true,value_if_false)参数:（1）Logical_test表示计算结果为TRUE或FALSE的任意值或表达式。（2）Value_if_true表示logical_test为TRUE时返回的值。（3）Value_if_false表示logical_test为FALSE时返回的值。2026/1/14109.1.2销售百分比法3.例题步骤一：新建工作簿并命名为“9章筹资管理”步骤2：打开“9章筹资管理”工作簿，双击“sheet1“工作表标签，将其命名为”资金需要量预测表“。2026/1/14119.1.2销售百分比法步骤3：根据案例资料已知条件将资产负债表的相关信息输入，并建立计算区域，分析资产负债表各项是否是敏感性项目。利用逻辑函数IF判断资产项目的敏感性,并计算出各个项目占基期销售收入的百分比。在C11单元格中输入公式“=IF(B11=“是”,B3/$F$3,“不适用”)”,利用填充柄将其填充至C15单元格;在C16单元格中输入公式“=SUM(C11:C15)”,并设置C11:C16单元格格式为“百分比”（快捷键CTRL+1）。2026/1/14129.1.2销售百分比法步骤4：利用逻辑函数IF判断负债及权益项目的敏感性,并计算出各个项目占基期销售收入的百分比。在F11单元格中输入公式“=IF(E11="是",D3/$F$3,"不适用")”,利用填充柄将其填充至F15单元格;在F6单元格中输入公式“=SUM(F11:F15)”,并设置F11:F16单元格区域格式为“百分比”。2026/1/14139.1.2销售百分比法2026/1/1414步骤5：根据销售百分比法计算公式来确定外部融资需求量。在B17单元格中输入公式“=C16*(G3-F3)-F16*(G3-F3)-G3*G4*(1-G5)+G6”,即计算出该公司2018年的外部资金需求量为1000万元。9.1.3回归直线法2026/1/14151.回归直线法回归直线法是资金习性预测法中的一种。资金习性预测法是根据资金同业务量之间的依存关系来预测未来资金需要量的一种方法,资金习性是指资金的变动同产销量(或销售额)变动之间的依存关系。资金按照习性可以分为不变资金、变动资金和半变动资金。9.1.3回归直线法2.知识链接有些功能在office的工具栏中的找不到，并不是office这个功能不存在，而是隐藏在了office的加载项中。Office加载项是Office程序为了完成某种功能而需要在启动程序时自动加载的模块，例如书法字帖功能、稿纸功能、制作信封功能等。用户可以根据工作需要启用或禁用Office加载项，以提高Office程序的运行效率。2026/1/14163.例题B传媒公司2020—2024年的销售收入与资金占用之间的关系如表9.2所示。2025年的预计销售收入为700万元。要求:用回归直线法预测该公司2025年的资金需求量。2026/1/14179.1.3回归直线法年度20202021202220232024销售额x(万元)500520480540690资金占用y(万元)100110120125130步骤1：通过office加载项添加分析工具。文件-选项-加载项-分析工具库。2026/1/14189.1.3回归直线法步骤2：调用分析数据库。管理Excel加载项,单击“转到”按钮,勾选“分析工具库”选项,单击“确定”按钮,如图9.8所示。2026/1/14199.1.3回归直线法步骤3：选择回归分析方法。单击“数据”选项卡,选择“数据分析”功能,在弹出的“数据分析”对话框中选择“回归”,单击“确定”按钮,如图9.9和9.10所示。2026/1/14209.1.3回归直线法步骤4：设置因变量和自变量计算区域。在“回归”对话框的“Y值输入区域”中,输入因变量指标,资金占用额“$B$4:$F$4”;在“X值输入区域”中,输入自变量指标,销售额“$B$3:$F$3”;在“输出区域”中,输入“$A$8”,2026/1/14219.1.3回归直线法

2026/1/14229.1.3回归直线法

2026/1/14239.1.4高低点法2.知识链接MAX函数MIN函数INDEX函数MATCH函数2026/1/14249.1.4高低点法3.例题

B传媒公司2020—2024年的产量与资金占用数据如表9.3所示,该集团2025年的计划产量为120件。要求:利用高低点法预测2025年的资金需要量。2026/1/14259.1.4高低点法年度产量x(件)资金占用量y(万元)2020601212021701322022801402023901472024100151步骤1：根据案例资料的已知条件输入相关信息，并建立计算结果区域。2026/1/14269.1.4高低点法步骤2：使用MAX函数找出高点。选择F3单元格，输入公式“=MAX(B3:B7)”，选择F4单元格输入公式=“MIN（C3:C7）”2026/1/14279.1.4高低点法步骤3:使用INDEX函数找出高点对应的占用资金。单元格F3中输入公式“=INDEX(C3:C7,MATCH(F3,B3:B7))”,F4中输入“=INDEX(C3:C7,MATCH(F4,B3:B7))”2026/1/14289.1.4高低点法

2026/1/14299.1.4高低点法9.2Excel在资本成本计算中的应用2026/1/14309.2.1个别资本成本9.2.2权益资本成本9.2.1个别资本成本债务资本成本是指借款和发行债券的成本,包括借款或债券的利息和筹资费用。按照是否考虑货币的时间价值分为：不考虑货币时间价值的，和考虑货币时间价值的。2026/1/14319.2.1个别资本成本【例9.5】A集团取得5年长期借款200万元，年利率10%，每年付息一次，到期一次还本，借款费用率0.2%，企业所得税税率20%，该项借款的资本成本率为多少？2026/1/14329.2.1个别资本成本【操作步骤】：步骤1：打开“第9章筹资管理.xlsx”工作簿，超乳一张新的工作表，将其重命名为“个别资本成本”。步骤2：根据公司的实际情况将相关信息输入，并建立计算结果区域。步骤3：在B7单元格中输入公式“=B3*(1-B6)/(1-B5)”,最终结果为7.52%。2026/1/14339.2.1个别资本成本【拓展】假如每年6月31日需要向银行缴纳利息，怎么让Excel像闹钟一样提醒你，不要忘记缴纳利息？这就用到了Excel到期提醒日的功能。2026/1/14349.2.1个别资本成本2.知识链接IRR函数语法：IRR(values,[guess])功能：计算投资项目或者债券等的内涵报酬率或成本率，使得投资或债券资本成本等于0YIELD函数语法：YIELD(settlement,maturity,rate,pr,redemption,frequency,[basis])功能：返回定期支付利息的债券的收益。函数YIELD用于计算债券收益率。2026/1/14359.2.1个别资本成本考虑货币时间价值【例题】A集团取得5年期长期借款2000万元,年利率10%,每年付息一次,到期一次还本,筹资费用率为0.5%,所得税税率为25%。要求:如果不考虑资本时间价值,计算该公司长期借款的资金成本;如果考虑资本时间价值,计算该公司长期借款的资金成本。2026/1/14369.2.1个别资本成本【操作步骤】：(1)利用Excel的单变量求解的方法步骤1：设计如图所示的Excel计算模型,并定义F3单元格格式为百分比2026/1/14379.2.1个别资本成本步骤2：在G3单元格中输入公式“G3=A3*(1-D3)-PV(F3,C3,-A3*B3*(1-E3),-A3)”。其中,A3*(1-D3)计算的是资本的流入量,PV(F3,C3,-A3*B3*(1-E3),-A3)计算的是资本的流出量。2026/1/14389.2.1个别资本成本步骤3：添加规划求解项，单击文件-选项-加载项-规划求解-规划求解加载项，勾选之后点击确定，找到Excel中的“数据”选项卡下的“分析”，执行规划求解。2026/1/14399.2.1个别资本成本步骤4：

选择“规划求解”，利用现金流入值=未来现金流出利用资本成本率进行折现的现值，也就是当“G3”单元格等于0的时候的资本成本率，就是长期借款资本成本率。所以“设置目标”为“$G$3”单元格，期望现金流入现值与流出现值的目标值为“0”，通过改变资本成本“$F$3”来实现。再单击“确定”按钮,则求解得到长期借款的资本成本,如图9.23所示，点击“保留规划求解的解”2026/1/14409.2.1个别资本成本2026/1/14419.2.1个别资本成本(2)利用Excel的IRR函数的方法IRR函数法存在两种方法：一、直接利用公式；二、利用滚动调节条。这两种方法的本质都是调节利率使得现金流入等于现金流出。

方法一：【操作步骤】：步骤1：设计如图9.25所示的Excel计算模型,并定义G2单元格格式为百分比。2026/1/14429.2.1个别资本成本步骤2：在A6:

F6

单元格区域分别输入计算公式。A6单元格为筹资净额(资本流入),输入公式“A6=A3*(1-D3)”。B6:F6

单元格区域分别是各年的税后利息支出额和还本额,即资本的流出量。第1-4年为各年支出的税后利息,第5年

除了税后利息支出外,还包括一次性还本额。在B6单元格中输入公式“=-$A$3*$B$3*(1-$E$3)”,接着利用填充柄将公式横向填充至E

6单元格。在F6单元格中输入公式“=-$A$3*$B$3*(1-$E$3)-A3”。

2026/1/14439.2.1个别资本成本步骤3：最后利用IRR函数计算长期借款的资本成本。在F

2单元格中输入公式“=IRR(A6:F6)”,结果为7.62%。

2026/1/14449.2.1个别资本成本方法二：【操作步骤】：步骤1：显示“开发工具”，单击“文件”按钮，在下拉菜单中

单击“选项”按钮，在对话框中

单击“自定义功能区”按钮，在最右边一栏

可以看到“开发工具”，单击“确定”按钮。2026/1/14459.2.1个别资本成本步骤2：在图9.29中点击“开发工具”下的“插入”工具按钮，插入“滚动条窗体控件”2026/1/14469.2.1个别资本成本步骤3：将“滚动条窗体控件”移到年利率B2单元格附近，单击鼠标右键，选择设置控键格式，在控制标签页进行设置。2026/1/14479.2.1个别资本成本步骤4：

在单元格B3中，输入公式B4/100,格式设置为“百分比”（ctrl+1），保留两位小数。这样就可以通过滚动条调节利率来查看资本成本的变化。如图9.32所示：步骤5：

同理设置其他单元格格式2026/1/14489.2.2权益资本成本

企业总资本往往是由多种筹资方式形成的,不同筹资方式的资本成本存在差异。企业为了更好地进行筹资和投资决策,需计算综合资本成本(也称加权平均资本成本)。综合资本成本是对各种个别资本成本按所占资本比重进行加权平均所得到的,故称为加权平均资本成本。2026/1/14499.2.2权益资本成本例题：A集团你筹资2，000万元，其中银行借款300万，筹资费用率为2%；债券200万，筹资费用率为3%；优先股300万，筹资费用率为5%；普通股700万，筹资费用率为6%，普通股发行价格为10万元；留存收益500万，所得税税率为25%，企业通过调节利率来控制其资本成本。要求：改变银行存款、债券、优先股、普通股、留存收益利率对综合资本成本的影响？【操作步骤】：步骤1：建立“综合资本成本”工作表，如图9.35所示。2026/1/14509.2.2权益资本成本步骤2：显示“开发工具”，单击“文件”按钮，在下拉菜单里面单击“选项”按钮，在对话框里单击“自定义功能区”按钮，在右边的两栏里面的右边一栏可以看到“开发工具”，单击“确定”按钮。（详情参照例题9.6）步骤3：点击“开发工具”下的“插入”工具按钮，插入“滚动条窗体控件”。步骤4：

将“滚动条窗体控件”移到E3单元格中，单击鼠标右键，选择设置控键格式，在控制标签页进行设置，或单击“开发工具”选项卡中的“属性”，也会弹出“设置控件格式”2026/1/14519.2.2权益资本成本步骤5：

在控制页标签中设置2026/1/14529.2.2权益资本成本步骤6：

使用同样的方法，将“调节值”列的其他单元格输入。步骤7：

在单元格D3中，输入公式E3/100，格式设置为“百分比”。步骤8：

同理，设置其他单元数值2026/1/14539.2.2