張雅暉

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

張雅暉

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

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

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

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

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

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

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

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

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

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

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

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

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

2.多產(chǎn)品下本量利分析的目標單元格設計

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

圖二 多產(chǎn)品下本量利分析的目標單元格設計

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

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

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

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

首先，在EXCEL2003主菜單下，選工具菜單下的對話框中的規(guī)劃求解功能啟動規(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。依次添加三項約束條件，詳見圖三。

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

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

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

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

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

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

圖五 多產(chǎn)品下實現(xiàn)目標利潤分析

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

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

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

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