本量利分析模型excel

Finance & Accounting2014 03・财务与会计57温素彬■成本—— 数量—— 利润分析（简称本量利分析）用来研究企业成本、业务量和利润之间的关系，是企业进行利润规划的基本方法。

传统的本量利分析都是基于线性、确定性假设条件下的，而未来的现实经济变量多数是不确定的、随机的。

因此，设计随机条件下的本量利分析模型更接近现实。

而随机条件下的本量利分析涉及复杂的数学计算，难以通过手工计算来实现。

Excel 强大的计算分析功能为开展随机条件下的本量利分析提供了方便，笔者将分（Ⅰ）（Ⅱ）两篇文章分别详细讲解。

一、随机条件下的本量利分析模型基本的本量利分析模型都假设成本与产量总是处于线性关系，即单价不变、单位变动成本不变、固定成本不变。

同时，假设产量、单价、单位变动成本、固定成本等都是确定性变量，决策时这些变量是已知的。

基本的本量利分析模型直观明了，在帮助管理者理解生产经营和利润规划时起到了较好的作用。

但是，由于基本模型是对本量利关系的理想表达，因此在进行现实决策时存在许多缺陷，最重要的两点体现在：一是线性假设经常不符合现实。

本量利分析模型中的相关变量经常是非线性的，例如，单价会随着市场供需关系的不同而发生变化，也会随着购销合同的不同而不同，如批量折扣；单位变动成本和固定成本也并非始终不变，它会随着技术水平、生产效率、材料采购价格、工资率水平等因素的变化而变化。

二是确定性假设不符合现实。

例如，生产和销售经常存在季节性；未来的市场存在随机性，进而单价也具有随机波动性；销量会随着市场供求的变化而存在不确定性；市场的不确定性经常导致单位变动成本的不确定性；等等。

由于现实经济变量存在着大量的不确定性，基本的本量利分析模型显然难以适应管理决策的需要，从而很大程度上限制了其有效性。

基于上述分析，随机条件下的本量利模型为：利润=（单价-单位变动成本）×销量-固定成本，其中，销量、单价、单位变动成本、固定成本都可能是不确定的或随机的。

156第11章 成本分析如何用汉字名称代替单元格地址材料成本汇总表效果图采购成本分析表效果图157　成本分解表如何准确将成本中的固定成分和翻去成分分离是做好本量利分析的关键，一些成本项目的性质比较明确，可以直接划分为固定成本或变动成本，但是有的则比较模糊。

比如电费，虽然电费与产量有关，但是产量为零时电费却并不为零，这说明电费中既有变动成分，又有固定成分。

成本分解表效果图01 新建工作表新建一个工作簿，并在工作簿中重命名其中一个工作表，删除其他的。

输入表格的标题、数据，并设置格式等。

这个工作表中包括了产量和电费的数据。

02 生成散点图选中C2:D14区域，使用上一章介绍的方法插入一个散点图，这次我们要选择的是一个没有线的默认散点图。

定义名字的方法有两种：一种是选定单元格区域后在“名字框”直接输入名字，另一种是选定想要命名的单元格区域，再选择“插入”\“名字”\“定义”，在“当前工作簿中名字”对话框内键人名字即可。

15803 对散点图进行调整对于生成的散点图我们进行一些小的调整，以便最终效果更加便于查看。

首先我们可以删除图例，因为现在只有一个系列。

另外，我们还可以修改一下散点图的标题。

04 设置轴标题如图所示，从“布局”选项卡中选择“坐标轴标题”下的横坐标及纵坐标项目可以添加轴标题，并设置相应的格式等。

添加后可以直接对轴标题进行编辑。

如何在公式中快速输入不连续的单元格地址05给散点添加趋势线右键单击散点图中的任意一个散点，从弹出的快捷菜单中选择“添加趋势线”。

06趋势线的设置在弹出的对话框中，选择“线性”，并勾选“显示公式”。

07分解成本确定后生成最终的散点图。

