Showing posts with label business statistics. Show all posts
Showing posts with label business statistics. Show all posts

Tuesday, December 27, 2011

Simplifying Excel Pivot Table and Pivot Chart Setup

Simplifying Excel Pivot

Table and Pivot Chart

Setup

Overview of Excel Pivot Tables

The Excel pivot table is an incredibly convenient tool to summarize and better understand large amounts of data by creating instant cross tabulations that can be quickly re-arranged to highlight different aspects of the data. Excel pivot tables give the user the ability to instantly summarize data in almost any possible configuration. Pivot tables allow you to quickly pull useful information out of large amounts of raw data. Important insights about the data can be readily obtained from Excel pivot tables that might be difficult to discover otherwise.

The most common use of pivot tables is to summarize data by all available categories and subcategories within the raw data. The incredible versatility of pivot tables allows you to expand and collapse all levels of data and instantly reshuffle all categories and subcategories in order to obtain just the right information and insights.

Overview of Excel Pivot Charts

The Excel pivot chart is an excellent supplement to any Excel pivot chart. The pivot chart provides a graphical representation of the pivot table's information that enables quicker comprehensive of trends and comparisons in the data. The pivot chart can instantly created in Excel in a single step after the pivot table has been constructed. The pivot chart a number of different chart types that facilitate even faster comprehension of data's messages.

All of that power and versatility can leave a person new to pivot tables wondering where to start. Fortunately, pivot tables are quite a bit more intuitive than they may initially appear. An easy, step-by-step process will be provided in this article to quickly create a pivot table with multiple configurations on the following data set:

Pivot Tables in Excel - Raw Data Table

Step 1 – Determine the Overall Objective

The most important aspect of creating an Excel pivot table is to establish the main goal and any lesser goals of the analysis. These goals will drive every aspect of the pivot table analysis going forward.

Two following goals will be established for this pivot table analysis:

  1. Compare sales results of the two salespeople

  2. Compare sales results of the two products

Step 2 – Determine the Query Question That Meets Each Goal

As often happens, getting the right answer depends on asking the right question. The query question determines how the data will be arranged and summarized within the pivot table. The following framework can be used to translate the goal determined in step 1 to a correct query question.

Here is a good way to structure the query question for each goal:

Compare _______________

For each _______________

For each _______________

Goal 1 – Compare Sales Results of the Two Salespeople

Compare: Sales Results of Each Salesperson

For each: Product

For each: Quarter

Goal 2 – Compare Sales Results of Each Product

Compare: Sales Results of Each Product

For each: Quarter

Step 3 – Create the Pivot Table That Implements Each Query Question

When the query question has been correctly created, it is usually a simple and straightforward to configure a pivot table that answers the query question. An empty pivot table dialogue box can be brought up in Excel as follows:

Pivot Tables in Excel - Insert Pivot Table

Insert / PivotTable will bring up the following empty pivot table dialogue box.

Pivot Tables in Excel - Empty Dialogue Box

The data table should be arranged in contiguous (touching) rows. The Table/Range consists of the entire table with headers.

Pivot Tables in Excel - Select Data

The location where the pivot table should be placed will be the cell address of the upper left corner of the output.

Pivot Tables in Excel - Completed Dialogue Box

Clicking OK brings up the following PivotTable Field dialogue box. All data column headers from the data table that was selected are shown in the empty PivotTable Field dialogue box.

Pivot Tables in Excel - Empty Dialogue Box

The column headers can now be arranged in the dialogue box as follows to answer the query question corresponding to each goal.

Goal 1 – Compare Sales Results of the Two Salespeople

Compare: Sales Results of Each Salesperson

For each: Product

For each: Quarter

Pivot Tables in Excel - 1st Pivot Table Field Arrangement

All columns headers have been checked. Headers of categorical (non-numeric) data are placed as Rows within the dialogue box. Categorical data include Quarter, Product, and Salesperson. These categorical data labels are placed in the dialogue box in the order that they appeared in the query question.

