Question
Please help set up Excel Solver for this problem .
Aviation Electronics produces three types of switching devices. Each type involves a two-step assembly operation. The assembly times are shown in the table below.
Assembly time per unit Station #1 Station #2 Model A 2.5 minutes 3.0 minutes Model B 1.8 minutes 1.6 minutes Model C 2.0 minutes 2.2 minutes Assembly lime per Unit (minutes) Profit per Unit Station 1 Station 2 2 3 Model A 4 Model B 5 Model C 6 Total NOTE 1: As I start to set-up these spreadsheets, I ofen find it helpful to a.) repeat key information from the problem AND format it in a way that will allow me to understand/remember what the heck the numbers mean. So you will see that I have added labels wherever possible This is for my benefit. Solver doesn't really care one way or the other 2.5 1.6 $ 2.2 $ 6.8 8.25 7.50 7.80 2.0 6.3 8 Work Time Available 9 (per week) 10 11 Wok Time Used 12 (per week) 13 14 15% 16 Model A 17 Model B 18 Model C 19 NOTE 2: As I start to set-up these spreadsheets, I ALWAYS keep in mind that Solver (or any other LP) will ask me four types of questions. The first is to choose a target cell. .the objective function. In this case, my target is cell D21. See the formula inside that cell? Before you move on to the next problem, it is my sincerest hope that you understand how and why I placed this formula in this cell The second thing Solver will ask for is the choice of maximizing or minimizing. (Right?) Yes, it might also ask if we want to set the objective function to a specific value...but we need not worry about this for these problems. The third question Solver will ask is "which cells/values shall we change?" to get our optimal solution. In this case, I've set-up the spreadsheet so that the cells to be changed are those from B16 to C18 (in yellow shaded area) Optimal Production Values Station 1 Totals Station 2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 NOTE 3: The last question that Solver will ask is one about the constraints required by the problem Some of these will be VERY easy to get Solver to digest. Some, however, will require a bit of extra foresight/planning. I begin by listing the constraints below (see Cells A23 through A31) This listing is for my own benefit, sort of like a mental checklist I use to make sure that I have giver Solver all needed constraints. Let's consider an "easy" constraint such as X1 20. Given the way I have set this spreadsheet up, I'll tell Solver that the value in Cell D16 must be greater than or equal to 20. Do you see why I picked this cell and understand the formula I have placed within it? Now, how to tackle a more complicated constraint such as either of the first two on the list? Well, I had to think a little about this but decided to make use of formulas in cells B11 (Constraint #1 and C11 (Constraint #2). Take a close look at the formula in either of these cells (please) Do they make sense to you? I'll ask Solver to set B11 less than or equal to 2,250...and, in so doing, will have tackled Constraint #1. I'll do likewise with C11 to tackle Constraint #2. Total Profit 21 23 Define: Model A = X1, Model B = X2, Model C = X3 24 Maximize: 8.25X1 + 7.50X2 + 7.80X3 25 Constraints: 26 1#1-2.5X1 + 1.8X2 + 2.0X3 = 20 30 :#5--X3 >= 20 31 :X1, X2, X3 >=0 Ahem, given #3, 4, and 5, we don't really have to worry about : NOTE 4: Please be advised that the approach I have given here is one of countless formats
Explanation / Answer
each workstation has 7.5 hours/ day
each workstation has 450 minutes/ day
each workstation has 2250 minutes in the next five days
Maximize
z = 8.25A + 7.5B + 7.8C
subject to the following
2.5A + 1.8B + 2C <= 2250
3A + 1.6B + 2.2C <= 2250
now if they already have been paid on the 20 of each model they have to make to catch up, so the max statement
z = 8.25A - 20 * 8.25 + 7.5B - 20 * 7.5 + 7.8C - 7.8 * 20
z = 8.25A + 7.5B + 7.8C - 471
and we will add
A,B,C >= 20
