梁馨勻+蘭素英

摘要：本量利分析以數(shù)量化的會計模型或圖式揭示企業(yè)的固定成本、變動成本、單價、銷售量、銷售額、利潤等變量之間的關系。文章運用Excel設計多品種本量利分析模型，通過建立基礎資料區(qū)、設置微調(diào)器、制作面積折線圖求解，使管理層直觀地了解企業(yè)的盈利情況，為預測、決策、規(guī)劃、控制提供必要的財務信息，幫助管理層在產(chǎn)銷規(guī)模、成本控制和產(chǎn)品結構等方面做出決策。

關鍵詞：Excel；本量利分析；保本

本量利分析，是指研究成本—業(yè)務量—利潤關系的一種數(shù)學分析方法。在現(xiàn)實的生產(chǎn)經(jīng)營活動中，大部分企業(yè)的生產(chǎn)活動是復雜的，一般都是進行多品種生產(chǎn)經(jīng)營，這就涉及多品種本量利分析。企業(yè)在生產(chǎn)、銷售多種產(chǎn)品的情況下，運用Excel建立分析模型，借助微調(diào)器及面積折線圖等功能，將數(shù)據(jù)變得動態(tài)可調(diào)，有利于提高企業(yè)管理層決策的效率。

一、基本分析思路

在多品種的條件下，只能用銷售額來表示企業(yè)的盈虧臨界點。通常計算多品種企業(yè)盈虧臨界點的方法有綜合邊際貢獻率法、聯(lián)合單位法、分算法等幾種方法。本文主要介紹綜合邊際貢獻率法。綜合邊際貢獻率法是假設產(chǎn)品結構不變的情況下，通過計算多品種的綜合邊際貢獻率，從而確定企業(yè)綜合保本點和每種產(chǎn)品的保本點的方法。

保本點也稱盈虧臨界點，是指企業(yè)達到收支平衡實現(xiàn)保本的銷售水平。盈虧臨界點分析是確定企業(yè)經(jīng)營安全程度和進行保利分析的基礎。多品種的盈虧臨界點分析關鍵是企業(yè)綜合保本銷售額的確定。具體來說，

企業(yè)綜合保本銷售額=

綜合邊際貢獻率法下保本點的具體計算步驟如下：

第一步，計算綜合邊際貢獻率

綜合邊際貢獻率=∑（各種產(chǎn)品的邊際貢獻率×該種產(chǎn)品的銷售額比重）

第二步，計算企業(yè)綜合保本銷售額

企業(yè)綜合保本銷售額=

第三步，計算各種產(chǎn)品保本銷售額

某種產(chǎn)品保本銷售額=企業(yè)綜合保本銷售額×該種產(chǎn)品的銷售額比重

二、模型設計與案例分析

（一）案例背景

以某公司2016年生產(chǎn)資料為例，構建本量利模型進行分析，主要分析該公司三種主要產(chǎn)品在盈虧平衡點時的銷量和銷售收入。建立多品種分析模型需要了解產(chǎn)品的銷量、單價、單位變動成本、固定成本、邊際貢獻率、銷售比重、綜合保本銷售額等。三種產(chǎn)品的基礎資料如表1所示。

（二）模型設計

1. 建立基礎資料區(qū)

第一步：打開Excel2010工作表，將案例中的基礎資料輸入。

第二步：設置計算公式。

（1）單價：在B5單元格中輸入“=6100*H5”；C5單元格輸入“=5500*H5”；D5單元格輸入“=6050*H5”。

（2）單位變動成本：在B6單元格中輸入“=4180.95*H7”；C6單元格輸入“=4278.43*H7”；D6單元格輸入“=2383.61*H7。

（3）固定成本：在E7單元格中輸入“=26900000*H9”。

（以上公式中的H5、H7、H9涉及下文微調(diào)器的使用）。

（4）單位邊際貢獻=單價—單位變動成本，在B8單元格中輸入“=B5-B6”，單擊公式欄中的“√”確定，即可得出計算結果。C8、D8單元格可以通過公式填充工具復制B8單元格來計算。

（5）邊際貢獻率= ，在B9單元格中輸入“=B8/B5”，單擊公式欄中的“√”確定，即可得出計算結果。C9、D9單元格可以通過公式填充工具復制B9單元格來計算。

（6）邊際貢獻總額=銷量×單位邊際貢獻，在B10單元格中輸入“=B4*B8”，單擊公式欄中的“√”確定，即可得出計算結果。C10、D10單元格可以通過公式填充工具復制B10單元格來計算。

（7）銷售收入=銷量×單價，在B11單元格中輸入“=B4*B5”，單擊公式欄中的“√”確定，即可得出計算結果。C11、D11單元格可以通過公式填充工具復制B11單元格來計算。E11單元格可以選定E11，然后單擊選項卡中的“公式”，再單擊“自動求和”，即可得出計算結果。

（8）銷售比重= ，在B12單元格中輸入“=B11/$E$11”，單擊公式欄中的“√”確定，即可得出計算結果。C12、D12單元格可以通過公式填充工具復制B12單元格來計算。

（9）加權邊際貢獻率=邊際貢獻率×銷售比重，在B13單元格中輸入“=B9*B12”，單擊公式欄中的“√”確定，即可得出計算結果。C13、D13單元格可以通過公式填充工具復制B13單元格來計算。E13單元格可以選定E13，然后單擊選項卡中的“公式”，再單擊“自動求和”，即可得出計算結果。