可以看到，在散点图上出现了线性趋势线和一个二元一次方程。

方程中的截距代表固定成本，即505.4；斜率代表单位变动成本，即1.9942。

知识点：趋势线Excel的图表中，散点图、折线图、面积图、条形图、柱形图、股份图和气泡图中都可以添加趋势线，通常适合使用趋势线的的图表有两类，一是成对的数字数据，即典型的散点图中使用的数据；二是基于时间的数据，比如折线图、散点图、面积图等使用的数据。

运⽤Excel软件计算NPV（净现值）和IRR（内部收益率）及投资回收期、折现率、财务净现值的解释运⽤Excel 软件计算NPV(净现值)和IRR(内部收益率)及投资回收期、折现率、财务净现值的解释如何⽤Excel 软件计算NPV(净现值)和IRR(内部收益率)投资决策时，可以借助Excel 软件通计算NPV(净现值)和IRR(内部收益率)两个重要的投资指标。

操作步骤第⼀步：启动Excel电⼦表格，在菜单栏“插⼊”⾥点击启动“函数”。

第⼆步：在粘贴函数对话框⾥“函数分类”选择“财务”，“函数名”选择“NPV”。

第三步：输⼊相关参数，求出NPV。

实例演⽰某项⽬投资期为6年，各年末净现⾦流量分别为-500、200、200、200、200、100，该项⽬基准收益率为10%，试通过NPV 法分析该项⽬是否可⾏？在Rate栏内输⼊折现率0.1，在Value栏内输⼊⼀组净现⾦流量，并⽤逗号隔开-500, 200, 200, 200, 200, 100。

也可单击红⾊箭头处，从Excel⼯作簿⾥选取数据。

然后，从该对话框⾥直接读取计算结果“计算结果= 178.2411105”。

或者点击“确定”，将NPV的计算结果放到Excel⼯作簿的任⼀单元格内。

⼤⼤提⾼了⼯作效率。

重复本⽂中的第⼀步和第⼆步，在第⼆步中的“函数名”中选择“IRR”，单击“确定”，弹出对话框遥在Value栏内输⼊⼀组净现⾦流量，两头⽤“{}”，中间⽤逗号隔开，即“{-500, 200, 200, 200, 200, 100}”，也可点击对话框中的红⾊箭头处，从Excel⼯作簿⾥直接选取。

然后，即可从该对话框中直接读出计算结果，或者单击“确定”，将IRR的计算结果放到Excel⼯作簿中的任⼀单元格内。

根据显⽰，读出IRR=25.5280777%>10%，即该项⽬的内部收益率⼤于项⽬的基准收益率，因此该投资项⽬可⾏。

从上⾯的例⼦可以看出，利⽤Excel来计算NPV和IRR，会起到事半功倍的效果，轻松之间就省去了繁杂⼿⼯计算的烦恼。

广西民族大学实验报告课程名称：《管理会计》实验项目：《本量利分析模拟实验》学院：商学院专业：会计学年级班级：2013级（一班）学号：学生姓名：谭长远指导教师：郭亮《管理会计》实验报告一、实验基本概况实验项目：本量利分析模拟实验实验方法：设计性实验实验地点：逸夫楼303实验年度：指导教师：郭亮二、实验目的及要求（一）实验目的：本量利分析概述；本量利分析模型的创建；本量利分析模型的应用（二）实验要求：（1）掌握日记账的登记规则、登记方法和错账的更正方法；（2）掌握各种明细分类账的登记规则、登记方法和错账的更正方法；（3）掌握总分类账的登记方法，进一步理解各种账户的性质，明确不同账户借贷方记载的内容，理解总账和明细分类账平行登记的概念。

三、实验的流程1、ABC公司是一家专门生产某名牌衬衫的公司。

其2001年度公布的会计报表如下：表损益表（简表）2001年12月31日单位：万元其它财务信息及统计信息如下：（1）2001年销售明细表中记录该年销售数量为100万件，据销售部门预测，2002年的销售数量可达120万件。

