1. Who books online?

Highlight and delete this text before submission

Write a report-style discussion of the main results of your analysis in Appendix 1. You may use subsections, tables etc. as you see fit.

Do not include numerical calculations. These should be placed in Appendix 1.

Do quote quantitative results.

Currently bookings for The Treetop Retreat are made via phone or through a travel or tourism agent. George and Jane plan to update their internet website and are considering allowing guests to book online. They have come across a data summary from a recent survey that describes the method used by travellers to make their most recent holiday accommodation booking, broken down by age group.

Information obtained from another source suggests that members of Generation X are most likely to participate in nature-based activities (like staying at The Treetop Retreat), so George and Jane are interested in whether this booking method will appeal to those in this age group.

See the questions in Appendix 1 for detailed instructions on the analysis that is to be performed. In Appendix 1 there is a contingency table that summarises the methods used by 700 travellers to book their most recent holiday accommodation. Include the contingency table in your appendix, but not in your report.

Discussion in the report (12 marks):

• Introduce the aim of this section of the report.

• Include your 100% stacked column chart in your report, and comment on any significant features.

• Report the answers to questions (b)-(e) from Appendix 1 but do not include the calculations.

• Specifically discuss any differences between the different age groups for the data shown in the contingency table.

• Use the results of the probability calculations and your observations from the column chart to make some recommendations about whether George and Jane should pursue the option of allowing guests to book online.

Highlight and delete the above text before submission

[Click here and insert your report-style discussion of the contingency table data.]

2. Offering an incentive for early bookings

Highlight and delete this text before submission

Write a report-style discussion of the main results of your analysis in Appendix 2. You may use subsections, tables etc. as you see fit.

Do not include numerical calculations. These should be placed in Appendix 2.

Do quote quantitative results.

Bookings for The Treetop Retreat are made anywhere from a couple of days to several weeks in advance. To reduce the uncertainty, George and Jane are considering offering an incentive (for example, a lower nightly tariff or a complementary breakfast) to guests who book well in advance of their stay. George and Jane want you to analyse the impact on the number of days in advance that bookings are made if an incentive is offered. Detailed instructions are in Appendix 2.

Discussion in the report: (12 marks)

• Introduce the aim of this section of the report including an explanation of the data to be analysed.

• Use Excel to produce a histogram for each set of booking data. Include the histograms in your report. Briefly describe for George and Jane what the histograms show. Include an explanation for the choice of bin size.

• Interpret the appropriate measures of central tendency and dispersion in non-statistical terms and compare these values for the data before and after the incentive was introduced (based on your analysis in Appendix 2). What effect did the introduction of the incentive have on the number of days in advance that bookings were made?

Highlight and delete the above text before submission

[Click here and insert your report-style discussion of the matters addressed in Appendix 2.]

3. Advertising The Treetop Retreat

Highlight and delete this text before submission

Write a report-style discussion of the main results of your analysis in Appendix 3. You may use subsections, tables etc. as you see fit.

Do not include numerical calculations. These should be placed in Appendix 3.

Do quote quantitative results.

In the lead-up to summer, George and Jane have decided to embark on an advertising campaign through local media, specifically: community TV, newspaper ads, and two types of radio advertisements. To be effective, they think they should reach an audience of at least 175,000 people but would like the advertising campaign to cost as little as possible. The most George and Jane can afford to spend is $7500. George and Jane have some ideas on how they would like to spread their advertising among the four types of media.

See Appendix 3 for a detailed description of the problem. Model this optimisation problem as a linear program — instructions are in Appendix 3. Use the Solver in Excel to find an optimal solution.

Discussion in the report: (13 marks)

• Introduce the aim of this section of the report.

• Using mainly the Answer Report from Excel, describe the optimal solution. Your variable names will not appear here. Rather, you will make statements like: ‘To conduct an effective marketing campaign while minimising costs, The Treetop Retreat should purchase six minutes of TV advertising, …’.

• Comment on which constraints appear to determine the optimal solution (the binding constraints).

• For each constraint that is not binding, state and interpret the amount of slack.

Discuss the results of the other questions asked in Appendix 3.