（10）保本銷量= ，在B14單元格中輸入“=B15/B5”，單擊公式欄中的“√”確定，即可得出計算結果。C14、D14單元格可以通過公式填充工具復制B14單元格來計算。

（11）保本銷售額=綜合保本銷售額×銷售比重，在B15單元格中輸入“=$E$16*B12”，單擊公式欄中的“√”確定，即可得出計算結果。C15、D15單元格可以通過公式填充工具復制B15單元格來計算。

（12）綜合保本銷售額=

，在E16單元格中輸入“=E7/E13”， 單擊公式欄中的“√”確定，即可得出結果。

（13）利潤=全年綜合保本銷售額×全年加權邊際貢獻率—全年固定成本，在E17單元格中輸入“=E16*E13—E7”， 單擊公式欄中的“√”確定，即可得出計算結果。

第三步：設置結論性文字。在B19單元格中輸入“=”盈虧平衡點：“&ROUND（E16，2）&”元”， 單擊公式欄中的“√”確定即可。構建好的模型如圖1所示。endprint

圖2所示，在當前條件下，企業(yè)的盈虧平衡點為58557502.79元，三種產(chǎn)品的保本量分別是4751.21噸、66.67噸、4827.84噸，保本額分別是28982375.57元、366686.06元、29208441.16元。企業(yè)當前三種產(chǎn)品的實際銷量均大于各自的保本量，共實現(xiàn)銷售收入764135000.00元，大于綜合保本銷售額58557502.79元，說明該企業(yè)目前處于盈利狀態(tài)。

2. 設置微調(diào)器

企業(yè)在生產(chǎn)經(jīng)營過程中，往往需要根據(jù)市場情況調(diào)整單價等因素。使用微調(diào)器可以快速完成單價等因素的調(diào)整。本文主要列舉了單價微調(diào)器的使用。依次點擊：開發(fā)工具—插入—數(shù)值調(diào)節(jié)鈕。

當鼠標變成“+”時，在G5單元格中畫一個大小適中的微調(diào)器。G5單元格作為過渡單元格。右鍵單擊數(shù)值調(diào)節(jié)鈕，調(diào)出“設置控件格式”對話框。最小值設為“0”，在“單元格鏈接”中選中“G5單元格”。為了把變動幅度控制在“-50%～150%”，把“最大值”設為200，步長為1。如圖3所示。

在H5單元格中輸入“=G5/100-50%”。同理可得單位變動成本和固定成本的微調(diào)器。每上（下）按動一次微調(diào)器按鈕，對應金額就會提高（降低）基礎的10%。如圖4所示。

至此，一個動態(tài)可調(diào)的多品種本量利分析模型就完成了。該模型可以對單價、單位變動成本、固定成本三個變量進行動態(tài)調(diào)節(jié)，通過觀察三個變量的變動對利潤、銷量的影響，從而做出合理的決策。

3. 制作面積折線圖

首先，按住Ctrl鍵，選擇區(qū)域A3：D3、A5：D6，然后依次點擊插入—面積圖—二維面積圖，雙擊網(wǎng)格線，選擇無線條。

其次，僅選數(shù)據(jù)區(qū)域B5：D6，按Ctrl+C復制數(shù)據(jù)，單擊繪圖區(qū)，按Ctrl+V粘貼，右鍵單擊顏色區(qū)域，把圖片類型修改為“帶數(shù)據(jù)標記的折線圖”，雙擊折線節(jié)點，可以設置數(shù)據(jù)點格式。

最后，在繪圖區(qū)添加5個文本框，輸入相應的文字，選中單價對應的文本框，在公式編輯欄輸入“=Sheet3！$H$5”；選中單位變動成本對應的文本框，在公式編輯欄輸入“=Sheet3！$H$7”； 選中中間的文本框，在公式編輯欄輸入“=Sheet3！$B$19”；把單價和變動成本的微調(diào)器復制粘貼到繪圖區(qū)。至此，一個動態(tài)可調(diào)的盈虧平衡面積折線圖就完成了。如圖5所示。

圖5可以直觀反映三種產(chǎn)品隨單價、單位變動成本變動的變化趨勢。企業(yè)當前三種產(chǎn)品的單價趨勢線均位于單位變動成本趨勢線上方，兩條趨勢線之間的面積為邊際貢獻，只要邊際貢獻大于固定成本，企業(yè)就能盈利。每按一次微調(diào)器，企業(yè)的邊際貢獻就會隨趨勢線的變化而變化。

三、結語

在現(xiàn)實經(jīng)濟生活中，企業(yè)利潤受到多因素的影響。本文運用Excel設計多品種本量利分析模型，通過建立基礎資料區(qū)、設置微調(diào)器、制作面積折線圖，為實際問題的解決提供定量分析和計算的方法，提高管理層的決策效率?；贓xcel的本量利分析模型，可以將復雜的數(shù)據(jù)簡單化，該模型的結論會隨著參數(shù)的改變而呈現(xiàn)動態(tài)變化，非常直觀形象，使管理者決策向科學化和合理化方向發(fā)展。

參考文獻：

[1]溫素彬.管理會計[M].機械工業(yè)出版社，2016.

[2]候志才.多產(chǎn)品的Excel本量利模型構建[J].財會月刊，2014（05）.

[3]王琦，溫素彬.管理會計中Excel的高級應用[J].財務與會計，2014（05）.

基金項目：廣西區(qū)級大學生創(chuàng)新創(chuàng)業(yè)訓練計劃項目資助（項目編號：201713638048）。

（作者單位：廣西大學行健文理學院）endprint