Interpret the general trend of the scatterplot and explain how much on average the cost of advertising changes for each dollar increase in the price of a listing

Interpret the general trend of the scatterplot and explain how much on average the cost of advertising changes for each dollar increase in the price of a listing

You may add extra sections to this template, change the visual style including font, colour, you can add a company logo, extra figures, tables, etc., as you see fit, however the questions must be answered ineither the Report Body or Appendix, as indicated in this template, otherwise the answer will not be marked.

Please do not rearrange the order of the template or condense the appendices into the report.  There needs to be two distinct sections (Report + Appendices) to replicate a business report.

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 given specific details as to what the report will contain.  Follow the length guideline (given on the previous page).

Highlight and delete this message before submission

Do Not Start The Report Body On This Page (Loss of 3 marks)

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.  

You may use subsections, tables, graphs etc.   You can also use bullet points to report key findings,providing you discuss any relevant points you wish to make.  Clearly presented information will help to earn maximum presentation marks.

Do not include numerical calculations or spreadsheets – these should be placed in the Appendices.

Do quote quantitative results.

Do include graphs when requested only.

You should have no more than 2 pages of writing (figures and tables excluded)

(12 Marks) Appendix 1 Discussion Points <replace with an appropriate title>

Loss of Marks: will occur if you use the words: mean, median, symmetric, skewed, boxplot, standard deviation, variance, Q1, Q3, IQR, outliers and kurtosis (or any variant of these).  Use non-specialist terms – i.e. everyday language.

Describe in 2-3 sentences, the aim of the analysis in this section including the variable of interest to the CLO.                                                          2 marks

Interpret the chosen measures of central tendency and dispersion in non-technical terms for each of the listing types. (Appendix 1 (b))                                                                                    3 marks

Discuss any unusual prices per stay in each listing type. If Q!-Casa sets up a minimum and maximum price per stay for each listing type, then what would be a pricing bracket guideline? (Appendix 1 (c))                     3 marks

Using the analysis from Appendix 1(d), discuss whether there are any obvious similarities or differences in the typical price per stay across the three types of listings. Is there a particular listing type that they should focus on?  Hint: compare the boxplots to obtain your answer. A bullet point discussion is OK.                                                            4 marks

(14 Marks) Appendix 2 Discussion Points <replace with an appropriate title>

Loss of Marks: will occur if you use the terms ‘decision variables’, ‘shadow price’, ‘objective’, ‘objective function’, ‘answer report’, ‘sensitivity report’, ‘binding’, ‘optimality’, ‘feasibility’  and ‘non binding’.  Use everyday language.

Describe in 2-3 sentences, the aim of the analysis in this section.           2 marks

Describe your linear programming solution in terms of maximum revenue for the total listings put on the website, and the optimum number of stays for each listings type, Classic, Designer and Luxury. (Appendix 2(b))             2 marks

If the revenue made from Luxury listings is increased by $20 for per stay then is the solution still optimal and what is the new revenue? (Appendix 2(c))                                   2 marks

Discuss whether all the allocated budget has been used up for cleaning costs. Based on your analysis, would it be possible to increase the budget? (Appendix 2(d))           3 marks

Must the CLO’s decision to sell at least twice as many Designer stays as Luxury stays be adhered to? Discuss briefly.  (Appendix 2(b))                                                                                  2 marks

Provide a recommendation as to whether the original (Appendix 2(b)) or the amended solution (Appendix 2(e) or (f)) should be pursued.                       3 marks

(15 Marks) Appendix 3 Discussion Points <replace with an appropriate title>

Loss of Marks: if you use the words: slope, intercept, regression, equation, correlation, coefficient of determination, R-squared, R², r, interpolation, extrapolation (or any variant of these).

Describe in 2-3 sentences, the aim of the analysis in this section.  Include any limitations you can think of with regards to the data set (Appendix 3(a)).                                               2 marks

Include here a scatterplot of Price per Listing ($) and Cost of Advertising ($) without the trendline, the regression equation or coefficient of determination.   Ensure the axes are properly labelled and you have an appropriate title that includes your network ID (Appendix 3(b)).              2 marks                                                                                                                               

