please prepare an excel sheet with all the steps

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 the table below. 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 the table below. Each consultant is expected to take at most 25 trips each year. Table 1. Travel Costs and Number of Trips for SC Consulting Travel Costs ($) State Washington Oregon Califomia Idaho Nevada Montana Wyoming Utah Arizona Colorado New Mexico North Dakota South Dakota Nebraska Kansas Oklahoma Los Angeles 150 150 75 150 100 175 150 150 75 150 125 300 300 250 250 250 Tulsa 250 250 200 200 200 175 175 150 200 125 125 200 175 100 75 25 Denver 200 200 150 125 125 125 100 100 100 25 75 150 125 125 75 125 Seattle 25 75 125 125 150 125 150 200 250 250 300 200 200 250 300 300 Number of trips 40 35 100 25 40 25 50 30 50 65 40 30 20 30 40 55 a) If there are no restrictions on the number of consultants at a site and the goal is to minimize costs, how many consultants should be assigned to each office? What is the annual cost in terms of the facility and travel? (use the following table along with solver to calculate the annual cost as well as the number of consultants) Tre Trips from Cout Free from Tripe from LA Cost from Tuise Trip Cost Demand Coor De 200 200 150 75 -100 Washington Oregon Galfomia Idaho Nevada Montana Wyoming Total of tripe 40 35 100 25 40 25 50 30 50 85 40 30 20 30 - Cost from LA 150 150 75 150 100 175 150 150 75 150 125 300 300 250 250 250 250 200 200 200 175 175 150 Utah 8 93 83 82 83%88% 125 12 100 100 100 25 75 150 125 Autona Colorado New Mexico North Dakota South Dakota Nebraska Kansas Oklahoma 250 200 175 100 75 25 300 200 200 250 8888888888 75 of trips of Consultants Fixed Cost of office Cost of Trips 165.00 131.230 140.000 545.000 105428 131.230 140.000 545.000 Total Office Cost Total System Cost 501.650 b) Identify a geographic location of a potential home office that will minimize the transportation cost based on the coordinated in Table 2. (Note: Fn is the average Travel Costs of each state) Table 2. Travel Costs and Geographical Coordinates 40 35 100 25 40 25 Markets Washington Oregon Califom Idaho Nevada Montan Wyoming Utah Arona Colorado New Mexico North Dakota South Dakota Nebraska Kansas Oklahoma F 156.3 168.8 137.5 150.0 143.8 150.0 143.8 150.0 156.3 1375 156.3 212.5 200.0 1813 175.0 175.0 y. 50 65 30 75 38 10 10 10 52 34 15 12 12 20 25 80 29 26 17 15 20 12 12 20 17 10 30 25 70 30 50 65 40 30 20 30 40 55