Question

A factory produces canoes, dinghies, speedboats and catamarans. The resources required are steel, fibreglass, wood and marine paint. The following table shows

the per-unit resource quantities required for each product,

the price we sell each product for,

the per-unit resource prices, and

the resource quantities available to us for purchase:

Products | Resources | Selling price | |||
---|---|---|---|---|---|

steel | fibreglass | wood | marine paint | ||

canoes | 0.000 | 27.000 | 1.000 | 7.000 | $381.80 |

dinghies | 15.000 | 4.000 | 2.500 | 2.100 | $153.10 |

speedboats | 36.000 | 4.000 | 1.000 | 2.300 | $220.60 |

catamarans | 11.000 | 64.000 | 13.000 | 24.000 | $1274.60 |

Price per unit | $2.00 | $2.40 | $7.00 | $40.00 | |

Amount available | 1900.000 | 900.000 | 55.000 | 900.000 |

After calculating the per-unit profits, we can formulate the linear program to maximise profit as follows:

Max *P* = 30*X*1 + 12*X*2 + 40*X*3 +
48*X*4

s.t.

15*X*2 + 36*X*3 + 11*X*4 ≤ 1900.00

27*X*1 + 4*X*2 + 4*X*3 + 64*X*4 ≤
900.00

*X*1 + 2.5*X*2 + *X*3 + 13*X*4 ≤
55.00

7*X*1 + 2.1*X*2 + 2.3*X*3 + 24*X*4 ≤
900.00

*Xi* ≥ 0.

When this linear program is solved in *Excel*, the
following Sensitivity Report is produced:

Variable Cells

Cell | Name |
Final Value |
Reduced Cost |
Objective Coefficient |
Allowable Increase |
Allowable Decrease |
---|---|---|---|---|---|---|

$B$5 | Canoes | 2.222222222 | 0 | 30 | 10 | 27.18161926 |

$B$6 | Dinghies | 0 | -67.16666667 | 12 | 67.16666667 | 1E+30 |

$B$7 | Speedboats | 52.77777778 | 0 | 40 | 1E+30 | 10 |

$B$8 | Catamarans | 0 | -345.0555556 | 48 | 345.0555556 | 1E+30 |

Constraints

Cell | Name |
Final Value |
Shadow Price |
Constraint R.H. Side |
Allowable Increase |
Allowable Decrease |
---|---|---|---|---|---|---|

$B$14 | Steel | 1900 | 0.277777778 | 1900 | 80 | 984.3478261 |

$B$15 | Fibreglass | 271.1111111 | 0 | 900 | 1E+30 | 628.8888889 |

$B$16 | Wood | 55 | 30 | 55 | 23.29218107 | 2.222222222 |

$B$17 | Marine paint | 136.9444444 | 0 | 900 | 1E+30 | 763.0555556 |

Now suppose that the amount of fibreglass available changes to 803.80 units. From the Sensitivity Report, what is the most you can say about the effect on the optimal production plan and profit?

Select one:

a. The optimal production plan will *not* change. The
optimal profit will be $2177.78.

b. The optimal production plan *will* change. The optimal
profit will be between $2200.00 and $2273.83.

c. The optimal production plan *will* change. The optimal
profit will be between $2200.00 and $2275.28.

d. The optimal production plan *will* change. The optimal
profit will be $2070.47.

e. The optimal production plan *will* change. The optimal
profit will be $2182.03.

f. The optimal production plan *will* change. The optimal
profit will be less than $2039.78.

Answer #1

When we solve the given lpp in excel using solver,

Here rows B2: B4 is obtained by the formula B2= B3*B2; C2= C3*C2; D2= D3*D2 ; E3= E3*E2

F3 is obtained by F3 = B3*B2+ C3*C2 + D3*D2+ E3*E2

F5= B5*$B$2+C5*$C$2+D5*$D$2+E5*$E$2

F6 = B6*$B$2+C6*$C$2+D6*$D$2+E6*$E$2 and so on.

Using Solver in excel we use the above following values

Data tab > Solver

So we get optimal profit in F6

But now that amount of fibre glass available has changed from 900 to 803.80, our problem will change same way,

Formulating in Solver

And Solving

As we can see the solution does not change, Therefore optimal production plan will not change. The optimal profit will be $2177.78.

Here is the sensitivity report

A factory produces cakes, pies, rolls and danishes. The
resources required are flour, sugar, meat and fruit. The following
table shows
the per-unit resource quantities required for each product,
the price we sell each product for,
the per-unit resource prices, and
the resource quantities available to us for purchase:
Products
Resources
Selling price
flour
sugar
meat
fruit
cakes
0.500
0.900
0.000
0.100
$8.40
pies
0.430
0.090
0.500
0.000
$20.21
rolls
0.250
0.015
0.000
0.000
$1.45
danishes
0.180
0.290
0.000
0.050...