Interpret the general trend of the scatterplot and explain how much on average the cost of advertising changes for each dollar increase in the price of a listing (Appendix 3(d)).       3 marks

Discuss the value of the R-squared coefficient of determination in everyday language and in relation to the variables Price per Listing ($) and Cost of Advertising ($). Explain whether the model is trustworthy to provide insight into predicting the average cost of advertising required based on price per listing (Appendix 3(e)).                                                                                         3 marks

Discuss your analysis of the average cost of advertising required for the average price per listing (Appendix 3(f)).                                                                                                                   3 marks

Discuss whether the model can be used reliably for a listing which is priced at $300. Include any possible issues with model. (Appendix 3(g)).                                                                      2 marks

(15 Marks) Appendix 4 Discussion Points <replace with an appropriate title>

Loss of Marks: will occur if you use the words: statistically independent, statistically dependent, conditional, joint, marginal, complement (or any variant of these).

Describe in 2-3 sentences, the aim of the analysis in this section.           2 marks

Summarise and discuss, using either bullet points or a table, all probabilities calculated in Appendix 4(b) with a focus on a non-technical discussion relating the results to each listing type and the location in Eastern Europe.                                                                                     5 marks

Create a 100% stacked column chart of the data in Table 1 (Appendix 4) to include here, with an appropriate figure number and caption. Excel instructions are provided below.                 3 marks

By using the 100% stacked column chart provide one insight, quoting approximate percentages.                                                                                                                                                                                                                                                                                                2 marks

Use the 100% stacked column chart to support a brief discussion of your calculations in Appendix 4(c). Note the insights from the 100% stacked column chart should link to your probabilities calculated in Appendix 4(c). Does the preference of Luxury stays depend on the location in Eastern Europe?             3 marks

Excel instructions to create a 100% stacked column chart

To create the 100% stacked column chart you need the data in the Excel file ‘Assignment 2 Data.xlsx’, which you can download from the Assignments page.   The worksheet for this question is called Appendix 4. 

Use EXCEL to obtain a 100% stacked column chart for the data from Table 2, with the three different listing types as labels shown on the horizontal axis.  The steps are as follows:

Step 1. Refer to Topic 6 in the Excel Booklet for instructions on how to obtain a 100% stacked column chart using data summarised in a table.

Step 2. To place Listing Type labels on the horizontal axis, further editing is required – see the instructions below.  Note! If you have a Mac and your Excel version is different to the instructions below, post on the social forum specifying which version of Excel your Mac has, and we can help you out.

Step 3.  Annotate the graph with an appropriate title for both axes. Ensure that the main title of your chart ends with your network ID.

Highlight and delete the above question text before submission

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

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 is the typical price of listings across Eastern Europe?

Before you begin! To complete this question, you need the data stored in the Excel file ‘Assignment 2 Data.xlsx’, which you can download from the Assignments page.

The worksheet for this question is called Appendix 1.  Further information about the data for Appendix 1 can be found in the Excel file.

The Product Specialists have scouted Eastern Europe after your preliminary report to the CLO. They have collected data on the prices per stay of three categories of listings, which they have classified as Classic, Designer and Luxury.Classic listings are the affordable listing type, while Designer is the middle range listing with some designer touches, and Luxury is the top class listing type with all the luxury one can need. The basis for the data collection is to inform the company about the price of listings and how Q! – Casa should set prices to be competitive. The CLO has asked you to describe each listing type and present information detailing the most typical price per stay and the variation in the prices per stay. This analysis will shape Q! – Casa’s pricing scheme because the CLO wants to implement an electronic pricing system which changes the prices daily to reflect changes in exchange rates and popularity. Therefore, to match current trends it is key to understand how to set daily prices so that each listing is not overpriced or underpriced.

The data collected contains 620 prices across the three listing types. The data can be found in the file Assignment 2 Data.xlsx’ (worksheet Appendix 1).  Use this data to answer the questions below.

Note: please do not include a printout of the data J (loss of 5 marks!)

(a) (2 marks) State the variable types of Price per Listing ($) and Listing Type (i.e. Quantitative (Continuous or Discrete) or Qualitative (Nominal or Ordinal)).

