Question

Month	Packing Costs	Number of Orders	Weight of Orders in Lbs.	Number of Fragile Items
1	$45,000	11,200	24,640	1,120
2	58,000	14,000	31,220	1,400
3	39,000	10,500	18,000	1,000
4	35,600	9,000	19,350	850
5	90,000	21,000	46,200	4,000
6	126,000	31,000	64,000	5,500
7	90,600	20,000	60,000	1,800
8	63,000	15,000	40,000	750
9	79,000	16,000	59,000	1,500
10	155,000	40,000	88,000	2,500
11	450,000	113,500	249,700	11,800
12	640,000	150,000	390,000	14,000
13	41,000	10,000	23,000	900
14	54,000	14,000	29,400	890
15	58,000	15,000	30,000	1,500
16	58,090	14,500	31,900	1,340
17	80,110	18,000	50,000	3,000
18	123,000	30,000	75,000	2,000
19	108,000	27,000	63,450	1,900
20	76,000	18,000	41,400	1,430

1) Assume Number of Orders is the cost driver, estimate the cost equation using:

a. High-Low Method:

b. Prepare a scattergraph, identify outliers (if any), TRIM THE DATA, and re-estimate the cost equation using the High/Low Method: ____________________________________(1)

c. Use Simple Regression Analysis (TRIMMED DATA): (2)

i. Document the goodness of fit (R-square):

ii. How well does the independent variable explain the variation in the dependent variable?

Circle one: Excellent Very Good Good O.K. Poor

iii. State the Independent variable by name

iv. Can we rely on ALL the coefficient values? Why or Why Not? _________

2) Assume Number of Fragile Items is the cost driver, estimate the cost equation using:

a. High-Low Method:

b. Prepare a scattergraph, identify outliers (if any), TRIM THE DATA, and re-estimate the cost equation using the High/Low Method: _____________________________________(3)

c. Use Simple Regression Analysis (TRIMMED DATA): (4)

i. Document the goodness of fit (R-square):

ii. How well does the independent variable explain the variation in the dependent variable?

Circle one: Excellent Very Good Good O.K. Poor

iii. State the Independent variable by name

iv. Can we rely on ALL the coefficient values? Why or Why Not? _________

3) Estimate the cost equation using MULTIPLE REGRESSION ANALYSIS and write it below:

NOTE: Use the trimmed data set.

a. Multiple Regression Analysis

(5)

i. Document the goodness of fit (Adjusted R-square):

ii. How well does the independent variable explain the variation in the dependent variable?

Circle one: Excellent Very Good Good O.K. Poor

iii. State the Independent variables

iv. Can we rely on ALL the coefficient values? Why or Why Not? _________

4) Given the five cost functions estimated above (#1 through #5), compute the following:

a. Assume management estimates that 26,000 orders, 57,000 lbs. of weight, and 900 fragile items will be incurred during the next month. Estimate total packing costs using each of the 5 cost functions?

(High/Low - Orders): (High/Low-Fragile):

(Simple - Orders): (Simple Fragile):

(Multiple Reg’n):

5) Based on your analysis, which cost estimation equation would you suggest that CC employ to estimate its Packaging Costs? Why? Provide a SOLID RECOMMENDATION.

Answer 1

1)

Assuming Number of Orders as the cost driver, the total cost is estimated using the high-low method in following steps,

Step 1: The variable cost is defined as,

Where y2 is the total cost of high activity, y1 is the total cost of low activity, x1 is highest activity unit (highest demand) and x2 is lowest activity unit (Lowest demand).

From the data values, y2 = 640000, y1 = 35600, x1 = 15000, x2 = 9000

Step 2: To calculate the fixed, put the value of highest activity or lowest activity in the total cost equation,

Step 3: Now the total cost equation is define as,

where x is the number of unit of demand.

b)

The scatter plot is obtained in excel by following steps,

Step 1: Write the data values in excel, The screenshot is shown below,

Step 2: Select column Packaging cost and number of demand column then INSERT > Recommended Charts > All Charts > Scatter X Y then Ok. the screenshot of Chart is shown below,

There are two outlier points can be seen in scatter plot. These points are,

Cost $ 450000 and $ 640000.

Now after removing these two points,

Now the, Variable cost is,

From the data values, y2 = 640000, y1 = 35600, x1 = 15000, x2 = 9000

and the fixed cost is calculated as,

Step 3: Now the total cost equation is define as,

c)

The regression analysis is done in excel by following these steps;

Step 1: Write the data values in excel, The screenshot is shown below,

Step 2: DATA > Data Analysis > Regression > OK. The screenshot is shown below,

Step 3: Select Input Y Range: Packaging Costs column, Input X Range: Number of Orders column then OK. The screenshot is shown below,

The result summary is obtained. The screenshot is shown below,

i) From the regression analysis, the R square value = 0.97806

ii) The data excellently fit the model.

iii) Independent Variable: Number of Orders

iv) The P-value for coefficients are,

	p-value
Intercept	0.312756
Number of Orders	1.06E-14

The P-value for Intercept is 0.312756 > 0.05 at 5% significant level hence we can not rely on this intercept value.

2)

a)

Using High-Low method,

Fixed Cost is,

Hence the Cost equation is,

b)

The scatterplot is shown below,

The two points with cost 640000 and 450000 are outliers points.

Now the cost equation with trimmed data is obtained as below,

Fixed Cost is,

Hence the Cost equation is,

c)

THe regression analysis is done in excel by following similarly steps stated in part 1 by taking Input Range X: Pacjkaging Cost column and Input Variable X: Number of Frazile Items. The screenshot for regression analysis is shown below,

i) R Square value = 0.4294

ii) The independent variable explains the dependent variable somewhat O.K.

iii) Independent Variable: Number of Fragile items.

iv) The P-value for each coefficient is less than 0.05 hence we can rely on these variabble.

3)

The multiple regression analysis is done in excel by following these steps,

Step 1: Write the trimmed data values in excel. The screenhot is shown below,

Step 2: DATA > Data Analysis > Regression Analysis > OK.

Step 3: Select Input X Range: Packaging Costs column, Input X Range: All the other three variable then OK. The screenshot is shown below,

The result summary is shown below,

i) The Goodness of fir, Adjusted R square = 0.998688

ii) Excellent

iii) Independent Variables are 1) Number of Orders 2) Weight of Orders in Lbs 3) Number of Fragile Items

iv) The P-values are,

4)

Prediction fromthe five equations are,

5)

The multiple linear regression model giving the highest R squared value which means the multiple linear regression model explaning the model very well with 99.89%. hence this model will be preferred.