用EXCEL建立财务模型
B 部分 应用B 部分介绍了大量的应用,介绍方式是按照先概述理论,然后介绍这些理论在金融模型中的应用。每个模型都遵循 A 部分介绍的设计步骤,以便提供学习和进一步发展的模板。B 部分的章节安排如下:6 业绩分析7 现金流8 预测模型9 财务预测10 方差分析11 收支平衡分析12 投资组合分析13 资本成本14 债券15 投资分析16 风险分析17 折旧18 租赁19 公司评估20 最优化21 决策树22 风险管理23 模型一览表第 6 章:业绩分析本章介绍用于考察公司经营业绩与理解财务信息的模型.我们从对公众公开的公司年度财务报告开始,这些年度报告包括:董事会报告-前一会计时期经营状况的定性报告审计报告-关于经营业绩的第三方报告利润表-标示收益和成本状况的报表资产负债表-标示公司资产与所欠银行、政府债务的简要报表现金流量表-标示由于经营活动或其他原因所产生的现金流以及现金的使用情况账目附注-根据英国公司法或海外公司法的要求,对上述报表所做出的细节解释。报表的细节取决于公司所在地的相关公司管理法律要求。私人公司(股权不公开)往往只能提供很少的经营信息。在英国的确存在减少信息披露的倾向,甚至第三方的审计报告都可以免除。公司制定年度报告和账目所依据的会计标准、会计惯例也必须在会计报告中进行详细说明。会计标准与会计惯例十分重要,因为由于会计标准的不同,不同国家不同地区的公司之间往往很难进行比较。例如,会计利润会由于人为延长资产折旧期或者改变存货估价方法而得到增加。公司业绩分析需要对可以获得的信息进行标准化处理,原始数据只有经过标准化处理才能提供关于公司经营业绩的有效信息。公司业绩分析师需要站在全行业的角度评审公司业绩,股东需要了解他们的投资是否安全并且能够得到增值,其他一些股东可能还想获得关于组织发展方面的更多信息。然而,信息往往被分散于报表的各个部分,例如,资产负债表中往往都有应收账款(accounts receivable)总额或存货( inventory)总额项目,但所有关于这些账目的细节说明都在报告背后予以标注。只有全面仔细地阅读整个报表才能完全理解报表中 所罗列的数字, 。大多数人并不善于分析海量数据从而很难在较短的时间内理解数据间的关系。如果你想借钱给某一公司或者想了解该公司的经营状况,你需要知道该公司的经营风险是否越来越大。一个办法就是将公司的所有账目以一种标准化的方法进行“展开” (spread accounts)以全面理解所有数据。过程如下:识别风险来源对风险进行定性描述通过展开账目、检查经营结果并分析经营状况减轻结果-风险是否正在增加标准化的账目有助于从结果中寻找异常数字根据绝对数值的变动考察经营发展趋势计算财务比率和其他比率,举例来说,利润率-利润有可能处于上升状态,但是利润率将告诉我们每一元销售收入是否正在带来更多的利润。清晰地显示出需要进一步深入分析或研究的部分基本信息将为现金流量分析、公司发展趋势预测和公司价值评估提供进一步的数据。为了便于读者理解,我们假定现在有这样一个虚构的公司“技术销售公司”.操作的第一步是建立一个包含利润表和资产负债表两个清单的模型.此模型收集 5 年的经营数据以便于判断经营趋势。如果你将借出一笔为期 5 年的款项,那么你将需要两年以上的数据以便于分析。现金流量和一些比例可以从这些数据信息中计算出来。这一切都基于 Application_Template.应用模板可以使许多基本操作任务自动完成。本章所应用的模型模板称为 Financial_Analysis6.1 利润和损失利润表(或称为收益表)的结构沿袭传统(见图 6.1),但为了便于理解,此处做了一些简化处理。当然可以通过增加数据将表格结构复杂化以获得更多的细节数据。图 6-1 收支平衡表 (原书 103 页 fig6.1)日期基于名为“Enddate” 的菜单单元格 C20,从最近的日期开始,使用 EDATE 函数向前回溯计算出其他日期,以保证时间准确。输入单元格用蓝色标记,总数用粗体绿色标记。根据现金流量准则,每一列各单元格数据累加得到最终总量结果。现金收入为正值,现金支出为负值。为使表格易于理解,所有数字使用会计格式编排,这样数字“0”一律显示为“-”。Excel 不能直接从一个表格到另一个表格的计算得出最终结果。需要花费一些精力用于理解每个单元格数据的含义。此模型使用数字编号系统,例如,当涉及到此表格中的数据时, “P”代表利润表, “B”代表资产负债表。表 6-1 清晰标示出了利润水平 利润水平 表 6.1 指标 别称毛利润 毛边际净经营利润 息税前利润或收益(EBIT,PBIT)财务项目扣除后利润税前利润 税前收益税后净利润 税后利润或收益(EAT,PAT)未分配收益 未分配利润出于完备性考虑,百分比数据也在右侧给出。 (见图 6.2)图 6-2 收支平衡表-百分比(原书 104 页,fig 6.2)6.2 资产负债表年末资产负债表将全面展示公司资产、负债的整体情况。根据国际惯例,资产方划分为流动资产和固定资产(见图 6-3) ,负债方划分为流动负债、长期负债和所有者权益。所有者权益又进一步划分为股本和留存收益(见图 6-4) 。当然,可以在图 6.4 的基础上增加优先股等栏目以便更加详细地描述资产负债表。图 6-3 资产 (原书 104 页 ,fig 6.3) 每年的总资产额已经给出并提供一个相应的百分比值.这里不需要任何程序,Excel 的作用在于保证计算的准确。在整个表格中数字、颜色、行列和整体外观的格式是统一的。同样的,所有的报表都使用名为“SetUpSheet”的宏插入传统的页眉、页脚。图 6-4 负债 (原书 105 页,fig 6.4) 此表格中的“CheckSum”一项用来核对资产与负债是否相等。 (见图 6-4) 。如果资产与负债不相等,在单元格 E43 中将显示警告信息。,043:ErosMISUIF利润表和资产负债表构成此模型的基本数据来源。在上表中,可以顺着行观察发展趋势。百分比图示会有所帮助。但是最终需要计算一些比率以更加透彻地了解表格所反映的经济事实。使用 Excel 可以快捷地得出结果,从而避免了使用简易计算器和财务计算器的繁冗计算过程。6.3 比率本节目的在于简要勾勒出财务分析人士常用的几个比率,以及这些比率的作用。我们不对例子中的账目做深入分析。某些公司之所以比其他公司业绩出色可能是由于更好的利用了各种资源并实施了行之有效的管理,也可能完全是出于好运气。财务分析力图通过比率分析专注于经营结果而忽略运气因素的作用。图 6-5 是一个解释公司经营结果的图示。图 6-5 风险 处理程序环境产业公司经营风险盈利能力现金流财务风险财务结构商业风险经营周期信用风险管理 第一步,明确调查目的。然后分析公司运营的社会环境。社会环境包括超出公司控制能力的各种因素: 社会发展趋势 技术发展水平 经济周期、经济增长与下滑的程度 影响公司的政治变革 某些商业道德观念的变化难点在于确定公司能否赢利并在竞争中幸存下来。所以接下来需要考察公司所在产业的整体情况及其竞争能力。一些产业可以构筑进入壁垒以确保其在一段时间内获得稳定盈利。一些产业可能凭借专利或品牌优势在一段时间内获得超额利润。在过去的十年里类似于微软的企业迅猛成长,它们所凭借的是其在产业中的主导地位,从而在几乎没有竞争对手的情况下快速发展。在考虑了宏观经济和产业背景因素之后,我们就可以更加高屋建瓴地考察公司业绩。比率分析有助于这一考察过程,但是以下几点需要注意: 没有绝对正确或错误的答案 绝对数值无关紧要 不同市场参与者之间的比率差异极大,比如零售商、制造商和服务公司之间。 需要在充分考虑当前经济情况的前提下考察发展趋势 比率分析是一种事后分析,整理账目和报告可能要花费几个月的时间 与同类企业进行对比分析有助于发现企业的优势与不足以下三点需特别注意 经营风险-经营周期(见图 6.6) 执行风险-盈利能力 财务风险-财务结构这三种风险决定公司产出的数量与质量,而生产什么、生产多少则是经理层需要决定的问题。图 6-6 经营周期 现金被用于购买原材料,原材料进而被投入生产过程直至产出最终产品。 (见图 6.6)公司卖出产品,出具发票,然后等待客户付款。这一周期的周转速度是衡量公司经理层管理能力的重要指标之一。如果产品不能买出,将造成产品库存积压。此时只有依赖银行信贷融资或股权融资才能使生产得以维持。这就是商业风险。经营风险指赢利能力,它往往被等同于现金流。公司需要利润以进行扩大再生产。从长期来看,赢利能力低下意味着公司失去竞争力,走向破产或被其他公司取而代之。如果发现有利润而无现金,那么就需要检查所采用的会计标准,或者需要考虑账目被伪造的可能性。例如,记入资产负债表的费用,比如研发费用,意味着现金已经被花费了,但是却在一段时间之后才会被载入利润表。这种记账方法取得了账面上短期内的利润增加。财务风险涉及到资产负债表的结构以及公司融资的来源。股权融资与债务融资的核心区别在于公司对于是否发放股息握有主动权,但是银行贷款却必须还本付息。随着债务融资比例的上升,公司的利息负担随之加重,财务风险同时增加。本节所述各种比率描述了股东所持有的公司资产比率,以及公司财务负担程度。经营周期现金应收帐款 原材料生产过程最终产品图 6-7 比率 (原书 108 页,fig 6.7) 杜邦比率(核心比率)与其费时费力地逐个计算各种财务比率,不如将注意力集中到核心比率,因为核心比率决定了权益收益率。权益收益率被认为是最为关键的比率,因为此比率直接标示股东的收益水平。权益收益率等于税后净利润/所有者权益,权益收益率可以被分解为以下三个比率:销售净利率(税后净利润/销售收入) 衡量赢利能力 P24/P10资产周转率(销售收入/总资产) 经营效率 P10/P24权益乘数(总资产/所有者权益) 财务结构 P23/P39上述三个比率彼此相乘就得到权益收益率(净资产收益率)税后净利润/所有者权益=销售收入 /总资产*总资产/所有者权益*税后净利润/销售收入上述三个比率共同构成权益收益率。这一比率最近持续下降。模型使用函数判断是否最近的一年(98 年)的业绩好过前两年(96.97) ,如果是,则显示“Better”,如果只比其中的某一年好,则显示“OK” ,如果比前两年都差,则显示 “Worse”.单元格 L11 所应用的公式是:=IF(K11=0,N/A,IF(J11=0, N/A,IF(AND(L11/K111,L11/J111), Better,IF (OR (L11/K111,L11/J111), OKWorse) ) ) )存货周转天数、应收账款周转天数和融资缺口的逻辑与上述公式正好相反.如果周转天数增加或者是缺口增大,那就意味着公司必须寻找更多的资金渠道为生产融资.融资缺口在前两个时期(96,97)增大,所以单元格公式显示结果为“Worse” 。在每一个公式中,由于数字“0”所可能导致的错误都被 IF 语句的使用加以规避。单元格 L11 中的公式为:=IF(Income!M100,Cashflow!M12/Income!M10*100,0)7.3 自由现金流对于现金流有诸多定义,更深入些的定义称为“自由现金流” 。这是可以用于清偿债务和支付股利的现金流。它的定义如下:经营利润(NOP)+折旧/摊销/非现金项目利息、税收、折旧、摊销前的收益(EBITDA)+/-净营运资产的变化额净经营现金流(NOCF)-固定资产支出/销售收益-税收所导致的净现金流出自由现金流以下是 Financial_Analysis 模型中的 Free_Cash_Flows 表。它包括现金流量表中的数据。 (见图 7-3) 。由于巨量的厂房设备投资,自由现金流出超过了 5 百万。图 7-3 自由现金流 (原书 120 页,fig7.3) 7.4 补仓率自由现金流量表包含贷款补仓.年度租金取决于期限,名义利率,资产价值和期末价值(未来价值) 。这个模型计算每年的租金额以及不同现金流需要的补仓数额(见图 7.4) 。这里更需要关注的是现金而不是贷款分摊及利息。例如,一个租赁公司想要了解现金流的敏感度和不确定性,这个方法可以突出风险所在之处。在这个例子中,2 百万的贷款是有风险的,并且并没有被及时补仓。单元格 F29 使用 PMT 函数计算年度支付额:=IF(SUM(F23 :F26 )1,M11/K111), Better,IF(OR(M11/L111,M11/K111) , OK, Worse) ) ) )图 9-9 预测比率 (原书 140 页,fig、9、9) 9.3 其他方法除了本章所介绍的预测方法,还有其他更复杂的一些方法可以用来进行财务报表预测。其中之一就是使用比率和实际数据而非采用百分比,如表 9.2 所示。现金和短期债务再一次被用来平衡资产负债表。虽然现金作为流动资产处理,模型可以通过迭代的方式使得负的现金能够加到短期债务中。为了使模型结构尽量作到简洁明了,这种方法没有被例子模型采用。表 9-2 可选预测方法 项目 计算销售收入 使用销售增长率计算销售成本 预测毛边际利润折旧 使用上一年折旧率净经营利润(NOP)利息支付 使用年度初期债务利率现金利息收入 预测利率额外收入项目 包含来自预测的实际项目税前利润(EBT) 总额税收 Forecast 中的税收支付率税后利润(EAT) 总额股利 Forecast 中的股利支付率留存收益(RE)资产负债表资产和负债减少产生的现金和可转让证券流动资产应收账款 应收账款/预期销售额存货 存货/Forecast 中的销售成本固定资产 固定资产/预期销售额按成本价格折旧 Forecast 中的期初固定资产折旧率净固定资产无形资产 相关投入总资产流动负债 基于良好销售业绩的信贷期限税收债务 债务/Forecast 中的销售额股本/所有者权益 Forecast 中的投入增长或下降累计留存收益 将利润表中的留存收益转入资产负债表总负债和所有者权益9.4 财务分析使用预测期固定的现金流进行定性分析日益受到重视。根据作者的经验,有可能表格中的预测结果是现实中根本不可能达到的。当然,公司自然希望它的财富能大幅增长。在这个应用程序中还有两个表格协助分析,它们是操作概要“Management_Summary”和操作分析“ Management_Analysis” 第一个表格查找其他的表格中的重要的行,然后把损益计算表,资产负债表,现金流和比率的概要内容放在一起(见图.) 。右侧的测试部分试图指出在预测的第一年管理层应该关注的问题。图 9-10 管理概要 (原书 142 页,fig 9.10)Management_Analysis 从四个预测表的第 79 行至 214 行截取数据,使用“组合控制框”和 OFFSET 函数构建一个动态曲线图。这使得你可以在三个曲线图中分析同一条线。 有三个数列的线形图:历史的,预测的,和外推五年的历史趋势线 历史和预测数据的柱状图 第一时期等于 100 时的各种因素柱状图图 9.11 是历史营业净现金流和预测净经营现金流的一个例子。这个方法清晰地显示在第四年现金减少。预测值在线形趋势线之上,因此有利于进一步调查。Figure.9-11 动态图 (原书 143 页,fig 9.11)Figure.9-12 柱状图 (原书 143 页,fig 9.12)这种方法的优点在于你可以观察每一条曲线并且可以立即看出历史和未来的联系。你可以改变“Forecast sheet”中的输入数据以观察各种比率或现金流的变动情况。在这个例子中,净经营现金流是变量,因此下一步将考察运营周期比率。诸如应付账款周转天数,应收账款周转天数,然后是融资需求和借款比率。第二组柱状图包含了一个数列,但是个别点的格式不同(见图.) 。预测的模式可以通过下列方法产生:选中数据点,右击,然后格式化模式。9.5 小结通过使用账目模型 Financial_Analysis 中的原始数据,本章介绍了模型的扩展功能以进行账目预测。使用销售百分比法并用现金或借款弥补缺口。账目可以向前描绘以生成过去和未来的混合报表。表格将尽可能地进行自我检查并提供管理信息。更多的管理分析基于总结报告和模型中动态数据表。10.方差分析方差分析将标准业绩或预算业绩与实际业绩进行比较。前一章主要关注财务预测与设计财务报表,本章则主要关注需要引起管理层重视的重要方差。一般而言,数据只能在会计期末进行处理,因此当管理层采取行动时,所依据的信息已经过时。通过数据处理计算方差,可以最大限度的保证采取行动的优越性以及缩小相应的误差。这种方法的优点在于: 有助于固定成本和可变成本控制 聚焦于存在问题的领域以便管理 协助制定计划及下一年的预算 协助决策制定Excel 一般被用来作为预算和其他会计任务的相加工具。单元格的结构设计和公式的运用可以高效处理纵横交错的数据。本章涉及到两个文件,它们能提供更多的分析技巧:Cash_Flow 利润表、资产负债表、现金流和比率的简单预算Cash_Flow_Budget 如上分层罗列,但增添了实际业绩,然后进行比较10.1 现金流预算两个文件都使用应用模板作为基础,并且它们也是 Financial_Analysis 的扩展。这一点保证了我们可以重复调用在前面一些章节里为了加速软件开发而设置的大块的编码。这个软件包含了 12 个月的历史和预测数据,而不再是 5 年的数据。由此,表格的复杂性大幅增加,这里所有的表格都遵循基本相同的版面布局: 菜单 利润表 资产负债表 比率 现金流 管理分析 管理概要图 10.1 中展示了基本现金流模型的程序菜单图 10-1 菜单 (原书 146 页, fig10.1)预测表格套用 Financial_Analysis 的格式,并使用前一年最后一个时期作为销售增长的基础。利润表和资产负债表里显示了最近会计时期的输入数据,借助于预测的百分比数据,模型生成了预测表格(见图 0.2) 。图 10-2 预测 (原书 147 页 ,fig、10、2)在每个月里,现金用来平衡资产负债表。现金流使用同样的框架,并且全篇套用同一格式。现金流来自利润表和资产负债表,因此它必须平衡。为了与正式的账目系统进行比较,账目报表也提供了控制总量。比率使用 30 天作为基础而非 360 天,并且有一个计算总量的列以便比较全年的比率。使用已有的程序意味着缩短程序的开发时间并且减少错误。以操作小结的形式提供了一个总结,并在操作分析表格中进行图表分析。 (见 Figure 10.3,和 Figure 10.4)图 10- 小结(原书 148 页, fig 10.3图 10- 图表(原书 148 页 ,fig 10.4)10.月度现金模型Cash_Flow_Budget 模型通过增加实际业绩结果和方差 , 对简单的预算模型进行了扩展。正如我们在前面章节所强调的那样,简单的预测模型用途有限,而能够对实际业绩与预期业绩进行比较的应用程序才会对经理层做出决策大有裨益。通过逐步输入全年实际的经营业绩数据,我们就能够将结果与预算进行比较。利润表的制作方法如下: 复制预测表格将其粘贴到新表格 将新表格更名,如Income 找到并且替换预测的参考输入为实际的数据。这包括:Fincome, Fbalance, Fratios和 Fcash 更新标题,改变配色方案 插入 IF 语句以保证如果没有实际数据则无任何显示值 对资产负债表、比率和现金流重复上述过程 手动更改图表上的引用, 因为系列的引用不能随着表格之间的复制而自动更新 使用格式化单元格“Format Cell”来改变单元格格式以设置数字,彩色代码和保护。图 10.清晰地展示了输入数据和总数。图 10-5 利润表 (原书 149 页 ,fig10.5)方差表的建立方式和实际-预算的形式具有相同的句法结构。这在 VCashflow 表格中的单元格 J10 有具体体现。如果在相关的 Actual Cashflow 表格中的 J10 单元格中没有数据,则 VCashflow 表格中的单元格 J10 将显示数值“0” 。=IF(AIncome!J10=0,0,ACashFlow!J10-FCashflow!J10)菜单系统被进一步扩展以包括预算间隔和月份号(见图 10.6) 。这样做的目的在于使模型可以处理月度、季度、双季度和年度数据。月份号记录采用实际数值的最后一个时期。图 10-6 预算菜单 (原书 150 页,fig10.6)10.3 “Flash”报告和制图 如果没有一份对实际结果与预算结果进行详细对比的 Flash”报告,管理报告将是不完整的。见(图 10.7) 。此表格以分栏表格的方式显示预算表和实际报表中的数据。使用者利用表格顶部的下拉列表选择相关月份,表格底部会根据不同月份的数据绘制相应的图表(见图 10.8)Figure 10-7 “Flash”报告(原书 151 页,fig10.7)百分比一列产生了一个问题,那就是负的数字可以产生错误的结果。如果实际或预算数值为负数,K13 单元格中的公式将乘以 “-1”以避免错误:=IF(F130,D17/$G17,0)*100图 11-6 收支平衡点的销售额(第 158 页 Fig 11.6)图 11-7 产品组合 (第 158 页 Fig 11.7)贡献率为 55.44%时的收支平衡点销售额为: _30,_Re 65arg()12FixedCostBreakEvnuontrbMinRi收支平衡点的销售额乘以每个产品的销售百分比,就可以得到每个产品的收支平衡点销售额,再除以每件产品的售价就可计算出需要的产品数量。11、2、 营业杠杆该模型也在杠杆作用表格中计算出了营业杠杆。营业杠杆被定义为息税前盈利相对于销售量的弹性。这种弹性来自于高水平的固定成本,使公司依靠提高销售量来抵消固定成本。这种风险通常称为经营风险,受下列因素支配: 宏观因素,例如经济和政治气候。 行业因素,例如水泥制造和汽车生产这样的重工业中的固定资产和成本要求。 公司因素,由企业本身的管理行为和策略决定,具体例如产品的成本结构。该分析需要输入更多的数据(见图 11-8) 。销售和盈利信息从模型表格中可以查到。营业杠杆的公式为:%__ChangeiDbitOperatingLverSls图 11-8 杠杆(第 159 页 Fig 11.8)在每种情况下收支平衡点不会发生变化,因为它由固定成本决定。如果销售下降,较高的营业杠杆作用会使盈利以更快的速度下降。11、3、 财务杠杆财务杠杆是每股收益相对于息税前盈利的弹性。它直接受到组织结构和债务融资水平的影响。公式为: %__ChangeiEPSFinaclLevrBIT杠杆作用表格的债务信息和股票数目是计算财务杠杆的原始数据(见图 11-9) 。图 11-9 财务杠杆(第 160 页 Fig 11.9)在第一年里,随着息税前盈利每变化一个单位,每股盈利会增长 1.14 倍。每股收益随着盈利增长而增长,反之亦然。11、4、综合杠杆作用该模型也计算出了财务杠杆作用和营业杠杆作用的综合效应。我们将两个杠杆综合起来计算销售量对每股收益的影响,计算公式如下: %__ChangeiEPSCombinedLvrels也可以写为:Combined_Leverage=Operating_Leverage*Financial_Leverage为了清楚地展示这些结果,这三种形式的杠杆作用在下面用简单的线图给出(见图11.10) 。图 11-10 综合杠杆(第 161 页 Fig 11.10)成本和利润每年都会改变,因此上述比率亦不固定。第一年利润率的增长并不具备持续性,销售额和每股收益在未来的时间里均会下降。11、5、 小结收支平衡点分析试图让读者了解成本的性质和影响。本章中使用的模型既分析了单一产品,也计算了多个产品的收支平衡点。第二部分给出了营业、财务杠杆作用以及综合杠杆作用,可以作为更进一步管理的手段。第 12 章 投资组合分析根据分散化投资原理和资本成本知识,本章和下一章将建立基本的财务理论。本章讨论马柯威茨(Markowitz)的资产组合理论中所论述的分散化投资原理,这个理论最初来自马柯威茨在 1952 年发表的一篇文章,它揭示了构建投资组合时,风险是如何发生变化的。有关股票的两个非常重要的变量是收益和波动率,波动率由股票的标准差来衡量。一个理性的投资者想要在风险一定的情况下赚取尽可能多的利润,则需要了解为了最大化收益,各种证券在资产组合中所占的比重。与随机持有一定数量的股票来有效地减少风险的方法相比,这个理论建立了一种更系统的资产配置方法。在 Portfolio 文件中,以下部分阐述了资产配置的重要理论。 12.1 公式投资组合的假设条件包括: 投资者都是风险回避者,高风险要求高收益; 投资者是理性的且仅以风险和收益作为决策依据; 投资者想要赚钱而不是赔钱。根据历史数据,如果你想要得到两种证券的最优组合,则可以购买风险小或者没有风险的政府债券以及购买某几种股票,无论如何,两种股票的组合可以增加收益, “管理”风险。Portfolio 文件包括在 Model 表上的所有工作区,对各种权重的资产组合计算其风险和收益。通过变换权重和利用历史数据,我们可以找到收益最大和风险最小的资产组合方式。假设最初的权重是 50:50。表 12-1 提供了每种证券的月收益。在实际计算时,显然数据越多越好,但是这里我们仅仅是说明这个方法,所以表中的这些数据已经足够演示这个分析技术的应用。E 列代表的是两种证券的加权收益,E13 单元中的公式是:=(C13*Weighting_A+D13*Weighting_B)/100图 12-1 投资组合输入(第 164 页 fig、12、1)第 26 行显示的是平均收益。投资组合的期望收益公式是:WeightingA*ReturnA+Weighting B*ReturnB投资组合的风险或波动率可以用标准差来衡量,在表上可以通过两种方法实现。利用标准差函数和相关性函数,然后用单元 30 中的公式计算:=SQRT(Weighting_A)2)*(SDev_A)2+(Weighting_B)2)*(SDev_B)2+2*Weighting_A*SDev_A*Weighting_B*SDev_B*Correlation)/100单元格 C28 和单元格 D28 中显示标准差的值,它使用函数 SDEVP 来计算所有数据而不是只计算一个数据样品:=STDEV(C13:C24)相关性可以用单元格 C30 中的函数 CORREL 计算:=CORREL(C13:C24,D13:D24)证券 A 和证券 B 的收益在图 12.2 中显示图 12-2 A 和 B 的收益(第 165 页,fig、12、2)如果你要了解所有的统计函数,你总是可以打开进入 Excel 菜单“帮助(H ) ”,选择一个统计函数,然后点击顶部的这是什么?(W) ,你就可以看到所有相关函数的列表,并且能够进一步查看每一个函数。底部有一个工作区,它是用来计算标准差,协方差,相关性和组合风险(见图 12-3) 。图 12-3 工作区(第 166 页, fig、12、3)协方差公式是: 2)(1Ani121 行显示了股票 A 和 B 各自给定数据的(Result-Mean)总和,然后分别除以数据的个数,126 行的标准差是这个结果的平方根,可以将其与 SDEVP 函数计算的结果相比较。协方差表示的是两个数据集合之间的联系并且在计算相关系数时用到。公式为: ni AveragBveragACo1 )(varA=某段时间股票 A 的收益B=某段时间股票 B 的收益n=时期数Average A=股票 A 的平均收益Average B=股票 B 的平均收益相关系数为: SDevBAiancConorelati*rSDevA=股票 A 的标准差(由上面计算得出)SDevB=股票 B 的标准差这些因子可以用来计算投资组合的风险,见单元格 136,单元格 D137 给出投资组合的标准差为 2.14。这个模型在页面的顶部给出了一个管理小结,用来显示持有两个证券的风险递减的情况。12.2 最优组合问题的下一阶段是了解当每个证券的持有比率发生变化时,收益与风险是怎样变化的。为了给出所有的答案,这里有两个数据表,一个用来显示风险,另一个用来显示收益。用这种方法可以非常方便地显示所有的组合情况。这样 Excel 就能够找出两种证券的最优组合。这个过程分三个阶段: 以 10%为权重的变化区间来计算所有可能证券组合的收益率和标准差; 查找每个可能的证券组合; 画出组合的收益和风险的散点图并将各点相连。模拟运算表按照标准格式进行制作,这种标准格式已在前面章节详细叙述过(见图12-4) ,组合权重的变化量由顶部单独输入,行和列的输入单元格用蓝色清晰地标出。图 12-4 数据模拟运算表(第 168 页,fig、12、4)单元格 B37 和 B51 分别指向两个名称为 Portfolio_Risk 和 Portfolio_Return 的单元格。结果是一个网状表,两个证券各占 50%的情况也包含其中。条件格式化用来显示每个表的结果(见图 12-5) 。图 12-5 条件格式化(第 168 页,fig、12、5)下一阶段是找出各种可能权重的组合,例如 100:0,90:10,80:20。下一张表名称为INDEX,它用 LOOKUP 函数沿着表格行和列来查找相关值。单元格 C66 的公式为:=INDEX($C$38:$M$48,C$65,C$65)单元 C38:M48 是所有组合的标准差数据表。 A 和 B 的权重比值为 1:0 时的标准差值为 4.12。图 12-6 的结果是以 10%的权重变化量显示的所有可能组合的标准差值。对收益表重复相同操作即可。Fig12-6 Index(第 169 页,fig、12、6)这张表在单元格 D69 显示了最小标准差的值,同时在单元格 G70 用 MATCH 函数来计算它是和哪个时期相关,应用这个函数是因为 LOOKUP 和它的衍生函数需要以升序排列的数据。 Cell D69=MIN(C66:M66 )Cell G70=MATCH(D69,C66:M66,0)单元格 D70 使用函数 OFFSET 从 67 行数据的左边开始移动,直到找到 MATCH 函数的返回值,这是一条市场线从无风险资产出发与最小方差曲线相切。这个理论延伸为资本资产定价模型(Capital Asset Pricing Model), 我们在下一章将详细介绍。Cell D70=OFFSET(B67,0,G70)第三步是用直线连接散点图中的点。图形是一段曲线。这一系列的命令为:=SERIES(Model!$B$67,Mode!$C$66:$M$66,Model!$C$67:$M$67,1)这个系列就是有效前沿,并且说明了其他的权重给出的是一个次有效的或者不是有效的组合。这张图展示了在给定风险的情况下可能得到的最高收益,以及给定收益情况下的可能承受的最低风险(见图 12-7) 。图 12-7 投资组合图(第 170 页,fig、12、7)这条连接无风险利率和组合前沿的线作为一个趋势线被延伸,并且标注成红色,看起来就像市场线被延伸。这条线的公式也可利用趋势线选项显示出来。图形显示了基于历史数据的收益与风险之间的一种权衡。我们可以看到 60%的 A 和 40%的 B 组合可以达到6.53 的收益和 2.03 的标准差。我们可以用下面的公式计算最小风险组合里 A 股票的权重:SDevB2-SDevA*SDevB*Corr/SDevA2+SDevB2-2*SDevA*SDeB*Corr这个公式放在 Model 表的 140 行(见图 12-8) ,计算出各个证券权重以及相应的风险和收益。有关风险和收益的计算公式在上面已经详细介绍过了。图 12-8 最优权重(第 170 页, fig、12、8)另一种方法是,模型也包含了一个“Solver”路径用来寻找最小风险时的权重。它被粘贴在工作表的顶部,用一个宏命令“Solver”进行工作(见图 12-9) 。图 12-9 Solver.(第 171 页,fig、12、9)如果你想通过改变 A 的权重来获得最小风险 ,则权重 B 可通过 100-A 的权重来获得。所以这里不需要再专门考虑 B 的权重。但是必须遵守的规则是: B 与 A 的权重之和必须等于 100 每个权重必须大于或者等于零。这样做的目的是避免“Solver”求出负值,而导致非正常的解。12.3 小结本章介绍了一个重要的金融理论投资组合理论,并且讲解了如何在模型中运用数学方法。在软件中利用了统计函数和数据给出两个证券构成的投资组合的最佳权重以及其他可能的持有方式。13 资本成本本章将介绍企业的资本成本,同时建立资本资产定价模型(CAPM) ,永久年金模型和资本的加权平均成本。模板在 Portfolio 文件中。这里的资本包括: 银行金融资产-贷款种类,期限贷款,资产抵押贷款和辛迪加贷款 债券和国际债券 权益资本-普通股票,优先股,以及它们的组合。每一种资本的来源都有成本。这个模型的目的是为了计算出给公司提供融资的各种方式的成本,找到各种融资方式的比例,然后得到一个资本总成本。资本成本与贷款者及提供资金的投资者的期望有关。如果投资者要求获得一定收益,那么公司必须投资于收益率大于借款成本的项目。如果公司不能赚取足够的利润,它就不能付给投资者所期望的收益。尽管资本成本像是一个理论数据,但它对诸如项目融资,投资评价以及公司估值有直接的影响,这些将在后续章节中陆续介绍。据上所述,资本成本是一个公司必须取得的最小收益。这个成本用加权平均资本成本(WACC)来衡量。本章集中讨论资本的两个领域。 权益成本-用资本资产定价模型,永久年金模型或者增长模型来计算 债务成本资本成本的影响因数包括: 短期和中期的利率环境; 市场风险和特有风险,因为一些公司和市场较其他公司和市场而言在本质上更具不确定性; 金融市场状况以及资本的可用性。13.1 资本资产定价模型资本资产定