PLEASE SHOW THIS IN EXCEL AND SHOW FORMULAS ETC. PLEASE DO NOT WORRY ABOUT A RATING I WILL GIVE YOU A THUMPS UP JUST NEED HELP!!

90. A medical supply company has customers in eight cities. It is trying to decide how many salespeople it needs to service these customers. Each salesperson needs to be located in one of the eight cities and needs to be assigned to a subset of the customers. For example, the company might base a salesperson in New York and have this person service customers in New York, Boston, and Philadelphia. Each salesperson receives an annual salary of $70,000 and can work as many as 230 days per year. This includes days working with customers and days traveling to and from customers. Find an assignment that minimizes the total cost of salaries and traveling. The solution should indicate the number of salespeople to employ, where they should be based, and which cities they should serve. Assume that customers in a given city must be serviced by a single salesperson

A B C E F G H 1 J K L M N P $70,000 230 Boston New Orleans 1800 19340 19340 1800 1920 19360 15940 9810 3080 33890 38360 32730 39280 30040 New York 1920 19360 1800 15900 3080 33980 38480 39390 Orlando Philadelphia 15940 3080 9810 18100 15900 3080 1800 14700 14700 1800 29490 33030 39740 37990 37720 38670 Phoenix 33890 21530 33980 29490 33030 1800 16880 11320 Portland Sacramento 38360 39280 32730 30040 38480 39390 39740 37720 37990 38670 16880 11320 1800 9040 1800 18100 21530 9040 5 #2. Problem #90 (40 points) 6 Annual salary 7 Max days worked 8 9 Annual travel costs 10 From To 11 Boston 12 New Orleans 4 13 New York 14 Orlando E 15 Philadelphia 16 16 Phoenix 17 Portland 18 Sacramento 19 20 Days of work 21 22 Annual days traveling 23 From To 24 Boston 25 New Orleans 26 New York 27 Orlando 28 Philadelphia 29 Phoenix 30 Portland 31 Sacramento 32 120 80 160 70 130 65 75 65 Locate salesperson New York 12 24 4 4 Boston New Orleans 4 24 24 4 12 24 21 17 13 23 33 25 36 33 37 31 21 13 33 36 Orlando Philadelphia 21 13 17 23 21 13 4 21 21 4 30 33 37 36 36 37 Phoenix 33 25 33 30 33 4 22 18 Portland Sacramento 36 37 33 31 36 37 37 36 36 37 22 18 4 17 17 4 37 A H J K L M N P Q Phoenix Portland Sacramento ays in travel Days worked aled/worked Logical days limit 33 34 35 36 37 38 39 40 B E F Assignments (based at city in column B, traveling to cities in row 34) From To Boston New Orleans New York Orlando Philadelphia Boston New Orleans New York Orlando Philadelphia Phoenix Portland Sacramento Number assigned to 41 42 43 Required Number of salespersons Total annual salary Total annual travel cost Total annual cost Answer key: Total cost - 402080 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 List the cities where a salesperson will be located and the cities assigned to each salesperson. City of salesperson Cities assigned to the salesperson 61 A B C E F G H 1 J K L M N P $70,000 230 Boston New Orleans 1800 19340 19340 1800 1920 19360 15940 9810 3080 33890 38360 32730 39280 30040 New York 1920 19360 1800 15900 3080 33980 38480 39390 Orlando Philadelphia 15940 3080 9810 18100 15900 3080 1800 14700 14700 1800 29490 33030 39740 37990 37720 38670 Phoenix 33890 21530 33980 29490 33030 1800 16880 11320 Portland Sacramento 38360 39280 32730 30040 38480 39390 39740 37720 37990 38670 16880 11320 1800 9040 1800 18100 21530 9040 5 #2. Problem #90 (40 points) 6 Annual salary 7 Max days worked 8 9 Annual travel costs 10 From To 11 Boston 12 New Orleans 4 13 New York 14 Orlando E 15 Philadelphia 16 16 Phoenix 17 Portland 18 Sacramento 19 20 Days of work 21 22 Annual days traveling 23 From To 24 Boston 25 New Orleans 26 New York 27 Orlando 28 Philadelphia 29 Phoenix 30 Portland 31 Sacramento 32 120 80 160 70 130 65 75 65 Locate salesperson New York 12 24 4 4 Boston New Orleans 4 24 24 4 12 24 21 17 13 23 33 25 36 33 37 31 21 13 33 36 Orlando Philadelphia 21 13 17 23 21 13 4 21 21 4 30 33 37 36 36 37 Phoenix 33 25 33 30 33 4 22 18 Portland Sacramento 36 37 33 31 36 37 37 36 36 37 22 18 4 17 17 4 37 A H J K L M N P Q Phoenix Portland Sacramento ays in travel Days worked aled/worked Logical days limit 33 34 35 36 37 38 39 40 B E F Assignments (based at city in column B, traveling to cities in row 34) From To Boston New Orleans New York Orlando Philadelphia Boston New Orleans New York Orlando Philadelphia Phoenix Portland Sacramento Number assigned to 41 42 43 Required Number of salespersons Total annual salary Total annual travel cost Total annual cost Answer key: Total cost - 402080 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 List the cities where a salesperson will be located and the cities assigned to each salesperson. City of salesperson Cities assigned to the salesperson 61