（2）由于销售增加，需增加固定资产支出220万元，并因此使公司年折旧额达30万元。

（3）经营和管理费用的固定部分增加20万元。

（4）销售成本及经营和管理费用中的变动费用与销售收入的比例不变。

四、实验要求：（1）根据上述资料，用本·量·利模型对该公司2002年度息税前利润进行预测。

（2）运用本·量·利模型预测该公司2002年的保本点的销售量、销售收入。

（3）分析销售单价、销售量、单位变动成本、固定成本变动对息税前利润的影响（4）制作敏感分析图2、实验原理本·量·利分析是利润预测的基本方法之一，它是在成本性态研究和盈亏平衡分析基础上，根据成本、产销量与利润的关系确定计划期间目标利润总额的一种方法。

本·量·利基本模型：P = p·x –a –b·xP：息税前利润p：单位x：销售量a：固定成本b：单位变动成本（一）本量利分析1．利润的计算息税前利润＝销售收入－固定成本－变动成本总额＝单价×销售量－固定成本－单位变动成本×销售量2．盈亏平衡点x的计算（大P=0）盈亏平衡点销售量＝固定成本/（单价－单位变动成本）3．盈亏平衡点px的计算盈亏平衡点销售额＝盈亏平衡点销售量×单价（一）本量利敏感分析表本量利关系中的敏感性分析，主要是研究销售单价、单位变动成本、固定成本和销售量这些因素变动对盈亏临界点和目标利润的影响程度。

EXCEL财务教程-本量利分析表,本量利分析样图本量利在财务分析中占有重要作用，通过设定的销量、变动成本、固定成本和售价可以推算出盈亏平衡销量及收入。

本章以数据表和分析图的形式展示某一数据发生变化时盈亏线的变化情况。

本量利就是对成本、销量和利润的分析。

成本、销量和利润三者之间的变化关系是决定企业是否盈利的关键，本量利分析是定量分析出企业成本、销量和利润三者之间的变化关系。

盈亏平衡点指标是企业盈亏分界线，它也是由本量利分析引出。

这里我们需要用到一些公式：成本＝单位成本×产量＋固定费用收入＝售价×产量在过到盈利平衡点时，成本＝收入。

此时的盈亏平衡量＝固定费用/。

固定费用保持不变，售价和单位成本可以变动，因此盈亏平衡量是变动的。

根据盈亏平衡量，可以求得，盈亏平衡收入＝盈亏平衡量×售价。

步骤01 新建工作表并输入字段名新建一个工作簿，并在工作簿中重命名其中一个工作表，删除其他的。

输入表格的标题、数据，并设置格式等。

步骤02 输入数据并设置格式在B3:B18区域输入产量数据，并新建立“固定费用”、“售价”、“单位成本”等新的数据区域以备后用。

步骤03 创建成本公式在C3单元格输入公式：=($K$26*B3+$C$28)/10000，并将公式复制到C4:C18区域。

步骤04 创建收入公式在D3单元格输入公式：=($H$26*B3)/10000，并将公式复制到D4:D18区域。

步骤05 创建利润公式在E3单元格输入公式：=D3-C3，并将公式复制到E4:E18区域。

步骤06 设置盈亏平衡线横坐标数据在C2 0单元格输入标题“盈亏平衡线辅助数据”。

在C2 2单元格输入公式：=ROUND(C28/(H26-K26),2)。

并在C23:C25输入公式：=C22。

步骤07 设置盈亏平衡线纵坐标数据在D22单元格输入“1800”；在D23单元格输入公式：=(C23*H26)/10000；在D24单元格输入“0”；在D25单元格输入“-100”。

《Excel在财务中的运用》课程教学标准（课程代码：4208030075）（适用：三年制高职大数据与会计专业630302）一、制定课程标准的依据本标准依据会计专业人才培养目标与培养规格要求、出纳、会计、会计主管等工作岗位国家职业标准以及学生就业面向的职业岗位职责，经过与企业专家反复研讨与论证共同制定，用于指导《Excel在财务中的运用》课程建设与课程教学。

