90.2K

Verified Solution

Link Copied!

A company produces four types of bookshelves, labeled 1, 2, 3, 4. The four types of bookshelves differ with respect to size, shape, and materials used. Each type requires a certain amount of skilled labor, wood, and glass, and sells for a different price. The data are given in the Assignment Excel File. During the coming week, the company can purchase up to 6400 hours of skilled labor, 9000 units of wood, and 8,000 units of glass. The unit costs are $20 per labor hour, $4.5 per unit of wood, and $3 per unit of glass. Note that the company only incurs such costs on the actual amount of labor, wood, and glass purchased. Market constraints are such that it is impossible to sell more than 1000 Type-1 bookshelves, 800 Type-2 bookshelves, 500 Type-3 bookshelves, and 700 Type-4 bookshelves per week. You are required to answer the following questions: 1. [50 marks] Construct a linear programming model to find out the company's production plan that can maximize its weekly profit. Note: For your Excel model, you are required to define range names for the inputs, decision variables, and outputs where appropriate, apply the defined range names in the model, and show the defined range names in the Excel worksheet. 2. [12 marks] Based on your Excel solution, which resource constraints can be regarded as bottlenecks. Provide your answer and justification in a Text Box below your model. 3. [28 marks] Obtain the "Sensitivity Report" from Solver. Which type of bookshelf has a value of zero at the optimal solution? How much selling price should that type of bookshelf be changed so that the optimal solution will be changed accordingly? What is the shadow price of the labor hours (with the value 6400)? If the company reduces 100 labor hours, how would it affect its maximum profit? Provide your answer in a Text Box below your model.

A B D E F G $20.0 $4.5 per $3.0 Type-1 3 1 Problem 1. 2 3 Input data 4 Hourly wage rate 5 Cost unit of wood 6 Cost per unit of glass 7 8 Bookself type 9 Labor hours per bookself 10 Wood per bookself 11 Glass per bookself 12 Unit selling price 13 Maximum sales 14 15 16 17 18 19 4 Type-2 2 2.5 2 $68.0 800 Type-3 4 1 1.5 $90.0 500 Type-4 3 3 2.5 $96.0 700 5 $108.0 1000 A B D E F G $20.0 $4.5 per $3.0 Type-1 3 1 Problem 1. 2 3 Input data 4 Hourly wage rate 5 Cost unit of wood 6 Cost per unit of glass 7 8 Bookself type 9 Labor hours per bookself 10 Wood per bookself 11 Glass per bookself 12 Unit selling price 13 Maximum sales 14 15 16 17 18 19 4 Type-2 2 2.5 2 $68.0 800 Type-3 4 1 1.5 $90.0 500 Type-4 3 3 2.5 $96.0 700 5 $108.0 1000

Answer & Explanation Solved by verified expert

See Answer