本量利分析模型

1、项目15制作量本利分析模型量本利分析法，全称为产量成本利润分析，也叫保本分析或盈亏平衡分析，是通过分析生产成本、销售利润和产品数量这三者的关系，掌握盈亏变化的规律，指导出企业选择能够以最小的成本生产最多产品并可使企业获得最大利润的经营方案。15.1 学习目标通过本项目的学习，可以学会使用Excel建立量本利分析模型的方法，学会建立盈亏平衡图表的方法，在表格制作的过程中学习并掌握以下Excel功能的综合运用。创建Excel工作簿在单元格中使用公式SUM函数的使用建立量本利分析模型使用规划求解工具求解盈亏临界点制作量本利分析图表在图表中添加盈亏临界点垂直参考线图表的修饰15.2 项目实施效果图本项

2、目完成后的效果图如下：AB1销售单价1702单位变动成本453单件产品人工成本244里件产品材料器115单件产品其它制苣叠106单位边际贡就1257B固定成本1070009管理人员工资5600010资产折旧费3000011固定销货药用210001213销量85614变动成本3852015总成本14552016销售收入14552017边际贡献1070001S利涧0,00图15-1盈亏临界点销量AECDEFGHI1销售单侪170销量边际贡献固京成本利涧2单位变动成本4500. 00107000-1070003里件产品人工成本2420025000.00107000-820004单件产品材料费1140

3、050000+ 00107000-570005单件产品其它制造费10&0075000.00107000-32000g单位边际市献125BOO100000.00107000-700071000125000. 001070001.30008固定成本1070001200150000. 00107000430009管理人员工资58。口口1400175000+ 00107000&300010资产折旧拶300001600200000. 001070009300011固定销售费用210001800225000.0010700011,8000122000250000.0010700014300013销量856

