CIS 2200 Excel Project
You will be provided with a spreadsheet file named 'excel_project.xlsx'. This will contain all the raw data. Use
this file as your workbook to find out all your answers.
Once opened in Excel, you should have single spreadsheet with rows of data organized under the following
columns: ID, YEAR, ITEM, MONTH, QUANTITY, RATING, COMMENT
Each row of data represents the sales quantity of a particular item for a particular year and month.
Additionally, each row of data starts with a Group ID number and data about the transaction rating and any
additional comment.
You are completely free to use any Excel functionality (Pivot Tables, Formulas, and even charts/graphs) that
you have learned either in class or in SAM or on your own. There is no need to use Excel VBA for this
project.
When you analyze this data, and figure out answers to the project questions, you should only concern yourself
with the rows of data where the ID column matches your Group ID number and ignore all other rows.
Project Questions:
Q1. How many rows of data begins with your Group ID (100411)? Going forward, we will refer to this set of
rows as your personal data set.
Q2. What is the oldest (or earliest) year that you have a record of in your personal data set?
Q3. Based on your personal data set, how many Perrier products were sold in total?
Q4. Aggregate sales quantity of a product is the sum of all quantities of product sold across all time. Overall,
some product had the most aggregate sales quantity. What is the aggregate quantity of this highest selling
product in your personal data set? (It is ok if there is a tie among multiple products having this same highest
quantity, because your answer concerns the quantity (a numeric figure) and not the particular product name)
Q5. In total, how many Coke products were sold in the month of May in your personal data set? Your answer
should combine the sales data for the month of May for all the years.
Q6. What is the sum of the sales quantities of Coke and Yoohoo for the month of Jan in your personal data
set? Your answer should combine the sales data for all the years.
Q7. In your personal data set, if there is a year in which there is abnormally high amount of sales, what year is
it?
Q8. In your personal data set, if there is a year in which there is abnormally low amount of sales, what year is
it?
Q9. Overall, in your personal data set, how many Snapple products were sold in the most recent 2 years? Your
answer should combine the sales data for all the months for these 2 years.
Q10. Suppose all the quantity data for Coke was entered incorrectly; they were reported as 2 times less than
they should have been. As an example, instead of correctly reporting 8 as the quantity, 4 was reported by
mistake. Correct the data in your personal data set, and then find how many actual Coke products were sold in
total in the month of Jan
Q11. Suppose all the quantity data for Yoohoo for the month of Dec was entered incorrectly; they were
reported as 43 less than they should have been. As an arbitrary example, instead of correctly reporting 71 as
the quantity, 28 was reported by mistake. Correct the data in your personal data set, and then find how many
actual Yoohoo products were sold in the most recent 5 years
Q12. Based on your personal data set, what is total number of records where either: (1) the rating is less than 4
and the comment is Broken or (2) the rating is greater than or equal to 4 and the comment is Expired?
========================================
You will be provided with a spreadsheet file named 'excel_project.xlsx'. This will contain all the raw data. Use
this file as your workbook to find out all your answers.
Once opened in Excel, you should have single spreadsheet with rows of data organized under the following
columns: ID, YEAR, ITEM, MONTH, QUANTITY, RATING, COMMENT
Each row of data represents the sales quantity of a particular item for a particular year and month.
Additionally, each row of data starts with a Group ID number and data about the transaction rating and any
additional comment.
You are completely free to use any Excel functionality (Pivot Tables, Formulas, and even charts/graphs) that
you have learned either in class or in SAM or on your own. There is no need to use Excel VBA for this
project.
When you analyze this data, and figure out answers to the project questions, you should only concern yourself
with the rows of data where the ID column matches your Group ID number and ignore all other rows.
Project Questions:
Q1. How many rows of data begins with your Group ID (100411)? Going forward, we will refer to this set of
rows as your personal data set.
Q2. What is the oldest (or earliest) year that you have a record of in your personal data set?
Q3. Based on your personal data set, how many Perrier products were sold in total?
Q4. Aggregate sales quantity of a product is the sum of all quantities of product sold across all time. Overall,
some product had the most aggregate sales quantity. What is the aggregate quantity of this highest selling
product in your personal data set? (It is ok if there is a tie among multiple products having this same highest
quantity, because your answer concerns the quantity (a numeric figure) and not the particular product name)
Q5. In total, how many Coke products were sold in the month of May in your personal data set? Your answer
should combine the sales data for the month of May for all the years.
Q6. What is the sum of the sales quantities of Coke and Yoohoo for the month of Jan in your personal data
set? Your answer should combine the sales data for all the years.
Q7. In your personal data set, if there is a year in which there is abnormally high amount of sales, what year is
it?
Q8. In your personal data set, if there is a year in which there is abnormally low amount of sales, what year is
it?
Q9. Overall, in your personal data set, how many Snapple products were sold in the most recent 2 years? Your
answer should combine the sales data for all the months for these 2 years.
Q10. Suppose all the quantity data for Coke was entered incorrectly; they were reported as 2 times less than
they should have been. As an example, instead of correctly reporting 8 as the quantity, 4 was reported by
mistake. Correct the data in your personal data set, and then find how many actual Coke products were sold in
total in the month of Jan
Q11. Suppose all the quantity data for Yoohoo for the month of Dec was entered incorrectly; they were
reported as 43 less than they should have been. As an arbitrary example, instead of correctly reporting 71 as
the quantity, 28 was reported by mistake. Correct the data in your personal data set, and then find how many
actual Yoohoo products were sold in the most recent 5 years
Q12. Based on your personal data set, what is total number of records where either: (1) the rating is less than 4
and the comment is Broken or (2) the rating is greater than or equal to 4 and the comment is Expired?
========================================
