The spreadsheet below gives some of Redstone’s production cost data. Enter this data in your own Excel spreadsheet. (Remember our discussion of Smith Farms in the Attend session!) Add columns 5, 6, 7, and 8 to show, respectively, average fixed cost (AFC), average variable cost (AVC), average total cost (ATC), and short-run marginal cost (SMC). You will send me the spreadsheet via the Drop Box. Don’t send it yet, however, because you will need to add more columns, as explained below.
To enter the data into an Excel spreadsheet, follow these steps:
1. Open Microsoft Excel.
2. Create a new spreadsheet by clicking on "File" > "New" > "Blank Workbook".
3. In the first row of the spreadsheet, enter the following column headers:
- Column 1: Quantity
- Column 2: Total Cost
- Column 3: Fixed Cost
- Column 4: Variable Cost
- Column 5: Average Fixed Cost (AFC)
- Column 6: Average Variable Cost (AVC)
- Column 7: Average Total Cost (ATC)
- Column 8: Short-run Marginal Cost (SMC)
4. Starting from the second row, enter the data for each column based on the Redstone's production cost data.
5. Calculate the values for AFC, AVC, ATC, and SMC using the following formulas:
- Average Fixed Cost (AFC) = Fixed Cost / Quantity
- Average Variable Cost (AVC) = Variable Cost / Quantity
- Average Total Cost (ATC) = Total Cost / Quantity
- Short-run Marginal Cost (SMC) = Change in Total Cost / Change in Quantity
6. Apply the formulas to the respective cells in columns 5, 6, 7, and 8 for each row.
7. Save the spreadsheet in a location of your choice by clicking on "File" > "Save As" and choose your desired file name and location.
Once you have added more columns to the spreadsheet, you can send it via a Drop Box service or any file-sharing method you prefer.
To enter the production cost data in your own Excel spreadsheet, follow these steps:
1. Open Microsoft Excel on your computer.
2. Create a new blank spreadsheet.
3. Label the columns as follows:
- Column 1: Quantity
- Column 2: Total Fixed Cost
- Column 3: Total Variable Cost
- Column 4: Total Cost
4. Enter the provided production cost data into the corresponding cells in each column, starting from row 2. For example, the quantity values would be entered in column 1, starting from cell A2.
- Column 1: Quantity
- Row 2: 0
- Row 3: 1
- Row 4: 2
- ...
- Column 2: Total Fixed Cost
- Row 2: $100
- Row 3: $100
- Row 4: $100
- ...
- Column 3: Total Variable Cost
- Row 2: $0
- Row 3: $50
- Row 4: $90
- ...
- Column 4: Total Cost
- Row 2: $100 (Total Fixed Cost + Total Variable Cost)
- Row 3: $150
- Row 4: $190
- ...
5. To calculate the average fixed cost (AFC), average variable cost (AVC), average total cost (ATC), and short-run marginal cost (SMC), you will need to add additional columns. Label these columns as follows:
- Column 5: Average Fixed Cost (AFC)
- Column 6: Average Variable Cost (AVC)
- Column 7: Average Total Cost (ATC)
- Column 8: Short-Run Marginal Cost (SMC)
6. In the first row of each new column, enter the formulas to calculate the respective cost measures. For example, to calculate AFC:
- In cell E2 (Average Fixed Cost):
- Enter the formula "=B2/A2" (Total Fixed Cost divided by Quantity), and press Enter.
- This formula calculates the average fixed cost for the given quantity.
7. Copy the formula from the first row and paste it into the remaining rows in column 5 (AFC), column 6 (AVC), column 7 (ATC), and column 8 (SMC) to calculate the respective cost measures for all data points.
8. Once you have completed these steps, save the Excel spreadsheet on your computer.
Remember to add any additional columns that are discussed later in the instructions before sending the spreadsheet via the Drop Box.