構(gòu)建Ｅｘｃｅｌ模板進行本量利分析

【摘 要】 本量利分析在企業(yè)預(yù)測、決策、規(guī)劃和控制中得到了廣泛的應(yīng)用，計算工作量相當(dāng)大。本文利用Excel 提供的函數(shù)、引用、模擬運算表、圖表制作等強大功能，通過分析模板的設(shè)計，自動得到本量利分析結(jié)果，提高了本量利分析的效率、數(shù)據(jù)計算的準確性和圖表繪制的規(guī)范性。

【關(guān)鍵詞】 本量利分析; Excel; 分析模板設(shè)計; 模擬運算表

本量利分析是成本、業(yè)務(wù)量和利潤三者依存關(guān)系分析的簡稱，它是在成本習(xí)性分析的基礎(chǔ)上，運用數(shù)學(xué)模型和圖型，對成本、利潤、業(yè)務(wù)量與單價等因素之間的依存關(guān)系進行具體的分析，研究其變動的規(guī)律性。它在企業(yè)預(yù)測、決策、規(guī)劃和控制中得到廣泛的應(yīng)用。本量利分析的理論和方法并不復(fù)雜，但計算工作量相當(dāng)大。應(yīng)用Excel進行本量利分析將會使計算工作大大簡化。本文擬對這一問題進行探討。

一、本量利分析的主要內(nèi)容

本量利分析的損益方程式為:

利潤=單價×銷量-單位變動成本×銷量-固定成本

M=P×Q-b×Q-F

分析的主要內(nèi)容包括:

(一)預(yù)期利潤分析

在規(guī)劃期間利潤時，根據(jù)單價、單位變動成本和固定成本、銷量關(guān)系，利用損益方程式計算出預(yù)期利潤。

(二)盈虧臨界分析與安全邊際分析

盈虧臨界分析主要研究利潤為零的特殊經(jīng)營狀態(tài)的有關(guān)問題，是本量利分析的一項基本內(nèi)容，亦稱損益平衡分析或保本分析。它主要研究如何確定盈虧臨界點、有關(guān)因素變動對盈虧臨界點的影響等問題，主要計算盈虧臨界點銷售量、盈虧臨界點銷售額、盈虧臨界點作業(yè)率。

根據(jù)盈虧臨界分析結(jié)果進一步進行安全邊際率分析。

安全邊際，是指正常銷售額超過盈虧臨界點銷售額的差額，它表明銷售額下降多少企業(yè)仍不致虧損。

安全邊際=正常銷售額(量)-盈虧臨界點銷售額(量)

安全邊際率是安全邊際與正常銷售額(或當(dāng)年實際訂貨額)的比值。

(三)敏感分析

(1)分析有關(guān)參數(shù)發(fā)生多大變化使盈利轉(zhuǎn)為虧損。

(2)分析各參數(shù)變化對利潤變化的影響程度。

反映敏感程度的指標是敏感系數(shù):

敏感系數(shù)=利潤變動百分比/參數(shù)變動百分比

筆者認為，敏感系數(shù)也可以利用(1)的結(jié)果直接計算，因為從盈利M到0表示利潤下降了100%，因此:

敏感系數(shù)=-100%/(1)下參數(shù)變動百分比

(四)影響利潤各因素變動分析

主要研究利潤不為零的一般經(jīng)營狀態(tài)的有關(guān)問題。包括:

1.編制單因素變動利潤敏感分析表與制作敏感性分析

列示各因素變動百分率及相應(yīng)的利潤值，各因素變動百分比通常以±20%為范圍。

2.分析單項因素變動對利潤的影響

分析銷量、單價、單位變動成本、固定成本諸因素中的一項變動對利潤產(chǎn)生的影響。

3.有關(guān)因素發(fā)生相互關(guān)聯(lián)的變動對利潤的影響

由于外界因素變化或企業(yè)擬采取某項行動，有關(guān)因素發(fā)生相互關(guān)聯(lián)的影響，企業(yè)需要測定其引起的利潤變動，以便選擇決策方案。

(五)實現(xiàn)目標利潤的有關(guān)條件分析

1.采取單項措施以實現(xiàn)目標利潤。

2.采取綜合措施以實現(xiàn)目標利潤。

二、案例資料

甲企業(yè)目前生產(chǎn)的A產(chǎn)品單價為2.2元，單位變動成本為1.5元，固定成本為42 000元，產(chǎn)銷量為12萬件，所得稅率25%。要求采用本量利分析法分析:

(1)A產(chǎn)品預(yù)期利潤為多少?

(2)A產(chǎn)品保本點為多少?安全性檢驗水平為什么等級?

(3)A產(chǎn)品有關(guān)參數(shù)發(fā)生多大變化使盈利轉(zhuǎn)為虧損、各參數(shù)變動對利潤的敏感程度為多少?

