Delete this page before submission


Introduction
 
(3 marks)

Purpose
Provide a qualitative description of report contents/problems addressed in the report (covering Appendices 1-4) and what insights the analyses will provide.
Write this introduction after you have a clear understanding of the content of your report.
Ensure you have explained what the report will contain. 
Highlight and delete this question text before submission
 
[Click here and insert your text for the Introduction]
 
Do Not Start the Report Body On This Page
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Report Body
Highlight and delete this question text before submission
Use the questions below to guide your report-style discussion of the main results of your analyses in Appendices 1-4. 
 
Do not include numerical calculations or spreadsheets – Place these in the Appendices.
Do quote quantitative results.
Do include graphs where requested.
 
Use non-statistical terms to explain your analysis – i.e. use everyday language. Refer to the sections ‘Loss of Marks” below to understand what you are not allowed to say.
 
Discussion Guidelines for Appendices 1-4:  the points listed below should be discussed in your report body to help you earn maximum marks.  You are welcome to enhance your discussion as you like, with tables and/or graphs and using bullet points to report main points is also acceptable (providing you discuss any relevant points you wish to make).  Clearly presented information will help to earn maximum presentation marks.
 
(8 Marks) Appendix 1 What are the most important attributes of restaurants?
1. (2 Marks) Summarise the aim of this section including an explanation of the data to be analysed.
2.  (6 Marks) Discussion of the results from Appendix 1.
Discussion points:
Comment on any significant features of your 100% stacked column chart, copy of which should be included in the report body with an appropriate caption (e.g. Figure 1: …).
Use the results of your probability calculations to compare and contrast the most important attributes by the four age groups.
If Emma would like to run an advertising campaign highlighting that her restaurant serves Locally Sourced Food/Drink which age groups are most likely to become customers?
 
Loss of Marks: will occur if you use the words conditional/marginal/probability to discuss your proportions/percentages. 
 
 
 
 
(10 Marks) Appendix 2 What is the optimum recruitment strategy for Emma’s Restaurant?
1. (2 marks) Summarise the aim of this section.
2. (8 marks) Discussion of the results from Appendix 2.
 
Discussion points:
Describe your linear programing solution in terms of maximum potential employees reached and optimum sources of recruitment. Explain whether budget will be fully exhausted, and whether the expected totals will be reached.
Explain using Appendix 2 (c) whether, in total potential employees reached terms, there is any advantage to increasing the cap on the number of newspaper advertisements.
Explain using results from Appendix 2(d) the impact of increasing the overall budget.
Explain using the results from Appendix 2(e) the impact of increasing the budget allocation to internet and print advertisements.
Loss of Marks: will occur if you use the words decision variables, shadow prices, answer report, sensitivity report or binding.
 
 (8 Marks) Appendix 3 The impact of social media on putting diners in restaurant seats
1. (2 Marks) Summarise the aim of this section including an explanation of the data to be analysed.
2. (6 marks) Discussion of the results from Appendix 3.
 
Discussion points:
Include the annotated boxplots from Appendix 3 (c) in this report body and discuss each one in non-specialist terms. Do not use the words median/symmetric/skewed/mean – rather, interpret the boxplots for Emma in terms of the number of online reviews read before forming an opinion of the restaurant and what the boxplots are telling you.
Present and discuss the typical number of online reviews read by diners in Adelaide, Melbourne and Sydney, as well as whether there are any exceptionally low or high numbers of online reviews read by diners and how many there were.
Explain in everyday language, how the number of online reviews read by diners in Adelaide, Melbourne and Sydney compared in terms of variability.
 
Loss of Marks: will occur if you use the words mean/median/symmetric/skewed to discuss the boxplots. Using the terms Q1, Q3, IQR (or any variant of these) will also incur a loss of marks.
 
(8 Marks) Appendix 4 Online website visits versus the number of online bookings
1. (2 Marks) Summarise the aim of this section.
2. (6 Marks) Discussion of the results from Appendix 4.
 
Discussion points:
Produce a scatterplot ONLY in this report body of online bookings versus unique website visits. Do not fit the trendline and do not include the equation or coefficient of determination. Ensure the axes are properly labelled and you have an appropriate title that includes your network ID. Include a figure caption.
Interpret the scatterplot for Emma in terms of the relationship between unique website visits and online bookings.
For the regression model in Appendix 4 (b), interpret the values of the slope and R-squared coefficient of determination in everyday language and explain whether the model you have developed can be trusted to provide a believable explanation for the number of online bookings.
Using your results from Appendix 4(b) and 4(c) explain the predicted number of online bookings and the corresponding conversion rate.
 
