張雅暉

EXCEL規(guī)劃求解在多種產(chǎn)品本量利分析中的應(yīng)用

張雅暉

本量利分析是管理會(huì)計(jì)的基礎(chǔ)內(nèi)容，本文利用大家熟悉的EXCEL電子表格軟件，設(shè)計(jì)了多種產(chǎn)品條件下的本量利分析模板，并巧妙地借助于EXCEL的規(guī)劃求解功能，解決了多產(chǎn)品下本量利分析的復(fù)雜計(jì)算問題，提高了多產(chǎn)品下本量利分析決策的效率。

規(guī)劃求解；多種產(chǎn)品；本量利分析；運(yùn)用

在管理會(huì)計(jì)應(yīng)用中，多產(chǎn)品下的本量利分析涉及公式較多，計(jì)算較為麻煩，發(fā)生差錯(cuò)的可能性較大；雖然也有學(xué)者或?qū)嶋H工作者利用EXCEL來進(jìn)行本量利分析，但內(nèi)容大多局限于單一產(chǎn)品的本量利分析，即使極個(gè)別人利用EXCEL進(jìn)行了多產(chǎn)品下的本量利分析，但也是把手工計(jì)算過程搬到了電子表格里。本文巧妙地運(yùn)用EXCEL的規(guī)劃求解功能，一次同時(shí)求出綜合及分產(chǎn)品的保本、保利銷售收入及銷售量指標(biāo)，較好地解決了多產(chǎn)品下本量利分析的復(fù)雜問題。

一、多產(chǎn)品下傳統(tǒng)本量利分析的基本方法

在實(shí)際經(jīng)濟(jì)生活中，大多數(shù)企業(yè)不止生產(chǎn)銷售一種產(chǎn)品。在企業(yè)經(jīng)營(yíng)多種產(chǎn)品的情況下，主要用銷售收入來表示企業(yè)的保本點(diǎn)（盈虧平衡點(diǎn)）或保利點(diǎn)（實(shí)現(xiàn)目標(biāo)利潤(rùn)的銷售額），多產(chǎn)品條件下確定保本點(diǎn)、保利點(diǎn)的常用方法有綜合邊際貢獻(xiàn)率法及聯(lián)合單位法。

綜合邊際貢獻(xiàn)率法是假設(shè)產(chǎn)品品種結(jié)構(gòu)保持不變的情況下，通過計(jì)算多品種下的加權(quán)平均邊際貢獻(xiàn)率，來確定綜合及每種產(chǎn)品的保本、保利銷售額。

聯(lián)合單位法是指企業(yè)各種產(chǎn)品之間存在相對(duì)穩(wěn)定的產(chǎn)銷量比例關(guān)系，這一比例關(guān)系的組合可以看做一個(gè)聯(lián)合單位，通過確定每一聯(lián)合單位的單價(jià)及單位變動(dòng)成本，從而求出聯(lián)合及每種產(chǎn)品的保本、保利點(diǎn)。

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

利用EXCEL解決多產(chǎn)品下本量利分析決策問題，重點(diǎn)是如何把銷售量、單價(jià)、單位變動(dòng)、產(chǎn)品銷售條件、有關(guān)決策變量的約束等在EXCEL工作表中細(xì)化，下面以“聯(lián)合單位法”為基礎(chǔ)，通過例子給予說明

例：翔宇公司計(jì)劃期銷售甲、乙、丙三種產(chǎn)品，計(jì)劃期固定成本總額為21600元，目標(biāo)利潤(rùn)為5400元；甲、乙、丙三種產(chǎn)品：預(yù)計(jì)銷售量分別為1000件、2000件、2500件，預(yù)計(jì)銷售單價(jià)分別為50元、15元、8元，預(yù)計(jì)單位變動(dòng)成本分別為40元、9元、6元。要求確定該公司計(jì)劃期的綜合及分產(chǎn)品的保本、保利銷售收入及銷售量。

1.多產(chǎn)品下本量利分析的基本單元格設(shè)計(jì)

圖一 多產(chǎn)品下本量利分析模板設(shè)計(jì)

如圖一所示，單元格B7為固定成本總額，單元格C4至C6分別為甲、乙、丙三種產(chǎn)品的單價(jià)，單元格E4至E6分別為甲、乙、丙三種產(chǎn)品的單位變動(dòng)成本，單元格B4至B6分別為甲、乙、丙三種產(chǎn)品的銷售量；單元格D4至D6分別為甲、乙、丙三種產(chǎn)品的銷售額，分別為相對(duì)應(yīng)的銷量與單價(jià)的乘積；單元格B9為綜合保本或保利銷售額，單元格B8為計(jì)劃期目標(biāo)利潤(rùn)總額。把上例中的已知數(shù)據(jù)錄入圖一所示的模板中。

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

