[原创]利用Excel巧妙处理项目可行性研究中的数据分析事宜
项目投资当然要认真进行利润核算，鄙人曾经撰写博文讨论过相关的财务指标与标准（详见拙作《MBA一日谈》“第二部分：会计与财务”的“投资决策分析”内http://blog.vsharing.com/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.vsharing.com/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.vsharing.com/linbindavid
四、 最后要说的几句话
1、 其它简单的计算如“投资回收期”等用加减乘除在Excel中就能计算，但建议事先把数字的摆放位置与公式设计好，注意数据间的关联关系。这样，在研究过程中，若一个数字因调整而发生变化，其它的数据结果也就发生相应的变化。有了这样的基础，可以减少很多研究工作中的不必要的麻烦，提高工作的效率，增加工作的快乐。
2、 笔者在博文《利用EXCEL提高工作管理效率》中（http://blog.vsharing.com/linbindavid/A911668.html）从其它的角度对Excel的用法进行了一些归纳，有兴趣者不妨一看，对大家的工作也许会有一些好处。
3、 如果目前在工具菜单中无“规划求解”，则请利用“加载宏”下载。若在“加载宏”中未找到“规划求解”，则需要到网络上寻找相关的软件下载。
4、如今进入新时代，尽量少用计算器，多用电脑进行计算。
赞 (0)