• LinkedIn
  • YouTube
  • Twitter
  • Facebook
  • Google+

Excel’s best-kept secret: What-If Analysis tools

by on
in Excel Training,Office Technology

What-If Analysis tools are a well-kept secret in Excel, located on the Data tab, Data tools group.

Scenario manager

You've probably seen payment calculators that let you adjust terms and rate.Chances are you’ve “plugged and played” various scenarios ending up with, “That second scenario looked good! What was that?” Scenario Manager allows you to save “plugs and plays” and compare them. Begin with a worksheet where you can see the formula and the values it uses.

  1. Choose Scenario Manager from the What-If Analysis dropdown button.
  2. Click the Add button, and create your first scenario named Original Values.
  3. Put your cursor in the Changing cells: field, and select all the fields that could vary in your scenarios.
  4. Click OK twice.
  5. Click Add  to create another scenario and start changing the values to create the variations.

Click the Show button to see any one of them or the Summary button to see them side by side.

Goal Seek

When your formula works, but it doesn’t give you a result that you like, try Goal Seek. The Payment formula is =PMT(B2/12,B3,-B1). Let’s say you can only afford a payment of $200.

  • Choose Goal Seek from the What-If Analysis dropdown button.
  • Verify that your result cell is indicated in Set cell:
  • Type 200 in To value: field.
  • With your cursor blinking in the By changing cell: field, click the Loan Amount (or any other field you wish to vary). Click OK.

Goal Seek will show you an answer if it found one. Confirm the change by clicking OK.

Data Table

If you know that your “what if” choices are a combination of two values, such as interest rate and term, a Data Table will work. Using your formula as the cornerstone, type a column of one variable, and a row of another to form the table border.

  • Choose Data Table from the What-If Analysis dropdown button.
  • Select the cell with Interest rate in the original formula (B2) for Row input cell field and Term in Months (B3) for Column input cell.

Like what you've read? ...Republish it and share great business tips!

Attention: Readers, Publishers, Editors, Bloggers, Media, Webmasters and more...

We believe great content should be read and passed around. After all, knowledge IS power. And good business can become great with the right information at their fingertips. If you'd like to share any of the insightful articles on BusinessManagementDaily.com, you may republish or syndicate it without charge.

The only thing we ask is that you keep the article exactly as it was written and formatted. You also need to include an attribution statement and link to the article.

" This information is proudly provided by Business Management Daily.com: http://www.businessmanagementdaily.com/32526/excels-best-kept-secret-what-if-analysis-tools "

Leave a Comment