Linear programming problem using maximization by means of excel solver

Question

A car company made promotion videos of their new car model to be sent to current and new customers. Customers who receive the video will also receive a coupon for a test drive of the new model. The response rate for the current customers is 25% and the response rate for the new customers is 20$, as estimated by the car company. Marketing research studies show that sales rate for current customers is 12% while sales rate for new customers is 20%. The cost per promotion, excluding test drive costs, sent to current customers is $4 and $6 for new customers. Management specified that a minimum of 30,000 current customers and 10,000 new customers should test drive the new model. Also, the number of current customers who do the test drive must be at least twice the number new customers who test drive the new model as well. If the marketing budget is $1,200,000, how many promotions should be sent to each group of customers in order to maximize total sales?

Answer 1

Alright. First, let us set up all information we have. (let us set the number of promotions sent to current customers as X and new customers as Y)

1. response rate for customers: current = 25%, new = 20%
2. sales rate for customers: current = 12%, new = 20%
3. cost per promotion: current = $4.00, new = $6.00
4. minimum number is tests: current >= 30,000, new >= 10,000
5. ratio of tests from customers: current > 2*new
6. budget constraint: $1,200,000

 

 

All of this information is given to us. Now given this information, we can set up these equations.

1. cost constraints per customer: $4X + $6Y <= $1,200,000
2. required number of response: .25X >= 30,000, .20Y >=10,000 (X >= 120,000; Y >= 50,000)
3. chance of sale: .25*.12*X = .03X, .20*.20*Y = .04Y

 

 

What we want to maximize is: max = .03X + .04Y

 

First, let us set X in terms of Y: 4X + 6Y = 1,200,000 -> X = 300,000 - 1.5Y

max = .03(300,000 - 1.5Y) + .04Y -> max  = 9,000 - .045Y + .04Y -> max = 9,000 - .005Y

 

Our constraint is: X >= 120,000 and Y >= 50,000

 

If you wish to graph the max equation, you get that it is at its maximum, given the constraints, when Y is 50,000.

 

When we set Y = 50,000, we get X = 225,000. The expected number of responses for current customers is 56,250 and for new customers, it is 10,000. The expected number of sales for current customers is 6,750 and for new customers, it is 2,000, giving us a total of 8,750 sales.