EXCEL规划求解在多种产品本量利分析中的应用

2016-12-14张雅晖

中国乡镇企业会计 2016年2期

关键词：本量保本保利

张雅晖

张雅晖

本量利分析是管理会计的基础内容，本文利用大家熟悉的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。依次添加三项约束条件，详见图三。