Chapter 4 Cost Problem (v2)

 

Step

Instructions

Points Possible

1

Start Excel. Download and open the workbook named: Chapter_4_Cost_Problem_Start.

0

2

In cell E7, by using an absolute cell reference, enter the total fixed cost. Refer to an appropriate cell among D7-D27. Copy the formula from cell E7 down the column to cell E27.

1

3

In cell F7, by using cell references, calculate the total variable cost. Use cells D7 and E7. Copy the formula from cell F7 down the column to cell F27.

1

4

In cell G8, by using cell references, calculate the average total cost. Use cells C8 and D8. Copy the formula from cell G8 down the column to cell G27.

1

5

In cell H8, by using cell references, calculate the average fixed cost. Use cells C8 and E8. Copy the formula from cell H8 down the column to cell H27.

1

6

In cell I8, by using cell references, calculate the average variable cost. Use cells C8 and F8. Copy the formula from cell I8 down the column to cell I27.

1

7

In cell J8, by using cell references, calculate the marginal cost. Use cells D7 and D8. Copy the formula from cell J8 down the column to cell J27.

1

8

In cells C29-I40, insert a Scatter Chart for the Average Total Costs, Average Variable Costs and Marginal Costs versus Quantity. Inserting a Chart Select the Scatter with the Straight Line chart from the provided chart options in the Charts group of the Insert tab of the Ribbon. Selecting Data Series Then choose Select Data in the Design tab on the Ribbon. Delete any series created automatically using the Remove button and add new series using the Add button. Select the range of data on the Problem worksheet for the ATC curve. Note that Quantity should stand for the X values and Average Total Costs for the Y values. Use cells C8-C27 for the X values and cells G8-G27 for the Y values. Use cell G6 as the series name. Add new series for the AVC curve. Note that Quantity should stand for the X values and Average Variable Costs for the Y values. Use cells C8-C27 for the X values and cells I8-I27 for the Y values. Use cell I6 as the series name. Add new series for the MC curve. Use cells C8-C27 for the X values and cells J8-J27 for the Y values. Use cell J6 as the series name. Edit Chart Elements On the Ribbon, select design Style 1. Go to the Add Chart Elements dropdown list in the Design tab of the Ribbon. Add the legend and choose the Bottom option. Add Quantity as the title for the horizontal axis. Add Costs as the title for the vertical axis. Chart Size and Position Go to the Format tab on the Ribbon. Set the chart height to 3 inches and the chart width to 6.2 inches. Drag the chart to position the entire chart so that it fits within cell C29-I40.

4

9

In cell H42 determine what the space between ATC and AVC in the graph represents.

1

10

In cell D44, by using cell references, enter the quantity at which MC intersects the ATC curve. Use an appropriate cell among C7-C27.

1

11

In cell D46, determine whether ATC is maximized or minimized at the point of intersection of the MC and ATC curves.

1

12

Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.