Theory of Constraints Bottom Line Measurements P & Q Answer Solver

A Guide to Implementing the Theory of Constraints (TOC)
PowerPoints	Preface	Introduction	Site Map	Contents	Next Step

Using Excel Solver To Calculate The P & Q Answer

Solver is Excel’s built-in, or rather, add-in linear programming module. You will find it under “Tools” “Solver.” If it is not there you may have to install it. Check “Tools” “Add-Ins” first, otherwise Excel Help will explain how to install the module.

This is the format we are going to use

	Product	P	Q	P’	Q’
1	Weekly Demand	100	50	100	50
3	Selling Price	90	100	72	80
4	Materials	45	40	45	40
5	Throughput	45	60	27	40
6	Units Supplied	93	50	0	50	Req.	Avail.
7	Resource A	15	10	15	10	2395	2400
8	Resource B, B’	14	29	14	29	4202	4800
9	Resource C	17	7	17	7	2281	2400
10	Resource D	15	5	15	5	1895	2400
11	Weekly Throughput	4185	3000	0	2000
12	Weekly Operating Expense	6400
13	Weekly Net Profit	2785

Let’s give it Excel row and column labels.

	A	B	C	D	E	F	G
1	Product	P	Q	P’	Q’
2	Weekly Demand	100	50	100	50
3	Selling Price	90	100	72	80
4	Materials	45	40	45	40
5	Throughput	45	60	27	40
6	Units Supplied	93	50	0	50	Req.	Avail.
7	Resource A	15	10	15	10	2395	2400
8	Resource B, B’	14	29	14	29	4202	4800
9	Resource C	17	7	17	7	2281	2400
10	Resource D	15	5	15	5	1895	2400
11	Weekly Throughput	4185	3000	0	2000
12	Weekly Operating Expense	6400
13	Weekly Net Profit	2785

We need to first set up some relationships between the cells

(1) Cell E13 is the sum of the weekly throughput less the weekly operating expense.

(2) Cell B11 is the product of B5 and B6, and repeat for cells C11 – E11.

(3) Cell B5 is sum of selling price less materials, B3-B4, and repeat for cells C5 – E5.

(4) Cell F10 is the sum of B10*B6 + C10*C6 + D10*D6 + E10*E6, and repeat for F9 – F7.

Now we can start solver (Tools -> Solver) and set the cells in the solver window.

(1) Set target cell as E13

(2) Equal to max

(3) By Changing cells B6:E6

(4) Subject to:
B6:E6 <= B2:E2
B6:E6 = integer
F7:F10<= G7:G10

Now we can solve the problem.

It’s not as difficult as it first looks. What we are asking solver to do is to maximize the objective function – our weekly net profit, by adjusting the units supplied (in whole numbers) but not exceeding any of the constraints (market and machine time).

In this example the information for resource B, C, and D, is pretty much redundant because we already know where the constraint is. However learning to use solver to work through these types of problems is useful for rapidly developing multiple what-ifs. And that after all is what decision analysis is all about.

To return to the previous page press Alt key + left arrow.