Numeric data to be evaluated is summed up under Values in the dialogue box.

The following pivot tables is now produced.

Pivot Tables in Excel - 1st Pivot Table

Step 4 – Create the Pivot Chart From the Pivot Table

As soon as the pivot table is created, the corresponding pivot chart for that pivot table can be created in a single step. Simply click anywhere on the pivot table, click on the PivotTable Tools Design tab and then select Pivot Chart as follows:

Pivot Charts in Excel - Insert Pivot Chart

This will bring up the the chart type dialogue box. In this case the 3-D Stacked Column chart was selected.

Pivot Charts in Excel - Chart Type

The resulting pivot chart is the following:

Pivot Charts in Excel - Pivot Chart 1

The chart provides a more intuitive comparison between sales of the two salespeople than does the table.

If the query question were changed to the following:

Compare: Sales Results of Each Salesperson

For each: Quarter

For each: Product

then the PivotTable Fields dialogue box would be reconfigured to implement the new query question by simply dragging the Product column label to the top of Rows to reflect the order in the new query question.

Pivot Tables in Excel - 2nd Pivot Table Field Arrangement

This would produce the following pivot table.

Pivot Tables in Excel - 2nd Pivot Table

These changes to the table are instantly reflected in the automatically updated pivot chart as follows:

Pivot Charts in Excel - Pivot Chart 2

Showing the data in separate columns in the pivot table for each quarter can be implemented by dragging the Quarter column header to Columns in the PivotTable Fields dialogue box as follows:

Pivot Tables in Excel - 3rd Pivot Table Field Arrangement

This instantly produces the following pivot table data for each quarter in its own column.

Pivot Tables in Excel - 3rd Pivot Table

When the data's arrangement is significantly changed within the pivot table, the pivot chart type sometimes should be changed to provide the most intuitive presentation of the data. In this case the pivot chart type was changed from a 3-D Stacked Column chart to a 3-D Clustered Column chart to create greater clarity. In the PivotChart Design tools tab, select Change Chart Type as follows:

Pivot Charts in Excel - Change Chart Type

The 3-D Clustered Chart is selected from the following Chart Type dialogue box:

Pivot Charts in Excel - Select Chart Type

This instantly produces the following pivot chart:

Pivot Charts in Excel - Chart 3

Goal 2 – Compare Sales Results of Each Product

Compare: Sales Results of Each Product

For each: Quarter

The Salesperson column label can be unchecked because the query question does not request any information about individual salespeople.

Pivot Tables in Excel - 4th Pivot Table Field Arrangement

The following pivot table is produced as a result of the Quarter and Product under Rows:

Pivot Tables in Excel - 4th Pivot Table

The following pivot chart more intuitively represents the data of this pivot table in the 3-D Stacked Column chart type:

Pivot Charts in Excel - Chart 4

Excel Master Series Blog Directory

Statistical Topics and Articles In Each Topic

Posted by at 2 comments:
Labels: , , ,

Friday, November 26, 2010

Normal Distribution in Excel To Find Your Sales Ranges

Using the Normal

Distribution in Excel

to Find Your Sales Ranges





This article will show you exactly how to use the Normal Distribution in Excel to calculate the probability that your daily sales are within one of two given ranges. The advantages of statistics in Excel to solve business statistics problems is that most problems can be solved in just one or two steps and there is no more need to look anything up on Normal Distribution tables.


As with any statistical test that uses or depends on the Normal Distribution, as assumption is made that your sales data is Normally Distributed. If you have at least 30 days of sample data, this is probably a reasonable assumption. Even so, you should always test for Normality prior to performing any parametric test or analysis.


Parametric means using or depending on a statistical distribution such as the Normal distribution. This blog contains a number of articles about Normality tests and nonparametric tests that are used in the event that your data is not Normally distributed.


In this problem, you are provided with the mean and standard deviation of your daily sales data and are then required to find the probability your sales from any random day will fall into one of two sales ranges. Here is the problem:

Problem: Use the Normal Distribution to Determine the Probability of Daily Sales Being in 1 of 2 Ranges


