Purchase Solution

Linear Programming Model on a Spreadsheet using Solver

Not what you're looking for?

Ask Custom Question

JL Foods is planning to increase its advertising campaign from $1.4 million to $2 million based, in part, in the introduction of a new product. JL Taco Sauce, to accompany its traditional products, JL Ketchup and JL Spaghetti Sauce. In the past, JL Foods promoted its two products individually, splitting its advertising budget equally between ketchup and spaghetti sauce.

From past experience, the marketing department estimates that each dollar spent advertising only ketchup increases ketchup sales by four bottles and each dollar spent advertising only spaghetti sauce increases its sales by 3.2 bottles. Since JL makes $0.30 per bottle of ketchup and $0.35 per bottle of spaghetti sauce sold (excluding the sunk cost of the given advertising budget), this amounts to a return of $1.20 (=4 * $0.30) per advertising dollar on ketchup and $1.12 (=3.2 * $0.35) per advertising dollar on spaghetti sauce. Because taco sauce is a new product, its initial return is projected to be only $0.10 per bottle, but each advertising dollar spent solely on taco sauce is estimated to increase sales by 11 bottles. The company also projects that sales of each product would increase by another 1.4 bottles for each dollar spent on joint advertising of the three products.

JL wishes to maximize its increase in profits this year from advertising while also "building for the future" by adhering to the following guidelines for this year's advertising spending:

- A maximum of $2 million total advertising
- At most $400,000 on joint advertising
- At least $100,000 on joint advertising
- At least $1 million promoting taco sauce, either individually or through joint advertising
- At least $250,000 promoting ketchup only
- At least $250,000 promoting spaghetti sauce only
- At least $750,000 promoting taco sauce only
- At least as much spent this year as last year promoting ketchup, either individually or by joint advertising
- At least 7.5 million total bottles of product sold

a) Construct, in equation form, the linear programming model that determines the optimal allocation of advertising dollars among the four advertising possibilities (each individual & joint).
b) Solve the problem using Solver
c) What is the total return per advertising dollar?
d) Suppose the constraint requiring that at least $750,000 be spent on promoting taco sauce were lowered to $700,000. How much would the profit increase?

hint: to calculate the profit impact of $1 in advertising as one of the objective function coefficients, you have to use a weighted average of the profit from each type of product since they have increased sales of 1.4 bottles each.

Purchase this Solution

Solution Summary

This solution provides assistance with the linear programming problem.

Solution provided by:
Education
  • BE, Bangalore University, India
  • MS, University of Wisconsin-Madison
Recent Feedback
  • "Your explanation to the answers were very helpful."
  • "What does 1 and 0 means in the repair column?"
  • "Went through all of the formulas, excellent work! This really helped me!"
  • "try others as well please"
  • "Thank you, this helped a lot. I was not sure how to plug in those numbers to a formula. This was a great help. Now I have to figure out how to explain cost of capital is used in net present value analysis, and how cost of capital is used in net present value analysis. This stuff gets confusing."
Purchase this Solution


Free BrainMass Quizzes
Motivation

This tests some key elements of major motivation theories.

Team Development Strategies

This quiz will assess your knowledge of team-building processes, learning styles, and leadership methods. Team development is essential to creating and maintaining high performing teams.

Business Ethics Awareness Strategy

This quiz is designed to assess your current ability for determining the characteristics of ethical behavior. It is essential that leaders, managers, and employees are able to distinguish between positive and negative ethical behavior. The quicker you assess a person's ethical tendency, the awareness empowers you to develop a strategy on how to interact with them.

Understanding the Accounting Equation

These 10 questions help a new student of accounting to understand the basic premise of accounting and how it is applied to the business world.

Academic Reading and Writing: Critical Thinking

Importance of Critical Thinking