Consider the following data from a small bookstore.
Number of sales people working—————–Sales (in $1000)
x = 10.4—————————————————–y = 17.6
SD(x) = 5.64———————————————–SD(y) = 5.34
1. Calculate the mean and standard deviation of # of sales people (x) and sales (y). Find sample size n.
2. Calculate the correlation coefficient with all three methods (covariance method, z-score method, and correlation tool in Excel).
3. Find b0 and b1 for the linear regression model with all three methods (two different formulas and Solver). Interpret the meaning of b1 of this linear regression model.
4. Calculate Se and SST.
5. Calculate R square with both methods (correlation coefficient method and the formula r square = 1 – SSE/SST). Interpret the meaning of R square of this linear regression model.
6. Use Regression tool in Excel to find b0, b1, SSE, SST, r, R square, and Se.
7. Estimate the sales if the number of sales people working is 8, 13, and 19, respectively.
1 Problem 1
Pronto Pizza is a family-owned pizza restaurant in Vinemont, a small town of 20,000 people in upstate New York. Antonio Scapelli started the business 30 years ago as Antonio’s Restaurant with just a few thousand dollars. Antonio, his wife, and their children, most of whom are now grown, operate the business. Several years ago, one of Antonio’s sons, Tony, Jr., graduated from NYU with an undergraduate degree in business administration. After graduation, he came back to manage the family business. Pronto Pizza was one of the earliest pizza restaurants to offer pizza delivery to homes. Fortunately, Tony had the foresight to make this business decision a few years ago. At the same time, he changed the restaurant’s name from Antonio’s to Pronto Pizza to emphasize the pizza delivery service.
The restaurant has thrived since then, and has become one of the leading businesses in the area. While many of their customers still “dine-in” at the restaurant, nearly 90% of Pronto’s current business is derived from the pizza delivery service. Recently, one of the national-chain fast food pizza delivery services found its way to Vinemont, NY. In order to attract business, this new competitor has guaranteed delivery of its pizzas within 30 minutes after the order is placed. If the delivery is not made within 30 minutes, the customer receives the order without charge. Before long, there were signs that this new pizza restaurant was taking business away from Pronto Pizza. Tony realized that Pronto Pizza would have to o er a similar guarantee in order to remain competitive.
After a careful cost analysis, Tony determined that to o er a guarantee of 29 minutes or less, Pronto’s average delivery time would have to be 25 minutes or less. Tony thought that this would limit the percentage of \free pizzas” under the guarantee to about 5% of all deliveries, which he had gured to be the break-even point for such a promotion. To be sure of Pronto’s ability to deliver on a promise of 29 minutes or less. Tony knew that he needed to collect data on Pronto’s pizza deliveries.
Pronto Pizza’s delivery service operates from 4:00 p.m. to midnight every day of the week. After an order for a pizza is phoned in, one of the two cooks is given the order for preparation. When the crust is prepared and the ingredients have been added, the pizza is placed on the belt of the conveyor oven. The speed of the conveyor is set so that pizzas come out perfectly, time after time. Once the pizza is ready and one of Pronto’s drivers is available to make the delivery, the pizza is taken in a heat-insulated bag to the customer. Pronto uses approximately ve to six drivers each night for deliveries. Most of the drivers hired by Pronto Pizza are juniors and seniors at the local high school. Given the large number of deliveries made each evening, Tony knew that he could not possibly monitor every single delivery. He had thought of the possibility of having someone else collect the data, but given the importance of accurate data, he decided to make all of the measurements himself. This, of course, meant taking a random sample of, rather than all, deliveries over some time period. Tony decided to monitor deliveries over the course of a full month. During each hour of delivery service operation, he randomly selected a phoned-in order. He then carefully measured the time required to prepare the order and the amount of time that the order had to wait for a delivery person to become available. Tony would then go with the delivery person to accurately measure the delivery time. After returning, Tony randomly selected an order placed during the next hour and repeated the process. At the end of the month, Tony had collected data on 324 deliveries. Once the data were available, Tony knew there were several issues that should be addressed. He was committed to going with the 29-minute delivery guarantee unless the data strongly indicated that the true average delivery time was greater than 25 minutes. How would he make this decision? Tony also realized that there were three components that could affect pizza delivery times: the preparation time, the waiting time for an available driver, and the travel time to deliver the pizza to the customer. Tony hoped that he had collected sufficient data to allow him to determine how he might improve the delivery operation by reducing the overall delivery time.
Tony has asked you for some assistance in interpreting the data that he has collected. In particular, he needs to know if the true average delivery time for Pronto Pizza is greater than 25 minutes. Use the data in the le Pronto Data.xlsx to answer his question. A description of this data set is given in the Data Description section. Also, examine the data for further information that might help Tony in making his decision about the 29-minute delivery guarantee and in improving his pizza delivery service. The Case Questions will assist you in your analysis of the data. Use important details from your analysis to support your recommendations.
1.1 Data Description
The data le contains the data collected by Tony Scapelli over the past month on Pizza deliveries. Data are recorded for each delivery order.
The variables are de ned as follows:
.Day: Day of the week (1=Monday, 7=Sunday).
.Hour: Hour of the day (4-11 pm).
.Prep Time: Time Required (in minutes) to prepare the order.
.Wait Time: Time (in minutes) to prepare the order.
.Travel Time: Time (in minutes) it took the car to reach the delivery location.
.Distance: Distance (miles) to delivery.
For parts 1. – 4., perform the analysis using hypothesis tests. For every hypothesis test, you should mention what the null and alternative hypotheses are (in words or with symbols) and interpret the results to answer the question.
1. Is there sucient evidence in the data to conclude that the averagetotaltime to deliver a pizza, once the order is placed, is greater than 25 minutes? (10 pts
2. What percentage of the time will Pronto fail to meet its guaranteed time of 29 minutes or less? Can we be reasonably con dent that they are meeting their requirement of failing to meet the guarantee 5% of the time or less? (10 pts)
3. Does the day of the week have an effect on delivery time? Attach any supporting output for your answer. (10 pts)
4. Determine if any particular component of the total time to make and deliver a pizza was causing the greater delivery time on Saturday. In order to do this, you might want to look at the differences in the different components between Saturday and on the other days of the week. (15 pts)
5. Based on your analysis of the data, what action (or actions) would you recommend to the owners to Pronto Pizza to improve their operation? (10 pts)