Reference no: EM132390974

CCCS 280 Introduction to Computer Information Systems

Assignment 

Part 1 Excel Basics

This part of the assignment tests your knowledge of classic excel functions like sum, sumif, vlookup, hlookup, if, nested ifs, count, countif. Your task is to complete the information in the following arrays using the data provided in Part 1 Data. It may be necessary to perform some self-study in order complete some of these entries. Please be sure to carefully read the comments in each column. The comments can be shown/hidden by right-clicking and selecting the appropriate option. Good luck.

Part 2 Date and String Functions

This part of the assignment tests your knowledge of excel date and string functions like sum, sumif, vlookup, hlookup, if, nested ifs, count, countif. Your task is to complete the information in the following arrays using the data provided in Part 1 Data. It may be necessary to perform some self-study in order complete some of these entries.

Please be sure to carefully read the comments in each column. The comments can be shown/hidden by right-clicking and selecting the appropriate option. Good luck.

Part 3 Q1

What are the ten best-selling products based on quantity sold?

Part 3 Q2

What are the ten worst selling products based on quantity sold?

Part 3 Q3

Which referral source brings in the highest revenue?

Part 3 Q4

What’s the average order price (in dollars)?HINT: Look at the revenue for each order and then use the Excel AVERAGE function outside the pivot table

Part 3 Q5

What’s the average quantity sold per order?HINT: Look at the total quantity sold for each order and then use the Excel AVERAGE function in a separate cell outside the Pivot Table

Part 3 Q6

What color product was sold most often in the October, November and December?

Part 3 Q7

What color product was sold most often in the July, August and September?

Part 3 Q8

What are the two best months for sales in 2016 based on both total sales and quantity sold?

Part 3 Q9

The store is concerned that their Groupon promotion creates one-time customers that just use the Groupon and never come back. Is this true? While providing some evidence, explain why/why not.

Part 3 Q10

The most revenue is generated from products sold by which three vendors?

Part 3 Q11

What are the three most popular products (by quantity sold) from those three vendors?

Part 3 Q12

For those customers who bought Boxers, what percentage are married?

Part 3 Q13

For those customers who were referred to by GROUPON, what are the top three (bought the most quantity) income brackets? (Rank from highest to Lowest)

Part 4 The Time Machine

One cold evening in January, after enjoying your favourite class (CCCS 280), you are walking to the metro when a rather large man approaches you. In a thick Austrian accent he commands you, "Come with me, if you want to live." While being chased and shot by a man made of liquid metal, you are escorted through a door in the McGill metro and shown a small electronic device. The large Austrian man shoves the electronic device into your hand, wishes you good luck and suddenly you are transported naked through time to 1992.

Bewildered, you hear a strange older man with crazy white hair yelling, "Great Scott!" He throws clothes your way before explaining that you were transported back in time by a reformed Terminator to save humanity. Before you can even ask: a) why you've been chosen to be humanity's saviour and b) how you're supposed to do it, the stranger grabs the electronic device and begins to inspect it.

While putting on baggy overalls, you begin to shift from shock and disbelief to anger. You tie a flannel shirt firmly around your waist and demand that the stranger tell you who he is and why you're here. Sighing, the man introduces himself as Dr. Brown. He assures you that he will explain everything you need to know, but that time is of the essence and he needs to repair the device as quickly as possible. The device is a transporter and it's the only thing that can bring you back to your own timeline. He can fix it but needs a new flux capacitor. While Dr. Brown can create a new flux capacitor, he will need funds to buy parts. He recommends you use your knowledge of the future to find money.

Now, you've watched enough Star Trek and Dr. Who to know that fiddling with time has ethical and irreversible historical effects. However, Dr. Brown has already tuned out and is focusing intently on the device before you can voice your concerns.

You decide that you're going to use your knowledge of these new things called computers and the Internet to get a high paying job. You impress management at a local bank and they hire you as a business analyst. The only problem is that you are stuck using Excel 4.0 . . . which doesn't have all the bells and whistles you're used to.

Your task is to replicate the Pivot table of Part 3 Q13 without using any pivot table tools.

Attachment:- Assignment Details.rar