MM305 Unit 7 Project Name: Directions: The following software may be used: o Excel->Data Analysis o Excel QM o QM for Windows (PC only) Please make sure to insert your answers, including any charts, graphs, or output, on this document. You may be required to also upload original files from Excel or QM for Windows. The calculations are not your final answer. A summary/explanation in your own word for each question posed should be included. Question 6 requires the use of the States Data File. The States Data file is located in Doc Sharing->Instructor Graded Projects. Be sure to put your name on this document and save it to your computer. Upload this Word document as your Unit 7 submission in the course Drop Box. Submitting Your Project: Save your project in a location that you will remember and with your full name. When you are ready to submit your project, click on the Dropbox and complete the steps below: Click the link that says, \"Submit an Assignment.\" In the \"Submit to Basket\" menu, select Unit 7 Project. In the \"Comments\" field include your name. Click the \"Add Attachments\" button. Follow the steps listed to attach your Word Document. You should revisit the Dropbox to view any helpful feedback your instructor has and to retrieve your graded project. Make sure that you save a copy of your submitted project. 1 MM305 Unit 7 Project 1. Even though independent gasoline stations have been having a difficult time, Susan Solomon has been thinking about starting her own independent gas station. Susan's problem is to decide how large her station should be. The annual returns will depend on both the size of the station and a number of marketing factors related to oil industry and demand for gasoline. After careful analysis, Susan developed the following table: Size of Gasoline Station Small Medium Large Good Market ($) $5,000 $10,000 $25,000 Fair Market ($) $2500 $7000 $12,500 Poor Market ($) -$1000 -$5,000 -$20,000 You may use Excel QM or QM for Windows: 1. What is the Maximax decision? Answer: Explanation (include software output or calculation): 2. What is the Maximin decision? Answer: Explanation (include software output or calculation): 3. What is the criterion of realism decision? Use = 0.25. Answer: Explanation (include software output or calculation): 4. Develop an Opportunity Loss Table. Table: 5. What is the Minimax Regret Decision? Answer: Explanation (include software output or calculation): 2 MM305 Unit 7 Project 2. Data collected on the yearly demand for 50-pound bags of fertilizer at Sunshine Garden Supply are shown in the following table. Year 1 2 3 4 5 6 7 8 9 10 11 Demand for Fertilizer (1,000s of Bags) 3 4 6 8 6 7 8 7 9 6 5 You may use Excel QM to: 1. Develop a three-year moving average to forecast sales in year 12. Answer: Explanation (include software output or calculation): 2. Develop a 3-year weighted average to predict demand in year 12, in which sales in the most recent year is given a weight of 1, the second most recent year is weighted 2 and sales in the third most recent year has a weight of 3. Answer: Explanation (include software output or calculation): 3. Develop a regression/trend analysis to estimate the demand for fertilizer in year 12. Answer: Explanation (include software output or calculation): 4. Analysis of the forecasting errors indicates which forecasting method (moving average, weighted moving average or regression) is best in this situation? Answer: Explanation: 3 MM305 Unit 7 Project 3. Kaplan College has decided to "wire" its campus. The first stage in this effort is to install the "backbone," i.e., to connect all the buildings. The table below gives the distances between the various buildings on campus in hundreds of feet. You may use QM for Windows or complete it by hand: Distances in Hundreds of Feet From Building 1 Building 2 Building 3 Building 4 Building 5 Building 6 To Building 1 Building 2 15 Building 3 12 8 Building 4 10 2 16 Building 5 14 5 13 6 Building 6 9 6 14 7 8 1. How should the buildings be connected to minimize the total length of cable? Answer: Explanation (including any calculations or software output): 2. What length of cable is required? 4 MM305 Unit 7 Project 4. The following represents the distances in miles from a warehouse (node 1) to various cities in Montana. The major outlet store is located at node 7. From Node 1 1 2 2 3 3 3 4 4 5 5 6 To Node 2 4 3 4 4 5 6 5 7 6 7 7 Distance 75 375 200 250 450 25 175 50 350 145 275 450 You may use QM for Windows or complete it by hand: 1. Find the shortest route and distance from Node 1 to Node 7. Answer: Explanation (including any calculations or software output): 5 MM305 Unit 7 Project 5. The network of a city sewer system and their capacities are shown below. Remember that the arc has both capacity and reverse capacity. For example, row 1 is the flow from node 1 to node 2 and row 2 is the reverse flow from node 2 to node 1. There are seven branches in this network. From To Node Fluid Flow Node 1 2 250 2 1 200 1 3 150 3 1 225 1 4 325 4 1 150 1 5 300 5 1 125 2 4 375 4 2 225 3 4 250 4 3 100 3 5 175 5 3 400 You may use QM for Windows or complete it by hand: 1. Determine the maximum flow (in hundreds of gallons of water per minute) from node 1 to node 5. Answer: Explanation (including any calculations or software output): 6 MM305 Unit 7 Project 6. The governor of Michigan believes that the state can improve the state's crime rate if the state can reduce the percent of the population not covered by health insurance and reduce the percentage of teen pregnancy. (This requires you to use the States Dataset file that is included in Doc Sharing->Instructor Graded Projects.) 1. Using the States Data Set and Excel DataAnalysis (Regression), create the multiple regression prediction equation of the form = 0 + 1(X1) + 2(X2) Answer: Explanation (including any calculations or software output): 2. Predict the crime rate for Michigan if the percent not covered by insurance was 25% or 25 and the percent of teen pregnancy was 10% or 10? Answer: Explanation (including any calculations or software output): 7 MM305 Unit 7 Project 7. A concessionaire for the local ballpark has developed a table of conditional values for the various alternatives (stocking decisions) and states of nature (size of crowd). Stocking Decision Large Inventory Average Inventory Small Inventory Large Crowd ($) $15,000 $12,500 $8,000 Average Crowd ($) $10,000 $1,000 $3,000 Small Crowd ($) $5,000 $5,500 $1,000 You may use Excel QM or QM for Windows: If the probabilities associated with the states of nature are 0.3 for a large crowd, 0.3 for an average crowd, and 0.4 for a small crowd, determine: 1. The alternative that provides the greatest expected monetary value (EMV). Answer: Explanation (including any calculations or software output): 2. The expected value of perfect information (EVPI). Answer: Explanation (including any calculations or software output): 8 4.4 4.4 4.4 12.3 12.3 12.3 14.8 14.8 14.8 17.6 17.6 17.6 26.1 26.1 26.1 4.4 26.1 12.3 17.6 12.3 17.6 0.5 1 1.5 0.5 1 1.5 0.5 1 1.5 0.5 1 1.5 0.5 1 1.5 1 1 0.5 0.5 1.5 1.5 Abbr AL AK AZ AR CA CO CT DE FL GA HI ID IL IN IA KS KY LA ME MD MA MI MN MS MO MT NE NV NH NJ NM NY NC ND OH OK OR PA RI SC SD TN State Alabama Alaska Arizona Arkansas California Colorado Connecticut Delaware Florida Georgia Hawaii Idaho Illinois Indiana Iowa Kansas Kentucky Louisiana Maine Maryland Massachusetts Michigan Minnesota Mississippi Missouri Montana Nebraska Nevada New Hampshire New Jersey New Mexico New York North Carolina North Dakota Ohio Oklahoma Oregon Pennsylvania Rhode Island South Carolina South Dakota Tennessee Region Southeast West West South West West Northeast South South South West West Midwest Midwest Midwest Midwest South South Northeast South Northeast Midwest Midwest South Midwest West Midwest West Northeast Northeast West Northeast South West Midwest South West Northeast Northeast South Midwest South Poverty Crime College Bachelors 17.5 465.7 66.7 22.3 9 654.4 45.7 25.5 16.5 478.6 51.4 28 18.8 516.4 62.5 18.8 14.2 503.8 65.4 31.7 12.9 344.1 62.6 35.5 9.4 306.7 68 34.5 10.8 708.6 66.1 26.9 14.9 688.9 58.8 26 16.5 496.1 69.6 27.6 10.4 272.5 62.3 26.6 14.3 239.3 49.1 23.8 13.3 412.5 57.4 27.4 14.4 375.5 65.7 21.1 11.8 298.2 64.3 24.3 13.4 415.1 65.4 30 18.6 306.6 60.9 21 17.3 658.4 65.3 22.4 12.3 119.4 57.1 24.2 9.1 628.2 62.9 35.2 10.3 466.2 74.7 36.7 16.2 522.2 59.9 24.4 11 412.5 69.2 32.5 21.9 328.7 77.4 20.1 14.6 505.2 60 28.1 15.1 302 51.9 25.5 12.3 323.3 65.5 24.8 12.4 727.5 55.6 24.5 8.5 166 63.9 35.4 9.4 326.1 71.1 34.6 18 670.6 67.7 25.1 14.2 401.8 74.2 30.6 16.3 486.6 66 23.4 11.7 200.5 67.6 25.2 15.2 385.1 62.7 24.6 16.2 539.7 56 22.9 14.3 265 46.5 25.9 12.5 417 63.9 25.3 11.5 252.8 67.4 27.2 17.1 726.2 70.4 24.9 14.2 300.6 72.1 25.5 17.1 721.6 61.6 24.3 TX UT VT VA WA WV WI WY Texas Utah Vermont Virginia Washington West Virginia Wisconsin Wyoming South West Northeast South West South Midwest West 17.2 11.5 11.4 10.5 12.3 17.7 12.4 9.8 508.2 225.6 140.8 258.1 332.1 300.4 276.9 245.5 56.9 58.5 48.3 68.7 50.7 59.1 59.1 59.4 24.5 30.8 34.2 33.1 29.9 15.3 25.6 22.5 Poverty represents the percentage of the population living in poverty in 2009 (from census.gov). Region is the designated area. Crime represents the violent crime rate per 100,000 population in 2009 (from census.gov). Co the percentage of high school graduates going directly to college in 2008 (from nces.ed.gov). Bachelors shows of the state's population with a Bachelor's degree or higher. Metro shows the percentage of the population livin metropolitan area in 2010 (from statehealthfacts.org). Income represents the median income per household in and 2010 (from census.gov). Population is the 2012 population (worldatlas.com). Area in Square Miles (worlda Density is population per square mile (worldatlas.com). College Debt is the average college debt for graduate (http://projectonstudentdebt.org). Teen pregnancy rate rank 2008 (Guttmacher Institute). Not Covered is the population not covered by health insurance (2009, US Census Bureau). Monthly Unemployment Rates for 20 (www.ncsl.org). Income 2000 $ 35,424 $ 52,847 $ 39,783 $ 29,697 $ 46,816 $ 48,240 $ 50,172 $ 50,365 $ 38,856 $ 41,901 $ 51,546 $ 37,611 $ 46,064 $ 40,865 $ 40,991 $ 41,059 $ 36,265 $ 30,718 $ 37,266 $ 54,535 $ 46,753 $ 45,512 $ 54,251 $ 34,299 $ 45,097 $ 32,777 $ 41,750 $ 45,758 $ 50,926 $ 50,405 $ 35,093 $ 40,744 $ 38,317 $ 35,996 $ 42,962 $ 32,432 $ 42,499 $ 42,176 $ 42,197 $ 37,570 $ 36,475 $ 34,096 Income 2010 Metro Population Area Density College Debt $ 40,976 74 4822023 50744 91.87 $ 25,192.00 $ 58,198 71 731449 571951 1.20 $ 24,571.38 $ 47,279 85 6553255 113635 57.20 $ 19,950.00 $ 38,571 62 2949131 52068 54.84 $ 23,048.00 $ 54,459 98 38041430 155959 235.68 $ 18,879.00 $ 60,442 88 5187582 103718 47.62 $ 22,283.00 $ 66,452 96 3590347 4845 722.65 $ 28,783.00 $ 55,269 79 917092 1954 446.82 $ 24,571.38 $ 44,243 96 19317568 53927 339.87 $ 23,054.00 $ 44,108 85 9919945 57906 167.27 $ 22,443.00 $ 58,507 72 1392313 6423 200.56 $ 17,447.00 $ 47,014 61 1595728 82747 18.42 $ 24,134.00 $ 50,761 88 12875255 55584 232.11 $ 26,470.00 $ 46,322 73 6537334 35867 177.79 $ 27,500.00 $ 49,177 57 3074186 55869 53.74 $ 28,753.00 $ 46,229 65 2885905 81815 34.25 $ 23,321.00 $ 41,236 54 4380415 39728 107.46 $ 22,287.00 $ 39,443 85 4601893 43562 101.25 $ 22,455.00 $ 48,133 50 1329192 30862 42.66 $ 26,046.00 $ 64,025 97 5884563 9774 576.39 $ 24,002.00 $ 61,333 97 6646144 7840 828.82 $ 27,181.00 $ 46,441 86 9883360 56804 176.10 $ 27,451.00 $ 52,554 71 5379139 79610 65.57 $ 29,793.00 $ 37,985 45 2984926 46907 62.65 $ 23,537.00 $ 46,184 78 6021988 68886 85.82 $ 23,229.00 $ 41,467 36 1005141 145552 6.65 $ 24,113.00 $ 52,728 61 1855525 76872 23.20 $ 24,287.00 $ 51,525 91 2758931 109826 23.68 $ 19,954.00 $ 66,707 63 1320718 8968 146.72 $ 32,440.00 $ 63,540 100 8864590 7417 1170.64 $ 27,610.00 $ 45,098 72 2085538 121356 16.35 $ 24,571.38 $ 49,826 92 19570261 47214 412.81 $ 25,851.00 $ 43,753 68 9752073 48711 189.33 $ 20,800.00 $ 51,380 49 699628 68976 9.30 $ 27,425.00 $ 46,093 76 11544225 40948 280.50 $ 28,683.00 $ 43,400 69 3814820 68667 53.04 $ 20,897.00 $ 50,526 75 3899353 95997 39.48 $ 25,497.00 $ 48,460 82 12763536 44817 277.76 $ 29,959.00 $ 51,914 100 1050292 1045 1005.54 $ 29,097.00 $ 41,709 69 4723723 30109 148.79 $ 25,662.00 $ 45,669 49 833354 75885 10.60 $ 24,232.00 $ 38,686 75 6456243 41217 150.78 $ 20,703.00 $ $ $ $ $ $ $ $ 38,609 47,550 39,594 47,163 42,525 29,411 45,088 39,629 $ $ $ $ $ $ $ $ 47,464 56,787 55,942 60,363 56,253 42,839 50,522 52,359 89 78 32 87 92 57 75 30 (from census.gov). Region is the Census Bureaution in 2009 (from census.gov). College indicates m nces.ed.gov). Bachelors shows the percentage percentage of the population living in a median income per household in US$ for 2000 om). Area in Square Miles (worldatlas.com). average college debt for graduates her Institute). Not Covered is the percent of the hly Unemployment Rates for 2013 26059203 2855287 626011 8185867 6897012 1855413 5726398 576412 261797 82144 9250 39594 66544 24078 54310 97100 92.92 33.31 67.16 196.22 98.42 75.36 103.63 5.49 $ $ $ $ $ $ $ $ 22,140.00 17,227.00 28,273.00 24,717.00 22,244.00 26,227.00 26,238.00 23,341.00 Not Covered Teen Pregnancy Jan Unemploy Feb Unemploy Mar Unemploy 16.9 14 6.9 7.2 7.2 17.7 20 6.7 6.5 6.2 19.6 6 8 7.9 7.9 19.2 5 7.2 7.2 7.2 20.0 17 9.8 9.6 9.4 15.3 23 7.3 7.2 7.1 12.0 38 8.1 8 8 13.4 7 7.2 7.2 7.3 22.4 15 7.8 7.7 7.5 20.5 10 8.7 8.6 8.4 8.2 12 5.2 5.2 5.1 15.2 35 6.3 6.2 6.2 14.8 21 9 9.5 9.5 14.2 33 8.6 8.7 8.7 11.4 41 5 5 4.9 13.3 27 5.5 5.5 5.6 16.2 19 7.9 7.9 8 16.0 8 5.9 6 6.2 10.2 45 7.3 7.3 7.1 14.0 26 6.7 6.6 6.6 4.4 46 6.7 6.5 6.4 13.8 34 8.9 8.8 8.5 8.8 48 5.6 5.5 5.4 17.6 2 9.3 9.6 9.4 15.3 25 6.5 6.7 6.7 15.4 32 5.7 5.6 5.6 11.5 42 3.8 3.8 3.8 20.8 4 9.7 9.6 9.7 10.5 50 5.8 5.8 5.7 15.8 29 9.5 9.3 9 21.7 1 6.6 6.8 6.9 14.8 18 8.4 8.4 8.2 18.0 16 9.5 9.4 9.2 10.7 47 3.3 3.3 3.3 14.3 28 7 7 7.1 18.1 9 5.1 5 5 17.7 31 8.4 8.4 8.2 11.4 36 8.2 8.1 7.9 12.3 39 9.8 9.4 9.1 17.0 11 8.7 8.6 8.4 13.5 40 4.4 4.4 4.3 15.4 13 7.7 7.8 7.9 26.1 14.8 9.9 13.0 12.9 14.0 9.5 15.8 3 43 49 37 30 24 44 22 6.3 5.4 4.7 5.6 7.5 7.4 7 4.9 6.4 5.2 4.4 5.6 7.5 7.3 7.2 4.9 6.4 4.9 4.1 5.3 7.3 7 7.1 4.9 Apr Unemploy May Unemploy June Unemploy 6.9 6.8 6.5 6 5.9 6.1 7.9 7.8 8 7.1 7.3 7.3 9 8.6 8.5 6.9 6.9 7 8 8 8.1 7.2 7.2 7.3 7.2 7.1 7.1 8.2 8.3 8.6 4.9 4.7 4.6 6.1 6.2 6.4 9.3 9.1 9.2 8.5 8.3 8.4 4.7 4.6 4.6 5.5 5.7 5.8 7.9 8.1 8.4 6.5 6.8 7 6.9 6.8 6.8 6.5 6.7 7 6.4 6.6 7 8.4 8.4 8.7 5.3 5.3 5.2 9.1 9.1 9 6.6 6.8 6.9 5.5 5.4 5.4 3.7 3.8 4 9.6 9.5 9.6 5.5 5.3 5.2 8.7 8.6 8.7 6.7 6.7 6.8 7.8 7.6 7.5 8.9 8.8 8.8 3.3 3.2 3.1 7 7 7.2 4.9 5 5.2 8 7.8 7.9 7.6 7.5 7.5 8.8 8.9 8.9 8 8 8.1 4.1 4 3.9 8 8.3 8.5 6.4 4.7 4 5.2 7 6.6 7.1 4.8 6.5 4.6 4.1 5.3 6.8 6.2 7 4.6 6.5 4.7 4.4 5.5 6.8 6.1 6.8 4.6