This activity demonstrates how to create graphics to enhance the presentation of quantitative data

Scenario

The sales manager for a small manufacturer is interested in marketing the company's rain coat line in a new area of the country. As a first step to establishing a marketing plan she has asked you to produce some graphs showing the market share of the products sold over the past several years in the region.

Analysis

After some research, you acquire the approximate sales for each of the competing products presently in the region under consideration $($See the data given below$).$ The question now is to show how best to organize the data to take advantage of a graphical analysis. Pie charts give a quick overview of the market share "slices$$ each competitor presently enjoys. So you decide to use a pie chart to graph the most recent year's sales data for the region. This will give a good indication of just who the market leaders are and how crowded it is at the top.

Next, you decide to graph the sales for the products over the three$-$year period available. Multiple bar charts can display several sets of data but are hard to read. Stacked bar charts have the advantage of showing clearly how parts of a whole change over time. In this case, the parts are different company rain coat sales and the whole is the total sales for the region. Thus, a stacked bat chart can be used to show how market shares of this total market vary over time.

Finally, the single bar chart excels in showing how single factors, like total market sales, change over equal periods of time. Armed with this information, you set off to create the desired graphs.

TASK A:

Create a spreadsheet with the data shown below.

Produce and print a pie chart showing the percentage of sales each product currently holds. When including a title, it is desirable to use all capitalized letters for the first title and upper and lower case for the second. Use the given titles.

MARKET SHARE ANALYSIS

$2005$ SALES

COMPANY

PRODUCT

ANNUAL SALES

Good Week

Rain coat with added Hood

$40,000$

Fire Rock

Rain Poncho

$90,000$

Big Blue

Rain pants and shirt

$28,000$

Air Tight

Rain Coat with detachable hood

$45,000$

$$

TASK B:

From the data below, produce a stacked bar chart showing the market share of sales each product enjoyed for the three years.

Add the appropriate titles, data labels and horizontal and vertical grids for clarity.

$$

PRODUCT

YEAR$1$

YEAR$2$

YEAR$3$

Rain coat with added Hood

$20,000$

$32,000$

$40,000$

Rain Poncho

$87,000$

$93,000$

$90,000$

Rain pants and shirt

$54,000$

$43,000$

$28,000$

Rain Coat with detachable hood

$40,000$

$48,000$

$45,000$

$$

$3.$ Graph the total sales for each year from the data given. Use a line graph to display the results. Format the sales figures in dollars and add appropriate titles.

Activity $2$

TASK A:

Download the excel file provided on the course page and configure the$$named sheets$$in the workbook to produce the following results:$-$

Pivot: Configure the pivot table to show Product in the rows, Country in the columns, Category as the filter and Amount as the values.

Filters: Filter the columns. Configure the filter to show rows for New Zealand only.

Sort: Sort the data by country.

Subtotals:$$repeat the sort by country,$$then using the wizard, insert subtotals, one per country, summing the amount column. Use the wizard; do not insert manually!! $($see the video in the readings if you are not following this$)$

Play Video

Groups: Group the Amount and Date columns and collapse the grou