Question

6-2A - High-Low Method, Scattergraph, Least-squares Regression Danny Office Supplies shows the following data related to shipping costs for the first six months of the year: Number of Shipping Packages Cost Shipped January 100 $1,200 February 120 1,300 March 1,350 April 130 1,500 May 1,400 June 90 1,100 125 1107 - Required: Using the high-low method, estimate the cost formula (write it in y=mx+b format). i. Using your answer from part a.) above, assuming in July the company expects to ship 150 packages, what will be the company's estimated shipping cost? b.) Using the scattergraph method, estimate the cost formula. c.) Using the least squares regression method, estimate the cost formula. d.) Are there any factors other than the number of packages shipped that may contribute to a variation in shipping cost?

Answer 1

Requirement a: Estimate cost formula using the high-low method as follows

Variable cost = Change in cost ÷ Change in activity

                        = ($1,500 − $1,100) (130 packages – 90 packages)

                        = $10 per package

Fixed Cost      = Total cost − (Variable cost per unit × Number of packages)

                        = $1,500 – ($10 × 130)

                        = $200

Cost formula: y = $10x + $200 where y is total cost and x is number of packages

Shipping cost in July when number of packages shipped is 150

            Shipping cost = ($10 × Number of packages) + $200

                                    = ($10 × $150) + $200

                                    = $1,700

Requirement b. Using the scatter graph method, estimate the cost formula as follows

Cost formula: y = $8.105x + $396.4 where y is total cost and x is number of packages

Using Scatter function of Excel, plot the following chart and then add trend line for cost formula

738 Til M3 PivotTable Table Picture Column Line Pie Bar Area Clip Shapes SmartArt Art Illustrations Scatter Other Charts Hyperlink Text Box Hea & Fc Tables Charts Scatter Chart 1 A B C D E F G к Month #Packages Shipping Cost Shipped 1007 $1,200 $1,300 120 2 January 3 February 4 March April 6 May 125 $1,350 $1,500 $1,400 $1,100 ih All Chart Types.... June 901 Scattergraph $1,600 y = 8.105x + 396.4 $1,400 $1,200 $1,000 $800 $600 $400 $200 $0H 0 — Shipping Cost Linear (Shipping Cost) 50 100 150

Requirement c. Using the regression method, estimate the cost formula as follows

Cost formula: y = $8.11x + $396.49 where y is total cost and x is number of packages

| Ta Group Ungroup Subtotal Show Detail Hide Detail Data Analysis Solver ? Outline Analysis N O P Q R S Home Insert Page Layout Formulas Data Review View Developer Acrobat Connections Clear Reapply 20 s Properties . From From From From Other Existing Refresh 21 Sort Filter Text to Remove Data Consolidate What If Access Web Text y Advanced Sources Connections All Edit Links Columns Duplicates Validation" Analysis Get External Data Connections Sort & Filter Data Tools B1 fx # Packages Shipped B C D E F G H I J K L M # Packages Shipping Data Analysis ? X Month SUMMARY OUTPUT Shipped Cost Analysis Tools OK 2 January 100 $1,200 F-Test Two-Sample for Variances Regression Statistics Fourier Analysis February 120 Cancel $1,300 Histogram Multiple R 0.874191356 4 March 125 $1,350 Moving Average Random Number Generation Help R Square 0.764210526 5 April 130 $1,500 Rank and Percentile Adjusted R Square 0.705263158 6 May 110 $1,400 Regression Sampling Standard Error 77.57282939 7 June 90 $1,100 t-Test: Paired Two Sample for Means Observations t-Test: Two-Sample Assuming Equal Variances 1 8 A E F G H I L ANOVA D Regression df Month Input Input Y Range: OK7 Regression SS M S F gnificance F 1 78013.1579 78013.16 12.96429 0.022746 4 24070.1754 6017.544 5 102083.333 $C$1:$C$71 Cancel Residual Total # Packages Shipping Shipped Cost 100: $1,200 1203 $1.300 $1.350 130; $1,500 110 $1,400 90: $1.100 Input X Range: $B$1:$B$7 2 January 3 February 4 March 5 April 6 May 7 June 1251 Help Labels Confidence Level: Constant is Zero 95 % Intercept # Packages Shipped Coefficients tandard Erro Stat P-value Lower 95%Upper 95%ower 99 396.49 255.220048 1.553527 0.19526 -312.113 1105.096 -312. 8.11 2.25108982 3.600595 0.022746 1.855236 14.35529 1.8552 $A$9 Nome Output options O Qutput Range: New Worksheet Ply: New Workbook Residuals Residuals Standardized Residuals Normal Probability Normal Probability Plots Residual Plots Line Fit Plots

Requirement d: Yes, there are factors other than the number of packages that may contribute to variation in shipping cost. For example the weight of the packages, mode of transportation and priority in package delivery also influence shipping costs.