Learn Data Analysis With Excel Free in Applied Way With Part wise labs(part-1)

Problem statement:

You are the new marketing manager of an established Bicycle company. The company sells bicycles and accessories, such as clothing and other accessories to bikers in six countries.

The company has just hired Lucky as its new Sales manager. You are tasked to introduce Lucky to the company, its product portfolio and its sales performance since 2011. To do this, you have asked Jacky, the IT manager to prepare some data for you.

Prerequisite:

Data provided in the file.

Excel

Your first solution should look like this (numbers may not be the same). 

Procedure to make chart more insightfull:

For the Yearly Category Revenue chart, display the total revenue for each year as a separate line.

  1. Create a Total Revenue line in the range C7 to H7 by summing the annual revenue in rows 3 through 6. Label the row as Total Revenue in cell B7
  2. Select the Yearly Category Revenue chart.
  3. The data source for the chart is highlighted.
  4. Drag the highlighted selection to include the total row (B7 to H7)

For the Revenue by Category chart, display the percentage for each category.

  1. Select the Revenue by Category chart.
  2. From the Chart Tools Design tab, change the chart style to display the data as a percentage including the data label as percentages. You can do this by either selecting a style from the Chart Styles group (such as Style 8 in Excel 2016 for example) or from the Chart Layouts group by selecting one of the Quick Layouts (such as Style 6 in Excel 2016 for example).

For the Revenue by Country chart, sort the countries to display the one with the highest revenue showing at the top and the one with the lowest revenue at the bottom.

  1. Select the data source for the Revenue by Country chart.
  2. Sort the data by Revenue from Largest to Smallest ensuring you also sort the countries with the revenue.
  3. Select the Revenue by Country chart.
  4. Select the Y axis, the axis that shows the country name.
  5. Select Categories in reverse order in the Axis options.

Final product looks like this ;

Congratulations You made Your First steps.

One Comment Add yours

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