In: Finance
GreenLawns provides a lawn fertilizing and weed control service. The company is adding a special aeration treatment as a low-cost extra service option, which it hopes will help attract new customers. Management is planning to promote this new service in two media: radio and direct-mail advertising. A media budget of $4,000 is available for this promotional campaign. Based on past experience in promoting its other services, GreenLawns has obtained the following estimate of the relationship between sales and the amount spent on promotion in these two media:
S = -2R2− 12M2− 9RM + 18R + 33M,
Where
S = total sales in thousands of dollars
R = thousands of dollars spent on radio advertising
M = thousands of dollars spent on direct-mail advertising
GreenLawns would like to develop a promotional strategy that will lead to maximum sales subject to the restriction provided by the media budget.
(a) | What is the value of sales if $2,000 is spent on radio advertising and $2,000 is spent on direct-mail advertising? Enter amounts in thousands i. e. $1,012, should be written as $1,012. | ||||||||||||||||||||||||||||||||
$ | |||||||||||||||||||||||||||||||||
(b) | Formulate an optimization problem that can be solved to maximize sales subject to the media budget of spending no more than $4,000 on total advertising. If the constant is "1" it must be entered in the box. For subtractive or negative numbers use a minus sign even if there is a + sign before the blank. (Example: -300). If your answer is zero enter “0”. | ||||||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||||||
(c) | Determine the optimal amount to spend on radio and direct-mail advertising. How much in sales will be generated? Enter amounts in thousands i. e. $1,012, should be written as $1,012. If your answer is zero enter “0”. | ||||||||||||||||||||||||||||||||
Amount spent on radio advertising = $ | |||||||||||||||||||||||||||||||||
Amount spent on direct-mail advertising = $ | |||||||||||||||||||||||||||||||||
Total Sales = $ |
a.
The given sales function is S = -2R2− 12M2− 9RM + 18R + 33M
and where R is thousand of dollar spent on radio advertising
and M is thousand of dollar spent on mail advertising
If $2000 is spent on radio advertisement and $2000 is spent on mail advertisement then the sales achieved is
S = -2(2)2 -12(2)2 -9(2)(2)+18(2)+33(2)
S = 10
The sale generated is $10,000
b.
The optimization model is as follows:
MAX S = -2R2− 12M2− 9RM + 18R + 33M
s.t.
R+M < or equal to 4
R,M > or equal to 0
c.
Define the decision variables and objective function as shown in the screenshot:
Define the constraint by using =sum( ) function. The screenshot is shown below:
All the inputs have been done; hence click on solve icon. A dailogue box will appear. In the target cell box, input B4, where final result of NET PRESENT value will appear. Also, select the MAX option box. In the text box, "by changing cell" update the cell names where actual numbers are to be updated. add the constraints. the screenshot is shown below
Now click on ok button
Click on Solve button and Solver dailogue box will appear saying that the solutiong has been found. the screenshot is as shown below.
If the keep solver solution option button is clicked, the value of the decision table will be updated automatically. the screenshot is as shown below,
From the solution it can be seen that the company should spend $4000 on radio advertisement and $0 on direct mail advertisement. The maximum sales achieved is $40,000