财务信息处理与分析06Excel在筹资管理中的应用

Excel在筹资管理中的应用06项目六了解并掌握销售百分比法资金需要量预测方法及应用。了解并掌握资本成本及其计算。了解并掌握最优资本结构的概念和运用。能够运用Excel设计销售百分比法资金需要量预测模型。能够运用Excel设计各借款筹资决策模型。能够运用Excel设计资本成本计算模型。能够运用Excel设计最优资本结构决策模型，并进行最优资本结构决策。李海峰运用Excel为蓝天股份公司实现了会计电算化和工资管理系统的设计，以及固定资产管理和账务处理的信息化，很好地提高了工作效率，受到了同事们的好评和领导的赞赏。为此，李海峰决定尝试利用Excel进行各个业务领域和业务环节的管理与应用，包括筹资管理、投资管理和利润规划与财务分析等环节，以更好地实现账务处理和财务管理的信息化处理与分析。CONTENTS任务6.1财务预测是财务管理的一个重要环节，包括资金需要量预测、成本费用预测、收入利润预测等。资金需要量预测是财务预测的重要内容，也是企业制定融资计划的基础。资金是企业生产经营中各种资产的货币表现。拥有一定和必要数量的资金，是企业进行生产经营活动的必要条件。资金需要量预测，就是对企业未来组织生产经营活动的资金需要量进行估计、分析和判断。合理预测资金需要量，可使企业避免资金浪费或资金不足。科学地进行资金预测，可以为企业生产经营活动的开展测定相应的资金需要量，也可以更好地为经营决策、降低资本成本和提高资金利用率创造有利条件。资金需要量预测的一般步骤:（1）销售预测销售预测是企业财务预测的起点。企业资金需要量的预测也是以销售预测为基础进行的。（2）外部资金需要量分析，估计需要的资产通过历史数据分析，可以得出资产与销售量之间的函数关系。根据两者之间的函数关系，又可以预测所需资产的总量，同样可以预测负债的自发增长数量。（3）估计收入、费用和留存收益利用收入、费用与销售额之间的函数关系，可以根据销售额估计收入和费用，进而确定净利润。而净利润和股利政策决定留存收益中可以提供的资金数额。根据所需追加的资金需要量，确定企业外部融资额。根据预计的资产总量，减去已有的资金来源、负债的自发增长数额、内部提供的留存收益数额，就可以得出所应追加的资金需要量。资金需要量预测一般采用销售百分比法和线性回归分析法，也可以利用Excel中的方案管理器进行资金需要量预测。销售百分比法是指在明确销售与预计资产、负债、未分配利润关系的基础上，根据销售的变动水平计算计划期需要追加的外部资金量的一种定量方法。1销售百分比法预测假设前提企业某些资产和负债类项目与销售收入之间存在一个稳定的百分比关系。2销售百分比法预测基本原理某项资产（或负债）预测值预测销售收入资产（负债）占销售收入百分比3销售百分比法预测应用依据销售百分比法预测的依据是以下会计恒等式：资产负债所有者权益资产为企业资金占用，而负债和所有者权益是资金的来源。当“预计资产预计负债预计所有者权益”时，表明企业资金占用多于资金来源，因此企业需要从外部追加资金，而“需追加的资金预计资产预计负债预计所有者权益”。或者从增量的角度考虑，可以得出如下关系式：外部资金需要量增加的资产增加的负债增加的留存收益4计算外部资金需要量的基本步骤步骤1预测计划期销售额，并计算销售变动率或者销售变动额（增量收入）。步骤2区分变动性敏感项目（随销售收入变动而呈同比率变动的项目）和非变动性敏感项目。根据基期的资产负债表确定随销售变动的资产项目和资产变动总额或总比例。通常资产变动性敏感项目有货币资金、应收账款、存货等流动性资产；固定资产、对外投资等固定性资产以及长期投资、无形资产等与销售变动无关。根据基期的资产负债表确定随销售变动的负债项目和负债变动总额或总比例。通常负债变动性敏感项目有一般应付款、其他应付款等，长期负债不变。变动性项目的销售百分率基期变动性资产或负债/基期销售收入步骤3根据企业的折旧方法计提折旧或摊销，并考虑更新改造支出计划，确定可供企业使用的内部周转资金。步骤4根据利润表和预计销售变动情况，预测计划期的利润和销售净利率，根据股利支付政策确定预计留存收益。步骤5计算需追加的外部筹资额。外部资金需要量增加的资产增加的负债增加的留存收益预计内部周转资金增加的资产增量收入基期变动资产占基期销售额的百分比增加的负债增量收入基期变动负债占基期销售额的百分比增加的留存收益预计销售收入销售净利率留存收益率内部周转资金折旧摊销等非付现支出计算公式如下：即（以下公式省略“预计内部周转资金”）：外部资金需要量资产增加负债自然增加留存收益增加资产销售百分比新增销售额负债销售百分比新增销售额销售净利率预计销售额（1股利支付率）（资产销售百分比负债销售百分比）新增销售收入销售净利率预计销售额（1股利支付率）蓝天股份2017年年末资产负债表（简表）如下表所示。资产金额负债及所有者权益金额货币资金2,000应付账款22,000应收账款18,000预收账款9,400存货32,000长期负债4,200固定资产净值46,650实收资本23,050留存收益40,000资产总额98,650负债及所有者权益总额98,650蓝天股份2017年年末资产负债表（简表，单位：万元）蓝天股份2018年实现销售收入200,000万元，销售净利率5%，留存收益率20%，预计下年销售增长率可望实现15%。设计模型，计算公司为满足生产发展，下年所需资金量。步骤1新建工作簿，保存为“蓝天股份财务管理模型”，将Sheet1命名为“资金需要量预测（百分比法）”。步骤2导入蓝天股份2017年年末资产负债表简表，如下图所示。资产负债表简表步骤3在原工作表中第11行创建“销售百分比法敏感项目计算表”，可为敏感项目赋值为“1”，非敏感项目赋值为“2”，如下图所示。步骤4同样，在工作表中第20行创建“报告期外部资金需要量预测”计算表，并按资料输入基年销售收入、销售收入增长率、销售净利率和留存收益率相应数据，如下图所示。销售百分比法敏感项目百分比计算表外部资金需要量计算表步骤5设置敏感项目计算表公式。将C13:C16和F13:F17单元格区域属性设置为“百分比”，然后参照下表设置相应单元格公式，结果如下图所示。敏感项目计算表公式值单元格公式备注C13=IF(B13=1,B3/$A$22,N)向下填充至C16F13=IF(E13=1,D3/$A$22,N)向下填充至F17C18=SUM(C13:C16)F18=SUM(F13:F17)敏感项目百分比计算结果步骤6设置外部资金需要量预测表区域公式。参照下表设置预计资金需要量B23、预计留存增加额B24和外部融资额B25单元格中的数值计算公式，算出下年资金需要量预计数额，结果如下图所示。外部资金需要量预测表公式值单元格公式B23=(C18-F18)*A22*B22B24=A22*(1+B22)*C22*D22B25=B23-B24外部资金需要量预测计算结果任务6.2长期借款是企业筹资方式中一种重要而常见的形式。长期借款是企业向银行或其他非银行金融机构借入的使用期超过一年的借款，主要用于构建固定资产和满足长期占用流动资金的需要。长期借款按照本息偿还方式的不同，分为到期一次性还本付息和分期偿还两种类型。分期等额偿还又包括等额本息、等额本金、等额利息等基本方式。实务中也可以在基本方式基础上协商确定还款方式。到期一次性还本付息是指在借款到期时一次性偿还本金和利息。等额本息是指借款期内每期偿还相等数额的款项，是典型的年金运算。等额本金是指借款期内累计偿还固定的本金及按借款余额计算的利息。等额利息是指借款期内每期末按借款利率偿还固定的利息，到期一次偿还本金。本节以等额本息还款方式为基础，建立分期偿还借款模型，并把还款方式时限分为按年、按半年、按季、按月4种具体形式。蓝天股份拟向银行申请790万元外部所需资金量的商业贷款，贷款年利率为8.6%，借款期限为5年。接下来利用长期借款基本模型计算在不同还款方式、不同还款时点下每期偿还金额。在上述“蓝天股份财务管理模型”工作簿中新增工作表，并命名为“长期借款还款基本模型”，在工作表中建立“长期借款还款基本模型”，并输入借款金额、借款年利率、借款年限等基本数据。1建立基本数据区长期借款还款基本模型本节介绍利用表单控件建立还款时点组合框控件的方法。表单控件在“开发工具”选项卡下，所以应首先激活“开发工具”选项卡。2创建“还款时点”下拉框加载“开发工具”选项卡步骤1选择“文件”“选项”命令，打开“Excel选项”对话框，在左侧列表中选择“自定义功能区”，在其右侧“自定义功能区”下拉列表中选择“主选项卡”，然后在下方的列表框中勾选“开发工具”复选框，单击“确定”按钮退出，“开发工具”选项卡便显示在功能区，如右图所示。步骤2单击“开发工具”选项卡“控件”组中的“插入”“组合框（窗体控件）”按钮，在B6单元格位置单击，插入“组合框”控件，然后调整其大小和位置，如右图所示。插入组合框步骤3右键单击组合框控件，在弹出的快捷菜单中选择“设置控件格式”，打开“设置控件格式”对话框，并切换到“控制”选项卡。步骤4在“数据源区域”编辑框中输入$D$3:$D$4，即单击控件时产生的下拉列表显示的数据所在单元格区域，在“单元格链接”编辑框中输入“$B$6”，即控件产生的结果（所选数据在数据源中的顺序号）存放的单元格；在“下拉显示项数”编辑框中输入“2”，然后单击“确定”按钮。“设置控件格式”对话框参照“还款时点”下拉框的创建，用同样的方法创建“还款方式”组合框控件。此处需要在“数据源区域”编辑框中输入“$E$3:$E$6”；在“单元格链接”编辑框中输入“$B$7”；在“下拉显示项数”编辑框中输入“4”，最后单击“确定”按钮。3创建“还款方式”下拉框接下来进行每年还款次数、还款利率、总还款期数、每期还款额和总还款额所在单元格B8、B9、B10、B11和B12公式的设置。4编辑公式上述基本模型建好之后，在实际应用中，用户可根据实际情况输入或改变借款金额、借款年利率、借款年限，选择还款时点等数据，模型会自动计算出每期还款额和总还款额，供用户决策时使用。单元格公式每年还款次数B8=IF(B7=1,1,IF(B7=2,2,IF(B7=3,4,IF(B7=4,12,0)还款利率B9=IF(B7=1,B4,IF(B7=2,B4/2,IF(B7=3,B4/4,IF(B7=4,B4/12,B4)总还款期数B10=B5*B8每期还款额B11=IF(B6=2,PMT(B9,B10,-B3),PMT(B9,B10,-B3,1)总还款额B12=B10*B11长期借款还款基本模型公式设置实际工作中，长期借款分析中的本金、利率、还款期限等各因素是相互影响的，其中每一项因素的变化最终都会对长期借款决策产生一定的影响。如借款期限的长短会影响借款利率，借款利率的变动又对长期借款分析产生较大的影响。故财务人员有时希望控制两个变量，观察两个变量在不同组合下的结果。我们可以利用前述模拟运算表进行长期借款还款双因素分析模型的设计。在“长期借款还款基本模型”的基础上，加上“到期还本付息”还款方式，以“借款金额”和“借款年限”两个变量的变化对“每期还款额”的影响为例，介绍长期借款双因素分析模型的设计。在上述“蓝天股份财务管理模型”工作簿中新增一张工作表，将其命名为“长期借款还款双因素分析模型”，并输入相应基础数据。1建立基本数据区长期借款还款双因素分析模型借款金额、借款年利率和借款年限数据由用户输入，还款时点B6和还款方式B7使用窗体组合框创建。“长期借款还款双因素分析模型”中每年还款次数、还款利率、总还款期数、每期还款额、总还款额所在单元格B8、B9、B10、B11、B12中的公式，可参照表6-5进行设置。2公式设置长期借款还款双因素分析模型公式设置单元格公式每年还款次数B8=IF(B7=1,1,IF(B7=2,2,IF(B7=3,4,IF(B7=4,12,0)还款利率B9=IF(B7=1,B4,IF(B7=2,B4/2,IF(B7=3,B4/4,IF(B7=4,B4/12,B4)总还款期数B1