張霞

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

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

中圖分類號(hào)：F235.19 文獻(xiàn)標(biāo)識(shí)碼：A 文章編號(hào)：1004-5937（2015）19-0077-03

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

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

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

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

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

（一）正確性

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

（二）可讀性

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

（三）易維護(hù)性

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

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

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

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

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

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

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.采用模擬運(yùn)算表準(zhǔn)備繪圖數(shù)據(jù)

以銷量作為自變量，同時(shí)對(duì)銷售收入、總成本和利潤(rùn)三個(gè)因變量函數(shù)進(jìn)行一維模擬運(yùn)算，注意，在單元格I3、I4中輸入自變量的各個(gè)值，因?yàn)楸景咐且粋€(gè)線性問題，所以只需輸入自變量銷量的起始值（0）和終止值（40 000），在單元格J2、K2、L2分別引用單元格E7、E8、E9。

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

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

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

4.在圖形上添加垂直參考線和參考點(diǎn)

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

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

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

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

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

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

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

四、盈虧平衡分析決策模型的評(píng)價(jià)

（一）優(yōu)點(diǎn)

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

（二）缺點(diǎn)

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

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

（三）模型的改進(jìn)

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

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

2.建立盈虧平衡分析多因素變動(dòng)分析模型

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

【參考文獻(xiàn)】

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

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

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

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