A small cheese maker specialises in two types of cheese – Brie and Gouda that he makes and sells in packets of 400 grams. The cheese maker has estimated that each packet of Brie uses 1.8 litres of special milk and 100 grams of special salt. For the Gouda cheese packet, the amounts of special milk and salt needed are 1.2 litre and 150 grams respectively. The cheese maker has a limited supply of special milk and salt and a maximum of only 120 litres of milk and 12000 grams of salt is made available each week. Once produced and sold, the Brie packet returns a £4.50 profit and £3 profit for the Gouda cheese. Required: 1. Enter the information on the table below into an Excel file. You should also show an appropriate formula for “Total profit” and quantity “Used” for each of the “milk” and “salt” resource in the cells as indicated below. Packet Brie Gouda Quantity 0 0 Total Profit Unit profit (£) 4.5 3 Insert formula Used Symbol Max Milk (litres) Insert number Insert number Insert formula 120 Salt (grams) Insert number Insert number Insert formula 12000 2. Find the solution of the linear programming problem (using Excel Solver) 3. State the production of each type of cheese that yield maximum profit.
A small cheese maker specialises in two types of cheese – Brie and Gouda that he makes and sells in packets of 400 grams. The cheese maker has estimated that each packet of Brie uses 1.8 litres of special milk and 100 grams of special salt. For the Gouda cheese packet, the amounts of special milk and salt needed are 1.2 litre and 150 grams respectively. The cheese maker has a limited supply of special milk and salt and a maximum of only 120 litres of milk and 12000 grams of salt is made available each week. Once produced and sold, the Brie packet returns a £4.50 profit and £3 profit for the Gouda cheese.
Required:
1. Enter the information on the table below into an Excel file. You should also show an appropriate formula for “Total profit” and quantity “Used” for each of the “milk” and “salt” resource in the cells as indicated below.
|
Packet |
Brie |
Gouda |
||
|
Quantity |
0 |
0 |
Total Profit |
|
|
Unit profit (£) |
4.5 |
3 |
Insert formula |
|
|
Used |
Symbol |
Max |
||
|
Milk (litres) |
Insert number |
Insert number |
Insert formula |
120 |
|
Salt (grams) |
Insert number |
Insert number |
Insert formula |
12000 |
2. Find the solution of the linear programming problem (using Excel Solver)
3. State the production of each type of cheese that yield maximum profit.
Step by step
Solved in 2 steps with 5 images
