Learning Microsoft Excel Data Analysis Tools.
This article illustrates the Microsoft Excel Data Analysis Tools. Those tools provide the ability to analyze data in a powerful skill and that helps you make better decisions.
- Using Goal Seek.
- Using Solver.
- Using Scenario Manager.
- Using data tables.
Using Goal Seek.
In the lesson of Microsoft Excel Data Analysis tools first, we will learn Goal Seek.
The goal means purpose or task.
Seek means find or search.
If you are using excel formulas and it gives you answers sheets rather not have you can turn off features off which is called Goal Seek.
As the definition tells that Goal means Purpose or task and Seek means to find or to know.
Here is the example:
An Institute has 50 Students and each student pays monthly fee 1000 and the total amount will be 50000.
Now here is the query If Institute wants to earn per month 200000, so, how many students will Institute get.
Select the Data tab.
Locate the Forecast Tools group.
Click the What-If Analysis command.
A list of three options appears.
In the previous lesson, we have seen goal seek tool where we changed the result of formula by changing the formula components one of the cells contributed.
A most sophisticated version of the goal seek is called solver and untimely very sophisticated we have worksheet here namely ABC and you see there is total.
The total is what’s happening in many rows.
Suppose: We have to use Gift offer. Offer is used only 2000/-, but we do not know which item we should buy and what quantity will be for solving this problem we have use solver command.
1. First of all, type following data.
2. Noted: Quantity and Cost should be multiple than the Total amount should also be the auto sum.
3. Now keep the mouse over the D6 Cell address as you see in above image.
Using Scenario Manager.
Definition of Scenarios here in this situation.
I want to give here Offer on Discount on different events/Exhibition.
1 select Dis column.
2 What-If Analysis
4 Scenarios Manager
Add for Diwali.
To see the summary click on Summary button.
Using data tables.
Now I tell you how to Data Table in excel.
The formula tells, what net amount will be after deduction of Discount.
The result will be shown now as below image.
2. What-If Analysis
3. Data Table.
4. Now you can check any quantity and also Price Row
6. In Price, I type and In Quantity 25 then hit enter from the keyboard.