(b) (3 marks) Using Excel, obtain Descriptive Statistics, including Quartile 1, Quartile 3 and the IQR, of the price of each listing type (Columns A-C).  Based on the descriptive statistics, which measure of central tendency and dispersion would you use for each variable?  Explain briefly.

Round all output to the nearest cent using the button circled below:

For full marks include screenshots of each set of descriptive statistics here.

Windows Instructions: See the EXCEL booklet, Topic 7, Summary Measures, for instructions on producing a descriptive summary including the extensions include Quartile 1, Quartile 3 and the Interquartile Range (IQR).

Mac Instructions: varies depending upon whether you have the analysis toolpack.  Follow these steps to see which one applies to you:

1. Start by checking if you have the analysis toolpack:Go to Tools > Excel Add-ins.  In the window that pops up, if you see “Atp” select it and press OK.  You can now access the toolpack via Tools > Data Analysis.  Follow the Windows instructions which will work quite nicely J
2. Didn’t see “Atp” (the analysis toolpack)? You will need to install StatPlus (see video on Assignments page).  Once StatPlus is installed, see the 2010 EXCEL booklet (online) and go to the Mac section at the back. After producing the descriptive summary tables you will need to: (1) change the title of the table to include your network id; (2) Check whether there is the label Percentile 75% (Q2) in the table.  If so, change it to Q3 (yes, Q3– StatPlus has labelled it incorrectly – you can’t trust programmers!!! J).  If you don’t see it there’s no problem J

(c) (3 marks) By extending your analysis in part (b), determine whether any outliers exist in the data within the different types of listings. To determine the existence of outliers, use the 1.5xIQR Rule.  Round the values of Q1, Q3 and IQR to the nearest cent as in part (b) before performing the calculations.

You can perform the calculations either in Excel OR type them out in Word – either way make sure to show your full working and decision making process when you present your solution.

(d) (4 marks) On the next page is a set of side-by-side boxplots of Price per Listing ($) across the three different types of listings: Classic, Designer and Luxury.

Based on the boxplots below, use three bullet points in total to discuss:

(i) Whether there are outliers(s) present – explain how the boxplots below supports your argument in part (c).

(ii) The shape of the distributions (symmetric, skewed left or skewed right).  Support your discussion by using the boxplot to refer to the location of the median, whisker lengths and presence/lack of outliers. Also, refer to the Skewness and Kurtosis statistics in your answer.

(iii)  The location of the medians (lower, centre, upper end of box) and their impact on the distribution shape.

Note: do not present 3 different long-winded discussions – three bullet points summarising the boxplots will be sufficient.

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.

For full marks: before submitting your assignment annotate the boxplot below with an appropriate title that includes your network ID otherwise you will receive 0 marks for the boxplot.

TOTAL 12 MARKS

Please provide the requested non-quantitative analysis 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.

Maximising Revenue: how many should we sell?

Q! – Casa wants to investigate how to maximise their revenue by determining the optimal number of stays between the three types of listings, Luxury, Designer and Classic. The price Q! – Casa charges travellers for each stay is $80 for Luxury, $60 for Designer and $40 for Classic.

Q! – Casa has a budget allocated towards the cleaning costs for each stay to ensure the quality of the amenities and each listing reaches their respectable standards of Luxury, Designer and Classic. Luxury listings get all the fancy additions and require a cleaning fee of $50 per stay, while Designer and Classic listings each require a cleaning fee of $30 per stay. The total budget the CLO has allocated towards cleaning is $30,000.

There are limitations and requirements to how many Designer and Classic listings which Q! – Casa has on their website. The CLO said you can assume that for every listing of each type, a listing accommodates a total of five stays per month. One limitation is there are at most 84 Designer listings on the website. Also, one requirement is the company has to improve their listings and they want to restrict the number of Classic listings to at most 50, so they can continue to improve the Luxury listings.

In addition, another limitation is the number of stays physically possible to be bought in relation to the number of listings in the database. The CLO said you can assume that for every listing of each type, a listing accommodates a total of five stays per month with a current total of 200 total listings advertised.

Based on the success of the Australian listings, it has been identified that number of Designer stays sold is at least double the number of Luxury stays. Finally, the CLO has determined that for Q! – Casa to be profitable a minimum of 500 stays in total are required per month.

