Excel企业管理数据分析案例：选择最佳的生产方案

对产品未来市场销售状况的预期，是影响生产方案制定的主要因素之一。如果市场销售预期很好，那么企业则会加大投资力度，提升生产能力；如果市场销售预期不好，企业则会减少投资甚至削减生产量。因为没人能够准确地预测未来，所以使用单一确定意义上的预测结果来描述不确定的市场销售结果具有很大的不合理性。较为合理的做法是使用一个风险变量来描述产品市场销售状况的不确定性。这个风险变量应该能够描述多种可能的销售情景以及每个情景出现的概率。如果决策者认为风险变量包含的不确定性较大，超出了风险承受水平，企业主可能希望采取措施对市场做调查，进一步确定未来市场对产品的喜爱程度，以便做出更加理智的决策。然而，市场调查结果是否能够反应整个产品市场的真实情况也具有不确定性。如果这种情况存在，那么生产方案的制定将会更加复杂，需要考虑至少两种不确定性带来的风险。

下面就以一个实例来说明：一家企业正在考虑制定下一步生产方案。目前，有三个备选方案，方案一是投资30万对工厂设备做技术改造，大幅提高生产能力；方案二则是花费8.5万元对设备进行小修理，提高生产量；方案三是停止生产。

市场部给出关于产品未来市场销售的预期，包含三种可能情景：销售很好（可能出现的概率为0.3）、销售一般（出现概率为0.6）、销售很差（出现的概率为0.1），并给出了不同的生产方案在不同的市场销售预期下能够获得的年度收益如下表。假设最低吸引力收益率为MARR为20%，生产方案的研究期为5年，5年后所有方案的市场残值为0。

此外，公司管理层认识到，可以通过进一步研究客户需求来提高未来销售预期的准确性，于是希望聘请外部咨询公司对市场销售情况做前期调查。根据外部咨询公司以及公司市场部的经验，市场调查的结果包括两种情况：乐观和悲观。历史经验表明，未来销售情况与市场调查结果准确率之间具有一定的条件概率关心（如下图），例如当未来销售很好时，调查结果为乐观的概率为0.85，悲观的概率为0.15。

现在需要解决如下三个问题：

1、目前条件下，企业是否需要进行市场调查？

2、市场调查费用为多少时企业进行市场调查将不再合算？

3、以期望现值为决策依据选择最佳生产方案。

下面将详细说明解决的思路和方法。

从决策程序上看，这是一个两阶段决策问题，存在两个决策点：首先，企业应该判断是否聘用咨询公司做市场调查；第二，确定最佳生产方案。在第一个决策点，需要判断市场调查提供的信息价值是否大于咨询公司索要的信息费用。第二个决策点，需要判断哪个生产方案能够带来最大的期望现值。

本案的决策问题包含两个不确定因素：第一时咨询公司市场调查结果，有可能是乐观，也可能是悲观；第二个是市场销售预期，包含销售很好、销售一般、销售很差这三种可能。第一个不确定性是是对第二个不确定性的反应，所以两个不确定性存在关联性。需要各根据提供的信息，估算咨询公司市场调查结果为乐观和悲观的概率，同时计算市场调查结果为乐观的情况下，市场销售预期三种情形对应的概率，以及市场调查结果为悲观，市场预期三种情形的对应概率。也就是说，格局市场调查得到的新信息，对公司原有的先验概率进行修正，得到关于对未来产品市场销售的后验概率。

首先计算在不同的生产方案和不同的市场销售预期情形下，企业未来的收入现值，然后利用贝叶斯公式计算市场调查结果为乐观和悲观的概率以及对应的不同市场销售预期的概率。

贝叶斯公式可以利用收集到的信息，对有判断进行修正。假设B是由相互独立的事件组成的概率空间{B1,B2,B3,…,BN}，如果通过实验或者调查知道P(A|BI)，那么我们可以得到：

其中P(Bi)称为先验概率，P(A|Bi)表示Bi出现的条件下A出现的概率，称为后验概率。

下图展示了建立决策树模型所有的基础数据：

在excel中建立决策树模型，具体结果如下图所示：决策树最右侧每个分支后面又两个数字，上面的数字代表这个分支受到风险和不确定性因素影响后的发生概率，也就是联合概率，下面数字代表这个分支最后的价值，这里等于每个方案在每个销售情景下的累积价值，也就是现值-投资额。

根据下面的决策树图，可以看到每个决策节点都有一个图形标记，TRUE和FALSE。TRUE表示当前条件下该决策方案是最优方案，FALSE代表在当前条件下该决策方案不少最佳方案。所以，最优的决策过程是：进行市场调查，如果市场调查结果为乐观，选择方案1，如果市场调查结果为悲观，则选择方案2。

基于上面在excel中建立的决策数据，可以回答第一个问题，即：在目前条件下，企业是否应该进行市场调查。

下图给出了三个决策点的最优决策：C75处的市场调查？决策点，其最优决策是进行市场调查；E29处的生产方案？决策点，其最优决策是方案1；E57处生产方案？决策点，其最优决策是方案2.

现在我们可以回答第一个问题，在市场调查费用为0的条件下，最优决策是进行市场调查，市场调查提供的新信息可以改善分析结果，提供决策树的期望值。

下面来解决第二个问题：市场调查费用为多少时，企业进行市场调查将不再合算。

不再合算，意味着进行市场调查方案的期望价值要小于不进行市场调查方案的期望价值。如果增加一些咨询费用，那么进行市场调查方案的期望价值就会减少。可以继续增加咨询费用，找到一个数值，使得进行市场调查方案的期望值等于不进行市场调查的期望值。

这里可以通过excel自带的单变量求解器来求解，结果如下：

这样，就得到了第二个问题答案：当市场调查费用大于3163.25元时，企业进行市场调查将不再合算。