Excel在企業(yè)本量利分析中的高級(jí)應(yīng)用探索

趙文麗

本文運(yùn)用Excel的規(guī)劃求解工具和微調(diào)器分別設(shè)計(jì)了聯(lián)合單位法和綜合邊際貢獻(xiàn)率法下的多品種本量利分析模型，為企業(yè)的經(jīng)營(yíng)決策提供一種快捷高效的途徑。利用Excel進(jìn)行多產(chǎn)品條件下的本量利分析，可以將經(jīng)營(yíng)數(shù)據(jù)模型化，而且可以動(dòng)態(tài)觀察各因素變化對(duì)盈虧平衡點(diǎn)的影響，使管理者的決策更加科學(xué)化、合理化。

本量利分析概念

本量利分析是研究成本―產(chǎn)量/銷售量―利潤(rùn)依存關(guān)系的一種分析方法，又稱為CVP分析（Cost―Volume―Profit Analysis），它是一種基于成本性態(tài)分析和變動(dòng)成本計(jì)算模式的分析方法。CVP分析以數(shù)學(xué)化的會(huì)計(jì)模型來(lái)揭示固定成本、變動(dòng)成本、單價(jià)、銷售量、銷售額、利潤(rùn)等多個(gè)變量之間的內(nèi)在規(guī)律性，其中所蘊(yùn)含的原理和方法為企業(yè)預(yù)測(cè)決策和規(guī)劃提供了一種必要的財(cái)務(wù)信息，是企業(yè)計(jì)劃和控制的重要工具。

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

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

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

在多品種條件下，由于每種產(chǎn)品的貢獻(xiàn)毛益不盡相同，因此，企業(yè)的保本點(diǎn)（盈虧平衡點(diǎn)）或保利點(diǎn)（實(shí)現(xiàn)目標(biāo)利潤(rùn)的銷售額）不能由各產(chǎn)品的銷售量直接相加，而只能以總的銷售收入金額來(lái)表示保本點(diǎn)。多品種條件下，確定保本點(diǎn)或保利點(diǎn)銷售額常用方法有聯(lián)合單位法、綜合邊際貢獻(xiàn)率法。

當(dāng)企業(yè)的多種產(chǎn)品之間存在著相對(duì)穩(wěn)定的產(chǎn)銷量比例關(guān)系，且預(yù)計(jì)的銷售量都能實(shí)現(xiàn)，那么就可以把這一比例關(guān)系的組合看作一個(gè)聯(lián)合單位，通過確定聯(lián)合單位的聯(lián)合單價(jià)及聯(lián)合單位變動(dòng)成本，然后根據(jù)單一產(chǎn)品的本量利分析法計(jì)算出聯(lián)合保本或保利量，這就是聯(lián)合單位法。聯(lián)合單位法下保本點(diǎn)的關(guān)鍵計(jì)算步驟如下：首先計(jì)算聯(lián)合單價(jià)，聯(lián)合單價(jià)=Σ（各產(chǎn)品銷量比×該產(chǎn)品單價(jià)）；然后計(jì)算聯(lián)合單位變動(dòng)成本，聯(lián)合單位變動(dòng)成本=Σ（各產(chǎn)品銷量比×該產(chǎn)品的單位變動(dòng)成本）；再計(jì)算聯(lián)合保本量，聯(lián)合保本量=固定成本/（聯(lián)合單價(jià)―聯(lián)合單位變動(dòng)成本）；最后計(jì)算各產(chǎn)品的保本量和保本額，各產(chǎn)品的保本量=各產(chǎn)品的銷量比×聯(lián)合保本量、各產(chǎn)品的保本額=各產(chǎn)品的保本量×單價(jià)。

假定產(chǎn)品的品種結(jié)構(gòu)保持不變，通過計(jì)算各產(chǎn)品的加權(quán)平均邊際貢獻(xiàn)率，來(lái)確定企業(yè)的綜合保本/保利點(diǎn)和每種產(chǎn)品的保本/保利點(diǎn)，這就是綜合邊際貢獻(xiàn)率法。綜合邊際貢獻(xiàn)率法下保本點(diǎn)的關(guān)鍵計(jì)算步驟如下：首先計(jì)算綜合邊際貢獻(xiàn)率，綜合邊際貢獻(xiàn)率=Σ（各種產(chǎn)品的邊際貢獻(xiàn)率×該種產(chǎn)品的銷售額比重）；然后計(jì)算企業(yè)綜合保本銷售額，企業(yè)綜合保本銷售額=企業(yè)固定成本總額/綜合邊際貢獻(xiàn)率；最后得出某種產(chǎn)品保本銷售額，某種產(chǎn)品保本銷售額=企業(yè)綜合保本銷售額×該種產(chǎn)品的銷售額比重。

聯(lián)合單位法在Excel中的設(shè)計(jì)與實(shí)現(xiàn)

