|
Excel and Solver For simple examples the analytical approach is very helpful. But in practical problems it is necessary to solve problems numerically. Suppose that the firm is selling two products. They are substitutes so raising the price of one product lowers the demand for that product and raises demand for the other product. That is, some consumers exit and other consumers switch. The demand price functions are as follows:
If the two commodities, then the market clearing price is a decreasing function of both quantities. If the firm wishes to increase its sales of commodity 1 it must lower its price of this commodity so is negative. The lower price attracts some of the people originally purchasing commodity 2. Then demand for commodity 2 falls and so the firm must lower the price of commodity 2 as well. Therefore is also negative. An identical argument hold if the firm wishes to sell more of commodity 2. The marginal cost of producing commodity is a quadratic function of . Total cost is therefore a cubic function.
Profit is then
This has no analytical solution unless c13 = c23 =0. However for any given set of parameter values it can be solved very easily indeed using Excel. First enter all the data for the problem. It is helpful to color code cells. Below the data cells are yellow.
The changing variables are the two quantities. The cell array E8:C9 contain the formulas for the two prices. Look on the formula bar. Cell B4 is the intercept parameter and cells C4 and D4 are the slope parameters. Thus cell E8 is Total revenue is the product of terms in the array B8:B9 and the array E8:E9. Of course we can write out the formula for revenue but Excel provides a shortcut. The “sumproduct” of two arrays is the sum of the term-by-term products of the cells in the two arrays. If you look at the formula bar below you will see how to type this. Or you can type =sumproduct( then drag the cursor over the array then a comma, then drag the cursor over the second array, and finally add the ). Below a similar short cut is taken to compute total cost. The array D14:F15 contains the linear quadratic and cubic of the two quantities. The sum product of this array and the data array E4:G5 is the variable cost of the firm. Total cost is therefore =SUMPRODUCT(E4:G5,D14:F15)+H4
The objective is to maximize profit = TR – TC. I always color code a cell which is the objective with a red border. To solve this problem numerically you will use the Add-In Solver. Click on the DATA Tab. Note that on the Data Analysis tab depicted below, one option is Solver.
Solver is an “Add-In” so if you have not used Solver before you need to add it in to use it. Click on the FILE tab at the top left and select Options (bottom left).
Then click on Add-Ins in the left menu. You will see the drop down menu shown above. Click on Go…. At the bottom. You need to make sure that the Solver Add-In is checked as shown below.
Then click on OK. Now you are ready to solve for the profit-maximizing outputs. From the DATA tab click on Solver. The following menu will appear.
The objective is to maximize profit. So the objective is cell G12 and Max must be checked. The variables are the quantities so the changing variable cells are in the array B8:B9. These variables are quantities so they must be non-negative therefore the appropriate box should be checked. Now click on Solve. With a bit of luck you have set the problem up correctly and the problem will be solved. Solver does the
marginal analysis for you. The program starts with some increments
Exercise: (a) Solve the problem for the data in the yellow cells above. (b) Suppose that the parameters are as follows:
Show that the new profit-maximizing prices are 110 and 95. (c) Suppose that marginal cost rises by 2 in each period. Show that the profit-maximizing price rises by 1 in each period. (d) Provide the intuition. HINT: Show that this is true with independent demands.
|
|
|
