建立企业盈亏平衡分析模型(量本利分析模型)-EXCEL动态图表-折线散点图
EXCEL梦想之家
https://www.360docs.net/doc/8117791361.html,/lxlzmh2002/home
转载请保留出处
假设有一个制衣厂，生产的裤子单位售价100元，
其中生产一件产品的人工费10元， 材料费15元， 制造过程中所耗费的水电费10元(当然可能会有其他随产量变动可做表如下： 其中“单位可变成本” 即为 直接人工，直接材料，可变制造费 相加之和
上表B2=B3+B4+B5, 请自己加公式。 B7解释一下，B7等于销售单介B1减单位可变成本B2, 这个值在会计
注意"单位"两个字，是销售一件产品贡献。
在实际企业生产过程中，还有部分费用是不随产量变动而变动的，我们称之为固定费用。象机器设备的
动，意思是说卖不卖东西，这部分费用依然产生。
我们暂且估计数据如下（本故事纯属虚构，如有雷同，纯属巧合）
1400
上表B8=B9+B10+B11
假设销量1000条，继续做表
上表，B14=B13*B7, B15=B13*B1, B16=B8+B13*B2, 解释一下B19盈亏平衡销量
当企业有销量时，单位边际贡献随之增加，只有所有售出产品的边际贡献总值超过企业的固定成本时，
际贡献总值等于固定成本。因为边际贡献总值=单位边际贡献乘 X 销量，所以盈亏平衡时的销量=固定成本/单位以下内容为EXCEL盈亏损模型所需要的建的辅助区域， 至于为什么要建这个区域，等做好之后您再慢慢
÷
其中： C1到F1标题手工填入，C2销量输入0, C2为0，D2肯定也为0, E2输入公式 =B8, F2输入公式: =D2-E2
C3输入1500, 假设这是企业最大销量(闲少？那您填多大我都不拦着)， D3输入公式: =C3*B7, E3输入公式: =B8再加两条参考线：
其中：C7到C10的公式都是: =B19, D8公式：=B8, C13到C17的公式都是: =B13, D14公式：=B14, D15公
至于为什么输入这些数字, 下边解释。 至于为什么要做参考线也下边解释。 现在只需要明白一点：
拿“盈亏点销量垂直参考线”来说，如果把C7到C10的值放图表上的X轴，D7到D10为Y轴的话， 因为X值相同，这“当前销量垂直参考线”中的数据就是垂直于X轴的直线上的五个点。 明白这些就足够了，下面开始做图表：1. 选择C1到F3，然后按工具栏上的 图表向导。 选择XY散点图，子图表类型选择 无数据点折线散点图， 然后按
2. 系列产生在处选择 列，然后按完成
3.完成之后，如下图所示：蓝色文字指出图表上各部分名字，下文会用到这些地方，别找不到
4. 简单修饰一下。
a. 选中图例-〉鼠标右键-〉图例格式－〉位置－〉底部－〉确定
b. 选中绘图区-> 鼠标右键－〉绘图区格式－〉区域 处选择 白色 －〉确定
c. 选中绘图区－〉再选中 y轴主格线 －〉鼠标右键 -> 网格线格式 —〉颜色处 选择 浅灰色 －〉确定
d. 选中 图表上的 黄色“利润”系列线型－〉鼠标右键－〉数据系列格式－〉左边 线型 下的颜色选择 黄色，同理按上述方法，将“边际贡献”系列改成蓝色，加粗线。 将“固定成本”系列改成桔红色，加粗线型
e. 选中x坐标轴－〉鼠标右键－〉坐标轴格式－〉刻度－〉最小值输入0， 最大值输入1500－〉确定
同样方法，将y轴刻度最小值设成 -50000, 最大值设成1500000
f. 再分别选择中，图例，x轴，y轴，在它们鼠标右键的格式设定中的字体选项里边，将它们的字体全设成11号字
请看下图是完成后样子，如果您的不是这样，请按上边步骤重新改过-60000-40000
-20000
20000
40000
60000
80000
1000001200000500100015002000边际贡献固定成本利润
图表区
绘图区
图例 轴，x 轴
y 轴主格
5. 选中绘图区-> 鼠标右键－〉数据源 -> 在“系列”页上，按 “添加”按钮－〉出现"系列4"(如下图
6. 在x值处，去选择表格上的C7到C10这个区域。在y值处，先删除里边内容中，再去选择表格上的D7到D10这个区
-50000
-30000
-10000
10000
30000
50000
70000
90000
0200400600800100012001400
边际贡献固定成本利润