# Linear Programming problems:Excel solver

@ Reconsider the Super Grain Corp. case study as presented in Section 4.1. The advertising firm, Giacomi & Jackowitz, now has suggested a fourth promising advertising medium-radio commercials-to promote the company's new breakfast cereal, Crunchy Start. Young children are potentially major consumers of this cereal, but parents of young children (the major potential purchasers) often are too busy to do much reading (so may miss the company's advertisements in magazines and Sunday supplements) or even to watch the Saturday morning programs for children where the company's television commercials are aired. However, these parents do tend to listen to the radio during the commute to and from work. Therefore, to better reach these parents, Giaeomi& Jackowitz suggests giving consideration to running commercials for Crunchy Start on nationally syndicated radio programs that appeal to young adults during typical commuting hours. Giacomi & Jackowitz estimates that the cost of developing each new radio commercial would be S50, 000, and that the expected number of exposures per commercial would be 900,000. The firm has determined that 10 spots are available for different radio commercials, and each one would cost $200,000 for a normal run.

E* Q. Formulate and solve a spreadsheet model for the revised advertising-mix problem that includes

this fourth advertising medium. Identify the data cells, the changing cells, and the target cell.

Also show the Excel equation for each output cell expressed as a SUMPRODUCT function.

b. Indicate why this spreadsheet model is a linear programming model.

c. Express this model in algebraic form.

4.8 Consider a cost-benefit-trade-off problem having the following data:

a. Formulate a linear programming model for this problem on a spreadsheet. 3

b. Use the spreadsheet to check the following solutions: (X1,X2) = (7, 7), (7, 8), (8, 7), (8'-S).

(8,9), (9, 8). Which of these solutions are feasible? Which of these feasible solutions has the

best value of the objective function?

c. Use the Solver to find an optimal solution.

d. Express the model in algebraic form.

4.16 The Fagersta Steelworks currently is working two mines to obtain its iron ore. This iron ore is shipped to either of two storage facilities. When needed, it then is shipped on to the company's steel plant. The diagram below depicts this distribution network, where M1 and M2 are the two mines, S1 and S2 are the two storage facilities, and P is the steel plant. The diagram also shows the monthly amounts produced at the mines and needed at the plant, as well as the shipping cost and the maximum amount that can be shipped per month through each shipping lane.

Management now wants to determine the most economic plan for shipping the iron ore from the mines through the distribution network to the steel plant.

a. Identify all the requirements that will need to be expressed in fixed-requirement contraints.

E* b. Formulate and solve a linear programming model for this problem on a spreadsheet.

c. Express this model in algebraic form.

#### Solution Summary

This posting contains solutions to following LPPs: