In: Statistics and Probability
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:
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 |
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.