Question

Problem 2 The Amazing Shed Company produces three different kinds of sheds: The Basic, The Superior, and The Deluxe. Each of the sheds uses the same resources: Fab and Assembly Teams, Paint Teams and Inspection Teams. Information on each shed is listed below: for Basic Shed: Superior Deluxe Shed: #Teams # Hours # Hours Shed: # Hours # Hours Available Available Required Required (unit Required (unit per Team (unit load) load) load) per day Fab and 3.5 3.7 Assembly Team Paint Team 1 1.25 Inspection 1.2 1.5 Team The company has received orders for 15 Basic, 10 Superior, and 15 Deluxe sheds to be completed in one week. (Assume 5 days in the week). 1.5

Answer 1

We will solve this linear programming problem using Excel solver as shown below

c)

We will setup excel as below

& Share 5 -S File Home Insert Page Layout Formulas Data Review Show Queries E Connections ALZA 21 A2 From Table Properties Get External New Refresh Al Sort Data Query - Recent Sources All Edit Links Get & Transform Connections Book1 (version 1).xlsb |Autosaved] - Excel View Tell me what you want to do... Clear E Flash Fill Consolidate Reapply B E Remove Duplicates Relationships Filter Text to Data Validation Advanced Columns Sort & Filter Data Tools Group Ungroup -- Puneet Sabharwal Data Analysis ? Solver What If Forecast Subtotal Analysis - Sheet Forecast Outline Analyze J10 A B C D E F G H I J K L M N O P Q Basic Shed: # Superior Shed: Deluxe Shed: hrs req. #Teams #hrs available per hrs req hrs req. req. team per day 3 3.5 3. 7 3 1 1.25 2 1.5 Fab and Assembly Team Paint Team Inspection Team 3 5 1.5 190 220 Demand orders Profit per shed 230 10 Setup table for Excel -> Solver 11 1. Decision variables | Basic Shed 13 Units produced Superior Shed Deluxe Shed -Demand order constraint B3*813+C3°C13+D3*013 14*513+C4*C13+14*013 85*913C5*C13+D5*013 105 70 60 Total hours for all teams per week E3*3*5 # of hours constraint (Fab and Assembly team) 54*14*5 # of hours constraint (Paint team) E5*F5*5 # of hours constraint (Inspection team) 19 Objective function 20 Objective is to maximize profit MAX Basic Shed Profit Superior Shed Profit Deluxe Shed Profit Total Profit 0 0 0 0 18*813 C8*013 01308 SUM/E20:122) ... Sheet3 Sheet4 Sheet5 Sheet2 . Ready B - + 87% 5:06 AM 3/23/2020 Type here to search де е се

We will setup Solver as below

5 LPrmar23+1.xlsx - Excel Home Insert Page Layout Formulas Data Share Review 7 4. A Consolidate Relationships View Tell me what you want to do... Clear Flash Fill Roapply I'Remove Duplicates Advanced Solver Parameters Sort & Filter Group Ungroup -- Puneet Sabharwal Data Analysis 2 Solver Connections Properties Refresh All Edit Links Connections From Table Get External New Data Q uery - Lo Recent Sources Gel & Transform > Sort Filter E23 - X fx Ses Objective: SES 23 A B C DELF Te Max Mid Value of #hrs availa team per 2 By Changing Variable Cells $B$13:$D$13 Basic Shed: # Superior Shed: Deluxe Shed:#hrs req. # Teams hrsrec h stea. 3 3.5 3. 7 3 1 1.2 1.5 1.5 Fab and Assembly Team Paint Team Inspection Team 3 4 5 Subject to the Constraints: $B$12:$D$13 <= $B$15:$D$15 $B$12:$D$13 = integer SD$16:$D$18 - SH$16:5H518 Add 15 190 15 Change 10 220 230 Delete 10 Setup table for Excel -> Solver 11 1. Decision variables 12 Basic Shed 13 Units produced Reset All Superior Shed Deluxe Shed Ioad/Save 14 15 Make Unconstrained Variables Non-Negative <-Demand 03*013+(3*C13+03*0 BA*B13C4*C13 D4D 85*813+15 °C 13+05 Select a Salving Method: Simplex LP Options 17 19 Objective function 20 Objective is to maximize profit MAX 0 0 Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems and select the Evolutionary engine for solver problems that are non-smooth. Basic Shed Profit Superior Shed Profit Deluxe Shed Profit Total Profit B8*B13 C8*013 D134D8 SUM(E20:E2 23 O Sheet2 Solve close Point Type here to search ole e M S X 02 P o + 879 5:12 AM 3/23/2020 da UROOPA

5 Formulas Data Review View Share Home Insert Page Layout Show Oueries Get External Solver Parameters Data o Connections LPrmar23+1.xlsx - Excel Tell me what you want to do... Clear Flash Fill x plicates tion Tools Consolidate Relationships Group Ungroup --- Subtotal Puneet Sabharwal Data Analysis ? Solver What If Forecast Analysis Sheet Forecast Outline Analyze Ses Objective: SES21 B13 T: Max Min Value DE H I J K L M N O P Q Ry Changing Variable Cells $B$12:$D$18 Subject to the Constraints: 3 4 5 Fab and As Team Paint Team Inspection Add $B$13:$D$13 <- $B$15:$D$15 $B$ 13:$D$13 - integer $D$16:$D$18 - SH$ 16:5H$18 Change Delele Beset All 10 Setup tabi 11 1. Decisior Load/Save 13 Units prod Make Unconstrained Variables Non-Negative Select a Solving Method: Simplex IP Options Total hours for all teams per week [3*F3*5 #of hours constraint (Fab and Assembly team) 1745 # of hours constraint (Paint team) 5*555 # of hours constraint (Inspection team) 105 70 60 17 18 19 Objective 20 Objective Sulving Method Select the GRG Nonlineat engine lur Sulver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems and select the Evolutionary engine for Solver problems that are non-smooth. 24 Help Solve Close Point DE- + 7% 5:13 AM 3/23/2020 Type here to search

& Share File Home Insert Page Layout Formulas Data 2 2 Show Oueries Connections A From Table LOE Properties Get External New Refresh Data Query-Lo Recent Sources All Edit Links Gel & Transform Connections LPmar23+1.xlsx - Excel Review View Tell me what you want to do... ZA Y Clear Flash Fill Remove Duplicates Reapply Sort Filter Advanced Columns Data Validation Sort & Filter Data Tools -Consolidate Relationships Puneet Sabharwal Data Analysis ? Solver " Group - Ungroup -- What If Forecast a Analysis Sheet Subtotal Forecast Outline Analyze D E F G H I J K L M N O P Q 2 Basic Shed: # Superior Shed: # Deluxe Shed: #hrs req. #Teams #hrs available per his reg, hestea req. team per day 3 1 1.25 2 2 7 1.2 1.5 1.5 26 Fab and Assembly Team Paint Team Inspection Team 3 4 5 15 15 10 220 Demand orders Profit per shed 190 230 10 Setup table for Excel -> Solver 11 1. Decision variables 12 Basic Shed 13 Units produced 14 Superior Shed Deluxe Shed 10 11 15 105 13.5 43.2 <-Demand order constraint 83*013+3*С13-03*D13 BA"8136C4°C13 D4D13 85*813+15C13+05*013 105 70 60 Total hours for all teams per week 3*F3*5 #of hours constraint (Fab and Assembly team) 1745 # of hours constraint (Paint team) 5*555 # of hours constraint (Inspection team) 19 Objective function 20 Objective is to maximize profit MAX Basic Shed Profit Superior Shed Profit Deluxe Shed Prolit Total Profit 2090 220 2300 6590 B8*813 C8*013 D13'08 SUM(E20:E22) 23 24 Sheet2 Ready A B E - E + 87% 5:13 AM 3/23/2020 Type here to search де е еxр OY

c)

Company can make profit of $6,590 (as per Solver optimal solution)

d)

Company should add team to Fab and Assembly team where hours constraint is restricting in meeting required orders

e)

We will first calculate total hours required for each team to meet the current orders

	Basic Shed	Superior Shed	Deluxe Shed
Units required	15	10	15

135.5	# of hours required by Fab and Assembly team
57.5	# of hours required by Paint team
55.5	# of hours required by Inspection team

Numbers of hrs currently available are

105	# of hours available Fab and Assembly team
70	# of hours available Paint team
60	# of hours available Inspection team

If we add one team to Fab and Assembly team

Revised optimal solution is below (we made capacity required same as capacity available for each team to meet the order demand)

& Share ASSE File Home Insert Page Layout Formulas Data 2 2 Show Oueries Connections A Refresh Properties Data Query-Lo Recent Sources All Edit Links Gel & Transform Connections LPmar23+1.xlsx - Excel Review View Tell me what you want to do... ZA Y Clear Flash Fill Roapply 1-Remove Duplicates Sort Filter Advanced Columns Data Validation Sort & Filter Data Tools -Consolidate Relationships Puneet Sabharwal Data Analysis ? Solver From Table LEE Get External No What If Forecast Analysis Sheet Forecast " Group - Ungroup -- Subtotal Outline Analyze F11 . X fx D E F G H I J K L M N O P - #Teams req. 2 Fab and Assembly Team Paint Team Inspection Team Basic Shed: # Superior Shed: # Deluxe Shed:#hrsreg. his reg, hestea 3 3.5 3.7 3 1 1.25 2 1.2 1.5 1.5 #hrs available per team per day 7 7 3 4 5 2 10 15 190 220 Demand orders Profit per shed 230 10 Setup table for Excel -> Solver 11 1. Decision variables 12 Basic Shed 13 Units produced 15 Deluxe Shed Superior Shed 10 15 15 135.5 57.5 55.5 <-Demand order constraint 83*013+3*С13-03*D13 BA"8136C4°C13 D4D13 85*813+15C13+05*013 135.5 57.5 55.5 Total hours for all teams per week # of hours constraint (Fab and Assembly team) #of hours constraint (Paint team) # of hours constraint (Inspection team) 19 Objective function 20 Objective is to maximize profit MAX Basic Shed Profit Superior Shed Profit Deluxe Shed Prolit Total Profit 2850 220 3450 8500 B8*813 C8*013 D13'08 SUM(E20:E22) 23 24 Sheet2 Ready D - E e ee XI para om MROEPWO + 87% 5:24 AM 3/23/2020 Type here to search

Minimum number of team needed for each resource pool is

	Hrs required	# hrs available per day per team	# of teams needed
Fab and Assembly Team	135.5	7	135.5/(7*5)= 3.87
Paint Team	57.5	7	57.5/(7*5)=1.64
Inspection Team	55.5	6	55.5/(7*5)=1.59

f)

Looking at Solver solution, we can see that Deluxe Shed profit is highest among all sheds and it is most profitable.

Please give thumbs up if you like my answer. Thank you!