本量利分析模型

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

2、目完成后的效果圖如下：1銷售電:介17D2甲位變動成本453野F產(chǎn)忌人工成本244單個卜產(chǎn)品材料要115立辟其它劄造強106里位邊際貢獻(xiàn)125f8回定成本1070009管理人員工資弓600010資產(chǎn)折日費300001-同定銷叁蕊用2W0D1213悄量05614變初成本3852D15總成本145520LQ銷修收人14532。1.7邊際貢獻(xiàn)10700016利潤0.00圖15-1盈虧臨界點銷量45&7Bq101112131415吧電費幼成本里件產(chǎn)品人工成本單件產(chǎn)品材料霆斗產(chǎn)品且H利方野空位以限重就引走成本首連人員L資近=折舊費同年用售空用16171R19sopi曲01.07000-1J7D0

3、D24200250&D.30LOTOOO-E2000114005000D.00L07000-570001060075D0D.10107000-32DOD1259001000&D.y107000-7DOO1000_25Dt>0.0010TO001350010700012001-0D0LDO10700043DODseoor175D00.001070003cm300002CODOO.00LOTOOO&30002LQM1300225D00.DOL070001iy兒j200n250DC10.10700014C31。11T0捎星邊際貢獻(xiàn)固定應(yīng)本用洞5563的印145520E2

4、2黃虧臨界點垂直勢考結(jié)呂820gmNFF6:U7Q0Q25燈0國日鋪-100Q0C28291行基匚o.aoHi際+獻(xiàn)一囿定成本制狗三盾霽白正百浜看縷圖15-2量本利分析效果圖15.3項目實施方案任務(wù)1：理解量本利分析的概念量本利分析是產(chǎn)量（或銷售量）、成本、利潤之間依存關(guān)系分析的簡稱，也稱VCP分析(Volume-Cost-ProfitAnalysis)o它著重研究銷售數(shù)量、價格、成本和利潤之間的數(shù)量關(guān)系。它所提供的原理、方法在經(jīng)濟管理中有著廣泛的用途，同時它又是企業(yè)進行決策、計劃和控制的重要工具。在量本利分析中，確定盈虧臨界點是進行量本利分析的關(guān)鍵。所謂盈虧臨界點，是指企業(yè)收入和成本相等的經(jīng)

5、營狀態(tài)，即邊際貢獻(xiàn)等于固定成本時企業(yè)所處的既不盈利又不虧損的狀態(tài)，通常用一定的業(yè)務(wù)量來表示這種狀態(tài)。盈虧平衡臨界點越低，說明企業(yè)獲得贏利的可能性越大，出現(xiàn)虧損的可能性越小，企業(yè)的抗風(fēng)險能力也就越強。量本利分析所考慮的相關(guān)因素主要包括銷售量、單價、銷售收入、單位變動成本、固定成本、營業(yè)利潤等。任務(wù)2:準(zhǔn)備量本利分析基礎(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)建空白工作簿。步

6、驟2:將空白工作簿另存為“量本利分析模型”。步驟3:將Sheet1工作表標(biāo)簽重命名為“量本利分析”，并刪除Sheet2、Sheet3空白工作表。步驟4：制作量本利分析表框架，如圖15-3所示。鈞售甲價17D里位變動成本里件產(chǎn)品大二成本21要件產(chǎn)品利料費11單件產(chǎn)品其它制造卷10皇位邊際貢款固定成本管埋人昌工資56OOD資產(chǎn)折舊費3000D國定銷售費用21?？诳诒救司统杀臼諉T量初成售際澗俏變總銷邊利表中，“邊際貢獻(xiàn)”是指銷售收入減去變動成本后的余額，邊際貢獻(xiàn)又稱為“邊際利潤”或“貢獻(xiàn)毛益”等。單位邊際貢獻(xiàn)是指單件產(chǎn)品銷售收入減去單位變動成本后的余額。上表中沒有數(shù)據(jù)的單元格為需要運算的數(shù)據(jù)。步驟5

7、:計算“單位變動成本”。點擊B2單元格，選擇【公式】菜單中的【自動求和】按鈕，在參數(shù)輸入狀態(tài)下用鼠標(biāo)選擇B3:B5單元格區(qū)域，然后按回車。步驟6:計算“固定成本”。點擊B8單元格，用與步驟2相同的方法將B9:B11單元格區(qū)域求和。步驟7:計算“變動成本”，變動成本=銷量*單位變動成本。點擊B14單元格，輸入公式“=B13*B2”并回車，步驟8:計算“總成本”，總成本=固定成本+變動成本。點擊B15單元格，輸入公式“=B8+B14”并回車。步驟9:計算“銷售收入”，銷售收入=銷量*銷售單價。點擊B16單元格，輸入公式“=B13*B1”并回車。步驟10:計算“邊際貢獻(xiàn)”，邊際貢獻(xiàn)=銷售收入=變動成

