Question

Implement a spreadsheet model for this problem and solve it using solver. Oakton Manufacturing makes two types of rocking chairs specifically designed for men and women known as the His and Hers models. Each chair requires four legs and two rockers but differing numbers of wooden dowels. Each His chair requires four short dowels and eight long dowels while each Hers chair requires eight short dowels and four long dowels. Each His chair contributes 10 in profit while each Hers chair contributes12. The company has 900 legs, 400 rockers, 1200 short dowels, and 1056 long dowels available. The company wants to maximize its profit while also ensuring that it makes at least half as many His chairs as Hers chairs. What is the spreadsheet model for this problem and what is the solution obtained using solver?

Answer 1

To implement a spreadsheet model for this problem, create a table with columns for chair type, legs required, rockers required, short dowels required, long dowels required, and profit. Use Solver to set up constraints and maximize profit. The solution will provide the optimal number of His and Hers chairs to produce and the maximum profit.

Step-by-step explanation:

To implement a spreadsheet model for this problem, we can start by creating a table with the following columns: Chair Type, Legs Required, Rockers Required, Short Dowels Required, Long Dowels Required, Profit. In the Chair Type column, we can list the two chair types: His and Hers.

For each chair type, we can fill in the required number of legs, rockers, short dowels, and long dowels based on the problem's description. In the Profit column, we can calculate the profit for each chair type by multiplying the contribution per chair by the number of chairs produced.

To solve the problem using Solver, we can set up the following constraints:

1. The total number of legs used should not exceed the available number of legs (900).
2. The total number of rockers used should not exceed the available number of rockers (400).
3. The total number of short dowels used should not exceed the available number of short dowels (1200).
4. The total number of long dowels used should not exceed the available number of long dowels (1056).
5. The number of His chairs produced should be at least half as many as the number of Hers chairs produced.

By running Solver with the goal of maximizing profit, we can find the optimal number of His and Hers chairs to produce, as well as the maximum profit.