SINGLE-INPUT PRODUCTION MODEL in EXCEL
 

1. PRODUCTION FUNCTION

This exercise provides a review of basic production theory (you may want to refer
to a basic microeconomics text, and perhaps check your graphs against those in the
book), and it provides good visualization of how input and output price changes
influence profit-maximizing production.

Open a blank MS-Excel worksheet and at the top of the worksheet type in the
following: production input price W(X)=$7.50; output price P(Y)=$4.31; and fixed
cost FC=$100. (Just type in the numbers, then format them to dollars.)

In column A type in integer input X quantities from 0 to 24.
In column B type in a formula for output Y quantities using the cubic production
function

Y = X^2(35-X)/100

In Excel, this would be expressed as a formula using cell references to the X
values; in cell B5, for example, you would enter (without the quotes)

"=A5^2*(35-A5)/100"

You should be able to simply copy this formula down column B.
With a little formatting of cells the top of your sheet should look like this:

  |   A   |   B   |   C
  ------------------------
1 |P(X)=   P(Y)=   FC=
2 |  $7.50   $4.31 $100.00
3 |
4 |      X       Y     MPP
5 |      0       0
6 |      1     0.3
7 |      2     1.3
8 |      3     2.9

Create and print off an XY graph of this production function, Y versus X (with
X on the horizontal axis).
 

2. INPUT SPACE

Now add the following column headings, and create formulas to calculate table
values for:

Column C: Marginal Physical Product (MPP)    e.g., "=(B7-B6)/(A7-A6)"
Column D: Average Physical Product (APP)     e.g., "=B7/A7"
Column E: Marginal Value Product (MVP)       e.g., "=B$2*C7"
Column F: Average Value Product (AVP)        e.g., "=B$2*D7"
Column G: Marginal Factor Cost (MFC)         e.g., "=A$2" (it's constant)

NOTE: The "$" symbol in a formula makes the row reference absolute rather than
relative.  If you copy a formula like "=$A$4" anywhere in the worksheet, every
cell with the formula refers to the same (absolute) cell location A4.
Without the "$" every cell with the formula refers to a relative cell location
(e.g., 4 columns to the left and 2 rows up).  So you can make row, column, or
both row and column references be absolute or relative.

Create an XY graph of MPP and APP versus X.  (In Excel's "Series" box Remove
the Y series so you just have MPP and APP).  Print off the graph and mark the
three stages of production (I, II and III) with a pencil or pen.
Does MPP intersect APP at maximum APP?

Create an XY graph of MVP, AVP and MFC.  (Remove the Y, MPP and APP series
that you have already graphed.)  At what quantity of X does MVP equal MFC?

At this level of X where MVP=MFC, what is the average "factor rent" per unit
of X (= AVP-MFC).  What is the total factor rent (= X times average factor
rent).
 

3. OUTPUT SPACE: TOTAL COSTS and REVENUES

Now add the following column headings, and create formulas to calculate table
values for:

Column H: Variable Cost (VC = P(X)*X)
Column I: Fixed Cost (FC = 100)
Column J: Total Cost (TC = VC + FC)
Column K: Total Revenue (TR = P(Y)*Y)
Column L: Profit (= TR - TC)

Create an XY graph of VC, FC, TC, TR and Profit versus output Y (remove the
intervening series you have already graphed).

At what X and Y values is profit maximized?  Is this consistent with the
values where MVP = MFC?

What is the maximum profit?  What is the difference between this profit and
the total factor rent you calculated above?  Explain.
 

4. OUTPUT SPACE: AVERAGE and MARGINAL COSTS and REVENUES

Now add the following column headings, and create formulas to calculate table
values for:

Column M: Average Variable Cost (AVC = VC/Y)
Column N: Average Total Cost (ATC = TC/Y)
Column O: Marginal Cost (MC)  e.g., "=(J6-J5)/(B6-B5)"
       (omit the negative value in the last row)
Column P: Marginal Revenue (MR)
Column Q: Average Profit  (= Profit/Y)
Column R: Marginal Profit   (omit the value in the last row)

Create an XY graph of AVC, ATC, MC, MR, AvProfit and MProfit versus output Y
(removing all the intervening series you have already graphed).
To zoom the graph to the area of interest, Format the Y-axis to a minimum of
$0 and a maximum of $5.  (Click on the axis to select it, then right-click,
choose "Format Axis" and reset the scale.

At what level of output does MR=MC?  Is this consistent with the other profit-
maximization points you determined previously?

If P(X)=$7.50, what is the break-even (zero-profit) P(Y)?
At what level of P(Y) is it more profitable for this firm to simply shut
down production?  [The shut-down P(Y) is where MR = minimum AVC.]
 

5. CHANGING INPUT and OUTPUT PRICES

If you used absolute cell references to the input and output prices in all of
your formulas, you can experiment with this model:

What happens in each of the graphs as you vary P(X)?  Which schedules shift,
and how?

What happens in each graph as you vary P(Y)?  Which schedules shift, and how?

For even-dollar P(X) values between $3 and $10, determine the break-even
(mininum ATC) values of Y and P(Y), and shut-down (minimum AVC) values of
Y and P(Y).

Create an XY graph of these P(Y)'s versus P(X).

Okay, that's enough!  Make sure your graphs are nice and pretty, do a short
write-up including answers to questions.