二、课程性质与作用《Excel在财务中的运用》课程是高职院校大数据与会计专业的专业选修课程，是EXCeI应用与财务会计类专业相关理论知识相融合的一门课程,属于复合型、应用型课程。

本课程服务会计专业升级和数字化改造，主要任务是培养学生利用Excel应用技术从事财务与会计工作的能力，对实现财务会计类专业人才培养目标和保证人才培养规格具有重要支撑作用。

Excel是现代办公中经常使用的一种数据处理软件，功能强大，应用灵活，使用方便，是企业信息化应用的基础和支撑，不仅能够满足个性化的数据分析需要，也能满足复杂多变环境下的大量数据分析需要，在国际上有着“通用财务软件”之美誉，是提高工作质量、减轻劳动强度的重要办公利器。

三、本课程与其他课程的衔接关系表1本课程与其它课程的衔接关系四、课程学习目标（一）知识目标进一步巩固财务会计、成本会计、财务管理、财务分析和税法等知识，熟知EXCeI应用的基本知识。

（二）能力目标掌握Excel在财务与会计中应用的基本方法和技巧，具备利用Excel软件解决会计工作实际问题的能力。

（三）素质目标培养良好的思想品德和政治素养，遵纪守法重操守，爱岗敬业讲诚信，勤学善为练真功，树立责任意识、规范意识、合作精神和严谨细致作风，树立工作质量意识和高效办公的思想，养成积极主动思考问题和及时解决问题的习惯。

