数据模型与决策作业

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.4100345029.71416

2、.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.352418028.

3、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.203479.57

4、8337029.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.173515035.2

5、1410.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.3059193.

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

7、ower 95%Upper 95%下限 95.0%上限 95.0%Intercept-353.8248.33297-7.320471.24E-09-450.722-256.918-450.722-256.918SIZE0.0.8.61031.03E-110.0.0.0.P150.0.10.947862.52E-150.0.0.0.INC8.1.5.2.41E-075.11.75565.11.7556NREST1.0.6.1.21E-080.1.0.1.PRICE-2.685310.-3.372130.-4.28185-1.08878-4.28185-1.08878回归方程如下：Y(earn)=

8、0.77size+0.04p15+8.78inc+1.41nrest-2.69price-353.82多重共线性检验如下：回到EXCEL表格-工具-数据分析-相关系数-确定,选定区域为从EARN到PRICE的所有列,点击标志在第一行,确定,生成相关性系数.EARNSIZEP15INCNRESTPRICEEARN1SIZE0.1P150.62823-0.052861INC0.0.0.1NREST0.-0.096390.-0.058251PRICE-0.180020.-0.025470.-0.0634512、将Y变量（earn）和X变量（从size到price）粘贴到MINITAB中，统计-回归-

9、逐步回归-响应（earn），预测变量（从size到price）-确定，得出数据如下：逐步回归: EARN 与 SIZE, P15, 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.0

10、00INC 11.7 12.8 9.7 9.7T 值 4.93 6.37 5.85 6.19P 值 0.000 0.000 0.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.5

11、6Mallows Cp 140.4 78.9 42.6 10.5 6.0五个变量的回归方程如下：Earn=0.0432p15+9.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.

12、71024.3057247.7782119362033.46313.3058343.01558285416027.64018.3059193.1936193195028.73412.5060277.568892489036.03114.10各组实际利润率=earn/k,因而51到60的每组实际利润率如下：STOREARNKSIZEP15INCNRESTPRICE实际利润率51216.3776146280032.12611.600.5265.764862202032.77018.000.5367.669096232030.0713.600.54127.971586248034.41716.500

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

14、EARNKSIZEP15INCNRESTPRICE预测利润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.169591

15、93.1936193195028.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%

16、17.89%5582.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组数据的相关系数和回归方程如下所示：

17、逐步回归: EARN 与 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.7

18、3 8.61P 值 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）

19、=0.0442p15+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%Marseill

20、es-1925197165023411299.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)SupplierSizeExtrafineFi

21、neMediumCoarseAmbrosiBrescianiCastriDe 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

22、 14.22 11.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

23、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 将各共厂两项成本相加可得：工厂ExtrafineFineMediumCoarseAmbrosi 0.30 13.30 11.10 10.05 Bresciani 17.80 14.50 11.80 10.05 Castri 18.20 15.02 12.20 10.70 De Blasi 0.70 15.00 12.30 10.65 Estensi 18.20 14.50 12.45 10.65 Fila

24、toi R. 18.25 13.90 11.40 8.90 Giuliani 20.25 14.40 11.50 10.15 目标函数为各工厂各品种生产的成本与各工厂生产产品的乘积。第二步，根据约束条件建立不等式；各工厂各种类产量与生产该产品的时间之积小于等于月最大开机时间；各工厂同种产品的产量大于等于各品种的需求量；Ambrosi和De Blasi共厂高级产品的产量是零。ActualsignRHS =SUMPRODUCT(B8:E8,H8:K8) 2,500 =SUMPRODUCT(B9:E9,H9:K9) 3,000 =SUMPRODUCT(B10:E10,H10:K10) 2,500 =

25、SUMPRODUCT(B11:E11,H11:K11) 2,600 =SUMPRODUCT(B12:E12,H12:K12) 2,500 =SUMPRODUCT(B13:E13,H13:K13) 38,000 =SUMPRODUCT(B14:E14,H14:K14) 2,500 =SUM(B8:B14) 25,000 =SUM(C8:C14) 26,000 =SUM(D8:D14) 28,000 =SUM(E8:E14) 28,000 =B8 0 =B11 0如图所示。打开工具-premium solver-set sell选目标函数的空格，min,变量区域选空白处-建立三个约束条件（非负）-

26、确定最终结果：DECISION VARIABLESProduct bought from each supplier (Kg/month)SizeSupplierExtrafineFineMediumCoarseAmbrosi - 6,250 - - Bresciani 4,286 - - - Castri 3,704 - - - De Blasi - - 2,040 - Estensi 3,846 - - - Filatoi R. 13,164 19,750 18,817 28,000 Giuliani - - 7,143 - 三、 温馨小扁豆饭店问题打开EXCEL表格,随便一单元格键入每月

27、销售数量(如F6),把鼠标选在F7地方,打开define-define assumption-选择正态分布（normal），mean输入300，标准差输入1000，点击OK；在G6键入劳动力成本，把鼠标选在G7地方，打开define-define assumption-选择离散分布（discrete），最小值输入5040，最大值输入6860，确定；在H6键入每餐收入，把鼠标选在H7地方，打开define-define assumption-选择自定义（custom）-把每餐饭的固定价格及概率输入-确定，以上三步得到图像如下：每月销售数量劳动力成本每餐收入000在F10输入“单干”，F11输入“