Loss of Marks: will occur if you use any words related to regression, e.g. intercept, slope, correlation coefficient, R-squared coefficient of determination, regression model, linear. You will also lose marks if your regression plot in this report body contains the straight line, the regression equation or the coefficient of determination. You may however quote percentages.
 
… end of report body …
Highlight and delete the above question text before submission
[Click here and insert your report-style discussion of Appendices 1-4]
 
Do Not Include the Report Body On This Page
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Conclusions and Recommendation
 
(3 marks)
Conclusion: Summarise the main findings of your report:  base this on your discussion.
Do not introduce new information in the conclusion. Do not use direct quotes.  Indicate whether the report fulfilled the purpose as stated in the introduction.
Recommendation: Base these on your conclusion.  Do not introduce new information in the recommendation.  Present options for resolving the issue (purpose) presented in the introduction. Be brief – use dot points.
Highlight and delete this question text before submission
[Click here and insert the text for the Conclusion]
 
 
 
 

Appendix 1 - [Enter a suitable appendix name]
 
Highlight and delete the question text below before submission.
Include full details of your working out in this appendix.
What are the most important attributes of restaurants?
 
Before you begin! To complete this question you will need the data stored in Excel file called ‘Assignment 2 Data.xlsx’, which you can download from the Assignments page. The worksheet for this question is called ‘Appendix 1’.
Research you have conducted for Emma indicates that interest in fine dining experiences has risen in the last few years as the “degustation” movement continues to sweep the nation. Restaurants incorporating local foods and wines in their menus stand to benefit from this recent trend, and many already have. Patrons of all age groups are particularly eager to see local food options on the menu but also cited several other reasons for visiting a new restaurant. One such study in particular has caught your eye, as it reported results of a recent survey that explored the attributes of restaurants considered most important by patrons of four age groups (less than 25; 25-44; 45-64 and over 65) when considering their restaurant experience.
The study was based on a sample of 1200 individuals and the attributes of restaurants considered most important by the participants when considering their restaurant experience are listed below:
 
·         Ambience;
·         Value;
·         Reputation;
·         Locally sourced Food/Drink;
·         Service.
 
Results of the survey are summarized in Table 1 below.
 
Table 1: Most important attribute by age group

Age Group	Most important attribute					Total
	Ambience	Value	Reputation	Locally sourced Food/ Drink	Service
Less than 25	3	7	22	54	56	142
25 – 44	32	27	56	124	35	274
45 – 64	64	83	120	51	114	432
Over 65	94	92	85	34	47	352
Total	193	209	283	263	252	1200

 
 
 
 
 
 
(a)      (2 marks) Use EXCEL to obtain a 100% stacked column chart for the data from Table 1, with Age Group labels shown on the horizontal axis.
 
EXCEL Instructions:
Refer to Topic 6 in the Excel Booklet for instructions on how to obtain a 100% stacked column chart. As we want to have Age Group labels on the horizontal axis, further editing is required. Select the chart and then choose the ‘Switch Row/Column’ button from the design tab within Chart Tools:

Ensure that the title of your chart ends with your network ID (e.g. Most important attribute by Age Group bloggsj001).
 
(b)      (2 marks) What is the probability that a respondent selected Locally sourced Food/Drink as the most important attribute? Include both the calculation and the appropriate probability statement.
 
(c)      (6 marks) Given that the respondent selected Locally sourced Food/Drink as the most important attribute, what is the probability that the respondent is:
(i)             Less than 25?
(ii)           Between 25 and 44?
(iii)          Between 45 and 64?
(iv)         Over 65?
In each case, include both the calculation and the appropriate probability statement.
 
(d)      (8 marks) Repeat calculations from (b) and (c) for Service instead of Locally sourced Food/Drink.
 
 
 
TOTAL 18 MARKS
 
 
 
 
 
 
Please provide the requested non-Quantitative analyses in the report body.
 
Highlight and delete the above question text before submission
 
Appendix 2 - [Enter a suitable appendix name]
 
Highlight and delete the question text below before submission.
Include full details of your working out in this appendix.
    
 
 
What is the optimum recruitment strategy for Emma’s Restaurant?
 
Before you begin! To answer this question, you will need to create an Excel spreadsheet for your linear programming model.  You can find a template for a linear programming model online, in the file LP_Solver_template.xls available from the assignment page. Note that you will need to add more parameters/constraints to this template. The instructions below will help you fill in the spreadsheet. 
 
If you had to choose one, which would you pick: amazing food and poor service or average food and flawless service? Great restaurants consistently deliver both excellent food and service! Emma knows that fine dining establishments have complex menus and will require skilled culinary professionals to manage the quality of the menu and take charge of all kitchen operations. This will be essential for Emma’s new restaurant in particular if she takes up the option of the Chef’s table.
 