8、本。點擊B17單元格，輸入公式“=B16-B14”并回車。步驟11:計算“利潤”，利潤=銷售收入-總成本，點擊B18單元格，輸入公式“=B16-B15”并回車。表中“銷量”數(shù)據(jù)B13單元格不用輸入數(shù)據(jù)，計算公式輸入完成后的量本利分析表如圖15-4所示。£B1銷售單價1702單位變動成本453單件產(chǎn)品人工成本244里件產(chǎn)品材料普115單件產(chǎn)品及它制造費106單位邊際責(zé)獻(xiàn)1251D固定成本10700D9置理人員工食5600010資產(chǎn)折舊強3000011固定能售若翔2LOOD1213銷量14變動成本01.E總成本107000L6船售收入017邊際貢獻(xiàn)LS利潤-107000任務(wù)3:使用【規(guī)劃

9、求解】工具確定盈虧臨界點銷量步驟1:加載【規(guī)劃求解】工具。由于Excel2010應(yīng)用程序安裝后默認(rèn)不加載“規(guī)劃求解”工具，所以，如果要使用【規(guī)劃求解】功能時，需要手動加載【規(guī)劃求解】工具，加載“規(guī)劃求解”工具的方法如下。點擊【文件】菜單下的【選項】命令，彈出【Excel選項】對話框，如圖15-5所示。I襦！一鈉/電Es劉運義大疑快疸的司LHtl帝運中|“.軻?X.能件殂NcaE邢;MK岳府，用r>TT»3沖Z*為交示與漪二同仁山引巨累;內(nèi)玉山期七三阻煙布阿&聲融三贏識-餐同的手Qj；正文4*y11V豪丁丁!31班認(rèn)。£1。；=苣才_L隼*通3：J；»

10、Vkro«4hMittiflfTMMtidJB月產(chǎn)ew-lhirkpad.2JRA圖15-5Excel選項】對話框在【Excel選項】對話框窗口中選擇【加載項】按鈕，然后點擊【轉(zhuǎn)到】按鈕，彈出【加載宏】對話框，如圖15-6所示。圖15-6【加載宏】對話框在【加載宏】對話框窗口中，選中【規(guī)劃求解加載項】，如圖15-7所示。圖15-7選中【規(guī)劃求解加載項】點擊【確定】按鈕，系統(tǒng)會自動安裝相關(guān)軟件，軟件安裝完成后會在【數(shù)據(jù)】選項卡中出現(xiàn)【規(guī)劃求解】按鈕，如圖15-8所示。本皇和分析（理.小麓-MkrwcftEwd圖15-8加載【規(guī)劃求解】功能后的【數(shù)據(jù)】數(shù)據(jù)選項卡步驟2:使用【規(guī)劃求解】工

11、具求解出盈虧臨界點銷量值。點擊B18單元格，選擇【數(shù)據(jù)】選項卡，點擊【規(guī)劃求解】按鈕，彈出【規(guī)劃求解參數(shù)】對話框，如圖15-9所示。圖15-91規(guī)劃求解參數(shù)】設(shè)置對話框,步驟3:在對話框窗口中設(shè)置【規(guī)劃求解參數(shù)】，選中【目標(biāo)值】前的單選按鈕，設(shè)置目標(biāo)值為0,點擊【通過更改可變單元格】下面的文本框，然后點擊B13單元格，其他選項保持默認(rèn)值不變，如圖15-10所示。椀XI沮S目衽.金到：。目標(biāo)僧:口歐研可口單元精通）由F懂*0肺V巾主于歪用糖1SA母2苣無功案交地為錨獨t型選擇求整方達(dá).魚）m閡酒lil吊臼嚙J幣0東翼人造宓理解轆制繇踹筑露罐以口確切秘堿睡鈉線隹黜閭美舊七圖15-10設(shè)置盈虧臨界點

12、銷量時的【規(guī)劃求解參數(shù)】步驟4:點擊【求解】按鈕，彈出【規(guī)劃求解結(jié)果】對話框，如圖15-11所示。凱劃定筆控到I-說可病足即有瞪蒐好的tt杭兄V根吉®保值獻(xiàn)肉御斛腦1置。逐扇加值口返回“規(guī)劃棚壑技”對國框n制作報告尢刎|j豌."取消一既|槐則求解技強一解，可粗足所有的狗床技最酰狀混。圖5-111規(guī)劃求解結(jié)果】對話框步驟5:點擊【確定】按鈕，求解結(jié)果如圖15-11所示。1n34567891011A銷售里價170單位變就成才45里仔產(chǎn)品人工成本24單件盧昂材料費11電件產(chǎn)品其它闔誥費10單位邊際貢獻(xiàn)125固定成本1070003300021000魂產(chǎn)折舊費固定柏碧者用121314

