Problem of Cutting, Assembling and Production of Chairs solved with Excel Solver

“The Old Chairs”, a rustic company manufactures, among many other products, three types of chairs A, B and C, which are sold at price of 11, 13 and 12 dollars each respectively. The chairs go through three processes, Cutting, Assembly and Painting, for which up to 17, 13 and 15 hours a week respectively are dedicated to these operations for these products. The type A chair takes 3 hours for cutting, 1 hour for assembling and 3 hours for painting. The type B chair takes 1 hour for cutting, 4 hours for assembling and 3 hours for painting. And finally the type C chair takes 5 hours for cutting, 2 hours for assembling and 2 hours for painting. According to the above information:

a) Solve the problem with continuous variables and point out the results for each variable.

Decision Variables: the level of weekly production was set for each of the varieties of chair as detailed below:

decision-variables-chairs

Objective Function: Maximize weekly earnings associated with the production and sale of the chairs.

objective-function-chairs

Constraints: In the process of cutting, assembling and painting the availability of weekly hours must be respected. Additionally the constraints of non-negativity must be met.

constraints-chairs

The computational implementation of the above problem with Excel Solver can achieve the following results:

optimal-solution-chairs

Where the optimal solution is A=1,914286, B=1,828571 y C=1,885714 with optimal value V(P)=67,45714.

b) Modify the conditions of the variables and select them whole (integer) and observe the difference between the response from point a) and this new one found.

When defining the decision variables as integers we are facing an Integer Programming model (being the first stage a problem in Linear Programming). The results are:

integer-optimal-solution-ch

The optimal solution is A=1B=2 y C=2 with optimal value V(PE)=61.

c) Reach to a conclusion about what happened between continuous variables and integer variables.

It is important to note that the domain of feasible solutions of the problem (part b) is a subset of the domain of feasible solutions of the linear problem (part a). Therefore it is natural that, without obtaining a solution with integer values for the decision variables in the first problem, the optimal value necessarily will decrease in whole variant of this maximization (V(PE)<V(P)). It can also be noted that the integer solution, not necessarily is achieved by approximating the fractional results of a solution of a linear problem to the nearest lower or higher integer. Consequently, to address the resolution of a model which takes integer values for the decision variables efficiently, it requires a specific algorithmic alternative such as the Branch and Bound Method.

Below is a link to download the Excel file used for solving the problem of cutting, assembling and production of chairs. Two sheets corresponding to part a) and b) the proposed problem are included in the file: Production of Chairs.

Rating: 5.0/5. From 1 vote.
Please wait...

, , , , , ,

Sin Comentarios aun. Se el primero en comentar!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Our Site is Proudly Hosted on