Using Excel for Data Analysis: Performing Analysis

Name: Using Excel for Data Analysis: Performing Analysis
Author: Cliff Brozo

Understand the tools Excel uses to perform in-depth scenario analysis.

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

Course: On-Demand

Welcome back to KnowledgeCity's course on using Excel for data analysis. I'm Cliff Brozo, I'm your instructor. And in today's lesson, we're going to talk about What-If Analysis and we're going to explore Excel's Scenario Manager. First, a bit of a definition, in What-If Analysis, Excel will change values in cells to see how those changes affect the outcome of formulas on the worksheet. Now there are three kinds of What-If Analysis tools that come with Excel. There's Scenario Manager that we're going to look at in this lesson, and there's also Goal Seek, and data tables. In order to get to the What-If Analysis Scenario Manager, we need to click on the Data tab and the What-If Analysis button and then choose Scenario Manager. Let's do just that. In order to utilize Scenario Manager, we need some data to work with and in this lesson we'll take a scenario where we'd like to buy a house and we need to make some decisions about how much we can spend based on some factors like what is the prevailing interest rate and how many years would we like to take out a loan for? For in this spreadsheet, we're using 5% as the interest rate, a 15-year mortgage, and a loan amount of $500,000. I've used the Payment function in Excel, there's the Payment function in Excel, to calculate the monthly payment. It takes the rate, divides it by 12, because this is a yearly rate and we want a monthly rate, it takes the number of years and multiplies that by 12 to get the total number of months, and it takes the loan amount of $500,000. We use a minus sign before the PMT function to turn a negative number into a positive number. So if I want to borrow $500,000, my payments will be 3953.97. This is also a calculation that says if I make 15 years of those payments, I will have paid back $711,000 for my $500,000 house. What I'd like to do is figure out what are my options, and to use the What-If Scenario Manager to find out, if I can get a better interest rate or a worse interest rate, what does that do to my monthly payments? Let's go do it. I click on the Data tab and I choose What-If Analysis and I come down into Scenario Manager. Scenario Manager starts out as a blank screen and we need to add Scenarios as we go along. I'm gonna think positive and create a Best Case Scenario. Best Case Scenario says, let me take the cells that are the interest rate and the number of years and let the Scenario Manager change those values. I'll click on Okay. And the best case will be what could change? Well, let's assume that the Best Case Scenario would be a 3% interest rate and a 20-year mortgage. I click on Okay, and there's my Best Case Scenario. I can add more Scenarios. Let's choose a Worst Case Scenario, and let's say that the interest rates go up to 10% and I have to also take out a 20-year mortgage. Now I have Best Case and Worst Case. I'm gonna add another one, Slightly Better. And I'll change my amounts. Instead of 5%, we'll make it 4% at 15 years. And Slightly Worse, and once again, I'll change my Scenario Manager to 6%. I'll click Okay. Now that I've defined four different Scenarios, I can create a Summary piece of information. And the thing that I would like to do is figure out what is the Best Case Scenario, Worst Case Scenario, Slightly Better, and Slightly Worse for my total number of payments. All I need to do is click Okay and a brand new spreadsheet opens up that gives me Scenario Summary information. My Current Value is 5%. I'm choosing 15 years, $711,000. My Best Case is 3% of 20 years. Then I only have to pay back 665,000. My Worst Case is 10% over 20 years and I'm paying over a million dollars for my $500,000 house. 4% at 15 years is pretty much the same as 3% for 20 years. And a Slightly Worse would be 6% over 15 years. Now one thing that I'd like to do for this is to change the name of these cells. B4, C4, and F4 don't tell me a whole lot. I can go back to my spreadsheet, define a name for this cell, and call it Rate and click on Okay. I can define a name for this cell and call it Years and click Okay. And define a name for this cell and call it Total and click Okay. Now I need to go back to my What-If Analysis, my Summary Manager, give me the Summary information again, and now I have the same information with Rate, Years, and Total built right in. Scenario Manager does a great job showing us different values and how those values affect exactly what we are going to be looking at. While I did this for the Total value, I could have also done it for the Monthly Payment. Now you can see that Scenario Manager allows me to compare different values and it recalculates the formula based on those new values, and it all happens in the What-If Scenario Manager. I'll see you soon.

Intermediate Provider : Cliff Brozo 7 Lessons · 52m in Arabic, German, English, Spanish, French, Portuguese, Chinese

Course Description

What-if analysis plays an important role in data analytics, and in these lessons you will learn how to use Scenario Manager and Goal Seek to prove your analyses. You will also learn the value of simulations, and when and how to use sparklines to condense data into a simple, easy-to-read format.

We’ll show you how to tell stories using charts, and how decision support systems can help to manage the ever-growing mountain of data you collect. Finally, you’ll learn how important pivot tables are, and how they give you the ability to pick and chose what data you need summarized.

What You'll Learn

Set up best- and worst-case scenarios using Scenario Manager
Use Goal Seek to find answers to seemingly unsolvable problems
Apply what-if analysis to prove your data analyses
Tell stories with data using charts
Use sparklines to condense data into a simple, easy-to-read format
Build pivot tables, charts, and reports to summarize the data you need

Key Takeaways

What-if analysis plays an important role in data analytics, and Scenario Manager and Goal Seek can be used to prove your analyses.
Goal Seek helps find answers to seemingly unsolvable problems, while Scenario Manager supports setting up best- and worst-case scenarios.
Sparklines condense data into a simple, easy-to-read format, and charts can be used to tell stories with data.
Decision support systems help manage the ever-growing mountain of data you collect.
Pivot tables give you the ability to pick and choose what data you need summarized.

Frequently Asked Questions

What topics does this course cover?

The course covers what-if analysis, Scenario Manager, the value of simulations, sparklines, storytelling with charts, decision support systems, and pivot tables, charts, and reports.

What is what-if analysis used for in this course?

What-if analysis plays an important role in data analytics, and the lessons show how to use Scenario Manager and Goal Seek to prove your analyses.

What skills will I gain from this course?

You will develop analytical skills, work with analytic applications, data analysis, pivot tables and charts, quantitative data analysis, and scenario analysis.

How does this course help manage large amounts of data?

It shows how decision support systems can help manage the ever-growing mountain of data you collect, and how pivot tables let you pick and choose what data you need summarized.

What will I learn about presenting data?

You will learn how to tell stories using charts and how to use sparklines to condense data into a simple, easy-to-read format.