例如，利達(dá)材料公司生產(chǎn)A、B、C三種產(chǎn)品，全廠固定成本為300000元。A、B、C三種產(chǎn)品：預(yù)計(jì)銷售量分別為100000 件、25000 件、10000件，預(yù)計(jì)銷售單價(jià)分別為10元、20元、50元，預(yù)計(jì)單位變動(dòng)成本分別為8.5元、16元、25元。

在確定該公司的綜合及各產(chǎn)品的保本或保利銷售收入及銷售量之前，首先，按表1所示來(lái)設(shè)計(jì)多產(chǎn)品條件下的本量利分析模型。表1中，單元格C7為固定成本總額，A、B、C三種產(chǎn)品的單價(jià)分布在單元格D4至D6中，A、B、C三種產(chǎn)品的單位變動(dòng)成本分布在單元格F4至F6中，A、B、C三種產(chǎn)品的銷售量分布在單元格C4至C6中；A、B、C三種產(chǎn)品的銷售收入分布在單元格E4至E6中，且其值為相對(duì)應(yīng)的單價(jià)與銷售數(shù)量的乘積；單元格C9用來(lái)存放綜合保本或保利銷售額，單元格C8為預(yù)計(jì)的目標(biāo)利潤(rùn)總額，總利潤(rùn)的計(jì)算公式見下文。

然后，按照Excel中規(guī)劃求解步驟來(lái)逐步求解。

一、多產(chǎn)品品類下本量利分析模型的目標(biāo)單元格設(shè)計(jì)

顯然，本案例的決策目標(biāo)就是多產(chǎn)品條件下的總利潤(rùn)，即單元格C8?？偫麧?rùn)的計(jì)算公式為=SUMPRODUCT（C4 ： C6，D4 ： D6）―SUMPRODUCT（C4 ： C6，F(xiàn)4 ： F6―C7。規(guī)劃求解時(shí)，可以根據(jù)情況確定總利潤(rùn)目標(biāo)的數(shù)值為零（保本） 或預(yù)期的計(jì)劃利潤(rùn) （保利）。

二、多產(chǎn)品品類下本量利分析模型的可變單元格（即未知數(shù)）設(shè)計(jì)

當(dāng)總利潤(rùn)目標(biāo)C8為零或預(yù)期利潤(rùn)值時(shí)，規(guī)劃求解后A、B、C三種產(chǎn)品的保本或保利銷售量出現(xiàn)的具體位置，本例中把可變單元格設(shè)置為C4至C6（未知數(shù)）。在規(guī)劃求解前，C4至C6內(nèi)的數(shù)據(jù)分別表示A、B、C三種產(chǎn)品的預(yù)計(jì)銷售量。

三、多產(chǎn)品條件下本量利分析模型的約束條件設(shè)計(jì)

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

在Excel 2010主菜單下，單擊“數(shù)據(jù)”選項(xiàng)卡下的“規(guī)劃求解”功能按鈕，啟動(dòng)規(guī)劃求解功能，可變單元格、目標(biāo)單元格和約束條件的設(shè)置都在“規(guī)劃求解參數(shù)”對(duì)話框中，然后單擊求解。

四、多產(chǎn)品條件下本量利分析模型的規(guī)劃求解結(jié)果解讀

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

綜合邊際貢獻(xiàn)率法在Excel中的設(shè)計(jì)與實(shí)現(xiàn)

下面利用Excel來(lái)實(shí)現(xiàn)綜合邊際貢獻(xiàn)率法下的多產(chǎn)品本量利分析模型，并且考慮固定成本、銷售單價(jià)、單位變動(dòng)成本等因素變化對(duì)盈虧平衡點(diǎn)的影響。

首先，打開Excel工作表，建立數(shù)據(jù)區(qū)并錄入已知數(shù)據(jù)，如表3所示。

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

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

最后，設(shè)置輸出結(jié)果。在B18單元格中輸入=“盈虧平衡點(diǎn)：”&ROUND（E16，1）&“元”。

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

至此，一個(gè)可以對(duì)單價(jià)、單位變動(dòng)成本、固定成本三個(gè)變量進(jìn)行動(dòng)態(tài)調(diào)整的多品種本量利分析模型就完成了。通過調(diào)整變量，幫助管理者做出合理的決策。

在當(dāng)前狀態(tài)下，企業(yè)盈虧平衡點(diǎn)的綜合保本銷售額為1200000 元，三種產(chǎn)品的保本銷售量分別是60000件、15000件、6000件，保本銷售額分別是600000元、300000元、300000元，如表4所示。當(dāng)前三種產(chǎn)品的實(shí)際銷量均大于各自的保本量，共實(shí)現(xiàn)銷售收入2000000元，大于綜合保本銷售額1200000元，表明企業(yè)處于盈利狀態(tài)。

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