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

管理定量分析课程实验指导书实验一 Excel的使用操作 一、实验目的及要求（一）实验目的1了解Excel的基本功能和特点；2体验Excel强有力的数据分析能力及其在管理定量分析方面的作用；3掌握Excel的基本操作方法；4掌握电子表格模型建立的基本步骤。（二）实验要求1建立一个完整的电子表格模型；2能够用所建立的模型进行简单的定量分析；3重点掌握公式编辑和常用函数的功能和格式；4实验完成后，将文件复制，交任课教师。二、实验设备及软件1硬件设备：PC机；2操作系统：Microsoft Windows 2000/XP；3基本软件：Microsoft Excel 2000；4专用软件：无。三、实验内容1电子表格的基本操作；2电子表格的基本编辑和美化；3公式的输入和编辑；4常用函数的功能和格式；5图表和图形对象的操作。四、实验步骤1启动Excel，熟悉初始界面。注意浏览各菜单、子菜单和命令。2进入帮助系统，熟悉帮助系统的使用操作。3输入电子表格数据。重点是： 练习文本、数字、日期和时间等数据的输入方法； 练习使用序列填充功能； 练习使用填充柄。4设置单元格格式。参见例1-1、1-3。例1-1：单变量求解问题在工作表中建立了如图1-1所示的模型 图1-1建立模型假设单元格B1的内容为“”，则单元格B8怎样变化？或者说，要求利润为“”，那么在其它条件不发生变化的情况下，产品的销售价格应该是多少？在Excel中，可使用“单变量求解”命令进行该类问题的分析。步骤如下：（1）执行“工具”/“单变量求解”命令，屏幕显示如图1-2所示“单变量求解”对话框。 图1-2单变量求解对话框（2）在目标单元格输入“B1”，在目标值输入框输入“”，在可变单元格输入“B8”。（3）单击“确定”按钮，屏幕弹出“单变量求解状态”提示框，“确定”后，单元格B8显示变化后的值，如图1-3所示。由此可以看出，满足条件的单价应该是57.8。 图1-3单变量求解结果5输入和编辑公式。参见例1-1、1-2、1-3。重点是： Excel公式的一般格式； 运算符及其优先顺序；（3）数组公式和数组常量；（4）单元格引用的式样和引用的类型。例1-2：模拟运算表模拟运算表是工作表中的一个单元格区域，它可以显示公式中某些值的变化对运算结果的影响。模拟运算表在经济管理中有着重要的作用。如图1-4所示，对例1-1所介绍的模型进行单输入模拟运算。图1-4 单输入模拟运算假设产量变化，其它条件不变。模拟计算销售金额、营销利润的，操作步骤如下：（1）确定模拟运算表的单元格区域：如图所示，确定为B12：D18。从B13开始向下输入产量数值；B12为运算公式中的“输入引用列的单元格”地址。（2）在第一个输入数值上一行的右侧（即C12单元格），输入销售金额公式“=单价*B12”，其右再输入利润公式“=C12费用固定成本B12*单片成本”。（3）选定包含公式和替换值的矩形区域，如图1-5所示B12：D18，执行“数据”/“模拟运算表”命令，屏幕弹出如图1-6所示提示框。图1-6模拟运算表 图1-5选定模拟运算单元格区域 （4）在“输入引用列的单元格”中输入“引用单元格”地址“$B$12”，单击“确定”按钮。之后，按输入的产量序列进行计算，结果显示在每一个输入值的右侧，如图1-7所示。图1-7 单输入的模拟运算表例1-3：解一元二次方程6模型求解。通过改变模型输入参数，获得模型计算结果。参见例1-2、1-3。7创建和编辑图表。利用已有的电子表格数据，建立合适的图表，并进行美化操作。8保存实验结果。将工作簿存盘，并复制到自己的软盘或其他移动储存设备。五、实验结果1完整的电子表格模型；2运用模型进行分析所得到的结果；3反映实验结果的电子文档。六、实验思考题1用Excel建立管理定量分析模型有哪些优越性？2建立电子表格模型有哪几个基本步骤？七、附加练习 1对于线性方程组AX=B，设A矩阵在单元格区域A4到C6中，B矩阵在单元格区域E4到E6中，求X.2设单元格A3中存放着定期存款的开户日期，单元格D2中存放着存款期限（单位：年），求到期日期。3在单元格区域A1到C30中存放着解释变量X的样本数据，D1到D30中存放着被解释变量Y的样本数据，计算指数回归模型的回归参数及附加的回归统计量。4对于投入-产出模型AX+Y=X，设直接消耗系数矩阵A在单元格区域A4到C6中，计划最终需求矩阵Y在单元格区域E4到E6中，求X.5计算从今天到2050年1月1日之间的天数。6在单元格区域A1到C30中存放着解释变量X的样本数据，D1到D30中存放着被解释变量Y的样本数据，计算线性回归模型的回归参数及附加的回归统计量。 7设单元格C1中存放着用百分制表示的学生考试成绩。若规定85-100分为“优秀”，60-84分为“及格”，0-59分为“不及格”，将用百分制表示的成绩转换为用汉字表示的三级计分制成绩。8设单元格C1中存放着用大写英语字母表示的职员的学历，其中，“A”表示“研究生”，“B”表示“本科生”，“C”表示“专科生”。将用英语字母表示的学历转换为用汉字表示的学历。9写出下列Excel公式的结果（建议先自己写出结果，然后上机计算。如有不同，仔细进行分析）。 =123&456结果为:=”ABC”>”SDF”结果为:=SQRT(“8”+”1”)结果为:=”99/6/1”“99/5/1”结果为:=YEAR(TODAY()结果为:=”NOT”&TRUE结果为:=”3”+”2”结果为:=1+”$4.00”结果为:=SQRT(8&1)结果为:=MONTH(TODAY()结果为: 10利用Excel数组运算功能，进行矩阵加、减法运算。 实验二 线性规划模型建立及求解 一、实验目的及要求（一）实验目的1理解线性规划原理；2掌握线性规划模型建立和求解基本技术；3理解敏感性分析的重要性，并掌握相关原理。（二）实验要求1能够熟练地建立线性规划电子表格模型；2能够熟练地运用Excel的规划求解功能求解线性规划模型；3能够熟练地运用敏感性报告进行敏感性分析；4理解相关原理和技术在管理决策中的重要作用；5实验完成后，将文件复制，交任课教师。二、实验设备及软件1硬件设备：PC机；2操作系统：Microsoft Windows 2000/XP；3基本软件：Microsoft Excel 2000；三、实验内容1线性规划模型的建立；2线性规划模型的求解；3敏感性分析。四、实验步骤例2-1 学校准备为学生添加营养餐，每个学生每月至少需要补充60单位的碳水化合物，40单位的蛋白质和35单位的脂肪。已知A、B两种营养品的含量及单价见表4-6。表4-6 两种营养品营养成分含量AB碳水化合物5单位2单位蛋白质3单位2单位脂肪5单位1单位单价1.5元/斤0.7元/斤问买A和B分别多少斤既满足学生营养需要又省钱？ （1）决策变量。可设x为营养品A的投入量（斤），y为营养品B的投入量（斤），x，y即为本问题的决策变量。（2）目标函数。（3）约束条件。本问题共有四个约束。最后得出它的线性规划模型如下：s.t.下面用Excel来求解这个问题，步骤如下：1输入模型参数。参见图2-1。图2-1：线性规划模型2建立模型参数间的联系。注意使用SUMPRODUCT()函数。3运用“规划求解”定义并解答问题。注意：单击“规划求解”命令。注意如果菜单中没有这个命令请使用“工具”菜单的“加载宏”安装。在弹出的“规划求解参数”设置对话框中设置决策变量、目标函数和约束条件所在的地址以及选定求最小值。 在“工具”菜单中，单击“规划求解”命令。 在“目标单元格”编辑框中，键入单元格引用或目标单元格的名称。 如果要使目标单元格中数值最大，单击“最大值”选项。如果要使目标单元格中数值最小，单击“最小值”选项。 在“可变单元格”编辑框中，键入每个可变单元格的名称或引用。 在“约束”列表框中，输入相应的约束条件。 单击“求解”按钮。 如果要在工作表中保存求解后的数值，在“规划求解结果”对话框中，单击“保存规划求解结果”。4确定最佳决策。5进行敏感性分析。建议先运用所学相关知识进行分析，然后通过反复修改模型参数进行验证。 目标函数系数的敏感性分析； 右边值变化的敏感性分析。五、实验结果1线性规划模型；2模型的求解结果（3个报告的工作表）；3反映实验结果的电子文档。    图2-2：对话框示例 六、实验思考题下面是对一个有关农户生产决策线性规划模型求解所得的计算机输出结果的一部分： Microsoft Excel 8.0 敏感性报告           可变单元格        终递减目标式允许的允许的 单元格名字值成本系数增量减量 $B$3水稻2001953320.125 $C$3棉花2402855041.25 $D$3玉米0-36.220036.21E+30        约束         终阴影约束允许的允许的 单元格名字值价格限制值增量减量 $E$9水(立方米)54406001E+3056 $E$6土地(亩)440501E+306 $E$7资金(元)45001.22450040092.1053 $E$8劳动(人年)4120040.06830.4 请根据以上报告所提供的信息，回答下列问题，并说明理由（注意：在分析某一问题时，假定其它条件均保持不变）。1模型中的决策变量有哪几个？其最优解是怎样的？2若“水稻”的目标函数系数增大25%，这一变化是否会导致模型最优解发生变化？3若有人愿向该农户提供一笔500元的贷款，年利率为25%，农户是否可以接受此项贷款？4该农户决定临时将6亩土地无偿转让给他人耕种一年，这样做是否会影响该农户当年的利润？若转让7亩土地呢？5假定决策变量“玉米”的目标函数系数是根据下式计算出来的： 利润 = 产量 × 价格 成本 200(元)=200(千克)×1.5(元/千克)100(元)若有人想请该农户为其生产400千克玉米，农户可接受的最低价格是多少？ 实验三 数学规划模型及其应用 一、实验目的及要求（一）实验目的1理解运输问题原理及其应用；2理解分配问题原理及其应用；3理解目标规划原理及其应用；4了解各种数学规划模型的特点和适用条件。（二）实验要求1能够熟练地建立各种数学规划电子表格模型；2能够熟练地运用Excel求解数学规划模型；3能够根据模型求解结果确定最佳决策；4理解相关原理和技术在管理决策中的重要作用；5实验完成后，将文件复制，交任课教师。二、实验设备及软件1硬件设备：PC机；2操作系统：Microsoft Windows 2000/XP；3基本软件：Microsoft Excel 2000；三、实验内容1各种数学规划模型的建立；2用Excel求解数学规划模型的操作方法。四、实验步骤1运输问题。参见例3-1。 输入和定义模型； 指定整数决策变量。例3-1：运输问题 设有某种物资共有3个产地、，其产量分别为9、5、7个单位；另有4个销地、，其销量分别为3、8、4、6个单位。已知由产地（=1、2、3）运往销地（j=1,2,3,4）的单位运价为（见表4-11）。问如何调运才能使总运费最省？表3-1 例3-1的表式运输模型产地销地产量2910791342584257销量384621解：设为由产地运往销地 (i = 1，2，3 ； j = 1，2，3，4)的运量，则此问题可表示为如下数学模型：(1)(2)(3)(4)(5)(6)(7)(3-1) 本问题的决策变量是由产地到销地的运量，目标函数是总运输成本的最小化。总运输成本的计算公式如下：总运输成本= 本问题的Excel表格如图3-1所示，其模型的参数设置见图3-2所示。这里I9单元格中的公式是SUMPRODUCT(C6:F8,C13:F15)，等价于：C6*C13+C7*C14+C8*C15+D6*D13+D7*D14+D8*D15+E6*E13+E7*E14+E8*E15+F6*F13+F7*F14+F8*F15。图3-1 例3-1的Excel求解然后用Excel中的规划求解功能求出本问题的解。规划求解参数表如图3-2所示。求解结果见图3-3。图3-2 例3-1的规划求解参数设置图3-3 例3-1的Excel求解结果2分配问题。参见例3-2。 分配问题又称为指派问题，是运输问题的特殊类型，只是在分配问题中所有的供应量和需求量均等于1，同样可以用表上作业法求解，但是根据分配问题的特殊性质，还可以用更有效的方法匈牙利法。这一部分我们通过一个例子来分别介绍Excel线性规划解法。例3-2：分配问题。有一份说明书，要分别译成英、日、德、俄四种文字，交甲、乙、丙、丁四个人去完成，因各人专长不同他们完成翻译不同文字所需的时间见表4-13。甲、乙、丙、丁完成翻译工作所需时间表3-2 不同翻译的翻译时间工作甲乙丙丁译成英文21097译成日文154148译成德文13141611译成俄文415139问应如何分派这四个人分别完成这四项任务使总的时间最少？解：本问题的决策变量用C10：F13中的单元格表示，它们是各个人在任务中的分配量。例如单元格E101表示将A任务分配给丙来完成，C120则表示未将C任务分配给甲去完成。这里要注意的是图3-4中的决策变量的值是最终状态的值，开始进行规划求解时没有必要指定，只要有个初始值即可。目标函数是总的加工时间最短。在单元格中输入目标函数，其计算公式为=SUMPRODUCT(C3:F6,C10:F13)图3-4 Excel求解例3-2的结果从表中可以见，最优值与用匈牙利法的结果完全一致，即分配A任务给丙完成，B任务给乙完成，C任务给丁完成，D任务给甲完成，所花费时间是28小时。 约束条件是每项任务至多有一个人去完成，每个人至多完成一项任务，还有非负条件约束，在参数设置对话框中单击“选项”按钮，选择“采用线性模型”和“假定非负”。单元格G10：G13和单元格C14：F14中的内容是SUM公式，分别对该行或列求和。如单元格G11的内容是=SUM(C11:F11)，最后求解本问题的最优解。3目标规划。参见例3-3。 权目标规划 优先目标规划例3-3：目标规划（广告决策）五、实验结果1数学规划模型；2模型的求解结果（计算机输出报告工作表）；3反映实验结果的电子文档。六、实验思考题1线性规划有哪些基本假设条件？2尽管线性规划的基本假设条件经常不具备，但人们常常倾向于使用线性规划模型，主要原因何在？ 实验四 风险型决策分析 一、实验目的及要求（一）实验目的1理解风险型决策原理和方法；2掌握风险型决策问题的操作技术。（二）实验要求1能够熟练地运用决策树进行决策分析；2能够熟练地运用模拟运算表进行敏感性分析；3能够用决策树计算完全信息价值；4能够运用效用理论进行决策分析；5实验完成后，将文件复制，交任课教师。二、实验设备及软件1硬件设备：PC机；2操作系统：Microsoft Windows 2000/XP；3基本软件：Microsoft Excel 2000；三、实验内容1用决策树计算完全信息价值；2用效用理论进行决策分析。四、实验步骤1构建决策树。输入相关参数后，根据软件计算结果，得到相应决策。例4-1：决策分析（Goferbroke 公司的决策损益表） 备择方案自然状态及先验概率有油干涸0.250.75钻探700-100卖地 90 90 注意： “模拟运算表”只能进行1个或2个变量模拟运算。 可以将模拟结果绘成图。2运用指数效用函数对同一问题进行决策分析。注意选取合适的风险容忍度指标（RT）。  3用后验概率进行决策分析。五、实验结果1决策树及决策分析结果；2敏感性分析结果；3反映实验结果的电子文档。六、实验思考题1在风险型决策过程中，为什么要进行敏感性分析？2计算完全信息价值的主要目的是什么？3风险容忍度（RT）的含义是什么？ 实验五 时间序列分析 一、实验目的及要求（一）实验目的1理解时间序列分析在预测中的地位和作用；2掌握时间序列分析的基本方法和技术；3理解时间序列分析的优点和局限性。（二）实验要求1能够熟练地建立时间序列预测模型；2理解移动平均、指数平滑、线性趋势、季节因子等预测模型之间的内在联系；3能够对预测误差进行判断和控制；4实验完成后，将文件复制，交任课教师。二、实验设备及软件1硬件设备：PC机；2操作系统：Microsoft Windows 2000/XP；3基本软件：Microsoft Excel 2000；4专用软件：无。三、实验内容1移动平均预测模型；2指数平滑预测模型；3考虑线性趋势的指数平滑预测模型；4考虑季节因子的指数平滑预测模型；5预测误差的测量和控制。四、实验步骤1用移动平均法进行时间序列预测分析。参见例5-1。例5-1：移动平均与指数平滑 方法一：利用AVERAGE()函数，通过输入公式和复制公式进行预测分析；方法二：使用“移动平均”分析工具。参见图5-1。图5-1：“移动平均”对话框 2用指数平滑法进行时间序列预测分析。参见例5-1。方法一：利用指数平滑模型，通过输入公式和复制公式进行预测分析；Ft+1=Dt+(1-)Ft 方法二：使用“指数平滑”分析工具。参见图5-2。图5-2：“指数平滑”对话框 3创建图表。数值预测完成后，要求创建图表，观察预测效果，特别是要注意观察预测值的系统滞后现象。4用考虑线性趋势的指数平滑模型进行时间序列预测分析。参见例5-2。例5-2：考虑线性趋势的指数平滑模型 ABCDE1  Alpha=0.20  2  Belta=0.20  3     4PeriodSalesBaselineTrendForcast5tDtStGtFt6January12141214.0070.00 7February12521277.6068.721284.008March13041337.8667.031346.329April13841400.7166.191404.8810May12791429.3258.681466.9011June15831507.0062.481487.9912July14701549.5858.501569.4713August17391634.2663.731608.0714September15731673.0058.731697.9915October18361752.5862.911731.7316November20411860.5971.931815.4917December19831942.6173.951932.5218    2016.56 C6=B6D6=(1983-1214)/11=70 C7=$D$1*B7+(1-$D$1)*(C6+D6)  D7=$D$2*(C7-C6)+(1-$D$2)*D6  E8=C7+D7   模型：St=Dt+(1)(St-1+Gt-1)Gt=(StSt-1)+(1)Gt-1Ft+1=St+Gt 确定S和G的初始值。为了使模型能够用于预测，必须确定S和G的初始值。一般情况下可取 S0=D0 G0=(DtD0) / t 计算St和Gt。 计算Ft+1。 数值预测完成后，要求创建图表，观察预测效果，特别是要注意观察预测值的系统滞后问题是否得到了有效解决。5用考虑线性趋势和季节因子的指数平滑模型进行时间序列预测分析。参见例5-3。例5-3：考虑线性趋势和季节因子的指数平滑模型 ABCDEF1  Alpha=0.20   2  Belta=0.20   3  Gama=0.20   4      5PeriodSalesBaselineF/CTrendSeasonalForcast6tDtStGtCtFt7Jan   1.3278 8Feb   1.3183 9Mar   1.2613 10Apr   1.0900 11May   0.9074 12Jun   0.8199 13Jul   0.7077 14Aug   0.6126 15Sep   0.7781 16Oct   0.8941 17Nov   1.0349 18Dec 155.00001.00001.2479 191219157.78601.35721.3399 202216160.08341.54521.3245209.80213218163.87161.99381.2751203.85224185166.63592.14791.0941180.80235154168.96952.18510.9082153.16246147172.78122.51040.8261140.33257124175.27772.50760.7076124.0526893172.59281.46910.5978108.90279127171.89491.03570.7702135.432810148171.45030.73960.8879154.622911161168.86680.07501.0186178.203012198166.8857-0.33621.2356210.833113236168.46720.04731.3521223.153214239170.90010.52451.3393223.203315221171.80450.60041.2773218.583416194173.38770.79701.0990188.623517161174.80180.92040.9108158.203618131172.29360.23470.8129145.163719110169.1124-0.44850.6962122.093820101168.7210-0.43710.5980100.833921131168.6437-0.36510.7715129.614022157169.9862-0.02360.8951149.424123189173.08040.60001.0333173.124224217174.06780.67751.2378214.614325243175.74130.87671.3582236.274426238176.83500.92011.3406236.554527224177.27740.82451.2746227.054628194177.78520.76121.0975195.744729162178.41090.73410.9102162.624830153180.95741.09660.8194145.634931138185.28731.74320.7059126.745032128192.43572.82430.6114111.845133151195.35112.84250.7718150.655234165195.42382.28850.8849177.405335194195.72081.89021.0249204.295436241197.02771.77361.2349244.61      270.01        E7 =AVERAGE(B19,B31,B43)/AVERAGE($B$19:$B$55) D18=(B43-B19)/24    C19=$D$1*(B19/E7)+(1-$D$1)*(C18+D18)  D19=$D$2*(C19-C18)+(1-$D$2)*D18  E19=$D$3*(B19/C19)+(1-$D$3)*E7   F20=(C19+D19)*E8   模型：St=(Dt/Ct-N)+(1)(St-1+Gt-1)Gt=(StSt-1)+(1)Gt-1Ct=(Dt/St)+(1)Ct-NFt+1=（St+Gt）Ct+1-N初始值： 线性趋势初始值的估计要用时期对应的数据。例如，在只有3个完整周期的数据中，可以用第1和第3个周期的第一个时期的数据。即G0=(D1+2ND0) / t其中：t=2N 基准值的初始值一般用第一个周期的平均数（用以排除季节影响）减去半个周期的趋势值（排除趋势影响）作为估计值。即S0=Di/ N G0 (N/2) 季节因子的初始值一般用可获得的完整周期数据计算。6预测误差分析。计算衡量预测误差的主要指标，并对模型的预测功效进行分析和判断。五、实验结果1时间序列预测模型及其预测结果；2预测误差的分析结论；3反映实验结果的电子文档。六、实验思考题1预测值存在系统滞后的主要原因是什么？如何克服？2移动平均、指数平滑、线性趋势、季节因子等时间序列预测模型之间的内在联系是怎样的？3衡量预测误差的主要指标有哪些？你认为哪个指标最重要？为什么？4时间序列预测分析最基本的理论假设是什么？ 实验六 相关与回归分析 一、实验目的及要求（一）实验目的1理解相关分析的本质及其重要性；2理解回归分析的基本原理；3熟练掌握Excel回归分析工具的使用操作技术。（二）实验要求1能够根据具体问题设计回归预测模型；2能够运用回归分析工具估计模型参数；3能够运用相关统计指标和辅助方法，初步判断模型的预测功能；4实验完成后，将文件复制，交任课教师。二、实验设备及软件1硬件设备：PC机；2操作系统：Microsoft Windows 2000/XP；3基本软件：Microsoft Excel 2000；4专用软件：无。三、实验内容1相关分析工具的使用操作；2回归分析工具的使用操作；3非线性模型的线性化技术；4预测模型的比较与取舍。四、实验步骤1相关分析。参见例6-1。例6-1：相关分析和回归分析x1x2y169290264181318298160254235187341318184327304178311289172295271175296273 方法一：利用CORREL ()函数；方法二：使用“相关系数”分析工具。参见图6-1。图6-1：“相关系数”对话框 2回归分析。具体实验步骤如下： 练习使用回归分析和预测函数。如LINEST()函数、FORECAST()函数、TREND()函数等。 练习使用矩阵运算函数进行回归分析。模型为：=(X'X)-1 X'Y相关函数有：MMULT（）、MINVERSE（）、TRANSPOSE（）。 “回归”分析工具。参见图6-2。图6-2：“回归”对话框 3根据回归分析结果，写出预测模型（回归函数），并利用有关统计量，对回归模型的预测功效进行判断和分析。然后，利用回归函数，进行预测。4非线性回归分析。具体步骤如下： 练习使用非线性回归分析函数。如LOGEST()函数、GROWTH（）函数。 将非线性模型的线性化，并利用“回归”分析工具进行回归分析，求得非线性模型的回归参数，并利用有关统计量，对回归模型的预测功效进行判断和分析。然后，利用回归函数，进行预测。五、实验结果1回归模型的设计及计算结果；2运用回归模型进行预测所的结果；3对预测结果准确性的分析结论；4反映实验结果的电子文档。六、实验思考题1与时间序列相比，回归分析方法在预测方面有哪些优缺点？2判断回归模型预测功效的主要统计指标有哪些？3时间序列分析方法和回归分析方法之间有何联系？七、附加练习以下是Excel线性回归分析输出报告的一部分。请写出回归模型，解释表中主要统计量的含义，并根据有关统计量判断模型的预测效果。SUMMARY OUTPUT   回归统计  Multiple R0.  R Square0.  Adjusted R Square0.  标准误差0.  观测值8       Coefficients标准误差t StatIntercept-0.0.-2.7112X10.0.2.2239X20.0.3.4083