Question

Exercise 6-34 Estimating Cost Behavior by Multiple Methods (Appendix) (LO 6-1, 6-2, 6-5, 6-9) Gator Beach Marts, a chain of convenience grocery stores in the Fort Lauderdale area, has store hours that fluctuate from month to month as the tourist trade in the community varies. The utility costs for one of the company's stores are listed below for the past six months. Total Utility Total Hours Month of Operation January 550 February 600 March 700 April 500 May 450 400 $1,620 1.700 1.900 1,600 1.350 1.300 Required: 1-a. Use the high-low method to estimate the cost behavior for the store's utility costs. Express the cost behavior in formula form ( Y a + DX 1-b. What is the variable utility cost per hour of operation? 3-a. Construct an Excel spreadsheet and use the Excel commands to perform a least squares regression. Estimate the cost behavior for the store's utility cost. Express the cost behavior in formula form. 3-b. What is the variable utility cost per hour of operation? 4. During July, the store will be open 300 hours, Predict the store's total utility cost for July using each of the cost-estimation methods employed in requirements (1) and (3) Complete this question by entering your answers in the tabs below.

Answer 1

1a. High low method
Variable cost per hour = (Cost at highest level - Cost at lowest level) / (Highest Activity - Lowest Activity)
= ($1900-1300) / (700-400) = $2 per hour

Fixed Cost = $1900 - 700 x $2 = $500 per unit

Y = $500 + $2x

1b. Variable Utility Cost per hour = $2

3-a

ANS - Microsoft Excel x Home Insert Page Layout - x Data Analysis EO Formulas Data Review View Connections * Clear Properties Reapply E Refresh 21 Sort Filter All Edit Links Advanced Connections Sort & Filter LA From From Access Web Show Detail Hide Detail 28 29 30 Group Ungroup Subtotal From From Other Text Sources Get External Data C O Text to Remove Data Consolidate What-If Columns Duplicates Validation Analysis Data Tools Existing Connections Outline Analysis K14 H J K L M N O P Q R S т SUMMARY OUTPUT OUTPUT 3 Total hours of Operation Line Fit Plot 5 $3,000 Regression Statistics 4 Multiple R 0.975590116 R Square 0.951776075 6 Adjusted R Square 0.939720093 Standard Error 54.90901565 8 Observations 9 10 ANOVA $2,000 $1,000 Total Utility Cost Total Utility Cost 0 200 400 600 800 Total hours of Operation Predicted Total Utility Cost 11 Significance F 0.000886491 1 SS MS F 238023.3333 238023 78.9464 12060 3015 250083.3333 12 Regression 13 Residual 14 Total 15 16 17 Intercept 18 Total hours of Operation Coefficients 501 2.02 Standard Error t Stat P-value 123.3053701 4.06308 0.01531 0.227344924 8.88518 0.00089 Lower 95% Upper 95%wer 95.0%pper 95.0% 1 58.6494089 843.351 158.649 843.351 1 .3887893 2.65121 1.38879 2.65121 2.0 RESIDUAL OUTPUT Observation Residuals icted Total Utility 1612 2 1713 3 1915 4 1511 1410 1309 Sheet19 Sheet20 -13 -15 89 30 6 Sheet18 Sheet21 Sheet22 Sheet23 Sheet24 Sheet25 Sheet26 Sheet27 0 Ready BOJ 80% o 5 W K Tally O EN ! 11:29 . + 07/02/2020

Y = $501 + $2.02 x

3b. Variable Utility Cost per hour = $2.02

4. Cost for 300 hours
High low method = $500 + $2 x 300 = $1100
Least Squares method = $501 + $2.02 x 300 = $1107