# Network Flow Models; Supply, Demand, and Transshipment Nodes; Optimization in Excel; Solver

Problem:

A furniture manufacturer has warehouses in cities represented by nodes 1, 2, and 3 in Figures 5.34. The values on the arcs indicate the per unit shipping costs required to transport living room suites at each warehouse is indicated by the negative number next to nodes 1, 2, and 3. The demand for living room suites is indicated by the positive number next to the remaining nodes.

a) Identify the supply, demand, and transshipment nodes in this problem.

b) Use Solver to determine the least costly shipping plan for this problem and provide Excel spreadsheet with formulas and solution (Solver).

Network Models; Optimization Problem using Excel add-in Solver

This is a problem (5-14) from the textbook by Cliff Ragsdale, "Spreadsheet Modeling and Decision Analysis".

This problem requires the use of Excel and the add-in, called Solver. The course is Excel-based and Solver is the optimization application used for all problems.

The problem appears in this text box and is also attached as a MS Word file, so it is sure to transmit legibly. The MS Word version will likely need to be the document from which to work, as the text box does not allow information to be transmitted well (see attached MS Word doc below).

© BrainMass Inc. brainmass.com October 24, 2018, 6:57 pm ad1c9bdddfhttps://brainmass.com/math/optimization/network-flow-models-supply-demand-and-transshipment-nodes-optimization-in-excel-solver-53726

#### Solution Summary

Network Flow Models; Supply, Demand, and Transshipment Nodes; Optimization in Excel and Solver are investigated. The solution is detailed and well presented. The response received a rating of "5/5" from the student who originally posted the question.

Linear programming Questions

Study Questions

______1) For a linear program, which is not a property?

a) Proportionality.

b) Constraints and object function terms are additive

c) The value of decision variables are divisible.

d) Model parameters are assumed to be known with certainty.

e) Must be solved on a computer

_____ 2) Which of the following is a valid objective function in linear programming?

a) Max 5xy.

b) Min 4x + 5y - (2/3)z.

c) Max 4 x 2 + 6 Y 2.

d) Min (x + y) / z.

e) None of the above.

______3) The improvement in the value of the objective function per unit increase in a right-hand side is the

a. sensitivity value.

b. shadow price.

c. constraint coefficient.

d. slack value.

_____4) Study of how changes in the coefficients of a linear programming problem affect the optimal solution is called

a. sensitivity analysis.

b. transshipment analysis.

c. sunk cost analysis.

d. duality analysis.

e. none of the above.

_____5) Which of these is not a type of integer model?

a. a real integer model.

b. a total integer model.

c. a 0-1 integer model.

d. a mixed integer model.

_____6) Which is not a linear program network flow problem?

a. Transportation.

b. Assignment.

c. Blending.

d. Transshipment.

_____7) Given Max 2X1 - 7X2, what is the slope of the objective function line?

a. -2/7.

b. 2/7.

c. -7/2.

d. 7/2.

e. None of the above.

_____ 8) When a constraint is binding

a. the slack is zero.

b. the slack is negative.

c. the slack is positive.

d. the surplus is positive.

e. the surplus is negative.

_____ 9) Which constraint will force the number of hens to be exactly 10 times the number of roosters?

a. R - 10 H =0.

b. H - 10 R =0.

c. H - 10 R > =0.

d. R - 10H > =0.

e. R - 10H < =0.

f. None of the above.

10) Honey Oak - Graph Manual - Honey Oak Furniture company produces tables and chairs from oak. Each week the company has 160 hours of labor and 72 board feet of fine oak. Honey Oak can only sell 6 tables per week. Each chair requires 20 hours of labor and 12 board feet. The tables require 16 hours of labor and 4 board feet of oak. The profit from a chair is $100 and a table is $400. Honey Oak Furniture wants to determine the number of chairs and tables to produce each week to maximize profit. Set up the linear program in the following box (Give the model). Next, plot and solve by hand. You may check with Solver.

| | | | | | | | | | | | | | | | | |

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

Shade the feasible region.

Circle the possible feasible extreme points.

Clearly mark the optimal point

Include the Z optimization line

11) You Chops Peas - Graph Manual - You Chops grocery store sells its own brand of canned peas and some national brands. The store makes $.30 profit per can selling its own brand of peas and a profit of $.20 per can from any of the national brands. You Chops has 864 square inches of shelf space and each can of peas requires 9 square inches. From the sales records, You Chops never sells more than one-half as many cans of its own brand as it does of the national brands. Furthermore, You Chops can obtain no more than 50 cans of the local brand per week. The store wants to know how many cans of its own brand of peas and how many of the national brands it should stock each week to maximize profit. Set up the linear program in the following box (Give the model). Next, plot and solve by hand. You may check with Solver.

| | | | | | | | | | | | | | | | | |