• What is the effect on the optimal solution and on the overall cost of the advertising campaign if George and Jane are able to negotiate a lower price per minute of normal radio advertising?

• What action do you recommend George and Jane take if an additional newspaper ad block becomes available? Justify your answer.

• What is the effect on the optimal solution and on the overall cost of the advertising campaign if an audience of at least 200,000 is required?

• What is the optimal mix of advertising if the budget is limited to $6000? How many people will be reached by the campaign?

Highlight and delete the above question text before submission

Appendix 1 – [Enter a suitable appendix name]

Highlight and delete the problem text below before submission.

Include full details of your working out in this appendix.

Problem 1 – Who books online?

InternetAgentPhoneTotalGeneration Y (20-34)90655160Generation X (35-49)117749200Baby Boomers (50-64)826543190Seniors (65+)455748150Total334261105700

Table 1. Contingency table of age group versus accommodation booking method.

(a) (2 marks) Use Excel to obtain a 100% stacked column chart for the data in the contingency table above in which each column corresponds to an age group. Include the contingency table in your Appendix, but not in your Report.

Excel Instructions: Refer to Topic 6 in the Excel Booklet for instructions on how to obtain a 100% stacked column chart. Make sure that the title of your chart ends with your UniSA username, for example albar001.

Consider a person who has been randomly chosen from amongst these 700 travellers.

(b) (2 marks) What is the probability that the person used the internet to make their most recent accommodation booking?

(c) (3 marks) What is the probability that the person used the internet, given that they are Generation X?

(d) (2 marks) What is the probability that the person is Generation X and used the internet?

(e) (3 marks) Are ‘being Generation X’ and ‘used the internet’ independent characteristics for these 700 people? Explain your answer.

Please provide the requested non-quantitative analysis in Section 1 of the report body.

Highlight and delete the above problem text before submission

Appendix 2 – [Enter a suitable appendix name]

Highlight and delete the problem text before submission.

Include full details of your working out in this appendix.

Problem 2 – Offering an incentive for early bookings

Bookings for The Treetop Retreat are made anywhere from a couple of days to several weeks in advance. To reduce the uncertainty, George and Jane are considering offering an incentive (for example, a lower nightly tariff or a complementary breakfast) to guests who book well in advance of their stay.

George and Jane want you to analyse the impact on the number of days in advance that bookings are made if an incentive is offered. You have been able to obtain two sets of data from a similar accommodation venture that introduced an incentive scheme. The first set of data describes the number of days in advance that bookings were made before the introduction of the incentive scheme. The second set of data describes bookings information after the introduction of the incentive scheme. The data is available in the file Assignment2Data.xls.

Note: you are not required to undertake an assessment of whether this is a financially viable proposal.

All non-Excel specific calculations can either be typed in Word or performed in Excel. In either case, make sure to show your full working for full marks.

(a) (3 marks) Using Excel, produce a histogram for each set of booking data in Assignment2Data.xlsx. Use class intervals (bins) of width 7, that is, 0-6, 7-13, … . Ensure that each histogram has a meaningful title and axis labels (do not leave the horizontal axis label as ‘Bin’).

Excel Instructions: Refer to Topic 6 in the Excel Booklet for instructions on how to obtain and format a histogram. Add your UniSA username (eg albar001) to each histogram title.

(b) (3 marks) Using Excel, produce a descriptive summary table for each set of booking data in Assignment2Data.xlsx. Ensure that each table has an informative heading that includes your username. Extend the descriptive summary table to include Quartile 1, Quartile 3 and the Interquartile Range (IQR).

Excel Instructions: Refer to Topic 7 in the orange Excel Booklet for instructions on producing a descriptive summary. You should produce a table similar to that on page 44 of this booklet.

(c) (2 marks) Using Excel, extend the analysis to include calculations for outliers, using the 1.5 x IQR rule.

(d) (5 marks) By referring to the quantitative analysis in (a)-(c), discuss the following features of each data set: shape, modality, skewness, whether there are outlier(s) present and for what value(s) these outlier(s) occur.

(e) (2 marks) Based on this data, comment on any differences between the two distributions. Does it appear that the incentive scheme has increased the ‘typical’ number of days in advance that a booking is made? Is there any apparent difference between the amount of dispersion in each of the distributions?

