# Excel 2010 Using What-If Analysis

Views: 165

The real power in Excel comes in its ability to perform multiple mathematical calculations for you. One of the tools in Excel that you can use to perform these calculations is a Data tool called What-If Analysis. What-If analysis allows you to see the effect that different values have in formulas. Have you ever thought, "What interest rate do I need to qualify for to have a car payment of $400 on the car I want?" This question can be answered using What-If Analysis.

In this lesson, you will learn how to use a What-If Analysis tool called Goal Seek.

Sponsored Links:

Thank you for taking the time to report an issue.

What's wrong... Please write below.

## More topics

The real power in Excel comes in its ability to perform multiple mathematical calculations for you. One of the tools in Excel that you can use to perform these calculations is a Data tool called What-If Analysis. What-If analysis allows you to see the effect that different values have in formulas. Have you ever thought, "What interest rate do I need to qualify for to have a car payment of $400 on the car I want?" This question can be answered using What-If Analysis.

In this lesson, you will learn how to use a What-If Analysis tool called Goal Seek.

Continuing with our Excel Tutorials, in this article, I’ll take you through using Goal Seek in Microsoft Excel. The function is same as that of earlier versions of Excel as well as Excel 2010. Goal Seek is yet another What-If Analysis Tool that is extremely useful and forms an integral part of many Excel modelling exercises. Unlike data tables, which allow you to change one or more variables to find how the output varies, goal seek allows you to “set” the value of the output at a particular value and find out what value of the input variable achieves that output.

What-If Analysis in Excel allows you to try out different values (scenarios) for formulas. The following example helps you master what-if analysis quickly and easily. Assume you own a book store and have 100 books in storage. You sell a certain % for the highest price of $50 and a certain % for the lower price of $20...

PowerPivot is an amazing free add-in for Excel 2010. It is from Microsoft, but not from the Excel team. PowerPivot is from the SQL Server Analysis Services team. It is the greatest thing to hit Excel in 20 years. Here are some reasons why you might consider using PowerPivot: Handle incredibly large data sets. I’ve seen 100 million rows, stored in the Excel workbook, without a problem...

There comes a time when you are presented with data in a cross-tabular format but your analysis requires that the data be formatted into a traditional table (or normalized) structure. The first thing you need to do is access a tool that, prior to Excel 2007, was fairly easy to reach. It has now been pushed into the background of Excel 2007 and Excel 2010; that tool is the Pivot Table and Pivot Chart Wizard.

Aging analysis is helping accountants since ages and beyond doubt this is one the those reports that are prepared almost every period to track both receivables and payables. If you want to learn more about aging or ageing analysis do read my explanation: What is age, aged, ageing or aging analysis? In very few words and if I try to define aging analysis in context of receivables or debtors, then it is an analysis that helps me determine when certain sales invoices are falling due.

All three analysis tools are what-if tools. In other words, you can see the impact of changing input values without changing the actual data. In keeping with our first two articles, "How to use Excel's Scenario Manager analysis tool" and "How to use Excel's Goal Seek analysis tool," we'll use the Data Table feature to explore possible mortgage options by evaluating several interest rates and term values. Excel's Data Table tool works similarly to Goal Seek and Scenario Manager. You can use table values to replace variables in a formula or function. In this way, you can view what-if results without changing the original formula's references.

If you ask me, Excel's what-if analysis tools are under-used. I often respond to readers with "Have you tried a data table?" Invariably, they don't know how to implement the feature—sometimes, they don't even know what a data table is. So, over the next few months, I'll introduce you to Excel's what-if analysis tools: Goal Seek, Scenario Manager, Data Table...

Excel’s Trust Center, which causes security prompts, was introduced in Excel 2007, so when you are working with a workbook in Excel 2007 or Excel 2010, it runs automatically and handles macros in different ways depending on the settings you select. This post will go through the similarities and differences in excel macro security in Excel 2007 and Excel 2010.

While many people are still unaware of it, Excel 2010 (and even previous versions) is a very powerful business intelligence client. That’s right, I said Excel. And I don’t mean the classic grab some data, do some charts and email it around sort of Excel, I mean connecting it to Analysis Services cubes and performing fast, useful data analytics on known sets of data. It can also go off and do some pretty amazing things with the Data Mining add-in, or PowerPivot, but for now I’m going to restrict myself to using core capabilities, and getting them published to SharePoint.