Excel在企業本量利分析中的高級應用探索

趙文麗

本文運用Excel的規劃求解工具和微調器分別設計了聯合單位法和綜合邊際貢獻率法下的多品種本量利分析模型，為企業的經營決策提供一種快捷高效的途徑。利用Excel進行多產品條件下的本量利分析，可以將經營數據模型化，而且可以動態觀察各因素變化對盈虧平衡點的影響，使管理者的決策更加科學化、合理化。

本量利分析概念

本量利分析是研究成本―產量/銷售量―利潤依存關系的一種分析方法，又稱為CVP分析（Cost―Volume―Profit Analysis），它是一種基于成本性態分析和變動成本計算模式的分析方法。CVP分析以數學化的會計模型來揭示固定成本、變動成本、單價、銷售量、銷售額、利潤等多個變量之間的內在規律性，其中所蘊含的原理和方法為企業預測決策和規劃提供了一種必要的財務信息，是企業計劃和控制的重要工具。

在產品生產和銷售過程中，存在著兩種不同性質的成本：固定成本與變動成本。固定成本是在一定范圍內保持不變的成本，它是不隨銷量發生變化的成本，包括固定資產折舊費、辦公費用、管理人員工資、職工培訓費等。而變動成本指支付給各種變動生產要素的費用，如工人工資成本、原材料成本等。變動成本隨產量變化而變化，常常在實際生產過程開始后才需支付。

此外，在本量利分析中，貢獻毛益（又稱為邊際貢獻、邊際利潤）指的是銷售收入與變動成本的差額，也就是對企業盈利所作的貢獻。用貢獻毛益補償固定成本之后，若有剩余即為企業營業利潤。若貢獻毛益不足以補償固定成本，兩者差額為企業營業虧損。貢獻毛益率是指貢獻毛益總額占銷售收入總額的百分比，或單位貢獻毛益占銷售單價的百分比。

多品種下本量利分析的基本方法

在多品種條件下，由于每種產品的貢獻毛益不盡相同，因此，企業的保本點（盈虧平衡點）或保利點（實現目標利潤的銷售額）不能由各產品的銷售量直接相加，而只能以總的銷售收入金額來表示保本點。多品種條件下，確定保本點或保利點銷售額常用方法有聯合單位法、綜合邊際貢獻率法。

當企業的多種產品之間存在著相對穩定的產銷量比例關系，且預計的銷售量都能實現，那么就可以把這一比例關系的組合看作一個聯合單位，通過確定聯合單位的聯合單價及聯合單位變動成本，然后根據單一產品的本量利分析法計算出聯合保本或保利量，這就是聯合單位法。聯合單位法下保本點的關鍵計算步驟如下：首先計算聯合單價，聯合單價=Σ（各產品銷量比×該產品單價）；然后計算聯合單位變動成本，聯合單位變動成本=Σ（各產品銷量比×該產品的單位變動成本）；再計算聯合保本量，聯合保本量=固定成本/（聯合單價―聯合單位變動成本）；最后計算各產品的保本量和保本額，各產品的保本量=各產品的銷量比×聯合保本量、各產品的保本額=各產品的保本量×單價。

假定產品的品種結構保持不變，通過計算各產品的加權平均邊際貢獻率，來確定企業的綜合保本/保利點和每種產品的保本/保利點，這就是綜合邊際貢獻率法。綜合邊際貢獻率法下保本點的關鍵計算步驟如下：首先計算綜合邊際貢獻率，綜合邊際貢獻率=Σ（各種產品的邊際貢獻率×該種產品的銷售額比重）；然后計算企業綜合保本銷售額，企業綜合保本銷售額=企業固定成本總額/綜合邊際貢獻率；最后得出某種產品保本銷售額，某種產品保本銷售額=企業綜合保本銷售額×該種產品的銷售額比重。

聯合單位法在Excel中的設計與實現

例如，利達材料公司生產A、B、C三種產品，全廠固定成本為300000元。A、B、C三種產品：預計銷售量分別為100000 件、25000 件、10000件，預計銷售單價分別為10元、20元、50元，預計單位變動成本分別為8.5元、16元、25元。

在確定該公司的綜合及各產品的保本或保利銷售收入及銷售量之前，首先，按表1所示來設計多產品條件下的本量利分析模型。表1中，單元格C7為固定成本總額，A、B、C三種產品的單價分布在單元格D4至D6中，A、B、C三種產品的單位變動成本分布在單元格F4至F6中，A、B、C三種產品的銷售量分布在單元格C4至C6中；A、B、C三種產品的銷售收入分布在單元格E4至E6中，且其值為相對應的單價與銷售數量的乘積；單元格C9用來存放綜合保本或保利銷售額，單元格C8為預計的目標利潤總額，總利潤的計算公式見下文。

然后，按照Excel中規劃求解步驟來逐步求解。

一、多產品品類下本量利分析模型的目標單元格設計

顯然，本案例的決策目標就是多產品條件下的總利潤，即單元格C8。總利潤的計算公式為=SUMPRODUCT（C4 ： C6，D4 ： D6）―SUMPRODUCT（C4 ： C6，F4 ： F6―C7。規劃求解時，可以根據情況確定總利潤目標的數值為零（保本） 或預期的計劃利潤 （保利）。

二、多產品品類下本量利分析模型的可變單元格（即未知數）設計

當總利潤目標C8為零或預期利潤值時，規劃求解后A、B、C三種產品的保本或保利銷售量出現的具體位置，本例中把可變單元格設置為C4至C6（未知數）。在規劃求解前，C4至C6內的數據分別表示A、B、C三種產品的預計銷售量。

三、多產品條件下本量利分析模型的約束條件設計

