EXCEL規劃求解在多種產品本量利分析中的應用

張雅暉

EXCEL規劃求解在多種產品本量利分析中的應用

張雅暉

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

規劃求解；多種產品；本量利分析；運用

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

一、多產品下傳統本量利分析的基本方法

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

綜合邊際貢獻率法是假設產品品種結構保持不變的情況下，通過計算多品種下的加權平均邊際貢獻率，來確定綜合及每種產品的保本、保利銷售額。

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

二、多產品下本量利分析的模型設計

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

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

1.多產品下本量利分析的基本單元格設計

圖一 多產品下本量利分析模板設計

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

2.多產品下本量利分析的目標單元格設計

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

圖二 多產品下本量利分析的目標單元格設計

3.多產品下本量利分析的可變單元格設計

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

4.多產品下本量利分析的EXCEL約束條件添加

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

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

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

三、多產品下本量利分析的規劃求解結果

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

圖四 多產品下盈虧平衡分析

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

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

圖五 多產品下實現目標利潤分析

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

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

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

（作者單位：新疆財經大學研究生學院）