本文共计约2000字，建议阅读时间3分钟目录
本篇开始讲Excel的建模功能。 模拟分析 Excel中最为人称赞的功能之一是可以创建动态模型。由我们前面已经掌握的知识，可以很简单创建一个随引用单元格变化而变化的公式，但如果参数较多、公式较为复杂时，管理起来比较混乱。 Excel为解决这种场景，有一个非常实用的工具，叫“模拟分析”。 举个例子，假设我希望通过投资理财实现10年后资产超过500万，那么我需要多少本金和年化收益率，目标有可能能达成。影响10年后资产的关键因素有2个，一是初始本金，二是年化收益率。 第一步，我们需要建立10年后资产与初始本金及年化收益率的公式关系。  第二步，建立模拟运算表的初始表格，左上角是链接到目标单元格的公式。  第三步，选中初始表格，运用“数据”|“预测”|“模拟分析”|“模拟运算表”来输出模拟运算结果。  用条件格式将结果超过500万的标识出来，可以看到，有三种可能的组合。
 模拟分析一般是用来解决what-if问题，还可以通过多种组合的模拟数据，判断结果受哪个因素影响更大，即敏感度分析。 在上面这个例子中，10年后的资产受年化收益率的影响更大一些，因为同样目标是500万，但初始资产50万是200万的1/4，但年化收益率却不需要是10%的4倍，25-30%即可。 这就是复利的力量。——爱因斯坦所说的第8大奇迹。 这只是双因素模拟分析。如果因素再加一个呢，比如再加上每年的工资结余。 这就得用上方案管理器了。 第一步，仍然是先公式建模。  第二步，添加方案，设置每个方案的参数值。操作路径是“数据”|“预测”|“模拟分析”|“方案管理器”|“添加”。   第三步，将输入参数和输出结果，定义名称。“公式”|“定义的名称”|“工具所选内容创建”，选择最左列。  在名称管理器，可以看到刚刚定义的名称。  第四步，输出方案。操作路径是“数据”|“预测”|“模拟分析”|“方案管理器”|“摘要”。   但参数数量不多、公式不复杂，且方案数量也不多的时候，使用方案管理器的必要性不大。 手动模拟结果如下。除了公式冗余外，没有其他缺点。相比方案管理器，调整参数更加方便，公式可以追踪，这更有掌控感。 规划求解 规划求解要解决的问题跟上述问题相反。上述问题是，我们已经有了方案（参数），能达到什么目标。只不过方案可以有多个进行比较。 而规划求解是，假设我已经知道需要达到什么样的目标，在既定的限制条件下，我能做什么。 我们将上述案例变换一下，假设我有50万初始资产，需要多少年化收益率，可以10年后总资产达到500万。 第一步，建立10年后资产与初始本金及年化收益率的公式关系，明确固定条件和可变条件。 第二步，点击“数据”|“分析”|“规划求解”，选择“设置目标”为十年后资产，可变单元格为年化收益率，点击求解，保留规划结果。 可知，50万初始资产在10年后变成500万，需要年化收益率达到26%。 这其实是一个非常简单的规划求解案例，用代数求解法在Excel通过公式也可以得出。 规划求解的难点从来都不在解法上，而是在模型的构建上。 再讲一个稍微复杂的案例，是《数据、模型与决策：基于电子表格的建模和案例研究方法》这本书的一个案例习题。 一家手机制造商的营销小组计划进行一次电话调研，以确定消费者对一款正在开发的手机的态度。为了使这一分析具有充足的样本量，他们需要接触至少100名年轻男性( 40岁以下）、150名年龄较大的男性（40岁以上）、120名年轻女性（40岁以下）和200名年龄较大的女性(40岁以上）。白天打一个电话的成本是1美元，晚上打一个电话的成本是1.5美元（因为晚上人工成本较高）。不管有没有人接听电话，这一成本都会发生。下表给出了特定类型的消费者接听电话的概率。假设不管是谁先接听电话,调研都可以开始进行。另外，由于晚上员工数量有限，所以最多只能有1/3的电话是在晚上打出的。营销小组应该如何做才能以最低的成本满足样本量的要求? 先构建模型。 假设条件是白天和晚上各打多少个电话（绿色底纹），初始数值随便先写一个，分别是1和0。目标总成本=C34*C35+D34*D35。 设置约束条件，个人群样本量=白天电话数*白天接电话概率+晚天电话数*晚天接电话概率计算，晚上打电话比例=晚上打电话数/(白天打电话数+晚上打电话数)。 规划求解的设置如下图所示。点击求解可以得到结果。 找到一个最优解，如下图所示，需要白天打500个电话，晚上打250个电话，所有约束条件均满足，并且总成本最低。 但其实最优解跟设置的初始参数是有关的。并不一定有解，也并不一定单次运行下的最优解是全局最优解。 比如，我们将初始参数设置是0和1时（白天打电话数量为0，晚上打电话数量为1），结果如下，找不到有用的解，电话数量已经迭代到几千万次了。 这就要求，设置初始参数时不能过于随意。比如这个案例下，单个电话成本，晚上比白天打电话贵50%，但接听电话的概率整体没有高那么多（95/80-1=19%），明显白天打电话更为划算，所以设置初始参数时，要把白天电话数设置成比晚上电话数更大。 |
|
|
