Using Excel’s Scenario Manager for Financial Planning

Excel’s Scenario Manager tool analyzes different financial scenarios to show the outcome based on the changes in variables. It shows the outcome based on business assumptions like increase or decrease of revenue, cogs, costs, etc. You can evaluate the best and worst-case scenarios for financial planning or decision-making. In this article, we will show how to use Excel’s Scenario Manager for financial planning.

Let’s assume you have a small business, and you want to analyze or project the upcoming year’s net profit of your business.

Key Metrics:

• Revenue
• Cost of Goods Sold (COGS)
• Operating Expenses
• Tax Rate

Let’s evaluate three different scenarios:

• Base Case: Current or ongoing business conditions.
• Best Case: Increase sales (revenue), reduce expenses, and an affordable tax rate.
• Worst Case: Decreased sales (lower revenue), increased expenses, and a higher tax rate.

Step 1: Set Up Your Data in Excel

Insert your structured financial data in Excel. Based on financial data calculate the net profit by using a formula in Excel.

To calculate the Net Profit from the financial data,

• Insert the following formula in cell B6.

Formula:

=B2-B3-B4-(B2-B3-B4)*B5

This formula will calculate the Net Profit based on these inputs.

Step 2: Access the Scenario Manager

• Go to the Data tab >> from What-If-Analysis select >> Scenario Manager.

• In the Scenario Manager dialog box >> click Add.

Step 3: Create the Scenarios

Let’s create 3 scenarios to analyze financial planning. Based on the outcomes of the scenarios you can make decisions to increase or decrease key metrics.

Add Base Case

The base case will contain the current business categories and values.

In the Add Scenario box;

• Scenario Name: Base Case
• Changing Cells: Select B2:B5 where B2 (Revenue), B3 (COGS), B4 (Operating Expenses), B5 (Tax Rate).
• Click OK.

• In the Scenario Values box;
  • $B$2: Revenue = 200,000
  • $B$3: COGS = 120,000
  • $B$4: Operating Expenses = 40,000
  • $B$5: Tax Rate = 20%
  • Click OK.

Add Best Case

Follow the similar steps to add the best case scenario. As it is the best case we will increase sales values and decrease costs.

In the Add Scenario box;

• Scenario Name: Best Case
• Changing Cells: Select B2:B5.
• Scenario Values:
  • $B$2: Revenue = 240,000 (higher sales)
  • $B$2: COGS = 110,000 (better supplier deal)
  • $B$2: Operating Expenses = 35,000 (cost-saving measures)
  • $B$2: Tax Rate = 18% (tax deduction)
  • Click OK.

Add Worst Case

Adding the worst case in scenario manager where tax is higher, increases cost and lower sales.

• Scenario Name: Worst Case
• Changing Cells: Select B2:B5.
• Values for Worst Case:
  • $B$2: Revenue = 150,000 (sales decline)
  • $B$3: COGS = 130,000 (higher cost of raw materials)
  • $B$4: Operating Expenses = 45,000 (higher rent or utilities)
  • $B$5: Tax Rate = 25% (increase in taxes)
  • Click OK.

Step 4: Show Scenarios Based on Cases

After creating the scenarios, you can see the net profit in cell B5 by selecting the case name.

• Navigate to the Scenario Manager >> select one of the scenarios (Base Case, Best Case, Worst Case).
• Select Best Case >> Click Show to apply the scenario.
• The values for Revenue, COGS, Operating Expenses, and Tax Rate will change according to the scenario, and the Net Profit will be recalculated.

Based on the best case scenario you can plan where you should spend less and where more.

Step 5: Generate a Scenario Summary

• Open Scenario Manager >> select Summary.

• From the Scenario Summary dialog box;
  • Select Scenario summary.
  • Result Cells: Select cell B6 containing Net Profit.
  • Click OK.

Now, Excel will create a new sheet named Scenario Summary. It will show a table that compares Revenue, COGS, Expenses, Tax Rate, and Net Profit for all three scenarios.

Analysis of Results:

• Base Case: The company earns a profit of $32,000 with current sales and expenses.
• Best Case: If sales are higher, expenses are lower, and better tax will increase the overall net profit which is $77900.
• Worst Case: If revenue of sales drops, costs increase, and the tax rate rises, the company will face a loss of $18750.

These scenarios will help your business financial planning. Like to reduce tax you can relocate your business to another state or region. You can focus on strategic planning to increase sales and be careful to control expenses to maximize the net profit.

Excel’s scenario manager will help to perform risk assessments, profit projections, strategic planning, etc.

Conclusion

By using Excel’s Scenario Manager, you can make decisions based on the outcomes of various assumptions. It allows financial planners to analyze different possibilities without making manual changes. You can set up multiple scenarios to assess your financial risks and rewards to make more informed decisions. This article shows a step-by-step explanation to use Scenario Manager in Excel. Explore all possible cases for better financial planning.