Susan Lewis, owner of a florist shop, is interested in predicting the cost of delivering floral 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.

Required:
1. Using a computer spreadsheet program such as Excel, run a regression on these data.
2. Using your results from Requirement 1, write the cost formula for delivery cost.
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 be the predicted delivery cost be for a month with 300 deliveries?
5. For this requirement, assume that Susan believes that actual variable and fixed costs might be as much as 20% higher or 20% lower than the amounts shown in the regression output (generated in response to Requirement 1). Return to the cost formula you constructed in responding to Requirement 2 and enter the formula into a computer spreadsheet program, such as Excel. Alter your cost formula equation to reflect first a 20% increase and second a 20% decrease in both cost components. Using your altered cost formula equation, calculate the range in predicted delivery cost for a month with 300 deliveries (i.e., highest possible total cost minus lowest possible total cost). (Note: Round the fixed cost and total cost to the nearest dollar and round the variable rate to the nearest cent.) Briefly explain how you altered your cost formula to reflect the 20% change in both cost components.

Transcribed Image Text:

Month January February March April May June July August September October November December Number of Deliveries 100 550 85 115 160 590 500 520 100 200 260 450 Delivery Cost $1,200 1,800 1,100 1,050 1,190 1,980 1,800 1,700 1,100 1,275 1,400 2,200