下载文档原格式
项目15 制作量本利分析模型量本利分析法，全称为产量成本利润分析，也叫保本分析或盈亏平衡分析，是通过分析生产成本、销售利润和产品数量这三者的关系，掌握盈亏变化的规律，指导出企业选择能够以最小的成本生产最多产品并可使企业获得最大利润的经营方案。
15.1 学习目标通过本项目的学习，可以学会使用Excel建立量本利分析模型的方法，学会建立盈亏平衡图表的方法，在表格制作的过程中学习并掌握以下Excel功能的综合运用。
●创建Excel工作簿●在单元格中使用公式●SUM函数的使用●建立量本利分析模型●使用规划求解工具求解盈亏临界点●制作量本利分析图表●在图表中添加盈亏临界点垂直参考线●图表的修饰15.2 项目实施效果图本项目完成后的效果图如下：图15-1 盈亏临界点销量图15-2 量本利分析效果图15.3 项目实施方案任务1：理解量本利分析的概念量本利分析是产量（或销售量）、成本、利润之间依存关系分析的简称，也称VCP分析（Volume -- Cost --Profit Analysis）。
它着重研究销售数量、价格、成本和利润之间的数量关系。
它所提供的原理、方法在经济管理中有着广泛的用途，同时它又是企业进行决策、计划和控制的重要工具。
在量本利分析中，确定盈亏临界点是进行量本利分析的关键。
所谓盈亏临界点，是指企业收入和成本相等的经营状态，即边际贡献等于固定成本时企业所处的既不盈利又不亏损的状态，通常用一定的业务量来表示这种状态。
盈亏平衡临界点越低，说明企业获得赢利的可能性越大，出现亏损的可能性越小，企业的抗风险能力也就越强。
量本利分析所考虑的相关因素主要包括销售量、单价、销售收入、单位变动成本、固定成本、营业利润等。
任务2：准备量本利分析基础数据某企业生产某种产品，该产品的市场销售价格为170元。
生产每一件产品的直接人工成本为24元，材料费11元，其它制造费10元。
管理人员工资5.6万元，资产折旧费3万元，固定销售费用2.1万元。
本量利分析模型的应用本论文将讨论本量利分析模型的应用，包括了它的概念、组成要素及应用场景等方面的内容。
本量利分析模型是管理会计中一个非常重要的概念，它与企业盈利能力息息相关，能够帮助企业进行决策，提高企业的盈利能力。
一、概念本量利分析模型指的是一种通过分析企业销售量与利润之间的关系，来测量企业盈利能力的方法。
即在一定的成本结构下，分析企业的销售量与利润的变化，从而判断企业的盈利能力。
本量利分析模型是一个基本的管理会计工具，通常用来评估企业生产方案的效益与可行性。
二、组成要素本量利分析模型主要由以下三个要素组成：1、销售量：指企业销售的产品数量，是一个量的概念。
2、销售价格：指企业销售产品的价格，通常以单价计算。
3、成本：指企业生产产品所需要的费用，包括直接成本和间接成本两种。
其中直接成本指产品所必须的材料、人工等费用，间接成本指非直接与产品相关的费用，如房租、管理人员薪水等。
三、应用场景本量利分析模型在企业中有着广泛的应用场景，常见的应用场景包括：1、产品定价：企业可以通过本量利分析模型来确定最优价格，从而达到最大化盈利的目的。
2、产品生产方案：企业可以通过本量利分析模型来比较不同的生产方案，从而选择最优的生产方案，提高盈利能力。
3、销售策略：企业可以通过本量利分析模型来确定最佳销售策略，如重点推销高利润产品。
4、市场营销：企业可以通过本量利分析模型来预测市场需求，制定相应的市场营销策略。
四、实际应用本量利分析模型在实际应用中有着广泛的应用，下面以一个实例进行说明。
某企业生产一种产品，其销售价格为200元/件，市场需求为每月2000件。
该企业的成本结构如下：直接材料费用：40元/件直接人工费用：20元/件制造费用：10元/件管理费用：8000元/月售后服务费用：3000元/月该企业通过本量利分析模型来确定最佳生产方案，其利润与销售量的关系如下图：该图表显示出了该企业不同销售量下的利润情况，通过分析图表可以得出以下结论：1、当销售量低于1000件时，企业将亏损。
本量利分析模型excel什么是本量利分析模型？本量利分析模型是一种管理工具，用于帮助企业分析其产品或服务的销售量与利润之间的关系。
通过本量利分析，企业可以了解其销售活动的盈利能力，从而做出相应的经营决策。
本量利分析模型通常使用Excel进行计算和展示。
如何构建本量利分析模型？首先，我们需要收集以下信息：1.产品价格：企业销售的产品或服务的售价。
2.变动成本：销售产品或服务所产生的直接变动成本，如原材料成本、制造成本等。
3.固定成本：与销售产品或服务无直接关联的固定费用，如租金、工资等。
4.销售量：销售的产品或服务的数量。
假设已经有一个产品的销售数据表，包含列：销售数量、销售单价、直接变动成本和固定成本。
我们可以在Excel中使用以下公式来计算本量利分析模型的各项指标：1. 总收入通过将销售数量乘以销售单价，可以计算出总收入。
假设销售数量列为A列，销售单价列为B列，则可以在C列使用以下公式计算总收入：= A1 * B12. 总成本通过将直接变动成本和固定成本相加，可以计算出总成本。
假设直接变动成本列为C列，固定成本列为D列，则可以在E列使用以下公式计算总成本：= C1 + D13. 总利润通过将总收入减去总成本，可以计算出总利润。
可以在F列使用以下公式计算总利润：= C1 - E14. 利润率利润率表示企业每一单位销售收入中的盈利情况。
可以通过将总利润除以总收入并乘以100来计算利润率。
可以在G列使用以下公式计算利润率：= (F1 / C1) * 1005. 边际效益边际效益表示企业每增加一个单位的销售量所带来的额外利润。
可以通过将销售数量逐行递增，然后计算每一行的总利润与前一行的总利润之差来计算边际效益。
如何使用本量利分析模型进行决策？本量利分析模型可以帮助企业做出以下决策：1.定价策略：企业可以通过分析不同价格水平下的销售量与利润变化，确定最优的产品定价。
2.产品组合策略：企业可以通过分析不同产品组合下的销售量与利润变化，确定最优的产品组合。
本量利分析模型.doc
本量利分析模型是用于分析企业在不同销售量下利润变化的一个工具。
它是由固定成本、可变成本、销售量和销售价格等变量构成的数学模型。
通过分析销售量的变化对企业利润的影响，企业可以在做出相关决策时更准确地估计风险和回报。
本量利分析模型的基本假设是：所有成本可以分为固定成本和可变成本，销售价格和可变成本是恒定的，销售量的变化对利润的影响是线性的。
在这些基础假设的基础上，可以得到以下公式：
总成本 = 固定成本 + 可变成本× 销售量
总收益 = 销售价格× 销售量
利润 = 总收益 - 总成本
其中，固定成本是在任何销售量下都不会变化的成本，例如房租、固定薪资等；可变成本是随销售量变化而变化的成本，例如原材料、生产设备维修费用等。
销售价格是产品或服务售出的价格，销售量是指企业在一定时间内售出的产品或服务数量。
通过本量利分析模型，企业可以确定不同销售量下的利润和利润率，进而进行决策，例如确定适当的价格、调整生产量、开拓新市场等。
同时，本量利分析模型也可以帮助企业评估不同投资方案的风险和回报。
但需要注意的是，本量利分析模型的基础假设可能并不完全符合实际情况，例如销售价格和可变成本不一定始终保持不变，利润与销售量的关系也可能不是线性的。
因此，在应用本量利分析模型进行决策时，还需要加入实际情况的考虑，以准确地估计风险和回报。
总之，本量利分析模型是一个非常有用的工具，可以帮助企业决策时更好地估计风险和回报，从而做出更明智的决策。
第十六章本量利分析本章考情分析本章属于重点章。
本章主要阐述成本按性态分类以及混合成本估计的方法，本量利分析基本模型的相关假设，本量利分析基本模型，单一品种保本分析，多品种情况下的保本分析、保利分析和利润敏感性分析。
本章内容较为简单，主要是为相关章节内容奠定计算基础。
本章提出的有关概念和计算是学习其他章节的基础，如利润对销售量的敏感系数与第九章经营杠杆系数联系紧密；与第17章短期经营决策进行结合等等。
因此，学习时主要注意把握本章重点内容在相关章节内容中是如何融合的。
本章从题型来看，包括客观题和主观题，近3年平均分值为8.5分左右。
考点一成本性态分析类别细分类含义要点阐释固定成本约束性固定成本约束性固定成本，即经营能力成本，是企业为维持一定的业务量所必须负担的最低成本，不能通过当前的管理决策行动加以改变。
要想降低约束性固定成本，只能通过合理利用经营能力、增加生产规模、降低单位固定成本加以解决。
如固定资产折旧费、财产保险等1．固定成本的“固定”强调“相关范围内”，即一定业务量范围和一定期间范围2.固定成本的“固定”强调特定范围内，成本总额不随业务量的增减变化而变化，但单位产品的固定成本随业务量的增减变化而反方向变化酌量性固定成本酌量性固定成本，即经营方针成本，是企业根据经营方针可以加以改变的固定成本。
如广告费、科研开发费、职工培训费等变动成本技术性变动成本技术性变动成本，是利用生产能力所必须发生的变动成本。
如直接材料成本、直接人工成本1．变动成本的“变动”强调“相关范围内”，即一定业务量范围和一定期间范围2．变动成本的“变动”强调特定范围内，成本总额随业务量的增减变化而正比例变化，但单位产品的变动成本不随业务量的增减变化而变化酌量性变动成本酌量性变动成本，是指通过管理决策行动加以改变的变动成本。
如销售佣金、技术转让费等类别细分类含义要点阐释混合成本半变动成本半变动成本，是指通常有一个初始量，类似于固定成本，在这个初始量的基础上随业务量的增长而正比例增长，又类似于变动成本。
项目1５制作量本利分析模型量本利分析法，全称为产量成本利润分析,也叫保本分析或盈亏平衡分析,是通过分析生产成本、销售利润和产品数量这三者的关系,掌握盈亏变化的规律，指导出企业选择能够以最小的成本生产最多产品并可使企业获得最大利润的经营方案。
1５.１学习目标通过本项目的学习，可以学会使用Eｘcｅl建立量本利分析模型的方法,学会建立盈亏平衡图表的方法,在表格制作的过程中学习并掌握以下Excel功能的综合运用。
●创建Exｃel工作簿●在单元格中使用公式●SUM函数的使用●建立量本利分析模型●使用规划求解工具求解盈亏临界点●制作量本利分析图表●在图表中添加盈亏临界点垂直参考线●图表的修饰15．2 项目实施效果图本项目完成后的效果图如下：图15－1 盈亏临界点销量图15－2 量本利分析效果图15.3 项目实施方案任务1:理解量本利分析的概念量本利分析是产量（或销售量）、成本、利润之间依存关系分析的简称，也称VＣP分析(Volume －- Ｃoｓt －-ProfiｔAnalｙsiｓ）。
它着重研究销售数量、价格、成本和利润之间的数量关系。
它所提供的原理、方法在经济管理中有着广泛的用途,同时它又是企业进行决策、计划和控制的重要工具。
在量本利分析中,确定盈亏临界点是进行量本利分析的关键。
所谓盈亏临界点，是指企业收入和成本相等的经营状态，即边际贡献等于固定成本时企业所处的既不盈利又不亏损的状态，通常用一定的业务量来表示这种状态。
盈亏平衡临界点越低，说明企业获得赢利的可能性越大，出现亏损的可能性越小,企业的抗风险能力也就越强。
量本利分析所考虑的相关因素主要包括销售量、单价、销售收入、单位变动成本、固定成本、营业利润等。
任务2:准备量本利分析基础数据某企业生产某种产品,该产品的市场销售价格为１70元。
生产每一件产品的直接人工成本为24元，材料费11元,其它制造费10元。
管理人员工资5.6万元，资产折旧费3万元,固定销售费用2.1万元。
试建立模型,计算盈亏临界点销量。
步骤1：启动Ｅxcｅl ２010应用程序，创建空白工作簿。
步骤2：将空白工作簿另存为“量本利分析模型”。
步骤３：将Ｓhｅet1工作表标签重命名为“量本利分析”,并删除Ｓheet2、Ｓheｅt3空白工作表。
步骤4：制作量本利分析表框架，如图１５-３所示。
如图15-３量本利分析表框架表中,“边际贡献”是指销售收入减去变动成本后的余额,边际贡献又称为“边际利润”或“贡献毛益”等。
单位边际贡献是指单件产品销售收入减去单位变动成本后的余额。
上表中没有数据的单元格为需要运算的数据。
步骤5：计算“单位变动成本”。
点击Ｂ2单元格，选择【公式】菜单中的【自动求和】按钮，在参数输入状态下用鼠标选择B3:Ｂ５单元格区域,然后按回车。
步骤6：计算“固定成本”。
点击B8单元格,用与步骤2相同的方法将Ｂ9：B１1单元格区域求和。
步骤７：计算“变动成本”,变动成本=销量＊单位变动成本。
点击B14单元格，输入公式“=B13＊B2”并回车，步骤8：计算“总成本”，总成本=固定成本+变动成本。
点击B15单元格，输入公式“=Ｂ８+B14”并回车。
步骤９：计算“销售收入”,销售收入=销量*销售单价。
点击Ｂ１6单元格，输入公式“＝Ｂ１3*B1”并回车。
步骤1０：计算“边际贡献”，边际贡献=销售收入=变动成本。
点击B17单元格,输入公式“=B16-B14”并回车。
步骤1１:计算“利润”，利润=销售收入-总成本，点击B１８单元格,输入公式“=B１６-B1５”并回车。
表中“销量”数据B13单元格不用输入数据,计算公式输入完成后的量本利分析表如图15-4所示。
图15-4 输入计算参数任务３:使用【规划求解】工具确定盈亏临界点销量步骤1:加载【规划求解】工具。
由于Exｃel 201０应用程序安装后默认不加载“规划求解”工具,所以，如果要使用【规划求解】功能时,需要手动加载【规划求解】工具，加载“规划求解”工具的方法如下。
①点击【文件】菜单下的【选项】命令,弹出【Excｅl选项】对话框,如图１5-5所示。
图15-5 【Excｅｌ选项】对话框②在【Excel选项】对话框窗口中选择【加载项】按钮,然后点击【转到】按钮,弹出【加载宏】对话框,如图1５－6所示。
图15－６【加载宏】对话框③在【加载宏】对话框窗口中,选中【规划求解加载项】,如图15－7所示。
图15-7 选中【规划求解加载项】④点击【确定】按钮,系统会自动安装相关软件，软件安装完成后会在【数据】选项卡中出现【规划求解】按钮,如图15-8所示。
图1５-8 加载【规划求解】功能后的【数据】数据选项卡步骤２：使用【规划求解】工具求解出盈亏临界点销量值。
点击B18单元格,选择【数据】选项卡,点击【规划求解】按钮,弹出【规划求解参数】对话框，如图１５-9所示。
图1５-9 【规划求解参数】设置对话框，步骤３:在对话框窗口中设置【规划求解参数】,选中【目标值】前的单选按钮,设置目标值为０，点击【通过更改可变单元格】下面的文本框，然后点击Ｂ1３单元格，其他选项保持默认值不变,如图15-10所示。
图15-１0 设置盈亏临界点销量时的【规划求解参数】步骤4:点击【求解】按钮，弹出【规划求解结果】对话框，如图15-１1所示。
图5-１1 【规划求解结果】对话框步骤5:点击【确定】按钮,求解结果如图15-11所示。
图1５-11 【规划求解】结果步骤6：点击B１8单元格，选择【开始】选项卡，在【数字格式】菜单中选择【数字】选项。
完成盈亏临界点销量的最终效果图见图1５-1所示。
任务４:制作量本利分析图步骤1：分别在D1:G1单元格区域的单元格中输入字段名“销量、边际贡献、固定成本、利润”，并设置居中，如图15－１2所示。
图15-12 输入图表字段名步骤２:在Ｄ2:D2单元格区域输入数量“０、２0０、400、…、20０0”。
选择D2单元格,输入“0”,选择D3单元格，输入“200”,同时选择Ｄ２、D3两个单元格，然后用鼠标拖曳选中区域的填充句柄向下填充,直至显示2０００时放开鼠标。
步骤3：计算“边际贡献”。
在E2单元格输入公式“=D2＊$Ｂ＄６”，可以直接输入,或选择Ｅ2单元格,输入“=”符号，然后用鼠标点击D2单元格,再输入“*”符号,再用鼠标点击B6单元格，按F４把B6转换为绝对地址,向下填充。
步骤４：输入“固定成本”。
选择F２单元格,输入“１０７0０0”，或输入单元格引用公式“=＄B＄8”,然后向下填充。
步骤5：计算“利润”。
选择G２单元格，输入公式“=D2＊$B$１－D2*$B＄2-＄B$８”并向下填充,公式的含义是“利润=数量＊销售单价-数量*单件产品人工成本-固定成本”。
完成输入的量本利数据如图15－１3所示。
图１5－１3 量本利数据步骤６:选择D1:G12单元格区域.步骤７:选择【插入】选项卡,点击【散点图】按钮,弹出图表类型选择菜单,如图15-１４所示。
图１5－１4 选择【散点图】类型步骤８：在菜单中选择【带平滑线的散点图】命令,弹出量本利分析图,如图15－1５所示。
图15-15 量本利分析图步骤9：用鼠标右击图例，在弹出的菜单中选择【设置图例格式】命令,弹出【设置图例格式】对话框,如图１5-1６所示。
图１5－16 【设置图例格式】对话框步骤1０：在【设置图例格式】对话框中选择【图例位置】为【底部】,然后点击【关闭】按钮,设置效果如图1５-1７所示。
图15-１7 设置【图例位置】在底部步骤11:在图表中添加“盈亏临界点垂直参考线”。
①准备垂直参考线数据。
选择A2２单元格，输入“盈亏临界点垂直参考线”。
在A２3:A2６单元格区域中输入X轴坐标标注点“８56”，“8５6”为盈亏临界点销量。
在B23:B2６单元格区域中输入Ｙ轴坐标标点“20００00（任意的）、107000（盈亏临界点边际贡献)、0(与X轴的交叉点）、-100000(任意的)”,如图15-18所示。
图15-18 垂直参考线引用数据②选中绘图区，选择【图表工具】下的【设计】选项卡,点击【选择数据】按钮，弹出【选择数据源】对话框,如图15-19所示。
图1５-19 添加图例项③在弹出的【选择数据源】对话框中点击【图例项(系列)】下的【添加】按钮,弹出【编辑数据系列】对话框,如图１５-20所示。
图１５-２０编辑数据系列④设置垂直参考线参数。
点击【系列名称】下的文本框,然后点击A22单元格；点击【X 轴系列值】下面的文本框,然后选择Ａ２3:Ａ26单元格区域；点击【Y轴系列值】下面的文本框,删除其中的内容,然后选择B２3:B2６单元格区域。
设置结果如图15-21所示。
图15-２1 垂直参考线参数设置结果⑤点击【确定】按钮，返回到【选择数据源】对话框窗口，这时【图例项】中多了一项【盈亏临界点垂直参考线】，如图１５－22所示。
图15-２2 添加垂直参考线后的数据源⑥点击【确定】按钮,图形中插入了一条垂直参考线，如图15－2３所示。
图15－２3 插入垂直参考线后的量本利分析图步骤12:调整垂直参考线线型。
①在垂直参考线的任意一个标记点位置点击左键，选中垂直参考线，如图15-24所示。
图1５-24 选择垂直参考线②在选中的垂直参考线标注点上点击右键，在弹出的菜单中选择【设置数据系列格式】命令,弹出【设置数据系列格式】对话框,如图1５－25所示。
图15-25 设置线型【宽度】③在对话框中选择【线型】选项，把线型【线宽】调为“1磅”，然后点击【关闭】按钮,步骤1３:结垂直参考线添加数据标签。
①点击垂直参考线标点（856,１０7０0０)一次,然后再点击一次(这里不是双击），选中该标注点,如图1５－2６所示。
图1５－2６选择垂直参考线的一个标注点②在选中的标注点位置点击右键,然后在弹出的菜单中选择【添加数据标签】命令,则在标注点右侧添加Y轴坐标值，如图１５－2７所示。
图15－27 在垂直参考线与边际贡献交叉点添加数据标签步骤14：调整图形位置,完成后的量本利分析模型及图表如图15-2所示。
