Excel Guides: What If Analysis l Goal Seek l Scenario Manager

Welcome to the second part of Finance Walk Excel Guides. The topics to be discussed here are more advanced in comparison with the previous guide.If you haven’t yet downloaded the first one, you better do it now.

The topics discussed in that guide are the foundations you’ll use to learn more advanced techniques in Excel.

But, if you’re already comfortable with your knowledge of the basics, then, let’s get this going...

Background

You had been hired as a junior financial analyst for a manufacturing company named FinanceWalk Inc.

You were given a fair amount of benefits, coupled with good pay. You thought you had landed to a stellar company. Then, suddenly, as you were looking at the company’s financial statements...

Sample Income Statement

Sample Income Statement

You completely unexpected this... The company was incurring big losses for the past two years!

The reason you’re hired? You need to help them figure out how they can turn their bottom lines into profit. With your skills, you need to provide the management inputs to help them solve the dilemma.

Note: Assume all amounts are in US dollars.Download the FinanceWalk Advanced Excel Guides.All the data and ‘solved’ sheets are compiled into it, so you’ll have to refer to this file from time to time.

What If Analysis – Goal Seek

Problem:

The VP for Sales approached you for your first assignment. He wants to know what Sales Price should be if they want 2016 Net Income to be 180,000. All other factors will be held constant. What should you do?

To solve this problem, let’s use Goal Seek.

Goal Seek is an Excel Tool which lets you work through your equation backward. In our problem, we had the end result in mind, what we need to know is what input (Sales Price) will lead us to that outcome (Net Income = 180,000).

Step 1: Use the FW Income Statement. Copy 2015 data of creating a new column for 2016.

Sample Variable Income Statement

Sample Variable Income Statement

Step 2: Go to Data > Data Tools > What If Analysis > Goal Seek.

Goal Seek Excel Guide

Goal Seek Excel Guides

Step 3: Fill-up the Goal Seek Tab.

Fill up Goal Seek Pop Up

Fill up Goal Seek Pop Up

Set Cell: This is the cell which will show your desired outcome. For our scenario, the outcome we would like to change is Net Income, thus, we choose the cell for Net Income. You don’t need to type anything, just select the Set Cell box then highlight the Net Income cell.

To Value: This is the value we want for our Set Cell. Thus, we type 180,000.

By Changing Cell: This will be the cell we would like to change, given our desired outcome. In our problem, we will be changing the 2016 Sales Price.

Goal Seek value Pop Up

Goal Seek value Pop Up

Result: If you have done the computations correctly, you should get 16.25

Target Value from Goal seek

Target Value from Goal Seek

Suggested: Read more about excel for finance

What If Analysis – Scenario Manager

Problem: Using Goal Seek, we can only find one type of outcome. But in real life, as could vary. Analysts usually call those outcomes as “Best Case”, “Worst Case” and “Most Likely Case" effect Before deciding, it is desirable to sell all these types of outcomes first. It will help us determine the best course or action. But then again, we cannot use Goal Seek.

What shall we do?

The solution is to use another component of What If Analysis, which is Scenario Manager. It lets us see different types of outcomes based on a single set of data. Scenario Manager is usually used for Sensitivity analysis.

Let’s see how it works.

Step 1: Copy the 2015 Income Statement and then label it as 2016. It will be our basis for creating three main scenarios: Best Case, Worst Case, and Most Likely Case.

7 Sample Variable Income Statement FinanceWalk

7 Sample Variable Income Statement FinanceWalk

Step 2: Identify the Changing Cells. These are the cells which affect changes in the other cells. In our 2016 IS, if we change Quantity Sold, Sales Price and Variable Cost per Unit, it will result in a change in Sales, Variable Costs, Gross Margin and Net Income. Thus, those three cells are Changing cells.

The two fixed expenses (Depreciation and Interest Expenses) are independent of all other cells. But, changing them will affect Net Income. Thus, they are also changing cells.

Thus, changing cells have two characteristics:

  1. Are independent of all other cells. They are directly inputted by users, and not through a formula.
  2. Changes in the value of these cells will affect the value of all other cells.

Thus, for our Income Statement, the following are the Changing Cells:

  1. Quantity Sold
  2. Sales Price per Unit
  3. Variable Cost per Unit
  4. Depreciation
  5. Interest Expense

Step 3: Go to Data > Data Tools > What If Analysis > Scenario Manager.

Scenario Manager Excel

Scenario Manager Excel Guides

Step 4: Click Add to Add a New Scenario.

Add new scenario

Add new scenario

Step 5: Edit the new Scenario. Use “Best Case” as the Scenario Name. Input also all the five Changing Cells before hitting OK.

Best Case Scenario

Best Case Scenario

Step 6: Input the value of the Changing Cells. Let’s suppose our Best Case Scenario would involve a jump in Sales to 70,000 coupled with an increase in Sales Price to 12.

Sample Best Case scenario

Sample Best Case scenario

Click Add to create additional scenarios. If you are already done creating scenarios, hit OK.

Step 7: Creating Worst and Most Likely Scenarios will be left to you.

Suppose Worst Scenario will involve a decrease in Sales Price to 5, and quantity to 20,000. Interest Expense will increase to 115,000.

Most Likely Scenario will have a Sales of 42,000 units, 9.50 Sales Price, Variable Cost per unit of 7.

Result: You should be able to create 3 Scenarios. Every time you choose one, then click Show, the Income Statement should change base on the parameters we have set.

Different Scenarios

Different Scenarios

What If Analysis – Scenario Summary

You can actually show all scenarios in one excel table. To do it, just click Summary.

Scenario Summary

Scenario Summary

You can choose either a simple summary or a pivot table. Let’s choose Scenario Summary.

Scenario Summary Pop up

Scenario Summary Pop up

Hitting OK will give you a new tab for the Scenario Summary.

Scenario Summary Report

Scenario Summary Report

You can change the Changing and Result Cell Names in order to make the table more readable.

Note: Before initiating the Scenario Summary process, be sure that the amounts displayed on the Income Statement are the current values. Otherwise, you Current Values table shown above will be incorrect.

Next Batch Starts on 29th April, 2019
- Classroom Course with Placement Assistance: Our Full-Time Financial Modeling Investment Banking Course (6 Weeks) starts on 29th April, 2019 and 2.5 Months Weekend Workshop starts on 6th April, 2019 in New Delhi, India and on 16th March, 2019 in Gurgaon, India.

- Online Course with Placement Assistance: Join our Online Financial Modeling Investment Banking course with Placement Assistance from anywhere in the world.

- CFA Level - I: 16th April, 2019

- Financial Modeling Investment Banking Weekend in Gurgaon: 6th April, 2019

- See Testimonials.

Only a few seats remain. Interested candidates can contact me.

View All BIWS Courses –Free $397 Bonus for FinanceWalk Readers
Excel Guides: What If Analysis l Goal Seek l Scenario Manager 1

Leave a Comment