Question

Homework: Chapter 6 Homework Save Score: 0 of 2 pts 6 of 7 (6 complete) HW Score: 80%, 8 of 10 pts E6-27A (similar to) Question Help O Kim Chen, owner of Rose Red, operates a local chain of floral shops. Each shop has its own delivery van. Instead of charging a flat delivery fee, Chen wants to set the delivery fee based on the distance driven to deliver the flowers. Chen wants to separate the fixed and variable portions of her van operating costs so that she has a better idea how delivery distance affects these costs. She has the following data from the past seven months: 3(Click the icon to view the data.) Read the requirements Requirement 1. Determine the company's cost equation (use the output from the Excel regression). (Round the amounts to two decimal places.) Data Table y = $ x+$ Month Miles Driven í Requirements January February March Van Operating Costs $5,490 $5,700 $4,910 $5,340 $5,820 $5,410 Use Microsoft Excel to run a regression analysis, then do the following: 1. Determine the company's cost equation (use the output from the Excel regression) 2. Determine the R-square (use the output from the Excel regression). What does Rose Red's R-square indicate? 3. Predict van operating costs at a volume of 16,000 miles assuming the company would use the cost equation from the Excel regression regardless of its R-square. Should the company rely on this cost estimate? Why or why not? 16,000 17,500 14,900 16,200 16,900 15,100 14,500 April ......... May .... June.. July... $4,920 Print Done Print Done Enter any number in the edit fields and then click Check Answer.

Answer 1

The linear regression equation is given as

Y = a + bX

where a = intercept, b = slope

Y = dependent variable = Van operating costs

X = independent variable = Miles driven

The value of the intercept and slope is found using regression analysis in excel.

In excel, go to data , data analysis , select regression

Connections 21 A2 三晶 File Home Insert Page Layout 2 Show Queries From Table Get External New Data Query, Lo Recent Sources All Get & Transform Formulas Data Review View Connections ALZA Y Properties Sort Filter Edit Links Connections Sort & Filter Developer Power Pivot Tell me what you want to do.... Clear E Flash Fill - Consolidate HlRemove Duplicates Relationships Advanced Columns Data Validation - Manage Data Model Data Tools Data Analysis ? Solver Reapply Text to 5 Refresh What If Forecast Analysis – Sheet Forecast Group - Ungroup - Subtotal Outline Analyze D F G J K L M N O P Q R S Data Analysis ок | D2 X fac A B C Van Miles operating 1 Month Driven costs 2 January 16000 5490 3 February 17500 5700 4 March 14900 4910 5 April 16200 5340 6 May 16900 5820 June 15100 5410 8 July 14500 4920 Cancel Analysis Tools Histogram Moving Average Random Number Generation Rank and Percentile Regression Sampling t-Test: Paired Two Sample for Means t-Test: Two-Sample Assuming Equal Variances t-Test: Two-Sample Assuming Unequal Variances Z-Test: Two Sample for Means Help

The values are entered as shown

Review View Home Insert Page Layout Show Queries From Table Get External New Data Q uery Lo Recent Sources All Get & Transform Formulas Data Connections Properties And Edit Links Developer Power Pivot T E Flash Fill 88 Remove Dupli Text to Advanced Columns Data Validation 8 x Pat Clear Reapply Refresh Sort Filter con Regression B2 - X V fix Input Input Y Range: OK SCS2:SCS8 A Cancel Input X Range: SB52:5B58 Help Labels Confidence Level: D Constant is Zero 95 % B C Miles Van Driven operating (x) --- costs (Y) 16000 5490 17500 5700 4910 16200 5340 16900 5820 15100 5410 145001 4920 1 Month 2 January 3 February 4 March 5 April 6 May 7 June 8 July 14900 Output options Output Range: New Worksheet Ply: New Workbook Residuals Residuals Standardized Residuals Residual Plots Line Fit Plots Normal Probability Normal Probability Plots

1 SUMMARY OUTPUT 3 Regression Statistics 4 Multiple R 0.87862 5 R Square 0.771973 6 Adjusted R Square 0.726367 7 Standard Error 184.0041 8 Observations 10 ANOVA 11 df 12 Regression 13 Residual 14 Total SSMSF gnificance F 1 573112.4 573112.4 16.92718 0.009225 5 169287.6 33857.52 6 742400 16 17 Intercept 18 X Variable 1 Coefficientandard Errt Stat P-value Lower 95%Upper 95%ower 95.09 Upper 95.0% 908.9346 1086.52 0.836556 0.440978 -1884.05 3701.924 -1884.05 3701.923622 0.281075 0.068317 4.114265 0.009225 0.10546 0.45669 0.10546 0.456690262

The company's cost equation is

y = $ 0.28 x + $ 908.93

------------------------------------------------------------------------------------

2)

From the regression output, the value of R-square = 0.772.

The R square value indicates that 77.2% of variation in van operating costs can be explained by the variation in miles driven.

-------------------------------------------------------------------------

3) Van operating cost at a volume of 16,000 miles is found using the cost equation

y = $ 0.28 $\times$ 16,000 + $ 908.93

y ( van operating cost at 16,000 miles)  = $ 5388.93

The company should rely on this estimate because the value of R-square is 0.772 which suggests a strong association between van operating costs and miles driven.