Spreadsheet-based 0ptimization

1
Spreadsheet-based 0ptimization
With modern spreadsheets, optimization is a snap
Objective Execute the optimization of profit
functions using the Excel spreadsheet.
2
Problem Maximizing profits from the sale of
microchips
Recall our inverse demand function for microchips
2.2
P 170 20Q
The Revenue (R) function is given by R P Q
(170 20Q)Q 170Q 20Q2 Thus marginal revenue
(MR) is given by dR/dQ 170 40Q
3
The cost function (C) is given by 2.4 C 100
38Q Thus the marginal cost (MC) function is given
by dC/dQ 38 The profit (?) function is given
by R C 170Q 20Q2 (100 38Q) 132Q
20Q2 100 Thus the marginal profit function (M?)
is given by d ?/dQ 132 40Q
4
Step 1 Set up a spreadsheet like this
5
Step 2 Type the number 2.0 in cell b7
6
Step 3 Move your cursor to cell c7 and type the
following in the formula bar 170-20b7
Hit enter or click right on the green check
mark to the left of the formula bar. Now your
spreadsheet should look like this
7
Step 4 Move your cursor to cell d7 and type the
following in the formula bar b7c7
Hit enter or click right on the green check
mark to the left or the formula bar. Now your
spreadsheet should look like this
8
Step 5 Move your cursor to cell e7 and type the
following in the formula bar 10038b7
Hit enter or click right on the green check
mark to the left or the formula bar. Now your
spreadsheet should look like this
9
Step 6 Move your cursor to cell f7 and type the
following in the formula bar d7-e7
Hit enter or click right on the green check
mark to the left or the formula bar. Now your
spreadsheet should look like this
10
3 ways to maximize profits (?)
Now we will show you 3 methods of maximizing the
profit function using Excel.
11
Method 1 Change the value of the number in cell
b7 until you find the highest corresponding value
in cell f7.
Example Enter the number 3.0 in cell b7.
Notice that profit increases to 116.
12
Method 2 Use MR and MC as guides. Vary the
numerical values in cell b7 until MR MC (or
alternatively, Mprofit 0).
Example Enter the number 3.0 in cell b7.
Notice that profit increases to 116.
13
Method 2 Use MR and MC as guides. Vary the
numerical values in cell b7 until MR MC (or
alternatively, Mprofit 0).
Step 1 Type MR, MC, and Mprofit into cells d12,
e12, and f12 respectively
14

Step 2 To compute MR when quantity is equal to 2
lots, place your cursor in cell d14 and type the
following in the formula bar 170-40b7
Your spreadsheet should look like this
15

Step 3 Note that MC 38, so type this into cell
e14.To compute marginal profit (Mprofit) move
your cursor to cell f14 and type the following in
the formula bar 132-40b7
Your spreadsheet should now look like this
16

Step 4 Now adjust the numerical values in cell
b7 until MR MC, or Mprofit 0.
Example Type 3.0 in cell b7. Your spreadsheet
should now look like this
17
Method 3 Use the Excel solver function

1. Move your cursor to cell f7.
2. From the tools menu select solver. You should
   see a dialog box like this

Solver Parameters
Solve
Set Target Cells
F7
Equal to
Max
Min
Close
By Changing Cells
Options
Subject to Constraints
Add
Change
Delete
18

1. Notice that the default is Maxthats OK we
   are trying a maximize a profit function.
2. In the By Changing Cells space type B7.
   Remember we are seeking to find the profit
   maximizing output-price combination.
3. Now click on the solve button.

Solver Parameters
Solve
Set Target Cells
F7
Equal to
Max
Min
Close
By Changing Cells
B7
Options
Subject to Constraints
Add
Change
Delete
19

The solver function found the profit maximizing
output (3.3 lots) and price (104,000 per lot).
20
Constrained optimization
Suppose we are seeking to maximize profits
subject to the constraint that our price per lot
cannot exceed 91,000that isP ? 91
21

1. Move your cursor to cell f7 and access the
   solver dialog box from the tools menu.
2. Now click on the add button and you will find a
   dialog box (something) like this
3. Type c7 into Cell Reference space and 91 into
   constraint space. Now click on OK

Add Constraint
Constraint
Cell Reference
c7
lt
91
OK
Cancel
Add
Help
Note lt is the default, which works in our case.
22

The solver function found the output (4.0 lots)
that maximizing profits subject to the price
constraint.