The production planner for a private label soft drink maker is planning the production of two soft drinks: root beer (R) and orange soda (S). There are at most 12 hours per day of production time and 1,500 gallons per day of carbonated water available. A case of root beer requires 2 minutes of time and 5 gallons of water to produce, while a case of orange soda requires 3 minutes of time and 5 gallons of water. Profits for the root beer are $6.00 per case, and profits for the orange soda are $4.00 per case.
1. Formulate the algebraic version of the appropriate linear programming model for this problem. Make sure to clearly define the decision variables, objective function and all constraints.
2. Using Excel's Solver, find the optimal solution to this problem and report it here (i.e., values of decision variables and of objective function). The optimal solution is actually very intuitive in this case. Can you explain it?
The production planner for a private label soft drink maker is planning the production of two soft drinks: root beer (R) and orange soda (S). There are at most 12 hours per day of production time and 1,500 gallons per day of carbonated water available. A case of root beer requires 2 minutes of time and 5 gallons of water to produce, while a case of orange soda requires 3 minutes of time and 5 gallons of water. Profits for the root beer are $6.00 per case, and profits for the orange soda are $4.00 per case.
1. Formulate the algebraic version of the appropriate linear programming model for this problem. Make sure to clearly define the decision variables, objective function and all constraints.
2. Using Excel's Solver, find the optimal solution to this problem and report it here (i.e., values of decision variables and of objective function). The optimal solution is actually very intuitive in this case. Can you explain it?
Related questions
Langoustine Restaurant LP Case
The Langoustine is an upscale seafood restaurant located in downtown Seattle, Washington. The restaurant has recently signed a contract with a new highly acclaimed chef and expanded the seating area. In preparation for the coming summer season, the principal owners of the restaurant approved an advertising budget of $279,000 and hired a local advertising firm, ACA Consulting to launch a major advertising campaign.
The ACA consultants provided the management of the restaurant with the following information about the industry exposure effectiveness per ad, their estimate of the number of potential new customers reached per ad, the cost of each ad.
| Television | Radio | Newspaper | |
| EXPOSURE | 85 | 25 | 12 |
| NEW CUSTOMERS REACHED per advertisement | 4,000 | 2,000 | 1,000 |
| COST per advertisement | $10,000 | $3,000 | $1,000 |
The exposure rating is viewed as a measure of the value of the ad to both existing and potential new customers. It is a function of such things as image, message recall, visual and audio appeal, and so on.
At this points, the ACA consultants pointed out that the data concerning exposure rating and new customer reach were only applicable to the first few ads in each medium.
For television, ACA stated that the exposure rating of 85 and 4,000 new customers reached per ad were reliable for the first 10 television ads. After 10 ads, the benefit is expected to decline â the exposure rating from additional television ads declines to 55 and the number of new customers reached goes down to 1500.
For radio ads, the preceding data are reliable up to a maximum of 15 ads. Beyond 15 ads, the exposure rating declines to 18 and the number of new customers reached declines to 1200.
Similarly, for newspaper ads, the exposure rating beyond 20 ads declines to 5 and the number of new customers reached declines to 700.
The table below summarizes the relevant data to account for the âhighâ impact and âlowâ impact ads:
| first 10 TV ads | additional TV ads beyond 10 | First 15 | additional radio ads | first 20 | additional newspaper ads | |
| EXPOSURE | 85 | 55 | 25 | 18 | 12 | 5 |
| NEW CUSTOMERS | 4,000 | 1,500 | 2,000 | 1,200 | 1,000 | 700 |
| COST per | $10,000 | $10,000 | $3,000 | $3,000 | $1,000 | $1,000 |
Assume that the management of the Langoustine accepted maximizing the total number of new customers reached, across all media, as the objective of the advertising campaign. In addition, the following guidelines were adopted:
Use no more than 20 television ads
Allocate at least 140,000 dollars to the television ads
Restrict the radio advertising budget to $90,000 (allocate no more than $90,000 to radio ads)
Allocate at least $30,000 to newspaper budget
Use at least twice as many radio advertisements as television advertisements.
Set the minimum on the total exposure rating from the advertising campaign to 2,000
How many television, radio and newspaper advertisements should be used to maximize number of potential customers reached and meet all of the guidelines and the budget constraint of $279,000
Formulate a linear programming model for this media selection problem (define decision variables, write down the objective function, develop constraints â show all steps/calculations) and solve it using Excelâs Solver. Generate Answer report and sensitivity reports.
Use the Answer and Sensitivity reports to develop a managerial report addressing the following questions (be specific â justify your answers by referring to reduced cost, or range of optimality, or shadow price etc.):
1.How many advertisement for television, radio and newspapers should be used to maximize the number of potential customers reached by the campaign? How will the advertising budget be allocated to each type of media?
2.What will be the total exposure rating of the ad campaign?
3.Would you recommend increasing the current $140,000 limit on advertising budget for television advertisements to 141,000 dollars? Explain/justify your answer.
4.If the current advertising budget of 279,000 was reduced by $1,000 what would be the total number of potential new customers reached?
5.If the estimate of the number of potential new customers reached by the first 15 television ads were revised by ACA downward to 3000 (from 4000), would the budget allocation change? Explain/Justify.
