用EXCEL做盈亏平衡分析
盈亏平衡分析(Excel)-合并
合并产品固定成本F 26,105,564 单位变动成本V 76 单位产品价格P 88 盈亏平衡量Q 02,216,801 盈亏平衡额S 0194,587,888 实际销售量Q 1,228,623 实际销售额R 107,846,924 实际总成本C 119,483,937.18 实际利润E -11,637,013数值输入量本项目1、输入固定成本、单位变动成本、单价产品价格、销售量，可即时进行量本利分析；各种特征线关系明确。
2、滚动条控制(1)增大固定成本，保本点上移,反之下移；(2)增大变动成本，保本点上移,反之下移；(3)增大产品价格，保本点下移,反之上移；(4)增大销售量，安全余量增加，否则减少。
3、以销售量约为保本量2倍，图形最为清楚。
请选择产品量本利关系分析1. 2.取列1列2坐标点产销量固定成本变动成本总成本销售收入x000.0026105563.610.0026105563.610.00 x110.1######26105563.6117690644.1043796207.7120431728.28 x220.1######26105563.6135381288.2161486851.8240863456.56 x330.2######26105563.6153071932.3179177495.9261295184.84 x440.3######26105563.6170762576.4196868140.028*******.13 x550.3######26105563.6188453220.51######################## x660.4######26105563.61################################### x770.4######26105563.61################################### x880.5######26105563.61################################### x990.6######26105563.61###################################x10100.6######26105563.61################################### x11110.7######26105563.61################################### x12120.8######26105563.61################################### x13130.8######26105563.61################################### x14140.9######26105563.61################################### x15150.9######26105563.61################################### x1616 1.0######26105563.61###################################保本点指示线保本额指示线######0.00194587888###############2216801194587888###### 1.1E+08利润辅助线安全余量安全余量(-988178)1E+06022168011078469241E+06-1163701312286231078469241E+061194839371E+061078469241.05倍销售业绩1240909销售实绩指示线2.12倍保本点#######12286230取较大值#######1228623-11637012.81228623119483937.2 1228623107846924.3利润(26105563.61) (23364479.43) (20623395.25) (17882311.07) (15141226.89) (12400142.71) (9659058.54) (6917974.36) (4176890.18) (1435806.00)1305278.184046362.366787446.549528530.72 12269614.90 15010699.08 17751783.25。
基于Excel的投资项目盈亏平衡分析模型
基于Excel的投资项目盈亏平衡分析模型[摘要]在项目投资决策中不仅要考虑投资项目的净现值也要考虑投资项目的风险大小和安全程度。
本文通过案例分析，运用Excel构建投资项目的静态盈亏平衡分析和动态盈亏平衡分析模型，通过对比指出了静态盈亏平衡分析的局限性并详细介绍了利用Excel进行动态盈亏平衡分析的过程，其结论有助于决策者认识到充分利用Excel的重要性，从而预先考虑措施与对策，实现科学决策。
[关键词]投资决策；盈亏平衡分析；Excel1盈亏平衡分析原理盈亏平衡分析的原理就是根据量本利之间的关系，计算项目的盈亏平衡点的销售量，从而分析项目对市场需求变化的适应能力。
静态盈亏平衡分析是在不考虑资金的时间价值情况下，对投资项目的盈亏平衡进行分析。
在考虑资金的时间价值和所得税等因素的条件下，项目的动态盈亏平衡点就是项目净现值为零的那一点，即动态保本销售量就是使项目净现值为零的销售量。
考虑单一产品的情况，令投资项目的净现值等于零，利用Excel的单变量求解工具可以计算出盈亏平衡点的产销量QNPV=n1t=1[Q×（pt-vt）-Ft]×（1-T）+T×Dt1（1+i）t-I=0式中，Q为产销量；Ft为第t年的付现固定成本；Dt为第t年的折旧；p 为产品单价；v为产品的单位变动成本；i为项目的基准利率（即贴现率）；T为所得税税率；I为项目的初始投资（假设在第0年一次性投入项目）；n为项目的寿命期；t为年份；在计算出盈亏平衡点产销量的基础上，还可以进一步计算出盈亏平衡点的安全边际率，计算公式为：安全边际率=现有产销量-盈亏平衡点产销量1现有产销量当产销量低于盈亏平衡点销售量时，投资项目处于亏损状态，反之，当产销量超过盈亏平衡点销售量时，项目就有了赢利。
根据投资项目安全边际率的大小，可以判断其风险的高低，一般而言，安全边际率越低，投资项目的经营风险很大，或经营上的安全程度很低，销售量微小的下降都可能使企业发生亏损，反之亦然。
盈亏平衡分析 EXCEL建模
盈亏平衡分析基于会
计利润输入
固定成本¥30,00030单位销售价格¥66单位可变成本¥44
运用公式计算盈亏平衡点
盈亏平衡点（销售量）15,000运用利润表倒推盈亏平衡
点的问题
销售量15,000销售收入¥90,000可变成本¥60,000毛利润¥30,000固定成本¥30,000会计利润¥0
¥-60,000
¥-30,000
¥0
¥30,000
¥60,000
¥90,000
¥120,000
¥150,000
05,00010,00015,000
销售量
会计利润为时0的盈亏平衡点
（1）销售量盈亏平衡点=固定成本/(单位销售收入-单位可变成本）输入
=B4/(b5-b6)
(2)输入任一试算值（如：12000）
（3）销售收入=单位销售价格X销售量
输入=B18XB5
（4）可变成本=单位可变成本X销售量
输入=B18XB6
(5)毛利润=销售收入-可变成本
（6）引用的固定成本
输入=B4
（7）会计利润=毛利润-固定成本
输入=B21-B22。
基于EXCEL的盈亏平衡分析模型设计
基于EXCEL的盈亏平衡分析模型设计摘要:盈亏平衡分析是在成本形态分析和变动成本法的基础上进一步分析研究销量、价格、成本及利润直接的内在规律性联系,为企业进行预测、决策、控制和计划提供必要的财务信息的一种定量分析方法,其关键是确定投资活动的盈亏平衡点。
根据这个平衡点,经营者可以掌握盈亏界限,及时了解经营状态,确定规模开发能力,合理利用资源,不断提高经济效益。
本文利用EXCEL的函数及相关分析工具具体介绍了单品种盈亏平衡分析模型与多品种盈亏平衡分析模型的设计。
关键词:EXCEL;盈亏平衡;模型盈亏平衡分析是在成本形态分析和变动成本法的基础上进一步分析研究销量、价格、成本及利润直接的内在规律性联系,为企业进行预测、决策、控制和计划提供必要的财务信息的一种定量分析方法。
1盈亏平衡分析概述西方管理学家通常认为企业管理的重心在于经营,经营的重心在于决策,即充分利用所掌握的信息,为实现某一目标,从而达到资源(人、财、物等)的优化配置。
盈亏平衡分析法是企业经营决策中常用的工具之一,它是根据产品的业务量(产量或销量)、成本、利润之间的相互制约关系的综合分析,用来预测利润,控制成本,判断经营状况的一种数学分析方法,亦即通过成本、销售量、利润三者之间的关系分析,确定项目的最低生产和销售水平,即盈亏平衡点,也称为保本点。
进行盈亏平衡分析所涉及的指标及计算关系如下:边际贡献:是指产品销售收入超过变动成本的余额,它反映了产品的初步盈利能力和数额,其基本关系式为:单位边际贡献=销售单价-单位变动成本边际贡献总额=销售收入总额-变动成本总额=销售量*单位边际贡献边际贡献率=边际贡献总额/销售收入总额=单位边际贡献/销售单价由于边际贡献是用来补偿固定成本的,补偿后的余额为最终利润,因此在盈亏平衡即利润为零时,可得到另一个保本公式:边际贡献=固定成本由此得到公式:销售量*(销售单价-单位变动成本)=固定成本盈亏平衡分析是研究企业销售收入与当期成本费用的平衡关系,并对达到盈亏平衡状况的业务量进行规划,即预算保本点,也就是计算盈亏平衡时的销售数量或销售金额。
Excel会计与财务管理——理论方案暨模型-第8章 Excel在盈亏平衡分与经济订货量中应用
8.1.3盈亏平衡分析模型举例
（1）保本分析 设保本销售量为x，在销售价格为200元时，保本销售量为： (200-120)x=900,000
x=11,250（件） 保本销售额为11,250×200=2,250,000元 即当销售量达到11,250件、销售额达到2,250,000元时，该企业才不 亏本。 （2）保利分析 税后利润=税前利润×(1－所得税率)
11-37
上机实验
2．某企业生产需要耗用甲、乙、丙、丁四种材料，相 关的数据下表所示：
存货名称
甲
年需要量R 一次订货成本S 单位储存成本CK 每日到货量 每日耗用量
18000 25 2 100 20
乙
丙
丁
20000 25 3 200 30
30000 25 4 300 40
25000 25 3 250 25
（3）在Excel单元格中计算产品的盈亏平衡点，进而确定作为当前 销售量或计划销售量与盈亏平衡点销售量之差的“安全边际”。
（4）进一步探讨问题中的各种经营管理参数（如单价）的变化对 盈亏平衡点的影响。可在模型中添加一个对参数（如单价）进行调节 的“滚动条”控件，通过对滚动条的调节，观察参数的变化对盈亏平 衡点的影响；
R：某存货的年需求量； C：单位存货的采购成本； S：一次采购费用； K：变动存储费用率； Q：一次采购量，则：
11-21
8.2.2经济订货量
11-22
8.2.2经济订货量
（2）存货陆续供应和耗用情况下的经济订货量计算公式
如果考虑存货不能一次到达、各批存货可能陆续入库，在 这种情况下，其基本公式又是什么样子的呢？
3．单因素变动盈亏平衡分析表和分析图的建立 （1）直接用公式计算的单因素变动平衡分析表模型
盈亏平衡点的计算excel
盈亏平衡点的计算excel在Excel中计算盈亏平衡点（Break-even Point）的基本步骤如下：1. 定义变量和公式：定义收入（Revenue）和成本（Cost）的公式。
定义盈亏平衡点的公式。
2. 输入数据：在Excel工作表中输入相关的收入和成本数据。
3. 计算盈亏平衡点：使用Excel的公式功能，在相应的单元格中输入盈亏平衡点的计算公式。
4. 检查结果：确认公式是否正确，并检查结果是否符合预期。
以下是一个简单的示例，演示如何在Excel中计算盈亏平衡点：假设我们有以下的收入和成本数据：固定成本（Fixed Cost）= $10,000单位变动成本（Variable Cost per Unit）= $50销售价格（Selling Price per Unit）= $100预计销售量（Expected Units Sold）= 1,0001. 在Excel工作表中，创建一个新的工作表或打开一个现有的工作表。
2. 在A1单元格中输入“固定成本”，在B1单元格中输入$10,000。
3. 在C1单元格中输入“单位变动成本”，在D1单元格中输入$50。
4. 在E1单元格中输入“销售价格”，在F1单元格中输入$100。
5. 在G1单元格中输入“预计销售量”，在H1单元格中输入1,000。
6. 在I1单元格中输入“盈亏平衡点”。
7. 在I2单元格中输入以下公式来计算盈亏平衡点：=B1/(F1-D1)*H1。
这个公式基于以下公式：盈亏平衡点= 固定成本/ (销售价格- 单位变动成本) * 预计销售量。
8. 按Enter键应用公式。
9. 检查I2单元格中的结果，它应该显示盈亏平衡点的值。
用Excel对盈亏平衡量变动分析
用Excel对盈亏平衡量变动进行分析利用滚动条窗体和动态图表的综合功能演示不同售价、成本的分析图本例分析中使用到下面的公式：成本=产量×单位成本内销利润=(产量×内销价格-成本)×内销份额外销利润=(产量×外销价格-成本)×外销份额利润=外销利润+内销利润Step 1 创建工作簿启动Excel自动新建一个工作簿，然后保存并命名为“内外销关系.xls”。
Step 2 重命名工作表双击Sheet1工作表标签进入工作表重命名状态，输入“内外销关系”后按键确认。
Step 3 删除多余的工作表单击Sheet2工作表标签，按住键再用右键单击Sheet3工作表标签，然后从弹出的快捷菜单中选择“删除”。
Step 4 输入表格标题在A1:F1、A7:A9、A11:A12单元格区域以及 A14单元格中分别输入表格各个字段的标题。
Step 5 输入表格数据依次在A2、F2单元格和B7:B9单元格区域输入“产量”、“参考利润”、“单位成本”、“内销价格”和“外销价格”的数据。
Step 6 设置“内销份额”和“外销份额”公式①单击B11单元格，在编辑栏中输入以下公式，按键确认。
=1-B14/10②单击B12单元格，在编辑栏中输入以下公式，按键确认。
=B14/10③选中B11:B12单元格区域，然后单击“格式”工具栏中的“百分比样式”按钮改变这两个单元格的数字格式。
Step 7 设置成本和外销利润公式①单击B2单元格，在编辑栏中输入以下公式，按键确认。
=A2*B7②单击C2单元格，在编辑栏中输入以下公式，按键确认。
=(A2*B8-B2)*B12③单击D2单元格，在编辑栏中输入以下公式，按键确认。
=(A2*B9-B2)*B11④单击E2单元格，在编辑栏中输入以下公式，按键确认。
=C2+D2Step 8 添加滚动条①右键单击工具栏中的任意空白处，从弹出的快捷菜单中选择“窗体”打开“窗体”工具栏。
excel盈亏平衡点(生产能力利用率)计算公式
Excel盈亏平衡点（生产能力利用率）计算公式1. 主题导言在管理会计中，盈亏平衡点是一个至关重要的概念，它指的是企业在销售产品或提供服务过程中，达到了总成本等于总收入的状态。
而生产能力利用率则是指企业生产线或生产设备实际生产能力与理论最大生产能力之比，是企业生产经营效率的重要指标。
在本文中，我将详细探讨Excel中盈亏平衡点（生产能力利用率）的计算公式，并对其深入分析，以帮助读者更好地理解并应用这一重要概念。
2. Excel中盈亏平衡点计算公式在Excel中，可以使用以下公式计算盈亏平衡点：盈亏平衡点 = 固定成本 / (1 - （可变成本 / 销售收入）)3. Excel中生产能力利用率的计算公式生产能力利用率 = 实际产量 / 理论最大产量4. 在文章中多次提及主题文字盈亏平衡点及生产能力利用率是管理会计中的重要指标，它们对企业的经营状况和效益有着重要影响，在Excel中的计算公式可以帮助企业更好地实时监控和评估经营状况。
5. 深入探讨盈亏平衡点在进行盈亏平衡点的计算时，固定成本是指企业在短期内不会改变的成本，如房租、管理人员工资等；可变成本是指与产品销售数量相关联的成本，如材料成本、直接人工成本等；销售收入则为销售产品或提供服务所获得的总收入。
通过上述公式，可以得出盈亏平衡点，即企业需要达到的销售收入水平，才能覆盖所有固定成本和可变成本，从而避免亏损。
6. 对生产能力利用率的个人观点和理解生产能力利用率是企业生产效率重要的衡量标准，它反映了生产线或设备的实际利用情况。
当生产能力利用率较低时，企业将面临着资源浪费和成本增加的问题，通过实时监控和调整生产能力利用率，企业可以更好地提高生产效率和降低生产成本，从而提升竞争力。
7. 总结回顾通过本文的深入分析，我们可以更清晰地理解Excel中盈亏平衡点（生产能力利用率）的计算公式，并针对企业实际情况进行应用。
盈亏平衡点和生产能力利用率的计算可以帮助企业及时了解自身的经营状况，从而采取合理的经营决策，提高盈利能力和竞争力。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
企业管理也在行 Excel做量本利分析(图)
2001-12-27 17:20:41 赛迪网--中国电脑教育报
什么是量、本、利分析呢？就是运用数学计算和图像法研究产品销量、成本及售价的变化，对目标利润影响的一种分析方法。
它作为财务分析的有效的手段，在企业管理中得到广泛应用。
本文试用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元。
图4
计算方法如下：
1.建立计算表如图4所示，单元格E18中键入公式“=(C18-B18)*D18-A18”。
2.单击“E18”。
执行菜单命令“工具-单变量求解”，显示“单变量求解”对话框，如图5所示。
图5
3.激活“目标值”文本框，键入“5000”(即目标利润5000元)。
4.激活“可变单元格”文本框，键入“D18”(或单击“D18”)，单击[确定]按钮，显示“单变量求解状态”对话框。
5.单击[确定]按钮，显示计算结果如图6所示。
图6
由图可知，要实现5000元目标利润，目标销量需达到155单位。
成本变化对利润影响的测定
成本包括固定成本和可变成本。
在产品售价不变的情况下，成本的变化将会引起利润做反方向的运动，即成本上升，利润下降；反之，成本下降，利润上升。
譬如，产品原材料提价，引起可变成本上升，导致利润下降；广告可以促销产品，但广告费的支出又引起固定成本的上升。
如此，在总成本上升的情况下，销量达到多少才能维持目标利润。
通过量、本、利分析，可以确定目标销量的增量，进而分析能否达到该增量，以便作出正确的经营决策。
图7
例3：上例产品固定成本上升5000元，单位可变成本上升300元，售价不变，如果目标利润维持5000元，则销量比原目标销量增加多少？
计算方法如下：
1.建立计算表如图7所示。
单元格A22、B22、C22、E22及F22中，分别键入公式
“=A18+5000”、“=B18+30”、“=C18”、“=D22-D18”、“=(C22-B22)*D22-A22”。
2.参照例2用单变量求解法求解D22中的数据，由此导出E22中的数据(目标销量增量)，结果如图8所示。
图8
由图可知，如要实现5000元的目标利润，销量则要在原目标销量的基础上增加120单位。