60.1K

Verified Solution

Link Copied!

Please help to solve with EXCEL Solver by using method of Uncapacitated Facility Location

[10 marks] The government is planning to set up several healthcare centres in some of the counties for the provision of homecare services to citizens. The annual cost of opening a centre in each county is provided in the worksheet of Q3 in AssignmentlData.xlsx. It is given that the travel cost per mile per case is $1, where the demand in the file is the expected number of cases in each county in a year. How many facilities should be opened? A B D E F. G H J K L 1 Distance Matrix (miles) 2 County Lake, IL McHenry, IL Cook, IL DuPage, IL Kane, IL Will, IL Kendall, IL Kankakee, IL Grundy, IL Lake, IN Demand 3 Lake, IL 19 35 32 33 55 52 82 72 62 64435 4 McHenry, IL 19 0 44 34 26 56 45 84 68 73 26008 5 Cook, IL 35 44 0 16 30 25 36 49 48 29 537674 6 DuPage, IL 32 34 16 0 14 23 24 51 41 41 90416 7 Kane, IL 33 26 30 14 0 31 19 60 42 55 40412 8 Will, IL 55 56 25 23 31 0 22 29 23 33 50227 9 Kendall, IL 52 45 36 24 19 22 0 46 24 54 5454 10 Kankakee, IL 82 84 49 51 60 29 46 0 29 36 10383 11 Grundy, IL 72 68 48 41 42 23 24 29 0 53 3754 12 Lake, IN 62 73 29 41 55 33 54 36 53 48457 13 14 County Lake, IL McHenry, IL Cook, IL DuPage, IL Kane, IL Will, IL Kendall, IL Kankakee, IL Grundy, IL Lake, IN 15 Annual Cost of a centre (for Q4d) 6728160 4815270 1035950 7070760 4426350 7433040 2653000 4077890 7188790 2996610 [10 marks] The government is planning to set up several healthcare centres in some of the counties for the provision of homecare services to citizens. The annual cost of opening a centre in each county is provided in the worksheet of Q3 in AssignmentlData.xlsx. It is given that the travel cost per mile per case is $1, where the demand in the file is the expected number of cases in each county in a year. How many facilities should be opened? A B D E F. G H J K L 1 Distance Matrix (miles) 2 County Lake, IL McHenry, IL Cook, IL DuPage, IL Kane, IL Will, IL Kendall, IL Kankakee, IL Grundy, IL Lake, IN Demand 3 Lake, IL 19 35 32 33 55 52 82 72 62 64435 4 McHenry, IL 19 0 44 34 26 56 45 84 68 73 26008 5 Cook, IL 35 44 0 16 30 25 36 49 48 29 537674 6 DuPage, IL 32 34 16 0 14 23 24 51 41 41 90416 7 Kane, IL 33 26 30 14 0 31 19 60 42 55 40412 8 Will, IL 55 56 25 23 31 0 22 29 23 33 50227 9 Kendall, IL 52 45 36 24 19 22 0 46 24 54 5454 10 Kankakee, IL 82 84 49 51 60 29 46 0 29 36 10383 11 Grundy, IL 72 68 48 41 42 23 24 29 0 53 3754 12 Lake, IN 62 73 29 41 55 33 54 36 53 48457 13 14 County Lake, IL McHenry, IL Cook, IL DuPage, IL Kane, IL Will, IL Kendall, IL Kankakee, IL Grundy, IL Lake, IN 15 Annual Cost of a centre (for Q4d) 6728160 4815270 1035950 7070760 4426350 7433040 2653000 4077890 7188790 2996610

Answer & Explanation Solved by verified expert

See Answer