本量利分析模型

项目 15 制作量本利分析模型
量本利分析法，全称为产量成本利润分析，也叫保本分析或盈亏平衡分析，是通过分析生产成本、销售利润和产品数量这三者的关系，掌握盈亏变化的规律，指导出企业选择能够以最小的成本生产最多产品并可使企业获得最大利润的经营方案。

15.1 学习目标
通过本项目的学习，可以学会使用Excel 建立量本利分析模型的方法，学会建立盈亏平衡图表的方法，在表格制作的过程中学习并掌握以下Excel 功能的综合运用。

创建Excel 工作簿在单元格中使用公式SUM函数的使用建立量本利分析模型使用规划
求解工具求解盈亏临界点制作量本利分析图表在图表中添加盈亏临界点垂直参考线图表
的修饰
15.2 项目实施效果图
本项目完成后的效果图如下：
门
4
L
E
1
销售单吟 170
2 里儘变动戌本
45 3 单件产品人工成本 2i 4 卑件土品材料费
11 5
单件产吕苴它制造费 10.
甲位辿际贡離
125
1
3 固定成本 1D700D 9
蒼理人灵工资 E600D rio 资产折E 資 30000 n 固左稻整豈用
21000 12
13
销臺
856 14 娈动成菲 20520 15 总成本 145520 10 销售收入 1155ED 17 边飯贡融
107000 IS
利润
D.00
图15-1盈亏临界点销量
里件产品人工成本 单件产品柑料建 竺处品上1宀：二狞 里说；am 亡耻 固连成本 首:里人员L 赍 贲=折|日费 司壬#肖菸用
图15-2量本利分析效果图
4b 0 一 JO 1.07000 -1D7000 24
200 25D&0. 00 107000 -E2000 11
400 旳防仇DO L07000 -57000 10
600 75 DOO. 00 107000 -32D0D
125
900 iDOtm 00 107000 -7 DOO
1000 129000. 00 10 TO 00 13500 107000 1200 150D0D. 00 107000 43D0D
S6d0r 14T)Ci 175000. 00 107000
通 30000 1!&00 200000, 00 LOTOOQ &3000 21 (JOO
1300 225090. CO L07000 1 LH JU J
200CI 250000. 00
107000 14n iri i
22 盘亏临界点垂直詩考线
23
836 2Q0CQC
P4 RM
1QB1QQ
25
◎
药
TQ0QS
28
29
16
17
1R
19
170 销虽
边际贡尅圄定反本
556 3囲如
145520
2
山玄2匚
1U7CQ0 o. ao
---- 血际由献——利毎 二匚店异口帝虫丁任廿
15.3项目实施方案
任务1：理解量本利分析的概念
量本利分析是产量（或销售量）、成本、利润之间依存关系分析的简称，也称VCP分析
(Volume -- Cost --Profit An alysis ）。

它着重研究销售数量、价格、成本和利润之间的
步骤4：制作量本利分析表框架，如图
15-3所示。

数量关系。

它所提供的原理、方法在经济管理中有着广泛的用途， 同时它又是企业进行决策、
计划和控制的重要工具。

在量本利分析中，确定盈亏临界点是进行量本利分析的关键。

所谓盈亏临界点，是指企 业收入和成本相等的经营状态，即边际贡献等于固定成本时企业所处的既不盈利又不亏损的 状态，通常用一定的业务量来表示这种状态。

盈亏平衡临界点越低， 说明企业获得赢利的可
能性越大，出现亏损的可能性越小，企业的抗风险能力也就越强。

量本利分析所考虑的相关因素主要包括销售量、单价、销售收入、 单位变动成本、固定
成本、营业利润等。

任务2:准备量本利分析基础数据
某企业生产某种产品， 该产品的市场销售价格为
170元。

生产每一件产品的直接人工成
本为24元，材料费11元，其它制造费10元。

管理人员工资5.6万元，资产折旧费3万元, 固定销售费用2.1万元。

试建立模型，计算盈亏临界点销量。

步骤1:启动Excel 2010应用程序，创建空白工作簿。

步骤2：将空白工作簿另存为“量本利分析模型”。

步骤3:将Sheet1工作表标签重命名为“量本利分析”
，并删除Sheet2、Sheet3空白
工作表。

A
B 1甜售里价 1九
2皀位变动成本
車件产品人工成本 24
4_ 更件产品苻料帝
11 单件产品其它制造費 10
6鱼位边际贡就
7
e 固走成本 9营理人吊匚卷 5500D
10資产折旧费 3000D 11固定销售费甲
2100D
12
13 14 15 1C 17 16
本
入
<
肚
本
收
贡
>
由
欧
隹
阡
刑
铸
变
总
钧
如图15-3量本利分析表框架
边际贡献又称为“边际利润”
1 Z g
4匸a 7 8 9 10 L1 L2 1：14让ie 17销售羊检单柱变动咸本
单件产品人工成丰
里件产品材料昔
单件产韶耳它制苜费
单位虫际贡研
固定或本首
理人员工召
资产折旧费
固定誚售费用
威
本
tt
贡
量
动
咸
售
怖
润
销
亜
总
ffi
迦
刑
170
45
2d
11
10
125
109000
56000
30000
21000
107000
j
-10700D
表中，“边际贡献”是指销售收入减去变动成本后的余额, 或
“贡献毛益”等。

单位边际贡献是指单件产品销售收入减去单位变
动成本后的余额。

上表中没有数据的单元格为需要运算的数据。

步骤5:计算“单位变动成本”。

点击B2单元格，选择【公式】菜单中的【自动求和】
按钮，在参数输入状态下用鼠标选择B3:B5单元格区域，然后按回车。

步骤6:计算“固定成本”。

点击B8单元格，用与步骤2相同的方法将B9:B11单元格区域求和。

步骤7 :计算“变动成本”，变动成本=销量*单位变动成本。

点击B14单元格，输入公式
“ =B13*B2 ”并回车，
步骤&计算“总成本”，总成本=固定成本+变动成本。

点击B15单元格，输入公式“ =B8+B14' 并回车。

步骤9:计算“销售收入”，销售收入=销量*销售单价。

点击B16单元格，输入公式“ =B13*B1” 并回车。

步骤10：计算“边际贡献”，边际贡献=销售收入=变动成本。

点击B17单元格，输入公式
“ =B16-B14”并回车。

步骤11：计算“利润”，利润=销售收入-总成本，点击B18单元格，输入公式“ =B16-B15” 并回车。

表中“销量”数据B13单元格不用输入数据，计算公式输入完成后的量本利分析表如图
15-4所示。

£B-
图15-4输入计算参数
任务3:使用【规划求解】工具确定盈亏临界点销量
步骤1 :加载【规划求解】工具。

由于Excel 2010 应用程序安装后默认不加载“规划
求解”工具，所以，如果要使用【规划求解】功能时，需要手动加载【规划求解】工具，加载“规划求解”工具的方法如下。

①点击【文件】菜单下的【选项】命令，弹出【E x c e l选项】对话框，如图15-5所示。

图15-5【Excel选项】对话框
②在【Excel选项】对话框窗口中选择【加载项】按钮，然后点击【转到】按钮，弹出
【加载宏】对话框，如图15-6所示。

E
库昇
即冃冃目
抓二工 T
图15-6【加载宏】对话框
③
在【加载宏】对话框窗口中，选中【规划求解加载项】 ，如图15-7所示。

嫌疙 ? X
可可力伺丘厲
讯攵怵翔I 弐贞
宦于凉匕和量我求诲工耳
图15-7选中【规划求解加载项】
④
点击【确定】按钮，系统会自动安装相关软件，软件安装完成后会在【数据】选项卡
中出现【规划求解】按钮，如图 15-8所示。

图15-8加载【规划求解】功能后的【数据】数据选项卡
步骤2:使用【规划求解】工具求解出盈亏临界点销量值。

点击 B18单元格，选择【数
据】选项卡，点击【规划求解】按钮，弹出【规划求解参数】对话框，如图
15-9
所示。

血游 ? X
可声和胳:0;
棕爭珂向号
加用列垂中曲^|櫥兀嘶羞摆戻輛肚
巨訪化I)
军星创井軒鯉xkx • Mrcmok Lnul
运 E.S *眞 迄左
鹅內*曲X
觀黑嚮納舉圖膵腐如嘶吨沖T嘶
MQP 篡昨退》曼间他）
图15-9【规划求解参数】设置对话框,
步骤3:在对话框窗口中设置【规划求解参数】，选中【目标值】前的单选按钮，设置
目标值为0，点击【通过更改可变单元格】下面的文本框，然后点击B13单元格，其他选项保持默认值不变，如图15-10所示。

舷般蜩X
星鬥熾舉詡勰黯誰津簾甌鸿删如痕却引
图15-10设置盈亏临界点销量时的【规划求解参数】
步骤4:点击【求解】按钮，弹出【规划求解结果】对话框，如图15-11所示。

挣.划求韵姑||一阁口带尼肝有网忘强打丁才F：
}E±fe-
□制化强岂尢脚
保存方第---
煤划求瞬找刮一瞬・円蒲足所有的约克反凰瓦伏况。

