本量利分析模型

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

2、完成后的效果图如下：AB1琏詹单价1702里位企动成卞453单件产品犬工成本2i4单件产品材誹费11匸、 w虫件产品其它匍益费10.6甲位边际市就1257W匡定成本10700D弓管理人员工资56COD10蛊产折1日费3000D11医定枱售疥用210UD1.213S5B14娈动成姿3：E52?1514552010销售fc152017边际贡就10700016Q* 00EFCHI1t.&78910111 2L31415里包養閒血応里件产品人工战広单件产品林糾建鱼蚪产品上I"：二劳司主成本養理人员丄赍 贲二折I日尝 固左菲宦爭用自一动戒1.仃17in=_一际.嗣8：023292420

3、025D&0. 00107000-E2D001140050D0D. 001070 00-57DOO1Q60075 DOO. 00107000-J2D00125&C6IDODCiD. CO107000-70&01000125000. 00LOTOOO180001Q7W12001颐呱00L07000聽DO)能（W175000. U)107000治丽30000100200000. 00LOTOOO93005210001300225 DOO. P01070001L800)200CI2SODOO. 00UiTlOOO143 DOO销宦边际贡献念烹成本.打;M4500.00L070

4、00 -1D7C0DE1701近顛0. ：0ZQOCQ0107000-1QQCOC3囲如145520图15-2量本利分析效果图图15-1盈亏临界点销量15.3项目实施方案任务1 ：理解量本利分析的概念VCP分析量本利分析是产量（或销售量）、成本、利润之间依存关系分析的简称，也称(Volume - Cost -Profit Analysis)。它着重研究销售数量、价格、成本和利润之间的数量关系。它所提供的原理、方法在经济管理中有着广泛的用途，同时它又是企业进行决策、计划和控制的重要工具。在量本利分析中，确定盈亏临界点是进行量本利分析的关键。所谓盈亏临界点，是指企业收入和成本相等的经营状态，即边际

5、贡献等于固定成本时企业所处的既不盈利又不亏损的 状态，通常用一定的业务量来表示这种状态。盈亏平衡临界点越低， 说明企业获得赢利的可能性越大，出现亏损的可能性越小，企业的抗风险能力也就越强。单位变动成本、固定量本利分析所考虑的相关因素主要包括销售量、单价、销售收入、 成本、营业利润等。任务2 :准备量本利分析基础数据某企业生产某种产品， 该产品的市场销售价格为 170元。生产每一件产品的直接人工成本为24元，材料费11元，其它制造费10元。管理人员工资 5.6万元，资产折旧费 3万元, 固定销售费用2.1万元。试建立模型，计算盈亏临界点销量。步骤1:启动Excel 2010应用程序，创建空白工作

6、簿。步骤2 :将空白工作簿另存为“量本利分析模型”。步骤3:将Sheet1工作表标签重命名为“量本利分析”，并删除Sheet2、Sheet3空白工作表。步骤4 :制作量本利分析表框架，如图15-3所示。舘售里价17D里位交动成本申件产品人丁戒本2斗更件产品材料另1L单件产品茸它制造费1D鱼位边际贡就ROODROOODZ1OOD固定"前本 管理人员工资 资产扌円R需 固定销嘗罢用123 4 5-6- -=9rsi如图15-3量本利分析表框架边际贡献又称为“边际利润”表中，“边际贡献”是指销售收入减去变动成本后的余额, 或“贡献毛益” 等。单位边际贡献是指单件产品销售收入减去单位变动成本

7、后的余额。上表中没有数据的单元格为需要运算的数据。步骤5 :计算“单位变动成本”。点击B2单元格，选择【公式】菜单中的【自动求和】按钮，在参数输入状态下用鼠标选择B3:B5单元格区域，然后按回车。步骤6:计算“固定成本”。点击B8单元格，用与步骤 2相同的方法将 B9:B11单元格 区域求和。步骤7 :计算“变动成本”，变动成本=销量*单位变动成本。点击 B14单元格，输入公 式“ =B13*B2 ”并回车，步骤 &计算“总成本”，总成本=固定成本+变动成本。点击B15单元格，输入公式“=B8+B14” 并回车。步骤9 :计算“销售收入”，销售收入=销量*销售单价。点击B16单元格，输

