《管理定量分析》实验指导书.doc

管理定量分析实验指导书 实验报告要求一、 实验报告要求格式符合规范、文字书写工整、版面整 洁、言简意明；二、 实验报告内容包括：1、 实验目的要求2、 实验操作要点3、 实验数据记录4、 实验分析和使用说明目 录实验一 预测分析技术实验二 模拟运算表实验三 线性规划求解实验一：预测分析技术一、实验目的：1、学会运用Excel进行线性回归预测分析和相关系数分析。 2、学会运用Excel进行时间序列预测分析。二、实验要求：1、掌握线性回归分析方法和相关系数分析。2、掌握移动平均法和指数平滑法。三、实验步骤：（一）线性回归分析1、如图1，在Excel工作表中输入我国1987年至1997年的布匹人均产量和人均纱产量，试用线性回归分析的方法分析两组数据之间的关系。 图12、 选择“工具”菜单的“数据分析”子菜单，双击“回归”选项，弹出回归分析对话框。3、 按如下方式填写对话框：x值输入区域为$C$1:$C$12，y值输入区域为$B$1:$B$12，并选择“标志”和“线性拟合图”两个复选框，然后单击“确定”按钮。结果如图2 图24、结果说明 第一部分是回归统计的结果，包括多元相关系数、可决系数、调整后的相关系数、回归标准差及样本个数；第二部分是方差分析的结果，包括可解释的离差、残差、总离差和它们的自由度以及由此计算出的F统计量和相应的显著水平；第三部分是回归方程的截距和斜率的估计值及它们的估计标准误差、t统计量大小双边拖尾概率值，以及估计值的上下界。 回归方程为：（二）相关系数分析采用图3表中的数据，计算变量之间的相关系数 图31、 用鼠标点击表中待分析数据的任一单元格2、 选择“工具“菜单”的“数据分析”子菜单，双击“相关系数”选项，弹出相关系数对话框3、 按如下方式填写对话框：输入区域为$A$1:$C$12，并选择“标志位于第一行”复选框，在输出选项中选择输出方式，然后单击“确定”按钮。（三）移动平均法1、如图1，在Excel工作表中输入某客运站旅客运输量 图12、在“工具”中的“数据分析”点击“移动平均”，输入数据区域：C2:C13，若进行三项移动平均，在“间隔”中输入：3，在“输出区域”中输入指定的区域（如D2:D13），选择“图表输出”，点击“确定”即可。图23、从图2可以看出，客运量具有明显的线性增长趋势。因此需使用二次移动平无法，即在一次移动平均的基础上再进行移动平均。所得结果如图3所示。图33、利用截距 和斜率 计算公式可得：于是，可得客运量的直线趋势预测模型为： 预测2006年第四季度的客运量为： （四）指数平滑法在指数平滑法中，预测成功的关键是 的选择。 的大小规定了在新预测值中新数据和原预测值所占的比例。 值愈大，新数据所占的比重就愈大，原预测值所占比重就愈小，反之亦然。若把一次指数平滑法的预测公式改写为： 则从上式可以看出，新预测值是根据预测误差对原预测值进行修正得到的。 的大小表明了修正的幅度。 值愈大，修正的幅度愈大， 值愈小，修正的幅度愈小。因此， 值既代表了预测模型对时间序列数据变化的反应速度，又体现了预测模型修匀误差的能力。在实际应用中， 值是根据时间序列的变化特性来选取的。若时间序列的波动不大，比较平稳，则 应取小一些，如0.10.3；若时间序列具有迅速且明显的变动倾向，则 应取大一些，如0.60.9。实质上， 是一个经验数据，通过多个 值进行试算比较而定，哪个 值引起的预测误差小，就采用哪个。1、已知某厂19781998年的钢产量如下表所示，试预测1999年该厂的钢产量。 年份 钢产量 年份 钢产量 1978 676 1989 2031 1979 825 1990 2234 1980 774 1991 2566 1981 716 1992 2820 1982 940 1993 3006 1983 1159 1994 3093 1984 1384 1995 3277 1985 1524 1996 3514 1986 1668 1997 3770 1987 1688 1998 4107 1988 1958 2、选择工具菜单中的数据分析命令，此时弹出数据分析对话框。在分析工具列表框中，选择指数平滑工具。这时将出现指数平滑对话框，如图4所示。 图43、在输入框中指定输入参数。在输入区域指定数据所在的单元格区域B1:B22；因指定的输入区域包含标志行，所以选中标志复选框；在阻尼系数指定加权系数0.3。 在输出选项框中指定输出选项。本例选择输出区域，并指定输出到当前工作表以C2为左上角的单元格区域；选中图表输出复选框。单击确定按钮。 这时，Excel给出一次指数平滑值，如图5所示。 图5 4、从图5可以看出，钢产量具有明显的线性增长趋势。因此需使用二次指数平滑法，即在一次指数平滑的基础上再进行指数平滑。所得结果如图6所示。 图65、利用截距 和斜率 计算公式可得： 于是，可得钢产量的直线趋势预测模型为： 预测1999年的钢产量为： 实验二：模拟运算表一、实验目的：学会运用Excel进行模拟运算表和单变量求解。 二、实验要求：掌握单变量模拟运算表和双变量模拟运算表及单变量求解。三、实验步骤模拟运算表就是将工作表中的一个单元格区域的数据进行模拟计算，测试使用一个或两个变量对运算结果的影响。在Excel中，可以构造两种模拟运算表：单变量模拟运算表和多变量模拟运算表。（一） 单变量模拟运算表单变量模拟运算表就是基于一个输入变量，用它来测试对公式计算结果的影响。下面运用一个具体的例子来介绍单变量模拟运算表的建立方法。假设某人正考虑购买一套住房，要承担一笔250000元的贷款，分15年还清。现想查看每月的还贷金额，并想查看在不同的利率下，每月的应还贷金额。1、 设计模拟运算表结构，如图1 图12、 在单元格C4中输入公式“=PMT(B4/12,12*15,C1)”。（基于固定利率及等额分期付款方式，返回贷款的每期付款额）3、 选择包括公式和进行模型运算的单元格区域B4 :C114、 单击【数据】菜单，选择【模拟运算表】项，弹出【模拟运算表】对话框，如图2 图25、 由于本例中引用的是列数据，故在【输入引用列的单元格】中输入“$B$4”。单击【确定】按钮，即得到单变量的模拟运算表，如图3 图3（二） 双变量模拟运算表双变量模拟运算表就是考虑两个变量的变化对公式计算结果的影响。假设某人想贷款45万购买一部车，要查看在不同的利率和不同的偿还年限下，每月的应还贷金额。1、 在一个工作表中，按图4输入数据 图42、 在行、列交叉处输入模拟运算表要用到的计算公式。本例中，在利率列与偿还年限的交叉处，B3单元格中输入公式“=PMT（A1/12，A2*12，C1）”3、 选中模拟运算表的单元格区域。本例中，选中B3:G11单元格4、 单击【数据】菜单，选择【模拟运算表】项，弹出【模拟运算表】对话框，如图5 图55、 填写公式中引用选中区域的首行、首列的单元格。本例中，首行单元格为$A$2，首列单元格为$A$1。结果如下：（三） 单变量求解单变量求解就是求解具有一个变量的方程，它通过调整可变单元格中的数值，使之按照给定的公式来满足目标单元格中的目标值。假设某公司想向银行贷款900万，贷款利率为8.7%，贷款年限为8年，如果公司每年可偿还120万元，该公司最多可贷款多少金额？1、 如图6，建立如下的工作表（注：B3单元格输入公式=PMT（B2，B4，B5）图62、 选择“工具”菜单中的“单变量求解”，弹出“单变量求解”对话框，在对话框中输入相关信息结果如下：实验三：线性规划求解线性规划求解是一个非常有用的工具，适用于解决运筹学、线性规划中的问题。现举一个例子说明在Excel中规划求解的建立过程。1、 建立规划求解模型某肥料厂专门收集有机物垃圾，如青草、树枝、凋谢的花朵等。该厂利用这些废物，并掺进不同比例的泥土和矿物质来生产高质量的植物肥料，生产的肥料分为底层肥料、中层肥料、上层肥料、劣质肥料4种。为使问题简单，假设收集废物的劳动力是自愿的，除了收集成本之外，材料成本是低廉的。生产各种肥料耗用的原料以及各种肥料的单价如表1所示。表示的意思是生产一个单位的肥料需要各种原材料各多少单位。例如，从表1的第二行可以看出，生产一个单位的底层肥料需要55个单位的泥土、54单位的有机垃圾、76个单位的矿物质、23个单位的修剪物，一个单位的底层肥料的单价是105.00元。表1各肥料成品用料及其价格表产品 泥土有机垃圾矿物质修剪物单价底层肥料 55547623105.00中层肥料6432452084.00上层肥料43329844105.00劣质肥料1845231857.00表2生产肥料的库存原材料 表3单位原材料成本单价库存情况现有库存泥土4100有机垃圾3200矿物质3500修剪物1600项目单位成本泥土0.20有机垃圾0.15矿物质0.10修剪物0.23 问题，求出在现有的情况下，即利用原材料的现有库存，应生产各种类型的肥料各多少数量才能获得最大利润，最大利润是多少？这是一个典型的规划求解问题，它所求的是在现有的原材料情况下，应该怎样合理搭配，才能获取生产产品的最大利润。这个问题所涉及到的可变因素、约束条件都较多，是一个较复杂的规划求解问题，但在Excel中却可以轻松求解。为了解决该问题，可在一个Excel工作表中输入该问题的原型，所建立的工作表如图1所示。图1规划求解工作表原型对该工作表的说明如下：1) 表一表一所示的是生产各种肥料所需要的原料单位及单价。此表的生产数量是未知的，所以全部填0。总价值=生产数量单价，所以H列中输入的是计算各种肥料的总价值。例如，F3表示底层肥料生产数量，G3是底层肥料的单价，所以H3种公式“=F3*G3”，表示所有底层肥料的总价。F3=0仅仅是一个假设，在规划求解后，它就不一定为0了，所以H列的公式是有意义的。2) 表二表二所示的是原材料的现有库存及生产肥料所用的材料。可用库存是生产各种肥料所用的原材料的总和。例如，B11单元格中的内容表示生产各种肥料所用的泥土总和，其中的公式为“=SUM(B3:B6*$F$3:$F$6)”。这是一个数组公式，也可以表示为“=B3* $F$3+ B4* $F$4+ B5*$F$5+ B6*$F$6”。结合表一，可以理解该公式的含义是所有的底层肥料、上层肥料、中层肥料及劣质肥料所用泥土总和。可据此理解C11、D11、E11、单元格中数组公式的意义。3) 表三表三所示的是各种原材料的单项成本，以及生产各种肥料所用的相关原材料的总成本。第15行表示各种原材料的单位成本，第16行表示生产所用各种原材料的总成本。例如，B16单元格中的公式是“=B15*B11”，B15是泥土的单价，B11是生产各种肥料所消耗的泥土总量，所以B15*B11就是生产肥料时泥土的总成本。同理可推，C16是耗用有机垃圾的总成本，D16是矿物质耗用的总成本。表四所示的总收入、总成本及总利润。其中，B19是总收入，B20是总成本，B21=总收入-总成本，所以它是总利润。2、 规划求解建立如图1所示的工作表，按图所示输入各单元格的公式之后，就可以进行规划求解了。其操作步骤如下。1) 单击如图1所示的规划模型中的任一单元格，然后选择“工具”菜单中的“规划求解”菜单项，系统将弹出“规划求解参数”对话框，如图2所示图2“规划求解参数”对话框2) 在“规划求解参数”对话框中的“设置目标单元格”编辑框中，输入目标函数所在的单元格。本例中的目标函数是求最大利润，它的单元格是B21，参见图13) 设置目标：最大值、最小值或具体的数值。本例中求的是最大利润，所以选择“最大值”。4) 设置可变单元格。可变单元格的设置是非常重要的，设置错误可能导致规划求解失败。在本例中，只有各种肥料的生产数量是不确定的，它一旦确定，则总利润、总成本、总收入等都可计算出来。所以可变单元格区域应是存放各种肥料生产数量所在的单元格区域。在本例中，它应是$F$3:$F$6单元格区域。5) 在“规划求解参数”对话框中添加约束条件。约束条件至关重要，少一个、多一个或填错了都会导致规划求解的失败。本例中的约束条件有以下3个。 各种肥料的生产数量不能小于0，即“$F$3:$F$6=0” 在生产过程中，各种原材料的总成本不能超过其相应的库存，即“$B$11:$E$11=$B$10:$E$10”。 各种原材料的使用量不能小于0，即“$B$11:$E$11=0”。添加约束条件的方法：单击“规划求解参数”对话框中的“添加”按钮，系统会弹出如图3所示的“添加约束”编辑框。图3“添加约束”编辑框在“添加约束”编辑框中的“单元格引用位置”中输入约束条件所在单元格的引用位置，然后从该对话框中间的条件下拉列表中选择一个比较运算符，在“约束值”中输入条件值。如果需要多个约束条件，则在输完一个约束条件之后，单击图3中的“添加”按钮。当所有的约束条件都输入完毕之后，单击图3中的“确定”按钮返回“规划求解”对话框。6) 建立好规划求解参数之后，单击“求解”按钮，系统将显示如图4所示的“规划求解参数”对话框。图4设置求解结果的保存方式7) 选择“规划求解结果”对话框中的“保存规划求解结果”选项，系统将把求解的结果保存在原规划求解模型中。本例的求解结果如图5所示。从图5可以看出，Excel将规划求解之后的结果保存在原工作表模型之中。事实上，Excel只会在目标函数单元格与可变单元格之间填写求解结果。本例中，表1一的生产数量和表四的盈余额是求解结果，在求解之后，Excel就会将计算结果保存在相关单元格中。其余单元格，如表意的总价值、表二的可用库存、表三的单项成本等数据都是根据求解的生产数量，由Excel通过生产公式计算出来的。3、 修改资源在产品生产一类规划求解问题中，原材料是比较重要的，怎样合理利用原材料是一个规划的问题。下面的例子说明怎样修改资源，合理规划。肥料厂接到一个电话：只要公司肯花10元的运费就能得到150个单位的矿物质。图5 规划求解结果这笔交易稍稍降低了矿物质的平均价格，但这些矿物质值10元吗?解决该问题的方法是，将库存矿物3500改为3650，用规划求解重新计算最大盈余。看除去10元的成本后，盈余是否增加。很显然这个问题与前面的规划求解没有本质的区别，只需将原来的库存由3500改为3650，再用规划求解重新计算最大盈余。然后减去10元的成本后，看盈余是否增加。具体做法是，将图1的D10单元格中的数据改为3650，然后进行规划求解。在这次求解过程中，“规划求解参数”对话框中的目标单元格、可变单元格、约束条件等与上次完全相同，不需作任何修改。参见图2。4、 修改约束条件约束条件是规划求解是否可解得关键所在，同样的规划模型，如果约束条件发生变化，求解的结果是不一样的。请看下面的例子。肥料厂接到一个电话，一个老顾客急需25个单位的上层肥料，公司经理在检查打印结果后，发现没有安排生产上层肥料。决定增加约束条件，为他生产上层肥料。修改上例中的约束条件的操作如下。1) 按前面的方法进入“规划求解参数”对话框，单击“添加”按钮。2) 输入新增的约束条件，$f$5=25.增加该约束条件之后，该对话框的目标函数、约束条件等如图6所示。3) 求解。结果发现盈余额为3246.51元，原来的盈余额为4483.41元，减少的利润为4483.41-3246.51=1236.9元。也就是说，如果为该顾客单独生产25个单位的上层肥料，肥料厂将损失利润1236.9元，很显然是不值得的。图6 添加新的约束条件5、 规划求解的结果报告规划求解不但可以在源工作表中保存求解结果，而且可以将求解的结果制成报告。对同一个规划模型，如果要分析它在不同的约束条件下，或约束相同但数据不同的情况下，目标函数的差别，最