本量利分析模型

1、项目 15 制作量本利分析模型量本利分析法， 全称为产量成本利润分析， 也叫保本分析或盈亏平衡分析， 是通过分析生产成本、 销售利润和产品数量这三者的关系， 掌握盈亏变化的规律， 指导出企业选择能够以最小的成本生产最 多产品并可使企业获得最大利润的经营方案。15.1 学习目标通过本项目的学习，可以学会使用 Excel 建立量本利分析模型的方法，学会建立盈亏平衡图表的 方法，在表格制作的过程中学习并掌握以下Excel 功能的综合运用。创建 Excel 工作簿 在单元格中使用公式 SUM 函数的使用 建立量本利分析模型 使用规划求解工具求解盈亏临界点 制作量本利分析图表 在图表中添加盈亏临界点垂直

2、参考线 图表的修饰15.2 项目实施效果图本项目完成后的效果图如下：图 15-1 盈亏临界点销量图 15-2 量本利分析效果图15.3 项目实施方案任务 1 ：理解量本利分析的概念量本利分析是产量 （或销售量） 、成本、 利润之间依存关系分析的简称， 也称 VCP分析（ Volume -Cost -Profit Analysis）。它着重研究销售数量、价格、成本和利润之间的数量关系。它所提供的原理、 方法在经济管理中有着广泛的用途，同时它又是企业进行决策、计划和控制的重要工具。 在量本利分析中，确定盈亏临界点是进行量本利分析的关键。所谓盈亏临界点，是指企业收入和 成本相等的经营状态， 即边际贡

3、献等于固定成本时企业所处的既不盈利又不亏损的状态， 通常用一定 的业务量来表示这种状态。盈亏平衡临界点越低，说明企业获得赢利的可能性越大，出现亏损的可能 性越小，企业的抗风险能力也就越强。量本利分析所考虑的相关因素主要包括销售量、单价、销售收入、单位变动成本、固定成本、营 业利润等。任务 2 ：准备量本利分析基础数据某企业生产某种产品，该产品的市场销售价格为 170 元。生产每一件产品的直接人工成本为 24 元，材料费 11 元，其它制造费 10元。管理人员工资 5.6万元，资产折旧费 3万元， 固定销售费用 2.1 万元。试建立模型，计算盈亏临界点销量。步骤 1：启动 Excel 2010应

4、用程序，创建空白工作簿。步骤 2：将空白工作簿另存为“量本利分析模型” 。步骤 3：将 Sheet1 工作表标签重命名为“量本利分析” ，并删除 Sheet2、 Sheet3 空白工作表。 步骤 4：制作量本利分析表框架，如图 15-3 所示。如图 15-3 量本利分析表框架 表中，“边际贡献”是指销售收入减去变动成本后的余额，边际贡献又称为“边际利润”或“贡 献毛益” 等 。单位边际贡献是指单件产品销售收入减去单位变动成本后的余额。上表中没有数据的单元格为需要运算的数据。步骤 5：计算“单位变动成本” 。点击 B2 单元格，选择【公式】菜单中的【自动求和】按钮，在 参数输入状态下用鼠标选择

5、B3:B5 单元格区域，然后按回车。步骤 6：计算“固定成本” 。点击 B8 单元格，用与步骤 2 相同的方法将 B9:B11 单元格区域求和。 步骤 7：计算“变动成本” ，变动成本 =销量 *单位变动成本。 点击 B14 单元格，输入公式“ =B13*B2” 并回车，步骤 8：计算“总成本” ，总成本 =固定成本 +变动成本。点击 B15 单元格，输入公式“ =B8+B14” 并回车。步骤 9：计算“销售收入” ，销售收入 =销量*销售单价。点击 B16 单元格，输入公式“ =B13*B1”并回车。步骤 10：计算“边际贡献”，边际贡献 =销售收入 =变动成本。点击 B17 单元格，输入公

6、式“=B16-B14” 并回车。步骤 11：计算“利润” ，利润 =销售收入 -总成本，点击 B18 单元格，输入公式“ =B16-B15”并回 车。表中“销量”数据 B13 单元格不用输入数据，计算公式输入完成后的量本利分析表如图 15-4 所 示。图 15-4 输入计算参数任务 3：使用【规划求解】工具确定盈亏临界点销量步骤 1：加载【规划求解】 工具。由于 Excel 2010应用程序安装后默认不加载 “规划求解” 工具， 所以，如果要使用【规划求解】功能时，需要手动加载【规划求解】工具，加载“规划求解”工具的 方法如下。 点击【文件】菜单下的【选项】命令，弹出【Excel选项】对话框，

