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

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

3、050000+ 00107000-570005單件產(chǎn)品其它制造費(fèi)10&0075000.00107000-32000g單位邊際市獻(xiàn)125BOO100000.00107000-700071000125000. 001070001.30008固定成本1070001200150000. 00107000430009管理人員工資58?？诳?400175000+ 00107000&300010資產(chǎn)折舊拶300001600200000. 001070009300011固定銷售費(fèi)用210001800225000.0010700011,8000122000250000.0010700014300013銷量856

4、14變動(dòng)成本33520300000.0015總成本14552016銷售收入145520250000.0017邊際貢獻(xiàn)107000200000.001B利潤(rùn)0. 00150000.001920100000.002150000.0022盈虧臨界點(diǎn)垂直參考線233562000000.0024856107000(500D0.CM)2535&0(1OQO00.OOJ26356-10000027ISOOW.OOf2329辿際貢獻(xiàn)固定咸本利閏黑虧臨畀點(diǎn)垂直爹考線如圖15-3量本利分析表框架可編輯word,供參考版!圖15-2量本利分析效果圖15.3 項(xiàng)目實(shí)施方案任務(wù)1：理解量本利分析的概念量本利分析是產(chǎn)量（

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

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

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

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

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

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

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

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

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

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

15、利潤(rùn)”。選擇G2單元格，輸入公式“=D2*$B$1-D2*$B$2-$B$8”并向下填充，公式的含義是“利潤(rùn)=數(shù)量*銷售單價(jià)-數(shù)量*單件產(chǎn)品人工成本-固定成本”。完成輸入的量本利數(shù)據(jù)如圖15-13所示。AECDEFG1銷售單價(jià)170鞘量邊際貢獻(xiàn)固定成本利潤(rùn)2單位變動(dòng)成本450d00107000-1070003單件產(chǎn)品人工成本2420025000.00107000-B20004單件產(chǎn)品材料若11400E0000.00107000-570005單件產(chǎn)品其它制造費(fèi)1060075000,00107000-320006單位邊際貢獻(xiàn)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:選擇【插入】選項(xiàng)卡，點(diǎn)擊【散點(diǎn)圖】按鈕，彈出圖表類型選擇菜單，如圖15-14所示。AA頁(yè)面布局公式翹g甲閱視圖尉郎帝國(guó)燼叵。四片剪貼畫用狀SmartArt界斑i剪柱

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

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

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