营长说
这其实是运筹学中的线性规划问题。线性规划是运筹学中研究较早、发展较快、应用广泛、方法较成熟的一个重要分支,它是辅助人们进行科学管理的一种数学方法。广泛应用于军事作战、经济分析、经营管理和工程技术等方面。为合理地利用有限的人力、物力、财力等资源做出的最优决策,提供科学的依据。在Excel软件中对应的功能是规划求解。
先做出条件和目标公式设置。
在E3:E7区域为每个项目设置决策变量,变量为1表示选中该项目,变量为0表示放弃该项目。
在B9和C9设置对应的资金合计公式,如下:
B9=SUMPRODUCT(B3:B7,E3:E7)
C9=SUMPRODUCT(C3:C7,E3:E7)
在D11设置净现值合计公式:
D11=SUMPRODUCT(D3:D7,E3:E7)
接下来需要开启【规划求解】,通过【文件】-【选项】-【加载项】打开以下的的对话框。
选择【规划求解加载项】并确定后,会在【数据】选项卡中出现【规划求解】的命令按钮。
点击【规划求解】命令,打开【规划求解参数】对话框。
目标值为净现值合计单元格D11,规则是最大值。可变单元格是决策变量区域E3:E7。
约束条件分别为:
(1) 第0年资金限额,即B9<=B8;
(2) 第1年资金限额,即C9<=C8;
(3) 决策变量<=1,即E3:E7<=1;
(4) 决策变量为整数;
(5) 决策变量>=0,即E3:E7>=0;
点击【求解】按钮,如存在最优结果,则弹出【规划求解结果】对话框,可以选择制作【运算结果报告】。
即选择项目A、D、E,第0年使用资金580万元,第1年使用资金110万元,得到最大的净现值为750万元。
规划求解运算结果报告。
在很多情况下,企业可能面对多个投资项目,但由于资金限制不能全部进行投资,需要对这些项目进行取舍,实现组合投资优化。即在有限资金条件下,实现投资的收益最大化。Excel中的规划求解就可以快速实现。
案例 : 某企业现有5个可供选择的投资项目,各个项目在第0年和第1年的投资额和净现值如下图所示,但第0年和第1年均有资金限制,分别为600万元和150万元。 如何实现组合投资最优化?![49b1bf55c2d658053466757a933ae7d1.png](https://img-blog.csdnimg.cn/img_convert/49b1bf55c2d658053466757a933ae7d1.png)
![c4db310fcd11f7b2ef9768118f851ce7.png](https://img-blog.csdnimg.cn/img_convert/c4db310fcd11f7b2ef9768118f851ce7.png)
![507fa6fd70ecc45f67f2e294a53ebc5c.png](https://img-blog.csdnimg.cn/img_convert/507fa6fd70ecc45f67f2e294a53ebc5c.png)
![32330ab644ab297801d759403e8d7e0e.png](https://img-blog.csdnimg.cn/img_convert/32330ab644ab297801d759403e8d7e0e.png)
![fbf60f0d7daa8704b3095a0264bb7e68.png](https://img-blog.csdnimg.cn/img_convert/fbf60f0d7daa8704b3095a0264bb7e68.png)
![275923e456b795463af4b4f418e7b58d.png](https://img-blog.csdnimg.cn/img_convert/275923e456b795463af4b4f418e7b58d.png)
![9081ca25df8bf7e6992215f5402f56c8.png](https://img-blog.csdnimg.cn/img_convert/9081ca25df8bf7e6992215f5402f56c8.png)
![965d64ae34f11738cff3200c0211a874.png](https://img-blog.csdnimg.cn/img_convert/965d64ae34f11738cff3200c0211a874.png)
![def6a352ba47e74a1d2eed10fd73b87a.png](https://img-blog.csdnimg.cn/img_convert/def6a352ba47e74a1d2eed10fd73b87a.png)
操作动图如下:
本文节选自营长新书《Excel高效办公:财务数据管理》,购书赠送300分钟高清视频教程,获取方法见图书封底说明。
精彩推荐
▼▼▼
Excel三维地图,实现销售业绩动态可视化 2020-03-18 报告 | 阿里巴巴120页PPT,深度诠释国家“智能+”战略! 2020-03-16 腾讯课堂 | Excel+PPT,职场办公利器,700分钟高清教程,复工复产你准备好了吗? 2020-03-11 Excel和Visio联姻,自动生成跨职能流程图,还能用图标标记状态,太牛了! 2020-03-11 案例 | Excel九宫格矩阵评价统计,离不开这些函数 2020-03-09