数据模型与决策作业.docx

1、数据模型与决策作业数据模型与决策作业一、Cropain公司基建部问题1、首先将60组数据单独列出，找到因变量Y（earn）和自变量X（size、p15、inc、nrest、price）,数据如下所示：STOREARNSIZEP15INCNRESTPRICE128.312998027.64516.102-1.591129028.32711.40368.9140294030.2521.704202.1184357027.6711.805115.8144170033.92516.606221.7160464032.5822.107292.994360033.18924.308134.41003450

2、29.71416.40937.485193028.44312.9010181.092352028.37613.0011246.9167397038.3922.8012178.3199319032.11110.1013214.983492036.0816.70140.6141121035.31131.0015252.3240415035.91614.0016124.282279033.4613.3017258.196418035.4912.3018193.078465029.75128.801954.899132028.81712.802045.475221029.81321.702166.35

3、2418028.62615.9022123.7177145034.91412.902357.8111167026.44111.702475.28472028.06416.7025115.695262028.25010.9026140.995399029.62123.202794.167349026.91816.0028250.8154395027.76414.3029-43.093257025.2533.4030145.667199036.56111.1031147.661479032.8317.7032175.1116446027.71912.9033117.992205033.6511.2

4、03479.578337029.54520.0035140.5103245034.91332.9036399.2191485034.59618.2037246.3263288038.82922.603877.626177031.01320.6039108.3169177031.81019.3040188.697433030.82910.5041143.5117331029.93619.5042175.5116155034.04412.504394.176405031.01911.7044214.2144392030.0267.604563.387323025.53218.7046237.173

5、515035.21410.5047208.859345034.47111.7048110.683407029.54425.0049165.4125280033.81211.3050-11.456215029.91214.1051216.3146280032.12611.605265.762202032.77018.005367.696232030.0713.6054127.986248034.41716.505582.988187028.81612.8056-2.972331028.71024.3057247.7119362033.46313.3058343.0285416027.64018.

6、3059193.1193195028.73412.5060277.592489036.03114.10打开EXCEL表格-工具-数据分析-回归-确定-Y值区域为EARN列，X值区域为SIZE到PRICE列，点标志-确定，生成数据如下：SUMMARY OUTPUT回归统计Multiple R0.92532R Square0.856217Adjusted R Square0.842903标准误差36.20023观测值60方差分析dfSSMSFSignificance F回归分析5421397.184279.4164.3131.64E-21残差5470764.671310.457总计59492161

7、.7Coefficients标准误差t StatP-valueLower 95%Upper 95%下限 95.0%上限 95.0%Intercept-353.8248.33297-7.320471.24E-09-450.722-256.918-450.722-256.918SIZE0.7718770.0896468.61031.03E-110.5921480.9516060.5921480.951606P150.0441750.00403510.947862.52E-150.0360860.0522650.0360860.052265INC8.7764911.4859285.9064022.4

8、1E-075.79738411.75565.79738411.7556NREST1.4126480.2104936.7111471.21E-080.9906351.8346610.9906351.834661PRICE-2.685310.796325-3.372130.001385-4.28185-1.08878-4.28185-1.08878回归方程如下：Y(earn)=0.77size+0.04p15+8.78inc+1.41nrest-2.69price-353.82多重共线性检验如下：回到EXCEL表格-工具-数据分析-相关系数-确定,选定区域为从EARN到PRICE的所有列,点击标志

9、在第一行,确定,生成相关性系数.EARNSIZEP15INCNRESTPRICEEARN1SIZE0.4366231P150.62823-0.052861INC0.4649430.1802740.1549231NREST0.337582-0.096390.067913-0.058251PRICE-0.180020.066436-0.025470.004017-0.0634512、将Y变量（earn）和X变量（从size到price）粘贴到MINITAB中，统计-回归-逐步回归-响应（earn），预测变量（从size到price）-确定，得出数据如下：逐步回归: EARN 与 SIZE, P15

10、, INC, NREST, PRICE 入选用 Alpha: 0.15 删除用 Alpha: 0.15响应为 5 个自变量上的 EARN，N = 50步骤 1 2 3 4 5常量 -0.6348 -354.7460 -421.2498 -412.5582 -379.4336P15 0.0459 0.0416 0.0397 0.0436 0.0432T 值 5.81 6.35 7.20 10.00 10.49P 值 0.000 0.000 0.000 0.000 0.000INC 11.7 12.8 9.7 9.7T 值 4.93 6.37 5.85 6.19P 值 0.000 0.000 0.

11、000 0.000NREST 1.33 1.48 1.46T 值 4.56 6.43 6.70P 值 0.000 0.000 0.000SIZE 0.61 0.63T 值 5.51 6.04P 值 0.000 0.000PRICE -2.01T 值 -2.55P 值 0.014S 68.0 55.8 46.8 36.5 34.5R-Sq 41.26 61.28 73.33 84.09 86.14R-Sq（调整） 40.04 59.63 71.59 82.67 84.56Mallows Cp 140.4 78.9 42.6 10.5 6.0五个变量的回归方程如下：Earn=0.0432p15+9

12、.7inc+1.46nrest+0.63size-2.01price-379.43;数据中51到60相关数据如下：STOREARNKSIZEP15INCNRESTPRICE51216.3776146280032.12611.605265.764862202032.77018.005367.669096232030.0713.6054127.971586248034.41716.505582.965088187028.81612.8056-2.978872331028.71024.3057247.7782119362033.46313.3058343.01558285416027.64018.3