Emma has budgeted up to $6,000 for a recruitment strategy through traditional print advertisements (ads), internet advertising and recruitment agency ads.  The money will therefore be allocated among three sources of recruitment: print ads, internet advertising and local recruitment agency ads. Emma’s goal is to reach the largest possible audience (potential employees) through the various sources of recruitment. Emma would like to ensure that no more than $2,400 is spent on internet and traditional print advertising in a local newspaper. There are a number of other requirements for these advertisements. Emma has decided that the number of recruitment agency ads would be capped at 12 and the number of internet ads should be no more than 25. Similarly the number of print ads in the local newspaper should be at most 10. At least 6 combined internet and print ads will be placed to ensure that the recruitment campaign has a significant internet and print media presence.  
 
Table 2 below presents the number of potential employees reached per source of recruitment as well as the associated costs.
Table 2: Potential employees reached and cost per source of recruitment

	Recruitment Sources
	Recruitment Agency	Internet	Print
Potential employees reached per  source of recruitment	5,600	2,400	3,000
Cost per source of recruitment	$600	$200	$300

 
 
(a)   (6 marks) Formulate a linear programming model for Emma’s Restaurant. In doing so, clearly indicate the following:
·  What are the decision variables? Define them precisely.
·  What is the objective? Using your decision variables, formulate the objective function.
·  What are the constraints? Using your decision variables, formulate these constraints.
Write out the full mathematical model in your Appendix.
Note: Do not attempt to use EXCEL until you have formulated the mathematical model!
 
 
(b)   (4 marks) Use EXCEL Solver to obtain a solution to the linear programming model from part (a), together with an Answer Report and a Sensitivity Report. In this Appendix, provide a printout of your solved EXCEL spreadsheet and of the two Solver reports with your Appendix.   Do not discuss the output, save this discussion for the report body!
EXCEL Instructions for Solver: All EXCEL output should bear your e-mail ID. To ensure this, you will need to save your EXCEL file as ‘E-mail ID Assignment 2.xls’ BEFORE you run Solver. In addition, your constraint names should begin with your initials, e.g. JP_Budget.
Refer to Topic 5 in the Excel Booklet or the Linear Programming Supplement to the textbook (pdf file posted on the course website) for detailed instructions on how to set up your spreadsheet and use Solver. You may use the LP Solver template, available from the course website, if you wish.
EXCEL Instructions for Pasting Excel Worksheets into this Appendix: In EXCEL select the cells you wish to include in this Appendix and copy them (Ctrl-C in Windows, Command-C on a Mac). 
Now come back to Word and select Edit > Paste Special.  A new window should pop up with paste options.  If you are in Windows, select the picture option for pasting.  If you are on a Mac, you can use either the picture or pdf option (the pdf option is clearer).   Resize the pasted picture as you like.
 
(c)   (6 marks) For the problem solved in part (b), interpret all the non-zero shadow prices and their ranges of feasibility for all constraints except the non-negativity constraints.
 
(d)   (4 marks) If Emma increases the budget to $12,000, would the solution obtained in part (b) still be optimal? Which of the Solver reports helps you answer this question and how? How would the solution change, if at all? In the Appendix, explain briefly and attach, if appropriate, the new Answer Report ONLY. Do not discuss the output in the Answer Report, save this discussion for the report body!
 
(e)   (6 marks) Instead of increasing the budget, Emma is considering increasing the allocation to internet and print advertising to $3,000. Would the solution obtained in part (b) still be optimal? Which of the Solver reports helps you answer this question and how? How would the solution change, if at all? In the Appendix, explain briefly and attach, if appropriate, the new Answer Report ONLY. Do not discuss the output in the Answer Report, save this discussion for the report body!
 
 
 
TOTAL 26 MARKS
 
 
Please provide the requested non-quantitative analysis in the report body.
 
 
 
 
Highlight and delete the above question text before submission

Appendix 3 - [Enter a suitable appendix name]
 
Highlight and delete the question text below before submission.
Include full details of your working out in this appendix.
 
The impact of social media on putting diners in restaurant seats
 