13、151G171；：量動成卸際澗錮變思銷協(xié)利335201蛤5201455201070007.2761-09圖15-11【規(guī)劃求解】結(jié)果步驟6:點擊B18單元格，選擇【開始】選項卡，在【數(shù)字格式】菜單中選擇【數(shù)字】選項。完成盈虧臨界點銷量的最終效果圖見圖15-1所示。任務(wù)4:制作量本利分析圖步驟1:分別在D1:G1單元格區(qū)域的單元格中輸入字段名“銷量、邊際貢獻(xiàn)、固定成本、利潤”，并設(shè)置居中，如圖15-12所示。圖15-12輸入圖表字段名步驟2:在D2:D2單元格區(qū)域輸入數(shù)量“0、200、400、2000”。選擇D2單元格，輸入“0”，選擇D3單元格，輸入“200”，同時選擇D2、D3兩個單元格，然

14、后用鼠標(biāo)拖曳選中區(qū)域的填充句柄向下填充，直至顯示2000時放開鼠標(biāo)。步驟3:計算“邊際貢獻(xiàn)”。在E2單元格輸入公式“=D2*$B$6"，可以直接輸入，或選擇E2單元格，輸入“=”符號，然后用鼠標(biāo)點擊D2單元格，再輸入“*”符號，再用鼠標(biāo)點擊B6單元格，按F4把B6轉(zhuǎn)換為絕對地址，向下填充。步驟4:輸入“固定成本”。選擇F2單元格，輸入“107000”，或輸入單元格引用公式“=$B$8”，然后向下填充。步驟5:計算“利潤”。選擇G2單元格，輸入公式“=D2*$B$1-D2*$B$2-$B$8”并向下填充，公式的含義是“利潤=數(shù)量*銷售單價-數(shù)量*單件產(chǎn)品人工成本-固定成本”。完成輸入的

15、量本利數(shù)據(jù)如圖15-13所示。AFCFFGL銷唐中片17?埔里邊際貢獻(xiàn)固定成木利洞Q單位變動成本450Q.QQL07QW-107DQD；里科卜產(chǎn)品人工成本2420025(X10,001070DO-B2D0D4居力單G卜產(chǎn)品材料費品苴它為1誥昔11W3K6005口區(qū)凡口。75000100LQ7QOT1070DO'32000&單位邊際貢獻(xiàn)125EDO103000,001070Ct)-7D0D7B固定成本13(X)1羽其00isoQoaoo1070001070CO180OD4300D9管理人曷_L資14M)1070M6600010資產(chǎn)打日要3000016003O3DO0,001070

16、0C&3D0D11固定泊若器用21QQC二迎325(X10,C0L070CO1LHDOO122000250000lno1070DO145DOD圖15-13量本利數(shù)據(jù)步驟6:選擇D1:G12單元格區(qū)域.步驟7:選擇【插入】選項卡，點擊【散點圖】按鈕，彈出圖表類型選擇菜單，如圖15-14所示。O舊市一寸用啊r-rwrr需具匕到運資10F域125107000匚資5&000*aoooo聲用21000856385201455201455301070000.00-二tWJU|-£2lu：-7000ILSOOO43W0?8W093W01IL：f'I14AIYii-JH至TE

17、和匕5U果西沙多mXSB53麻停魴范期I：,并且通螞源表示曲數(shù)rEU可翊京晶圖15-14選擇【散點圖】類型步驟8:在菜單中選擇【帶平滑線的散點圖】命令，彈出量本利分析圖，如圖15-15所示。圖15-15量本利分析圖步驟9:用鼠標(biāo)右擊圖例，在彈出的菜單中選擇【設(shè)置圖例格式】命令，彈出【設(shè)置圖例格式】對話框，如圖15-16所示。圖15-161設(shè)置圖例格式】對話框步驟10:在【設(shè)置圖例格式】對話框中選擇【圖例位置】為【底部】，然后點擊【關(guān)閉】按鈕，設(shè)置效果如圖15-17所示。-O-Q圖15-17設(shè)置【圖例位置】在底部步驟11:在圖表中添加“盈虧臨界點垂直參考線”。準(zhǔn)備垂直參考線數(shù)據(jù)。選才iA22單元

18、格，輸入“盈虧臨界點垂直參考線”。在A23:A26單元格區(qū)域中輸入X軸坐標(biāo)標(biāo)注點“856”，“856”為盈虧臨界點銷量。在B23:B26單元格區(qū)域中輸入Y軸坐標(biāo)標(biāo)點“200000（任意的）、107000（盈虧臨界點邊際貢獻(xiàn)）、0（與X軸的交叉點）、-100000（任意的）”，如圖15-18所示。2122竟與臨界點垂直于考的23B5020000024B5610700025026B56-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ù)系列A22單元格；點擊設(shè)置垂直參考線參數(shù)。點擊【系列名稱】下的文本框，然后點擊【X軸系列值】下面的文本框，然后選擇A23:A26單元格區(qū)域；點擊【Y軸系列值】下面的文本框，刪除其中的內(nèi)容，然后選擇B23:B26單元格區(qū)域。設(shè)置結(jié)果如圖15-21所示。嚼事fi府析!貌較國31虧幽點垂亶整片統(tǒng)1粒系列值兇：一本里利分析味但:加26江-656,950,