本量利分析模型

1、項目15制作量本利分析模型量本利分析法，全稱為產(chǎn)量成本利潤分析，也叫保本分析或盈虧平衡分析，是通過分析生產(chǎn)成本、銷售利潤和產(chǎn)品數(shù)量這三者的關(guān)系，掌握盈虧變化的規(guī)律，指導出企業(yè)選擇能夠以最小的成本生產(chǎn)最多產(chǎn)品并可使企業(yè)獲得最大利潤的經(jīng)營方案。15.1 學習目標通過本項目的學習，可以學會使用Excel建立量本利分析模型的方法，學會建立盈虧平衡圖表的方法，在表格制作的過程中學習并掌握以下Excel功能的綜合運用。創(chuàng)建Excel工作簿在單元格中使用公式SUM函數(shù)的使用建立量本利分析模型使用規(guī)劃求解工具求解盈虧臨界點制作量本利分析圖表在圖表中添加盈虧臨界點垂直參考線圖表的修飾15.2 項目實施效果圖本項

2、目完成后的效果圖如下：AB1銷售單價1702單位變動成本453單件產(chǎn)品人工成本244里件產(chǎn)品材料器115單件產(chǎn)品其它制苣疊106單位邊際貢就1257B固定成本1070009管理人員工資5600010資產(chǎn)折舊費3000011固定銷貨藥用210001213銷量85614變動成本3852015總成本14552016銷售收入14552017邊際貢獻1070001S利澗0,00圖15-1盈虧臨界點銷量AECDEFGHI1銷售單儕170銷量邊際貢獻固京成本利澗2單位變動成本4500. 00107000-1070003里件產(chǎn)品人工成本2420025000.00107000-820004單件產(chǎn)品材料費1140

3、050000+ 00107000-570005單件產(chǎn)品其它制造費10&0075000.00107000-32000g單位邊際市獻125BOO100000.00107000-700071000125000. 001070001.30008固定成本1070001200150000. 00107000430009管理人員工資58?？诳?400175000+ 00107000&300010資產(chǎn)折舊拶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 項目實施方案任務(wù)1：理解量本利分析的概念量本利分析是產(chǎn)量（

5、或銷售量）、成本、利潤之間依存關(guān)系分析的簡稱，也稱VCP分析(Volume-Cost-ProfitAnalysis)。它著重研究銷售數(shù)量、價格、成本和利潤之間的數(shù)量關(guān)系。它所提供的原理、方法在經(jīng)濟管理中有著廣泛的用途，同時它又是企業(yè)進行決策、計劃和控制的重要工具。在量本利分析中，確定盈虧臨界點是進行量本利分析的關(guān)鍵。所謂盈虧臨界點，是指企業(yè)收入和成本相等的經(jīng)營狀態(tài)，即邊際貢獻等于固定成本時企業(yè)所處的既不盈利又不虧損的狀態(tài)，通常用一定的業(yè)務(wù)量來表示這種狀態(tài)。盈虧平衡臨界點越低，說明企業(yè)獲得贏利的可能性越大，出現(xiàn)虧損的可能性越小，企業(yè)的抗風險能力也就越強。量本利分析所考慮的相關(guān)因素主要包括銷售量、

6、單價、銷售收入、單位變動成本、固定成本、營業(yè)利潤等。任務(wù)2:準備量本利分析基礎(chǔ)數(shù)據(jù)某企業(yè)生產(chǎn)某種產(chǎn)品，該產(chǎn)品的市場銷售價格為170元。生產(chǎn)每一件產(chǎn)品的直接人工成本為24元，材料費11元，其它制造費10元。管理人員工資5.6萬元，資產(chǎn)折舊費3萬元,固定銷售費用2.1萬元。試建立模型，計算盈虧臨界點銷量。步驟1:啟動Excel2010應(yīng)用程序，創(chuàng)建空白工作簿。步驟2:將空白工作簿另存為“量本利分析模型”。步驟3:將Sheet1工作表標簽重命名為“量本利分析”，并刪除Sheet2、Sheet3空白工作表。步驟4：制作量本利分析表框架，如圖15-3所示。&E1銷售單價1702單位變動成本單件產(chǎn)品人工成

