Excel Sales Analysis and Reporting Guide
Translated by
ScribdTranslationsExcel Sales Analysis and Reporting Guide
Translated by
ScribdTranslationsEXCEL TEST
With the provided database, carry out the following exercises:
1. Build a summary pivot table with the sale, contribution, and margin per month by
client, ordering by annual sales of the customer from highest to lowest.
a. Indicate which client has the highest annual sales?
Indicate which client had the highest sales in the month of March?
c. Build a pivot table, similar to the previous one, using only the data from the line
from the office bookstore, month of February.
d. Create a bar chart to represent the sales for each month of the company.
e. What is the total sales and accumulated margin for the year of the company?
2. Using the data from the Sales sheet by Client:
Based on this data, classify the customers as 'Valuable' and 'Not Valuable'.
Valuable Client: buy at least 2 lines of products during the period and have one.
accumulated sales greater than S/.2,000.
b. How many valuable clients does the company have?
3. Using the tools you know. Indicate which provider gives us a greater
annual contribution? with which brand?
a. How many suppliers represent 80% of the company's sales? Graph them in a...
pie chart.
b. How many products were sold month by month?
c. How many different products were sold month by month?
4. Copy the 'sales by customer' sheet and apply a filter, showing only the rows that contain the
sale of the computer supplies line with sales exceeding $500 and a margin between 10% and
20%.
a. How many rows are there? Show the data.