Please provide the requested non-quantitative analysis in Section 2 of the report body.

Highlight and delete the problem text before submission

Appendix 3 – [Enter a suitable appendix name]

Highlight and delete the problem text below before submission.

Include full details of your working out in this appendix.

Problem 3 – Advertising The Treetop Retreat

In the lead-up to summer, George and Jane have decided to spend up to $7500 in local advertising. The money is to be allocated among four promotional media: community TV, newspaper ads, and two types of radio advertisements. TV and radio ads are purchased in minutes but may have several airplays of shorter duration (for example, 30 seconds) — this is determined by the TV and radio stations. Newspaper ads are sold in blocks, but smaller ads are possible.

To be effective, George and Jane think they should reach an audience of at least 175,000 people but would like to minimise the cost of the advertising campaign. The number of people reached and the cost for each advertisement is given in the table below.

Audience reachedCost ($)Newspaper (per block) 8900 275TV (per minute)7800345Radio, normal (per minute)3000100Radio, primetime (per minute)3500135George and Jane would like to use a variety of promotional media, and think that a minimum of ten minutes of TV ads and five minutes of each type of radio ad (normal and primetime) should be included. However, they want to avoid saturation of the airwaves and so want to limit their radio ads to 30 minutes in total. The number of newspaper ads is limited to six blocks due to available space.

(a) (5 marks) Formulate the linear programming model. Specifically:

• Determine the four decision variables required.

• Name the decision variables and write down clear and explicit definitions for them.

• Formulate the constraints.

• Write down a formula for the objective function.

• Include the full model in the appendix but not in your report.

Important: in part (a) you do not require, and should not use, Excel.

(b) (3 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. Important: make sure that you display at least two decimal places in your spreadsheet. For example, we do not want to round off answers to the nearest minute if they are not whole minutes. It is completely acceptable in the solution to have, for example, 6.25 minutes of a radio ad or a half-block newspaper ad. Provide a printout of your Excel spreadsheet and of the two Solver reports in your appendix. You will describe and explain the results in your peport.

Excel Instructions: All Excel output should bear your username. To ensure this, you will need to save your Excel file as ‘albar001 Marketing Strategy.xlsx’ BEFORE you run Solver. In addition, your constraint names should begin with your initials, e.g. AA_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. Make sure that you are using ‘Simplex LP’ in Excel 2010 as the Solving Method within the Solver. In Excel 2007 and earlier, this is equivalent to ‘Assume Linear Model’. Versions of Excel on the Mac may vary — if you are unsure, seek help. If the correct option is not selected, you will not get the correct Sensitivity Report.

You may use the LP Solver template, available from the course website, if you wish.

For parts (c) – (f), make each of your decisions based on the Answer Report and Sensitivity Report obtained in (b).

(c) (2 marks) George and Jane think that they will be able to negotiate a lower price per minute of normal radio advertising of $95. Will the solution obtained in (b) still be optimal? Without changing and re-solving the model, calculate by how much the advertising cost will decrease.

(d) (2 marks) If an additional block becomes available for newspaper advertising, should George and Jane take it? Make reference to the Shadow Price for the limit on newspapers ad blocks in your answer. Without changing and re-solving the model, calculate the effect on the advertising cost.

(e) (2 marks) George and Jane have been advised that, for a successful campaign, they should reach an audience of at least 200,000 people. Will the solution obtained in (b) still be optimal? Without changing and re-solving the model, calculate by how much the advertising cost will increase.

(f) (4 marks) Upon reviewing their budget, George and Jane have decided to only spend up to $6000 in local advertising. Explain why this means that their optimal solution from (b) is no longer feasible. George and Jane decide to reach as large an audience as possible (the minimum of 175,000 no longer applies), while spending no more than $6000 and complying with all other constraints from (a). This effectively changes the linear programming problem from one of minimising cost to one of maximising audience reach, with some changes to constraints. Make the necessary changes in your Excel worksheet and re-run Solver. Comment on the optimal solution in your report. There is no need to include an Answer Report or a Sensitivity Report in your appendix.