Question

Selecting a Basis for Predicting Shipping Expenses (Requires Computer Spreadsheet*) Boom Company assembles and sells portable speaker systems in the midwest. In an effort to improve the planning and control of shipping expenses, management is trying to determine which of three variables—units shipped, weight shipped, or sales value of units shipped—has the closest relationship with shipping expenses. The following information is available: Month Units Shipped Weight Shipped (lbs.) Sales Value of Units Shipped Shipping Expenses May 6,000 9,300 $200,000 $8,960 June 10,000 12,000 220,000 12,320 July 8,000 12,150 160,000 10,400 August 14,000 15,000 228,000 16,640 September 12,000 10,500 280,000 13,760 October 9,000 12,000 320,000 13,120 Required a. With the aid of a spreadsheet program, determine whether units shipped, weight shipped, or sales value of units shipped has the closest relationship with shipping expenses. Complete the r-squared values between each possible independent variable and shipping expenses: Round answers to two decimal places. Units shipped Answer 0.92 Weight shipped Answer 0.59 Sale value Answer 0.23 Based on the coefficient of determination, Answer has the closest relationship with shipping expenses. b. Using the independent variable that appears to have the closest relationship to shipping expenses, develop a cost-estimating equation for total monthly shipping expenses. Round answers to two decimal places. Shipping expenses = $Answer 3,200 + $Answer 0.9 X c. Use the equation developed in requirement (b) to predict total shipping expenses in a month when 5,000 units, weighing 7,000 lbs., with a total sales value of $114,000 are shipped. Round answers to two decimal places. $Answer 5,000

Answer 1

• To determine which component has the closed relationship with the shipping expenses, we need to calculate the coefficient of determination. Coefficient of determination can be calculated with the use of coefficient of correlation function of EXCEL. Coefficient of determination is the squared value of coefficient of correlation. CORREL FUNCTION of EXCEL is used:

• The calculations have been shown in the following table:

38 Coefficient of Determination (Units Shipped and Shipping Expenses) 40 Shipping Month Units Shipped 42 M 43 June 8,960 10,000 12,320 8,000 10.400 14,00016,640 2.00013.760 ust 46 September 47 October 48 Coefficient of Determination 49 CORRELIB42 B47.C42C47r2 0.921903 Coefficient of Determination (Weight Shipped and Shipping Expenses) 50 Month Weight Shipped 51 52 Ma 53 June 12,00012,320 501 1040 55 Auqust 56 September 57 October 58 Coefficient of Determination CORREL(BS2:B57,052:C57J 15,000 16.640 10,500 3,760 12,000 13,120 9 Coefficient of Determination (Shipping Value and Shipping Expenses) Shipping Expense Shipping Value 60 61 IM 62 June 63 64 65 September 66 October 67 Coefficient of Determination (CORRELIB64:B69,C64:069r2 200,000 220,00012,320 160.000 10.400 228,000 16,640 ust 20000 13.120 0.225015 Part A) Based on the above calculations, units shipped has the highest coefficient of determination which indicates that it has the closes relationship with the shipping expenses. Answer for Part A is "Units Shipped"

Part B) We will use the high-low method to determine the variable cost per unit, fixed cost and arrive at the cost equation. The formula for calculating variable cost per unit under this method is given below Variable Shipping Expense Per Unit Shipped = (Total Shipping Expenses for Highest Units-Total Shipping Expenses for Lowest Units)(Highest Units-Lowest Units) Using the information provided in the question, we get, Variable Shipping Expense Per Unit Shipped = (16640-8960)(14000-6000-$0.96 per unit Fixed Shipping Expense = Total Shipping Expense at Highest Units-Total Variable Shipping Expense at Highest Units = 16640-096*14,000 = S3200 Based on the above calculations, the total cost equation can be derived as follows: Total Shipping Expense 3200+ 0,96X where X is the Number of Units Shipped Part C) Based on the equation derived in Part B), the total shipping expense can be arrived as follows: Total Shipping Expense-3200+ 096*5,000 $8,000 (here, Х-5,000 Units Shipped)