4、14变动成本33520300000.0015总成本14552016销售收入145520250000.0017边际贡献107000200000.001B利润0. 00150000.001920100000.002150000.0022盈亏临界点垂直参考线233562000000.0024856107000(500D0.CM)2535&0(1OQO00.OOJ26356-10000027ISOOW.OOf2329辿际贡献固定咸本利闰黑亏临畀点垂直爹考线如图15-3量本利分析表框架可编辑word,供参考版!图15-2量本利分析效果图15.3 项目实施方案任务1：理解量本利分析的概念量本利分析是产量（

5、或销售量）、成本、利润之间依存关系分析的简称，也称VCP分析(Volume-Cost-ProfitAnalysis)。它着重研究销售数量、价格、成本和利润之间的数量关系。它所提供的原理、方法在经济管理中有着广泛的用途，同时它又是企业进行决策、计划和控制的重要工具。在量本利分析中，确定盈亏临界点是进行量本利分析的关键。所谓盈亏临界点，是指企业收入和成本相等的经营状态，即边际贡献等于固定成本时企业所处的既不盈利又不亏损的状态，通常用一定的业务量来表示这种状态。盈亏平衡临界点越低，说明企业获得赢利的可能性越大，出现亏损的可能性越小，企业的抗风险能力也就越强。量本利分析所考虑的相关因素主要包括销售量、

6、单价、销售收入、单位变动成本、固定成本、营业利润等。任务2:准备量本利分析基础数据某企业生产某种产品，该产品的市场销售价格为170元。生产每一件产品的直接人工成本为24元，材料费11元，其它制造费10元。管理人员工资5.6万元，资产折旧费3万元,固定销售费用2.1万元。试建立模型，计算盈亏临界点销量。步骤1:启动Excel2010应用程序，创建空白工作簿。步骤2:将空白工作簿另存为“量本利分析模型”。步骤3:将Sheet1工作表标签重命名为“量本利分析”，并删除Sheet2、Sheet3空白工作表。步骤4：制作量本利分析表框架，如图15-3所示。&E1销售单价1702单位变动成本单件产品人工成

7、本244单件产品材料11单件产品其它制造费16单位边际贡献0国定成本g管理人员工资saooo10资产折旧费3000011固定销售费用210001213销量14变初成本15总成本16梢售收入17，边际贡献16利润表中，“边际贡献”是指销售收入减去变动成本后的余额，边际贡献又称为“边际利润”或“贡献毛益”等。单位边际贡献是指单件产品销售收入减去单位变动成本后的余额。上表中没有数据的单元格为需要运算的数据。步骤5:计算“单位变动成本”。点击B2单元格，选择【公式】菜单中的【自动求和】按钮，在参数输入状态下用鼠标选择B3:B5单元格区域，然后按回车。步骤6:计算“固定成本”。点击B8单元格，用与步骤2

8、相同的方法将B9:B11单元格区域求和。步骤7:计算“变动成本”，变动成本=销量*单位变动成本。点击B14单元格，输入公式“=B13*B2”并回车，步骤8:计算“总成本”，总成本=固定成本+变动成本。点击B15单元格，输入公式“=B8+B14”并回车。步骤9:计算“销售收入”，销售收入=销量*销售单价。点击B16单元格，输入公式“=B13*B1”并回车。步骤10:计算“边际贡献”，边际贡献=销售收入=变动成本。点击B17单元格，输入公式“=B16-B14”并回车。步骤11:计算“利润”，利润=销售收入-总成本，点击B18单元格，输入公式“=B16-B15”并回车。表中“销量”数据B13单元格不

9、用输入数据，计算公式输入完成后的量本利分析表如图15-4所示。AE1销售单价1702单位变动成本453单件产品人工成本244单件产品材料费115单件产品具它制造费106单位边际贡献12578国正成本1070009管理人员工资5COOO10资产折旧曾3000011固定销售费用210001213销量14变动成本015总成本1070001C销总收入017边际贡献0ie利润-107000图15-4输入计算参数任务3:使用【规划求解】工具确定盈亏临界点销量步骤1:加载【规划求解】工具。由于Excel2010应用程序安装后默认不加载“规划求解”工具，所以，如果要使用【规划求解】功能时，需要手动加载【规划求

10、解】工具，加载“规划求解”工具的方法如下。点击【文件】菜单下的【选项】命令，弹出【Excel选项】对话框，如图15-5所示。图15-51Excel选项】对话框在【Excel选项】对话框窗口中选择【加载项】按钮，然后点击【转到】按钮，弹出【加载宏】对话框，如图15-6所示。图15-6【加载宏】对话框X在【加载宏】对话框窗口中，选中【规划求解加载项】，如图15-7所示。可用加载宏二I标基打的导二I分析工具度口分析工具库二侬规划求第加戟项用于优化和必式求解的工具图15-7选中【规划求解加载项】点击【确定】按钮，系统会自动安装相关软件，软件安装完成后会在【数据】选项卡中出现【规划求解】按钮，如图15-

11、8所示。本量先话所便提*MkroftEmccI公法M市国皿因ib画一出豳r尸黑息昌窑口通邂蠹手朗主EU丁上徘序蒜还”分列g的扑算事并所创胭取消组台若美LM-Si举荚步郦fi里复加看方性,-”连里播序峙地SJ3BZB分较显示G分析图15-8加载【规划求解】功能后的【数据】数据选项卡步骤2:使用【规划求解】工具求解出盈亏临界点销量值。点击B18单元格，选择【数据】选项卡，点击【规划求解】按钮，弹出【规划求解参数】对话框，如图15-9所示。图15-91规划求解参数】设置对话框，步骤3:在对话框窗口中设置【规划求解参数】，选中【目标值】前的单选按钮，设置目标值为0,点击【通过更改可变单元格】下面的文本

12、框，然后点击B13单元格，其他选项保持默认值不变，如图15-10所示。图15-10设置盈亏临界点销量时的【规划求解参数】步骤4:点击【求解】按钮，弹出【规划求解结果】对话框，如图15-11所示。献I求解枚到一第，可茗是所离的约束及最优状况。保留规划求解的解O还原初值制作4艮告大纲电扇.二卜.J取消保存方案.返回I规划本解荽数”对话框可编辑word,供参考版!想划求解找到一解，可蒜足斫有的约束及最优状况。:地最优廨*使用单官也赞住规划图5-111规划求解结果】对话框步骤5:点击【确定】按钮，求解结果如图15-11所示。AE销皂单价170单位变动成本45单件产品人工成本24单件产品材料费11单件产

13、品其它制造量10单位边际贡献125固定成本107000管理人员工资5500030000210008563B520 14552014552010700010资产折旧费11固定销售费用12成本收贡 量动成售际 销变总销边1E1415U17187.27GE-09图15-11【规划求解】结果步骤6:点击B18单元格，选择【开始】选项卡，在【数字格式】菜单中选择【数字】选项。完成盈亏临界点销量的最终效果图见图15-1所示。任务4:制作量本利分析图步骤1:分别在D1:G1单元格区域的单元格中输入字段名“销量、边际贡献、固定成本、利润”，并设置居中，如图15-12所示。DEFG销塑边际贡献固定成本利润图15

14、-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:计算“

15、利润”。选择G2单元格，输入公式“=D2*$B$1-D2*$B$2-$B$8”并向下填充，公式的含义是“利润=数量*销售单价-数量*单件产品人工成本-固定成本”。完成输入的量本利数据如图15-13所示。AECDEFG1销售单价170鞘量边际贡献固定成本利润2单位变动成本450d00107000-1070003单件产品人工成本2420025000.00107000-B20004单件产品材料若11400E0000.00107000-570005单件产品其它制造费1060075000,00107000-320006单位边际贡献125B00100000,00107000-700071000125000

16、+0010700018000a固定成本1070001200150000.00107000430009管理人员工资5G000L4C0175000,00107000兆00010资产折旧趋30000ieoo200000.001070009300011固定销售践用210001G00225000.00107000118000122000250000,00107000143000图15-13量本利数据步骤6:选择D1:G12单元格区域.步骤7:选择【插入】选项卡，点击【散点图】按钮，弹出图表类型选择菜单，如图15-14所示。AA页面布局公式翘g甲阅视图尉郎帝国烬叵。四片剪贴画用状SmartArt界斑i剪柱

17、形匹腌图掰图要用图茴若居脱百用其他曲赛民其它制造费T献1110125散点囹匚资熹希用107000560003000021000WUU60。600100012001012!15i国应1400 17!帚半越S的散点图切诙才的数直35C 38520 1.45520 145520 L07000 0. 00b所有医超!继国-DfUUU-M2000-7000 1800043000680 DO 93000118000143。如果有许冬以X轴为 JE序于1方!据点.并且法 些穹据法函数，国何 哽用谡到图15-14选择【散点图】类型步骤8:在菜单中选择【带平滑线的散点图】命令，弹出量本利分析图，如图15-15所

18、示。边际更做固定成本 利阔图15-15量本利分析图步骤9:用鼠标右击图例，在弹出的菜单中选择【设置图例格式】命令，弹出【设置图例格式】对话框，如图15-16所示。设置展的S高圉阿选哽图例选项ii布酸目由西襟式明黑发光和柔北由混Til. YJJ Lly X/ 口(b-(l(e6 上部左右上 s 靠右 00000显硬例，但不与图强重我僖）图15-161设置图例格式】对话框步骤10:在【设置图例格式】对话框中选择【图例位置】为【底部】，然后点击【关闭】按钮，设置效果如图15-17所示。图15-17设置【图例位置】在底部步骤11:在图表中添加“盈亏临界点垂直参考线”。准备垂直参考线数据。选才iA22单

19、元格，输入“盈亏临界点垂直参考线”。在A23:A26单元格区域中输入X轴坐标标注点“856”，“856”为盈亏临界点销量。在B23:B26单元格区域中输入Y轴坐标标点“200000（任意的）、107000（盈亏临界点边际贡献）、0（与X轴的交叉点）、-100000（任意的）”，如图15-18所示。2122盈三临界点垂直参考线23S56200000240561070002585602e856-100000图15-18垂直参考线引用数据选中绘图区，选择【图表工具】下的【设计】选项卡，点击【选择数据】按钮，弹出【选择数据源】对话框，如图15-19所示。图15-19添加图例项在弹出的【选择数据源】对话框中点击【图例项（系列”下的【添加】按钮，弹出【编辑数据系列】对话框，如图15-20所示。图15-20编辑数据系列设置垂直参考线参数。点击【系列名称】下的文本框，然后点击A22单元格；点击【X轴系列值】下面的文本框，然后选择A23:A26单元格区域；点击【Y轴系列值】下面的文本框，删除其中的内容，然后选择B23:B26单元格区域。设置结果如图15-21所示。狷裔数据晕列?X系列名称由）:年里利分析博诚22靓=盈亏临界点垂直叁考线1轴系列倡&卜=本量利分析T悔瑁：$跳演度=656,856,056.I轴系列值位）：述宣利分析例$然节时至1晶-200000.1