Please solve problem 24 in Excel using excels solver. Please show ALL formulas,
ID: 375343 • Letter: P
Question
Please solve problem 24 in Excel using excels solver. Please show ALL formulas, ALL calculations, ALL formulas used for each and every cell, screen shots and ALL instructions used to solve the problem. i need to know step by step how you came u came up with the solution.
24, Harry and Melissa Jacobson produce handcrafted furniture in a workshop on their farm. They have obtained a load of 600 board feet of birch from a neighbor and are planning to produce round kitchen tables and ladder-back chairs during the next 3 months. Each table will require 30 hours of labor, each chair will require 18 hours, and between them they have a total of 480 hours of labor available. A table requires 40 board feet of wood to make, and a chair requires 15 board feet. A table earns the couple $575 in profit, and a chair earns $120 in profit. Most people who buy 53Explanation / Answer
First let us find out what is given to us : Load acquired by Harry and Mellisa from neighbour board feet 600 Amount of Labor required by Table hours 30 Amount of Labor required by Chair hours 18 Total amount of labor available hours 480 Amount of board required by table board feet 40 Amount of board required by chair board feet 15 Profit on table USD 575 Profit on chair USD 120 Number of chairs produced for every table produced numbers 4 Now let the number of tables to be produced is represented by X1 & let the number of chairs to be produced are represented by X2 & let profit on entire business is represented by P Now here the object function will be : max P = X1*575+X2*120 Where constraints are : X1*30+X2*18=480 X1*40+X2*15=600 X2=4*X1 X1 & X2 >0 Now let us use excel solver to solve this problem Here we will test all available options and since number of chairs to be produced has to four times that of Tables produced our total calculation will be based upon the number of tables produced . Now let us go ahead and produce 1 table and see the results. X1 to be produced Numbers 1 X2 to be produced=4*X1 Numbers 4 Board required Board Consumed Labor required Labor Consumed Profit Total Profit X A B=A*X C D=C*X p P*X Board Feet/piece Board Feet Hours/piece Hours USD/Piece X1 to be produced Numbers 1 40 40 30 30 575 23000 X2 to be produced Numbers 4 15 60 18 72 120 480 Total 100 102 Board & Labor hours available 600 480 Now in above we can see we have consumed only 100 board feet of board out of 680 and 102 hours of labor out of 480 , so let us try with 2 , 3 , 4 & 5 pieces of tables and stop where any of the constraints of board and labor hours is exceeded. X1 to be produced Numbers 2 X2 to be produced=4*X1 Numbers 8 Board required Board Consumed Labor required Labor Consumed Profit Total Profit X A B=A*X C D=C*X p P*X Board Feet/piece Board Feet Hours/piece Hours USD/Piece X1 to be produced Numbers 2 40 80 30 60 575 23000 X2 to be produced Numbers 8 15 120 18 144 120 960 Total 200 204 Board & Labor hours available 600 480 X1 to be produced Numbers 3 X2 to be produced=4*X1 Numbers 12 Board required Board Consumed Labor required Labor Consumed Profit Total Profit X A B=A*X C D=C*X p P*X Board Feet/piece Board Feet Hours/piece Hours USD/Piece X1 to be produced Numbers 3 40 120 30 90 575 23000 X2 to be produced Numbers 12 15 180 18 216 120 1440 Total 300 306 Board & Labor hours available 600 480 X1 to be produced Numbers 4 X2 to be produced=4*X1 Numbers 16 Board required Board Consumed Labor required Labor Consumed Profit Total Profit X A B=A*X C D=C*X p P*X Board Feet/piece Board Feet Hours/piece Hours USD/Piece X1 to be produced Numbers 4 40 160 30 120 575 23000 X2 to be produced Numbers 16 15 240 18 288 120 1920 Total 400 408 24920 Board & Labor hours available 600 480 X1 to be produced Numbers 5 X2 to be produced=4*X1 Numbers 20 Board required Board Consumed Labor required Labor Consumed Profit Total Profit X A B=A*X C D=C*X p P*X Board Feet/piece Board Feet Hours/piece Hours USD/Piece X1 to be produced Numbers 5 40 200 30 150 575 23000 X2 to be produced Numbers 20 15 300 18 360 120 2400 Total 500 510 25400 Board & Labor hours available 600 480 Now as we can see we can produce a maximum of 4 tables and 16 chairs because if we are producing more than that then labor constraint is there. Maximum profit we can earn is USD 24920