(a) (7 marks) Formulate a linear programming model for this problem, filling in the template over the page. This template is taken from the Week 4 lecture (slide 14).  Type up the full mathematical model in Word and include it here.

For full marks fill in the template provided over the page, clearly indicating:

• The decision variables. Define them precisely.
• The objective. Using your decision variables, formulate the objective function.
• The constraints. Using your decision variables, formulate these constraints.

Linear Programming Model Template

Decision Variables

<list and define the decision variables here>

Objective and Objective Function

<state the objective (min or max) and include the objective function here>

Constraints

<enter 1 constraint per line including a name for each constraint that contains your initials>

(b) (3 marks) Before you begin!  Part (a) needs to be completed first before entering the model in Excel or (i) you will likely get (b)-(e) wrong; and (ii) you’ll miss out on 7 marks!

To complete this question now you need to edit the Excel Linear Programming template in the assignment file‘Assignment 2 Data.xlsx’, which you can download from the Assignments page.

For full marks complete the following two steps:

1. Enter your model from part (a) in the template.All EXCEL output will need to bear your e-mail ID. To ensure this, you will need to save your EXCEL file as ‘E-mail ID Assignment 2.xlsx’ BEFORE you run Solver. In addition, your constraint names should begin with your initials, e.g. NFY Max Number of Listings.
2. Use EXCEL Solver to obtain a solution to the linear programming model from part (a), together with anAnswer Report and a Sensitivity Report.   In this Appendix, provide a screenshot of your solvedEXCEL spreadsheet and the Answer and Sensitivity reports.

Do not discuss the output, save this for the report body!

Hint #1: If the solution does not contain whole numbers, this is OK.  You will be asked to address this issue in the report write-up.

Hint #2: The Excel booklet, Week 4 lecture notes and Stevenson supplement (Lecture notes page) will also be useful to help you set up your Linear Programming model, as well as interpret the output and perform the analyses in parts (c) and (d).

(c) (4 marks) For the problem solved in part (b), state and interpret the range of optimality for each decision variable. For example,

1. “The range of optimality for Designer listings is (0,100), this means the revenue for each stay can be between $0 and $100 without changing the optimal number of Designer stays per month.”

(Note that this is not the actual answer!) Please keep your answers to 2 decimal places.

(d) (8 marks) For the problem solved in part (b), discuss the sensitivity report and interpret each constraint (excluding non-negatively).

For each interpretation state:

1. The shadow price;
2. The range of feasibility; and

iii. The impact of the shadow price on the objective function for changes within the range of feasibility.

An example of an interpretation:

 “The optimal solution requires all of the number of listings Q! – Casa to be used. The range of feasibility for the listing is from 1800 to 4000 and currently the value is 1800. For every extra possible listing, the maximum revenue will increase by $12.08.”  

(Note that this is not the actual answer!) Please keep your answers to 2 decimal places.

Explain in a single generalised statement what a zero shadow price indicates and why a resource would have a zero shadow price (you do not need to explain for each constraint with a zero shadow price nor calculate a range of feasibility).

(e) (3 marks) One of the budget constraints the CLO has given you is the total cleaning budget cost. If you increase the cleaning budget by $650, what is the new maximum revenue. Which of the Solver reports helps you answer this question? Show your calculations for the allowable increase in cleaning budget.  Based on the proposed change, will the solution in part (b) still be optimal?

Attach the new Answer Report ONLY, for the scenario in which the cleaning budget is increased by $650 and state the new maximum revenue.

Do not discuss the rest of the Answer Report – you will do this in the report body!

(f) (3 marks) Suppose the new revenue is $51,940. Using the original sensitivity report, calculate the amount of additional cleaning budget required to achieve this new revenue. (Please keep your calculations to 2 decimal places)

TOTAL 28 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.

How much should we be spending on advertising?

Before you begin! To complete this question you need the data stored in the Excel file ‘Assignment 2 Data.xlsx’, which you can download from the Assignments page.

The worksheet for this question is called Appendix 3.  Further information about the data for Appendix 3 can be found in the Excel file.

