本文是介绍如果使用EXCEL的图表功能制作企业盈亏平衡的动态分析模型，也称量本利分析模型。欢迎同学们批评、指正、砸砖~http://s15/middle/adb725224c56bba1916ce&690

 

 

以下内容是全部工作底稿和制作方法：（如果您想试一下，请跟着我做表）

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

假设有一家制衣厂，生产裤子的单位售价是100元，其中生产一件产品的人工费10元， 材料费15元， 制造过程中所耗费的水电费10元(当然可能会有其他随产量变动的制造费用，本文为简化计算暂且不计)

依题可做表如下： 其中“单位可变成本” 即是 直接人工，直接材料，可变制造费 相加之和

上表B2=B3+B4+B5, 请自己加公式。 B7解释一下，B7 ＝销售单价B1 － 单位可变成本B2，这个值在会计上专有名词叫单元边际贡献，意思说卖出一件产品后对利润所做的贡献，注意"单位"两个字，是销售一件产品的贡献。

 

在企业的实际生产过程中，还有部分费用是不随产量的变动而变动的，我们称之为固定费用。如：机器设备的折旧，管理层人员的工资，销售过程中的广告费，等等。它们是不随销量变动，意思是有没有销量，这部分费用依然产生，而且相对固定不变。

 

我们暂估一下这部分数据，如下：（本故事纯属虚构，如有雷同，纯属巧合）

上表 B8=B9+B10+B11

 

假设销量是1000条，继续做表：

上表，B14=B13*B7, B15=B13*B1, B16=B8+B13*B2,，其中B19盈亏平衡销量需要解释一下：

企业随着销售量的增加，边际贡献（所售产品的单位边际贡献之和）也随之增加。但是，只有当所有售出产品的边际贡献总值超出企业的总固定成本时，这时我们可以说这个企业开始盈利。 在利润刚好为零的那一时刻，即是企业的盈亏平衡点。实际上那一刻也就是：边际贡献总值等于固定成本。 因为边际贡献总值=单位边际贡献乘 X 销量，所以盈亏平衡点时的销量= 固定成本÷单位边际贡献， 即：B19=B8/B7

 

以下内容是为EXCEL盈亏损模型所需要的辅助区域， 致于为什么要建这个区域，等做好之后您再慢慢体会，继续做表。。。

其中： C1到F1标题为手工输入，C2销量输入0,  因为C2为0，D2肯定也为0,  E2输入公式 =B8,  F2输入公式: =D2-E2

C3输入1500，假设这是企业最大销量(您闲少？那您填多大我没意见)，D3输入公式: =C3*B7，E3输入公式: =B8，F3输入公式: =D3－E3

 

再加两条参考线：（为什么要加参考线？答案是做好您再体会）

其中：C7到C10的有公式且是一样的: =B19，D8公式：=B8 。C13到C17的公式一样且都是: =B13， D14公式：=B14，D15公式：=B8， D16公式：=B17，其他位置的数字请直接手工输入。

至于为什么输入这些数字，等做完图表您再体会。 现在只需要明白一点：

拿“盈亏点销量垂直参考线”来说，如果把C7到C10的值放图表上的X轴，D7到D10为Y轴的话， 因为X值相同，这些数据其实是在一条直线上的四个点。

 

开始做图。。。。。。

1. 选择C1到F3，然后按工具栏上的 图表向导。 选择XY散点图，子图表类型选择 无数据点折线散点图， 然后按一步。

 

2.  在 “系列产生在" 处选择 “列”，然后点 完成。

3.  完成之后，如下图所示：蓝色文字和箭头指出图表上各位置叫什么名字，下文会用到这些地方。

 

4.  简单修饰一下：

a.  选中图例-〉鼠标右键-〉图例格式－〉位置－〉底部－〉确定

b. 选中绘图区-> 鼠标右键－〉绘图区格式－〉区域 处选择 白色 －〉确定

c. 选中绘图区－〉再选中 y轴主格线 －〉鼠标右键 -> 网格线格式 --〉颜色处 选择 浅灰色 －〉确定

d. 选中 图表上的 黄色“利润”系列线型－〉鼠标右键－〉数据系列格式－〉左边 线型 下的颜色选择 黄色，粗细处选择倒数第二粗的线粗

    同理按上述方法，将“边际贡献”系列改成蓝色，加粗线。 将“固定成本”系列改成桔红色，加粗线型

e. 选中x坐标轴－〉鼠标右键－〉坐标轴格式－〉刻度－〉最小值输入0， 最大值输入1500－〉确定

   同样方法，将y轴刻度最小值设成 -50000, 最大值设成150000

f. 再分别选择中，图例，x轴，y轴，在它们鼠标右键的格式设定中的字体选项里边，将它们的字体全设成11号字体

下图是完成后样子，如果不是这样，请按上边步骤改过。

5. 选中绘图区-> 鼠标右键－〉数据源 -> 在“系列”页上，按 “添加”按钮－〉出现"系列4"(如图)

6. 在x值处，去选择表格上的C7到C10这个区域。在y值处，先删除里边内容中，再去选择表格上的D7到D10这个区域，确定之后图表应该是下面样子：

7. 按上述方法再添加一个系列，系列5 。  x值选择C13到C17，    y值处先删掉里边内容，再选择 D13到D17

 

8.完成后，如下图所示。

 

9. 在绘图区选中 系列4 -> 鼠标右键 -> 数据系列格式－〉左侧 线形 处 颜色 选紫色， 粗细选倒数第二粗 －〉右侧 数据标记 处，前景色选红色，背景色选白色，大小设成 5磅 －〉确定

 

10. 在绘图区选中 系列5 -> 鼠标右键 -> 数据系列格式－〉左侧 线形 处颜色 选天蓝色， 粗细选倒数第二粗 －〉右侧 数据标记 处，前景色选红色，背景色选白色，大小设成 5磅 －〉确定

11. 完成效果如下图：

 

12. 分别清除系列4和系列5的最上边和最上边的数据点格式，操作如下：

a.  先鼠标左键选中 系列4，再鼠标左键单击 系列4 最上边的数据点，选中这个数据点 (切记不是双击那个点，是两次单击才可选中单个数据点)，选中之后，按鼠标右键会出现 数据点格式

 

b. 在右侧的 数据标记 处选 “无” －〉确定

重复以上操作，将系列4最下边数据点，以及系列5 最上和最下 数据点的格式去除掉。 完成后效果如下：

 

13. 关键数据点加 数据标志。 选中系列4， 再选中系列4与固定费用和边际贡献交叉的那个点－〉鼠标右键－〉数据点格式－〉数据标志－〉选中Y值－〉确定

同样操作，选中系列5，再选中系列5与利润交叉的那个点，加数据标志 Y值。确定后，效果如下图所示：

啥？ 跟我的不一样？字体大，没边框？您自己在这个数据标志的右键格式设定里改改嘛...

 

到此为止，一个静态的盈亏模型就建好了。 虽然是静态的，但如果您去修改表格上的数值，如价格，销量，固定成本，这个模型就随之变化了。

 

有没有更好的办法，不直接去修改表值，而是在图表这里直接变动价格，变动销量来实现动态图表呢？

答案是有的，下面我们就在此图的基础之上，来做一个动态图表。

 

这里我们需要用到 “窗体”工具栏上的微调按钮，如图。如果你的工具栏上没有窗体工具栏，请去视图菜单下的工具栏里钩选窗体工具栏

14. 鼠标单击窗体工具栏上的微调按钮，然后在表格上拖拽出这个按钮，然后在其上按鼠标右键－〉选择 设置控件格式

当前值输入100，最小值输入50, 最大值300，步长5, 单元格链接处 选择B1，确定 （目的是用这个按钮控制销售单价的变化）

 

15. 再次鼠标单击窗体工具栏上的微调按钮，然后在表格上拖拽出第二个按钮，然后在其上按鼠标右键－〉选择 设置控件格式

当前值输入1000, 最小值输入0, 最大值1500，步长50, 单元格链接处 选择B13，确定 （目的是用这个按钮控制销量变化）

 

完成之后，当单击这两个按钮的向上或向下键头的时候，你会发现这个图表已经是动态的了。

注：如果您的按钮上没有“控制”这个选项，那请您注意一下：我用的是 窗体工具栏 上的微调按钮，而不是 控件工具栏 的。这两个有区别的

 

16.  给这两个按钮添加一些文字说明

a.   在单元格C18中输入公式：

      ="售价="&B1&"元，盈亏平衡销量="&ROUND(B19,1)

b.   在单元格C19中输入公式：

      ="销量="&B13&"时,"&IF(B17>0,"盈利",IF(B17=0,"保本","亏损"))

c.   在绘图工具栏上 选择 矩形框，在图表上边拉出一个条形矩形框。然后选中这个矩形框，上边公式栏中输入 =C18, 回车.  完成之后矩形框上就会出现C18里的文字，如图所示：

注:   绘图工具栏默认在EXCEL窗口的下边，如果你找不到绘图工具栏，请去 视图 菜单下的工具栏里将其钩选

 

d. 再建一个矩形框。选中这个矩形框，上边公式栏中输入 =C19, 回车. 完成之后矩形框上就会出现C19里的文字，如图所示

 

17.   最后一步，在图表区右键的表选项里给表加上标题。 点编辑菜单下的 －〉定位－〉定位条件－〉选 对象－〉确定 －〉在选中图形上按鼠标右键－〉组合－〉组合，使EXCEL图表，两个按钮，两个矩形框组合到一起。 最后看一下成果吧^^

 

由以上图形不难看出：

1.   企业的销量从无到有，产品的边际贡献也逐渐增加，利润的负值(亏损)逐渐减少。

2.   当边际贡献数值增长到与固定费用持平时，这时利润刚好为零，此时即所谓盈亏平衡点。

3.   当边际贡献再继续增加时，这时企业已开始盈利了。

 

终于完成了，做一个这样的制作说明竟然用了27张图片!!!

 

需要本模型原文件及制作方法的朋友, 请至下面文库的下载链接下载。

http://wenku.baidu.com/view/866873e9e009581b6bd9eb37.html 

终于可以不用发邮件了，好用就给个好评价吧~~