7、如图 15-5 所示。图 15-5 【Excel 选项】对话框 在【 Excel选项】对话框窗口中选择【加载项】按钮，然后点击【转到】按钮，弹出【加载宏】 对话框，如图 15-6 所示。图 15-6 【加载宏】对话框 在【加载宏】对话框窗口中，选中【规划求解加载项】 ，如图 15-7 所示。图 15-7 选中【规划求解加载项】 点击【确定】 按钮，系统会自动安装相关软件， 软件安装完成后会在 【数据】 选项卡中出现 【规 划求解】按钮，如图 15-8 所示。图 15-8 加载【规划求解】功能后的【数据】数据选项卡步骤 2：使用【规划求解】工具求解出盈亏临界点销量值。点击B18 单元格，选择【数

8、据】选项卡，点击【规划求解】按钮，弹出【规划求解参数】对话框，如图 15-9 所示。图 15-9 【规划求解参数】设置对话框，步骤 3：在对话框窗口中设置 【规划求解参数】 ，选中【目标值】 前的单选按钮， 设置目标值为 0， 点击【通过更改可变单元格】下面的文本框，然后点击 B13 单元格，其他选项保持默认值不变，如图 15-10 所示。图 15-10 设置盈亏临界点销量时的【规划求解参数】步骤 4：点击【求解】按钮，弹出【规划求解结果】对话框，如图15-11 所示。图 5-11 【规划求解结果】对话框步骤 5：点击【确定】按钮，求解结果如图15-11 所示。图 15-11 【规划求解】结果

9、步骤 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 时放

10、开鼠标。步骤 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 ”并向下填充，公 式的含义是“利润 =数量 *销售单价 -数量 *单件产品人工成本 -固定成本”。完成输入的

11、量本利数据如图 15-13 所示。图 15-13 量本利数据步骤 6：选择 D1:G12 单元格区域 .步骤 7：选择【插入】选项卡，点击【散点图】按钮，弹出图表类型选择菜单，如图15-14 所示。图 15-14 选择【散点图】类型步骤 8：在菜单中选择【带平滑线的散点图】命令，弹出量本利分析图，如图15-15 所示。图 15-15 量本利分析图步骤 9：用鼠标右击图例，在弹出的菜单中选择【设置图例格式】命令，弹出【设置图例格式】 对话框，如图 15-16 所示。图 15-16 【设置图例格式】对话框步骤 10：在【设置图例格式】对话框中选择【图例位置】为【底部】，然后点击【关闭】按钮，设置效

12、果如图 15-17 所示。图 15-17 设置【图例位置】在底部步骤 11：在图表中添加“盈亏临界点垂直参考线” 。 准备垂直参考线数据。选择 A22 单元格，输入“盈亏临界点垂直参考线” 。在 A23:A26 单元格区域中输入 X 轴坐标标注点“ 856”，“856 ”为盈亏临界点销量。在 B23:B26 单元格区域中输入 Y 轴坐标标点 “ 200000（任意的）、107000（盈亏临界点边际贡献） 、 0（与 X 轴的交叉点）、-100000（任意的）”，如图 15-18 所示。图 15-18 垂直参考线引用数据 选中绘图区，选择【图表工具】下的【设计】选项卡，点击【选择数据】按钮，弹出

13、【选择数 据源】对话框，如图 15-19 所示。图 15-19 添加图例项 在弹出的【选择数据源】对话框中点击【图例项（系列 ）】下的【添加】按钮，弹出【编辑数据系列】对话框，如图 15-20 所示。图 15-20 编辑数据系列 设置垂直参考线参数。点击【系列名称】下的文本框，然后点击 A22 单元格；点击【 X轴系列 值】下面的文本框，然后选择 A23:A26 单元格区域；点击【 Y 轴系列值】下面的文本框，删除其中的 内容，然后选择 B23:B26单元格区域。设置结果如图 15-21 所示。图 15-21 垂直参考线参数设置结果 点击【确定】按钮，返回到【选择数据源】对话框窗口，这时【图例

14、项】中多了一项【盈亏临 界点垂直参考线】 ，如图 15-22 所示。图 15-22 添加垂直参考线后的数据源 点击【确定】按钮，图形中插入了一条垂直参考线，如图 15-23 所示。图 15-23 插入垂直参考线后的量本利分析图步骤 12：调整垂直参考线线型。 在垂直参考线的任意一个标记点位置点击左键，选中垂直参考线，如图 15-24 所示。图 15-24 选择垂直参考线 在选中的垂直参考线标注点上点击右键，在弹出的菜单中选择【设置数据系列格式】命令，弹 出【设置数据系列格式】对话框，如图 15-25 所示。图 15-25 设置线型【宽度】 在对话框中选择【线型】选项，把线型【线宽】调为“1 磅”，然后点击【关闭】按钮，步骤 13：结垂直参考线添加数据