1 / 22

Spreadsheet-based 0ptimization

Spreadsheet-based 0ptimization. With modern spreadsheets, optimization is a snap. Objective: Execute the optimization of profit functions using the Excel spreadsheet. Problem : Maximizing profits from the sale of microchips. Our inverse demand function for microchips [ 1 ] :.

abeni
Download Presentation

Spreadsheet-based 0ptimization

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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 Our inverse demand function for microchips [1]: 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] 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-20*b7 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: =b7*c7 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: =100+38*b7 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-40*b7 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-40*b7 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 • Move your cursor to cell f7. • From the “tools” menu select “solver”. You should see a dialog box like this: Solver Parameters Set Target Cells $F$7 Solve Equal to Max Min Close By Changing Cells: Options Subject to Constraints: Add Change Delete

  18. Notice that the default is “Max”—that’s OK– we are trying a maximize a profit function. • In the “By Changing Cells” space type: $B$7. Remember we are seeking to find the profit maximizing output-price combination. • Now click on the “solve” button. Solver Parameters Set Target Cells $F$7 Solve Equal to Max Min Close By Changing Cells: $B$7 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,000—that is:P  91

  21. Move your cursor to cell f7 and access the “solver” dialog box from the tools menu. • Now click on the add button and you will find a dialog box (something) like this: • Type c7 into Cell Reference space and 91 into constraint space. Now click on OK Add Constraint Constraint: Cell Reference: c7 <= 91 OK Cancel Add Help Note: <= 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.

More Related