投资项目盈亏平衡分析及EXCEL的应用

投资项目盈亏平衡分析及EXCEL的应用摘要：在项目投资决策中，动态盈亏平衡分析通常被用来判断项目的经营风险，从而提高决策的科学性。本文通过案例分析，运用EXCEl。构建投资项目的动态盈亏平衡分析模型，其结论有助于项目执行者在执行决策认识到充分利用ExCEL的重要性，从而预先考虑措施与对策，实现科学决策。关键词：Excel；投资项目；动态盈亏平衡分析盈亏平衡分析是在成本形态分析和变动成本法的基础上进一步分析研究销量、价格、成本及利润直接的内在规律性联系，为企业进行预测、决策、控制和计划提供必要的财务信息的一种定量分析方法。西方管理学家通常认为企业管理的重心在于经营，经营的重心在于决策，即充分利用所掌握的信息，为实现某一目标，从而达到资源（人、财、物等）的优化配置。盈亏平衡分析法是企业经营决策中常用的工具之一，它是根据产品的业务量（产量或销量）、成本、利润之间的相互制约关系的综合分析，用来预测利润，控制成本，判断经营状况的一种数学分析方法，亦即通过成本、销售量、利润三者之间的关系分析，确定项目的最低生产和销售水平，即盈亏平衡点，也称为保本点。在上海财经大学出版社出版的由刘兰娟主编的《财经管理中的计算机应用》一书中，介绍了用Excel建立盈亏平衡分析模型的方法。在该书中作者运用公式计算边际贡献、销售收益、总成本和利润，运用模拟运算表作出制图所需的数据，用散点图来展示边际贡献、固定成本与利润的关系，并用模拟运算表的方法在图形中加入盈亏平衡销量垂直参考线和当前销量垂直参考线，用微调项控件来对销量和售价进行调节，将调节对利润和盈亏平衡点的影响结果显示在文本框中。利用Excel做的将业务、公式和图表紧密结合后的一个盈亏平衡分析的图形，交互式的功能让管理人员体会到数据在“说话”，他只需调整销量和售价两个参数，马上在图表上展示出边际贡献、固定成本与利润的关系图，同时还能给出盈亏平衡的分析结果，是盈利还是亏损，以及该售价所对应的盈亏平衡销量。Excel制作的盈亏平衡分析模型，在此模型中可以调节销量和售价两个变量，求得相应销量下的利润情况和相应单价对应的平衡销量并形象地将结果展示出来。模型采用结构图的方式能够形象地展现出影响利润的各个因素之间的关系，比如可以通过调整可变成本的滑块，变化各种相关的因素，图中的直接人工等因素就会发生变化进而会影响总的可变成本，并带动总成本，最终影响利润。而影响固定成本的主要因素，包括管理层薪酬、保险费用、折旧、广告等，这里采用了直接在进度条上拖动的方式，根据业务状况灵活调整，来影响固定成本，并带动总成本，最终影响利润。一、传统盈亏平衡分析模型描述1、基本模式Excel电子表格建立盈亏平衡分析模型的方法，可采用公式计算、单变量求解、规划求解等寻找盈亏平衡点的多种方法，分析各种管理参数的变化对盈亏平衡点的影响。盈亏平衡分析问题描述：销售量Q，销售收益R，总成本C以及利润л之间的关系的模型：销售收益R=销售量Q*销售单价p总成本C=固定成本+变动成本V变动成本V=单位变动成本v*销售量Q总成本C=固定成本+单位变动成本v*销售量Q利润л=销售收益R−总成本C单位边际贡献=销售单价p−单位变动成本v边际贡献=销售收益R−变动成本V边际贡献率k=单位边际贡献/销售单价2、盈亏平衡销量Q0和盈亏平衡销售收益R0二、EXCEL中建立盈亏平衡分析模型的步骤（1）在Excel中建立盈亏平衡分析的框架，输入产品的单价、单位变动成本、固定成本（2）给定销售量的情况下，计算总成本、销售收益、利润等。（3）可绘制利润随着销售量改变的XY散点图形（利用模型运算表）（4）计算盈亏平衡点：可以使用A：单变量求解B：规划求解C：公式计算（5）可绘制参数（例如单价）对盈亏平衡点的影响（6）根据预期的利润确定实现该利润的产品销量三、案例分析某公司制造一种高质量运动鞋，公司管理层邀请你帮助公司整理用于管理决策的信息，公司最高生产能力为1500。一项销售调查显示明年的平均每双销售价格定为90元；公司的成本数据为：固定成本为37800元，每双可变成本为36元。若当前的销量为900，要求：1、计算单位边际贡献及边际贡献率；2、计算销售收益、总成本及利润；3、盈亏平衡（保本点）销量及盈亏平衡销售收益；4、假若公司预算利润为24000元，计算为达到利润目标所需要的销量及销售收益；5、根据公司的销售收益、总成本、利润等数据，绘制本-量-利图形；通过图形动态反映出销量从100按增量10变化到1500时利润的情况及“盈利”、“亏损”、“保本”的决策信息。6、假定销售单价从80元按增量变化到100元时，计算出盈亏平衡销量和盈亏平衡销售收益的相应变化值？并且以图形方式动态反映。操作步骤：（1）在Excel中建立模型，计算单位边际贡献、单位边际贡献率、销售收益、总成本、利润、盈亏平衡销量、盈亏平衡销售收益各个单位格公式如下：C9：=C5—C6C10：=C9/C5C11：=C2*C5C12：=C6*C2+C7C13：=C11—C12C15：=C7/C9C16：=C15*C5（2）利用单变量求解方法计算目标利润对应的目标销售量和目标销售收益（3）绘制公司的销售收益、总成本、利润等数据，绘制本—量—利。A：利用模拟运算表准备作图数据。以销售量为变量，销售收益、总成本、利润进行单变量模拟。公式如下：G3：=B11H3：=B12I3：=B13模拟运算参数：选F3：I5B：选择F2：I2F5：I6，绘制XY散点图，注意：数据系列选择列。调整图形为-500000500001000001500000500100015002000销售收益总成本利润C：构造垂直参考线的数据增加销售量垂直线参考：公式：F8：=B15F9：=B15G8：-40000G8：140000选择F8：G9，选择“编辑”的“选择性粘贴”增加销售量垂直参考线：公式：F12：=B2F13：=B2G12：-40000G13：140000选择F12：G13，选择图形，选择“编辑”的“选择性粘贴”D：在表格的A19和A20建立公式，反映建模型的结果：A19：==销售量为:&B2&IF(B130,赢利,IF(B13=0,平衡,亏损))A20：==售价=&B5&元，盈亏平衡销量=&ROUND(B15,0)E：在图表中创建微调器控件，反映销售量和单价与利润之间的关系。在图表中创建标签：公式是：=$b$19同样：创建单价和销售利润之间的关系：四、传统盈亏平衡分析的缺陷和改进1、传统盈亏平衡分析的原理及缺陷传统盈亏平衡分析，是通过把成本划分为固定成本和可变成本。假定产销量一致，根据成本、产量、售价和利润之间的函数关系，找出产量、产品价格、单位产品的可变成本、年固定成本、生产能力利用率等因素的盈亏平衡点，再结合我们预测的各个因素可能的变动情况，对项目的风险情况及项目对各因素不确定性的承受能力进行大致判断的一种分析方法。其中，某因素的盈亏平衡点就是项目处于盈亏平衡状态时该因素的数值，而在传统盈亏平衡分析中，盈亏平衡状态定义为销售收入等于总成本即项目盈利为零的状态。传统盈亏平衡分析的主要不足之一是，它是以盈利为零作为盈亏平衡状态，没有考虑资金的时间价值，是一种静态分析。而建设项目生产经营期是一个长期的过程，资金在其运动过程中具有潜在的随时间推移而产生增值的能力，因此，在进行各种投资时，必须考虑投资的实践机会成本、资金的时间价值，按照投资的具体情况、目标以及所在行业的基准收益水平确定一个基准收益率。传统盈利为零的盈亏平衡实际上意味着项目已经损失了基准收益水平的收益，项目存在着潜在的亏损。为克服传统静态盈亏平衡分析的不足，对其进行改进，进行动态盈亏平衡分析。2、传统盈亏平衡分析的改进为克服传统盈亏平衡分析的不足，可以对其进行改进，方法是进行非线性盈亏平衡分析、动态盈亏平衡分析和动态多因素盈亏平衡分析。2.1非线性盈亏平衡分析非线性盈亏平衡分析就是把销售收入看成产品价格和产量的多元函数，同时价格又是产量的函数；把成本看成单位产品可变成本和产量的多元函数，同时单位产品可变成本又是产量的函数。据盈亏平衡等式可求出产量盈亏平衡点Q1、Q2，并由d(TR-TC)／dQ＝0可求出最大盈利点Qmax。如图1，当项目的产量介于Q1、Q2之间时，项目处于盈利状态；而当产量低于较小的平衡点或高于较大的平衡点时，项目均处于亏损状态；Qmax为最佳经济规模点。又根据价格与产量及单位产品可变成本与产量的函数关系可以求出价格盈亏平衡点和单位产品可变成本盈亏平衡点。图1盈亏平衡图2.2动态盈亏平衡分析动态盈亏平衡分析将项目盈亏平衡状态定义为净现值NPV等于零的状态，便能将资金的时间价值考虑在盈亏平衡分析内，变静态盈亏平衡分析为动态盈亏平衡分析。由于净现值的经济实质是项目在整个经济计算期内可以获得的、超过基准收益水平的、以现值表示的超额净收益，所以，净现值等于零意味着项目刚好获得了基准收益水平的收益，实现了资金的基本水平的保值和真正意义的“盈亏平衡”。动态盈亏平衡分析不仅考虑了资金的时间价值，而且可以根据企业所要求的不同的基准收益率确定不同的盈亏平衡点，使企业的投资决策和经营决策更全面、更准确。其中，NCF为所得税前年净现金流量，TR为年销售收入，OC为年经营成本；OCF为年固定经营成本，TA为年销售税金及附加，FI为年固定资产投资，LI为流动资金本年增加额，SV为回收固定资产残值，SLI为回收流动资金，为基准收益率，r为销售税金的税率，t为年份。根据净现值等于零的等式可以计算出销售收入、经营成本、固定资产投资、产量、价格、单位产品可变成本等各个因素的动态盈亏平衡点。2.3动态多因素盈亏平衡分析实际的应用中，多因素盈亏平衡分析和动态盈亏平衡分析可以很好的结合，即在项目盈亏平衡分析是要分析多个因素同时变动对项目净现值的影响，例如，双因素动态盈亏平衡分析可以确定坐标平面上的一条盈亏平衡线，三因素动态盈亏平衡分析可以确定坐标空间上的一个盈亏平衡面，如果分析的因素超过三个，则所确定的是一个盈亏平衡的多个因素变动率关系。根据净现值等于零的等式可以计算出净现值等于零时销售收入、经营成本、固定资产投资、产量、价格、单位产品可变成本等多个因素同时变动的多个因素变动率关系。动态多因素盈亏平衡分析例证例如：有一个石油化工的投资项目，其投资额、年销售收入、年经营成本、年销售税金、期末资产残值如表1所示。采用的数据是根据对未来最可能出现的情况进行预测估算的。由于对未来影响经济效益的某些因素把握不大，投资额、经营成本和产品价格均有可能在±20%的范围内变动,设基准收益率为10%，试进行动态多因素盈亏平衡分析。表1现金流量表万元年份投资期第1年第2～10年第11年投资(I)-15000销售收入(TR)2200022000经营成本(OC)1520015200销售税金(TA)(销售收入的10%)22002200期末资产残值（SV）2000净现金流量（NCF）-15000046006600解：方案净现值计算公式为NPV＝-I＋(TR－TA－TC)(P/A，10%，10)(P/F，10%，1)＋Sv(P/F，10%，11)＝-15000＋4600×6.144×0.9091＋2000×0.3505＝11394(万元)如果同时考虑投资额与经营成本的变动，设投资额变动的百分比为x，经营成本变动的百分比为y，则两个因素同时变动对方案净现值影响的计算公式为：NPV＝－I(1＋x)＋[TR－TA－OC(1＋y)](P/A，10%，10)(P/F，10%,1)+Sv(P/F,10%,11)将表中的数据代入上式，经过整理得NPV=11394-15000x-84900y取NPV的临界值，即令NPV＝0，则有：y＝－0.1767x＋0.1342这是一条直线方案，如图2，它是NPV＝0的临界线，它与x轴交于(75.94%，0),与y轴交于(0，13.42%),在临界线左下方的区域为盈利区，右上方为亏损区，也就是说，无论投资与经营成本如何变动，只要其变动率的组合落在临界线的左下方，方案就可以盈利。图2双因素敏感性分析图参考文献王