图5-11【规划求解结果】对话框
步骤5:点击【确定】按钮，求解结果如图15-11所示。

A B
1销售单忻170
2单啦变动戒本45
3单件产品人工咸本24
4审件产品村料竇11
5单件产品只它制洁吏丄0
6里位边际予就125
7
9匿正应本107000
3首理人员工资50000
10冏产折旧苗30000
L1国定销售费用 a ooo
L2
L5上退IE856
14变动成本38&2U
15总成本145520
工销售收人145520
边际质献107000
L6利润7. 270E-0&
图15-11【规划求解】结果
步骤6 :点击B18单元格，选择【开始】选项卡，在【数字格式】菜单中选择【数字】选项完成盈亏临界点销量的最终效果图见图15-1所示。

任务4:制作量本利分析图
步骤1分别在D1：G1单元格区域的单元格中输入字段名“销量、边际贡献、固定成本、利润”，并设置居中，如图15-12所示。

图15-12输入图表字段名
步骤2:在D2:D2单元格区域输入数量“ 0、200、400、…、2000”。

选择D2单元格，输入“ 0”，选择D3单元格，输入“ 200”，同时选择D2、D3两个单元格，然后用鼠标拖曳选中区域的填充句柄向下填充，直至显示2000时放开鼠标。

步骤3 :计算“边际贡献”。

