Excel在非線性條件下的本量利分析中的應用

[摘 要] 本量利分析作為一種對企業的成本總額、銷售量、銷售收入、產品單價、利潤等變量之間的內在聯系進行分析研究的定量分析法，在企業預測、決策、規劃和控制等方面得到了廣泛的應用。本文通過案例分析，重點探討了Excel在非線性條件下的本量利分析中的應用，其分析結論有助于管理者在執行決策時有效利用Excel工具，從而提高決策效率。

[關鍵詞] Excel;本量利分析;非線性條件;規劃求解

doi : 10 . 3969 / j . issn . 1673 - 0194 . 2010 . 21 . 001

[中圖分類號]F275 [文獻標識碼]A [文章編號]1673 - 0194(2010)21- 0002- 02

本量利分析法是在成本性態分析和變動成本法的基礎上，運用數量化模型揭示企業的變動成本、固定成本、相關業務量以及銷售單價與利潤在數量上的相互影響、相互制約關系的一種定量分析方法。它可以幫助財務人員進行利潤規劃和管理，為實現利潤最大化尋求各種途徑。為了隨時分析各種因素的變化對利潤指標的影響，可以通過建立本量利分析模型來對利潤變化的情況進行動態分析。

一、非線性條件本量利分析的基本原理

對本量利關系的理論研究是建立在銷售收入、成本總額和利潤與產銷量之間具有線性關系的基礎之上的，但在現實條件下，銷售收入、成本總額和利潤與產銷量之間可能并不具有線性關系，而是具有非線性關系。在這種情況下，可以通過非線性條件下本量利分析模型的建立來實現。

其分析可按以下步驟進行:首先建立基本分析模型結構;然后通過繪制反映本量利之間關系的散點圖來觀察因變量和自變量之間的變動趨勢，再通過添加趨勢線并顯示公式的方式來反映自變量和因變量之間關系的函數式;在此基礎之上可計算保本點和利潤最高點對應的產銷量。

二、非線性條件下的本量利分析模型的建立

案例基本資料如下:某公司只生產和銷售一種產品，產品的銷售單價為820元/件，預計在不同的產銷量水平下成本總額的有關資料如圖1中已知條件區域所示。本文通過建立Excel模型完成對本量利的分析并計算該公司保本點產銷量和最高利潤及其對應的產銷量。

具體操作步驟如下:

1.設計模型結構并繪制本量利基本關系圖。

如圖1所示建立該項目的已知條件區域;并選取以下單元格區域分別輸入數組公式:

單元格區域D3:D13 =A3:A13;

單元格區域E3:E13 =B14*D3:D13，計算不同產銷量水平下的銷售收入;

單元格區域F3:F13 =E3:E13-B3:B13，計算不同產銷量水平下的銷售利潤。

然后根據計算結果繪制反映該公司產品本量利之間關系的散點圖。

2.由圖1中的結果我們可以看出，銷售收入與產銷量之間具有線性關系，而成本總額與產銷量之間具有非線性關系。為了得到成本總額與產銷量之間的函數關系表達式，我們可進一步利用Excel的圖表功能，直接將鼠標指針對準表中的“成本總額數據”系列，然后單擊右鍵，在系統彈出的快捷菜單中執行【添加趨勢線】命令，在系統彈出的對話框中，在【類型】選項卡上，在“選項數據系列”欄中選擇“成本總額”系列，然后根據成本總額曲線的形狀與其變動趨勢比較符合的“多項式”，并在“階數”欄中選擇2。

3.單擊打開【選項】選項卡，選中“顯示公式”復選框和“顯示R平方值”復選框。單擊【確定】，即可在繪制完成的圖表上得到成本總額與產銷量之間的函數關系，即y=0.52x2 + 38.25x + 150 000，

同時得到R平方值為1。

3.Excel在非線性條件下的本量利模型中的應用有兩個關鍵點:

一是其非線性變量關系的確定需結合圖形來觀察變量之間的變動趨勢，然后通過添加趨勢線并顯示公式的方式來實現。

二是規劃求解工具的應用。Excel規劃求解的計算功能可以快速求解線性規劃函數，從而避免了煩瑣的筆算過程，提高了解決問題的效率和準確率，為財務管理人員提供了一個非常有用的分析工具。

主要參考文獻

[1]張蕊. Excel在管理會計中的應用——本量利分析[J]. 現代商業，2008(26).

[2]馬元駒.Excel動態圖表本量利分析模型的創建和應用[J].中國管理信息化:會計版，2005(10).

[3]張瑞君. 計算機財務管理[M]. 北京:中國人民大學出版社，2007.