基于Excel的多品種條件下本量利分析模型設計

梁馨勻+蘭素英

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

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

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

一、基本分析思路

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

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

企業綜合保本銷售額=

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

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

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

第二步，計算企業綜合保本銷售額

企業綜合保本銷售額=

第三步，計算各種產品保本銷售額

某種產品保本銷售額=企業綜合保本銷售額×該種產品的銷售額比重

二、模型設計與案例分析

（一）案例背景

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

（二）模型設計

1. 建立基礎資料區

第一步：打開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涉及下文微調器的使用）。

（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所示，在當前條件下，企業的盈虧平衡點為58557502.79元，三種產品的保本量分別是4751.21噸、66.67噸、4827.84噸，保本額分別是28982375.57元、366686.06元、29208441.16元。企業當前三種產品的實際銷量均大于各自的保本量，共實現銷售收入764135000.00元，大于綜合保本銷售額58557502.79元，說明該企業目前處于盈利狀態。

2. 設置微調器

企業在生產經營過程中，往往需要根據市場情況調整單價等因素。使用微調器可以快速完成單價等因素的調整。本文主要列舉了單價微調器的使用。依次點擊：開發工具—插入—數值調節鈕。

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

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

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

3. 制作面積折線圖

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

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

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

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

三、結語

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

參考文獻：

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

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

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

基金項目：廣西區級大學生創新創業訓練計劃項目資助（項目編號：201713638048）。

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