规划求解工具在个人投资理财决策中的应用

来源:用户上传      作者:

　　 [提要] 在个人投资理财决策时，我们常常要考虑两个重要问题，就是如何分配财产，以及如何平衡风险与收益。规划求解是在满足一组约束条件的情况下，求出一个多变量函数极值的模型。在Excel 2010版本中，可以利用规划求解工具，根据实际问题建立数学模型，能非常方便地得到目标函数的最优值，以及取得最优值时各决策变量的取值。本文以个人投资理财决策为例，探讨利用规划求解工具进行分散投资，以获得最大回报的问题。
　　 关键词：Excel;投资理财;规划求解
　　 中图分类号：F83 文献标识码：A
　　 收录日期：2020年7月7日
　　 一、引言
　　 在经济迅速发展的时代，我们几乎都懂得一些理财知识。在理财的时候，我们常常要考虑的一个重要问题是如何分配投资以及如何平衡风险与收益。1981年，诺贝尔经济学奖得主詹姆斯·托宾说：“不要把鸡蛋放在同一个篮子里”，这句话仿佛成了投资理财的金句。为什么要把鸡蛋放在不同篮子里？资产配置其实是利用不同资产间的风险差异，降低整体风险，降低投资组合的波动率。这一理论经常用于投资，但要想得到更合理的资产配置，我们还是要具体问题具体分析。
　　 二、Excel与规划求解
　　 Excel是一款优秀的表格软件，在财会、金融、经济、科技、教育等领域发挥了很大的作用。Excel不仅具有较强的数据处理功能，还具有强大的绘图功能。尤其是其体系完备的函数能大大简化数据处理过程，能为财务、会计、统计等工作带来更大的帮助。Excel 2010版本中的规划求解是一个非常有用的工具，不仅可以解决运筹学中的线性规划问题，而且还能用来求解线性方程组和非线性方程组。
　　 在经济管理中，经常会遇到最优化问题，比如最大利润、最小成本、最优投资组合等问题。在运筹学上称为最优化原则，最优化的典型问题就是规划问题。规划问题包含两个方面：一是用尽可能少的人力、物力、财力资源去完成给定的任务;二是用给定的人力、物力、财力资源去完成尽可能多的工作。其实只有一个目的，就是利润的最大化，成本的最小化。例如，要生产几种不同的产品，每种产品在不同的设备上加工，加工工时不同，每种产品的利润也不同，在各设备生产能力有限的情况下，如何安排生产才能获得最大利润？再比如，某产品的产地和销地有若干个，从各产地到各销地的运费不同，在满足各销地需求量的情况下，如何调度可使得运费成本最小？生产的安排和运输的调度都可以用规划求解来解决。
　　 规划求解工具是Excel的一个可选安装模块。在默认安装Office时，规划求解工具并没有被安装。所以在默认情况下，Excel中不包含规划求解工具，所以打开Excel看不到规划求解按钮，需要安装规划求解工具后才能使用。安装office后，规划求解通常在硬盘上，因此不需要office安装盘就可以很方便的加载硬盘上的规划求解。加载“规划求解”的步骤如下：打开Excel，单击“文件”选项卡，单击“选项”，单击“加载项”，单击“转到”，勾选“规划求解加载项”，单击“确定”即可。加载“规划求解”后，就可以在“数据”选项卡的“分析”组中看到“规划求解”按钮。
　　 Excel中规划求解的基本步骤如下：
　　 第一步，确定决策变量（待解决的变量）X1，X2，…。在Excel中，存放决策变量的单元格称为可变单元格，用来存放X1，X2，…的值。
　　 第二步，确定目标函数Y。目标函数Y的值可根据实际情况设置为最大、最小或某个特定的整数。在Excel中确定目标单元格就是指定一个单元格为目标单元格，并在目标单元格中输入目标函数或公式。
　　 第三步，确定约束条件。设置规划任务的资源限制条件，比如人力、物力、财力等资源约束关系（等于、小于、大于等）。
　　 第四步，用规划求解工具求解。将可变单元格、目标单元格和约束条件等都放在规划求解对话框的指定位置，然后求解就可以了。
　　 三、个人投资理财中规划求解工具的应用
　　 某银行提供的各种理财产品的收益和风险如表1所示，可见高收益往往伴随着高风险。（表1）
　　 假如，银行理财经理接待了一位注重理财的客户，客户现在有50万元想理财投资。理财经理通过对该客户的风险测试和评估，认为该理财客户是一位稳健的投资者。因此，为该客户安排了一份投资计划，各理财产品分配情况如下：每个理财产品的购买金额≤25%;风险高的债券基金、混合基金、股票基金，购买总金额≤30%;风险高的混合基金、股票基金，购买总金额≤15%;风险中等的货币基金、债券基金，20%≤购买总金额在≤40%。
　　 为了得到最高的收益，同时又能满足该客户对风险的承受能力，如何估算出各种理财产品各投资多少呢？可利用Excel的数据分析工具——“规划求解”来解决本问题。首先在Excel中建立如图1所示的规划求解模型。（图1）
　　 规划求解操作步骤如下：
　　 第一步，确定决策变量。假设这六种理财产品的投资金额，分别用X1，X2，X3，X4，X5，X6表示，即规划决策的决策变量，也就是最终要找寻的答案。点击“数据”选项卡，单击“分析”组中的规划求解，打開“规划求解参数”对话框，在“通过更改可变单元格”处，把代表决策变量X1～X6的单元格区域设定为区域C5：H5。
　　 第二步，建立目标函数。目标函数就是投资回报的最大值，这里用MAX表示。
　　 MAX=2.75%×X1+3.50%×X2+2.50%×X3+6.00%×X4+7.00%×X5+10.00%×X6
　　 在工作表中，第3行C3：H3区域存放的是理财产品的投资回报率，第5行C5：H5区域正是对应各理财产品的投资金额。那么，这两行对应位置数据相乘的累加和，就是总的投资回报。这里把目标函数MAX的计算公式设置在J3单元格中，在J3单元格中输入目标函数：=SUMPRODUCT（C3：H3，C5：H5），之后还是在“规划求解参数”对话框中设置目标为J3，到“最大值”，将存放目标函数公式的J3单元格称为目标单元格。 　　 第三步，分别建立六个约束条件。
　　 （1）购买各理财产品的总金额等于投資总额50万元。在J5单元格中输入：=SUM（C5：H5），则J5表示的就是投资总金额。在“规划求解参数”对话框中单击“添加”，设置约束为J5=500000。
　　 （2）购买的每个理财产品金额必须大于等于0。即代表X1～X6的单元格区域C5：H5应都大于等于0。在“规划求解参数”对话框中单击“添加”，把C5：H5区域设置为大于等于0。
　　 （3）每个理财产品购买金额小于等于总购买金额的25%。即代表X1～X6的单元格区域C5：H5应都小于等于总购买金额的25%。这里把第6行的C6：H6分别输入总购买金额的25%，计算结果是固定常数。单击“添加”，设置约束为C5：H5≤C6：H6。
　　 （4）风险高的债券基金、混合基金、股票基金，三者总金额小于等于总购买金额的30%。
　　 约束条件为：6.00%×X4+7.00%×X5+10.00%×X6≤500000×30%
　　 在该约束条件中，只考虑债券基金、混合基金和股票基金，所以将其他三个产品的标志设为0，这三个产品的标志设为1。在J8单元格中输入：=SUMPRODUCT（$C$5：$H$5，C7：H7），这就是这三个基金的购买总金额。在L8中输入L5×30%，可设置约束为J8≤L8。
　　 （5）风险高的混合基金、股票基金，二者购买总金额小于等于总购买金额的15%。
　　 约束条件为：7.00%×X5+10.00%×X6≤500000×15%
　　 本约束条件仅考虑混合基金、股票基金，所以将其他四个产品的标志设为0，这两个产品的标志设为1。在J9单元格输入：=SUMPRODUCT（$C$5：$H$5，C8：H8），即这两个基金的购买总金额。在L9中输入L5×15%，可设置约束为J9≤L9。
　　 （6）风险中等的货币基金、债券基金，二者购买总金额在20%～40%。
　　 约束条件为：2.50%×X3+6.00%×X4≥500000×20%，2.50%×X3+6.00%×X4≤500000×40%。
　　 本约束条件仅考虑货币基金X3和债券基金X4，这两个产品的标志设为1，其他产品的标志设为0。在J9单元格输入：=SUMPRODUCT（$C$5：$H$5，C9：H9），即这两个基金的购买总金额。在L10、L11中分别输入L5×40%、L5×20%。可设置约束为：J10≥L11，J10≤L10。
　　 经过观察发现，（4）～（6）的约束条件J8、J9、J10分别小于等于L8、L9、L10，所以在设置约束条件时，可以合并，再单独设置J10≥L11。在设置各约束时，Excel会自动采用单元格的绝对引用方式，即行号和列标前都自动带了$符号。“规划求解参数”对话框设置完毕后，主要内容如图2所示。（图2）
　　 第四步，执行规划求解。
　　 在“规划求解参数”对话框中，单击“求解”按钮，C5：H5区域呈现出规划求解的结果，如图3所示。在满足这位投资人条件的情况下，投资理财计划如下：银行定期125，000元，银行理财125，000元，货币基金100，000元，债券基金75，000元，混合基金0，股票基金75，000元。同时，得到目标单元格J3的投资回报的最大值22，312.50。（图3）
　　 从以上操作步骤可以看出，规划求解的关键，首先是要设计出一个模型表格，将决策变量、目标函数、约束条件依次合理布局，然后利用“数据”选项卡“分析”组中的“规划求解”工具，在“规划求解参数”对话框中设置目标单元格。目标单元格中必须事先输入含有决策变量的计算公式，目标值可以根据实际情况设置为最大值、最小值和目标值。“可变单元格”即决策变量的单元格，决策变量往往是一个单元格区域，如C5：H5。“约束”栏用来输入约束条件，可多次单击“添加”输入多个约束条件。此外，单击“选项”可修改迭代运算的参数为“采用线性模型”以提高运算速度。所有设置完成后，单击“求解”，Excel就能自动完成计算过程。
　　 四、规划求解工具注意事项
　　 虽然使用规划求解工具，能帮助我们解决一些复杂的数据计算，提高工作效率和准确性，但是在使用中也应该注意一些特殊情况。规划求解的结果可能会出现三种可能：
　　 （一）规划求解找到一个解。找到唯一解是规划求解的目标，也是解决方案，会显示“规划求解找到一解，可满足所有的约束及最优状况”，这是最理想的计算结果。
　　 （二）规划求解找不到有用的解。如果没有可行解，显示“规划求解找不到有用的解”，这意味着使用给定的有限资源无法满足所有的约束条件。这通常是因为给定的约束条件过于苛刻，所以无法实现目标。
　　 （三）规划求解——“设置的目标单元格”的值未收敛。如果最优目标值是无界的，规划求解结果窗口将会显示：“设置目标单元格”的值未收敛。这种情况恰恰与上一个情况相反，通常是因为约束条件给的太宽泛，有无穷多组解。
　　 所以，我们在使用规划求解进行实际数据分析的时候，要注意设定的约束条件是否太过苛刻或太过宽泛，这也一定程度上影响着规划求解是否能找到合适的答案。
　　 五、总结
　　 在日常生活和工作中，我们经常会遇到最大值、最小值、最佳方法的问题。例如，生产的组织安排问题，运输的调度问题，原料的恰当搭配问题。当要寻找做某件事的最优方法时，可以考虑用“规划求解”来解决。规划求解是在满足一组约束条件的情况下，求出一个多变量函数极值的模型。在Excel中，建立规划求解模型，能非常方便地得到目标函数的最优值，以及取得最优值时各决策变量的取值。
　　 Excel是一款日常工作中常用的办公软件，熟练掌握Excel的办公技巧能极大地提高工作效率。我们应该意识到Excel不仅仅是优秀的表格工具软件，而且还能呈现可视化图形，更重要的是还能做常见的数据分析。随着新技术的发展，Excel的数据分析功能也会越来越强大，我们要善于挖掘Excel中一些高级功能，并结合专业进行应用，帮助我们分析工作中的各种数据，从而为决策提供参考。
　　 本文以个人投资理财决策为例，探讨了利用Excel规划求解进行分散投资以获得最大回报的问题。真正的分散投资讲究的是投资平台和理财产品的差异化，从平台和产品类型、投资期限、预期收益等多方面去分散投资。“不要把鸡蛋放在同一个篮子里”这句话经常被引用，不管是一个篮子，还是多个篮子，实际上我们还需要从风险角度出发，因为不同的风险带来的破坏力也是不同的。还需要看这些篮子之间是否具有独立性，如果几个篮子是有联系的，在面对风险时都会同时遭殃;如果这些篮子的性质是盈亏互补的，那就比较稳妥，可以规避风险。所以，在投资理财时，我们要从多个角度出发，具体问题具体分析，清楚自己的风险偏好，在可承受的风险能力下理性投资。
　　主要参考文献：
　　[1]杨尚群，乔红.Excel 2010商务数据分析与处理（第二版）[M].北京：人民邮电出版社，2016.8.
　　[2]杨俏文.Excel线性规划求解在管理会计中的应用[J].商业经济，2018（9）.
　　[3]段永峰.Excel在生产与运作管理任务分配教学中的应用[J].中国冶金教育，2018（10）.
　　[4]陈雪.Excel数据挖掘处理及应用分析[J].通讯世界，2018（12）.
　　[5]原永娟，孔凡尧.基于Excel工具的企业项目投资分析[J].山西财税，2019（12）.
转载注明来源:https://www.xzbu.com/2/view-15336207.htm