Excel量化分析案例：洪水疏浚渠道工程扩建项目方案比较

沉默的白面书生

发布于 2020-05-09 16:53:50

4960

发布于 2020-05-09 16:53:50

文章被收录于专栏：全栈数据化营销

某地区的洪水疏浚项目目前具备700立方英尺的疏浚能力，经过工程分析和历史数据研究，得到不同渠道容量下发生洪水的概率，并给出不同建设方案的投资额，如下表：

历史数据显示，当洪水超过渠道疏浚能力时，发生严重洪灾的平均损失为20000万元，该渠道扩建工程的建设，通过发行期限为40年的债券募集资金，年利率为8%。则公司需要解决如下问题：

1、以等值年度费用期限作为判断标准，根据目前的信息和数据应该选择哪种扩建方式使得经济效果最为合理？

2、对发生洪灾的平均损失，以及流量1000立方英尺/秒扩建方案的资本投资额做敏感性分析，确定不同参数下的最佳扩建方案。

下面说明具体的解决方法：

根据要求存在5个方案：不扩建、修建1000立方英尺渠道、修建1300英尺渠道、修建1600立方英尺渠道、修建1900英尺渠道。评判方案优劣性的标准时每个方案的等值年度费用期望值的大小，这包括两部分：洪水超过疏浚能力时造成的平均损失期望值，以及债券募集基金的等值年度资本恢复额。这里使用构建决策树计算洪水超过疏浚能力时造成的平均损失期望值。把平均损失期望值与等值年度资本恢复额相加，得到每个方案的等值年度费用期望值，结果如下表所示：

构建上图的决策树之后，就可以回答第一个问题了。从上图可以看出，发行30000万决策分治后面具有‘TRUE’标记，这意味着此方案的等值年度费用期望值最少，也就最优方案。

上图是最优化设置的详细参数，‘Arrival probability’为100%意味着如果按照最优决策过程来决策，导致该决策方案的概率为1。‘Benefit of Correct Choice’表示该决策方案与最差决策方案的期望值之差为1715.80万元。

下图展示了每个决策方案对应的可能出现的结果及其发生的概率。例如，对于发行30000万的方案，有5%的可能性发生洪水，导致22515.80485万元的等值年度费用期望；有95%的可能性不会发生洪水，导致2515.8万元的等值年度费用期望。

下图以累积概率分布图的形式展示了每个方案对应的可能出现的结果及其发生概率：

下图给出了每个决策方案结果的基本统计数据：

下面来解决第二个问题：对发生洪灾的平均损失和流量为1000立方英尺/秒的扩建方案的资本投资额进行敏感性分析，确定不同参数下的最佳扩建方案。

从下图可以看出，随着洪水超过疏浚能力时造成的平均损失的增加，整个模型期望值在不断下降。但是，下降不少线性的，而是呈现出曲线形状。

结合下图，可以看出当洪水导致的平均损失较小并接近左边端点15000万元的地方时，最优方案时不发行。但是，当平均损失稍微增加一点就达到16779万元时，最优方案变成发行30000万元。当平均损失达到41694万元时，最优方案变成了发行40000万元。随后，平均损失下降到达120000万元为止，发行40000万元一直时最优方案。

下图说明，对于模型的期望值，‘洪水发生导致的平均损失’相对于‘发行20000万元方案的资本投资额’具有更高的敏感性。当输入变量‘洪水发生导致的平均损失’在其基础上的-25%到500%之间变化时，模型期望值的变化范围为[-6089.8，-3000]。而输入变量‘发行20000万元方案所需资本投资额’导致的模型期望值的变化范围为[-4000，-3096.5]。

下面的蜘蛛图更详细展示了随着输入变量的变化，模型期望值的变化情况：

下图是两个输入变量对模型输入值的三维敏感性分析图。从中可以看到，洪水超过疏浚能力时造成的平均损失为X变量，发行20000万元方案所需的资本投资额为Y变量，模型的期望值为Z变量，三个变量合在一起形成了一个三维曲面图，这个曲面图展示出，在两个自变量的联合变化形成的不同情况下，决策树的最优方案将会发生变化。