2024年5月17日发(作者：商余馥)
图2
3、选择“XY散点图”及其子图“无数据点平滑线散点图”，单击[下一步]，显示
“图表数据源”对话框。
4、选择“系列产生在列”，单击[完成]，显示图表如图3所示。
Excel做量本利分析
什么是量、本、利分析呢？它是运用数学计算和图像法研究产品销量、成本及售价的
变化，对目标利润影响的一种分析方法，它作为财务分析的有效的手段，在企业管理中得
到广泛应用。本文试用Excel对量、本、利关系做一解析。
盈亏平衡点的测定
盈亏平衡点指某种产品的销售收入恰好等于总成本时的销售量或销售额，在该点，既
无盈利，也无亏损。盈亏平衡点的测定，需要根据产品的有关数据资料，对量、本、利之
间的关系进行分析。
例1：某产品固定成本为12000元，单位可变成本为100元，售价为210元，试确定
盈亏平衡点的销量。
计算方法如下：
图1
如图1所示，在A2、B2、C2中分别键入固定成本、单位可变成本、售价，在单元格
D2中键入公式“=A2/(C2-B2)”，则D2中显示的数据即为所求，可知盈亏平衡点销量为
109单位。
盈亏平衡图作法如下：
1、取销售量为0、20、40……180，分别键入A5、A6……A14， B5中键入公式
“=12000+100*A5”、C5中键入公式“=210*A5”。将B5、C5中的公式分别复制到
B6:B14、C6:C14，得到数据如图2所示。
2、选择A4:C14，单击“插入-图表…”(或单击工具栏[图表向导]按钮)，显示“图表
类型”对话框。
图4
图6
由图可知，要实现5000元目标利润，目标销量需达到155单位。
成本变化对利润影响的测定
目标利润下销量的测定
当产品的固定成本、单位可变成本、售价已知时，如果要实现一定的目标利润，可通
过量、本、利分析确定相应的销量。
例2：上例产品固定成本、单位可变成本、售价不变，试测定销量达到多少时可获目
标利润5000元。
图5
3、激活“目标值”文本框，键入“5000”(即目标利润5000元)。
4、激活“可变单元格”文本框，键入“D18”(或单击“D18”)，单击[确定]按钮，显
示“单变量求解状态”对话框。
5、单击[确定]按钮，显示计算结果如图6所示。
计算方法如下：
1、建立计算表如图4所示，单元格E18中键入公式“=(C18-B18)*D18-A18”。
2、单击“E18”。 执行菜单命令“工具-单变量求解”，显示“单变量求解”对话框，
如图5所示。
图3
图中X轴为销量，Y轴为成本，两直线中较平缓的一条为总成本线，较陡峭的一条为
总收入线，两直线交点为盈亏平衡点，该点X轴的坐标为109，即当销量为109单位时盈
亏平衡。位于平衡点左侧两直线所夹区域为亏损区，平衡点右侧两直线所夹区域为盈利区。
成本包括固定成本和可变成本。在产品售价不变的情况下，成本的变化将会引起利润
做反方向的运动，即成本上升，利润下降；反之，成本下降，利润上升。
譬如，产品原材料提价，引起可变成本上升，导致利润下降；广告可以促销产品，但
广告费的支出又引起固定成本的上升。如此，在总成本上升的情况下，销量达到多少才能
维持目标利润。
通过量、本、利分析，可以确定目标销量的增量，进而分析能否达到该增量，以便作
出正确的经营决策。
图7
例3：上例产品固定成本上升5000元，单位可变成本上升300元，售价不变，如果目
标利润维持5000元，则销量比原目标销量增加多少？
计算方法如下：
1、建立计算表如图7所示。单元格A22、B22、C22、E22及F22中，分别键入公式“=
A18+5000”、“=B18+30”、“=C18”、“=D22-D18”、“=(C22-B22)*D22-A22”。
2、参照例2用单变量求解法求解D22中的数据，由此导出E22中的数据(目标销量增
量)，结果如图8所示。
图8
由图可知，如要实现5000元的目标利润，销量则要在原目标销量的基础上增加120单
位。
2024年5月17日发(作者：商余馥)
图2
3、选择“XY散点图”及其子图“无数据点平滑线散点图”，单击[下一步]，显示
“图表数据源”对话框。
4、选择“系列产生在列”，单击[完成]，显示图表如图3所示。
Excel做量本利分析
什么是量、本、利分析呢？它是运用数学计算和图像法研究产品销量、成本及售价的
变化，对目标利润影响的一种分析方法，它作为财务分析的有效的手段，在企业管理中得
到广泛应用。本文试用Excel对量、本、利关系做一解析。
盈亏平衡点的测定
盈亏平衡点指某种产品的销售收入恰好等于总成本时的销售量或销售额，在该点，既
无盈利，也无亏损。盈亏平衡点的测定，需要根据产品的有关数据资料，对量、本、利之
间的关系进行分析。
例1：某产品固定成本为12000元，单位可变成本为100元，售价为210元，试确定
盈亏平衡点的销量。
计算方法如下：
图1
如图1所示，在A2、B2、C2中分别键入固定成本、单位可变成本、售价，在单元格
D2中键入公式“=A2/(C2-B2)”，则D2中显示的数据即为所求，可知盈亏平衡点销量为
109单位。
盈亏平衡图作法如下：
1、取销售量为0、20、40……180，分别键入A5、A6……A14， B5中键入公式
“=12000+100*A5”、C5中键入公式“=210*A5”。将B5、C5中的公式分别复制到
B6:B14、C6:C14，得到数据如图2所示。
2、选择A4:C14，单击“插入-图表…”(或单击工具栏[图表向导]按钮)，显示“图表
类型”对话框。
图4
图6
由图可知，要实现5000元目标利润，目标销量需达到155单位。
成本变化对利润影响的测定
目标利润下销量的测定
当产品的固定成本、单位可变成本、售价已知时，如果要实现一定的目标利润，可通
过量、本、利分析确定相应的销量。
例2：上例产品固定成本、单位可变成本、售价不变，试测定销量达到多少时可获目
标利润5000元。
图5
3、激活“目标值”文本框，键入“5000”(即目标利润5000元)。
4、激活“可变单元格”文本框，键入“D18”(或单击“D18”)，单击[确定]按钮，显
示“单变量求解状态”对话框。
5、单击[确定]按钮，显示计算结果如图6所示。
计算方法如下：
1、建立计算表如图4所示，单元格E18中键入公式“=(C18-B18)*D18-A18”。
2、单击“E18”。 执行菜单命令“工具-单变量求解”，显示“单变量求解”对话框，
如图5所示。
图3
图中X轴为销量，Y轴为成本，两直线中较平缓的一条为总成本线，较陡峭的一条为
总收入线，两直线交点为盈亏平衡点，该点X轴的坐标为109，即当销量为109单位时盈
亏平衡。位于平衡点左侧两直线所夹区域为亏损区，平衡点右侧两直线所夹区域为盈利区。
成本包括固定成本和可变成本。在产品售价不变的情况下，成本的变化将会引起利润
做反方向的运动，即成本上升，利润下降；反之，成本下降，利润上升。
譬如，产品原材料提价，引起可变成本上升，导致利润下降；广告可以促销产品，但
广告费的支出又引起固定成本的上升。如此，在总成本上升的情况下，销量达到多少才能
维持目标利润。
通过量、本、利分析，可以确定目标销量的增量，进而分析能否达到该增量，以便作
出正确的经营决策。
图7
例3：上例产品固定成本上升5000元，单位可变成本上升300元，售价不变，如果目
标利润维持5000元，则销量比原目标销量增加多少？
计算方法如下：
1、建立计算表如图7所示。单元格A22、B22、C22、E22及F22中，分别键入公式“=
A18+5000”、“=B18+30”、“=C18”、“=D22-D18”、“=(C22-B22)*D22-A22”。
2、参照例2用单变量求解法求解D22中的数据，由此导出E22中的数据(目标销量增
量)，结果如图8所示。
图8
由图可知，如要实现5000元的目标利润，销量则要在原目标销量的基础上增加120单
位。