Q! – Casa is growing in popularity and the CLO has noticed your talent in the business especially when it comes to numbers. He really believes you create magic when you do your analysis. He has decided to get you working with the marketing arm of the business. The marketing team are currently playing the game of assigning advertising budgets to each type of listing daily from their expertise and past experience. Your task is to determine whether there is a relationship between the price at which a listing advertised and the cost of advertising incurred daily. The next step is to build a model which can predict the cost of advertising per listing based on the price per listing.

The marketing team at Q! – Casa have given you the data, capturing the price of each listing and the cost of advertising.  The spreadsheet has fallen into your Dropbox and you now need to perform the analyses requested below, to help with the model’s development.  Considering the marketing team is scared they will lose their jobs because of your skills with numbers, you need to make sure the analysis is performed well otherwise you’ll end up being demoted and moved somewhere very cold and isolated…

Please do not include a printout of the data J (loss of 5 marks!)

(a) (2 marks) Use EXCEL to calculate the average Price per listing ($) from the data using the average() function. For full marks screenshot your completed Excel calculation here.

(b) (5 marks) You are interested to predict Cost of Advertising ($) from the Price of listing ($). As a first step, use EXCEL to draw a scatterplot of Price of listing ($) vs Cost of Advertising ($).  Annotate the axes of this plot and include a title that is meaningful and contains your network ID.

Now compute the correlation between these two variables using the CORREL function (Topic 9 in the Excel Booklet). In this Word document, type the Excel calculation you performed to compute the correlation coefficient.  Now save a copy of this graph for the Report.

Using EXCEL add a trendline to the scatterplot and display the regression equation and coefficient of determination (R-squared value) (Topic 9 in the Excel Booklet).   Include a copy of the scatterplot with the trendline here (the scatterplot without the line will be used in the report as it does not contain mathematics, while the plot with mathematics will appear in this appendix J).

Comment briefly on the scatterplot and correlation coefficient and whether a linear model is appropriate for this data.

(c) (2 marks) State which is the dependent variable and which is the independent variable.

(d) (3 marks) Discuss the value of the intercept and whether it is meaningful. Also, what does the slope measure in this scenario?

(e) (3 marks) What is the value of the coefficient of determination (R-squared value)? Briefly discuss what the value means and whether the model is a good fit for the data.

(f) (4 marks) Use the simple regression equation from part (b) to predict the average Cost of Advertising required for the average Price per Listing (calculated in part (a)).  Comment on the accuracy of the prediction and discuss briefly.

(g) (4 marks) Use the simple regression equation from part (b) to predict the average Cost of Advertising required if the Price per Listing is $300. Comment on the accuracy of the prediction and discuss briefly.

TOTAL 23 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 below before submission.

Include full details of your working out in this appendix.

Q!- Casa: Where to set up?

You have investigated and calculated the optimal number of stays for each listing type, the average prices for each type of room in Eastern Europe and conducted an analysis on the relationship between how much should be spent on advertising costs based on the price of the stay. Now it is time to understand at which locations Q! – Casa should set up in Eastern Europe and whether there is a relationship between the price of a stay and location.

The following data has been categorised by listing types and location from the competitors. The data summarises a total of across five different locations, in which travellers were categorised to their listing type.  Use this data to compute the requested probabilities below and over the page.

Table 1: Listing Type by Location

(a) (1 mark) What data types are the two variables in Table 1?

(b) (8 marks) Using the data in Table 1, calculate the following probabilities.  For full marks include both the appropriate probability statement and the calculation.  Present your answer to 4 significant digits.  You donot need to write a sentence summarising the results.

1. What is the probability that someone stays in Croatia?

1. Given the location is Italy, what is the probability that someone stayed in a Luxurylisting?

iii. If the listing type is Classic then what is the probability someone stayed in Greece?

1. What is the probability that someone stayed in Croatiaand booked a Designer listing?

1. What is the probability that someone stayed in Czech Republicor they booked a Luxury listing?

(c) (6 marks) Using the data in Table 1, determine whether Location is statistically dependent on Luxury listings. For full marks, show all calculations and all relevant probabilities.

For all calculations include both the calculation and the appropriate probability statement.  You do not need to write a sentence summarising the results nor discuss the results. Present your answer to 4significant digits.