決策目標(biāo)是多產(chǎn)品的利潤(rùn)為零或計(jì)劃利潤(rùn)（本例中為54000元），這里選擇單元格B8作為多產(chǎn)品的總利潤(rùn)，總利潤(rùn)可通過sumproduct函數(shù)算出來，多產(chǎn)品下的利潤(rùn)總額：（總利潤(rùn)）B8=sumproduct(b4:b6,c4:c6)－sumproduct(b4:b6,e4:e6)－b7，這時(shí)多產(chǎn)品下本量利分析決策目標(biāo)就轉(zhuǎn)化為利潤(rùn)B8為零 (保本)或計(jì)劃利潤(rùn)54000元(保利)，利潤(rùn)計(jì)算圖示如下：

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

3.多產(chǎn)品下本量利分析的可變單元格設(shè)計(jì)

本例中選定單元格B4至B6為可變單元格，求解前分別表示甲、乙、丙三種產(chǎn)品的已知銷售量。當(dāng)目標(biāo)值利潤(rùn)單元格B8為零時(shí)，求解后可變單元格就變成甲、乙、丙三種產(chǎn)品的保本銷售量；當(dāng)目標(biāo)值利潤(rùn)單元格B8為計(jì)劃期利潤(rùn)54000元時(shí)，求解后可變單元格就變成甲、乙、丙三種產(chǎn)品的保利銷售量。

4.多產(chǎn)品下本量利分析的EXCEL約束條件添加

在完成了上述的單元格設(shè)計(jì)及布局之后，下面以EXCEL2003為例，說明多產(chǎn)品下本量利分析決策的規(guī)劃求解參數(shù)設(shè)置。

首先，在EXCEL2003主菜單下，選工具菜單下的對(duì)話框中的規(guī)劃求解功能啟動(dòng)規(guī)劃求解功能。其次，添加銷售量的約束條件。甲、乙、丙三種產(chǎn)品銷售量分別為1000件、2000件、2500件，它們的銷量比為1：2：2.5；單元格B4至B6分別為甲、乙、丙三種產(chǎn)品的銷售量，聯(lián)合單位法下單元格B4至B6必須滿足：B4:B6>=0，B4: B6為整數(shù)；B5=2*B4，B6=2.5*B4。依次添加三項(xiàng)約束條件，詳見圖三。

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

三、多產(chǎn)品下本量利分析的規(guī)劃求解結(jié)果

約束條件添加完畢后，讓“圖三”中目標(biāo)單元格B8的值為零，點(diǎn)擊圖三中“求解”按鈕，便可得出多產(chǎn)品下的盈虧平衡分析的最終運(yùn)算結(jié)果，見圖四。

圖四 多產(chǎn)品下盈虧平衡分析

盈虧平衡分析的結(jié)果是：該公司計(jì)劃期甲、乙、丙三種產(chǎn)品的保本銷售亮分別為800件、1600件、2000件，計(jì)劃期綜合保本銷售收入為80000元。

把圖三中目標(biāo)單元格B8的值設(shè)置為“計(jì)劃期目標(biāo)利潤(rùn)54000元”，點(diǎn)擊圖三中“求解”按鈕，便可得出多產(chǎn)品下實(shí)現(xiàn)目標(biāo)利潤(rùn)分析的最終運(yùn)算結(jié)果，見圖五。

圖五 多產(chǎn)品下實(shí)現(xiàn)目標(biāo)利潤(rùn)分析

實(shí)現(xiàn)目標(biāo)利潤(rùn)分析的結(jié)果是：該公司計(jì)劃期甲、乙、丙三種產(chǎn)品的實(shí)現(xiàn)目標(biāo)利潤(rùn)銷售量分別為2800件、5600件、7000件，計(jì)劃期綜合保利銷售收入為280000元。

就本例來說，雖然只有三種產(chǎn)品，但稍加改進(jìn)便可推廣到其他多種產(chǎn)品的本量利分析，品種越多，運(yùn)用EXCEL進(jìn)行本量利分析決策的優(yōu)勢(shì)就越明顯，可以最大地減輕手工計(jì)算工作量，提高決策質(zhì)量，更好地服務(wù)于經(jīng)營(yíng)管理。

[1]孫茂竹等，管理會(huì)計(jì)學(xué)(第7版)[M].中國(guó)人民大學(xué)出版社，2015.8.

（作者單位：新疆財(cái)經(jīng)大學(xué)研究生學(xué)院）