数据分析案例：Excel在企业经营分析中的运用案例
数据分析与量化
计划开办一家化工原料生产和销售企业，经营期为5年。生产和销售的产品为某种原料，根据预测，这种原料的需求量第1年为20吨，以后每年增加2吨。引进这种原料生产线的建设投资为每条30 万元，每条生产线的生产能力为每年7吨。如果开工生产线太少，生产能力不足，企业效益将会不好，如果生产能力太大，产品销售不出去，会产生过多的产品库存，同样会影响企业效益。因此，计划前两年引进2条生产线并投入生产，后3年再增加2条生产线。企业生产经营的其他数据如下图所示。对该企业5年生产经营进行风险分析的步骤如下：
根据这些数据，建立企业5年生产经营的确定性excel模型，有关单元格的定义说明如下：
第 1、2年产品生产量（B14~C14）= 前两年生产线数量（B2）×每条生产线生产能力（B5）；
第 3、4、5年产品生产量（D14~F14=后三年生产线数量（B3）×每条生产线生产能力（B5）；
年产品销售量（B16~F16）=MIN（上年末产品库存量+本年产品生产量，本年产品需求量）；
年末产品库存量（B18~F18=上年末产品库存量 + 本年产品生产量—本年产品销售量；
销售收入（B20~F20）= 产品销售价格×产品销售量；
库存费用（B22~F22）= 产品库存费用（B6）×年末产品库存量（B18~F18）；
前两年固定资产折旧（B23~C23）= 前两年生产线数量（B2）×固定资产折旧率（B8）；
后三年固定资产折旧（D23~F23）=(前两年生产线数量（B2）+ 后三年新增生产线数量（B3）)×固定资产折旧率（B8）；
税前利润（B25~F25）= 销售收入—生产成本—库存费用—固定资产折旧；
累积税前利润（B26~F26）= 以前及本年税前利润之和；
营业税（B26~F26）= 营业税税率（B9）×（销售收入—生产成本）；
累积营业税（B27~F27）= 以前及本年营业税之和；
累积所得税（B28~F28）= 所得税税率（B10）×累积税前利润；
税后利润（B28~F28）= 累积税前利润—累积营业税—累积所得税。
由上图可以看出，根据这些确定性的数据，该企业1~3年的累积税后利润都是负值，第4年的累积税后利润首次为正值。即该企业前3年的经营都将亏损，从第4年开始盈利。
以上的结论是根据确定性数据的前提得到的。如果这些数据发生变化，累积税后利润也将发生变化。
例如产品的需求量、产品销售价格、生产成本等，在未来5年肯定会发生变化，而且这种变化是随机的，但是会大致符合一定的趋势和规律。因此，要定义一个随机变量，需要确定这个随机变量的概率分布，它的一些统计参数，如均值、标准差等。
下面简化例子以便于观察和分析，假定模型中所有的随机变量都服从正态分布。它们的均值就是相应数据单元格的值，标准差等于均值的10% 。
将图1中第1年 ~ 第5年“产品需求量”、“产品销售价格”和“生产成本”三个变量定义为“假设变量”。
以“第 1 年产品需求量”为例，操作如下：将光标置于“第 1 年产品需求量”（B15），单击“定义假设”工具图标，出现“随机变量分布选项”，选择“正态分布（Normal）”，输
接着定义将第1年~第5年的销售价格定义为随机变量，其中“第1年销售价格”的对话窗口下图：
最后定义第1年 ~ 第5年的生产成本为随机变量，其中“第1年生产成本”的的设置如下图：
这样，就将模型中第1年 ~ 第5年“产品需求量”、“产品销售价格”和“生产成本”三个变量，共15个单元格定义为正态分布随机变量。
接下来定义模型中的预测变量。所谓预测变量是模型中随着假设变量的变化而变化，决策者需要观察的变量。在这个模型中，预测变量是第1年 ~ 第5年的累积税后利润。
将每个假设变量按照定义的概率分布和统计参数发生5000次随机的变化，产生随机变量的个数， 开始运行后，模型开出现第1年 ~ 第5年累积税后利润的概率分布图形。其中“第 5 年累积税后利润”的概率分布图形如下图所示：
由上图可以看出，第5年累积税后利润是随着15个假设变量的变化而变化的，它的分布类似正态分布，最大值为49.43万元，最小值为 - 39.43万元。由此可见，这个企业到第 5 年末还发生亏损的可能性是存在的。
如果要计算“第5年累积税后利润”大于0，即企业不亏损的概率，将图下方左侧文本框中“- infinity（负无穷大）”改为0，就可以得到企业在第 5 年末不亏损的概率为73.9% 。
如果要计算“第5年累积税后利润”小于0，即企业亏损的概率，将图下方右侧文本框中“+infinity（正无穷大）”改为0，得到结果为企业在第5年末亏损的概率为 26.1% 。
由此可见，这个企业生产经营的风险还是可控的。类似的，还可以计算给定事件发生的概率，例如第5年累积税后利润大于5万元的概率等。
下面是敏感图分析，可以看到需求量对上述结果的影响是最大的，其次是价格，成本对上述结果的影响是负向的。
下面来看看数据变动的变化，使用的是决策表的功能，决策表是用来表示当决策变量取不同的值时，选定的目标预测变量均值的变化。
模型中有2个决策变量：“前两年生产线数量”和“后三年新增生产线数量”。在运用决策表之前，需要先定义这两个决策变量。定义决策变量“前两年生产线数量”的对话窗口如下图：
上图看出两个决策变量不同的数值，目标预测变量“第5年累积税后利润”的均值是不同的，当“前两年生产线数量”为2条，“后三年新增生产线数量”为2条时，目标预测变量“第5年累积税后利润”的均值最大，为9.18万元。
数据分析与量化
商业分析案例、商业分析课程与咨询
