Final Group Project
Brief Explanation of Final Group Project
The best way to learn business analytics is through hands-on practice with Excel and data visualization
tools. As a result, the group project is a critical component of this course. The project will be done in
groups of 5 students.
Background:
The CGIC is one of the Canadian leading multi-line insurers that provides auto, home, life, and business
insurance. Currently, there are many instances in which a client submits a request for a quote; however,
the clients end up not purchasing the auto insurance. Because of the high number of web quote
requests, CGIC cannot attend to all the quotes. Therefore, the company wants to find a way to decide
which quotes should be followed up based on the tendency of the quote to become “bound.” CGIC
currently has auto insurance quotes gathered from the web quoting service. The company is interested
in investigating if the received client information can be used to predict whether a quote will be bound
or not.
The project will have two phases: 1) Phase-I: Data Exploration and Pre-processing and 2) Phase-II: Data
visualization using data visualization tools. You must create an Excel file for Phase 1 and a
presentation for Phase 2. Both the report and presentation will be graded. Phase II visualization will
build upon the cleaned data (Data Pre-processing) that would be completed in Phase I. In addition, it
must contain an executive summary of your final findings. Details and requirements about the project
are below. The output of the project will be presented to the class. Presentations are to be at most 15
minutes long, followed by a brief question-and-answer period.
Phase-I: Data Exploration and Pre-processing
Cleaning:
1) Understanding and Expressing the Problem Statement:
Start by understanding the context and purpose of the analysis. What is the objective of this
analysis? What questions are you trying to answer with this dataset?
2) Identifying Missing Values/Imputation:
Calculate the number of missing values for each attribute. You can use Excel or any other data
analysis tool to identify which attributes have missing data. Please use a 30% threshold. Handle
missing values: Decide on a strategy for dealing with missing data (e.g., imputation, removal of
rows/columns).
3) Outlier and Noise Detection:
Use different approaches, including visualization and statistical methods, or create scatter plots to
visually inspect potential outliers and noise values in any attribute. Handle outliers and noise values:
Decide whether to remove, transform, or impute these values.
4) Binarization and Discretization:
If needed, convert continuous variables into categorical variables based on business requirements.
5) Final Data Description:
Describe the final dataset in terms of the number of variables, data types, and the number of
records.
(Tips: Frequently save your working process and keep the newly created attribute next to the original
attributes)
After you think the dataset is clean:
1) Descriptive Statistics and Correlation Analysis:
After cleaning, calculate descriptive statistics (mean, mode, max, min, histograms etc.) for numeric
attributes.
2) Count Statistics for Nominal Attributes:
Generate count statistics for each category level of nominal attributes. Include the target attribute
("IS_BOUND") to understand class distribution.
3) Multi-dimensional Analysis:
Use pivot tables or cross-tabulations to analyze patterns related to the "IS_BOUND" target variable.
Identify which attributes seem to have the most impact on whether a policy is bound or not.
Throughout this process, document all the steps and decisions made, as well as the reasoning behind
them. This documentation is essential for the transparency and reproducibility of the analysis.
What worksheet tabs should be included in your Phase 1 report (Excel):
1. A tab that is the cover page with names of group members and the subject of the class
2. A worksheet tab that includes all the changes of the attributes. Use the first row to explain your
actions to the original attributes. Please don’t delete any original attribute. See below
demonstrations:
3. A tab that includes Descriptive Statistics and Correlation Analysis (graphs and very short
descriptions).
4. A tab that includes Count Statistics for Nominal Attributes.
5. A tab that includes Multi-dimensional Analysis by using pivot tables. (All pivot tables should have
table titles)
Evaluation Criteria (Excel Sheet):
Your report will be evaluated based on the following criteria:
The overall organization and flow of the report, ensuring that it is easy to follow.
A clear explanation of why the selected exportable attributes were chosen and how you changed
the original attributes.
Examine the extent to which the data cleaning process was thorough, including handling missing
values, outliers, and inconsistencies.
The use of various appropriate visualizations and the clarity of interpretations drawn from the
visualizations and their relevance to the problem statement.
The use of data pre-processing techniques, such as cross-tabulation or multi-dimensional
analysis, and their appropriateness for the problem.
The depth of insights derived from the analysis and whether they contribute to the overall
understanding of the subject.
Phase-II: Data visualization using data visualization tools.
In Phase 2 of this project, you will build upon the data analysis conducted in Phase 1. You will use data
visualization tools to communicate your findings effectively. Your goal is to create informative and
visually appealing presentations that convey the insights derived from the dataset.
1) Data Visualization Tools:
Choose appropriate data visualization tools. Here are some examples:
Tableau: A powerful data visualization tool that offers a range of visualization options.
Power BI: Microsoft's business analytics tool that allows for interactive and insightful data visualizations.
Excel Dashboard: A tool that collects business data into a singular page and makes reviewing important
aspects, such as key performance indicators, easier.
Select one tool based on your familiarity and the complexity of your visualizations.
2) Visualization Types:
Create a variety of visualization types to represent different aspects of the dataset. Consider:
Bar charts, pie charts, and histograms for distribution analysis.
Line charts for trends over time (if applicable).
Scatter plots for relationships between variables.
Heatmaps for correlation matrices.
Geospatial maps (if location data is available).
Each visualization should serve a specific analytical purpose.
3) Interactivity:
If your chosen tool allows for interactivity, utilize it to enhance the user's understanding of the data. For
example, interactive filters, tooltips, and drill-down options can be valuable.
4) Aesthetics and Clarity:
Ensure that your visualizations are visually appealing, well-labeled, and easy to understand.
Use color effectively but avoid excessive use of colors that may confuse the audience.
5) Narrative Storytelling:
Craft a narrative that guides the audience through your findings. Explain the "story" your data tells.
6) Audience Consideration:
Consider your audience. Who will be viewing your presentation, and what level of technical expertise do
they have? Tailor your explanations accordingly.
What sections should be included in your Phase 2 Presentation (no more than 15 mins):
(You can either present the result in PowerPoint slides or within the visualization software or both.)
1. Introduction: Who are the members in your group. Briefly summarize the project's objectives and
the dataset.
2. Data Overview: Present basic statistics and insights from Phase 1.
3. Key Findings: Highlight the most significant discoveries and patterns.
4. Visualizations: Showcase your visualizations, explaining each one and its relevance.
5. Interpretation: Analyze and interpret the visualizations to answer the project's questions.
6. Conclusion: Summarize the project's insights and their potential implications.
7. Recommendations: If applicable, provide actionable recommendations.
Evaluation Criteria (Presentation):
Your presentation will be evaluated based on the following criteria:
Clarity and effectiveness of visualizations.
Depth of analysis and insightfulness.
Presentation structure and storytelling.
Appropriateness of data visualization tools.
Documentation quality.
Overall professionalism and adherence to project requirements.