Data Analytics Project – MOS-1023 Fall 2021 (1) © Robert Pilling, CPA, CA
Data Analytics Project Winter 2022
Twenty years ago, 3 friends, Carmen Will, Yutong Wii and Mary Maykit decided to
create a wholesale food distribution company which distributes some very
specialized products in the food industry. They named their company Will Wii
Maykit Marketing, Ltd. (WWMML) They have marketed their specialty products
across the United States, and have developed a sizable business. 2 years ago, you
and a few of your friends were hired as analysts in WWMML. Things went really
well for the 1st year, but then things turned down in the last year. As a result, a
new CEO has been hired to try to turn the company into a more profitable
venture.
The new CEO is named Marvin Milburn. He is definitely an “old school” manager,
but he is becoming more adept at modern computer usage.
Marvin is wanting to set up a system which he can easily use to monitor both the
company’s sales throughout the country and the sales people employed in the
company. While he doesn’t utilize a computer much, (in fact, so far he has only
used one to generate paper reports for review) he is aware of some of the power
to analyse which can be utilized in Excel. He just took his first course on how to
use Excel on his computer and got excited. He said he saw an excel file on the
WWMML management system containing the 2021 sales data. He realizes there
is a full year of information in this data, but the way it’s laid out he is not getting
much information from it. He knows that you and your group are good at this
kind of analysis, because you took MOS-1023 and went through the data analytics
portion of it. Marvin expects you will be able to support each other through this
process. He wants you to go through the table of information he has seen and
perform the following four parts:
Data Analytics Project – MOS-1023 Fall 2021 (2) © Robert Pilling, CPA, CA
1. Clean Up The Data
A. Marvin was told that the very first thing when doing any kind of data
analytics is to vacuum the data. Or dry clean it. Or at least clean it
somehow. (He wasn’t really sure what term was used.) It appears that
there are some missing pieces of data, and maybe some other data records
which need to be corrected. You remember that your professor told you
(via his video on Excel) this would entail using the filter function on the
data, and going through each of the columns/rows of the data to see if
there are any anomalies. In most cases, you can figure out what the
anomaly should be changed to by various techniques. If there is nothing in
a field, check to see if there’s something similar to it from one of the other
fields of the other records selling that kind of item or for that price. Don’t
worry if you miss the odd error. Often the things which you miss will show
up when performing the data reporting part using pivot tables. If they do,
simply go back to the data, make the change and then update the
dashboard like they showed you in the series of three videos on YouTube
you watched as part of your MOS-1023 Data Analytics week viewing for
class. (It might be good to watch them again a second time, looking for
how they did the things you are being asked to do.)
B. Since you will be doing reporting by quarters and dates, you should ensure
that all the dates are actually in date format. Marvin also noticed that the
clerk they had doing the entries at the beginning of the year may have
forgotten that it was 2021. If any of the entries are dated in 2020, simply
correct the year on the date entry, and if necessary, the delivery date,
which is normally 3 days after the sale date (but not always).

