構建Ｅｘｃｅｌ模板進行本量利分析

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

【關鍵詞】 本量利分析; Excel; 分析模板設計; 模擬運算表

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

一、本量利分析的主要內容

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

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

M=P×Q-b×Q-F

分析的主要內容包括:

(一)預期利潤分析

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

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

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

根據盈虧臨界分析結果進一步進行安全邊際率分析。

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

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

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

(三)敏感分析

(1)分析有關參數發生多大變化使盈利轉為虧損。

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

反映敏感程度的指標是敏感系數:

敏感系數=利潤變動百分比/參數變動百分比

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

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

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

主要研究利潤不為零的一般經營狀態的有關問題。包括:

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

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

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

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

3.有關因素發生相互關聯的變動對利潤的影響

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

(五)實現目標利潤的有關條件分析

1.采取單項措施以實現目標利潤。

2.采取綜合措施以實現目標利潤。

二、案例資料

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

(1)A產品預期利潤為多少?

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

(3)A產品有關參數發生多大變化使盈利轉為虧損、各參數變動對利潤的敏感程度為多少?

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

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

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

(7)甲企業提出要求，A產品要實現60 000元稅后利潤，不考慮其它參數變化各參數要達到什么水平?各參數發生關聯變化時，各參數應達到什么水平?

三、本量利分析Excel分析模板設計思路

第一，將分析模板設計成兩大部分:條件區域與分析區域;

第二，在條件區域輸入各參數的現有水平、目標利潤、將要采取的措施等;

第三，在分析區域自動實現預期利潤分析、盈虧臨界分析與安全邊際分析、敏感分析、影響利潤各因素變動分析、實現目標利潤的有關條件分析。

模板設計完成后，分析者只需在條件區域輸入數據就可以自動得到本量利分析的各項分析結果。

四、本量利分析Excel分析模板具體設計

(一)設置條件區域(圖1)

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

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

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

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

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

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

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

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

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

-20%到+20%)的依據。

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

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

具體過程見圖4。

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

分析單項因素變動對利潤的影響與有關因素發生相互關聯的變動對利潤的影響，公式設計較為簡單，略。

(六)自動實現目標利潤的有關條件分析

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

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

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

【主要參考文獻】

[1] 中國注冊會計師協會編[M].北京:經濟科學出版社，2007.400-409.