8、入公式“=B13*B1 ”并回车。步骤10 :计算“边际贡献”，边际贡献=销售收入=变动成本。点击 B17单元格，输入公 式“ =B16-B14”并回车。步骤11 :计算“利润”，利润=销售收入-总成本，点击B18单元格，输入公式“ =B16-B15” 并回车。表中“销量”数据 B13单元格不用输入数据，计算公式输入完成后的量本利分析表如图15-4所示。AE1销售单价170Z单也变动戒本4D2单件产品人工成卫244里件产品新料時115单件产昂其它制造茅10612578固正成本107000官理人员工资56000 ,10资产折1日费3000011固君肖售费用21000L2Li销董14变动戚本a15

9、总成本107000ie销割攵入017边际贡社016利润-107000.图15-4输入计算参数任务3 :使用【规划求解】工具确定盈亏临界点销量步骤1 :加载【规划求解】工具。由于 Excel 2010应用程序安装后默认不加载“规划求 解”工具，所以，如果要使用【规划求解】功能时，需要手动加载【规划求解】工具，加载“规划求解”工具的方法如下。 点击【文件】菜单下的【选项】命令，弹出【 ExceI选项】对话框，如图 15-5所示。图15-5【Excel选项】对话框 在【Excel选项】对话框窗口中选择【加载项】按钮，然后点击【转到】按钮，弹出【加载宏】对话框，如图15-6所示。血法? X巨訪化V图1

10、5-6【加载宏】对话框 在【加载宏】对话框窗口中，选中【规划求解加载项】，如图15-7所示。加疙? X可可力伺丘厲S一a 曰库Lt f _J3_t Tr-nTa讯攵怵翔I弐贞宦于凉匕和量式求诲工耳图15-7选中【规划求解加载项】 点击【确定】按钮，系统会自动安装相关软件，软件安装完成后会在【数据】选项卡 中出现【规划求解】按钮，如图15-8所示。LuT筑耶S |g a e倚啰冃 mFF申二一一二务惑曲 雀xa琶曲裁#N全审时 _iln* 0E ertt-H flBkJW创宅红取.音好臺汇2sac rs»tiTWE9Eiasx MiLrmaft E.a-l?I图15-8加载【规划求解】

11、功能后的【数据】数据选项卡步骤2 :使用【规划求解】工具求解出盈亏临界点销量值。点击B18单元格，选择【数15-9所示。据】选项卡，点击【规划求解】按钮，弹出【规划求解参数】对话框，如图X标 <11到©超遒世:|_贏苗即<?目窃1E：也|q雯单元牯:C1)器熾聲瀬鹏黑型聘撫曲确沁刊哋嘶IMQP不薛退曼|<1切图15-9【规划求解参数】设置对话框，步骤3 :在对话框窗口中设置【规划求解参数】，选中【目标值】前的单选按钮，设置目标值为0，点击【通过更改可变单元格】下面的文本框，然后点击B13单元格，其他选项保持默认值不变，如图 15-10所示。翊X靈汙蜒黠盘詡踹翅隣離沖

12、簾曲斓懸时畋如引图15-10设置盈亏临界点销量时的【规划求解参数】步骤4 :点击【求解】按钮，弹出【规划求解结果】对话框，如图15-11所示。规力衣咤果X制化强岂尢脚HU；揀第讎II-鴛可衙足竊有的绚屯備优扰5肚3賴憋俅翔1解O还陳即值誦定1 一耶肖保存右秦-煤划求瞬找刮一瞬円蒲足所有的约克反凰瓦伏况。返回“规则求解參数”对国框图5-11【规划求解结果】对话框步骤5 :点击【确定】按钮，求解结果如图 15-11所示。AR1销售单价1702年位吏訪成亓453里件产显人工咸本244单件产品材辨费115电件产昂其宅制造罢106里便边耶頁就12579固正应本1070003管理人员工资000010资产折

13、日帝3000011固定销售贾用2100012LS销量55614变动硯本LE总成本1455201<销售收入14J35Z017inrzooo1E和润7. 270E-09图15-11【规划求解】结果步骤6 :点击B18单元格，选择【开始】选项卡，在【数字格式】菜单中选择【数字】选项任务4 :制作量本利分析图步骤1:分别在D1G1单元格区域的单元格中输入字段名“销量、边际贡献、固定成本、利润”，并设置居中，如图 15-12所示。严W12F图15-12输入图表字段名步骤2 :在 D2:D2单元格区域输入数量“ 0、200、400、2000”。选择 D2单元格， 输入“ 0”，选择D3单元格，输入“

14、 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、 =数量*销售单价-数量*单件产品人工成本-固定成本”。完成输入的量本利数据如图 15-13所示。AECDEF1销害单怕JTu辺际权址固定戌本利嗣4bU0. 00Ll?70UU-107 DUD32+20025000.00107000-B2D0D4单伴二品神特昜1_4005C000.00L070：0-570000单件比品真它詡造费10eoo75000.00107000-32DOJ5125000100000.00107000-700071000.25000. 00L070：n18000g圖定威丰L070D01200150000.0010700043D009當理人員工资5MD0lTsooo. on1

16、0700068 Oft 010资立析旧費30000IftOO200000. 0010700093D0D111国土詞誉贾用210D01800226000*0010?0L01L8D0D122000250000. 00107000143000图15-13量本利数据步骤6 :选择D1:G12单元格区域.步骤7 :选择【插入】选项卡，点击【散点图】按钮，弹出图表类型选择菜单， 如图15-14 所示。迪?E4S 折察皙 別!5 tff.E 31 <E1匚何I TH：L1启HE制務產L0125107DCC56 DM*SCDCO21DC030520145520145520107DCO0. DO*tLM

17、3i6CKj 7!WOO 10i ldtw 12! ltXj IB) L400 17!带半脳輕昨豈匸虫舷g風T彳亏i二津，H讨K些雄砖亍目散疋可 使中虱-a； UUL I :：!I I -roo- 1HOO 43000 CS030 9SOOOLL90 X14300IM aS 逼t站！徵片 勢鮎15乞核-nuTv 5?耳趕産图15-14选择【散点图】类型15-15步骤 &在菜单中选择【带平滑线的散点图】命令，弹出量本利分析图，如图所示。边障貢fit伍1室血玄图15-15量本利分析图步骤9 :用鼠标右击图例，在弹出的菜单中选择【设置图例格式】命令，弹出【设置图 例格式】对话框，如图 15-

18、16所示。图15-16【设置图例格式】对话框步骤10 :在【设置图例格式】对话框中选择【图例位置】为【底部】，然后点击【关闭】按钮，设置效果如图 15-17所示。图15-17设置【图例位置】在底部步骤11 :在图表中添加“盈亏临界点垂直参考线”。 准备垂直参考线数据。选择A22单元格，输入“盈亏临界点垂直参考线”。在A23:A26单元格区域中输入 X轴坐标标注点“ 856”，“ 856”为盈亏临界点销量。在B23:B26单元格区域中输入 Y轴坐标标点“ 200000 （任意的）、107000 （盈亏临界点 边际贡献）、0 （与X轴的交叉点）、-100000 （任意的）”，如图15-18所示。2

19、122盈亏临黑直垂直秦考线23B502000002410700025B5Q0.26B56-10COOO图15-18垂直参考线引用数据 选中绘图区，选择【图表工具】下的【设计】选项卡，点击【选择数据】按钮，弹出【选择数据源】对话框，如图15-19所示。图15-19添加图例项 在弹出的【选择数据源】对话框中点击【图例项（系列）】下的【添加】按钮，弹出【编辑数据系列】对话框，如图15-20所示。翊? X事列拿和® :图15-20编辑数据系列 设置垂直参考线参数。点击【系列名称】下的文本框，然后点击A22单元格；点击【X轴系列值】下面的文本框，然后选择A23:A26单元格区域；点击【Y轴系列值】下面的文本框，删除其中的内容，然后选择B23:B26单元格区域。设置结果如图15-21所示。?XI招黑列但毬）-車臺刮分析'$耐血序畸茂垂 -35空 96乩 056,.=桶利分析呻眸幵羊胜阳篦=?nnrwn. in