(4)編制A產(chǎn)品單因素變動利潤敏感分析表與制作敏感性分析圖。

(5)甲企業(yè)如果采取以下單項措施，預(yù)期利潤為多少?1)為提高市場競爭力產(chǎn)品價格下降到2元;2)采取措施將單位可變成本降到1.4元;3)采取措施將固定成本降到35 000元;4)提高生產(chǎn)效率將產(chǎn)量提高到15萬件。

(6)甲企業(yè)經(jīng)過多次綜合平衡采取以下綜合措施，預(yù)期利潤將為多少?1)產(chǎn)品價格下降到2.1元;2)單位可變成本降到1.45元;3)固定成本降到40 000元;4)產(chǎn)量提高到14萬件。

(7)甲企業(yè)提出要求，A產(chǎn)品要實現(xiàn)60 000元稅后利潤，不考慮其它參數(shù)變化各參數(shù)要達到什么水平?各參數(shù)發(fā)生關(guān)聯(lián)變化時，各參數(shù)應(yīng)達到什么水平?

三、本量利分析Excel分析模板設(shè)計思路

第一，將分析模板設(shè)計成兩大部分:條件區(qū)域與分析區(qū)域;

第二，在條件區(qū)域輸入各參數(shù)的現(xiàn)有水平、目標利潤、將要采取的措施等;

第三，在分析區(qū)域自動實現(xiàn)預(yù)期利潤分析、盈虧臨界分析與安全邊際分析、敏感分析、影響利潤各因素變動分析、實現(xiàn)目標利潤的有關(guān)條件分析。

模板設(shè)計完成后，分析者只需在條件區(qū)域輸入數(shù)據(jù)就可以自動得到本量利分析的各項分析結(jié)果。

四、本量利分析Excel分析模板具體設(shè)計

(一)設(shè)置條件區(qū)域(圖1)

(二)自動實現(xiàn)預(yù)期利潤分析(見圖2、具體公式見圖3)

(三)自動實現(xiàn)盈虧臨界分析與安全邊際分析(見圖2、具體公式見圖3)

B20“安全性檢驗”通過if函數(shù)進行自動判斷，B20=IF(B19>0.4，\"很安全\"，IF(B19>0.3，\"安全\"，IF(B19>0.2，\"較安全\"，IF(B19>0.1，\"值得注意\"，\"危險\")))

(四)自動實現(xiàn)敏感分析(見圖2、圖3)

敏感系數(shù)的計算不必按教科書所寫先計算參加變化10%的利潤，再計算利潤變化百分比，然后相除得到，直接用“-1/臨界時參數(shù)變化百分比”。

(五)自動實現(xiàn)影響利潤各因素變動分析

利用Excel“數(shù)據(jù)”下“模擬運算表”可以快速制作單因素變動利潤敏感分析表。

第一，在A30設(shè)變化率為0值(A30

=0)，作為“模擬運算表”引用行(變化率

-20%到+20%)的依據(jù)。

第二，在B32:B35輸入基本公式，一定要將變化率A30設(shè)計在公式中，否則無法運用“模擬運算表”功能。

第三，選中B31:K35，運用“模擬運算表”得到各參數(shù)變化-20%到+20%下的利潤結(jié)果。運算表中B32:B35為公式區(qū)域，“輸入引用行單元格”=A30，同時按Ctrl+Shirt+Enter就完成了。

具體過程見圖4。

根據(jù)敏感分析表的結(jié)果繪制敏感分析圖(見圖2)。

分析單項因素變動對利潤的影響與有關(guān)因素發(fā)生相互關(guān)聯(lián)的變動對利潤的影響，公式設(shè)計較為簡單，略。

(六)自動實現(xiàn)目標利潤的有關(guān)條件分析

1.采取單項措施以實現(xiàn)目標利潤:可以利用敏感系數(shù)進行計算。先計算目標利潤與原預(yù)期利潤的變化率，如:價格應(yīng)達到的水平=原價格×(1+利潤變動率/敏感系數(shù))。具體公式見圖5。

2.采取綜合措施以實現(xiàn)目標利潤:則在條件區(qū)域不斷調(diào)整參數(shù)水平，并在條件區(qū)域E9中設(shè)E9=K47(見圖1)，可以動態(tài)觀察目標利潤的變化，最終確定各項參數(shù)水平。

借助筆者利用Excel提供的函數(shù)、引用、模擬運算表、圖表等強大功能所做的以上設(shè)計，分析者只需在條件區(qū)域輸入各項參數(shù)水平以及將要采取的措施引起參數(shù)的變化值，就可以自動得到期望的本量利分析結(jié)果，大大提高本量利分析的效率、數(shù)據(jù)計算的準確性和圖表繪制的規(guī)范性?！?/p>

【主要參考文獻】

[1] 中國注冊會計師協(xié)會編[M].北京:經(jīng)濟科學(xué)出版社，2007.400-409.