Over the past 12 months the Four Winds Novelty Company firm has recorded its internet sales (equals monthly output levels) and its monthly total variable costs (TVC) for a particular novelty item as shown in the following table. Sales have grown over this period with relatively few shocks due to uncontrollable weather, political and sporting events. This online retailer carries no inventories; when it receives a pre-paid on-line order from a customer, it simply buys the product from a supplier and ships it out to the customer.
Sales = Output TVC ($)
a) Using regression analysis, find an equation that best fits the data to represent the TVC function. (I was able to duplicate the example in the book for the cubic equation, but not the quadratic or linear equations -- I must be doing something wrong in the excel regression analysis, but I don't know what). I also do not completely understand how to interpret the output of the analysis to determine best fit.
b) At what sales/output level will marginal costs (MC) reach a minimum?
c) Estimate the value of TVC for sales/output level 250,000 units, and calculate the 95% confidence interval for your estimate
Please open the attached Excel file and then follow the explanations below:
Data in worksheet Data and regression in worksheet Regression. The attached regression was run using the Data Analysis add-in in the Data tab. (If you are using some other tool, you would have to adjust accordingly.) Choose regression, then variables and the confidence level. Then input the range of cells where the data is located. Make sure that ...
This solution shows a regression output on sales and variable costs for Four Winds Novelty Company. The problem includes data analysis based on the monthly internet sales and total variable costs. The regression analysis has been done with the Excel Data Analysis tool, with explanations on how to interpret the output data. The solution also shows how to find the minimum level of marginal cost (MC) and to predict a 95% confidence level of sales on a particular level of variable costs using the regression equation.