在E2单元格输入公式“ =D2*$B$6”，可以直接输入，或选择E2单元格，输入“=”符号，然后用鼠标点击D2单元格，再输入“ * ”符号，再用鼠标点击B6单元格，按F4把B6转换为绝对地址，向下填充。

步骤4 :输入“固定成本”。

选择F2单元格，输入“ 107000”，或输入单元格引用公式
“ =$B$8”，然后向下填充。

步骤5 :计算“利润”。

选择G2单元格，输入公式“ =D2*$B$1-D2*$B$2-$B$8 ”并向下
填充，公式的含义是“利润=数量*销售单价-数量*单件产品人工成本-固定成本”。

完成输入的量本利数据如图15-13所示。

A E C D F
1边际矗臥1BI走或本利间
此、U. U0L070L0-1157000 3里件产刚人工戍本2425000,00L070D002DOD
411叔00L07000-57D0D
5单fl亠品具它制造茂1160075000.00L070DO-82D0D
512E0K103000. 00L07000■7000
71000125000. 00L0700018D0D
g10700012001BOOOX 001070004300 J
9誉理人员工资14M IZ J OCO. CO 1.07000&aooo
10资产打旧瑁3000 C1000203000* 00L070DC98000
111円工询售宴用2100C】徊225000.00L07000 1 LB000
1223 CW2590C0.00 1.07000143000
图15-13量本利数据
步骤6:选择D1:G12单元格区域.
步骤7:选择【插入】选项卡，点击【散点图】按钮，弹出图表类型选择菜单，如图15-14
所示。

步骤&在菜单中选择【带平滑线的散点图】命令，弹出量本利分析图，如图
15-15
所示。

步骤9:用鼠标右击图例，在弹出的菜单中选择【设置图例格式】命令，弹出【设置图 例格式】对话框，如图 15-16
所示。

