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...
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
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
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
Step 2: Go to Data > Data Tools > What If Analysis > Goal Seek.
Goal Seek Excel Guides
Step 3: Fill-up the Goal Seek Tab.
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
Result: If you have done the computations correctly, you should get 16.25
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
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:
- Are independent of all other cells. They are directly inputted by users, and not through a formula.
- 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:
- Quantity Sold
- Sales Price per Unit
- Variable Cost per Unit
- Interest Expense
Step 3: Go to Data > Data Tools > What If Analysis > Scenario Manager.
Scenario Manager Excel Guides
Step 4: Click Add to Add a 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
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
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.
What If Analysis – Scenario Summary
You can actually show all scenarios in one excel table. To do it, just click Summary.
You can choose either a simple summary or a pivot table. Let’s choose Scenario Summary.
Scenario Summary Pop up
Hitting OK will give you a new tab for the Scenario Summary.
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.