基于Excel的盈虧平衡分析決策模型創新研究

張霞

【摘 要】 為了加強對利潤的管理，使企業能順利實現財務管理目標，財務管理人員一般都要進行盈虧平衡分析。文章在介紹盈虧平衡分析理論的基礎上，詳細闡述了利用Excel工具建立盈虧平衡分析決策模型和創建可調動態盈虧平衡分析圖形的過程和方法，以更好地為決策者和管理者服務，提高管理決策模型的有效性，實現財務管理向智能化發展。

【關鍵詞】 Excel； 盈虧平衡分析； 模型

中圖分類號：F235.19 文獻標識碼：A 文章編號：1004-5937（2015）19-0077-03

一、盈虧平衡分析的基礎理論

盈虧平衡分析是在變動成本計算模型的基礎上，研究企業在一定期間的成本、業務量和利潤三者之間變量關系的一種專門方法。這一分析方法是在成本按性態進行劃分的基礎上發展起來的，主要用于確定公司達到盈虧平衡時的銷售水平，即分析銷量高于或低于這一平衡點時的盈利和虧損狀況。在產品的生產或銷售中，存在固定成本與變動成本這兩種不同性質的成本。固定成本是不隨銷量變化的成本，包括行政管理人員的工資、辦公費、固定資產折舊費、職工教育培訓費等。變動成本是隨銷量變化的成本，一般包括直接材料費、人工費及設備的維修費等。產品的盈虧平衡點是指一種產品提供的邊際貢獻正好抵銷固定成本時或產品的全部銷售收入等于全部成本時的銷量或銷售收入。由于產品的銷售收入與銷量成正比，因此，有時人們也從銷售收入出發而不是銷量出發，將使利潤等于零的銷售收入稱為盈虧平衡點。為明確起見，在可能發生混淆的時候，將使利潤等于零的銷量稱為“盈虧平衡銷量”，將使利潤等于零的銷售收入稱為“盈虧平衡銷售收入”。

如果將盈虧平衡銷量表示為Q0，固定成本為F，銷售單價為p，單位變動成本為v，盈虧平衡銷售收入為R0，邊際貢獻為k，盈虧平衡銷量和盈虧平衡銷售收入可以使用如下公式計算。

二、在Excel中建立盈虧平衡分析決策模型的基本原則

在Excel中建立盈虧平衡分析決策模型的基本原則一般包括以下三個方面：

（一）正確性

模型的邏輯必須正確與完備。在一個單元格中輸入的公式應該能夠計算出正確值，尤其在使用IF（）函數進行嵌套計算時，應該能正確計算出各個條件下的結果數值，正確進行四舍五入操作。

（二）可讀性

模型的基本含義與結論應該便于創建者或其他使用者正確理解。模型應提供多方面的配套分析數據和圖形，以便決策者從各個方面去深入理解它所提供的含義與性質。

（三）易維護性

模型應該讓使用者在問題發生變化時可以方便地進行修改。應將決策問題中的所有已知參數集中安排在模型工作邊的一個單元格區域，并設定決策變量單元格、中間變量單元格和目標單元格。在中間變量和目標變量的計算公式中，應使用參數單元格、決策變量單元格、中間變量單元格和函數，不應直接使用參數值和中間計算值。

三、盈虧平衡分析決策模型的建立

運用Excel電子表格建立盈虧平衡分析決策模型的方法很多，如使用公式計算、單變量求解工具、規劃求解工具和模擬運算表的查表加內插值法。下面介紹單變量求解工具和公式計算的方式建立盈虧平衡分析決策模型。

（一）利用單變量求解工具計算盈虧平衡銷量

假設宏遠公司生產某種零件用于智能手機的生產，固定成本為2 700元，單位變動成本為0.15元，銷售單價為0.38元，當前銷量為260件。建立盈虧平衡分析決策模型必須先計算出盈虧平衡銷量，根據盈虧平衡分析的基礎理論，在Excel中建立盈虧平衡分析基礎數據，在D6單元格輸入公式“=D5*D4”，在D7單元格輸入公式“=D2+D3*D5”，在D8單元格輸入公式“=D6-D7”，分別計算銷售收入、總成本和利潤。利用單變量求解工具，在打開的單變量求解對話框中，對參數進行設置，其中目標單元格為D8單元格，目標值為0，可變單元格為D5單元格，計算公司收支相等、利潤為0時的銷量，即盈虧平衡銷量，如圖1所示。

