Assignment Brief
This major assignment is a group project. Students are required to demonstrate the following: • Ability to retrieve relevant data from trusted sources (eg. Use of ABS database or RBA databases, etc) and understand the nature of the data retrieved. This includes being able to identify the appropriate graphical/visual tools of representing the data • Ability to use a standard statistical package (eg. SAS or Microsoft EXCEL) to analyse and summarise the data. • Ability to use inferential tools to test possible hypotheses and conduct basic inferences. • Ability to undertake statistical analysis to establish relationships or patterns in the data collected. • Ability to present a 10 minute oral presentation on key findings and recommendations • Ability to produce a business report describing and interpreting the analyses conducted. • Ability to write an analytical report of your findings. (In your written report, you must provide the following information: A research statement; a project summary, any underlying theory behind the study, methodology of analysis, results and conclusions. It is also important that contributors to each section of the written report is noted.) While your written report should be self -contained, it is important that EXCEL sheets demonstrating your analyses should be included as part of the Appendix.
PROJECT BRIEF:
The data analytical part of the project (75% of total project mark) is divided into 3 parts. Each part tests you on different statistical tools that you have learned in this course. As a group, you need to demonstrate that you have participated in all parts of this project. Work allocation should not be divided according to the parts shown here. The instructions that follow refer to the Data analysis component of your overall project. The other 25 marks will be allocated to the Introduction and Conclusion sections. Please refer to the marking rubrics to see how marks are allocated to the entire project. An additional 10 marks will be assigned for proper sourcing and referencing and another 10 marks for verbal presentation. Total Project will be worth 120 marks.
PART A (40 marks)
Gourmet Delight is a fictitious five star restaurant in the Central Business District (CBD) of Melbourne. The restaurant has the capacity to sit 100 customers. The restaurant is open for lunch (12pm – 3pm) and for dinner (6 -11pm) seven days a week. The owner of Gourmet Delight has been concern that the clientele at the restaurant has been declining in the last six months due to poor service times for their meals. The owner decided to put all staff through an extensive training program in the hope of uplifting the standard of their performance. The owner of Gourmet Delight strongly believes that a serving time of no more than 20 minutes equates to efficient service. File: Serving Times Project Sem 2 2018.xls, Sheet 1, gives the serving times (in minutes) of the restaurant prior to training and after training. Using EXCEL, obtain the following summary measures and write a report of your findings: (a) Summary statistics (b) Box- whiskers plots (c) Histograms (If you need help, see Computer Exercise 3) Six months after the training, several staff left the employment at Gourmet Delight. This caused concerns to the owner of Gourmet delight. A further random sample of service times was recorded and stored in the same file (Sheet 2). Use this information to determine if the owner of Gourmet Delight should be concern.
PART B (15 marks)
To increase patronage to Gourmet Delight, the owner decided to advertise in the local press, offering special discounts and other promotions such as “two for the price of one” deals. Advertising expenditure and monthly revenue for Gourmet Delight for a period of 18 months are given in Sheet 3 of the Serving Times Project file. Using regression analysis, obtain the necessary outputs, and write a report to determine if advertising expenditure has an impact on the restaurant’s revenue.
PART C (20 marks)
(a) Restaurant patronage is often measured by what is termed “turns” per table (successive seatings per day) and by “covers” (seated dining per night). Sheet 4 of the Serving Time Project file gives the patronage for Gourmet Delight for successive days in August. The patronage recorded refers to all paying customers over the entire trading day. Write a brief report of the pattern of patronage. Provide explanation for the pattern observed. Obtain a simple exponentially smoothed series of the data. (You need to plot both series – original series and the smoothed series. (5 marks)
(b) Gourmet Delight’s quarterly takings for the past few years are given in Sheet 5 of the Serving Time Project file. Using trend analysis, obtain a forecast for the four quarters of 2018. Write a brief report of your findings. In your report, you need to explain how you have used trend analysis to obtain the forecasts. (15 marks)
Quantitative Methods BAC115
The following pages discuss quantitative techniques in light of the given case of Gourmet Delight restaurant. The owner feels that clientele is reducing due to increased serving time while 20 minutes is average efficient serving time. Hence, the owner decided to provide training to all staff in order to shorten the serving time. Due to staff leaving over period of next six months, the owner gets concerned about the same. Also, the owner advertises to increase footfall, such as promotional schemes of buy one get one. The owner also analyses patronage data to understand the forecast of expected demand or patronage and manage accordingly.
This part compares the serving time before and after training to understand the impact of training on average serving time. Poor service time for the meals has led to decrease in clientele of the Gourmet Delight Restaurant. The owner decided to train the staff regarding the same and then compares the results. The serving time before and after the training is presented below:
| Serving Times prior to training (minutes) | Serving Times after Training (minutes) |
| 25 | 10 |
| 26 | 30 |
| 27 | 20 |
| 29 | 24 |
| 30 | 15 |
| 35 | 30 |
| 35 | 12 |
| 35 | 26 |
| 35 | 14 |
| 36 | 27 |
| 36 | 27 |
| 37 | 18 |
| 38 | 13 |
| 39 | 29 |
| 39 | 20 |
| 40 | 14 |
| 40 | 16 |
| 41 | 24 |
| 42 | 14 |
| 42 | 18 |
| 42 | 17 |
| 43 | 10 |
| 45 | 10 |
| 47 | 18 |
| 47 | 18 |
| 50 | 34 |
| 55 | 20 |
| 55 | 22 |
| 60 | 25 |
| 65 | 25 |
‘Descriptive statistics’ tool available in MS-Excel was used in order to generate the summary statistics. The result is as follows:
| SUMMARY STATISTICS | ||||
| Serving Times prior to training (minutes) | | Serving Times after Training (minutes) | | |
| | | | | |
| Mean | 40.53 | Mean | 20.00 | |
| Standard Error | 1.75 | Standard Error | 1.21 | |
| Median | 39.50 | Median | 19.00 | |
| Mode | 35.00 | Mode | 18.00 | |
| Standard Deviation | 9.59 | Standard Deviation | 6.65 | |
| Sample Variance | 92.05 | Sample Variance | 44.28 | |
| Kurtosis | 0.53 | Kurtosis | -0.86 | |
| Skewness | 0.70 | Skewness | 0.25 | |
| Range | 40.00 | Range | 24.00 | |
| Minimum | 25.00 | Minimum | 10.00 | |
| Maximum | 65.00 | Maximum | 34.00 | |
| Sum | 1,216.00 | Sum | 600.00 | |
| Count | 30.00 | Count | 30.00 | |
| Largest(1) | 65.00 | Largest(1) | 34.00 | |
| Smallest(1) | 25.00 | Smallest(1) | 10.00 | |
| Confidence Level(95.0%) | 3.58 | Confidence Level(95.0%) | 2.48 |
From above, it can be seen that the average service time has reduced from 40.5 minutes to 20.0 minutes which is the desirable service time. This is reflected in drastic reduction of median as well; 39.5 to 19.0 which is the middle value. Further, the mode or most frequent value has also reduced from 35.0 to 18.0. The standard deviation has also reduced from 9.59 to 6.65 minutes that indicates service time is more consistent and there is less variation as compared to previous situation. This is evident is minimum and maximum values as well as range. The range of service time has reduced from 40.0 minutes to 24.0 minutes (Weisberg, 1992).
Further, the skewness has reduced from 0.70 to 0.25 indicating reduction in extremely large values (of service time). However, in both cases, skewness is not that significant; mean and median are not very far apart in either case, hence, indicating less or nominal impact of outliers that can be ignored. Kurtosis has turned negative from being at 0.53 earlier. This indicates that there are fewer outliers now as compared to previous situation. This, in turn, means that the service time has become much more uniform with very few cases of extreme values (either very high or very low service time) (Garver, 1932).
Overall, it seems that the training has had desired impact on the service time in Gourmet Delight Restaurant as seen from summary statistics above. The time has not only reduced, but the variation has also reduced leading to fewer extreme service times, either very high or very low.
‘Descriptive statistics’ tool available in MS-Excel was used in order to generate the summary statistics. The result is as follows:
‘Histogram’ in ‘Data Analysis tools’ in MS-Excel was used in order to generate the two histograms as follows:
| Prior | Frequency | | After | Frequency |
| 10 | 0 | | 10 | 3 |
| 16 | 0 | | 16 | 7 |
| 21 | 0 | | 21 | 8 |
| 26 | 2 | | 26 | 6 |
| 31 | 3 | | 31 | 5 |
| 36 | 6 | | 36 | 1 |
| 41 | 7 | | 41 | 0 |
| 46 | 5 | | 46 | 0 |
| 51 | 3 | | 51 | 0 |
| 56 | 2 | | 56 | 0 |
| 61 | 1 | | 61 | 0 |
| 66 | 1 | | 66 | 0 |
| More | 0 | | More | 0 |
The histograms have been plotted using the same bins so as to make the two histograms comparable. It can be seen that the entire curve has shifted towards left in case of after training service time. This clearly indicates that the service time has reduced drastically.
Further, the uniformity in service time has increased as earlier, the service time ranged from 24 minutes to even 66 minutes, with maximum frequency being around 41 minutes. However, after training, the service time ranges from 10 minutes to 36 minutes with maximum frequency of around 21 minutes. Further, the histogram’s peak is flatter in nature with lighter tails, indicating consistency in service time.
This part compares the serving time six months later, post training. The majority of staff has left the restaurant, causing the owner to be a little concerned. Random service time was recorded as follows:
| Serving Time (6 months Late |
| 14 |
| 15 |
| 16 |
| 20 |
| 20 |
| 22 |
| 25 |
| 25 |
| 26 |
| 28 |
| 28 |
| 15 |
| 20 |
| 30 |
| 31 |
| 25 |
| 28 |
| 24 |
| 18 |
| 20 |
‘Descriptive statistics’ tool available in MS-Excel was used in order to generate the summary statistics. The result is as follows:
| Serving Time (6 months Late | |
| | |
| Mean | 22.50 |
| Standard Error | 1.18 |
| Median | 23.00 |
| Mode | 20.00 |
| Standard Deviation | 5.26 |
| Sample Variance | 27.63 |
| Kurtosis | -1.15 |
| Skewness | -0.10 |
| Range | 17.00 |
| Minimum | 14.00 |
| Maximum | 31.00 |
| Sum | 450.00 |
| Count | 20.00 |
| Largest(1) | 31.00 |
| Smallest(1) | 14.00 |
| Confidence Level(95.0%) | 2.46 |
The following presents a comparison of service time immediately after training and after six months:
| Serving Times after Training (minutes) | | Serving Time (6 months Late | | |
| | | | | |
| Mean | 20.00 | Mean | 22.50 | |
| Standard Error | 1.21 | Standard Error | 1.18 | |
| Median | 19.00 | Median | 23.00 | |
| Mode | 18.00 | Mode | 20.00 | |
| Standard Deviation | 6.65 | Standard Deviation | 5.26 | |
| Sample Variance | 44.28 | Sample Variance | 27.63 | |
| Kurtosis | -0.86 | Kurtosis | -1.15 | |
| Skewness | 0.25 | Skewness | -0.10 | |
| Range | 24.00 | Range | 17.00 | |
| Minimum | 10.00 | Minimum | 14.00 | |
| Maximum | 34.00 | Maximum | 31.00 | |
| Sum | 600.00 | Sum | 450.00 | |
| Count | 30.00 | Count | 20.00 | |
| Largest(1) | 34.00 | Largest(1) | 31.00 | |
| Smallest(1) | 10.00 | Smallest(1) | 14.00 | |
| Confidence Level(95.0%) | 2.48 | Confidence Level(95.0%) | 2.46 |
The summary statistics do not indicate anything of concern as the statistics are improving, even when mean time has crept up a little. The range and standard deviation continues to reduce further, indicating more and more consistency in service time. The same analysis is reflected through reduced skewness and kurtosis. This indicates staff is getting trained and using the acquired skills so as to improve service time.
The staff may be leaving due to getting other opportunities in the industry due to improved skills acquired through training. Alternatively, it may be normal attrition for the restaurant.
The owner has decided to increase the advertising budget in order to increase clientele. For this various promotion schemes have been started such as, buy one get one etc. The monthly advertising budget and corresponding revenue is as follows:
| Monthly Revenue ($) | Advertising Expenditure($) |
| $ 2,000.00 | $ 350.00 |
| $ 4,500.00 | $ 450.00 |
| $ 2,845.00 | $ 300.00 |
| $ 3,700.00 | $ 250.00 |
| $ 5,800.00 | $ 325.00 |
| $ 1,900.00 | $ 250.00 |
| $ 6,800.00 | $ 900.00 |
| $ 10,000.00 | $ 1,200.00 |
| $ 7,400.00 | $ 850.00 |
| $ 3,200.00 | $ 450.00 |
| $ 2,800.00 | $ 400.00 |
| $ 3,250.00 | $ 340.00 |
| $ 2,478.00 | $ 280.00 |
| $ 1,999.00 | $ 200.00 |
| $ 3,650.00 | $ 360.00 |
| $ 2,850.00 | $ 320.00 |
| $ 2,973.00 | $ 250.00 |
| $ 1,798.00 | $ 180.00 |
In order to understand the impact of increased advertising budget on the revenue, Regression analysis was performed using ‘Regression’ available in ‘Data Analysis’ tool of Microsoft-Excel. The result was as follows (Freedman, 2005):
It can be seen that correlation coefficient is high at 0.92, indicating strong linear relationship between the revenue and advertising expenditure. The coefficient of determination is 0.84, which indicates that almost 84% of change in variable (revenue) can be attributed to the other variable (advertising expenditure). Additionally, the F is larger than Significance F, indicating that the null hypothesis can be rejected and regression variables are not 0.
The regression equation can be explained as: y = 717.75 + 7.45x, where y is monthly revenue and x is advertising budget.
This indicates that the owner is not misplaced in increasing the advertising budget as it clearly helps to impact the monthly revenue in a positive way. As the budget increases, monthly revenue also increases, as seen in following plot:
Part C (a)
This part is focussed on forecast and trend in patronage of the Gourmet Delight Restaurant. The patronage or turns per table for the month of August have been provided as follows:
| August | | Number of people who were served a meal |
| 1 | Tues | 55 |
| 2 | Wed | 60 |
| 3 | Thur | 67 |
| 4 | Fri | 102 |
| 5 | Sat | 110 |
| 6 | Sun | 69 |
| 7 | Mon | 62 |
| 8 | Tues | 65 |
| 9 | Wed | 58 |
| 10 | Thur | 68 |
| 11 | Fri | 110 |
| 12 | Sat | 116 |
| 13 | Sun | 70 |
| 14 | Mon | 59 |
| 15 | Tues | 66 |
| 16 | Wed | 50 |
| 17 | Thur | 78 |
| 18 | Fri | 115 |
| 19 | Sat | 100 |
| 20 | Sun | 80 |
| 21 | Mon | 60 |
| 22 | Tues | 62 |
| 23 | Wed | 54 |
| 24 | Thur | 77 |
| 25 | Fri | 120 |
| 26 | Sat | 100 |
| 27 | Sun | 85 |
| 28 | Mon | 69 |
| 29 | Tues | 60 |
| 30 | Wed | 68 |
| 31 | Thur | 70 |
Using this data, the ‘Exponential Smoothing’ tool in ‘Data Analysis’ tab of Microsoft excel was used. A simple exponential smoothing has been done in order to arrive at patronage forecast as follows:
| August | | Number of people who were served a meal | α = 0.10 |
| 1 | Tues | 55 | #N/A |
| 2 | Wed | 60 | 55.00 |
| 3 | Thur | 67 | 55.50 |
| 4 | Fri | 102 | 56.65 |
| 5 | Sat | 110 | 61.19 |
| 6 | Sun | 69 | 66.07 |
| 7 | Mon | 62 | 66.36 |
| 8 | Tues | 65 | 65.92 |
| 9 | Wed | 58 | 65.83 |
| 10 | Thur | 68 | 65.05 |
| 11 | Fri | 110 | 65.34 |
| 12 | Sat | 116 | 69.81 |
| 13 | Sun | 70 | 74.43 |
| 14 | Mon | 59 | 73.99 |
| 15 | Tues | 66 | 72.49 |
| 16 | Wed | 50 | 71.84 |
| 17 | Thur | 78 | 69.65 |
| 18 | Fri | 115 | 70.49 |
| 19 | Sat | 100 | 74.94 |
| 20 | Sun | 80 | 77.45 |
| 21 | Mon | 60 | 77.70 |
| 22 | Tues | 62 | 75.93 |
| 23 | Wed | 54 | 74.54 |
| 24 | Thur | 77 | 72.48 |
| 25 | Fri | 120 | 72.94 |
| 26 | Sat | 100 | 77.64 |
| 27 | Sun | 85 | 79.88 |
| 28 | Mon | 69 | 80.39 |
| 29 | Tues | 60 | 79.25 |
| 30 | Wed | 68 | 77.33 |
| 31 | Thur | 70 | 76.39 |
The above forecast is conducted at an alpha of 0.10 or damping factor of 0.90. From the graph above, it can be seen that the forecast is cyclical in nature where it peaks around weekends and troughs around weekdays. This holds true for both the original series in blue and the forecasted series in orange. The orange series shows less dramatic fluctuations due to exponential smoothing but the pattern remains the same (NIST, 2018).
The data for restaurant takings was provided on a quarterly basis for four years from 2014 till 2017.
| | | tn | Restaurant takings ($'000s)(yi) | Forecasted yi |
| 2014 | 1 | 1 | $ 36.77 | 32.92 |
| | 2 | 2 | $ 31.85 | 34.24 |
| | 3 | 3 | $ 33.84 | 35.57 |
| | 4 | 4 | $ 36.56 | 36.89 |
| 2015 | 1 | 5 | $ 38.12 | 38.22 |
| | 2 | 6 | $ 34.41 | 39.54 |
| | 3 | 7 | $ 38.00 | 40.87 |
| | 4 | 8 | $ 41.13 | 42.19 |
| 2016 | 1 | 9 | $ 47.90 | 43.52 |
| | 2 | 10 | $ 42.00 | 44.85 |
| | 3 | 11 | $ 50.30 | 46.17 |
| | 4 | 12 | $ 55.69 | 47.50 |
| 2017 | 1 | 13 | $ 58.83 | 48.82 |
| | 2 | 14 | $ 48.81 | 50.15 |
| | 3 | 15 | $ 46.00 | 51.47 |
| | 4 | 16 | $ 45.50 | 52.80 |
The same was plotted in a line chart and linear trend line was added to the chart. The equation for trend analysis was y = 1.3253x + 31.592. This equation has been utilised to calculated forecasted restaurant takings for the given quarters. The ‘x’ denotes the period or number o quarter which ranges from 1 till 16. The y indicates forecasted restaurant takings, as calculated (Immerwahr, 2004).
It can be seen that there is an upward trend for restaurant takings as indicated by the black trend line.
Login
Pages: 4
Words: 890