项目 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所示。