In this problem, you will investigate the cost functions. You will do this by calculating the values of these functions for different production quantities and by creating graphs for these curves.

Grader – Instructions Excel 2016 Project

# Chapter 4 Cost Problem (v2)

## Project Description:

In this problem, you will investigate the cost functions. You will do this by calculating the values of these functions for different production quantities and by creating graphs for these curves.

## Steps to Perform:

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. | 0 |

Total Points | 13 |

Created On: 07/05/2019 1 Chapter 4 Cost Problem (v2)

Problem

Cost Problem

Sugar Plum Oak is an Amish, handmade, furniture production company based in Norfolk, NE. One of their signature items is handmade rocking chairs. Suppose you are analyzing weekly cost data for handmade rocking chairs at a firm like Sugar Plum Oak.

a) Using the Total Cost schedule provided, calculate total and average fixed cost and variable cost, average total cost, and marginal cost.

Q Total Cost TFC TVC ATC AFC AVC MC

0 $525.00

1 $552.50

2 $585.00

3 $622.50

4 $665.00

5 $712.50

6 $765.00

7 $822.50

8 $885.00

9 $952.50

10 $1,025.00

11 $1,102.50

12 $1,185.00

13 $1,272.50

14 $1,365.00

15 $1,462.50

16 $1,565.00

17 $1,672.50

18 $1,785.00

19 $1,902.50

20 $2,025.00

b) Use Excel to graph ATC, AVC, and MC.

c) What does the space between ATC and AVC in the graph represent?

The space between ATC and AVC in the graph represents

d) Using the same graph, at how many units does MC intersect the ATC curve?

Q = units

e) Is ATC maximized or minimized at this point?

ATC is at this point.

Instructions

Project Description: In this problem, you will investigate the cost functions. You will do this by calculating the values of these functions for different production quantities and by creating graphs for these curves.

For the purpose of grading the project you are required to perform the following tasks:

Step Instructions Points Possible

1 Start Excel. 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 ะก8 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. 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. 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. 0