pls i need help with this excel final exams work.pls no price increase.

MBA674 Final Exam Application Problems 1. Complete all 6 Problems. 2. Each Application Problem worths 25 points and total Application Problems worth 150 points. 3. Show your Excel work. 4. Due date is Sunday, October 9th, 11:59 PM. 25 s worth 150 points. AP1. The Excel file Restaurant Sales provides sample information on lunch, dinner, and delivery sales for a local Italian restaurant. Develop 95% prediction intervals (upper and lower limits) for the dollar sales of each of these variables for next Restaurant Sales Date Day Lunch Sales $ Dinner Sales Delivery Sales $ $ TOTAL $ 6-Apr 13-Apr 20-Apr 27-Apr 5-Apr 12-Apr 19-Apr 26-Apr 3-Apr Friday Friday Friday Friday Thursday Thursday Thursday Thursday Tuesday $ $ $ $ $ $ $ $ $ 442.00 508.00 467.00 379.00 403.00 412.00 345.00 426.00 289.00 $ $ $ $ $ $ $ $ $ 1,289.00 1,178.00 1,332.00 1,409.00 805.00 891.00 913.00 940.00 646.00 $ $ $ $ $ $ $ $ $ 1,242.00 1,068.00 1,254.00 1,143.00 276.00 243.00 294.00 350.00 321.00 $ $ $ $ $ $ $ $ $ 2,973.00 2,754.00 3,053.00 2,931.00 1,484.00 1,546.00 1,552.00 1,716.00 1,256.00 10-Apr 17-Apr 24-Apr 4-Apr 11-Apr 18-Apr 25-Apr 31-Mar 7-Apr 14-Apr 21-Apr 28-Apr 1-Apr 8-Apr 15-Apr 22-Apr 29-Apr Tuesday Tuesday Tuesday Wednesda Wednesda Wednesda Wednesda Saturday Saturday Saturday Saturday Saturday Sunday Sunday Sunday Sunday Sunday $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 307.00 298.00 233.00 354.00 387.00 419.00 341.00 542.00 449.00 503.00 622.00 592.00 873.00 697.00 741.00 846.00 779.00 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 579.00 572.00 523.00 782.00 664.00 621.00 731.00 1,468.00 1,652.00 1,581.00 1,732.00 1,856.00 1,345.00 1,209.00 1,137.00 1,324.00 1,229.00 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 328.00 365.00 411.00 289.00 301.00 278.00 243.00 884.00 782.00 714.00 636.00 601.00 489.00 341.00 326.00 319.00 398.00 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 1,214.00 1,235.00 1,167.00 1,425.00 1,352.00 1,318.00 1,315.00 2,894.00 2,883.00 2,798.00 2,990.00 3,049.00 2,707.00 2,247.00 2,204.00 2,489.00 2,406.00 for a local Italian h of these variables for next Saturday. AP2. We're interested in whether the average test scores for the sample of schools to the left is significantly different than state wide averages (at the bottom of the data set). Conduct hypothesis tests for "math" and "writing" (so two separate tests) a) Lay out the null and alternate hypotheses b) Conduct a hypothesis test (95% confidence) c) Explain the results and what conclusions we can draw Ohio Education Performance Results Year 2000 School District Writing Reading Math Citizenshi Science All Indian Hill Wyoming Mason City Madiera Mariemont Sycamore 95 98 96 94 99 85 98 96 92 95 92 88 89 86 85 88 74 80 95 93 94 82 89 87 91 87 86 88 88 84 83 81 72 69 68 68 Forest Hills Kings Local Lakota Loveland Southwest Fairf ield Oak Hills Three Rivers Milford Ross West Clermont Reading Princeton Finneytown Norwood Lockland Franklin City Winton Woods Northwest North College Hill Mount Healthy Felicity Franklin St. Bernard Deer Park Cincinnati Public 93 92 90 85 92 90 88 87 82 84 88 88 83 79 86 88 85 82 75 77 87 52 81 69 63 91 86 88 93 82 86 86 85 86 85 83 80 75 71 77 79 79 77 74 76 72 64 59 66 59 73 78 73 72 73 71 75 66 72 66 63 58 59 61 64 52 49 55 51 50 40 52 40 40 35 85 82 85 86 78 83 77 84 82 75 73 76 76 67 75 82 70 65 62 66 62 81 41 43 50 88 78 81 86 73 77 79 77 76 78 70 75 63 62 67 64 67 59 61 57 53 64 48 52 44 67 64 64 61 58 57 57 56 53 52 48 46 46 45 44 41 40 40 38 35 32 28 26 25 23 State Averages 79 79 60 72 68 46 s significantly different ath" and "writing" (so AP3. Accounting Department Survey Data 1) Perform a chi- square test of independence to determine if age group is independent of having a CPA degree. 2) Perform a chi- square test for independence to determine if plans to attend graduate school are independent of gender. Employee Gender Years of Service Years Undergraduate Study Graduate Degree? CPA? 1 2 3 4 5 6 7 8 9 10 11 12 F F M F M F M F M M F F 17 6 8 8 16 21 27 7 8 23 9 8 4 2 4 4 4 1 4 4 4 2 4 2 N N Y Y Y N N Y N N Y N Y N Y N Y Y N Y N Y Y N 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 F M F F M M M M F M F F M M F 8 26 9 9 19 5 19 20 14 31 10 10 26 28 5 4 4 4 2 2 4 4 4 4 4 0 4 4 4 4 Y N N N Y N Y N Y N N N Y N N N Y Y N Y N N N Y N N Y Y N Y g a CPA degree. re independent of gender. Age Group 41-45 26-30 31-35 31-35 36-40 51-55 51-55 26-30 31-35 41-45 31-35 26-30 26-30 46-50 26-30 26-30 36-40 36-40 51-55 46-50 36-40 51-55 51-55 31-35 46-50 51-55 21-25 AP4. a) Use regression analysis to determine what effect lot cost and type have on selling price. For the variable "type" use the method for categorical variables described in the practice problems. b) Interpret all of the relevant parts of the regression output. House Sales Region Selling Price Lot Cost Midwest Midwest Midwest Midwest Midwest Midwest Midwest Midwest $85,145 $112,740 $115,350 $116,415 $133,070 $136,530 $139,435 $139,955 $17,030 $20,700 $18,030 $19,600 $24,455 $25,500 $29,155 $30,400 Midwest Midwest Midwest Midwest South Midwest Midwest Midwest Midwest Midwest South Midwest Midwest Midwest South South Midwest Midwest Midwest Midwest Midwest South Midwest Midwest Midwest South South South South South $147,905 $153,845 $155,270 $155,870 $160,000 $165,220 $165,350 $168,354 $168,500 $170,000 $172,749 $175,000 $175,470 $179,365 $181,916 $182,237 $183,370 $184,210 $184,460 $184,873 $185,160 $186,000 $187,390 $188,603 $189,120 $191,028 $196,898 $198,202 $200,119 $200,423 $24,650 $27,500 $19,600 $24,650 $29,500 $25,500 $25,000 $27,316 $33,000 $25,200 $45,025 $28,000 $28,600 $32,200 $45,025 $45,025 $28,000 $28,000 $22,300 $33,400 $29,000 $35,851 $27,000 $31,300 $35,000 $45,000 $45,025 $45,025 $45,000 $45,025 Type Type 0 Townhouse 0 Townhouse 0 Townhouse 0 Townhouse 0 Townhouse 1 Single Family 0 Townhouse 0 Townhouse 0 Townhouse 1 Single Family 0 Townhouse 0 Townhouse 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 0 Townhouse 1 Single Family 0 Townhouse 0 Townhouse 0 Townhouse 0 Townhouse 1 Single Family 1 Single Family 1 Single Family 0 Townhouse 1 Single Family 1 Single Family 0 Townhouse 1 Single Family 1 Single Family 1 Single Family 0 Townhouse 0 Townhouse 1 Single Family 0 Townhouse Midwest South South Midwest South South South South South Midwest Midwest South Midwest South Midwest South South Midwest $201,700 $202,000 $203,076 $203,950 $204,900 $205,821 $206,400 $207,345 $209,400 $210,380 $211,513 $211,797 $212,079 $214,205 $214,900 $216,049 $219,990 $220,257 $40,940 $31,160 $45,025 $33,000 $34,000 $39,299 $35,851 $35,600 $43,579 $33,856 $31,300 $34,000 $33,400 $36,500 $31,300 $35,600 $37,557 $31,300 Midwest South South South South Midwest Midwest Midwest South South South South South South South Midwest South Midwest South South South South South South South South Midwest South South Midwest $223,890 $224,108 $225,900 $226,000 $230,000 $230,440 $230,555 $233,900 $234,971 $239,000 $239,870 $241,195 $242,191 $242,899 $246,648 $248,500 $249,900 $250,005 $250,800 $252,135 $253,055 $254,048 $255,000 $256,235 $257,040 $260,100 $262,740 $262,890 $265,058 $265,500 $28,000 $45,025 $28,618 $35,600 $45,025 $33,000 $28,000 $44,200 $29,202 $39,169 $41,354 $41,340 $33,434 $48,252 $41,600 $20,000 $38,200 $33,000 $73,400 $41,341 $41,340 $39,680 $43,198 $48,500 $37,631 $73,400 $44,900 $48,500 $41,404 $35,800 1 Single Family 1 Single Family 0 Townhouse 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 0 Townhouse 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 0 Townhouse 1 Single Family 1 Single Family 0 Townhouse 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 0 Townhouse 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 0 Townhouse 1 Single Family 1 Single Family 1 Single Family 0 Townhouse South South South South Midwest South South South Midwest Midwest South Midwest South South Midwest South Midwest South $267,060 $267,250 $267,640 $268,000 $268,210 $268,500 $269,410 $270,518 $271,105 $274,455 $274,903 $277,720 $280,622 $280,804 $281,487 $281,824 $283,440 $286,758 $73,400 $45,676 $73,400 $43,344 $29,700 $41,099 $73,400 $46,499 $45,000 $41,000 $45,345 $44,650 $45,130 $40,667 $57,285 $50,448 $50,900 $57,000 South Midwest South South South South South South South Midwest South South South South South South South South South South Midwest South South South South Midwest South South South South $287,771 $289,000 $290,000 $290,190 $294,450 $294,990 $299,096 $301,500 $305,000 $307,373 $307,387 $307,663 $309,075 $310,100 $310,372 $312,898 $318,274 $318,523 $319,602 $322,920 $323,716 $324,266 $324,412 $326,570 $329,611 $330,486 $330,710 $333,158 $335,000 $337,374 $46,300 $44,000 $48,300 $82,250 $73,400 $57,000 $43,784 $59,000 $49,067 $41,292 $45,850 $44,840 $82,250 $41,800 $60,000 $40,768 $85,800 $50,000 $82,250 $82,250 $34,500 $47,891 $62,523 $51,000 $48,611 $35,000 $49,920 $49,123 $68,375 $70,399 0 Townhouse 1 Single Family 0 Townhouse 1 Single Family 1 Single Family 1 Single Family 0 Townhouse 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 0 Townhouse 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 0 Townhouse 1 Single Family South South South South South Midwest South South South South South South South South South South South South $337,380 $338,065 $340,000 $340,730 $342,423 $348,744 $350,702 $352,781 $354,065 $354,117 $355,688 $358,162 $359,949 $366,990 $367,600 $369,101 $371,152 $379,000 $49,150 $54,850 $40,000 $50,850 $48,309 $53,000 $46,386 $60,000 $48,115 $56,219 $65,373 $44,470 $50,591 $55,508 $50,000 $46,773 $58,223 $72,915 South South South South South South South South South Midwest South South South South South South $379,424 $379,575 $385,000 $385,942 $387,527 $398,651 $400,330 $404,510 $407,076 $410,810 $417,790 $432,426 $444,304 $446,507 $492,820 $575,120 $64,902 $44,294 $75,000 $49,123 $48,000 $65,429 $75,510 $58,225 $48,668 $66,500 $63,099 $57,422 $53,938 $75,510 $84,122 $79,790 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family 1 Single Family AP 5. Golfing Statistics a. Run a regression with "Earnings/Event" as the dependent variable and all of the other variables as independent variables. b. Which of the variables are statistically significant? How do you know? c. Explain what the coefficient for "Driving accuracy" means. (Use the correct units) Earnings/Event Events Avg. Score GIR (%)* Driving Distance Driving Accuracy (%) $239,493.68 $177,249.18 $218,619.18 $186,380.08 $209,511.75 22 28 22 24 20 70.37 69.43 70.23 70.46 69.78 67.9 69.4 67.1 68.0 68.3 288.4 286.9 276.0 308.5 282.9 60.2 67.9 71.0 56.4 68.5 $181,987.29 $162,536.13 $174,534.95 $135,353.70 $212,540.82 $297,079.50 $168,904.45 $135,791.58 $133,695.52 $112,192.04 $215,121.67 $183,922.93 $150,251.76 $183,356.69 $130,274.35 $286,285.40 $72,708.05 $99,597.31 $85,557.56 $46,406.25 21 23 21 27 17 12 20 24 23 26 12 14 17 13 17 5 19 13 9 8 70.34 69.92 70.25 70.64 69.93 70.26 69.96 70.21 70.53 70.59 70.22 70.86 70.94 71.13 71.53 69.73 70.79 71.07 71.10 71.24 65.1 66.3 65.3 68.0 68.7 69.3 66.0 68.5 68.2 66.5 66.5 62.9 66.2 66.9 62.5 69.4 61.9 64.1 64.1 61.1 299.1 287.8 277.0 291.8 294.2 298.7 291.4 309.8 289.1 279.7 292.4 287.2 300.0 291.7 286.8 308.4 292.1 295.8 290.4 289.9 52.7 65.2 62.4 67.9 61.3 61.3 64.8 55.7 64.8 71.2 60.1 52.0 62.6 67.1 62.7 70.6 56.7 57.2 69.3 65.5 her variables as Putts/Round 31.82 31.30 31.81 31.81 31.43 31.72 31.68 31.52 32.35 31.55 32.31 31.79 31.73 31.86 31.30 32.29 31.99 32.31 32.06 32.47 32.09 31.50 31.52 31.95 32.31 AP6. The Morton Supply Company produces clothing, footwear, and accessories for dancing and gymnastics. They produce three models of pointe shoes used by ballerinas to balance on the tips of their toes. The shoes are produced from four materials: cardstock, satin, plain fabric, and leather. The number of square inches of each t of material used in each model of shoe, the amount of material available, and the profit/model are shown belo Material (measured in square inches) Cardstock Satin Plain Fabric Leather Profit per model Model 1 Model 2 Model 3 Material Available 12 24 45 11 10 20 40 11 $50 14 15 30 10 $44 1200 2000 7500 1000 $40 a. Develop and solve an optimization model to find the number of each model to produce to maximize the total b. What constraints are binding? Interpret the slack values for the nonbinding constraints. c. Clearly explain all the key information in the sensitivity report in language that the production manager would ancing and gymnastics. They of their toes. The shoes are mber of square inches of each type he profit/model are shown below: to produce to maximize the total profit. at the production manager would understand.