7、本244單件產(chǎn)品材料11單件產(chǎn)品其它制造費16單位邊際貢獻0國定成本g管理人員工資saooo10資產(chǎn)折舊費3000011固定銷售費用210001213銷量14變初成本15總成本16梢售收入17，邊際貢獻16利潤表中，“邊際貢獻”是指銷售收入減去變動成本后的余額，邊際貢獻又稱為“邊際利潤”或“貢獻毛益”等。單位邊際貢獻是指單件產(chǎn)品銷售收入減去單位變動成本后的余額。上表中沒有數(shù)據(jù)的單元格為需要運算的數(shù)據(jù)。步驟5:計算“單位變動成本”。點擊B2單元格，選擇【公式】菜單中的【自動求和】按鈕，在參數(shù)輸入狀態(tài)下用鼠標選擇B3:B5單元格區(qū)域，然后按回車。步驟6:計算“固定成本”。點擊B8單元格，用與步驟2

8、相同的方法將B9:B11單元格區(qū)域求和。步驟7:計算“變動成本”，變動成本=銷量*單位變動成本。點擊B14單元格，輸入公式“=B13*B2”并回車，步驟8:計算“總成本”，總成本=固定成本+變動成本。點擊B15單元格，輸入公式“=B8+B14”并回車。步驟9:計算“銷售收入”，銷售收入=銷量*銷售單價。點擊B16單元格，輸入公式“=B13*B1”并回車。步驟10:計算“邊際貢獻”，邊際貢獻=銷售收入=變動成本。點擊B17單元格，輸入公式“=B16-B14”并回車。步驟11:計算“利潤”，利潤=銷售收入-總成本，點擊B18單元格，輸入公式“=B16-B15”并回車。表中“銷量”數(shù)據(jù)B13單元格不

9、用輸入數(shù)據(jù)，計算公式輸入完成后的量本利分析表如圖15-4所示。AE1銷售單價1702單位變動成本453單件產(chǎn)品人工成本244單件產(chǎn)品材料費115單件產(chǎn)品具它制造費106單位邊際貢獻12578國正成本1070009管理人員工資5COOO10資產(chǎn)折舊曾3000011固定銷售費用210001213銷量14變動成本015總成本1070001C銷總收入017邊際貢獻0ie利潤-107000圖15-4輸入計算參數(shù)任務(wù)3:使用【規(guī)劃求解】工具確定盈虧臨界點銷量步驟1:加載【規(guī)劃求解】工具。由于Excel2010應(yīng)用程序安裝后默認不加載“規(guī)劃求解”工具，所以，如果要使用【規(guī)劃求解】功能時，需要手動加載【規(guī)劃求

10、解】工具，加載“規(guī)劃求解”工具的方法如下。點擊【文件】菜單下的【選項】命令，彈出【Excel選項】對話框，如圖15-5所示。圖15-51Excel選項】對話框在【Excel選項】對話框窗口中選擇【加載項】按鈕，然后點擊【轉(zhuǎn)到】按鈕，彈出【加載宏】對話框，如圖15-6所示。圖15-6【加載宏】對話框X在【加載宏】對話框窗口中，選中【規(guī)劃求解加載項】，如圖15-7所示?？捎眉虞d宏二I標基打的導二I分析工具度口分析工具庫二儂規(guī)劃求第加戟項用于優(yōu)化和必式求解的工具圖15-7選中【規(guī)劃求解加載項】點擊【確定】按鈕，系統(tǒng)會自動安裝相關(guān)軟件，軟件安裝完成后會在【數(shù)據(jù)】選項卡中出現(xiàn)【規(guī)劃求解】按鈕，如圖15-

