基于Excel的多品種條件下本量利分析模型設(shè)計(jì)

梁馨勻+蘭素英

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

關(guān)鍵詞：Excel；本量利分析；保本

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

一、基本分析思路

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

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

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

綜合邊際貢獻(xiàn)率法下保本點(diǎn)的具體計(jì)算步驟如下：

第一步，計(jì)算綜合邊際貢獻(xiàn)率

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

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

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

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

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

二、模型設(shè)計(jì)與案例分析

（一）案例背景

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

（二）模型設(shè)計(jì)

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

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

第二步：設(shè)置計(jì)算公式。

（1）單價(jià)：在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）單位邊際貢獻(xiàn)=單價(jià)—單位變動成本，在B8單元格中輸入“=B5-B6”，單擊公式欄中的“√”確定，即可得出計(jì)算結(jié)果。C8、D8單元格可以通過公式填充工具復(fù)制B8單元格來計(jì)算。

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

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

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

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

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

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

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

（12）綜合保本銷售額=

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

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

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

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

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

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

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

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

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

3. 制作面積折線圖

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

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

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

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

三、結(jié)語

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

參考文獻(xiàn)：

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

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

[3]王琦，溫素彬.管理會計(jì)中Excel的高級應(yīng)用[J].財(cái)務(wù)與會計(jì)，2014（05）.

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

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