To make a spreadsheet for total cost, put quantities in ...



15. Costs in Excel

Problems

1. Make an excel spreadsheet showing the total cost to calculate the fixed cost function, the variable cost function, the average variable cost function, the average cost function, and the marginal cost function.

2. Use the excel spreadsheet to create schedules for total cost, fixed cost, variable cost, average variable cost, average cost, and marginal cost. Make the spreadsheet so that the initial quantity and the increment by which quantity increases can be easily changed.

3. Use the excel spreadsheet to calculate the average cost minimizing level of output.

The Cost Function

First, type in the cost function.

TC = 5,000,000 + .2Q2

Each number should be in its own cell. The other symbols can be placed in the same sell. For example, in A1, you would put TC = , but in B1 you would put 5,000,000, then in C1 you would put ‘+ and in D1 you put .2 and finally in E1 you put in Q^2.

| |A |B |C |D |E |

|1 |TC = |5,000,000 |‘+ |.2 |Q^2 |

Next, put in the fixed cost function. Put FC = in A2. And then type in = and click on the 5,000,000 in B2.

| |A |B |C |D |E |

|2 |FC = |=B1 | | | |

Then put in the variable cost function. Put VC = in A3. In B3 type = and click on the .2 in D1. Then in C3 type in Q^2

| |A |B |C |D |E |

|3 |VC = |=D1 |Q^2 | | |

After that, let’s put in the average variable cost function. Type in AVC = in A4. In B4, type equal and click on the .2 in B3. Type in Q in C4.

| |A |B |C |D |E |

|4 |AVC = |=B3 |Q | | |

Then put in the Average cost function. Type in AC = in A5. In B5, click on the 5,000,000 in B1. In C5, but Q^-1 +. In D5, put in =B4. And then in E5, type in Q.

| |A |B |C |D |E |

|5 |AC = |=B1 |Q^-1 + |=B4 |Q |

Finally, put in the marginal cost function. Type in MC = in A6. In B6, type in = 2* and then click on the .2 in B4. In C6 type in Q.

| |A |B |C |D |E |

|6 |MC = |=2*B4 |Q | | |

Next, lets use the spreadsheet to find in the average cost minimizing level of output. In A7, write average cost minimizing output. In A8, type in Q=. In B8 type in =(B5/(B6-D5))^.5

| |A |B | |D |E |F |G |

|8 |Q= |=(B5/(B6-D5))^.5 | | | | | |

In the columns C8, D8 and the like, show the total cost, average cost, and marginal cost resulting from the level of output in B8.

In A9, put the column heading Q and then in A10, type in = and then click on the revenue spreadsheet and on that sheet click on the very first level of output. That will be the cell right below the Q. Hit enter.

Copy and past for 50 cells. This should give you a column of quantities identical to those on the revenue sheet.

In B9, type in the column heading for total cost, TC. In B10, put in =B$1+C$1*A10^2. In C 9, put in the heading for fixed cost, FC. And then in C10, put in =B$2. In D9, type in VC for variable cost. In D10 put in =B$3*A10^2. In E9, type in AVC for average variable cost. In E10, put in B$4*A10. In F9 put in AC for average cost. In F10, put in =B10/A10. Finally, in G9, type in MC for marginal cost. And in G10, put in = B$6*A10.

It all should look roughly as below, but watch out. This assumes that the first quantity in your revenue sheet was A14.

| |A |B |C |D |E |F |G |

| |Q |TC |FC |VC |AVC |AC |MC |

|1 |=Sheet1!A14 |=B$1+C$1*A10^2 |=B$2 |=B$3*A10^2 |B$4*A10 |=B10/A10 |B$6*A10 |

Graphing

To graph the cost schedule and show the firms total cost curve, you select the Q using your mouse, hold down the left mouse button and drag over three column to the right and then down 50 cells.

Push the button for chart wizard. Choose a scatter diagram with lines. Be sure to put the graph on its own page.

The same procedure is used for quantity, average variable cost, average cost, and marginal cost, but this time push control so that you can skip fixed cost, variable cost, and total cost.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download