投资项目盈亏平衡分析及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—C6 C10：=C9/C5 C11：=C2*C5

C12：=C6*C2+C7 C13：=C11—C12 C15：=C7/C9

C16：=C15*C5

（2）利用单变量求解方法计算目标利润对应的目标销售量和目标销售收益

（3）绘制公司的销售收益、总成本、利润等数据，绘制本—量—利。

A：利用模拟运算表准备作图数据。以销售量为变量，销售收益、总成本、利润进行单变量模拟。公式如下：

G3：=B11

H3：=B12

I3：=B13

模拟运算参数：选F3：I5

B：选择F2：I2 F5：I6，绘制XY散点图，注意：数据系列选择列。

调整图形为