Scribd
Upload
23 views9 pages

Final Reduced Objective Allowable Allowable Minimum Maximum Range Cell Name Value Cost Coefficient Increase Decrease

Uploaded by

Đỗ Khang
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
23 views9 pages

Final Reduced Objective Allowable Allowable Minimum Maximum Range Cell Name Value Cost Coefficient Increase Decrease

Uploaded by

Đỗ Khang
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 9

Microsoft Excel 16.

0 Sensitivity Report
Worksheet: [B2-Modelling.xlsx]Door & Window
Report Created: 10/26/2023 1:17:29 PM

Variable Cells
Final Reduced Objective Allowable Allowable Minimum Maximum Range
Cell Name Value Cost Coefficient Increase Decrease
$B$8 Production quantities Door 2 0 300 450 300 0 750 0<D<750
$C$8 Production quantities Window 6 0 500 1E+30 300 200 1E+30 200<W

Constraints
Final Shadow Constraint Allowable Allowable Minimum Maximum Range
Cell Name Value Price R.H. Side Increase Decrease
$D$4 Plant 1 Used per week 2 0 4 1E+30 2 2 1E+30 2<P1
$D$5 Plant 2 Used per week 12 150 12 6 6 6 18 6<P2<18
$D$6 Plant 3 Used per week 18 100 18 6 6 12 24 12<P3<24

Doors' price increases to $500 and windows' price drop to $300


%D: (500-300)/450 = 44.44%
%W: (500-300)/300 = 66.67%
Total % = 44.44 + 66.67 = 111.1%
==> Check by solver --> The initial plan changes: The firm will produce 4 doors, 3 windows
Door Window
Unit profit $ 500.00 $ 300.00
Used per week Available per week
Plant 1 1 0 4 ≤ 4
Plant 2 0 2 6 ≤ 12
Plant 3 3 2 18 ≤ 18

Production quantities 4 3

Total profit $ 2,900.00 Result: The maximum profit is 3.600 USD.


Conclusion: The firm will produce 2 door/week and 6 windows/week.
Microsoft Excel 16.0 Sensitivity Report
Worksheet: [B2-Modelling.xlsx]Ban ba lo
Report Created: 10/26/2023 1:38:32 PM

Variable Cells
Final Reduced Objective Allowable Allowable Minimum Maximum Range
Cell Name Value Cost Coefficient Increase Decrease
$B$7 Units Produced Collegiate 1000 5 32 1E+30 5 27 1E+30 27<C
$C$7 Units Produced Mini 975 0 24 4.444444444 24 0 28.44444 0<M<28.44444

Constraints
Final Shadow Constraint Allowable Allowable
Cell Name Value Price R.H. Side Increase Decrease
$D$4 Material Used per week 4950 0 5000 1E+30 50
$D$5 Labour Used per week 84000 0.6 84000 1000 39000

Maximum gain from hiring more 600 USD


Number of new hire 0.41667 new hire
full time
1 part time
Giảm giá 25% cả 2 sp Tăng giá 50% cả 2 sp dựa trên giá đã giảm
C% 160% C% (36-24)/+∞ = 0
M% 25% M% (27-26)/4,44 = 202,7%
Total % 185% Check by solver Total% 202,7% Check by solver
Initial plan doesn't change Initial plan doesn't change
Collegiate Mini
Unit profit $ 32.00 $ 24.00
Used per week Available per week
Material 3 2 4950 <= 5000 sq-ft
Labour 45 40 84000 <= 84000

Production quantities 1000 975


<= <=
Sale forecast 1000 1200

Total profit $ 55,400.00 Result: The maximum profit is 55.400 USD


Conclusion: Firm will produce 1000 Collegiates and 975 Minis
Microsoft Excel 16.0 Sensitivity Report
Worksheet: [B2-Modelling.xlsx]Lathe - B3
Report Created: 11/15/2023 2:01:37 PM

Variable Cells
Final Reduced Objective Allowable Allowable
Cell Name Value Cost Coefficient Increase Decrease
$B$6 Factory 1 Customer 1 10 0 700 100 1E+30
$C$6 Factory 1 Customer 2 2 0 900 100 100
$D$6 Factory 1 Customer 3 0 100 800 1E+30 100
$B$7 Factory 2 Customer 1 0 100 800 1E+30 100
$C$7 Factory 2 Customer 2 6 0 900 100 100
$D$7 Factory 2 Customer 3 9 0 700 100 1E+30

Constraints
Final Shadow Constraint Allowable Allowable
Cell Name Value Price R.H. Side Increase Decrease
$B$8 Received Customer 1 10 700 10 0 10
$C$8 Received Customer 2 8 900 8 0 2
$D$8 Received Customer 3 9 700 9 0 2
$E$6 Factory 1 Shipped out 12 0 12 0 1E+30
$E$7 Factory 2 Shipped out 15 0 15 2 0
Customer 1 Customer 2 Customer 3
Factory 1 $ 700.00 $ 900.00 $ 800.00
Factory 2 $ 800.00 $ 900.00 $ 700.00

Customer 1 Customer 2 Customer 3 Shipped out Supply


Factory 1 10 2 0 12 = 12
Factory 2 0 6 9 15 = 15
Received 10 8 9
= = = Total cost
Demand 10 8 9 $ 20,500.00 Result: The minimum cost is 20.500 USD
Conclusion:
Factory 1 transports 10 units to customer 1 and 2 units to customer 2
Factory 2 transports 6 units to customer 2 and 9 units to customer 3
Word processing Graphics Packets Registrations Hourly wage
Ann 35 41 27 40 $ 14.00
Ian 47 45 32 51 $ 12.00
Joan 39 56 36 43 $ 13.00
Sean 32 51 25 46 $ 15.00

Cost Word processing Graphics Packets Registrations


Ann $ 490.00 $ 574.00 $ 378.00 $ 560.00
Ian $ 564.00 $ 540.00 $ 384.00 $ 612.00
Joan $ 507.00 $ 728.00 $ 468.00 $ 559.00
Sean $ 480.00 $ 765.00 $ 375.00 $ 690.00

Total
Word processing Graphics Packets Registrations assignments Supply
Ann 0 0 1 0 1 = 1
Ian 0 1 0 0 1 = 1
Joan 0 0 0 1 1 = 1
Sean 1 0 0 0 1 = 1
Total assigned 1 1 1 1
= = = =
Demand 1 1 1 1 Total cost $ 1,957.00

Result: The minimum cost is 1957 USD


Conclusion:
Ann should be assigned to Packets
Ian should be assigned to Graphics
Joan should be assigned to Registrations
Sean should be assigned to Word processing

You might also like