Question

In: Statistics and Probability

    Trinkets and Things is developing a cost formula for its packing activity. Discussion with workers...

    Trinkets and Things is developing a cost formula for its packing activity. Discussion with workers in the packing department has revealed that packing costs may be associated with the number of customer orders, the order weight in pounds, and the relative fragility of the items (more fragile items must be specially wrapped). Data for the past 20 months have been gathered:

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:   

            c.         Use Simple Regression Analysis (TRIMMED DATA):            

                        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:            _____________________________________

            c.         Use Simple Regression Analysis (TRIMMED DATA):                                                                                                                       

                        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

                                                                                                                                                                                                                                                                                                                               

                        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.

                                                                                                                                                                                                                                                                                                     

                                                                                                                                                                                                                                                                                                     

                                                                                                                                                                                                                                                                                                     

                                                                                                                                                                                                                                                                                                     

                                                                                                                                                                                                                                                                                                     

Solutions

Expert Solution

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,

P-value
Intercept 0.076428 > 0.05 Can not rely at 5% Significance
Number of Orders 3.11E-12 < 0.05 Can rely at 5% Significance
Weight of Orders in Lbs 2.84E-10 < 0.05 Can rely at 5% Significance
Number of Fragile Items 3.11E-06 < 0.05 Can rely at 5% Significance

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.


Related Solutions

Relate Kolberg's stages of moral development (and its critiques), to our discussion of developing a pragmatic...
Relate Kolberg's stages of moral development (and its critiques), to our discussion of developing a pragmatic approach to last week's topic regarding sex-education. In your post, include how you would frame your opinion on Kolberg's scale, how you would interpret the moral position of those responsible for creating the ab-only policies that are a current standard in Mississippi, and how this all may influence adolescent moral development. Refer to lecture material as much as possible. Last weeks discussion: How, why,...
In activity-based costing, each major activity has its own overhead cost pool, its own activity measure,...
In activity-based costing, each major activity has its own overhead cost pool, its own activity measure, and its own overhead rate. True or False TrueFalse
Define Activity Based Cost and its impact on profitbility.
Define Activity Based Cost and its impact on profitbility.
analyst two challenges that spincent overcame in developing its export activity. describe how it overcame them.
analyst two challenges that spincent overcame in developing its export activity. describe how it overcame them.
Ohme Framing's cost formula for its supplies cost is $1,620 per month plus $13 per frame....
Ohme Framing's cost formula for its supplies cost is $1,620 per month plus $13 per frame. For the month of April, the company planned for activity of 882 frames, but the actual level of activity was 878 frames. The actual supplies cost for the month was $13,500. The supplies cost in the flexible budget for April would be closest to:
Meadows Manufacturing has $10,000 in payroll for its factory workers that must be added the cost...
Meadows Manufacturing has $10,000 in payroll for its factory workers that must be added the cost of its product. How would Meadows record the direct labor cost? A : a debit to Wages Payable and a credit to Work in Process Inventory B : a debit to Work in Process Inventory and a credit to Manufacturing Overhead C : a debit to Manufacturing Overhead and a credit to Factory Labor D : a debit to Work in Process Inventory and...
MOCKTOE INC. Flexible Budget Cost Formula (per toaster) Activity (toasters) 110000 120000 130000 Variable costs: Total...
MOCKTOE INC. Flexible Budget Cost Formula (per toaster) Activity (toasters) 110000 120000 130000 Variable costs: Total variable costs $0.00 $0 $0 $0 Fixed costs: Total fixed costs $0 $0 $0 Total costs $0 $0 $0 ToasToe Inc. (TI) is a manufacturer of heating elements for toaster overs. To improve control over operations, the president wants to install a flexible budgeting system, rather than the single master budget being used at present. The following data are available for expected costs for...
1. BTC uses the cost formula Y = $5,300 + $0.60X to estimate its maintenance costs...
1. BTC uses the cost formula Y = $5,300 + $0.60X to estimate its maintenance costs where X represents machine hours. If the August budget calls for 8,000 hours of planned machine time, what maintenance cost is expected to be incurred during August? 2. MCK bases its predetermined overhead rate on the estimated machine hours for the upcoming year. Its estimates for the upcoming appear below:             Machine hours                                    52,800             Variable manufacturing overhead      $4.10 per machine hour             Fixed...
What are some general things a company can do to reduce its DBMS security cost? Give...
What are some general things a company can do to reduce its DBMS security cost? Give some examples and why it would be cheaper for any company to have these options.
Vanguard Corporation has provided the following data from its activity-based costing system: Activity Cost Pool Total...
Vanguard Corporation has provided the following data from its activity-based costing system: Activity Cost Pool Total Cost Total Activity Assembly $ 1,078,920 74,000 machine-hours Processing orders $ 76,560 1,650 orders Inspection $ 119,616 1,680 inspection-hours The company makes 400 units of product O37W a year, requiring a total of 630 machine-hours, 50 orders, and 10 inspection-hours per year. The product's direct materials cost is $36.92 per unit and its direct labor cost is $30.06 per unit. According to the activity-based...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT