本量利分析模型excel
管理会计中Excel的高级应用__省略_的本量利分析模型设计与应用_I_温素彬
Finance & Accounting2014 03・财务与会计57温素彬■成本—— 数量—— 利润分析（简称本量利分析）用来研究企业成本、业务量和利润之间的关系，是企业进行利润规划的基本方法。
传统的本量利分析都是基于线性、确定性假设条件下的，而未来的现实经济变量多数是不确定的、随机的。
因此，设计随机条件下的本量利分析模型更接近现实。
而随机条件下的本量利分析涉及复杂的数学计算，难以通过手工计算来实现。
Excel 强大的计算分析功能为开展随机条件下的本量利分析提供了方便，笔者将分（Ⅰ）（Ⅱ）两篇文章分别详细讲解。
一、随机条件下的本量利分析模型基本的本量利分析模型都假设成本与产量总是处于线性关系，即单价不变、单位变动成本不变、固定成本不变。
同时，假设产量、单价、单位变动成本、固定成本等都是确定性变量，决策时这些变量是已知的。
基本的本量利分析模型直观明了，在帮助管理者理解生产经营和利润规划时起到了较好的作用。
但是，由于基本模型是对本量利关系的理想表达，因此在进行现实决策时存在许多缺陷，最重要的两点体现在：一是线性假设经常不符合现实。
本量利分析模型中的相关变量经常是非线性的，例如，单价会随着市场供需关系的不同而发生变化，也会随着购销合同的不同而不同，如批量折扣；单位变动成本和固定成本也并非始终不变，它会随着技术水平、生产效率、材料采购价格、工资率水平等因素的变化而变化。
二是确定性假设不符合现实。
例如，生产和销售经常存在季节性；未来的市场存在随机性，进而单价也具有随机波动性；销量会随着市场供求的变化而存在不确定性；市场的不确定性经常导致单位变动成本的不确定性；等等。
由于现实经济变量存在着大量的不确定性，基本的本量利分析模型显然难以适应管理决策的需要，从而很大程度上限制了其有效性。
基于上述分析，随机条件下的本量利模型为：利润=（单价-单位变动成本）×销量-固定成本，其中，销量、单价、单位变动成本、固定成本都可能是不确定的或随机的。
财务管理必会excel之成本分析
156第11章 成本分析如何用汉字名称代替单元格地址材料成本汇总表效果图采购成本分析表效果图157 成本分解表如何准确将成本中的固定成分和翻去成分分离是做好本量利分析的关键，一些成本项目的性质比较明确，可以直接划分为固定成本或变动成本，但是有的则比较模糊。
比如电费，虽然电费与产量有关，但是产量为零时电费却并不为零，这说明电费中既有变动成分，又有固定成分。
成本分解表效果图01 新建工作表新建一个工作簿，并在工作簿中重命名其中一个工作表，删除其他的。
输入表格的标题、数据，并设置格式等。
这个工作表中包括了产量和电费的数据。
02 生成散点图选中C2:D14区域，使用上一章介绍的方法插入一个散点图，这次我们要选择的是一个没有线的默认散点图。
定义名字的方法有两种：一种是选定单元格区域后在“名字框”直接输入名字，另一种是选定想要命名的单元格区域，再选择“插入”\“名字”\“定义”，在“当前工作簿中名字”对话框内键人名字即可。
15803 对散点图进行调整对于生成的散点图我们进行一些小的调整，以便最终效果更加便于查看。
首先我们可以删除图例，因为现在只有一个系列。
另外，我们还可以修改一下散点图的标题。
04 设置轴标题如图所示，从“布局”选项卡中选择“坐标轴标题”下的横坐标及纵坐标项目可以添加轴标题，并设置相应的格式等。
添加后可以直接对轴标题进行编辑。
如何在公式中快速输入不连续的单元格地址05给散点添加趋势线右键单击散点图中的任意一个散点，从弹出的快捷菜单中选择“添加趋势线”。
06趋势线的设置在弹出的对话框中，选择“线性”，并勾选“显示公式”。
07分解成本确定后生成最终的散点图。
可以看到，在散点图上出现了线性趋势线和一个二元一次方程。
方程中的截距代表固定成本，即505.4；斜率代表单位变动成本，即1.9942。
知识点：趋势线Excel的图表中，散点图、折线图、面积图、条形图、柱形图、股份图和气泡图中都可以添加趋势线，通常适合使用趋势线的的图表有两类，一是成对的数字数据，即典型的散点图中使用的数据；二是基于时间的数据，比如折线图、散点图、面积图等使用的数据。
用excel表格进行投入产出分析
饲料成本
羔羊育肥时间（天） 180 饲料成本（元） 体重（公斤）元/每公斤 105 25 10
3月7日
8
10
3
10
150
销售价格 75 -0.1 售价变动率 0
单位变动成本 50
验证 5000 5000 5000 15000
点销售收入
只羔羊成本（元）
饲料母羊成本/只 238.8333 120450 163650
销售价/只 250
5
量、本、利分析的基本模型
销售收入 销售成本 变动成本 固定成本 67500 50000 45000 目标利润 单位变动成本 0 50 一、基本模型 变动成本率 5000 900 0.666666667 400 单位变动成本变动率 固定成本变动额 0 0 二、因素变动分析模型 1、实现目标利润要求的单位变动成本 69.44444444 2、如何确定销售价格 55.55555556 3、有关因素变动后的盈亏临界点销售量 200 4、有关因素变动后的盈亏临界点销售收入 15000 销售量
1、销售利润 2、边际贡献 17500 22500 3、盈亏临界点销售量 200 4、盈亏临界点销售收入 15000 5、实现目标利润销售量 200 6、实现目标利润销售额 15000 每只母羊年产活羔（只） 3
羔羊成本
每只Байду номын сангаас羊年饲养成本（元） 401.5 每只羔羊成本（元） 133.8333333
运用Excel软件计算NPV（净现值）和IRR（内部收益率）及投资回收期、折现率、财务净现值的解释
运⽤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财务教程-本量利分析表,本量利分析样图
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在财务中的运用》课程教学标准
《Excel在财务中的运用》课程教学标准（课程代码：4208030075）（适用：三年制高职大数据与会计专业630302）一、制定课程标准的依据本标准依据会计专业人才培养目标与培养规格要求、出纳、会计、会计主管等工作岗位国家职业标准以及学生就业面向的职业岗位职责，经过与企业专家反复研讨与论证共同制定，用于指导《Excel在财务中的运用》课程建设与课程教学。
二、课程性质与作用《Excel在财务中的运用》课程是高职院校大数据与会计专业的专业选修课程，是EXCeI应用与财务会计类专业相关理论知识相融合的一门课程,属于复合型、应用型课程。
本课程服务会计专业升级和数字化改造，主要任务是培养学生利用Excel应用技术从事财务与会计工作的能力，对实现财务会计类专业人才培养目标和保证人才培养规格具有重要支撑作用。
Excel是现代办公中经常使用的一种数据处理软件，功能强大，应用灵活，使用方便，是企业信息化应用的基础和支撑，不仅能够满足个性化的数据分析需要，也能满足复杂多变环境下的大量数据分析需要，在国际上有着“通用财务软件”之美誉，是提高工作质量、减轻劳动强度的重要办公利器。
三、本课程与其他课程的衔接关系表1本课程与其它课程的衔接关系四、课程学习目标（一）知识目标进一步巩固财务会计、成本会计、财务管理、财务分析和税法等知识，熟知EXCeI应用的基本知识。
（二）能力目标掌握Excel在财务与会计中应用的基本方法和技巧，具备利用Excel软件解决会计工作实际问题的能力。
（三）素质目标培养良好的思想品德和政治素养，遵纪守法重操守，爱岗敬业讲诚信，勤学善为练真功，树立责任意识、规范意识、合作精神和严谨细致作风，树立工作质量意识和高效办公的思想，养成积极主动思考问题和及时解决问题的习惯。
五、课程总体设计表2(Excel在财务中的运用》课程总体设计表1情境（任务、项目等）具体设计表情境（任务、项目等）具体设计表情境（任务、项目等）具体设计了解资金需要量预测知识，熟悉资金成本计算方法，利用建立资金表5情境（任务、项目等）具体设计表6情境（任务、项目等）具体设计七、教学基本条件（一）教学团队基本要求1.专任教师担任本课程的专任教师应该是具有行业企业工作经历的双师型教师，并每年参加企业实践活动，从事多年的财会专业课程的教学，积累了丰富的教学经验，具有较完备的理论及实践知识结构。
第8章 Excel在盈亏平衡分和经济订货量中应用
11-13
8.1.3盈亏平衡分析模型举例
4．多因素变动盈亏平衡分析表和分析图的建立 （1）多因素变动盈亏平衡分析表的建立
11-14
8.1.3盈亏平衡分析模型举例
4．多因素变动盈亏平衡分析表和分析图的建立 （2）多因素变动盈亏平衡分析图的建立
11-15
8.2 Excel经济订货量模型
2．经济订货量模型举例 （1）基本的经济订货量模型举例
11-24
8.2.2经济订货量
2．经济订货量模型举例 （1）基本的经济订货量模型举例 当其他因素都有变化时，可用盈亏平衡分析中的多变量
分析模型设计滚动条进行控制选择。
11-25
8.2.2经济订货量
2．经济订货量模型举例
（2）存货陆续供应和耗用情况下的经济订货量模型举例 在【例8-2】中，假设企业所需的原材料是陆续供应和耗用的， 送货期内每日平均送货量为1000公斤，每日平均耗用量为100公斤， 则可建立存货陆续供应和耗用情况下的经济订货量模型，如图832所示。在此模型中，E10单元格中的公式为 “=SQRT(2*E4*E6/((1-E9/E8)*E5*E7))”，E11单元格中的公式为 “=SQRT(2*E4*E6*E5*E7*(1-E9/E8))”，E12单元格中的公式为 “=(E11-34152.6)/34152.6”（34152.6为存货陆续供应和耗用情况下 各个因素不发生变动时的总成本）。
8.1.1盈亏平衡分析的基本模式
盈亏平衡分析（breakeven analysis）也称本量利分析或 保本分析，是根据产品的业务量（产量或销售量）、成本、 利润之间的相互制约关系的综合分析来掌握盈亏变化的的规 律，指导企业选择能够以最小的成本生产和销售出最多的产 品，并使企业获得最大利润的经营方案。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
本量利分析模型excel
什么是本量利分析模型？
本量利分析模型是一种管理工具，用于帮助企业分析其产品或服务的销售量与利润之间的关系。
通过本量利分析，企业可以了解其销售活动的盈利能力，从而做出相应的经营决策。
本量利分析模型通常使用Excel进行计算和展示。
如何构建本量利分析模型？
首先，我们需要收集以下信息：
1.产品价格：企业销售的产品或服务的售价。
2.变动成本：销售产品或服务所产生的直接变动成本，如原材料成本、
制造成本等。
3.固定成本：与销售产品或服务无直接关联的固定费用，如租金、工资
等。
4.销售量：销售的产品或服务的数量。
假设已经有一个产品的销售数据表，包含列：销售数量、销售单价、直接变动成本和固定成本。
我们可以在Excel中使用以下公式来计算本量利分析模型的各项指标：
1. 总收入
通过将销售数量乘以销售单价，可以计算出总收入。
假设销售数量列为A列，销售单价列为B列，则可以在C列使用以下公式计算总收入：
= A1 * B1
2. 总成本
通过将直接变动成本和固定成本相加，可以计算出总成本。
假设直接变动成本列为C列，固定成本列为D列，则可以在E列使用以下公式计算总成本：
= C1 + D1
3. 总利润
通过将总收入减去总成本，可以计算出总利润。
可以在F列使用以下公式计算总利润：
= C1 - E1
4. 利润率
利润率表示企业每一单位销售收入中的盈利情况。
可以通过将总利润除以总收
入并乘以100来计算利润率。
可以在G列使用以下公式计算利润率：= (F1 / C1) * 100
5. 边际效益
边际效益表示企业每增加一个单位的销售量所带来的额外利润。
可以通过将销
售数量逐行递增，然后计算每一行的总利润与前一行的总利润之差来计算边际效益。
如何使用本量利分析模型进行决策？
本量利分析模型可以帮助企业做出以下决策：
1.定价策略：企业可以通过分析不同价格水平下的销售量与利润变化，
确定最优的产品定价。
2.产品组合策略：企业可以通过分析不同产品组合下的销售量与利润变
化，确定最优的产品组合。
3.成本控制策略：企业可以通过分析不同成本水平下的销售量与利润变
化，确定成本控制的目标。
总结
本量利分析模型是一种有用的管理工具，可以帮助企业了解销售活动的盈利能力，并做出相应的经营决策。
通过在Excel中构建本量利分析模型，我们可以计算
出各项指标，如总收入、总成本、总利润、利润率和边际效益，从而支持决策制定过程。
通过合理利用本量利分析模型，企业可以优化营销和生产策略，提高盈利能力。