張霞

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

【關(guān)鍵詞】 Excel； 盈虧平衡分析； 模型

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

一、盈虧平衡分析的基礎(chǔ)理論

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

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

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

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

（一）正確性

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

（二）可讀性

模型的基本含義與結(jié)論應(yīng)該便于創(chuàng)建者或其他使用者正確理解。模型應(yīng)提供多方面的配套分析數(shù)據(jù)和圖形，以便決策者從各個方面去深入理解它所提供的含義與性質(zhì)。

（三）易維護性

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

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

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

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

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

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

1.建立基本模型

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

2.采用模擬運算表準備繪圖數(shù)據(jù)

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

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

3.利用模擬運算表的數(shù)據(jù)創(chuàng)建圖表

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

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

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

5.使用IF函數(shù)得到?jīng)Q策結(jié)論文字

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

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

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

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

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

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

（一）優(yōu)點

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

（二）缺點

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

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

（三）模型的改進

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

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

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

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

【參考文獻】

[1] 劉蘭娟.財經(jīng)管理中的計算機應(yīng)用[M].上海：上海財經(jīng)大學(xué)出版社，2014.

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

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

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