You are a sales manager at Superstore Sales- one of Canada’s largest retail stores. You need to send a report to your manager containing a summary of sales for the Corporate customer segment. The dataset superstore_sales.xlsx is provided below. Most of the data headers are self-explanatory. However, for clarity, following are the meanings of a few data headers: Region: Analogous to a large state in Canada. Province: A district within Region. Product Base Margin: Profit margin on a product.
The final report is to be made for only the Corporate customer segment.
Section 1: Basic Formatting
- Increase the column widths to appropriate sizes so that they are readable.
- Format the header row in a suitable colour and put borders around the header row cells.
Section 2: Filtering
- Filter the data by customer segment.
- Create four new worksheets, one for each customer segment.
- Copy raw data of each customer segment into the respective worksheet.
- Now you’ll have four individual sheets, one for each customer segment. For this assignment, you only have to work with the Corporate customer segment (this point onwards).
Section 3: Report Making - I
- Freeze the header row.
- Delete or hide any unwanted columns.
- Hint: Decide judiciously between which columns should be deleted / hidden.
- Round off sales and profits to one decimal place.
- Format sales and profit in US dollar units.
- Format the Order Date and Ship Date into a more readable format (say 15-Jan 2012, or choose a format that you find the most readable).
Section 4: Report Making - II
- Sorting:Sort alphabetically by Region.
- Within Region, sort alphabetically by Province.
- Within Province, sort in decreasing order of sales.
Conditional Formatting - I:
- Within every Region, highlight the top 10% orders by sales in light green fill and dark green border.
Conditional Formatting - II:
- To help your manager understand profits better, highlight profits in a scale of green and losses in a scale of red (Higher the profit, darker the shade of green; More the loss, darker the shade of red).
- Learning Tip: Learn how to apply multiple conditional formattings in one column.
Section 5: Report Making - III: *NApply double bottom borders to demarcate every Region.
Section 6: Printing, Saving and Exporting to PDF
- Experiment with different page layout options to fit the report in minimum number of pages.
- Apply password protection to the worksheet
- Save a printable version