（二）利用公式計算建立盈虧平衡分析決策模型

1.建立基本模型

基于以上案例，在Excel中建立盈虧平衡分析基礎數據，在E7單元格輸入公式“=E6*E5”，在E8單元格輸入公式“=E3+E4*E6”，在E9單元格輸入公式“=E7-E8”，在E11單元格輸入公式“=E3/（E5-E4）”，在E12單元格輸入公式“=E11*E5”，在C15單元格輸入公式“=C11&"="&ROUND（E11，0）”。

2.采用模擬運算表準備繪圖數據

以銷量作為自變量，同時對銷售收入、總成本和利潤三個因變量函數進行一維模擬運算，注意，在單元格I3、I4中輸入自變量的各個值，因為本案例是一個線性問題，所以只需輸入自變量銷量的起始值（0）和終止值（40 000），在單元格J2、K2、L2分別引用單元格E7、E8、E9。

進行靈敏度分析即求解模擬運算表的步驟是：選取單元格I2：L4，執行“數據”菜單下的“模擬運算表”命令，在“模擬運算表”對話框中的“輸入引用列的單元格”文本框中，選擇模型中的單元格E6，并將計算結果放置在單元格J3：L4區域。同理在單元格P3：Q14區域建立模擬運算表，即單價隨盈虧平衡銷量的靈敏度分析。

3.利用模擬運算表的數據創建圖表

圖形能夠使人們更直觀、更清晰地理解模型的信息。圖形雖然可用折現圖、餅圖、柱形圖，但在大多數情況下采用的是XY散點圖而不是折線圖。因為同樣坐標值在XY散點圖上的點是重疊的，而折線圖則不會重疊。一般而言，經常使用XY散點圖來反映自變量是數值的函數值圖形。利用單元格I3：L4的數據繪制一個XY散點圖的圖形，然后編輯圖形，對X軸和Y軸的刻度固定，以圖形的方式反映銷售收入、總成本和利潤三者間的關系。圖形的效果是圖3所示的三條直線。

4.在圖形上添加垂直參考線和參考點

利用單元格E11的數據在圖形上添加盈虧平衡銷量的垂直參考線；利用單元格E11：E12的數據添加盈虧平衡銷量，銷售收入等于總成本即利潤為0時的參考點；利用單元格E6的數據添加反映當前銷量時的銷售收入、總成本和利潤的垂直參考線，當前銷量（E6）由小變大時，反映當前銷量的銷售收入、總成本和利潤的垂直參考線由左向右移動，反映了由虧損變為盈利的過程；利用單元格E7、E8、E9的數據添加反映當前銷售收入、總成本和利潤的參考點；利用E11單元格的數據，添加盈虧平衡銷量在X軸的參考點，反映盈虧平衡銷量。構造垂直參考線和參考點的坐標數據時，盡量采用單元格的引用，因為垂直參考線和參考點要隨著模型中銷量和單價的變化而變化。

5.使用IF函數得到決策結論文字

IF函數是常用的分支函數，使用IF（）函數能夠判斷數據的不同情況，得到不同的決策結論。在單元格C14中輸入公式：“=C6&"="&E6&"時，"&IF（E9>0，"盈利！盈利額="&E9，IF（E9<0，"虧損！虧損額="&E9"，盈虧平衡"）”，在C14單元格得到決策結論。最后，在圖表區添加一個文本框，在該文本框中輸入公式“=$C$14”，即可在圖表區顯示盈虧平衡分析的決策結果，如圖2所示。

6.制作改變模型中參數值的控件按鈕

在圖表中添加兩個微調項控件，使得銷量單元格E6從0到40 000按步長1 000變化，單元格E5從0.25到0.4按步長0.01變化，這樣就制作成了盈虧平衡銷量和單價可調圖形，且在文本框中顯示“盈利”、“盈虧平衡”、“虧損”等決策結論的文字。需要注意的是銷量的最大值是40 000，而微調項控件最大值只能是30 000，因此在對話框中的值縮小100倍，然后在模型工作表的單元格E6中輸入公式“=F6*100”；同理，單價的最小值是0.25，而微調項控件最小值只能是0，因此在對話框中的值擴大100倍，在模型工作表的單元格E5輸入公式“=F5/100”。

7.組合控件、文本框和圖表

