# Assignment: Excel Pivot Table (100 points)

### GET A 40% DISCOUNT ON YOU FIRST ORDER

Posted in Uncategorized

# Assignment: Excel Pivot Table (100 points)

### GET A 40% DISCOUNT ON YOU FIRST ORDER

Assignment: Excel Pivot Table (100 points)
Read the brief case below. Using the Excel spreadsheet provided, complete the questions.
Atlas Bikes
Atlas Bikes has been selling their bikes since 1975 in the United States. They expanded to Australia and Germany in the year 2015. The bikes are sold via a trusted network of sales outlets, which are not owned by Atlas Bikes. As a measure to improve their footprint in the market, Atlas sales wants to open new stores which will be owned by them. To do this, the Senior Management wants to review the numbers and come up with their expansion strategy.
1. Instructions: In the Excel spreadheet provided, complete the tasks below. Upon completion you will submit a single, completed, Excel workbook.
15 points for each of the following questions
2. Use the data in the sheet titled “Main” to calculate the total sales for US, Germany and Australia. What were the numbers recorded by United States in 2016 a. Create a new worksheet titled – “Q1_Total_Sales” and insert a new pivot table for the data in the sheet – “Main” b. List the total sales for all the 3 countries in this table c. The names of the countries show feature in the 1st column d. Split the dollar sales year wise. Format the numbers to reflect the “\$” symbol before the figures
3. The Management would like to know the split of the revenue figures logged by the Sales Outlets for 2015 and 2016. In the same sheet, display the same numbers as percentages of the total sales logged country-wise for a specific year. The management wants to know all the stores that logged less than 25% of the sales, country-wise for 2015 and 2016. Which store has logged sales less than 25% for both the years?
a. Create a new worksheet titled – “Q2_Outlets_Revenues” and insert a new pivot table for the data in the sheet – “Main” b. In the first column, list the countries first and then the Sales Outlets under them. c. Repeat Step a. and insert another pivot table in the sheet – “Question 2 – Outlets’ Revenues”. d. Display the revenues for the second pivot table as “% of Parent Row Total” e. Use conditional formatting to highlight the cells that are below 25%
4. June happens to be the busiest month in terms of the sales. For the year 2016, which are the top 5 products that they sold? What was the quantity sold for the number one product? a. Create a new worksheet titled – “Q3_Bestsellers” and insert a new pivot table for the data in the sheet – “Main” b. Add a filter for the year c. List all the products in the 1st column d. Filter out all the months except for June e. Sort the quantity in Descending order f. Use conditional formatting to highlight the cells for the Top 5 products
5. Management would like to know – how much revenue was contributed by each of the distribution channels. Drill down to the Product Categories. They want to use a slicer to filter the data, rather than an actual filter which is more cumbersome, for filtering the data country and year wise. What were the Internet Sales for United States for 2015? a. Create a new worksheet titled – “Q4_Channelwise_Sales” and insert a new pivot table for the data in the sheet – “Main” b. The Distribution Channel and the Product Categories should be listed in the 1st column c. Insert the “Revenue” twice, once for the numbers and secondly for the representation in percentages d. Insert a Slicer on Country and Year. Arrange the slicers in an appealing way.
6. Management would like to know – what is the split in terms of quantity, across the Product Categories, for each country for the year 2015 and 2016. Use a Line Chart to show the numbers. Rank the Countries in terms of quantity sold. a. Create a new worksheet titled – “Q5_Quantity_Sold” and insert a new pivot table for the data in the sheet – “Main” b. List all the Product Categories in the 1st column. c. Split the Quantity across the countries [ Hint: Drag the field Country in the “Column Label” area
d. Select the Pivot Table and Click on Options> Pivot Chart Insert a Line Chart of your choice
7. As the Sr. Data Analyst, you want to shorten the process of creating a particular Pivot table about the Quantities sold across the Product Categories, split by the countries. Create a Macro to perform this operation for you “on the fly”. Insert a button to run this macro. a. Create a new worksheet titled – “Q6_Macro” and insert a new pivot table for the data in the sheet – “Main” b. Name the Macro as “Q6Macro” c. Run the Macro from View>Macros>View Macros to verify. d. Add a New Button titled “Q6Macro” and link it to the Macro created. Place this button on the top right of the sheet titled – “Q6Macro” [ Hint: The Option to add a button is available in the “Developer” tab. If the Developer tab is not visible, go to File>Options>Customize Ribbon. Make sure that the ‘Developer’ option is selected as shown

### GET A 40% DISCOUNT ON YOU FIRST ORDER

Posted in Uncategorized