Let’s Excel

SCENARIO

VanArsdel is a company that manufactures and sells sporting goods. The company has offices in the United States (US) and several other countries. Its sales comprise of US sales and International sales. VanArsdel’s sales come from its owned manufactured products, as well as other manufacturers’ products. 

Bobby is a district manager who is responsible for several districts over two States for the VanArsdel’s US office. He just sent you his district’s sales transactions for the year 2015 up to the end of June, in form of a CSV file. Bobby would like you to help him perform some analysis on his districts. Specifically, he would like to know:

  • Which of his districts performs the best and the worst in the period?
  • Which of the cities have the most sales and which of them have the least sales?
  • How are the product categories performing overall in his districts?
  1. Start with a blank Excel file.
  2. Import the CSV file by creating a New Query (New Query is found in the Data tab for Excel 2016 and in the Power Query tab for Excel 2013 and 2010).
  3. Edit the query before loading the data (remember to select the Edit button from the CSV import dialog box):
    • Remove the following columns (IsCompete).
    • Split the City column to only contain the City and remove the state and country information, since there are already columns for State and Country.
    • Replace the State abbreviation with the State name (change NY entries to New York and TX entries to Texas).
  4. Load the data to the Excel data model (remember to select the bottom half of the Close & Load to icon and then select the Close & Load To… option).

Create Pivot Table(s) to Perform Analysis

Now that you have imported the data, you can create pivot table(s) and/or pivot chart(s) to help Bobby with his questions. Recall that Bobby would like to know:

  • Which of his districts performs the best and the worst in the period?
  • Which of the cities have the most sales and which of them have the least sales?
  • How are the product categories performing overall in his districts?

Which district has the most total sales (highest revenue)?

Which of the cities have the most sales and which of them have the least sales?

How are the product categories performing overall in his districts?

data can be shown also in charts

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s