ABC Machinery produces ice machine with historical (monthly) data in the Excel file. Please construct its short-run total cost function to answer the following questions:
QUESTIONS
1. Given the data, please construct a Short-Run total cost (TC) function (cubic function) by Excel regression for ABC Machinery. (20%)
2. Given your answer in 1, please indicate how much the fixed cost (FC) is and what the marginal cost (MC) function is. (20%)
3. Please comment on whether the estimated function in question 1 is a good fit or not. What is the interpretation of coefficient of determination (R-square)? Does the regression show overall significance at 5%? (20%)
4. How much will be the total costs if ABC Machinery produces 200 ice machines in a month? Please show your calculation. (20%)
5. Assume that the marginal revenue (MR) function (at unit of $1,000) of ABC ice machine is
MR = 30 − 0.1Q
In order to maximize the monthly profit, what will be the optimal output level? Please show your calculation. (20%)
(Hint: Make MR=MC then apply the quadratic formula to solve the equation.)
Data
ABC Machinery
Total Cost ($1,000) Output Rate (Q)
2251 121
1866 62
2310 153
2407 187
2109 87
2523 204
1726 43
2201 110
2170 92
1812 54
2736 233
2290 141
1910 66
2894 271
2277 139
To construct the short-run total cost (TC) function for ABC Machinery, we can use Excel regression analysis. Here's how to do it:
1. Open the Excel file that contains the historical data for Total Cost and Output Rate.
2. Select the Total Cost and Output Rate data.
3. Go to the "Data" tab and click on "Data Analysis" (Note: If you don't see the "Data Analysis" option, you may need to enable it by going to "File" > "Options" > "Add-Ins" > "Excel Add-Ins" and then check "Analysis ToolPak" and click "OK").
4. In the "Data Analysis" dialog box, select "Regression" and click "OK."
5. In the "Regression" dialog box, enter the Input Y Range as the Total Cost data and the Input X Range as the Output Rate data. Check the "Labels" box if your data has labels, and choose an output location for the regression results.
6. Check the "Confidence Level" box and enter 95% (or any other desired level).
7. Click "OK" to perform the regression analysis.
8. The regression results will include coefficients for the cubic function equation, including the intercept, coefficients for the output rate and its powers.
Now let's answer the specific questions based on the regression results:
1. To construct a cubic function for the short-run total cost (TC), you need to examine the coefficients given by the regression analysis. The equation will have the form TC = a + bQ + cQ^2 + dQ^3, where a, b, c, and d are the coefficients obtained from the regression analysis.
2. From the regression results, the fixed cost (FC) is represented by the coefficient 'a'. The marginal cost (MC) function can be obtained by taking the derivative of the total cost function with respect to output rate (Q). The MC function will be MC = b + 2cQ + 3dQ^2.
3. To evaluate the goodness of fit, you need to look at the coefficient of determination (R-square) provided in the regression results. R-square measures the proportion of the variation in the total cost that is explained by the independent variable (output rate). A higher R-square indicates a better fit. Additionally, you can also check if the regression model is statistically significant at a 5% level by examining the p-values or t-values for the coefficients. If they are less than 0.05, the regression model is considered significant.
4. To calculate the total costs when ABC Machinery produces 200 ice machines in a month, substitute the value of Q=200 into the short-run total cost function derived in question 1.
5. To find the optimal output level for maximizing monthly profit, you need to equate the marginal revenue (MR) function to the marginal cost (MC) function. Solve the resulting equation for Q, using the quadratic formula.
By following these steps, you can construct the total cost function and derive the fixed cost, marginal cost, evaluate goodness of fit, calculate total costs at a specific level of output, and determine the optimal output level for ABC Machinery.