28、合伙”，分别键入单干和合伙公式，在分别定义define foreast，得出图形如下所示：每月销售数量劳动力成本每餐收入000单干=F7*H7-G7-3995-11*F7合伙=IF(C43500,3500,IF(C49000,C4,9000+0.1*(C4-9000)点击run下的start simulation，生成数据和图形，四、 CCI数据处理把CCI里三列数据连接起来,粘贴在minitab中，统计-基本统计量-显示描述性统计，把date和revenues全部加入变量中，确定，出现以下结果：描述性统计: Date, Revenues 均值标 下四分 上四分变量 N N* 均值 准误 标准

29、差 最小值 位数 中位数 位数Date 150 0 33345 3.55 43.4 33270 33307 33345 33382Revenues 150 0 49274 688 8427 30000 42745 49921 54991变量 最大值Date 33419Revenues 71281把CCI里三列数据连接起来，点击run-CB predictor-范围就是两列数据，然后一直选next，最后把预测的值放到随便一个空格处，把报告、图表等四项内容全选上，点击run，出来的相关数据如下：Report for =Created: 2012/6/27 at 22:30:13Summary: N

30、umber of series: 1Periods to forecast: 4Seasonality: noneError Measure: RMSESeries: RevenuesRange: B2:B151Method: Single Moving AverageParameters: Periods: 41Error: 8307.3Series Statistics:Mean: $49,273.88 Std. Dev.: $8,427.20 Minimum: $30,000.00 Maximum: $71,280.90 Ljung-Box: 62.4817Forecast: DateL

31、ower: 5%ForecastUpper: 95%1-Jul$36,258.26 $49,923.77 $63,589.28 2-Jul$36,142.73 $49,923.77 $63,704.81 3-Jul$36,168.85 $49,923.77 $63,678.68 4-Jul$36,243.51 $49,923.77 $63,604.03 Charts for =Created: 2012/6/27 at 22:30:14Results Table for =Created: 2012/6/27 at 22:30:14SeriesRevenues数据Date Historical

32、 Data Lower: 5% Fit & Forecast Upper: 95% Residuals1-Feb$49,887.78 $49,887.78 2-Feb$61,440.47 $61,440.47 3-Feb$40,644.97 $40,644.97 4-Feb$42,811.79 $42,811.79 5-Feb$48,145.60 $48,145.60 6-Feb$39,025.98 $39,025.98 7-Feb$56,855.29 $56,855.29 8-Feb$52,069.28 $52,069.28 9-Feb$41,353.28 $41,353.28 10-Feb

33、$61,110.75 $61,110.75 11-Feb$52,067.93 $52,067.93 12-Feb$46,255.42 $46,255.42 13-Feb$47,437.37 $47,437.37 14-Feb$35,913.85 $35,913.85 15-Feb$51,006.90 $51,006.90 16-Feb$51,916.32 $51,916.32 17-Feb$48,316.69 $48,316.69 18-Feb$58,179.03 $58,179.03 19-Feb$45,195.08 $45,195.08 20-Feb$45,274.06 $45,274.0

34、6 21-Feb$30,000.00 $30,000.00 22-Feb$40,440.08 $40,440.08 23-Feb$51,244.84 $51,244.84 24-Feb$46,848.92 $46,848.92 25-Feb$45,216.40 $45,216.40 26-Feb$38,582.15 $38,582.15 27-Feb$47,553.81 $47,553.81 28-Feb$59,089.63 $59,089.63 1-Mar$40,823.95 $40,823.95 2-Mar$37,171.39 $37,171.39 3-Mar$59,416.87 $59,

35、416.87 4-Mar$30,000.00 $30,000.00 5-Mar$50,762.22 $50,762.22 6-Mar$40,157.58 $40,157.58 7-Mar$49,934.94 $49,934.94 8-Mar$54,549.14 $54,549.14 9-Mar$58,655.00 $58,655.00 10-Mar$48,683.40 $48,683.40 11-Mar$56,727.74 $56,727.74 12-Mar$61,504.60 $61,504.60 13-Mar$71,280.90 $71,280.90 14-Mar$51,839.67 $4

36、8,623.20 $3,216.47 15-Mar$51,903.67 $48,670.81 $3,232.86 16-Mar$58,660.49 $48,438.21 $10,222.28 17-Mar$55,272.50 $48,877.61 $6,394.89 18-Mar$42,544.51 $49,181.53 ($6,637.02)19-Mar$45,033.38 $49,044.92 ($4,011.54)20-Mar$50,753.84 $49,191.44 $1,562.40 21-Mar$55,549.87 $49,042.62 $6,507.25 22-Mar$45,282.94 $49,127.52 ($3,844.58)23-Mar$45,716.91 $49,223.36 ($3,506.45)24-Mar$47,283.17 $48,847.90 ($1,564.73)25-Mar$47,807.66 $48,