Ms Excel _ Assignment




Instructions:
1. Use the Excel file “Lab2Start v5.xlsx” for this exercise.
2. Make a copy of the worksheet data for your work. (Note: Do not copy the worksheet copy the data)
3. The first thing you need to do is to convert the data into an Excel table.
Add a total row for the table, and use the Sum aggregation to show the total of the Revenue column and then filter the data only for United States.
Q1. What is the total revenue for all the sales in the United States?
a) 101990988 b) 27975547 c) 24606895 d) 12494351
Now, you need to add several columns, derived from existing columns in the data.
First, let's add a "Month" column. Insert a new column to the left of the Customer ID column, and use formula to derive the month of sales from the Date column.
HINT: Use the Text() function
Q2. What is the total revenue for all the sales in the month of December?
a) 9086931 b) 8801313 c) 6809175 d) 6494188
Next, let's add an "Age Group" column. Insert a new column to the left of the Customer Gender, and use formula to derive the age group from the Customer Age column. Let's group the customers based on the following criteria:
Youth (<25)
Young Adults (25-34)
Adults (35-64)
Seniors (>64)
HINT: Use the nested IF() functions.
Q3. What is the total revenue for all the sales for Young Adults Age Group?
a) 56053751 b) 29918498 c) 30655614 d) 5556163
Now, let's add a "Frame Size" column. Insert a new column to the left of the Order Quantity, and use
formula to derive the frame size of a bicycle from the last two characters of the Product column,
when the Product Category is Bikes. Otherwise, leave it blank.
HINT: Use the IF() and RIGHT() function.
Q4. What is the total revenue for all the bikes with frame size 62 for the customer age group
seniors?
a) 22914 b) 125893 c) 159102 d) 12452
Last but not least, let's add a "Profit" column. Insert a new column to the right of the Revenue, and
use formula to derive the Profit from both the Revenue and Cost columns. Show the total for the
Profit column. Use the Sum aggregation in the total row of the table, for the Profit column.
HINT: Profit is Revenue less Cost.
Q5. What is the total profit for United States sales in the month of October 2015, for customer age
group Adults?
a) 111823 b) 142036 c) 148903 d) 138419


Pivot Table / Data Pilot exercise

Copy the file exped.xls and open it with MS Excel. This file is a sample of 1000 sales transactions for the
Expeditioner. For each sale, there is a row recording when it was sold, where it was sold, what was sold, how it was
sold, the quantity sold, and the sales revenue.

Use Microsoft's PivotTable to produce the following report:


Continue to use the Pivot Table to answer the following questions:



Comments

Post a Comment

Popular posts from this blog

Image
PRINCIPLES AND PRACTICES OF MANAGEMENT CASE DISCUSSION A TYPICAL DAY IN THE WORK LIFE OF NATALIE ANDERSON ORGANISATIONS (JAMES A. F. STONER) For most of our lives, we are members of one organisation or another- Family, college, church, business, cultural society, sports team etc. Organisation is two or more people who work together in a structured way to achieve a specific goal. All organisations, formal or informal, are put together and kept together by a group of people who see that there are benefits available from working together toward some common goal. Examples of organization in a Sentence She is the leader of an international organization devoted to the protection of natural resources. He has been working on the organization of his notes into an outline. She is responsible for the organization of the party. The new president plans to make changes to the company's organization. PURPOSE Purpose or goal is very basic, fundamental element of any org...
Read more
Image
MANAGEMENT COLLEGES  NIRF  RANKING 2018 Ministry of Human Resource Development  Government of India Management (Research and Teaching) About NIRF : The National Institutional Ranking Framework (NIRF) was approved by the MHRD and launched by Honourable Minister of Human Resource Development o n 29th September 2015. This framework outlines a methodology to rank institutions across the country. The methodology draws from the overall recommendations broad understanding arrived at by a Core Committee set up by MHRD, to identify the broad parameters for ranking various universities and institutions. The parameters broadly cover “Teaching, Learning and Resources,” “Research and Professional Practices,” “Graduation Outcomes,” “Outreach and Inclusivity,” and “Perception”. India Rankings – 2016 based on this framework were released on 4th April 2016. For India Rankings – 2017, the main ranking parameters remain the same. However, there are a fe...
Read more
Image
What is cloud computing? A beginner's guide _________________________________________________________________________________ Simply put, cloud computing is the delivery of computing services—servers, storage, databases, networking, software, analytics and more—over the Internet (“the cloud”). Companies offering these computing services are called cloud providers and typically charge for cloud computing services based on usage, similar to how you are billed for water or electricity at home. Still foggy on how cloud computing works and what it is for? This beginner’s guide is designed to demystify basic cloud computing jargon and concepts and quickly bring you up to speed. _________________________________________________________________________________ Uses of cloud computing You are probably using cloud computing right now, even if you don’t realise it. If you use an online service to send email, edit documents, watch movies or TV, listen to music, play games ...
Read more