將控件、文本框、圖表組合在一起制成盈虧平衡可調圖形，結果如圖3所示。

四、盈虧平衡分析決策模型的評價

（一）優點

首先，利用Excel建立的盈虧平衡分析決策模型能夠準確、快捷地計算出盈虧平衡銷量和盈虧平衡銷售收入，并得出決策結論，即銷量達到多少時，企業盈利，盈利額為多少，為公司決策者進行盈虧平衡分析提供了有力的依據和數據支持。其次，所建立的模型將所有已知的參數如固定成本、單位變動成本、單價和銷量安排在一個固定的單元格區域，模型中所有可變單元格的計算公式都統一使用了參數單元格、中間變量單元格和函數，沒有直接使用參數值和中間計算值。因此，當已知參數發生變化時，只需改變已知參數單元格中的數值即可，模型同樣會第一時間作出反應，計算出盈虧平衡銷量和盈虧平衡銷售收入，并得出決策結論。最后，利用Excel中的圖形工具為建立管理決策模型提供了一種極為有效的工具，特別是可調圖形不但使模型的決策過程與結果能動態表現，而且制作極為簡便，根本不需要任何編程。該模型中制作的XY散點圖能直觀地反映銷售收入、成本和利潤三者之間的關系，體現了模型分析能力與圖形直觀的人機界面的完美結合，決策者可以在圖形上調節參數，觀察反映決策結果的曲線及其特征的變化，從而提高管理決策模型的有效性，便于決策者從各個方面對盈虧平衡進行分析和決策。

（二）缺點

一是以上建立的盈虧平衡分析模型是建立在單一產品盈虧平衡分析基礎上的，然而，現實生產過程中，大多數企業都同時進行著多種產品的生產和經營，由于各種產品的銷售價格、單位變動成本、固定成本都不一樣，進行盈虧平衡分析時不能借助于產品的實物數量指標，因其實物數量不能簡單地相加，必須利用能夠綜合反映各種產品銷售量的金額指標，即銷售收入。因此，多品種盈虧平衡分析方法與單一品種盈虧平衡分析法不同，該模型的適用具有一定的局限性，只適用于單一產品的盈虧平衡分析。

二是該模型只對銷量和單價進行了變動分析，當模型中的一系列因素發生相互關聯的變化時，如公司外部環境條件發生變化，企業擬采取某些措施等都會使單價、固定成本、單位變動成本和銷量發生變化，此時，以上建立的盈虧平衡分析模型就不能適用了。

（三）模型的改進

1.建立多品種盈虧平衡分析模型

在遵循單一產品的盈虧平衡分析模型的基礎上對多種產品進行盈虧平衡分析，應根據不同情況采取相應的方法進行確定。目前，多品種的盈虧平衡分析主要方法是銷售金額配合法，銷售金額配合法是根據各種產品的銷售額占全部銷售額的比重，分別計算出全部產品的綜合盈虧平衡銷售收入和各種產品的盈虧平衡銷售收入。

2.建立盈虧平衡分析多因素變動分析模型

第一，可以為固定成本、單位變動成本、單價和銷量都添加窗體控件，即為各因素建立“滾動條”控件，利用用戶圖形接口工具建立多因素變動分析區，這樣能夠非常方便、直觀地進行多因素變動分析，決策者只需調節模型中的滾動條即可得到盈虧平衡分析的結果。上述利用“滾動條”按鈕工具建立的多因素變動分析模型，一個條件的變動需要有一個工作表來反映其結果，不便于分析。第二，利用Excel中提供的一種管理假設分析方案的工具——方案管理器就能很好地解決這個問題。此工具可以根據使用者或決策者輸入的多種假設條件，建立多個方案，每個方案都可以假設一組條件，根據這些條件，系統自動產生相應的多種結果，并將輸入值和結果作為方案的總結報告保存。通過查看方案總結報告，便可看到假設條件對目標值的影響。

【參考文獻】

[1] 劉蘭娟.財經管理中的計算機應用[M].上海：上海財經大學出版社，2014.

[2] 王艦.盈虧平衡分析的三種實現方法[J].會計之友，2009（9）：48-49.

[3] 杜茂寶.利用Excel建立項目投資動態盈虧平衡分析模型[J].財會通訊，2007（2）：69-70.

[4] 任紅松.投資項目動態盈虧平衡分析及Excel計算方法[J].技術經濟與管理研究，2007（6）：10-11.