METHOD OF LEAST SQUARES USING COMPUTER SPREADSHEET PROGRAM
Susan Lewis, owner of a florist shop, is interested in predicting the cost of delivering flo- ral arrangements. She collected monthly data on the number of deliveries and the total monthly delivery cost (depreciation on the van, wages of the driver, and fuel) for the past year.
Month
|
Number of Deliveries |
Delivery Cost ($) |
|
January
|
100
|
1,200
|
|
February
|
550
|
1,800
|
|
March
|
85
|
1,100
|
|
April
|
115
|
1,050
|
|
May
|
160
|
1,190
|
|
June
|
590
|
1,980
|
|
July
|
500
|
1,800
|
|
August
|
520
|
1,700
|
|
September
|
100
|
1,100
|
|
October
|
200
|
1,275
|
|
November
|
260
|
1,400
|
|
December
|
450
|
2,200
|
Required:
1. Using a computer spreadsheet program such as Excel, run a regression on these data. Print out your results.
2. Using your results from Requirement 1, write the cost formula for delivery cost. (You may round the fixed cost to the nearest dollar and the variable rate to the nearest cent.)
3. What is R2 based on your results? Do you think that the number of direct labor hours is a good predictor of delivery cost?
4. Using the cost formula in Requirement 2, what would predicted delivery cost be for a month with 300 deliveries?