五、课程总体设计表2(Excel在财务中的运用》课程总体设计表1情境（任务、项目等）具体设计表情境（任务、项目等）具体设计表情境（任务、项目等）具体设计了解资金需要量预测知识，熟悉资金成本计算方法，利用建立资金表5情境（任务、项目等）具体设计表6情境（任务、项目等）具体设计七、教学基本条件（一）教学团队基本要求1.专任教师担任本课程的专任教师应该是具有行业企业工作经历的双师型教师，并每年参加企业实践活动，从事多年的财会专业课程的教学，积累了丰富的教学经验，具有较完备的理论及实践知识结构。

本量利分析模型设计、分析与应用本量利分析法是财务管理中的一项重要内容,主要用于研究产品的成本、业务量和利润三者之间变动关系, 通过对以上各因素的调整, 寻找一条增加利润或实现目标利润的途径。

本量利的基本方程式非常简单,表达为:利润= 销售收入- 总成本= 销售收入- ( 变动成本+ 固定成本)= (单价- 单位变动成本) ×销售量- 固定成本在这个方程式当中有5 个相互联系的变量( 因素) :利润、单价、单位变动成本、销售量、固定成本,只要给定其中任意 4 个变量值, 便可以求出最后一个变量的值。

运用Excel 表格设计本量利分析模型,基本原理就是依据由本量利基本方程式建立起来的5 个变量( 或者因素) 之间的相互关系,在Excel 表格中定义出函数后,则既可以对函数中的一个变量( 因素) 进行变动分析,也可以对多个变量( 因素) 同时进行变动分析,成为本量利分析过程中的一件利器。

所谓excel图表本量利分析模型其特点是根据变量之间数量关系在excel工作表中建立本量利分析的基本模型。

同时利用excel工作表的图表显示功能生成能够以图表方式动态地显示本量利变量关系的分析模型"。

一、本量利分析模型的创建第一步,将相关资料输入Excel工作表。

在Excel工作表中输入创建本量利分析基本模型的相关资料。

如图1图1第二步,计算销售总额、成本总额、利润总额和保本点。

（这里根据表1进行计算）用鼠标单击单元格C7,光标变为“| ”时输入“= C3 *C6”,按回车键得到销售总额。

用鼠标单击单元格C8,光标变为“| ”时输入“= C3* C5 + C4”,按回车键得到成本总额。

用鼠标单击单元格C9,光标变为“| ”时输入“=C7 - C8”,按回车键得到销售总额。

用鼠标单击单元格C11,光标变为“| ”时输入“=C4 / (C6 - C5) ”,按回车键得到保本点。

然后在C13单元格输入IF语句“= IF (C9 > 0,‘利润总额’& ROUND(C9, 0) ,‘亏损额’& - ROUND (C9, 0) ) ”。

【EXCEL】量本利分析图（销售盈亏平衡分析）量本利分析也称盈亏平衡分析或保本分析，其是根据对成本、销售收⼊和销售数量三者之间相互关系的综合分析来掌握盈亏变化的规律，指导企业选择能够以最⼩的成本⽣产和销售出最多的产品，并使企业获得最⼤利润的经营⽅案。

盈亏平衡分析图的具体制作步骤如下。

Step1：在⼯作表中绘制两个滚动条控件，分别设置控件的单元格链接为G4（数值范围为3～20）和G5（数值范围为1～100）。

如下图所⽰。

再按如下关系式设计图中的数据表格，表达销售收⼊与单价、销量的关系。

销售收⼊=单价*销量Step2：在B1：B2单元格区域输⼊固定成本和单位变动成本两个已知的数据，并按下列关系式计算相关的数据，结果如下图所⽰。

总变动成本=单位变动成本*销量总成本=固定成本+总变动成本销售收⼊=单价*销量利润=销售收⼊-总成本盈亏平衡销量=固定成本/（单价-单位变动成本）盈亏平衡销售收⼊=单价*盈亏平衡销量Step3：在B13和C13单元格分别输⼊“最⼩销量”为1000，“最⼤销量”为100000，在B14：C16单元格区域输⼊公式，以计算“销售收⼊”、“总成本”和“利润”，如下图所⽰。

设计完成的盈亏平衡分析表格如下图所⽰。

Step4：以A13：C16单元格区域为源数据，使⽤图表向导创建⽆数据点折线散点图，并设置“系列产⽣在”为“⾏”，结果如下图所⽰。

Step5：设置数值（X）轴坐标轴的刻度“最⼤值”为100000。

Step6：将图例移动到绘图区的下⽅，删除数值（Y）轴主要⽹格线，结果如下图所⽰。

Step7：单击图表，再单击菜单栏上的“图表”——“源数据”命令，在弹出的“源数据”对话框的“系列”选项卡中添加⼀个新系列，“X值”的源数据引⽤B7单元格，“Y值”的源数据引⽤B8单元格，名称为“盈亏平衡点”，然后单击“确定”按钮关闭对话框。

Step8：再添加⼀个新系列，“X值”的源数据引⽤F5单元格，“Y值”的源数据引⽤F6单元格，名称为“平衡点指标”。

EXCEL在财管中应用复习资料一、单项选择题（每小题1分，共20分）1、关于财务函数IPMT，下列说法错误的是（）。

A. 其语法为IPMT(Rate , Per，Nper, Pv, Fv, Type)B. Rate是固定值，其计量基础应当与Nper相同C. 在所有参数中，支出的款项表示为负数；收入的款项表示为正数D. IPMT为归还的贷款中本金部分2、符合表格结构、数据清单规范要求的是（）。

A. 多个数据库放到一个工作表上B. 不在数据清单中使用合并单元格C. 在数据库中间放置空白行或空白列在所有参数中D. 任意两行的内容完全相同3、下列属于文本连接符的是（）。

A.“:”（冒号）B.“,”（逗号）C.“&”D.“'”（单引号）4、公式：Index(A2:E5,2,2)运算结果返回的是（）的值。

A.A2B.B3C.D3D.E55、关于查找函数下列说法错误的是（）。

A. Lookup，从单行或单列区域查找指定值，返回指定一列或一行中的数据。

如果是数组形式，在数组第一行或第一列中查找指定的值，并返回数组最后一行或最后一列内同一位置的值B. Lookup、Vlookup、Hlookup都可以实现精确查找C. Hlookup，只能在区域内第一行查找，可以返回所有区域内的任意行的数据D. Vlookup，只能在区域内第一列查找，可以返回所有区域内的任意列的数据，如果查找值有两个以上，则返回最后一个数据6、在Excel中使用公式，当多个运算符出现的公式中时，由高到低各运算符的优先级是（）。

A. 括号、%、?、乘除、加减、&、比较符B. 括号、%、?、乘除、加减、比较符、&C. 括号、?、%、乘除、加减、比较符、&D. 括号、?、%、乘除、加减、&、比较符7、在Excel中输入现金会计科目编码1001，正确的输入是（）。

A.’1001B.”1001”C.1001D.#10018、下列指标中，属于非贴现指标的是（）。

谈本量利模型在企业经营决策中的应用作者：张玉兰来源：《财经界·学术版》2015年第17期摘要：本量利分析是指用数学化的会计模型来揭示成本、销售、利润等变量之间的内在规律性联系的定量分析方法。

本文结合实际以农产品加工企业为例，阐述本量利模型在经营决策中的具体应用，并介绍了本量利模型的具体表现形式。

关键词：本量利模型经营决策应用一、本量利模型在企业经营决策中的具体应用（一）本量利分析法用于企业长期投资决策本量利的分析方法可给企业的投资和决策以及后期的分析方面提供一个非常具有普遍意义的方法。

它的数学模型是：P=（p-b）x-a，不难看出影响企业净的利润（P）的因素有单价（p），还有单位变动成本（b）以及销售量（x），还有固定成本（a），那么哪一个因素影响最大，而其中的哪一个因素影响又最小，各个因素的变化极限又是多大，这些都是本量利关系敏感分析的关键。

它是从成本的角度出发，然后求出这项投资中所需要达到的每年的最低销售量，然后再和企业现实的销售量做出对比，就可以明确的判断这个方案是否可行，是否是最优的方案。

（二）用于开停机生产决策对于亏损产品，绝不能简单地予以停产，而必须综合考虑企业各种产品的经济状况、生产能力的利用及有关因素的影响。

对于这类问题的决策分析可以采用贡献毛利分析法来解决。

也就是在计算分析时，只需要弄清亏损产品是否能提供边际贡献，能够提供多少贡献毛益。

假定某企业本年产销甲、乙、丙三种产品，年终按全部成本法算出三种产品的成本与各项产品的销售收入比较，甲产品净利5，000元，乙产品净利1，000元，丙产品净亏2，000元，全部净利合计为4，000元。

固定成本总额按各产品的销售额比例摊销。

根据所给的资料可知，丙产品全年净亏2，000元，表面看来，为了减少亏损，增加盈利，应停产丙产品，但让我们用贡献毛益分析法来分析一下。

根据有关资料编制贡献毛益及净利计算表，如下所示：结论：只要亏损产品能提供贡献毛益就不应该停产。

Excel做量本利分析 什么是量、本、利分析呢？它是运用数学计算和图像法研究产品销量、成本及售价的变化，对目标利润影响的一种分析方法，它作为财务分析的有效的手段，在企业管理中得到广泛应用。

本文试用Excel对量、本、利关系做一解析。

盈亏平衡点的测定 盈亏平衡点指某种产品的销售收入恰好等于总成本时的销售量或销售额，在该点，既无盈利，也无亏损。

盈亏平衡点的测定，需要根据产品的有关数据资料，对量、本、利之间的关系进行分析。

例1：某产品固定成本为12000元，单位可变成本为100元，售价为210元，试确定盈亏平衡点的销量。

计算方法如下：图1 如图1所示，在A2、B2、C2中分别键入固定成本、单位可变成本、售价，在单元格D2中键入公式“=A2/(C2-B2)”，则D2中显示的数据即为所求，可知盈亏平衡点销量为109单位。

盈亏平衡图作法如下： 1、取销售量为0、20、40……180，分别键入A5、A6……A14， B5中键入公式“=12000+100*A5”、C5中键入公式“=210*A5”。

将B5、C5中的公式分别复制到B6:B14、C6:C14，得到数据如图2所示。

2、选择A4:C14，单击“插入-图表…”(或单击工具栏[图表向导]按钮)，显示“图表类型”对话框。

图2 3、选择“XY散点图”及其子图“无数据点平滑线散点图”，单击[下一步]，显示“图表数据源”对话框。

4、选择“系列产生在列”，单击[完成]，显示图表如图3所示。

图3 图中X轴为销量，Y轴为成本，两直线中较平缓的一条为总成本线，较陡峭的一条为总收入线，两直线交点为盈亏平衡点，该点X轴的坐标为109，即当销量为109单位时盈亏平衡。

位于平衡点左侧两直线所夹区域为亏损区，平衡点右侧两直线所夹区域为盈利区。

目标利润下销量的测定 当产品的固定成本、单位可变成本、售价已知时，如果要实现一定的目标利润，可通过量、本、利分析确定相应的销量。

例2：上例产品固定成本、单位可变成本、售价不变，试测定销量达到多少时可获目标利润5000元。

假设有一个制衣厂，生产的裤子单位售价100元，其中生产一件产品的人工费10元， 材料费15元， 制造过程中所耗费的水电费10元(当然可能会有其他随可做表如下： 其中“单位可变成本” 即为 直接人工，直接材料，可变制造费 相加之和上表B2=B3+B4+B5, 请自己加公式。

B7解释一下，B7等于销售单介B1减单位可变成本B2, 这个值在会计注意"单位"两个字，是销售一件产品贡献。

在实际企业生产过程中，还有部分费用是不随产量变动而变动的，我们称之为固定费用。

象机器设备的动，意思是说卖不卖东西，这部分费用依然产生。

我们暂且估计数据如下（本故事纯属虚构，如有雷同，纯属巧合）1400上表B8=B9+B10+B11假设销量1000条，继续做表上表，B14=B13*B7, B15=B13*B1, B16=B8+B13*B2, 解释一下B19盈亏平衡销量当企业有销量时，单位边际贡献随之增加，只有所有售出产品的边际贡献总值超过企业的固定成本时，际贡献总值等于固定成本。

因为边际贡献总值=单位边际贡献乘 X 销量，所以盈亏平衡时的销量=固定成本/单位以下内容为EXCEL盈亏损模型所需要的建的辅助区域， 至于为什么要建这个区域，等做好之后您再慢慢÷其中： C1到F1标题手工填入，C2销量输入0, C2为0，D2肯定也为0, E2输入公式 =B8, F2输入公式: =D2-E2C3输入1500, 假设这是企业最大销量(闲少？那您填多大我都不拦着)， D3输入公式: =C3*B7, E3输入公式: =B8再加两条参考线：其中：C7到C10的公式都是: =B19, D8公式：=B8, C13到C17的公式都是: =B13, D14公式：=B14, D15公至于为什么输入这些数字, 下边解释。

至于为什么要做参考线也下边解释。

现在只需要明白一点：拿“盈亏点销量垂直参考线”来说，如果把C7到C10的值放图表上的X轴，D7到D10为Y轴的话， 因为X值相同，这“当前销量垂直参考线”中的数据就是垂直于X轴的直线上的五个点。

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

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

创建Excel工作簿在单元格中使用公式SUM函数的使用建立量本利分析模型使用规划求解工具求解盈亏临界点制作量本利分析图表在图表中添加盈亏临界点垂直参考线图表的修饰项目实施效果图本项目完成后的效果图如下：图15-1 盈亏临界点销量图15-2 量本利分析效果图项目实施方案任务1：理解量本利分析的概念量本利分析是产量（或销售量）、成本、利润之间依存关系分析的简称，也称VCP分析（Volume -- Cost --Profit Analysis）。

它着重研究销售数量、价格、成本和利润之间的数量关系。

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

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

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

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

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

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

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

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

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

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