Friday, January 29, 2016

What If Analysis In Excel, And How Goal Seek,Data Table and Scenario Manager Work?



Before starting know about what if analysis in excel and how goal seek, data table and scenario manager work?   We must have the knowledge of what does what if analysis means. In this tutorial I will teach you what is what if analysis and how its work. I hope that will you really enjoy this.

What is what if analysis?

It is the process of changing the value which you want to put in you data then you see how changes will affect the outcome formula on the spreadsheet.

How many tools are available in excel to perform what if analysis?

There are three types of different tools in excel to perform in what if analysis:-
1) Goal Seek
2) Data Table
3) Scenario Manager

1) Goal seek – Goal seek is the part of what if analysis. It is the ability to calculate backward to obtain an input data that he given output data. Below image shows that how to use goal seek and its work.

Step 2

Click on ok button and you see Mahesh Mishra total number will increase 136 to 150 and his Math Number will increase 14 to 28.Below image shows that when we press ok button see change the data.

2) Data Table – It also a part of what if analysis. It is the range of the cell that shows the changing of one variable or two variables put in your formula affect the result. See how to use data table and its work.

First select the range then go to data table option, In Row Input Cell click the principle amount and Column Input Cell click the rate or time period cell and press ok. Below image shows that how its work?
Step 1









Step2

 Image 2 Bank Loan analysis in Data Table

The Simple Interest formula is Principle *Rate*Time/100. Always remember when you start the data table first select the range and follow the above given description.

3) Scenario Manager- It is a set of value that Microsoft excel saves can substitute automatically in cells or excel spreadsheet. In simple suppose you create our three month budget sheet in three different sheet and you want to see all data in your master worksheet, In that case you use scenario manager. Below image shows that How to use Scenario Manager?

Step 1- Below image shows that a person whose budget sheet before is Promotion.
Step 2- Below image shows that a person whose budget sheet after is Promotion.
Step 3- Below image shows that a person whose budget sheet when he earn salary or extra income and his increase expense.


Final Step- Final step mean that you create our three month budget sheet in three different sheet and you want to see all data in your master worksheet, in below image you see the whole date in your master worksheet.

Through the video you can better understand to given all description about this tutorial.

In this article you have studied about  what if analysis in excel and how goal seek, data table and scenario manager work?. If you like this tutorial doesn’t forget to like us on Facebook and also follow me on twitter. And if you find anything missing than let me know through your commenting.

People Also Read : How to add input message, error alert, invalid data in data validation?
People Also Read : How to load Analysis Toolpak in Excel 2010?




3 comments:

  1. when i read your blog i understand little little but when i saw your video clear my confusion

    ReplyDelete
  2. Thank you so much for the information. i appreciate very much for providing this information.


    PUBG APK

    ReplyDelete

Like us on facebook

blogger templatesblogger widgets

Follow on Twitter

Linkedin

Categories

Contact Form

Name

Email *

Message *

Mad About Computer. Powered by Blogger.