11、8所示。本量先話所便提*MkroftEmccI公法M市國皿因ib畫一出豳r尸黑息昌窯口通邂蠹手朗主EU丁上徘序蒜還”分列g(shù)的撲算事并所創(chuàng)胭取消組臺若美LM-Si舉莢步酈fi里復(fù)加看方性,-”連里播序峙地SJ3BZB分較顯示G分析圖15-8加載【規(guī)劃求解】功能后的【數(shù)據(jù)】數(shù)據(jù)選項卡步驟2:使用【規(guī)劃求解】工具求解出盈虧臨界點銷量值。點擊B18單元格，選擇【數(shù)據(jù)】選項卡，點擊【規(guī)劃求解】按鈕，彈出【規(guī)劃求解參數(shù)】對話框，如圖15-9所示。圖15-91規(guī)劃求解參數(shù)】設(shè)置對話框，步驟3:在對話框窗口中設(shè)置【規(guī)劃求解參數(shù)】，選中【目標值】前的單選按鈕，設(shè)置目標值為0,點擊【通過更改可變單元格】下面的文本

12、框，然后點擊B13單元格，其他選項保持默認值不變，如圖15-10所示。圖15-10設(shè)置盈虧臨界點銷量時的【規(guī)劃求解參數(shù)】步驟4:點擊【求解】按鈕，彈出【規(guī)劃求解結(jié)果】對話框，如圖15-11所示。獻I求解枚到一第，可茗是所離的約束及最優(yōu)狀況。保留規(guī)劃求解的解O還原初值制作4艮告大綱電扇.二卜.J取消保存方案.返回I規(guī)劃本解荽數(shù)”對話框可編輯word,供參考版!想劃求解找到一解，可蒜足斫有的約束及最優(yōu)狀況。:地最優(yōu)廨*使用單官也贊住規(guī)劃圖5-111規(guī)劃求解結(jié)果】對話框步驟5:點擊【確定】按鈕，求解結(jié)果如圖15-11所示。AE銷皂單價170單位變動成本45單件產(chǎn)品人工成本24單件產(chǎn)品材料費11單件產(chǎn)

13、品其它制造量10單位邊際貢獻125固定成本107000管理人員工資5500030000210008563B520 14552014552010700010資產(chǎn)折舊費11固定銷售費用12成本收貢 量動成售際 銷變總銷邊1E1415U17187.27GE-09圖15-11【規(guī)劃求解】結(jié)果步驟6:點擊B18單元格，選擇【開始】選項卡，在【數(shù)字格式】菜單中選擇【數(shù)字】選項。完成盈虧臨界點銷量的最終效果圖見圖15-1所示。任務(wù)4:制作量本利分析圖步驟1:分別在D1:G1單元格區(qū)域的單元格中輸入字段名“銷量、邊際貢獻、固定成本、利潤”，并設(shè)置居中，如圖15-12所示。DEFG銷塑邊際貢獻固定成本利潤圖15

14、-12輸入圖表字段名步驟2:在D2:D2單元格區(qū)域輸入數(shù)量“0、200、400、2000”。選擇D2單元格，輸入“0”，選擇D3單元格，輸入“200”，同時選擇D2、D3兩個單元格，然后用鼠標拖曳選中區(qū)域的填充句柄向下填充，直至顯示2000時放開鼠標。步驟3:計算“邊際貢獻”。在E2單元格輸入公式“=D2*$B$6，可以直接輸入，或選擇E2單元格，輸入“=”符號，然后用鼠標點擊D2單元格，再輸入“*”符號，再用鼠標點擊B6單元格，按F4把B6轉(zhuǎn)換為絕對地址，向下填充。步驟4:輸入“固定成本”。選擇F2單元格，輸入“107000”，或輸入單元格引用公式“=$B$8”，然后向下填充。步驟5:計算“

15、利潤”。選擇G2單元格，輸入公式“=D2*$B$1-D2*$B$2-$B$8”并向下填充，公式的含義是“利潤=數(shù)量*銷售單價-數(shù)量*單件產(chǎn)品人工成本-固定成本”。完成輸入的量本利數(shù)據(jù)如圖15-13所示。AECDEFG1銷售單價170鞘量邊際貢獻固定成本利潤2單位變動成本450d00107000-1070003單件產(chǎn)品人工成本2420025000.00107000-B20004單件產(chǎn)品材料若11400E0000.00107000-570005單件產(chǎn)品其它制造費1060075000,00107000-320006單位邊際貢獻125B00100000,00107000-700071000125000