The daily unit sales of a commercial web site are normally distributed with a mean of 20 units sold daily and a standard deviation of 1 unit per day. Normality tests were performed on the sales data to verify that they are normally distributed. 

The sales results from one random day are selected. What is the probability the sales from that day will be either more than 21.50 units sold OR less than 18.5 units sold?

Since we are solving for a probability (area under the Normal curve) for a Normal curve that is not standardized, use NORMDIST.


Probability of daily sales less than 18.5 units OR more than 21.5 units =

Probability of daily sales less than 18.5 PLUS Probability of daily sales greater than 21.5


The OR statement requires that the answer contains all elements of both sets. 



The probability of less than 18.5 units sold in a single day

= NORMDIST(X, Sample Mean, Sample Standard Deviation, Cumulative?)

       (Cumulative ask if we are calculating the
         CDF - Cumulative Distribution Function - YES)

       (Note that NORMDIST calculate the area LEFT of X)

= NORMDIST(18.5,20,1,TRUE)

= 0.066807

= 6.67%

 Click On Image To See Larger Version

OR (Plus) 

The probability of more than 21.5 units sold in a single day
= 1 - NORMDIST(X, Sample Mean, Sample Standard Deviation, Cumulative?)

      (Note that 1 - NORMDIST calculate area RIGHT of X)

= 1 - NORMDIST(21.5,20,1,TRUE)

= 1 - 0.933193 = 0.066807

= 6.67%
Click On Image To See Larger Version


The probability that daily daily are less than 18.5 OR greater than 21.5 = Probability of one day’s sales less than units + Probability of one day’s sales more than 21.5 units 

= NORMDIST(18.5,20,1,TRUE) + [ 1 - NORMDIST(21.5,20,1,TRUE) ] 

= 0.066807 + 0.066807

= 0.133614

= 13.36%

So the probability that a day’s sales are less than 18.5 units or more than 21.5 units = 13.36%

Click on Image To See Larger Version





Basic Description of Normal Distribution
The shape of the Normal distribution resembles a bell so it is sometimes called the "bell curve." The Normal curve is symmetric about its mean in the middle, and its tails on either side extend to infinity. The Normal distribution is a continuous function, not a discrete function, This means that any value can be graphed somewhere on a Normal curve. Discrete functions only map specific values, such as whole numbers.


Mapping the Normal Distribution
Any Normal distribution can be completely mapped if only the following two parameters are known:

1) Mean - µ - the Greek symbol "mu"

2) Standard deviation - σ - the Greek symbol "sigma"

If the mean and standard deviation of a Normal distribution are known, then every point of the Normal curve can be mapped.

*************************************************************

A Standardized Normal Curve is a Normal curve that has mean = 0 and standard deviation = 1

*************************************************************

The "68 - 95 - 99.7%" Rule
for the Normal distribution states that:

68% of all observations lie within 1 standard deviation of the mean, within the range of µ +/- σ

95% of all observations lie within 2 standard deviations of the mean, within the range of µ +/- 2σ

99.7% of all observations lie within 3 standard deviations of the mean, within the range of µ +/- 3σ

Click Image To See Larger Version

Click Image To See Larger Version




*****************************************

Here is a link to this article if you wish to link to it:

Using Normal Distribution in Excel To Find Your Sales Ranges


If You Like This, Then Share It...
Using Normal Distribution in Excel To Find Your Sales Ranges Using Normal Distribution in Excel To Find Your Sales Ranges Using Normal Distribution in Excel To Find Your Sales Ranges Using Normal Distribution in Excel To Find Your Sales Ranges Using Normal Distribution in Excel To Find Your Sales Ranges Using the Normal Distribution to Find Sales Limits Using Normal Distribution in Excel To Find Your Sales Ranges


Using the Normal Distribution to Find Sales Limits

<

Excel Master Series Blog Directory

Statistical Topics and Articles In Each Topic

Posted by at 2 comments:
Labels: , , , ,
Subscribe to: Posts (Atom)