10 20 30 40 50 60 70 80 90 100 110 120 130 140150 160 170

Shade the feasible region.

Circle the possible feasible extreme points.

Clearly mark the optimal point

Include the Z optimization line

2) Quick Cake - Sensitivity - The Quick Cake Company makes giant cakes and pies. These are specialty-baked items. The main ingredients of the cooked goods are flour, sugar, and eggs. The resource requirements of the cake and pies and the amount available are in the following table.

Product Flour Sugar Eggs

Cake 6 Pounds 4 Pounds 2 Eggs

Pie 4 Pounds 5 Pounds 3 Eggs

Total Resources 1000 pounds 800 pounds 450 eggs

The sales price of the cake is $16 and the pie is $18. The cost of the cake is $4 and the cost of the pie is $2. Set up this problem to maximize the profit.

Set up the linear model to maximize the profit.

Now run the sensitivity analysis and post in the following box.

a) How many cakes and pies should QuickCake make?

b) Which of the three constraints is non-binding?

c) What is the profit at the optimal solution?

d) How much would one additional egg add to the Z value?

e) How much could you increase the profit of the pie before the solution would change?

f) Do we have a surplus of any ingredient? If so, what and how much surplus?

3) Textile Mill - Block manufactures denim. It has three grades that it produces. Those are: high, medium, and low. Block has 2 factories. Factory 1 cost $6000 per day to run; whereas, Factory 2 cost $5000 per day. The amount each factory is capable of producing and the requirements are in the following table.

Denim

Grades Factory 1 (1000 lb) Factory 2

(1000 lb) Requirements (1000 lb)

1. High 6 2 12

2. Medium 2 2 8

3. Low 4 10 9

Block wants to know the number of days to operate each mill in order to meet the requirements at the minimum cost.

a) Give the set up for the LP problem.

b) Give the Excel set up

c)Give the solution.

d) What is the Z value?

e) What is (are) the binding constraints?

4) Insurance Sales - Charlie is going into the insurance business. He will sell homeowner, auto, and life insurance policies. To sell policies, time is required. The time in hours to sell a homeowner policy is 14 hours. To sell an auto policy is 12 hours. To sell a life insurance policy requires 28 hours. He has 5600 hours budgeted for the year to sell these policies. There are also expenses associated with the policies. To sell a homeowner policy takes $40. To sell an auto policy takes $25. Finally, to sell a life insurance police takes $30. Charlie has budget $12,000 for expenses. Charlie wants to be able to cover all the types of insurance, so he wants to sell at least 50 of each of the policy types. His yearly profit from a homeowner policy is $40, from an auto is $30 and from a life policy it is $50.

Charlie wants to maximize his profits.

a. Formulate an LP programming model for this problem.

b. Give the Excel sheet set up.

c. Solve this model using the computer for non-integer and integer.

Non Integer

Integer

d. For the Non-integer, what is/are the binding constraint(s)?

5) Sales Territories - Outback Camping supply wants to redistribute their sales territory. Now Outback has 12 salespersons in three regions - South, East and Midwest. The sales by region by salesperson are South $600 per day, East $540 per day, and the Midwest at $375 per day. In the reassignment, no salespersons are to be fired. Also, Outback wants at least 5 in the South region. Outback also wants the ratio of South sales to the other two to be greater than or equal to 2. Outback wants to determine the number to assign to each region. Outback also has a budget of $750 per day. This budget is not to be exceeded. Salespersons in the South spend $80 per day. Salespersons in the East spend $70 per day and salespersons in the Midwest spend $50 per day..

Outback wants to maximize sales. Solve as an integer problem.

b. Formulate an LP programming model for this problem.

b. Give the Excel sheet set up.

c. Solve this model using the computer for an integer solution.

7) Coal Field - Consider the following transportation problem.

From To

Supply

1 Narrows 2 Lee 3 Oxford

A Blue 12.5 13.5 14 400

B Cannon 11.5 12 12.8 300

C Wiley 13.6 15.8 11.2 200

Demand 230 250 370

a) Draw the node network diagram.

b) Set up the linear program.

c) Show the Excel set up

c) Cut and paste your Excel solution the following box.

d) Provide the quantity moving between the nodes.

To

From 1 2 3

A

B

C

e) Provide the overall Z value.

f) Were the supply and demand balanced?

Food for thought

1) The Dow has fallen from 14,000 to 7,000 for a 50% drop. It is now at 9,000. What percent does it need to increase to move back to 14,000?

2) 2) If you buy cantaloupes from Georgia at $12 per dozen and sell them for $1 each in North Carolina at a roadside stand. Which of the following may allow you to make a profit?

a. Buy a bigger truck.

b. Negotiate a cheaper lease on your stand.

c. Grow your own cantaloupes.

d. Offer a discount to persons who buy large quantities.

3) True or False

3 + 4 X 7 = 49