Data Analytics Project – MOS-1023 Fall 2021 (3) © Robert Pilling, CPA, CA
C. Marvin has also heard a rumour that in the data there are actually some
incomplete entries. Somehow all these blank entries are still in the data,
and he would like you to remove these lines completely. They can be
identified because all of the amount fields are either “0.00” or “div/0”.
(There are extra shipping charges lines which should not be removed. If
there is an amount in the Shipping Fee column but not the Quantity or
Revenue, simply change both the Product Name and the Category fields in
the record to “Shipping”.) Do not be concerned if Unit Price times Quantity
does not calculate exactly to the Revenue. There were some entries where
premium prices were used and others where discounts were given.
D. Make changes to any types of records that you need to in order to ensure
that the records are grouped appropriately, and each sale is accurately
allocated to the appropriate product. There is no need to sort the original
data. Watch for incorrectly spelled names of individuals and regions and
change any spelling mistakes to the correct name. (Some of the data entry
people are not that good about being consistent in how they enter things.)
Creating The Dashboards
Marvin has said that he would like you to create 3 interactive dashboards
based upon underlying pivot tables. They should be readily available in the
workbook. (i.e. just before or after the data.) He wants to be able to work
with the three dashboards to give him information he needs for decision
making. They are to be divided into three separate dash boards with the
Excel tab name for them being, Region, Customer and Salesperson. (How
the sales are being analysed.) See the following descriptions of what each
should contain.
Data Analytics Project – MOS-1023 Fall 2021 (4) © Robert Pilling, CPA, CA
Dashboard 1 – Sales by Region
a. Marvin would like to know what the bestselling items are in each of
the regions; both in total and by region. (The 4 regions are North,
East, West, and South. Each record has the region included in one of
the columns unless the clerk forgot to enter the region. You should
correct that in the data cleaning stage.) He only wants to see the top 6
products in terms of total $ of sales for each product in the region(s)
during each quarter. He would like these presented in a bar graph
sorted by the total sales in that time frame. (Highest sales first.)
b. Another graph in the Region dashboard, should show the total sales by
region. Marvin would like to be able to see the total sales made in any
region summarized by product category. He would like to see the
report one region at a time but all in the same graph. (i.e. one graph
can show the North region, and by changing the slicer it can then show
the East, West or South regions.) Marvin has heard that using data
slicers as a separate part of the dashboard can make it very easy for
him to explore without having to know all the technical aspects of
Excel. Marvin would also like to be able to view the analysis by
quarter, one quarter at a time or in total for the year. The slicer for
which quarter is being displayed should control all of the graphs on the
same dashboard.
c. A third graph on this dashboard should show the average number of
units per sales transaction. They should be labelled by product name.
This should also be able to be broken out by region. (i.e. for each
product, take the total # of units sold divided by the number of sales of
that product. 1 line of data = 1 sales transaction.) Rank these from
Data Analytics Project – MOS-1023 Fall 2021 (5) © Robert Pilling, CPA, CA
lowest to highest for the top 4 products in terms of products per sale
transaction in the displayed region.
d. As the fourth graph on this dashboard, Marvin would like to see a pie
chart (he says he loves pie – but this is the only graph which should be
pie-shaped) showing the sales total by product category with the
dollar amount of sales (in $1,000s of dollars) shown on or beside each
wedge of the pie for the top 6 products sold by the company. (i.e. if
the total sales for a product in the selected region were $5,275,300 it
should say $5,275 and the graph would note somewhere, maybe in
the title description, that it is shown in $1,000 values.) Again, he
wants to be able to see this chart by quarter or in total for the year.
2. Dashboard 2 – Sales By Customer
a. Knowing that any company is only as good as their customers, Marvin
has asked for a 2nd dashboard where he can track the sales by
customer. He would like to know total sales by customer for each
quarter, and the percentage sales contributed by each customer. This
should be viewable in total or by region. It should be ranked from
highest to lowest for the largest 5 customers in terms of dollars of
sales.
b. A second graph beside it should show the average dollar amount
generated per item sold to each customer ranked by highest to lowest
average sale price. (Top 6 customers only.) The largest sales in a
quarter may be from different customers than those appearing in the
Data Analytics Project – MOS-1023 Fall 2021 (6) © Robert Pilling, CPA, CA
graph for the whole year (or for another quarter). Since it is for
comparative purposes, be sure to put it into a bar graph format.
c. On this dashboard, he would also like to see two more graphs. One
showing the monthly sales ranked by customer totals for the month
and the other being the quarterly sales ranked by customer. Again, be
sure to do both by using bar graphs. Only the top 10 customers in the
applicable time period should be displayed.
3. Dashboard 3 – Sales By Salesperson
a. Marvin is also concerned about how much each salesperson is
generating in sales. There are 11 salespersons in the company.
Marvin would like to see a graph showing total sales generated by
each person for the year. He would also like to be able to see on the
same graph an option (slicer) to show the total sales per salesperson
by quarter. In each instance, the sales per salesperson should be
ranked from highest sales to lowest sales with each bar on the graph
being a distinctive colour. (i.e. not all the same colour)
b. As a separate graph on this dashboard, Marvin would like you to
show the monthly sales for any of the individual salespersons. (i.e. a
slicer will allow you to select any of the salespersons, and it will then
show their month sales totals for the year.) The slicer to select a
sales person for display should ONLY affect this one graph.
Knowing that this will take some time, Marvin has asked that your group prepare
this analysis, including the 3 dashboards, with the underlying individual pivot
tables, by 6:00 PM on Friday, March 18th. It should all be in one uploaded Excel
file with supporting worksheets where the pieces of the dashboards were drawn
Data Analytics Project – MOS-1023 Fall 2021 (7) © Robert Pilling, CPA, CA
from displayed clearly in the tabs on the bottom of the worksheet. (i.e. Your file
should consist of the SIGNIN tab, followed by the Data tab which you have
cleaned, followed by the 3 dashboard sheets, followed by the supporting pivot
table sheets. DO NOT HIDE ANY SHEETS.)
Marvin is a stickler for aesthetics, so use colours to emphasize the outcomes of
your work, and organize the dashboards in a manner which is easy to follow.
There should only be one slicer for each category on each of the dashboards, and
it should control the outcomes of all graphs using that category which appear on
that dashboard.
Marvin is thinking of giving you and your team a raise in your salaries (marks)
based upon the outcomes of this project. It can be up to 12% of your whole
salary. He will have his Executive Vice President, Professor Pilling, set up a rubric
of the parts he feels are important and how much of your raise will be based upon
each. This will be posted on the OWL system underneath the project and data by
Saturday evening. In that rubric will be warnings to ensure all members of the
group have their accurate information entered into the SIGNIN tab prior to
generating the data. Double and triple check that they are accurate and that you
do not click on the box at the bottom to generate the data until it is complete.
Adding or changing information after generating the data will result in your
raise being denied. (i.e. you will get a mark of zero.)

Data Analytics Project – MOS-1023 Fall 2021 (8) © Robert Pilling, CPA, CA
Comments from the Executive Vice President:
Because Excel works a bit differently on an Apple computer than it does on a Windows based
computer, it is STRONGLY recommended that you use ONLY Windows based computers in
doing this project. IF you do not have access to a Windows based computer, you should
arrange to do your work on a computer in one of the Social Science computing labs. Also, there
have been issues with creating pivot tables in a shared document in past terms. It is
recommended that work on the project be done in series rather than simultaneously.
Keep copies of your files as you make changes in the event that something you do makes your
file inaccessible. In the past, some students have had issues with a file becoming inaccessible.
If this happens and you do NOT have another copy, you end up back at square one. It is
recommended that every hour or so, you make a duplicate copy of the file with a version
number (such as –02 appended to the name.) Along that line, another piece of advice which I
give to my students is that every time you have done enough work that you don’t want to do it
again, SAVE THE FILE. (Maybe make a version backup, too.) Occasionally, try opening a
recently saved file to ensure there will be no issues in opening the file you submit.
In the event that one or more of your group members are not participating fully, please send an
email to me and copy it to all members of your group. I will address it and marks may be
removed from any non-participating member’s mark.
NEVER add a person to your group after you have generated your data. See the warning in red
on the previous page.
On Fridays, the assignments dropbox can have some issues uploading due to volumes of
submission being made due on Friday afternoon. You are encouraged to upload your file
before the due date. Only one member of your group needs to upload the file and all
members of your group will be able to view the file that is uploaded. If you have a change of
mind, though, and want to upload a new version of your project after it has been uploaded, the
dropbox can have up to 10 resubmissions before the due date. Only the latest one will be
reviewed.
ENJOY AND LEARN FROM THE EXPERIENCE!