Note:$$ Use Excel to solve each of the problems listed below.$$

The owner of XYZ$,$ Inc., hopes to expand the present operation by adding one new outlet.$$ She has studied three locations.$$ Each would have the same labor and materials costs $($food$,$ serving containers, napkins, etc.$)$ of $$3\mathrm{.}50$ per sandwich.$$ Sandwiches sell for $$6$ each in all locations.

$$

Rent and equipment costs $($all fixed costs$)$ would be $$5,000$ per month for Location A$,$ $$5,500$ for location B$,$ and $$6,000$ for Location C$.$

$$

Required :

a$.$ Determine the volume $($Q$)$ necessary for each location to realize a monthly profit of $$15,000.$ Rounded to the nearest whole number.

b$.$ If expected sales $($in units$)$ at A$,$ B$,$ and C are $22,000$ per month, $24,000$ per month, and $26,000$ units per month, respectively, which location would yield the highest profits.$$

Hint:$$ You would need to compute the profit for each location first before you selected the $$best$$ location.$$

$$

Using the following factor ratings, determine which location alternative $($X$,$ Y$,$ or Z$)$ should be chosen on the basis of maximum composite score.

$$Location Score

Factor

Weight

X

Y

Z

Convenience

$0\mathrm{.}15$

$80$

$70$

$60$

Parking facilities

$0\mathrm{.}27$

$88$

$76$

$92$

Display area

$0\mathrm{.}18$

$94$

$88$

$90$

Shopper traffic

$0\mathrm{.}20$

$98$

$86$

$80$

Operating costs

$0\mathrm{.}10$

$86$

$90$

$85$

Neighborhood

$0\mathrm{.}10$

$82$

$83$

$72$

$$

A manager has received an analysis of several cities being considered for a new office complex. The data $(10$ points maximum$)$ are as follows:$$

$$

$$L O C A T I O N$$ S C O R E

Factor

A

B

C

D

Business services

$9$

$5$

$5$

$8$

Community services$$

$7$

$6$

$7$

$4$

Real Estate cost

$3$

$9$

$7$

$5$

Construction costs$$

$5$

$6$

$8$

$6$

Cost of living$$

$4$

$7$

$5$

$7$

Transportation$$

$5$

$5$

$4$

$9$

Taxes

$6$

$6$

$8$

$4$

REQUIRED:

a$.$ If the manager weights the factors equally, how would the locations stack up in terms of their composite factor rating scores $($in other words, list the locations in the order $($descending order$)$ of their composite factor rating scores$).$

b$.$ Suppose that the manager weights business services$$and$$construction costs$$double the weights of the other factors. How would the location stack up now?$$

Complete the required work using Excel.