图15-14选择【散点图】类型
固室庶宜
—m
图15-15量本利分析图
图15-16【设置图例格式】对话框
步骤10:在【设置图例格式】对话框中选择【图例位置】为【底部】，然后点击【关闭】按钮，设置效果如图15-17所示。

口
图15-17设置【图例位置】在底部
步骤11：在图表中添加“盈亏临界点垂直参考线”。

①准备垂直参考线数据。

选择A22单元格，输入“盈亏临界点垂直参考线”。

在A23:A26单元格区域中输入X轴坐标标注点“ 856”，“ 856”为盈亏临界点销量。

在B23:B26单元格区域中输入Y轴坐标标点“ 200000 （任意的）、107000 （盈亏临界点
边际贡献）、0 （与X轴的交叉点）、-100000 （任意的）”，如图15-18所示。

21
22盈亏临黑直垂直秦考线
23B50200000
24107000
25B5Q0.
26B56-10COOO
图15-18垂直参考线引用数据
②选中绘图区，选择【图表工具】下的【设计】选项卡，点击【选择数据】按钮，弹出
【选择数据源】对话框，如图15-19所示。

图15-19添加图例项
③在弹出的【选择数据源】对话框中点击【图例项（系列）】下的【添加】按钮，弹出【编辑数据系列】对话框，如图15-20所示。

專列? X .至列拿祢® :
图15-20编辑数据系列
④设置垂直参考线参数。

点击【系列名称】下的文本框，然后点击A22单元格；点击【X
轴系列值】下面的文本框，然后选择A23:A26单元格区域；点击【Y轴系列值】下面的文本
框，删除其中的内容，然后选择B23:B26单元格区域。

设置结果如图15-21所示。

tWU⑷
/tfifi(E)
X 删盹
A
铤却生列
?
X
［审靂利賢前! $1$22
> ■湮弓窗点窘直巒考烧
-本重■利牙扳!餵Z: 3AJE6 虽 -65乳 05® 056,..
丁戦系列值〔1）：
=下重利”析1 *»鮭3: $BUB|
匿］=^Donooi innnn
碼定取角
图15-21垂直参考线参数设置结果
⑤
点击【确定】按钮，返回到【选择数据源】对话框窗口，这时【图例项】中多了一项
【盈亏临界点垂直参考线】，如图15-22所示。

频提区碰囚丈亘鼻币无崔显示。

如果选轻牺的教堀区垸，则寄鞍系钊窗格中的所有系列“
图15-22添加垂直参考线后的数据源
⑥ 点击【确定】按钮，图形中插
入了一条垂直参考线，如图 15-23所示。

图15-23插入垂直参考线后的量本利分析图
步骤12：调整垂直参考线线型。

X
边际质賦
固亡戒応 利问
隘亏船畀直垂直墊制主
酬!项係列）⑸
氷平］廿类）铀标签
（D
^眩亟贡秫 ——固定氐取
利珂 一雷亏苗界臣审岂蚕看錢
①在垂直参考线的任意一个标记点位置点击左键，选中垂直参考线，如图15-24所示。

图15-24选择垂直参考线
②在选中的垂直参考线标注点上点击右键，在弹出的菜单中选择【设置数据系列格式】命令，弹出【设置数据系列格式】对话框，如图15-25所示。

图15-25设置线型【宽度】
③在对话框中选择【线型】选项，把线型【线宽】调为“ 1磅”，然后点击【关闭】按钮，
步骤13：结垂直参考线添加数据标签。

①点击垂直参考线标点（856，107000）—次，然后再点击一次（这里不是双击），选中
该标注点，如图15-26所示。

300000,00
(150000 00)
---- 谊际貢輔 ------ 直定成玉一利伺一幕亏IS巽旦爭負薈肴莎
图15-26选择垂直参考线的一个标注点
②在选中的标注点位置点击右键，然后在弹出的菜单中选择【添加数据标签】命令，则在标注点右侧添加Y轴坐标值，如图15-27所示。

图15-27在垂直参考线与边际贡献交叉点添加数据标签步骤14：调整图形位置，
完成后的量本利分析模型及图表如图15-2所示。

1。

6。