Microsoft Excel 2021: What-If Analysis

Name: Microsoft Excel 2021: What-If Analysis
Author: Bob Akbar

Use tools to predict desired values in a multi-variable environment.

Preview the first lesson free — get full access to all 3 lessons.

Course: On-Demand

Hello, my name is Bob Akbar, and in these lessons you will learn scenario manager, goal seek, and data tables. These are all part of the what if analysis. In this lesson, I will show you scenario manager. scenario manager is located under the data tab, and you will see it says the what if analysis and is the option right there, scenario manager. So let's highlight one of our loans here, and I've intentionally not included any interest in this example so that we can see simply the numbers and how they affect things. So we have put in a principal amount, we put a down payment in, we put in taxes, and we've calculated what the finance amount would be, how many payments there are, and therefore what the loan payment would be. So let's highlight one of our scenarios, and let's go to the what if analysis and scenario manager. In the scenario manager, I've already created one that says no down payments, and in the no down payment, cell B3, which is the down payment, is the one that is going to be changed. And then it asks us what we would like for that value to be, and we're going to say zero. If we do not put any money down on this scenario, what is going to happen? And then for the summary, it asks us which result we would like to change, we would like to change the payments. So let's see if we do not put any money down, what is going to be our payments? And here it is. In the scenario summary, it opens up a new tab for us, and it says with the 40,000 down, our payments would have been 1573. However, our scenario of no down payments, our payments would be $1,907. This gives us a quick idea of what would happen if something changes in our scenario. So let's try a different one. Let's say that we want to finance this instead of 120, we want to pay it off faster. So let's highlight our scenario. We're going to go to the one of analysis, scenario manager, and we're going to add a new scenario. And this one is going to be faster pay. So there's our faster payoff. And the cell we're going to change now is going to be the terms of the load. We're going to click OK. And then it asks us what we would like to change. So we would like to see what are our payments going to be. And in order for us to see what our payments are going to be, we're going to say we're going to pay this off twice as fast. So we're only going to have 60 payments. Congratulations, we just had a raise at work. And there it is. We can also add additional criteria, but that's all we're concerned about for our example for this lesson. So now we have faster payoff. We can select that and we can click on summary, and it says which result would we like the cell to be? So we're going to have to sell the payment is the number that we are looking for, because if we're paying faster, our payments are going to be higher. And there it is. So if we had no down payment, 120 payments, we would have 1,907 original loan with a 40,000 payment, 120 was 1573. However, if we do 40,000 for our down payment and do a faster payoff, and you notice the highlighted cell is the one that we have specified as our criteria. So if we pay this off in 60 payments with a $40,000 down, we will end up with $2,907 as our payments. Thanks for watching. Stay tuned for the next lesson where I will show you goal seeking.

Advanced Provider : Bob Akbar 3 Lessons · 13m in English

Course Description

While data analytics is great for gaining insights into previous results, it’s also useful for predicting how to improve results in the future. In these lessons, you’ll learn how to perform predictive analytics in Excel—also known as a “What-If” analysis—to determine which inputs you’ll need in advance to produce a desired result, so that you can take a more proactive approach to your analysis.

We’ll go over how to use an existing formula with multiple variables instead of just one. By the end of these lessons, you’ll have all the tools you’ll need to analyze data, run scenarios, and prepare reports to show others how to achieve their goals.

What You'll Learn

Perform predictive analytics in Excel through a What-If analysis
Use Excel's predictive analytics tools to determine inputs needed in advance for a desired result
Apply an existing formula with multiple variables instead of just one
Work with Scenario Manager, Goal Seek, and Data Tables
Run scenarios and prepare reports to show others how to achieve their goals
Identify the common requirements for each analytics tool

Key Takeaways

A What-If analysis is a form of predictive analytics in Excel used to determine which inputs are needed in advance to produce a desired result.
What-If analysis enables a more proactive approach by predicting how to improve results in the future, rather than only reviewing previous results.
The course covers using an existing formula with multiple variables instead of just one.
Scenario Manager, Goal Seek, and Data Tables are the predictive analytics tools taught in this course.
By the end of the lessons, learners will have the tools needed to analyze data, run scenarios, and prepare reports.

Frequently Asked Questions

What will I learn in this course?

You'll learn how to perform predictive analytics in Excel, also known as a What-If analysis, to determine which inputs you'll need in advance to produce a desired result. The course covers using an existing formula with multiple variables instead of just one, and how to analyze data, run scenarios, and prepare reports.

Which Excel tools does this course cover?

The course covers three predictive analytics tools through its lessons: Scenario Manager, Goal Seek, and Data Tables. It also explains the common requirements for each analytics tool.

What is a What-If analysis?

A What-If analysis is predictive analytics in Excel that helps you determine which inputs you'll need in advance to produce a desired result, allowing you to take a more proactive approach to your analysis.

What skills will I gain from this course?

This course builds skills in analytical thinking, analytics, Microsoft Analysis Services, predictive analytics, predictive analytics software, and scenario analysis.