13、059193.1936193195028.73412.5060277.568892489036.03114.10各组实际利润率=earn/k,因而51到60的每组实际利润率如下：STOREARNKSIZEP15INCNRESTPRICE实际利润率51216.3776146280032.12611.600.2787195265.764862202032.77018.000.1014185367.669096232030.0713.600.09804854127.971586248034.41716.500.1789365582.965088187028.81612.800.12758656-2.

14、978872331028.71024.30-0.0036557247.7782119362033.46313.300.31681958343.01558285416027.64018.300.22018759193.1936193195028.73412.500.20644160277.568892489036.03114.100.403396各组的预测利润=变量回归方程上各项*相关各项的数据举例：第51店的预测利润为Earn=0.0432*2800+9.7*32.1+1.46*26+0.63*146-2.0111.6-379.43=159.524;因而各组利润为：STOREARNKSIZEP

15、15INCNRESTPRICE预测利润51216.3776146280032.12611.60159.5245265.764862202032.77018.00130.1045367.669096232030.0713.6055.15854127.971586248034.41716.50107.2215582.965088187028.81612.8033.78656-2.978872331028.71024.3053.06957247.7782119362033.46313.30241.15158343.01558285416027.64018.30269.16959193.1936193

16、195028.73412.50129.30560277.568892489036.03114.10255.897各组预测利润率、与实际利润率比较为：STOREARNKSIZEP15INCNRESTPRICE预测利润预测利润率实际利润率51216.3776146280032.12611.60159.52420.56%27.87%5265.764862202032.77018.00130.10420.09%10.14%5367.669096232030.0713.6055.1588.00%9.80%54127.971586248034.41716.50107.22115.00%17.89%5582

17、.965088187028.81612.8033.7865.20%12.76%56-2.978872331028.71024.3053.0696.73%-0.37%57247.7782119362033.46313.30241.15130.84%31.68%58343.01558285416027.64018.30269.16917.28%22.02%59193.1936193195028.73412.50129.30513.82%20.64%60277.568892489036.03114.10255.89737.20%40.34%60组数据的相关系数和回归方程如下所示：逐步回归: EARN

18、 与 SIZE, P15, INC, NREST, PRICE 入选用 Alpha: 0.15 删除用 Alpha: 0.15响应为 5 个自变量上的 EARN，N = 60步骤 1 2 3 4 5常量 -3.083 -103.061 -145.274 -399.009 -353.820P15 0.0482 0.0501 0.0485 0.0444 0.0442T 值 6.15 7.95 9.10 10.10 10.95P 值 0.000 0.000 0.000 0.000 0.000SIZE 0.798 0.852 0.754 0.772T 值 5.73 7.23 7.73 8.61P 值

19、0.000 0.000 0.000 0.000NREST 1.39 1.45 1.41T 值 4.93 6.34 6.71P 值 0.000 0.000 0.000INC 8.8 8.8T 值 5.44 5.91P 值 0.000 0.000PRICE -2.69T 值 -3.37P 值 0.001S 71.7 57.6 48.5 39.5 36.2R-Sq 39.47 61.60 73.22 82.59 85.62R-Sq（调整） 38.42 60.26 71.79 81.33 84.29Mallows Cp 171.3 90.2 48.6 15.4 6.0Y（earn）=0.0442p15

20、+0.772size+1.41nrest+8.8inc-2.69price-353.820根据60组数据的预测回归方程对未来10组数据进行预测如下：STOREARNKSIZEP15INCNRESTPRICE预测利润预测利润率Calais6605460038182219.3152.93%Montchanin733120130031211367.7059.24%Aubusson1050135221029132263.2396.02%Toulouse8362453400376213364.99543.66%Torcy784962603038180.9440.12%Marseilles-1925197

21、165023411299.12410.72%Marseilles-2109093257025533-29.466-2.70%Clermont7381697803011967.3049.12%Montpellier5841492500292613124.00221.23%Dijon6811501650355415176.74425.95%二、菲拉托伊里尤尼蒂纺织厂问题首先,把要求的相关决策变量清空,如下所示：DECISION VARIABLESProduct bought from each supplier (Kg/month)SupplierSizeExtrafineFineMediumCo

22、arseAmbrosiBrescianiCastriDe BlasiEstensiFilatoi R.Giuliani根据题意，本题给出了各工厂生产机器的单位时间和月度最大使用时间，和各工厂生产四种产品的单位成本和运输成本，要求的是在满足各品种需求量的基础上各工厂如何生产总成本最低的问题，根据题意，首先建立目标函数：COST OF PRODUCTION($/Kg)SupplierSizeExtrafineFineMediumCoarseAmbrosi 13.00 10.65 9.60 Bresciani 17.40 14.10 11.20 9.45 Castri 17.40 14.22 11.

23、00 9.50 De Blasi 14.30 11.25 9.60 Estensi 17.50 13.80 11.40 9.60 Filatoi R. 18.25 13.90 11.40 8.90 Giuliani 19.75 13.90 10.75 9.40 COST OF TRANSPORTATION($/Kg)SupplierSizeExtrafineFineMediumCoarseAmbrosi 0.30 0.30 0.45 0.45 Bresciani 0.40 0.40 0.60 0.60 Castri 0.80 0.80 1.20 1.20 De Blasi 0.70 0.70 1.05 1.05 Estensi 0.70 0.70 1.05 1.05 Filatoi R. - - - - Giuliani 0.50 0.50 0.75 0.75