need to do this assessment in excel. Please advise

Redstone Clayworks, Inc., a small firm located in Sedona, manufactures clay fire pits

that homeowners place on their patios for decoration and light-duty heating. Red-

stone is one of 53 firms worldwide supplying clay fire pits to retailers such as Home

Depot, Lowe's, Front Gate, and other upscale home product chains. Despite the best

marketing efforts of these retailers to differentiate their individual brands of fire pits,

consumers don't care much about brands: A clay fire pit is a clay fire pit, regardless of who sells it. The following spreadsheet below provides data on Redstone's costs of production.

(1) (2) (3) (4)

Q TC TFC TVC

0 5,000 5,000 0

100 10,000 5,000 5,000

200 19,000 5,000 14,000

300 27,000 5,000 22,000

400 38,000 5,000 33,000

500 50,000 5,000 45,000

600 66,000 5,000 61,000

700 84,000 5,000 79,000

800 104,000 5,000 99,000

900 126,000 5,000 121,000

1,000 150,000 5,000 145,000

a. Create a spreadsheet using Microsoft Excel (or any other spreadsheet software) that

matches the one above by entering the output and cost data given above. Then use

the appropriate formulas to create four new columns in your spreadsheet for aver-

age fixed cost ( AFC ), average variable cost ( AVC ), average total cost ( ATC ), and

short-run marginal cost ( SMC ). [Computation check: At Q 5 400, SMC 5 $110].

The world demand and supply curves for clay fire pits intersect at $190 per unit.

b. Use the appropriate formulas to create two more columns in your spreadsheet for

total revenue ( TR ) and marginal revenue ( MR ). [Computation check: At Q 5 400:

MR 5 $190].

c. Use the appropriate formulas to create three more columns in your spreadsheet for

profit ( PROF ), average profit ( AVGPROF ), and profit margin ( PROFMARG ). [Com-

putation check: At Q 5 400: AVGPROF 5 $95].

d. If Redstone's manager wishes to minimize average total cost, how many clay fire

pits should be produced? How many to maximize profit margin?

e. Redstone's manager is fired, and you are now the manager of Redstone Clayworks.

How many fire pits would you choose to produce? Why?

f. Now triple the total fixed costs to $15,000 in your spreadsheet. How does this

change your production decision in part e ? Explain briefly.

g. Suppose a congressional environmental panel announces that greenhouse gases

from clay fire pits are contributing significantly to global warming. The announce-

ment causes worldwide demand for fire pits to shrink substantially, and clay fire pit

prices fall to $65 per unit. How does this change your production decision in part e ?