Before you begin! To complete this question you will need the data stored in Excel file called ‘Assignment 2 Data.xlsx’, which you can download from the Assignments page. The worksheet for this question is called ‘Appendix 3’.
How many online reviews do you read before you form an opinion about a restaurant? In this question you will explore the distribution of the number of online reviews influencing dining decisions from diners in Melbourne, Sydney and Adelaide. A better understanding of the impact of social media will help inform Emma’s decision to when considering promotion and marketing of her new business.
(a)    (9 marks) Using EXCEL, make a histogram and obtain Descriptive Statistics, including Quartile 1, Quartile 3 and IQR, for Adelaide (column B), Melbourne (column C) and Sydney (column D). Do not include a printout of the data! Excel will behave very differently for this question depending on whether you use Windows or a Mac, so refer to the individual instructions below.
Windows EXCEL Instructions: Add your network ID to column names (e.g. Number of online reviews should become Number of online reviews bloggsj001).
See Topic 7, Summary Measures, in the EXCEL booklet for instructions on producing a descriptive summary.  You will need to extend your descriptive summary table to include Quartile 1, Quartile 3 and the Interquartile Range (IQR).  All instructions can be found in the EXCEL booklet.
See Topic 6 in the EXCEL booklet for instructions on how to produce and edit a histogram. Ensure each histogram has a meaningful title.
Mac EXCEL Instructions: the instructions are quite different for Macs – see the special section at the back of the Mac version of 2013 EXCEL booklet (online). Once you have produced your descriptive summary tables you will need to make the following changes: (1) change the title of the table to include your network id. For example, Variable #1 (Adelaide) should become Adelaide bloggsj001; (2) Change the label Percentile 25% (Q1) to Q1; (3) Change the label Percentile 75% (Q2) to Q3 (yes, Q3 – StatPlus has labelled it incorrectly – you can’t trust programmers! J).
(b)    (3 marks) Extend the analysis in (a) to include calculations for outliers using 1.5xIQR Rule. Indicate whether there are any outliers based on this rule, and whether the corresponding number of online reviews read by diners are exceptionally high or exceptionally low.
(c)    (8 marks) By referring to your output from (a) and (b) as well as the boxplots provided below, discuss the following features for each city (Adelaide, Melbourne, and Sydney):
(a)    The shape of each distribution.
(b)    Whether there are outliers(s) present – you can use your results in part (b). If there are outliers, use boxplots below to indicate the number of outliers present.
(c)    How the boxplots confirm your analysis.
 
Given the shape of the distributions, and for the purposes of making comparisons, which would be the single most appropriate measure of central tendency and dispersion? Simply state your choice and give a brief reason why. Do not provide an interpretation in this Appendix (you will do this in the report body).
 
 
Before submitting your assignment, annotate the boxplot below with an appropriate title that includes your network ID (e.g. Number of online reviews read before forming an opinion by City bloggsj001).
 
 
 
      

 
 
TOTAL 20 MARKS
 
Please provide the requested non-quantitative analysis in the report body.
 
 
 
 
 
Highlight and delete the above question text before submission

Appendix 4 - [Enter a suitable appendix name]
 
Highlight and delete the question text before submission.
Include full details of your working out in this appendix.
 
Online website visits versus the number of online bookings    
 
 
Before you begin! You will need data for this question.  In the file Assignment 2 Data.xlsx, you should see a worksheet called Appendix 4.  This worksheet contains the data for analysis.
Emma is considering an online marking campaign to promote her new restaurant. She decided to research the influence of online marketing on dining decisions. In particular Emma is interested in the proportion of unique visits to a website that result in an online booking.
In this question you will be working with data relating to 40 fine dining restaurants over a period of six months. For each restaurant, the number of unique visits to the website together with the number of resulting online bookings was recorded.
 
(a)   (5 marks) Use EXCEL to draw a scatterplot (no trendline) of Number of online bookings (100s) versus Number of unique website visits (1000s). Based on your scatter diagram, is it appropriate to fit a regression line in this case? Comment briefly and include a copy of your scatterplot in this section.
EXCEL Instructions:
Refer to Topic 9 in the Excel Booklet for instructions on how to obtain a scatterplot. Make sure that the title of your scatter diagram ends with your network ID (e.g. Online bookings vs unique website visits bloggsj001).
(b)   (6 marks) Use EXCEL to add a trendline and display its equation and R-squared value. Interpret the value of the intercept, slope and coefficient of determination. Include a copy of your scatterplot including a trendline in this section.
EXCEL Instructions:
Refer to Topic 9 in the Excel Booklet for instructions on how to add a trendline and display its equation and R-squared.
(c)   (5 marks) Use the simple regression equation from part (b) to predict the total number of online restaurant bookings if, in a six month period, Emma’s restaurant, attracts 110,000 unique visitors to her website. Comment on the accuracy of that prediction.
 
 
 
TOTAL 16 MARKS
 
Please provide the requested non-quantitative analysis in the report body.
 
 
 
 Highlight and delete the above question text before submission

代写 MATH 1053 - 定量分析作业