16、+0010700018000a固定成本1070001200150000.00107000430009管理人員工資5G000L4C0175000,00107000兆00010資產(chǎn)折舊趨30000ieoo200000.001070009300011固定銷售踐用210001G00225000.00107000118000122000250000,00107000143000圖15-13量本利數(shù)據(jù)步驟6:選擇D1:G12單元格區(qū)域.步驟7:選擇【插入】選項卡，點擊【散點圖】按鈕，彈出圖表類型選擇菜單，如圖15-14所示。AA頁面布局公式翹g甲閱視圖尉郎帝國燼叵。四片剪貼畫用狀SmartArt界斑i剪柱

17、形匹腌圖掰圖要用圖茴若居脫百用其他曲賽民其它制造費T獻1110125散點囹匚資熹希用107000560003000021000WUU60。600100012001012!15i國應(yīng)1400 17!帚半越S的散點圖切詼才的數(shù)直35C 38520 1.45520 145520 L07000 0. 00b所有醫(yī)超!繼國-DfUUU-M2000-7000 1800043000680 DO 93000118000143。如果有許冬以X軸為 JE序于1方!據(jù)點.并且法 些穹據(jù)法函數(shù)，國何 哽用謖到圖15-14選擇【散點圖】類型步驟8:在菜單中選擇【帶平滑線的散點圖】命令，彈出量本利分析圖，如圖15-15所

18、示。邊際更做固定成本 利闊圖15-15量本利分析圖步驟9:用鼠標右擊圖例，在彈出的菜單中選擇【設(shè)置圖例格式】命令，彈出【設(shè)置圖例格式】對話框，如圖15-16所示。設(shè)置展的S高圉阿選哽圖例選項ii布酸目由西襟式明黑發(fā)光和柔北由混Til. YJJ Lly X/ 口(b-(l(e6 上部左右上 s 靠右 00000顯硬例，但不與圖強重我僖）圖15-161設(shè)置圖例格式】對話框步驟10:在【設(shè)置圖例格式】對話框中選擇【圖例位置】為【底部】，然后點擊【關(guān)閉】按鈕，設(shè)置效果如圖15-17所示。圖15-17設(shè)置【圖例位置】在底部步驟11:在圖表中添加“盈虧臨界點垂直參考線”。準備垂直參考線數(shù)據(jù)。選才iA22單

19、元格，輸入“盈虧臨界點垂直參考線”。在A23:A26單元格區(qū)域中輸入X軸坐標標注點“856”，“856”為盈虧臨界點銷量。在B23:B26單元格區(qū)域中輸入Y軸坐標標點“200000（任意的）、107000（盈虧臨界點邊際貢獻）、0（與X軸的交叉點）、-100000（任意的）”，如圖15-18所示。2122盈三臨界點垂直參考線23S56200000240561070002585602e856-100000圖15-18垂直參考線引用數(shù)據(jù)選中繪圖區(qū)，選擇【圖表工具】下的【設(shè)計】選項卡，點擊【選擇數(shù)據(jù)】按鈕，彈出【選擇數(shù)據(jù)源】對話框，如圖15-19所示。圖15-19添加圖例項在彈出的【選擇數(shù)據(jù)源】對話框中點擊【圖例項（系列”下的【添加】按鈕，彈出【編輯數(shù)據(jù)系列】對話框，如圖15-20所示。圖15-20編輯數(shù)據(jù)系列設(shè)置垂直參考線參數(shù)。點擊【系列名稱】下的文本框，然后點擊A22單元格；點擊【X軸系列值】下面的文本框，然后選擇A23:A26單元格區(qū)域；點擊【Y軸系列值】下面的文本框，刪除其中的內(nèi)容，然后選擇B23:B26單元格區(qū)域。設(shè)置結(jié)果如圖15-21所示。狷裔數(shù)據(jù)暈列?X系列名稱由）:年里利分析博誠22靚=盈虧臨界點垂直叁考線1軸系列倡&卜=本量利分析T悔瑁：$跳演度=656,856,056.I軸系列值位）：述宣利分析例$然節(jié)時至1晶-200000.1