Showing posts with label anova excel. Show all posts
Showing posts with label anova excel. 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: , , ,

Saturday, October 30, 2010

Creating a Simple Moving Average in 3 Steps in Excel

Creating a Simple Moving

Average in 3 Steps in Excel

Simple Moving Average in Excel - 2-Interval, 3_Interval, 6-Interval Charts
(Click On Image To See a Larger Version)

This is one of the following three articles on Time Series Analysis in Excel

Forecasting With Exponential Smoothing in Excel

Forecasting With the Weighted Moving Average in Excel

Forecasting With the Simple Moving Average in Excel

Overview of the Moving Average

The moving average is a statistical technique used to smooth out short-term fluctuations in a series of data in order to more easily recognize longer-term trends or cycles. The moving average is sometimes referred to as a rolling average or a running average. A moving average is a series of numbers, each of which represents the average of an interval of specified number of previous periods. The larger the interval, the more smoothing occurs. The smaller the interval, the more that the moving average resembles the actual data series.

Moving averages perform the following three functions:

  1. Smoothing the data, which means to improve the fit of the data to a line.

  2. Reducing the effect of temporary variation and random noise.

  3. Highlighting outliers above or below the trend.

The moving average is one of the most widely used statistical techniques in industry to identify data trends. For example, sales managers commonly view three-month moving averages of sales data. The article will compare a two-month, three-month, and six-month simple moving averages of the same sale data. The moving average is used quite often in technical analysis of financial data such as stock returns and in economics to locate trends in macroeconomic time series such as employment.

There are a number of variations of the moving average. The most-commonly employed are the simple moving average, the weighted moving average, and the exponential moving average. Performing each of these techniques in Excel will be covered in detail in separate articles in this blog. Here is a brief overview of each of these three techniques.

Simple Moving Average

Every point in a simple moving average is the average of a specified number of previous periods. This blog article will provide a detailed explanation of the implementation of this technique in Excel.

Weighted Moving Average

Points in the weighted moving average also represent an average of a specified number of previous periods. The weighted moving average applies different weighting to certain previous periods; quite often the more recent periods are given greater weight. A link to another article in this blog which provides a detailed explanation of the implementation of this technique in Excel is as follows:

http://blog.excelmasterseries.com/2010/11/weighted-moving-average-accurate-simple.html

Exponential Moving Average

Points in the exponential moving average also represent an average of a specified number of previous periods. Exponential smoothing applies weighting factors to previous periods that decrease exponentially, never reaching zero. As a result exponential smoothing takes into account all previous periods instead of a designated number of previous periods that the weighted moving average does. A link to another article in this blog which provides a detailed explanation of the implementation of this technique in Excel is as follows:

http://blog.excelmasterseries.com/2010/11/excel-marketing-forecasting-technique-3.html

The following describes the 3-step process of creating a simple moving average of time-series data in Excel;

Step 1 – Graph the Original Data in a Time-Series Plot

The line chart is the most commonly-used Excel chart to graph time-series data. An example of such an Excel chart used to plot 13 periods of sales data is shown as follows:

Simple Moving Average in Excel - Raw Data
(Click On Image To See a Larger Version)

Step 2 – Create the Moving Average in Excel

Excel provides the Moving Average tool within the Data Analysis menu. The Moving Average tool creates a simple moving average from a data series.

Simple Moving Average in Excel - Data Analysis Tool
(Click On Image To See a Larger Version)

The Moving Average dialogue box should be filled out as follows in order to create a moving average of the previous 2 periods of data for each data point.

Simple Moving Average in Excel - 2-Interval Dialogue Box
(Click On Image To See a Larger Version)

The output of the 2-period moving average is shown as follows, along with the formulas that were used to calculate the value of each point in the moving average.

Simple Moving Average in Excel - Output
(Click On Image To See a Larger Version)

Step 3 – Add the Moving Average Series to the Chart

This data should now be added to the chart containing the original time line of sales data. The data will simply be added as one more data series in the chart. To do that, right-click anywhere on the chart and a menu will pop up. Hit Select Data to add the new series of data. The moving average series will be added by completing the Edit Series dialogue box as follows:

Simple Moving Average in Excel - Add Data Series to Chart0
(Click On Image To See a Larger Version)

Simple Moving Average in Excel - New Data Series Information
(Click On Image To See a Larger Version)

The chart containing the original data series and that data’s 2-interval simple moving average is shown as follows. Note that the moving average line is quite a bit smoother and raw data’s deviations above and below the trend line are much more apparent. The overall trend is now much more apparent as well.

Simple Moving Average in Excel - 2-Interval Chart
(Click On Image To See a Larger Version)

A 3-interval moving average can be created and placed on the chart using the same procedure as follows:

