利用Excel巧妙处理项目可行性研究中的数据分析事宜 -转自: 管理人中人 - 畅享博客

利用Excel巧妙处理项目可行性研究中的数据分析事宜

http://blog./linbindavid

 

项目投资当然要认真进行利润核算，鄙人曾经撰写博文讨论过相关的财务指标与标准（详见拙作《MBA一日谈》“第二部分：会计与财务”的“投资决策分析”内http://blog./linbindavid/A911714.html），这些计算如果用计算器手工进行，需要一定的数学背景（如微积分知识等），并且费时较多，容易出差错，用Excel则可以大大减轻工作量，提高工作效率，另外还未必需要非常严格的数学训练。本文尝试在这方面做一些探讨，与大家共同分享一些计算心得。

一、             净现值与内部收益率的计算

在项目的财务分析中，最经常使用NPV（净现值）与   IRR（內部收益率），若项目投资的NPV＞0或IRR＞资本成本，则认为项目是可行的。手工计算用插入法、查表等手段，比较烦人，用Excel则可轻易搞定。举例如下∶

项目投资与收益情况（单元格A1）	收益（单元格B1）	在Excel中输入的公式
期初投资(单元格A2)	-200
第一年收益	30
第二年收益	50
第三年收益	90
第四年收益	150
第五年收益	200
净现值（NPV）	220.2	=NPV(5.76%,B3:B7)+B2
內部收益率（IRR）	30.0%	=IRR(B2:B7)

说明∶

• 以5.76%作为资本成本；
• NPV=220.2＞0，可以投资；
• IRR=30.0％＞5.76%，可以投资；
• NPV计算每一年期末现金，故期初的投入应单独列出来相加；
• IRR的定义是NPV值等于零时的折现率，将IRR值代入NPV公式中，则净现值=NPV（30.0%，B3:B7）+B2=0。从这种替代计算中，我们更可以进一步理解NPV与IRR的定义。

二、             保本规模与最佳生产规模的计算

某公司欲投资一项目，其固定成本也未必是固定的，与生产规模有一定关系，其公式为“固定成本=20000+产量规模^2/3500”，单位变动成本为9，单价为25，欲知其保本生产规模与最佳生产规模。

1、保本点的计算

保本点=固定成本/（价格－变动成本），算起来不难。但在这里，固定成本也未必固定，所以用手工算就有一些麻烦。遇到这种求极值的问题，自然想起了Excel中的“规划求解”，用这样的工具求最优解非常舒适，看来完全可以用此工具计算项目的保本点。做法如下∶

1）制表

用手工所在Excel中制作出下表∶

生产规模（单元格A1）	固定成本	变动成本	单价	单件利润	总利润（单元格F1）
		9	25
上格所用公式	=20000+A2^2/3500			=D2-B2/A2-C2	=E2*A2

2）用规划求解计算

点击Excel中的工具→规划求解，设定目标单元格F2的值为0，可变单元格为A2，点击求解，得出下表∶

保本生产规模（单元格A1）	固定成本	变动成本	单价	单件利润	总利润
1279	20468	9	25	0.00	0.00
上格所用公式	=20000+A2^2/3500			=D2-B2/A2-C2	=E2*A2

轻易算出保本产量（A2）为1279件，此时的总利润为0。就是说，生产规模必须不低于1279，此项目才能不出现亏损情况。

2、最佳生产规模的计算

1）制表

用手工所在Excel中制作出下表∶

生产规模（单元格A1）	固定成本	变动成本	单价	单件利润	总利润（单元格F2）
		9	25
上格所用公式	=20000+A2^2/3500			=D2-B2/A2-C2	=E2*A2

2）用规划求解计算

点击Excel中的工具→规划求解，设定目标单元格F2的值为“最大值”，可变单元格为A2，点击求解，得出下表∶

生产规模（单元格A1）	固定成本	变动成本	单价	利润	总利润
28000	244000	9	25	7.29	204000.00
上格所用公式	=20000+A2^2/3500			=D2-B2/A2-C2	=E2*A2

轻松算出最佳生产规模为28000件，此时的利润为204000元。http://blog./linbindavid

三、             “规划求解”可以用在项目设计的其它方面

在可行性研究中，经常涉及到厂址的选择与生产线的设计等方案，这个时候，要考虑到运输、搬运成本的最小化，“规划求解”能够帮上大忙。比如，工厂选址需要考虑与原料供应地的距离，欲使运输成本最低，不妨求助于“规划求解”。请看下例∶

某公司生产需要三种不同的原材料，供应地分别在A、B、C三个不同的地方，按一般情况，到A地运货的机会为30％，B地运货的机会为50％，此地运货的机会为20%，怎样使运输成本最低呢？步骤如下∶

1、 设定坐标系

以坐标系为基础，确定三厂的位置值。在此，假设为A(2,8)、B（9,3）、C（8,8），厂址的位置为未知数，定为（X,Y）。则，

距离公式=[（X-2）^2+(Y-8)^2]^1/2+[（X-9）^2+(Y-3)^2]^1/2+[（X-8）^2+(Y-8)^2]^1/2

考虑到运输频率的不同，则公式变为∶

距离公式=30%×[（X-2）^2+(Y-8)^2]^1/2+50%×[（X-9）^2+(Y-3)^2]^1/2+20%×[（X-8）^2+(Y-8)^2]^1/2

2、 计算

点击Excel中的工具→规划求解，设定目标单元格F2的值为“最大值”，可变单元格为A2，点击求解，得出下表∶

厂址∶	位置
X	6.7
Y	5.5
A地∶	2
	8
B地∶	9
	3
C地∶	8
	8
最优解	7.93

可以判断出，厂址的位置应选择（6.7,5.5）的位置为最佳位置。生产线等等规划设计都可以用这样的思路与办法解决。很多时候，我们都可以依此办法来进行项目的规划设计。

特别需要指出的是设置条件时需要考虑各种条件，所以，在“规划求解”中要特别注意使用的一个条件就是“约束”，可以在这里把各种约束条件输入（比如，产量必须超过一定数量，原材料的有效性等等），这样，就可以获得更科学的规划答案。http://blog./linbindavid

 

四、             最后要说的几句话

1、 其它简单的计算如“投资回收期”等用加减乘除在Excel中就能计算，但建议事先把数字的摆放位置与公式设计好，注意数据间的关联关系。这样，在研究过程中，若一个数字因调整而发生变化，其它的数据结果也就发生相应的变化。有了这样的基础，可以减少很多研究工作中的不必要的麻烦，提高工作的效率，增加工作的快乐。

2、 笔者在博文《利用EXCEL提高工作管理效率》中（http://blog./linbindavid/A911668.html）从其它的角度对Excel的用法进行了一些归纳，有兴趣者不妨一看，对大家的工作也许会有一些好处。

3、 如果目前在工具菜单中无“规划求解”，则请利用“加载宏”下载。若在“加载宏”中未找到“规划求解”，则需要到网络上寻找相关的软件下载。

4、如今进入新时代，尽量少用计算器，多用电脑进行计算。