Scribd
Upload
9 views18 pages

OAT Module2 MS Excel Part2

The document outlines the features and uses of Pivot Tables and What-If Analysis in Excel, highlighting their capabilities for data summarization, scenario exploration, and decision-making. Pivot Tables allow users to analyze and visualize large datasets by summarizing data, filtering, and drilling down into details, while What-If Analysis tools like Scenario Manager, Data Table, Goal Seek, and Solver help forecast outcomes and optimize decisions. Common applications include budgeting, financial planning, and resource allocation.

Uploaded by

alanaugustine005
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
9 views18 pages

OAT Module2 MS Excel Part2

The document outlines the features and uses of Pivot Tables and What-If Analysis in Excel, highlighting their capabilities for data summarization, scenario exploration, and decision-making. Pivot Tables allow users to analyze and visualize large datasets by summarizing data, filtering, and drilling down into details, while What-If Analysis tools like Scenario Manager, Data Table, Goal Seek, and Solver help forecast outcomes and optimize decisions. Common applications include budgeting, financial planning, and resource allocation.

Uploaded by

alanaugustine005
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

MODULE 2-EXCEL

Features of Pivot Table


• A pivot table in Excel is a powerful data summarization tool that helps
you analyze, organize, and visualize large datasets. It allows you to:

Rotate (or pivot) data to view it from different angles


Summarize and aggregate data
Create custom views of your data
Filter and drill down into specific details
• Key features of pivot tables:
Data summarization: Automatically groups and summarizes data,
calculating totals, averages, counts, and more.
Flexibility: Easily change the layout, fields, and calculations to view
data from different perspectives.
Data filtering: Quickly filter data to focus on specific subsets.
Drill-down capability: Double-click on a cell to see detailed data
behind the summary.
• Common uses for pivot tables:
Data analysis: Identify trends, patterns, and correlations.
Reporting: Create customized reports from large datasets.
Data visualization: Transform complex data into intuitive charts and
graphs.
What if Analysis
• "What-If Analysis" in Excel is a set of tools that helps you:
1. Explore different scenarios
2. Forecast outcomes
3. Make informed decisions
What-If Analysis tools:
1. Scenario Manager: Create and manage multiple scenarios, comparing
outcomes.
2. Data Table: Analyze how changes in one variable affect other variables.
3. Goal Seek: Find the input value needed to achieve a specific output.
4. Solver: Find the optimal solution given constraints and objectives.
Scenario Manager:-
 Create scenarios with different input values
Switch between scenarios to compare results
Use summary reports to compare scenario outcomes

Data Table:-
Create a table showing how changes in one variable affect other
variables
Analyze sensitivity of outputs to input changes
• Goal Seek:-
Set a target value for a cell
Determine the input value needed to achieve that target

• Solver:-
 Define objective (e.g., maximize profit)
 Set constraints (e.g., limited resources)
Find optimal solution
• Common uses for What-If Analysis:
1. Budgeting and forecasting
2. Financial planning
3. Resource allocation
4. Risk analysis
5. Optimization
Example: Using What-If Analysis to determine:-
How changes in interest rates affect mortgage payments
The impact of price changes on revenue
Optimal production levels given resource constraints
• To access What-If Analysis tools in Excel:
1. Go to the "Data" tab
2. Click "What-If Analysis" in the "Forecast" group
Scenario Manager
• A Scenario Manager is a tool in Excel that works on a set of values
that Excel saves and can be changed up to 32 cells simultaneously.
Scenario manager
Scenario manager
Scenario manager
Scenario manager
Scenario manager
Data table – What if analysis
In Excel, the "Data Table" option in the "What-If Analysis" feature is used to
explore how changing one or two variables in formulas can affect the results. It
helps create a range of outcomes based on different input values without manually
changing each variable.
Steps:
• List one set of input values in a column and the other in a row.
• Set up the output formula at the intersection of the table’s first row and first column.
• Select the entire table range (including the input values and the formula).
• Go to the Data tab → What-If Analysis → Data Table.
• Enter the "Row input cell" and "Column input cell" to correspond with the original input
cells.

You might also like