Question

Problem:  Using Solver, solve the linear program to find the optimal number of batches to make of each of the three cookies.

Price per chocolate chip cookie		$                      1.50
Price per sugar cookie		$                      1.00
Price per snickerdoodle cookie		$                      1.00
Recipes for one batch
Number of cookies/batch	20	20	30
Ingredient	Chocolate chip cookie recipe	Sugar cookie recipe	Snickdoodle recipe
Butter (sticks)	2	2	2
Sugar (cups)	1	2	1
Eggs	2	3	1
Chocolate chips (cups)	1	0	0
Flour (cups)	2	2	2
Vanilla extract (teaspoons)	0	0	0.75
Baking powder (teaspoons)	0	0	1
Ingredients available:
6 sticks of butter
5 cups of sugar
12 eggs
2 cups of chocolate chips
10 cups of flour
One eight ounce container of baking powder
One one ounce container of vanilla extract
Note: One ounce = 6 teaspoons
Using Excel, build a spreadsheet model of the problem (you can modify the one done in class if you'd like).
Using Solver, solve the linear program to find the optimal number of batches to make of each of the three cookies.

Answer 1

Total revenue of the product mix is determined as follows:

Revenue per batch = (no. of cookies/batch) x (price per cookie)

Total revenue = Sum of (No. of batches of cookies x revenue per batch)

The excel model is as followed:

Solver solution is as follows:

Optimal Solution:

Product	Chocolate chip cookie recipe	Sugar cookie recipe	Snickdoodle recipe
No. of batches	2	0	1

Total revenue = $90