本量利分析模型.docVIP

.

.

精品

精品

.

精品

项目15制作量本利分析模型

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

15.1学习目标

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

创建Excel工作簿

在单元格中使用公式

SUM函数的使用

建立量本利分析模型

使用规划求解工具求解盈亏临界点

制作量本利分析图表

在图表中添加盈亏临界点垂直参考线

图表的修饰

15.2项目实施效果图

本项目完成后的效果图如下：

.

.

精品

精品

.

精品

图15-1盈亏临界点销量

图15-2量本利分析效果图

15.3项目实施方案

任务1：理解量本利分析的概念

量本利分析是产量（或销售量）、成本、利润之间依存关系分析的简称，也称VCP分析

.

.

精品

精品

.

精品

（Volume--Cost--ProfitAnalysis）。它着重研究销售数量、价格、成本和利润之间的数量关系。它所提供的原理、方法在经济管理中有着广泛的用途，同时它又是企业进行决策、计划和控制的重要工具。

在量本利分析中，确定盈亏临界点是进行量本利分析的关键。所谓盈亏临界点，是指企业收入和成本相等的经营状态，即边际贡献等于固定成本时企业所处的既不盈利又不亏损的状态，通常用一定的业务量来表示这种状态。盈亏平衡临界点越低，说明企业获得赢利的可能性越大，出现亏损的可能性越小，企业的抗风险能力也就越强。

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

任务2：准备量本利分析基础数据

某企业生产某种产品，该产品的市场销售价格为170元。生产每一件产品的直接人工成本为24元，材料费11元，其它制造费10元。管理人员工资5.6万元，资产折旧费3万元，固定销售费用2.1万元。试建立模型，计算盈亏临界点销量。

步骤1：启动Excel2010应用程序，创建空白工作簿。

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

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

步骤4：制作量本利分析表框架，如图15-3所示。

如图15-3量本利分析表框架

.

.

精品

精品

.

精品

表中，“边际贡献”是指销售收入减去变动成本后的余额，边际贡献又称为“边际利润”或“贡献毛益”等。单位边际贡献是指单件产品销售收入减去单位变动成本后的余额。

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

步骤5：计算“单位变动成本”。点击B2单元格，选择【公式】菜单中的【自动求和】按钮，在参数输入状态下用鼠标选择B3:B5单元格区域，然后按回车。

步骤6：计算“固定成本”。点击B8单元格，用与步骤2相同的方法将B9:B11单元格区域求和。

步骤7：计算“变动成本”，变动成本=销量*单位变动成本。点击B14单元格，输入公式“=B13*B2”并回车，

步骤8：计算“总成本”，总成本=固定成本+变动成本。点击B15单元格，输入公式“=B8+B14”并回车。

步骤9：计算“销售收入”，销售收入=销量*销售单价。点击B16单元格，输入公式“=B13*B1”并回车。

步骤10：计算“边际贡献”，边际贡献=销售收入=变动成本。点击B17单元格，输入公式“=B16-B14”并回车。

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

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

.

.

精品

精品

.

精品

图15-4输入计算参数

任务3：使用【规划求解】工具确定盈亏临界点销量

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

=1\*GB3①点击【文件】菜单下的【选项】命令，弹出【Excel选项】对话框，如图15-5所示。

图15-5【Excel选项】对话框

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

.

.

精品

精品

.

精品

图15-6【加载宏】对话框

=3\*GB3③在【加载宏】对话框窗口中，选中【规划求解加载项】，如图15-7所示。

图15-7选中【规