please provide the decision variables,objective function, constriant equations and how it will be solved in excel? solver?

Case: Motel Location for Natures Inn

Natures Inn operates a motel chain with two types of motels. Under its brand Comfort Express

(CE), it offers a relatively inexpensive, spartan motel. Under its Family Suites (FS) brand, it

offers a more expensive motel distinguished by various extra features. Both brands are

associated with the latest ideas in green building design, attracting a segment of the customer

market that is willing to let considerations of sustainability influence its purchasing decisions.

Following a successful first round of expansion in the Northeast region, Natures Inn is

planning another round of expansion, this time into the Midwest region.

Working with a real estate consultant, Natures Inn has identified 10 potential sites for the

location of new motels. Each site can accommodate either a CE motel or an FS motel (but not

both). Using historical data from the Northeast region, the consultant has estimated the net

present value (NPV) of the cash flows attainable over the next 10 years at each location, with

separate figures for CE and FS.

The consultant has also carried out a survey to explore the extent to which demand might be

affected when two motels are located in close proximity. The first finding was that CE

customers and FS customers are different segments, for the most part, and little crossover

demand occurs. On the other hand, within CE and FS segments, competition occurs when

motels are located too close to each other. The data suggest that competition becomes an

economic factor for CE motels when they are located within 30 miles of each other and for FS

motels within 40 miles of each other. Natures Inn has therefore decided to respect these

distances in their choice of locations: In the Midwest, CE motels will not be located within 30

miles of each other, and FS motels will not be located within 40 miles of each other. The results

of the consultants work are summarized in Exhibit 6.1.

Exhibit 6.1 Proximity Data and Economic Estimates

The task for Natures Inn is now to develop a location plan for its Midwest expansion.

Excel please!!! Case: Motel Location for Nature's Inn Nature's Inn operates a motel chain with two types of motels. Under its brand Comfort Express (CE). It offers a relatively inexpensive, spartan motel. Under its Family Suites (FS) brand, it offers a more expensive motel distinguished by various extra features. Both brands are associated with the latest ideas in green building design, attracting a segment of the customer market that is willing to let considerations of sustainability influence its purchasing decisions. Following a successful first round of expansion in the Northeast region, Nature's Inn is planning another round of expansion, this time into the Midwest region. Working with a real estate consultant, Nature's Inn has identified 10 potential sites for the location of new motels. Each site can accommodate either a CE motel or an FS motel (but not both). Using historical data from the Northeast region, the consultant has estimated the net present value (NPV) of the cash flows attainable over the next 10 years at each location, with separate figures for CE and FS. The consultant has also carried out a survey to explore the extent to which demand might be affected when two motels are located in close proximity. The first finding was that CE customers and FS customers are different segments, for the most part, and little crossover demand occurs. On the other hand, within CE and FS segments, competition occurs when motels are located too close to each other. The data suggest that competition becomes an economic factor for CE motels when they are located within 30 miles of each other and for FS motels within 40 miles of each other. Nature's Inn has therefore decided to respect these distances in their choice of locations in the Midwest, CE motels will not be located within 30 miles of each other, and FS motels will not be located within 40 miles of each other. The results of the consultant's work are summarized in Exhibit 6.1. Location M 2 INVES Location 40 M 10 CENPVS M EL 29 IL 1 LS . SE LE 30 67.8.10 AR 12 13.30 AR 10 The task for Nature's Inn is now to develop a location plan for its Midwest expansion Exhibit 6.1. Proximity Data and Economic Estimates 1 2,6 2 1 1 Location Locations within Locations within CE NPV ($ FS NPV ($ 30 Miles 40 Miles Million) Million) 2 10.147 11.899 12.191 11.242 3 4 4 13.359 10.731 4 3 3,5 9.344 7.519 5 4 11.388 14.235 6 9 1,8,9 6.935 9.636 7 8,10 8,10 12.629 8.687 8 7, 9, 10 6, 7, 9, 10 13.505 10.293 9 6, 8, 10 6, 8, 10 9.344 9.709 7, 8, 9 7,8,9 8.249 11.461 The task for Nature's Inn is now to develop a location plan for its Midwest expansion. 10