Question

Billy-Sean O'Hagan has joined the Physics Society at Suburban State University, and the group is planning to raise money to support the dying space program by making and selling umbrellas. The society intends to make three models: the Sprinkle, the Storm, and the Hurricane. The amounts of cloth, metal, and wood used in making each model are given in this table.

	Sprinkle	Storm	Hurricane	Total Available
Cloth (square yards)	1	2	2	500
Metal (pounds)	2	1	3	500
Wood (pounds)	1	3	6	500
Profit ($)	1	1	2

The table also shows the amounts of each material available in a given day and the profits to be made from each model. How many of each model should the society make to maximize its profit?

Sprinkle_______ umbrellas

Storm________ umbrellas

Hurricane_______ umbrellas

Answer 1

We will solve this problem with the help of Excel.

Let x is the number of Sprinkle umbrellas

Let y is the number of Storm umbrellas

Let z is the number of Hurricane umbrellas

Objective function:

Maximize Profit = x + y + 2z

Subjected to:

x + 2y + 2z ≤ 500

2x + y + 3z ≤ 500

x + 3y + 6z ≤ 500

Load the data into Excel.

Consider three decision variable columns and a maximization profit column.

Let us use the Excel function to find the used amount.

Similarly, do for the other two columns.

Find the maximum profit for the last step.

Now, go to Data>Solver.

Use the steps as below:

Now click on Solve.

The output will be as follows:

Therefore, to maximize the profit, 200 Sprinkle umbrellas, 100 Storm umbrellas and no Hurricane umbrellas should be made to maximize the profit. The maximum profit will be $300.