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