Question

In: Statistics and Probability

A company would like to estimate its total cost equation using customer records.  The company has randomly...

A company would like to estimate its total cost equation using customer records.  The company has randomly sampled 28 customer records. Each customer record contains a Customer #, the Order Size, and the Total Cost of the Order.  The analyst remembers from accounting and economics classes taken in college that

TOTAL COST = Fixed Costs + Variable Cost per Unit *Order Size.

The analysis sees that this is a linear relationship where the TOTAL COST depends on the Fixed Costs, which do not depend on order size, and a variable cost per unit, which is multiplied by the Order Size.  The analysis decides to use simple linear regression to estimate the firm’s Total Cost function.  Use the data file, Estimating a Total Cost Regression Model.xlsx to answer the following questions:

  1. What is the dependent variable in this analysis? What is the independent variable in this analysis?
  2. Use excel to estimate the regression model.  State the estimated total cost function.
  3. What is the estimated Fixed Cost for the Company? Remember the fixed costs is independent of output. You can estimate it as the Total Cost when output is “0”. (Look at the regression output produced by Excel for part b.)
  4. What is the estimated average unit variable cost for the Company? (Look at the regression output produced by Excel for part b.)
Customer # Order Size (Quantity) Total Cost of Order
10211 28 1631
10212 31 1923
10213 43 2070
10214 47 2392
10215 32 1886
10216 43 2307
10217 25 1486
10218 46 2448
10219 41 2210
10220 48 2401
10221 29 1860
10222 32 1786
10223 49 2485
10224 44 2203
10225 33 1855
10226 46 2380
10227 42 2102
10228 31 1683
10229 30 1706
10230 35 1955
10231 34 1992
10232 33 1926
10233 27 1852
10234 32 1807
10235 31 1880
10236 42 2134
10237 39 1979
10238 36 1882

Solutions

Expert Solution

a.) Looking at the equation

Total Cost = Fixed Costs + Variable Cost per unit*Order Size.

We can infer that Total Cost is the dependent variable and Order Size is the independent variable, As order size changes, the total cost also changes.

b.) Using Excel, we get the cost function as follows:

y = 34.891x + 725.64

where y stands for Total Cost function and x stands for the size of the order.

Refer below image for clarification:

Procedure for getting the equation:

1.) Put the data in excel

2.) Select two columns order size and total cost

3.) Insert Scatter Plot

4.) Then click on format and choose more trendline options under the trendline tab

5.) Choose the linear regression type and select the display equation on the chart and you will be able to see the equation on the chart as in the above figure

c.) Estimated Fixed Cost for the company as per below equation

y = 34.891x + 725.64

Fixed Cost = $ 725.64

d.) Estimated Variable Cost for the Company is $34.891 / unit.


Related Solutions

A company would like to estimate its total cost equation using customer records.  The company has randomly...
A company would like to estimate its total cost equation using customer records.  The company has randomly sampled 28 customer records. Each customer record contains a Customer #, the Order Size, and the Total Cost of the Order.  The analyst remembers from accounting and economics classes taken in college that TOTAL COST = Fixed Costs + Variable Cost per Unit *Order Size. The analysis sees that this is a linear relationship where the TOTAL COST depends on the Fixed Costs, which do...
A company would like to estimate its total cost equation. It has collected 48 months of...
A company would like to estimate its total cost equation. It has collected 48 months of monthly production output and corresponding total production costs. The collected data is in the file Production Cost Data Only.xlsx. Recall that TOTAL COST = Fixed Costs + Variable Cost per Unit *Output. Use the data to estimate a function that describes total cost for this company. (Round answers to 2 decimal places) State the estimated total cost function. What is the estimated Fixed Cost...
A company would like to estimate its total cost equation. It has collected 48 months of...
A company would like to estimate its total cost equation. It has collected 48 months of monthly production output and corresponding total production costs.  The collected data is in the file Production Cost Data Only.xlsx. Recall that TOTAL COST = Fixed Costs + Variable Cost per Unit *Output. Use the data to estimate a function that describes total cost for this company. (Round answers to 2 decimal places) Develop a scatterplot of the two variables: monthly output and monthly total costs....
Propose a regression equation you would like to estimate. Write out the equation and define the...
Propose a regression equation you would like to estimate. Write out the equation and define the dependent and independent variables, and indicate how you would measure them. What type of data would you use and, as a result, what is an observation in your dataset? Finally, why would you want to estimate this regression – what would be the purpose/insight?
A cruise company would like to estimate the average beer consumption to plan its beer inventory...
A cruise company would like to estimate the average beer consumption to plan its beer inventory levels on future​ seven-day cruises.​ (The ship certainly​ doesn't want to run out of beer in the middle of the​ ocean!) The average beer consumption over 19 randomly selected​ seven-day cruises was 81,788 bottles with a sample standard deviation of 4,524 bottles. Construct a 90% confidence interval to estimate the average beer consumption per cruise (upper and lower limit)
A cruise company would like to estimate the average beer consumption to plan its beer inventory...
A cruise company would like to estimate the average beer consumption to plan its beer inventory levels on future​ seven-day cruises.​ (The ship certainly​ doesn't want to run out of beer in the middle of the​ ocean!) The average beer consumption over 23 randomly selected​ seven-day cruises was 81,648 bottles with a sample standard deviation of 4,596 bottles. Complete parts a and b below. a. Construct a 95​% confidence interval to estimate the average beer consumption per cruise. The 95​%...
A cruise company would like to estimate the average beer consumption to plan its beer inventory...
A cruise company would like to estimate the average beer consumption to plan its beer inventory levels on future​ seven-day cruises.​ (The ship certainly​ doesn't want to run out of beer in the middle of the​ ocean!) The average beer consumption over 18 randomly selected​ seven-day was 81,903 bottles with a sample standard deviation of 4,585 bottles. Complete parts a and b below. A. Construct a 95% confidence interval to estimate the average beer consumption per cruise. The 95% confidence...
A cruise company would like to estimate the average beer consumption to plan its beer inventory...
A cruise company would like to estimate the average beer consumption to plan its beer inventory levels on future​ seven-day cruises.​ (The ship certainly​ doesn't want to run out of beer in the middle of the​ ocean!) The average beer consumption over 19 randomly selected​ seven-day cruises was 81,838 bottles with a sample standard deviation of 4,568 bottles. Complete parts a and b below. a. Construct a 95​% confidence interval to estimate the average beer consumption per cruise. The 95​%...
Sausage Company would like to estimate how long it will take to realize cash from its...
Sausage Company would like to estimate how long it will take to realize cash from its ending inventory. For this purpose the following data are submitted: Accounts Receivable, less allowance for doubtful accounts of $40,000 $  660,000 Ending Inventory   750,000 Net Sales 5,650,000 Cost of Goods Sold 4,250,000 Estimate how long it will take to realize cash from the account receivables. Estimate how long it will take to sell all ending inventory. Estimate how long it will take to realize cash...
A company would like to estimate how much its employees pay in childcare costs per month...
A company would like to estimate how much its employees pay in childcare costs per month in order to decide whether it would be worthwhile to provide day care facilities to its employees. The company was advised by a statistician that childcare cost per month historically follows a normal distribution. A random sample of 25 employees who use childcare services revealed that on average they paid $300 per month in childcare costs with a standard deviation equal to $10. Estimate...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT