下载文档原格式
项目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．变动成本的“变动”强调特定范围内，成本总额随业务量的增减变化而正比例变化，但单位产品的变动成本不随业务量的增减变化而变化酌量性变动成本酌量性变动成本，是指通过管理决策行动加以改变的变动成本。
如销售佣金、技术转让费等类别细分类含义要点阐释混合成本半变动成本半变动成本，是指通常有一个初始量，类似于固定成本，在这个初始量的基础上随业务量的增长而正比例增长，又类似于变动成本。
项目 15 制作量本利分析模型量本利分析法，全称为产量成本利润分析，也叫保本分析或盈亏平衡分析，是通过分析生产成本、销售利润和产品数量这三者的关系，掌握盈亏变化的规律，指导出企业选择能够以最小的成本生产最多产品并可使企业获得最大利润的经营方案。
15.1 学习目标通过本项目的学习，可以学会使用Excel 建立量本利分析模型的方法，学会建立盈亏平衡图表的方法，在表格制作的过程中学习并掌握以下Excel 功能的综合运用。
创建Excel 工作簿在单元格中使用公式SUM函数的使用建立量本利分析模型使用规划求解工具求解盈亏临界点制作量本利分析图表在图表中添加盈亏临界点垂直参考线图表的修饰15.2 项目实施效果图本项目完成后的效果图如下：门4LE1销售单吟 1702 里儘变动戌本45 3 单件产品人工成本 2i 4 卑件土品材料费11 5单件产吕苴它制造费 10.甲位辿际贡離12513 固定成本 1D700D 9蒼理人灵工资 E600D rio 资产折E 資 30000 n 固左稻整豈用21000 1213销臺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 24200 25D&0. 00 107000 -E2000 11400 旳防仇DO L07000 -57000 10600 75 DOO. 00 107000 -32D0D125900 iDOtm 00 107000 -7 DOO1000 129000. 00 10 TO 00 13500 107000 1200 150D0D. 00 107000 43D0DS6d0r 14T)Ci 175000. 00 107000通 30000 1!&00 200000, 00 LOTOOQ &3000 21 (JOO1300 225090. CO L07000 1 LH JU J200CI 250000. 00107000 14n iri i22 盘亏临界点垂直詩考线23836 2Q0CQCP4 RM1QB1QQ25◎药TQ0QS282916171R19170 销虽边际贡尅圄定反本556 3囲如1455202山玄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空白工作表。
AB 1甜售里价 1九2皀位变动成本車件产品人工成本 244_ 更件产品苻料帝11 单件产品其它制造費 106鱼位边际贡就7e 固走成本 9营理人吊匚卷 5500D10資产折旧费 3000D 11固定销售费甲2100D1213 14 15 1C 17 16本入<肚本收贡>由欧隹阡刑铸变总钧如图15-3量本利分析表框架边际贡献又称为“边际利润”1 Z g4匸a 7 8 9 10 L1 L2 1：14让ie 17销售羊检单柱变动咸本单件产品人工成丰里件产品材料昔单件产韶耳它制苜费单位虫际贡研固定或本首理人员工召资产折旧费固定誚售费用威本tt贡量动咸售怖润销亜总ffi迦刑170452d1110125109000560003000021000107000j-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 B1销售单忻1702单啦变动戒本453单件产品人工咸本244审件产品村料竇115单件产品只它制洁吏丄06里位边际予就12579匿正应本1070003首理人员工资5000010冏产折旧苗30000L1国定销售费用 a oooL2L5上退IE85614变动成本38&2U15总成本145520工销售收人145520边际质献107000L6利润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 F1边际矗臥1BI走或本利间此、U. U0L070L0-1157000 3里件产刚人工戍本2425000,00L070D002DOD411叔00L07000-57D0D5单fl亠品具它制造茂1160075000.00L070DO-82D0D512E0K103000. 00L07000■700071000125000. 00L0700018D0Dg10700012001BOOOX 001070004300 J9誉理人员工资14M IZ J OCO. CO 1.07000&aooo10资产打旧瑁3000 C1000203000* 00L070DC98000111円工询售宴用2100C】徊225000.00L07000 1 LB0001223 CW2590C0.00 1.07000143000图15-13量本利数据步骤6:选择D1:G12单元格区域.步骤7:选择【插入】选项卡，点击【散点图】按钮，弹出图表类型选择菜单，如图15-14所示。