A、B、C三種產品銷售量分別為100000 件、25000件、10000 件，它們的銷量比為10 ： 2.5 ： 1。聯合單位法下A、B、C三種產品的銷售量單元格C4至C6必須滿足：$C$4 ： $C$6≧0，$C$4 ： $C$6=整數，$C$4=10×$C$6，$C$5=2.5×$C$6。

在Excel 2010主菜單下，單擊“數據”選項卡下的“規劃求解”功能按鈕，啟動規劃求解功能，可變單元格、目標單元格和約束條件的設置都在“規劃求解參數”對話框中，然后單擊求解。

四、多產品條件下本量利分析模型的規劃求解結果解讀

如表2所示，求解前，若目標單元格C8的值設置為0，那么單擊“規劃求解”就可以立即看到A、B、C三種產品條件下的盈虧平衡分析的綜合運算結果。由此可以看出，計劃期A、B、C三種產品的保本銷售量分別為60000件、15000件、6000件，綜合保本銷售額為1200000 元。若求解前，目標單元格C8的值設置為170000，則可得出多產品條件下保利分析的最終運算結果。

綜合邊際貢獻率法在Excel中的設計與實現

下面利用Excel來實現綜合邊際貢獻率法下的多產品本量利分析模型，并且考慮固定成本、銷售單價、單位變動成本等因素變化對盈虧平衡點的影響。

首先，打開Excel工作表，建立數據區并錄入已知數據，如表3所示。

然后，在數據區按順序依次設置計算公式，如表3所示。分別在單價行的B5、C5、D5單元格中輸入“=10×H5”“=20×H5”“=50×H5”。分別在單位

位變動成本行的B6、C6、D6單元格中輸入“=8.5×H7”“=16×H7”“=25×H7”。在單元格E7中輸入固定成本的計算公式“=300000×H9”。為方便觀察銷售單價、單位變動成本和固定成本因素的變化對盈虧平衡點的影響，以上公式計算中用到的H5、H7、H9，會放置微調器來動態調整三個因素的數值。單位邊際貢獻=單價—單位變動成本，分別在單位邊際貢獻行的B8、C8、D8單元格中輸入“=B5―B6”“=C5―C6”“=D5―D6”。邊際貢獻率=單位邊際貢獻/單價，分別在邊際貢獻率行的B9、C9、D9單元格中輸入“=B8/B5”“=C8/C5”“=D8/D5”。邊際貢獻總額=銷量×單位邊際貢獻，分別在邊際貢獻總額行的B10、C10、D10單元格中輸入“=B4×B8”“=C4×C8”“=D4×D8”。銷售收入=銷量×單價，分別在銷售收入行的B11、C11、D11單元格中輸入“=B4×B5”“=C4×C5”“=D4×D5”，對E11單元格進行單擊“自動求和”計算出全年銷售收入。銷售比重=銷售收入/全年銷售收入，分別在銷售比重行的B12、C12、D12單元格中輸入“=B11/$E$11”“=C11/$E$11”“=D11/$E$11”。加權邊際貢獻率=邊際貢獻率×銷售比重，分別在加權邊際貢獻率行的B13、C13、D13單元格中輸入“=B9×B12”“=C9×C12”“=D9×D12”，對E13 單元格進行單擊“自動求和”，計算出全年加權邊際貢獻率。保本銷量=保本銷售額/單價，分別在保本銷量行的B14、C14、D14單元格中輸入“=B15/B5”“=C15/C5”“=D15/D5”。保本銷售額=綜合保本銷售額×銷售比重，分別在保本銷售額行的B15、C15、D15單元格中輸入“=$E$16×B12”“=$E$16×C12”“=$E$16×D12”。依據公式“綜合保本銷售金額=全年固定成本/全年加權邊際貢獻率”，在E16單元格中輸入公式“=E7/E13”。依據“利潤=綜合保本銷售金額×全年加權邊際貢獻率—全年固定成本”，在E17單元格中輸入公式“=E16×E13―E7”。

最后，設置輸出結果。在B18單元格中輸入=“盈虧平衡點：”&ROUND（E16，1）&“元”。

在表3的基礎上，在單元格G5、G7、G9中繼續添加3個微調器，目的是為了動態觀察固定成本、單位變動成本和銷售單價對綜合盈虧平衡點的影響，使決策更快捷。右擊數值調節鈕設置控件格式，最小值設為0，最大值設為200，步長為1， 這樣能把變動幅度控制在―50%至150%，“單元格鏈接”分別設置為G5、G7、G9。在H5單元格中輸入“=G5/100 ―50%”，H7、H9復制H5公式即可。

至此，一個可以對單價、單位變動成本、固定成本三個變量進行動態調整的多品種本量利分析模型就完成了。通過調整變量，幫助管理者做出合理的決策。

在當前狀態下，企業盈虧平衡點的綜合保本銷售額為1200000 元，三種產品的保本銷售量分別是60000件、15000件、6000件，保本銷售額分別是600000元、300000元、300000元，如表4所示。當前三種產品的實際銷量均大于各自的保本量，共實現銷售收入2000000元，大于綜合保本銷售額1200000元，表明企業處于盈利狀態。

本文利用Excel對多品種下的本量利分析，分別進行了聯合單位法和綜合邊際貢獻率法模型的設計與實現，從計算結果可以看出，兩種計算模型的計算結果是一樣的。第二種方法中還加入了微調器，便于動態觀察各因素對盈虧平衡點的影響，能夠極大地提高工作的效率與效果。本文案例只有三種產品，稍加改進就可推廣到更多品種的盈虧平衡分析中，可見，熟練使用Excel進行本量利分析，能在經營中提高決策的效率和質量。（作者單位：洛陽理工學院會計學院）