inear Programing, Statistical Analysis and excel solver

b) Solve it using Excel solver. Please attach your Solver model and output (including Answer and Sensitivity Report) as Exhibit A.

As part of Exhibit A please attach 1) your spreadsheet model with formulas printed. To do this click on Ctrl-~ (the tilde key). Excel will then display all formulas. Then print your spreadsheet. Format so that it fits on 1 page landscape or portrait. 2) Your Solver dialog box. To do this when you are in the Solver dialog box click on Alt-PrtSc. Then go into any program like word and click on Paste (or Ctrl-V). Your Solver dialog box will be pasted.

See attachments.

c) What is the maximum profit? What production quantities should be made to achieve this profit?

The maximum profit is $4800. To achieve this profit, they should make 40 Chairs, 180 Desks, and 40 Tables.

d) Which resources are economically scarce (i.e., they are limiting our profits)?

Assembly hours, Machining hours, and Wood supply are the economically scarce resources (they are the binding constraints limiting our profit).

3

Microsoft Excel 16.0 Answer Report

Worksheet: [brown.xlsx]Brown

Report Created: 5/11/2019 4:51:09 PM

Result: Solver found a solution. All Constraints and optimality conditions are satisfied.

Solver Engine

Engine: Simplex LP

Solution Time: 0.031 Seconds.

Iterations: 4 Subproblems: 0

Solver Options

Max Time 100 sec, Iterations 1000, Precision 0.000001

Max Subproblems 1000, Max Integer Sols 1000, Integer Tolerance 0%, Assume NonNegative

Objective Cell (Max)

Cell Name Original Value Final Value

$E$8 Profit Total 0 4800

Variable Cells

Constraints

Cell Name Cell Value Formula Status Slack

$E$11 Fabrication LHS 1320 $E$11<=$G$11 Not Binding 680 $E$12 Assembly LHS 1800 $E$12<=$G$12 Binding 0 $E$13 Machining LHS 1600 $E$13<=$G$13 Binding 0 $E$14 Wood LHS 9400 $E$14<=$G$14 Binding 0 Microsoft Excel 16.0 Sensitivity Report

Worksheet: [brown.xlsx]Brown

Report Created: 5/11/2019 4:51:09 PM

Variable Cells

Constraints

$E$11 Fabrication LHS 1320 0 2000 1E+30 680

$E$12 Assembly LHS 1800 1.238095238 1800 360 46.66666667 $E$13 Machining LHS 1600 1.047619048 1600 360 70 $E$14 Wood LHS 9400 0.095238095 9400 155.5555556 900

(c) Create a “transportation model” similar to the one discussed in the live session. Explain what your variables mean, and what each of the constraints specify. Attach your spreadsheet formulation as Exhibit B. Please note that your spreadsheet must be sufficiently annotated so that it is readable in order to receive credit. See note earlier regarding printing Solver dialog box and solver model with formulas.

See attachments.

(d) What is the value of the objective function after optimization?

The value of the objective after optimization is 130 distance units.

(e) Explain in detail the movement plan to get cars from locations with excess supply to locations that are in deficit.

Move 2 cars from Location 4 to 2.

Move 3 cars from Location 4 to 6.

Move 4 cars from Location 5 to 1.

Move 11 cars from Location 5 to 3.

Move 2 cars from Location 6 to 2.

Move 2 cars from Location 8 to 3.

Move 7 cars from Location 8 to 6.

(f) Stretch your thinking: Bike sharing is growing around the world especially in countries such as China and Brazil. It is anticipated once driverless cars become a reality (and commonplace) car sharing would become very common. Think about similarities between the problem you solved and the issue of getting bikes to the locations they are needed. How might an optimization model be used to improve efficiencies for a bike sharing provider?

Various answers are acceptable. The key aspect is that the imbalance in supply and demand can be resolved by efficiently moving bikes and driverless cars around.

5 MATHEMATICAL MODEL

Decision Variables

Let Xij = Number of cars moved from location i to location j.

Supply locations (i) = 4, 5, 6, 8.

Demand Locations (j) = 1, 2, 3, 7.

Objective (Minimize distance):

MIN 7X41 + 5X42 + 8X43 + 3X47 + 3X51 + 8X52 + 5X53 + 6X57 + 5X61 + 4X62 + 4X63 + 3X67 + 3X81 + 7X82 + 4X83 + 4X87

Constraints:

(supply)

X41 + X42 + X43 + X47 = 5 (supply of cars at 4) X51 + X52 + X53 + X57 = 15 (supply of cars at 5) X61 + X62 + X63 + X67 = 2 (supply of cars at 6)

X81 + X82 + X83 + X87 = 9 (supply of cars at 8)

(demand)

X41 + X51 + X61 + X81 = 4 (demand of cars at 1)

X42 + X52 + X62 + X82 = 4 (demand of cars at 2) X43 + X53 + X63 + X83 = 13 (demand of cars at 3)

X47 + X57 + X67 + X87 = 10 (demand of cars at 7)

(Non-negativity)

X41, X42, X43, X47, X51, X52, X53, X57, X61, X62, X63, X67, X81, X82, X83, X87 >= 0

6

American RentaCar: Transportation Problem

Parameters

Supply 4

Locations 5

6

8

Demand

11 Decisions

13 Supply 4

14 Locations 5

6

8

Demand

19 Objective

Total Distance