The assignment requires that you analyse a data set, interpret, and draw conclusions from your analysis, and then convey your conclusions in a written report. The assignment must be completed individually and must be submitted electronically in CloudDeakin by the due date. When submitting electronically, you must check that you have submitted the work correctly by following the instructions provided in CloudDeakin. Hard copies or assignments submitted via email will NOT be accepted.
The assignment uses the file DreamCruise dataset A3.xlsx which can be downloaded from CloudDeakin. The assignment focuses on materials presented up to and including Week 11. The Excel file which has been provided has different worksheets explaining and containing the DreamCruise dataset. For confidentiality reasons actual data has not been used in the assessment task. Following is an introduction to this scenario and detailed guidelines.
DreamCruise1 is an established holiday company that specializes in providing leisure cruises from various ports in Australia to islands in the South Pacific. Before the Covid-19 pandemic, DreamCruise was a thriving business with a loyal customer base. However, like many other companies in the tourism industry, DreamCruise has been severely impacted by the pandemic and has had to suspend its operations.
1 DreamCruise is a fictitious company and not meant to bear any resemblance to any existing company. All data and any individuals mentioned are fictitious and have been produced by the Unit team.
As the world looks forward to a post-Covid era, DreamCruise is now exploring ways to relaunch itself and rebuild its business.
Assume that you are a business analyst recruited by DreamCruise. You have received an email from Maria Rodriguez, DreamCruise’s Director of Analytics. Your response will be used as part of a report to the DreamCruise Board of Directors. Maria’s email together with guidelines (shown in blue) are presented below:
From: Maria Rodriguez, Director of Analytics, DreamCruise Subject: Analysis of the DreamCruise’s booking passengers dataset Hi …,
We are very happy with the strong interest and business generated from the DreamCruise’s booking passengers dataset. The Board wants a detailed understanding of some of the key aspects of the bookings. I have attached an Excel file with key data and included some guidelines (shown in blue) to direct your work.
Please provide answers to the following questions. Return the Excel file to me. As I have training in business analytics, I am comfortable with technical language. The Board wants a report from you which explains the outcome of your analysis. As they do not have the benefit of training in business analytics your report must present the results of your analysis in plain, straight-forward language. I have provided a template for you to use.
The Board is concerned about the average passenger satisfaction. It has been suggested at a recent Board meeting that the average Passenger Satisfaction for every Booking Type, is now less than 70. Does the data confirm this hypothesis?
To answer this question, you will need to conduct an appropriate hypothesis test for Passenger Satisfaction for each Booking Type.
Passenger Satisfaction is an important measure for DreamCruise, as it represents a major element of the company’s marketing strategy. Build a multiple regression model to predict Passenger Satisfaction. Your model should provide insights into which factors have a significant influence on passenger satisfaction, as well as the ability to predict Passenger Satisfaction for various scenarios.
For this analysis, you will need to build a multiple regression model using Passenger Satisfaction as the dependent variable. All other variables in the DreamCruise dataset should be included in the model, except ID, Age Band, Satisfaction Band, and Spending Band (i.e., exclude ID, Age Band, Satisfaction Band, and Spending Band from your regression model).
Follow the model building process introduced in the lecture and seminars. Carefully consider the following:
(i.e., Gender, Cabin, and Booking Type).
Copy the DreamCruise Dataset (excluding ID, Age Band, Satisfaction Band, and Spending Band from your regression model) to the “Correlation” sheet in the Excel file that has been provided (no earlier than Column W - be careful not to overwrite the Conclusion, Correlation Table and Scatter Diagram frames).
Complete the Dummy Variables Summary table which is in the Conclusion section of the Correlation worksheet. The table summarises the results of your transformation of categorical variables into dummy variables.
|Food and Drink
I look forward to receiving details of your analysis and your report. Sincerely,
The provided data file includes multiple sheets, labelled “Data Description”, “DreamCruise Data Set” and a worksheet for your dashboard. The “Data Description” sheet describes all the variables used in the “DreamCruise Data Set” and is copied below for your convenience.
|ID of the passenger making the booking (“Booking Passenger”)
|Age of the Booking Passenger
|Booking passengers have been allocated to one of five age bands: Young (<30 years), Core (30 – 45 years), Prime (46 – 59 years), Mature (60 – 69 years), Senior(>70 years)
|Gender of Booking Passenger: Male, Female, or Other/Prefer Not to Disclose
|Number of children included in the booking (included in Passengers)
|Number of passengers included in the booking (including children)
|Suite, Luxury, Porthole, Internal
|Solo, Double (couple or two friends travelling together), Group, Family
|Cost per passenger on Cabin (included in Total Spending)
|Spending per passenger on side trips
|Spending per passenger on travel insurance
|Food and Drink
|Spending per passenger on food and drinks
|Spending per passenger on entertainment
|Spending per passenger on merchandise
|Total Spending per passenger (sum of all cost/spending categories)
|Budget (<$2,500pp), Medium ($2,501 - $3,499), High ($3,500 - $4,499pp), Premium (>=$4,500pp).
|Passenger rating of satisfaction with their experience: 0 – 100 (lowest to highest)
|Unhappy (<50), Unimpressed (50 – 59), Acceptable (60 – 69), Happy (70 – 79),Delighted (>80)
The assignment consists of two parts.
Your data analysis must be performed on the Assignment 3 Excel file. The file includes tabs for:
When conducting the analysis, you need to apply techniques learnt in the lectures and seminars. The analysis section you submit should be limited to the Hypothesis Testing, Correlation, and Regression worksheets of the Excel file. These are the only worksheets which will be marked. Your analysis should be clearly labelled and grouped around each question. Poorly presented, unorganised analysis or excessive output will be penalised.
In the Conclusion section of each worksheet there is space allocated for you to write a succinct response to the questions posed in Maria’s email (above). When drafting your Conclusion, make sure that you directly answer the questions asked. Cite (state) the important features of the analysis in your Output section. Responses in the Conclusion section will be marked.
Use the Output section for your analysis to complete the analysis as directed in Maria’s email and supports your response to his questions (which you will write in the Conclusion section). Analysis in the Output section will be marked. Make sure your analysis and process complete, clear, and easy to follow. You may need to add (or widen/narrow) rows or columns to present your analysis clearly and completely. Poorly presented, disorganised analysis or excessive output will be penalised. It is useful to produce both numerical and graphical analysis. Sometimes something is revealed in one that is not obvious in the other.
Use the Workings section for calculations and workings that support your analysis. The Workings section will not be marked.
Having analysed the data, including answers (in technical terms) to the Data Analysis questions from Part 1 you are required to provide a formal report which can be placed before the DreamCruise Board of Directors. Assume that none of the directors on the Board have any training in statistics; they will only be familiar with broad generally understood terms (e.g., average, correlation, proportion, and probability). They will need you to explain more technical terms, such as quartile, mode, standard deviation, coefficient of variation, correlation coefficient, and confidence interval, etc.
In section 1 of the report a short interpretation of your findings to each question. In section 2 of the report, Make TWO (2) recommendations that the DreamCruise Board could consider to maximise Passenger Satisfaction. Your recommendations can be based on analysis in this assignment, analysis from previous assignments and any other analysis that you consider is relevant and adds impact to your recommendations.
Thoughts to consider in framing your recommendations include:
Make sure that all your recommendations are directly informed by your data analysis. Do not include any commentary that is not supported by your data analysis.
Highest marks will be awarded to students who draft distinct (i.e., different) recommendations, and whose recommendations take into account a broad range of (data-supported) considerations.
When exploring data, we often produce more results than we eventually use in the final report, but by investigating the data from different angles, we can develop a much deeper understanding of the data. This will be valuable when drafting your written report.
It is useful to produce both numerical and graphical statistical summaries. Sometimes something is revealed in one that is not obvious in the other.
You are allowed approximately 1,000 words (950 to 1,050 words) for your report. Remember you should use font size 11 and leave margins of 2.54 cm.
A template is provided for your convenience. Carefully consider the following points:
· Do not include any charts, graphs, or tables into your Report.
When you have completed drafting your report, it is a useful exercise to leave it for a day, and then return to it and re-read it as if you knew nothing about the analysis. Does it flow easily? Does it make sense? Can someone without prior knowledge follow your written conclusions? Often when re- reading, you become aware that you can edit the report to make it more direct and clearer.