Local Steel Construction Company produces two products, steel
and wood beams. Steel beams have a unit contribution margin of
$200, and wood beams have a unit contribution margin of $150. The
demand for steel beams exceeds Local Steel Construction Company's
production capacity, which is limited by available direct labor and
machine-hours. The maximum demand for wood beams is 90 per week.
Management desires that the product mix should maximize the weekly
contribution toward fixed costs and profits.
Direct manufacturing labor...

Find solutions for your homework or get textbooks
home / study / business / operations management / operations
management questions and answers / partial of the excel solver
sensitivity report for the lp model in problem (3) is provided
...
Question: Partial of the Excel Solver Sensitivity
Report for the LP model in Problem (3) is provided b...
Edit question
Partial of the Excel Solver Sensitivity Report for the LP model
in Problem (3) is provided below.
Microsoft Excel 16.0...

Variable Cells
Final
Reduced
Objective
Allowable
Allowable
Cell
Name
Value
Cost
Coefficient
Increase
Decrease
$B$2
DVs Standard
0
0
25
7.5
17.77777778
$C$2
DVs Deluxe
20
0
65
160
15
Constraints
Final
Shadow
Constraint
Allowable
Allowable
Cell
Name
Value
Price
R.H. Side
Increase
Decrease
$D$4
Standard Leather
0
0
210
1E+30
210
$D$5
Premium leather
180
2.142857143
180
0
105
$D$6
Cutting and Sewing
80
0
120
1E+30
40
$D$7
Finishing
40
22.85714286
40
12.17391304
0
Consider the RHS sensitivity...

Deegan Industries manufactures two types of roll bars for
Porsches. Model DRB is bolted to the car using existing hotels in
the car's frame. Model DRW is a heavier roll bar that must be
welded to the cars frame. Model DRB requires 20 pounds of a special
high alloy steel, 40 minutes of manufacturing time, and 60 minutes
of assembly time. Model DRW requires 25 pounds of the special high
alloy steel, 100 minutes of manufacturing time, and 40 minutes...

Tom's, Inc., produces various Mexican food products and sells
them to Western Foods, a chain of grocery stores located in Texas
and New Mexico. Tom's, Inc., makes two salsa products: Western
Foods Salsa and Mexico City Salsa. Essentially, the two products
have different blends of whole tomatoes, tomato sauce, and tomato
paste. The Western Foods Salsa is a blend of 50% whole tomatoes,
30% tomato sauce, and 20% tomato paste. The Mexico City Salsa,
which has a thicker and chunkier...

Fancy Millwork has a factory that produces custom kitchen
cabinets. It has multiple product lines. Materials and labor for
the cabinets are determined by each job. To simplify the
assignment, we will assume the following average costs. The company
estimates that it will have 32,000 direct labor hours in total for
the kitchen cabinets. The materials include $2,000 for the wood and
other materials on a per job basis. It requires 40 hours of labor
on average for a custom...

1) X Company incurred the following costs in
2017:
Factory insurance
$5,629
Customer service
4,766
Advertising costs
4,594
Factory maintenance
5,023
Direct labor
5,972
Direct materials
4,514
Sales salaries
5,023
Factory utilities
5,153
Research & Development
5,599
Material handling
4,174
What was total overhead in 2017?
2) X Company had the following inventory
account balances in 2017:
Account
January 1
December 31
Materials
$14,524
$16,900
Work in Process
14,622
21,768
Finished Goods
14,594
14,594
The following additional information for the...

Answer Questions 2 and 3 based on the following LP
problem.
Let P1 = number of Product 1 to be
produced
P2 =
number of Product 2 to be produced
P3 =
number of Product 3 to be produced
Maximize 100P1 + 120P2 +
90P3 Total
profit
Subject to
8P1 + 12P2 + 10P3 ≤
7280 Production budget
constraint
4P1 + 3P2 + 2P3 ≤ 1920 Labor
hours constraint
P1
> 200 Minimum
quantity needed...

Briefly state the basic characteristics of pure competition,
pure monopoly, monopolistic competition, and oligopoly. Under which
of these market classifications does each of the following most
accurately fit? (a) a supermarket in your hometown; (b) the steel
industry; (c) a Kansas wheat farm; (d) the commercial bank in which
you or your family has an account; (e) the automobile industry. In
each case justify your classification.
“Even if a firm is losing money, it may be better to stay in...

ADVERTISEMENT

Get Answers For Free

Most questions answered within 1 hours.

ADVERTISEMENT

asked 35 minutes ago

asked 56 minutes ago

asked 1 hour ago

asked 1 hour ago

asked 1 hour ago

asked 1 hour ago

asked 1 hour ago

asked 1 hour ago

asked 1 hour ago

asked 1 hour ago

asked 2 hours ago

asked 2 hours ago