在单元格D12中输入公式“=IRR(D24:D34)”，将单元格D12中的公式复制到单元格E12中。
（3）在单元格D13中输入公式“=IRR(D24:D34-E24:E34)”。
（4）建立贴现率为1%-20%的净现值单变量模拟运算表，如上图所示。发现12%时是-0.28，13%时是6.8，利用插值法公式：
“=C17+(D18-D17)/(D19-D17)*(C19-C17)”计算出的贴现率为12.04%。
4．略
30
实验六 Excel最优化问题模型的建立
一、实验目的
1．练习掌握Excel规划求解工具的使用。 2．掌握与财务管理相关的最优化求解问题。 二、实验内容
1．“猛牛”奶制品厂用牛奶为原料生产A和B两种奶制品。一桶牛奶可以在1号生产线上用12小时加工成3公斤A产品，或者在2号生产线上用8小时加工成4公斤B产品。由于市场对A、B两种奶制品的需求非常旺盛，因此每天所生产的A、B两种奶制品都能全部出售。根据目前的市场情况，销售每公斤A产品可获利24元，销售每公斤B产品可获利16元。“猛牛”奶制品厂每天能得到50桶牛奶的供应，每天工人的总劳动时间为480小时，并且1号生产线每天至多能加工100公斤A产品，2号生产线的加工能力没有限制。请为该厂制定一个生产计划，如何安排A、B两种奶制品的生产，能使每天获利最大。
2．Nutri-Jenny是一个体重控制中心。它为客户提供各种各样的冷冻主餐。这些主餐受到了严格的营养成分监控，保证客户膳食平衡。一种新的主餐称为“牛腰间肉大餐”。是由牛腰间肉和肉汁，加上一些豌豆、胡罗卜和面包卷制成的。Nutri-Jenny需要确定主餐中各种配料的数量以满足营养需求，同时使成本尽可能地低。每一种配料的营养成分和成本如表9-4所示。
表9-4 每种配料的营养成分和成本表
配料 牛腰间肉 肉汁 豌豆 胡萝卜 面包卷 维生素A 维生素C 卡路里 脂肪所含卡路里 （国际单位（毫克每盎（每盎司） （每盎司） 每盎司） 司） 54 20 15 8 40 19 15 10 15 350 1 3 1 蛋白质 成本 （克每盎（每盎司） 司） 8 1 1 1 40美分 35美分 15美分 18美分 10美分 主餐的营养要求如下：①必须含有280到320之间的卡路里；②脂肪所含卡路里不能超过总卡路里含量的30%；③必须至少含有600国际单位的维生素A、10毫克的维生素C、30克的蛋白质，而且由于实际原因，至少必须包含2盎司的牛腰间肉，每盎司牛腰间肉至少配有半盎司的肉汁。
要求：建立线性规划模型并求解，生成规划求解报告。
3．某企业有三个工厂，生产的产品将运到5个仓库，三个工厂的生产能力分别为310、260和280，每个仓库的需求量为180、80、200、160和220。从工厂运到各仓库的运费如表9-5所示。问该公司怎样安排，所花费的总运费最小？
31
表9-5 从工厂到仓库的运费表
运费 工厂A 工厂B 工厂C
仓库1 10 6 3 仓库2 8 5 4 仓库3 6 4 5 仓库4 5 3 5 仓库5 4 6 9 4．现有一笔资金，准备购买甲、乙、丙三个公司的股票，各公司在过去12年的收益率如表9-6所示。
表9-6 各公司过去12年的收益率 年份 1 2 3 4 5 6 7 8 9 10 11 12
甲 11.20% 10.80% 11.60% -1.60% -4.10% 8.60% 6.80% 11.90% 12.00% 8.30% 6.00% 10.20% 乙 8.00% 9.20% 6.60% 18.50% 7.40% 13.00% 22.00% 14.00% 20.50% 14.00% 19.00% 9.00% 丙 10.90% 22.00% 37.90% -11.80% 12.90% -7.50% 9.30% 48.70% -1.90% 19.10% -3.40% 43.00% 要求：在保证收益率不低于12%的前提下，怎样组合可以使风险最小？在方差不大于0.01的前提下，怎样组合可以使收益率最大？若风险厌恶度等于0.5，则最优证券组合是什么？此时的收益率和方差各为多少？
5．求解方程组。 20x?25y?16z?100 30x?18y?22z?80 8x?12y?40z?66三、实验步骤
1．
步骤1：分析问题，整理出决策变量、目标变量、目标函数和各项约束条件函数。
本生产计划是要解决如何安排A、B两种奶制品生产数量，才能获利最大，因此目标变量为总利润，设为y。决策变量为A、B两种奶制品生产数量，设x1和x2分别为A、B两种奶制品的产量（公斤），显然，x1和x2必须大于等于0.
32
因为每公斤A产品可获利24元，每公斤B产品可获利16元，那么目标函数即为：y=24x1+16x2。由于1号生产线可用12小时加工成3公斤A产品，因此生产每公斤A产品所需要的时间为4小时；同理，2号生产线生产每公斤B产品所需要的时间为2小时，生产两种产品的总时间不能超过480小时，这样工时的约束条件为：4x1+2x2≦480。因为一桶牛奶可加工成3公斤A产品或4公斤B产品，每天只能得到50桶牛奶，所以原材料的约束条件为：x1/3+x2/4≦50。又因为1号生产线每天只能加工100公斤A产品，2号生产线的加工能力没有限制，所以生产能力的约束条件为x1≦100.根据上述分析，可列出以下目标函数和约束条件：
max:y?24x1?16x2st:4x1?2x2?480x1x2??5034x1?100x1,x2?0步骤2：根据目标函数和约束条件，在Excel中建立参考模型。如下图所示。
步骤3：检查模型中各单元格的计算公式。 C9单元格中输入公式“=C8/C3*C4”，将其复制到D9中。 C10单元格中输入公式“=C8/C3”，将其复制到D10中。 C11单元格中输入公式“=C8*C5”，将其复制到D11中。
E8单元格中输入公式“=C8”，E9公式“=SUM(C9:D9)”，E10公式“=SUM(C10:D10)”，E11公式“=SUM(C11:D11)”
步骤4：设置规划求解的各项参数并求解。
选择“工具”中的“规划求解”选项，设置求解此问题的规划求解的各项参数。其中$E$11为目标函数的因变量，$C$8:$D$8为决策变量，$C$8:$D$8＞＝0和$E$8:$E$10＜＝$F$8:$F$10为约束条件。
步骤5：分析求解结果，仔细阅读“运算结果报告”、“敏感性报告”与“极限值报告”，并理解报告中的各项内容。
33
求解后发现：当1号生产线安排60公斤的生产量且2号生长线上安排120公斤的生产量时，“猛牛”公司的获利最大，达到3360元。 2．
步骤1：分析问题，整理出决策变量、目标变量、目标函数和各项约束条件函数。此步略。
步骤2：根据目标函数和约束条件，在Excel中建立参考模型。如下图所示。
步骤3：检查模型中各单元格的计算公式。 G14单元格中输入公式
“=B14*$B$22+C14*$C$22+D14*$D$22+E14*$E$22+F14*$F$22”，并利用填充柄复制到G18。
H20单元格中输入公式“=0.5*B22”，H22单元格中输入公式“=B10*B22+C10*C22+D10*D22+E10*E22+F10*F22”。
步骤4：设置规划求解的各项参数并求解。
选择“工具”中的“规划求解”选项，设置求解此问题的规划求解的各项参数。其中$H$22为目标函数的因变量，$B$22:$F$22为决策变量，$B$22〉=$H$19；$B$22:$F$22＞＝0；$C$22＞=$H$20；$G$14＜＝$I$14；$G$14＞＝$H$14；$G$15＜＝$I$15；$G$16:$G$18$＞=$H$16:$H$18为约束条件。单击【确定】即可。
3．
同上，步骤1略。 步骤2：根据目标函数和约束条件，在Excel中建立参考模型。如下图所示。
34