D29: Building a Cost Curve

A producer has three technologies available for making its product: A, B and C. For each technology, it has data on how much capital (K) and labor (L) are needed to produce various amounts of output (Q). In addition, it knows that the price of capital, `P_K`, is $20 and the price of labor, `P_L`, is $5. The management team would like to know the cost-minimizing technology to use for various Qs, and what the average cost would be at each one. It would also like graphs of both total and average costs that illustrate how the technologies compare.

Use the spreadsheet in Teams to build and graph the producer's total cost (TC) and average cost (AC) curves. Here are the steps to carry out:

1. Fill in the PK and PL cells with the prices given above.
2. Click on one of the cells in the Production Options section and use Insert > Table to turn it into a data table.
3. Add appropriate formulas to the TC and AC columns.
4. Create a pivot table for the data. Place it in a new worksheet.
5. Set up the pivot table with Q in the rows, Tech in the columns, and the minimum of TC in the values section.
6. Click on the Design menu and then set Report Layout to tabular form.
7. On the Design menu, set Grand Totals to Rows Only (the column totals are meaningless here).
8. Change the column heading "Grand Total" to "Min TC".
9. Make a copy of the pivot table and paste it in below the table. Leave at least one blank row for clarity.
10. Now select the cells in the copied table (or leave them selected after the paste) and insert a scatter plot with straight lines and markers.
11. Change the graph's title to Total Cost.
12. Go back to the original data table and create a second pivot table on a new worksheet.
13. Set up the new table in the same way as the first one but for AC rather than TC. Change the "Grand Total" heading to "Min AC".
14. Make a copy of the table and create an appropriate graph. Set its title to Average Cost.
15. Use the AC results to fill in the table in the Results section of the original sheet. Copy the minimum AC results from the pivot table and paste them into the results column. Then set the formatting to round the data to two digits (dollars and cents). 
16. Finally, fill in the Tech column to indicate which technology had the minimum cost at that Q.

Save and submit the results!