Skip to content

Create a report containing a summary of sales for the Corporate customer segment from the Superstore_data

Notifications You must be signed in to change notification settings

shrutidesAI2011/Superstore_data_upGrad

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

3 Commits
 
 
 
 
 
 

Repository files navigation

Superstore_data_upGrad

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

About

Create a report containing a summary of sales for the Corporate customer segment from the Superstore_data

Topics

Resources

Stars

Watchers

Forks

Releases

No releases published

Packages

No packages published