Simple Moving Average in Excel - 3-Interval Dialogue Box
(Click On Image To See a Larger Version)

Simple Moving Average in Excel - Output
(Click On Image To See a Larger Version)

It is interesting to note that the 2-interval simple moving average creates a smoother graph than the3-interval simple moving average. In this case the 2-interval simple moving average might be the more desirable than the 3-interval moving average.

Simple Moving Average in Excel - 3-Interval Chart
(Click On Image To See a Larger Version)

For comparison, a 6-interval simple moving average will be calculated and added to the chart in the same way as follows:

Simple Moving Average in Excel - 6-Interval Dialogue Box
(Click On Image To See a Larger Version)

Simple Moving Average in Excel - Output
(Click On Image To See a Larger Version)

Simple Moving Average in Excel - 2-Interval, 3-Interval, 6-Interval Chart
(Click On Image To See a Larger Version)

As expected,the 6-interval simple moving average is significantly smoother than the 2 or 3-interval simple moving averages. A smoother graph more closely fits a straight line.

Analyzing Forecast Accuracy

Accuracy can be described as goodness of fit. The two components of forecast accuracy are the following:

Forecast Bias – The tendency of a forecast to be consistently higher or lower than actual values of a time series. Forecast bias is the sum of all error divided by the number of periods as follows:

Bias = ∑Et/n = ∑(Yt-act – Yt-est)/n

A positive bias indicates a tendency to under-forecast. A negative bias indicates a tendency to over-forecast. Bias does not measure accuracy because positive and negative error cancel each other out.

Forecast Error – The difference between actual values of a time series and the predicted values of the forecast. The most common measures of forecast error are the following:

MAD – Mean Absolute Deviation

MAD calculates the average absolute value of the error and is computed with the following formula:

MAD = ∑ |Et| / n = ∑ |(Yt-act – Yt-est)| / n

Averaging the absolute values of the errors eliminates the canceling effect of positive and negative errors. The smaller the MAD, the better the model is.

MSE – Mean Squared Error

MSE is a popular measure of error that eliminates the cancelling effect of positive and negative errors by summing the squares of the error with the following formula:

MSE = ∑ Et2 / n = ∑ (Yt-act – Yt-est)2 / n

Large error terms tend to exaggerate MSE because the error terms are all squared. RMSE (Root Square Mean) reduces this problem by taking the square root of MSE.

MAPE – Mean Absolute Percent Error

MAPE also eliminates the cancelling effect of positive and negative errors by summing the absolute values of the error terms. MAPE calculates the sum of the percent error terms with the following formula:

MAPE = ∑ ( |Et| / Yt-act ) * 100% / n = ∑ ( |(Yt-act – Yt-est)| / Yt-act ) * 100% / n

By summing percent error terms, MAPE can be used to compare forecasting models that use different scales of measurement.

Calculating Bias, MAD, MSE, RMSE, and MAPE in Excel For the Simple Moving Average

Bias, MAD, MSE, RMSE, and MAPE will be calculated in Excel to evaluate the 2-interval, 3-interval, and 6-interval simple moving average forecast obtained in this article and shown as follows:



Simple Moving Average in Excel - Simple Moving Averages
(Click On Image To See a Larger Version)

The first step is to calculate Et, Et2, |Et|, |Et| / Yt-act , and then sum them as follows:

Simple Moving Average in Excel - 2-Interval Initial Calculations
(Click On Image To See a Larger Version)

Bias, MAD, MSE, MAPE and RMSE can be calculated as follows:

Simple Moving Average in Excel - 2-Interval Final Calculations
(Click On Image To See a Larger Version)

The same calculations are now performed to calculate Bias, MAD, MSE, MAPE and RMSE for the 3-interval simple moving average.

Simple_Mov_Av_15_3-Interval_Initial_Calculations_600
(Click On Image To See a Larger Version)

Simple_Mov_Av_16_3-Interval_Final_Calculations_600
(Click On Image To See a Larger Version)

The same calculations are now performed to calculate Bias, MAD, MSE, MAPE and RMSE for the 6-interval simple moving average.

Simple_Mov_Av_17_6-Interval_Initial_Calculations_600
(Click On Image To See a Larger Version)

Simple_Mov_Av_18_6-Interval_Final_Calculations_600
(Click On Image To See a Larger Version)

Bias, MAD, MSE, MAPE and RMSE are summarized for the 2-interval, 3-interval, and 6-interval simple moving averages as follows. The 3-interval simple moving average is the model that most closely fits that actual data.

Simple Moving Average in Excel - Error Summarization
(Click On Image To See a Larger Version)

 Excel Master Series Blog Directory

Statistical Topics and Articles In Each Topic

 

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