Question

The Livewright Medical Supplies Company has a total of 12 salespeople it wants to assign to
three regions—the South, the East, and the Midwest. A salesperson in the South earns $600 in
profit per month for the company, a salesperson in the East earns $540, and a salesperson in the
Midwest earns $375. The southern region can have a maximum assignment of 5 salespeople. The
company has a total of $16,500 per month available for expenses for all 12 salespeople. A salesperson
in the South has average expenses of $1760 per month, a salesperson in the East has average expenses
of $1540 per month, and a salesperson in the Midwest has average monthly expenses of $1100. The com-
pany wants to determine the number of salespeople to assign to each region to maximize profit
1. Use Solver to determine how many salespeople to assign to each region and write a sentence summarizing your answer.
You must assign all 12 people and you can't assign fractional salespeople.

2. Liveright pays out the sales expenses of $16,500/month on the last day of the month.
A services company has offered to manage the expense program for Liveright for a fee of $192,000 paid at the begininng of the year.
Ignoring the cost savings from outsourcing this work, does the services contract represent a lower cost to Livewright?
Assume an interest rate/hurdle rate/cost of capital of 10% per year.
Write a sentence indicating which choice is best and why.

Answer 1

1) In the Excel sheet, put all the data as per below:

	South	EAST	MIDWEST	Total
Expenses per person	1760	1540	1100
Profit per person	600	540	375
No. of people	1	1	1	=Sum(B4+C4+D4)
Total Profit	=B4*B3	=C4*C3	=D4*D3	=B5+C5+D5
Total Expenses	=B2*B4	=C2*C4	=D2*D4	=B6+C6+D6

Initially, take random value for No of Salesperson = 1, since we will get the correct value when we use a solver.

In the Solver, select the option of "Max", select the cell as $E$5.

In the constarints, add that $E$4 =12 & $B$4 <= 5, $B$4 = integer, $C$4 = integer, $E$4 = integer, $E$6<=16500 and run the solver.

You will get, sales person for South =1 & for East =6 and Midwest =5

2) Depending on the initial investment needed as well as the total expenses including the operating costs, there's definitely a cost saving since 16500*12(no of months) > 1,92,000, definitely there will be cost saving and it represents a lower cost. If we add the cost of capital, the cost will increase a bit but then depending on the core competency the decision can be taken