QUESTION #1 50 MARKS SC Consulting, a supply chain consulting firm, must decide on the location of its home offices. Its clients are located primarily in the 16 states listed in Table. There are four potential sites for home offices: Los Angeles, Tulsa, Denver, and Seattle. The annual fixed cost of locating an office in Los Angeles is $165,428, Tulsa is $131,230, Denver is $140,000, and Seattle is $ 145,000. The expected number of trips to each state and the travel costs from each potential site are shown in Table. Each consultant is expected to take at most 25 trips each year. Develop a EXCEL Spreadsheet Model for the following conditions: If there are no restrictions on the number of consultants at a site and the goal is to minimize costs, where should the home offices be located and how many consultants should be assigned to each office? What is the annual cost in terms of the facility and travel? b) If 10 consultants are to be assigned to a home office, at most, where should the offices be set up? How many consultants should be assigned to each office? What is the annual cost of this network? State 40 150 Total # of Cost from trips LA 150 35 75 150 100 175 Washington Oregon California Idaho Nevada Montana Wyoming 125 125 125 175 Utah Cost from Cost From Cost from Tulsa Denver Seattle 250 200 250 200 200 150 200 200 150 175 125 100 150 200 250 250 125 200 200 175 200 100 125 75 75 300 125 300 131,230 140,000 145,000 75 Arizona Colorado 150 125 New Mexico 125 250 250 North Dakota South Dakota Nebraska Kansas Oklahoma Fixed Cost of office 250 250 165,428 25