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 1. 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 also shown in

Table 1.

Each consultant is expected to take at most 25 trips each year.

Question A: 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?

Question B: If, at most, 10 consultants are to be assigned to a home office, where should the offices be set up? How many consultants should be assigned to each office? What is the annual cost of this network?

Question C: What do you think of a rule by which all consulting projects out of a given state are assigned to one home office? How much is this policy likely to add to cost compared to allowing multiple offices to handle a single state? (Note: to answer question C, you should refer to "the capacitated plant location model with single sourcing" which is explained in Chapter 5 "Network Design in the Supply Chain", Sunil Chopra and Peter Meindl, Supply Chain Management: Strategy, Planning, and Operation, Pearson, 2010.)

NOTE IT : With EXCEL SHEET !