Mini Case (Make sure to read all sections before starting the assignment) Business Case Background You have a new job working at a consulting company (ExpertCon Inc.) that help solve a wide range of...

Mini Case
(Make sure to read all sections before starting the assignment)
Business Case Background
You have a new job working at a consulting company (ExpertCon Inc.) that help solve a wide
ange of business problems and challenges that your clients may have. Clients range from small
usiness to individuals seeking help in analyzing their data. The data may be captured from
different sources and in different format – in other words the data may not be complete or clean.
Your first week was a full week of training. The training mainly focused on how to resolve
analytical problems using Excel, and how to create a database in Access to manage and capture
future data.
During your second week, your manager Maria see’s that you are learning fast and thinks you
are ready to handle your first client. He gives you a
iefing of the client’s company background,
then he sends you an email cc’ing the client, informing them that you will be the focal point who
will handle the company’s portfolio.
Problem Description

In the email, you find the following problem description of your client:
Dear John,
We are happy that you will be our portfolio manager and excited to work with you on a number
of business-related problems that will help our managers and executives make more data-
supported decisions for the continued growth of our company and satisfaction of our clients. We
have a large amount of data that we continue to capture and which we are interested in analyzing.
Professor Dube
ie is a new lecture for University du Quebec a Montreal (UQAM). Due to his
PHD thesis, he has lost track of time and forgot to submit his student final grades to the
department. He heard about our consulting company that specialized in data analytics through a
colleague and decided to see if we can help him. He asked the chair of his department for an
extension for the grades submission and contacted us right away.
To submit the final grades, he needs:
1) all his student grades to be sorted in alphabetical order,
2) compute the weighted total for each student,
3) compute the grade of each student as follows:
Grade total
A+ 90
A XXXXXXXXXX-
A XXXXXXXXXX-
B XXXXXXXXXX-
B XXXXXXXXXX-
B XXXXXXXXXX-
C XXXXXXXXXX-
F XXXXXXXXXX-
4) He also asked if you could provide a statistical distribution of the student grades for his
class.
Best Regards
Andrew, Planning Division, Head
Data Set

In the email, you also see this data set as an attachment to the problem description:
First name Last name Student
number
Assign-
Ments (%)
Mid-term
exam (%)
Final
exam (%)
John Brown XXXXXXXXXX
Clement Albert XXXXXXXXXX
Robert Dessousa XXXXXXXXXX
Alex Carter XXXXXXXXXX
Paul Kennedy XXXXXXXXXX
Ke
y Lennington XXXXXXXXXX
Ching Jung XXXXXXXXXX
Ann Loyd XXXXXXXXXX
Jack Westbury XXXXXXXXXX
Eva Marquis XXXXXXXXXX
Albert Smith XXXXXXXXXX
John Nixon XXXXXXXXXX
Peter Sevigny XXXXXXXXXX
Janice Savage XXXXXXXXXX
Portfolio background

After speaking to your client Prof. Dube
ie, you discover that the data set he provided you is the
esults for the assignments, the mid-term and the final exam that are worth 20%, 35% and 45%
espectively. He thanks you for your quick response and is eager to hear from you soon.
Requirements

The problem presented here can have two levels of complexity in terms of analysis and
presentation. In other words, the criteria identified below will determine the effort put and the
quality and rigor it takes to complete the assignment.
Scenario A: Low Rigor Level
 Word file: ~1000 words
 Figures: 2 or more figures
 Tables: 2 or more tables
 Excel: Create cells; graphs; trendline; insert formula;
if-the-else function; insert some statistical functions such as average
explain graphs
Scenario B: High Rigor Level
 Word file: ~3000 words
 Figures: 4 or more figures
 Tables: 4 or more tables
 Excel: Create cells; graphs; trendline; insert formula; vlookup
if-the-else function; insert some statistical functions such as average;
identify intermediate steps in different sheets;
show decision making process; interpret results
 Access: Create tables; populate with data; relationships; forms; reports;
at least 3 SQL statements;
Guidelines

From your training, you remember that the solution procedure for each client will not be straight
forward with a set of steps to follow as each of their problems will be different. However, you
emember the basic guidelines your trainer taught you when looking for a solution:
1) Type the data in Excel
2) Organize your data with titles
3) Add tables, charts and functions if needed
4) Add columns and rows when needed to help your analysis (sums, averages, etc)
5) Structure your analysis so your client can understand how you came to the solution
6) Highlight the cell which contains the answer(s) to your client’s problem(s)
For Scenario B:
You were also trained on Access to create a database that will help your client better capture,
integrate and analyze future data sets. You remember that your trainer also gave you some
guidelines for creating an Access database:
1) Identify all fields needed in the data set (columns)
2) Start by creating your tables (database)
3) Create forms to help capture the data
4) Create relevant reports for your clients
Analysis Steps
Your manager made it clear and explicit that you need to document (with
ief explanation) the
steps you took in order to solve your client’s business problem (you can keep the steps short and
asic, you do not need a paragraph for each step):
1) …
2) …
3) …
4) etc.
Decision
You will need to explain to your client the solution you found for their problem (what was your
logic, the premise of your decision, your alternative solutions, the recommended solution and
why you recommended that solution). Your manager also asked if you could create a short
PowerPoint slide to help explain your decision to your client.