What-If Analysis and Forecasting

In Advanced Excel Chapter 15, you will learn how to evaluate different business possibilities and forecast future performance. This chapter covers Goal Seek, Scenario Manager, one-variable and two-variable Data Tables, Forecast Sheet, trendlines, and forecasting formulas.

What-If Analysis answers questions such as “What sales value is required to earn a target profit?” Forecasting uses historical information to estimate future values such as sales, demand, expenses, or inventory requirements.

Learning Objectives

• Understand the difference between What-If Analysis and forecasting.
• Use Goal Seek to calculate a required input.
• Create and compare scenarios with Scenario Manager.
• Build one-variable and two-variable Data Tables.
• Create forecasts using linear and seasonal methods.
• Use Excel’s Forecast Sheet.
• Calculate growth rates and moving averages.
• Create forecast charts and confidence intervals.
• Interpret forecast results responsibly.

1. What Is What-If Analysis?

What-If Analysis changes one or more input values to determine how those changes affect a formula result. It is useful for financial planning, budgeting, pricing, investment analysis, sales planning, and risk evaluation.

Common What-If Questions

• How many units must be sold to achieve a target profit?
• What price should be charged to earn a desired margin?
• How will loan payments change when the interest rate changes?
• What happens to profit if sales increase and costs decrease?
• How much can a company spend while maintaining its profit target?

2. Excel What-If Analysis Tools

Tool	Purpose	Typical Question
Goal Seek	Finds one input required to produce a desired formula result	What quantity produces ₹200,000 profit?
Scenario Manager	Stores and compares different groups of input values	What happens under best, expected, and worst cases?
Data Table	Calculates multiple results for one or two changing inputs	How does EMI change across different rates and terms?
Forecast Sheet	Creates a future forecast from historical time-series data	What may next year’s monthly sales look like?

3. Goal Seek in Excel

Goal Seek works backward from a required formula result. You identify a formula cell, enter the desired result, and tell Excel which input cell it may change.

Goal Seek Requirements

• The target cell must contain a formula.
• The changing cell must be referenced directly or indirectly by that formula.
• Only one input cell can be changed during one Goal Seek operation.

4. Goal Seek Example: Calculate Required Sales Quantity

Create the Following Model

Cell	Description	Value or Formula
B2	Selling Price Per Unit	₹1,500
B3	Variable Cost Per Unit	₹900
B4	Fixed Cost	₹300,000
B5	Units Sold	1,000
B6	Revenue	Formula
B7	Total Variable Cost	Formula
B8	Profit	Formula

Revenue Formula

=B2*B5

Total Variable Cost Formula

=B3*B5

Profit Formula

=B6-B7-B4

Find the Quantity Required for ₹500,000 Profit

1. Go to Data → What-If Analysis → Goal Seek.
2. In Set cell, select B8.
3. In To value, enter 500000.
4. In By changing cell, select B5.
5. Click OK.
6. Review the result and click OK to keep it.

Based on these assumptions, the contribution per unit is ₹600. To cover ₹300,000 fixed costs and earn ₹500,000 profit, the required quantity is approximately 1,333.33 units. Because partial units may not be possible, the business should round up to 1,334 units.

5. Goal Seek Example: Required Selling Price

You can also determine the selling price required to achieve the same target profit.

1. Set the Units Sold value to the expected quantity.
2. Open Goal Seek.
3. Set cell: B8.
4. To value: 500000.
5. By changing cell: B2.
6. Click OK.

Important Limitation

Goal Seek changes only one variable. If several constraints or changing inputs must be optimized simultaneously, Excel Solver is usually more appropriate.

6. Scenario Manager

Scenario Manager stores different sets of input values inside the same worksheet model. It allows you to switch between possibilities without manually replacing each assumption.

Example Business Scenarios

Scenario	Units Sold	Selling Price	Variable Cost	Fixed Cost
Best Case	1,800	1,600	850	290000
Expected Case	1,400	1,500	900	300000
Worst Case	1,000	1,400	980	325000

Create the Scenarios

1. Create the profit model used in the Goal Seek example.
2. Go to Data → What-If Analysis → Scenario Manager.
3. Click Add.
4. Enter Best Case as the scenario name.
5. Select the changing cells for Units Sold, Selling Price, Variable Cost, and Fixed Cost.
6. Enter the Best Case values.
7. Repeat the process for Expected Case and Worst Case.

Display a Scenario

1. Open Scenario Manager.
2. Select the required scenario.
3. Click Show.

Create a Scenario Summary Report

1. Open Scenario Manager.
2. Click Summary.
3. Select Scenario Summary.
4. Select the Profit cell as the result cell.
5. Click OK.

Excel creates a separate summary worksheet comparing the input values and calculated result for each scenario.

7. Scenario Profit Results

Profit Formula

=(Selling Price-Variable Cost)*Units Sold-Fixed Cost

Scenario	Profit Calculation	Profit
Best Case	(1600-850) × 1800 – 290000	₹1,060,000
Expected Case	(1500-900) × 1400 – 300000	₹540,000
Worst Case	(1400-980) × 1000 – 325000	₹95,000

8. One-Variable Data Table

A one-variable Data Table calculates several results by substituting a list of values into one input cell. It is useful for sensitivity analysis.

Example: Profit at Different Sales Quantities

Use the profit model from the previous section. Enter the following quantities vertically:

800
1000
1200
1400
1600
1800
2000

Create the Data Table

1. Enter the quantity values in cells D3:D9.
2. In cell E2, enter a reference to the Profit formula cell:

=B8

3. Select the complete range D2:E9.
4. Go to Data → What-If Analysis → Data Table.
5. Leave the Row input cell blank.
6. Set the Column input cell to the original Units Sold cell, B5.
7. Click OK.

Expected Results

Units Sold	Estimated Profit
800	₹180,000
1000	₹300,000
1200	₹420,000
1400	₹540,000
1600	₹660,000
1800	₹780,000
2000	₹900,000

9. One-Variable Data Table with Multiple Results

A one-variable Data Table can display more than one formula result for each input value.

Example Output Columns

• Revenue
• Total Cost
• Profit
• Profit Margin

Place references to these result cells across the top row of the Data Table. Excel will calculate every result for each quantity in the first column.

10. Two-Variable Data Table

A two-variable Data Table shows how one formula result changes when two different inputs are varied simultaneously.

Example: Profit by Quantity and Selling Price

Enter selling prices across the top row:

₹1,300 | ₹1,400 | ₹1,500 | ₹1,600 | ₹1,700

Enter quantities down the first column:

800
1000
1200
1400
1600
1800

Create the Two-Variable Data Table

1. Enter a reference to the Profit cell in the top-left corner of the table.
2. Select the entire table, including prices, quantities, and the formula reference.
3. Go to Data → What-If Analysis → Data Table.
4. Set the Row input cell to the Selling Price input cell.
5. Set the Column input cell to the Units Sold input cell.
6. Click OK.

Interpret the Results

Each intersection shows the profit generated by one selling-price and sales-quantity combination. Apply a color scale to identify high-profit and low-profit combinations quickly.

11. Financial Data Table Example: Loan EMI

Create the Loan Model

Input	Example Value
Loan Amount	₹2,000,000
Annual Interest Rate	9%
Loan Term	10 years

Monthly EMI Formula

=-PMT(B3/12,B4*12,B2)

Two-Variable Loan Analysis

• Place interest rates across the top row.
• Place loan terms down the first column.
• Place a reference to the EMI formula in the top-left cell.
• Use the Interest Rate cell as the Row input cell.
• Use the Loan Term cell as the Column input cell.

The completed table shows how monthly payments change across different interest rates and repayment periods.

12. What Is Forecasting?

Forecasting estimates future values using historical data. Excel can calculate a simple linear projection or create a time-series forecast that accounts for repeating seasonal patterns.

Common Forecasting Applications

• Monthly sales forecasting
• Product demand planning
• Inventory requirements
• Expense and cash-flow forecasting
• Website traffic forecasting
• Student enrollment forecasting
• Staffing requirements

13. Prepare Historical Data for Forecasting

Month	Sales
01-Jan-2025	420000
01-Feb-2025	445000
01-Mar-2025	468000
01-Apr-2025	455000
01-May-2025	490000
01-Jun-2025	515000
01-Jul-2025	540000
01-Aug-2025	528000
01-Sep-2025	565000
01-Oct-2025	590000
01-Nov-2025	625000
01-Dec-2025	680000

Data Preparation Rules

• Store dates as valid Excel dates.
• Sort records from oldest to newest.
• Use consistent intervals, such as daily, weekly, or monthly.
• Remove accidental duplicates.
• Investigate missing periods and unusual values.
• Keep dates and values in separate columns.

14. Linear Forecast with FORECAST.LINEAR

FORECAST.LINEAR predicts a value using a straight-line relationship between known X-values and Y-values.

Syntax

=FORECAST.LINEAR(x,known_y's,known_x's)

Example

If dates are in A2:A13, sales values are in B2:B13, and the future date is in A14, use:

=FORECAST.LINEAR(A14,$B$2:$B$13,$A$2:$A$13)

When to Use It

• The trend is approximately linear.
• The data does not show strong seasonal patterns.
• You require a straightforward projection.

15. Forecast with TREND

The TREND function returns values along a linear trend.

Syntax

=TREND(known_y's,[known_x's],[new_x's],[const])

Example

=TREND($B$2:$B$13,$A$2:$A$13,A14)

In versions of Excel supporting dynamic arrays, TREND can return forecasts for multiple future dates through one spilling formula.

=TREND($B$2:$B$13,$A$2:$A$13,A14:A19)

16. Seasonal Forecast with FORECAST.ETS

FORECAST.ETS predicts future values using an exponential-smoothing method designed for time-series data. It is useful when historical values contain a repeating seasonal pattern.

Syntax

=FORECAST.ETS(target_date,values,timeline,[seasonality],[data_completion],[aggregation])

Example

=FORECAST.ETS(A14,$B$2:$B$13,$A$2:$A$13)

Automatic Seasonality

When the optional seasonality argument is omitted or set to 1, Excel attempts to detect seasonality automatically.

=FORECAST.ETS(A14,$B$2:$B$13,$A$2:$A$13,1)

17. Forecast Confidence Interval

A forecast is an estimate, not a guaranteed result. A confidence interval helps communicate the uncertainty around that estimate.

Forecast Value

=FORECAST.ETS(A14,$B$2:$B$13,$A$2:$A$13)

Confidence Interval

=FORECAST.ETS.CONFINT(A14,$B$2:$B$13,$A$2:$A$13,0.95)

Lower Confidence Bound

=B14-C14

Upper Confidence Bound

=B14+C14

In this example, B14 contains the forecast and C14 contains the confidence-interval amount.

18. Create a Forecast Sheet

Forecast Sheet creates a new worksheet containing historical values, forecast values, and a forecast chart.

Steps

1. Select the historical Date and Sales columns.
2. Go to Data → Forecast Sheet.
3. Select a line or column forecast chart.
4. Choose the forecast end date.
5. Open Options to review seasonality, confidence interval, and missing-point handling.
6. Click Create.

Forecast Sheet Output

• Historical timeline
• Historical values
• Forecast values
• Lower confidence boundary
• Upper confidence boundary
• Forecast visualization

19. Calculate Month-over-Month Growth

If the previous month’s sales are in B2 and current sales are in B3, use:

=IFERROR((B3-B2)/B2,0)

Format the result as a percentage.

20. Calculate Compound Annual Growth Rate

CAGR Formula

=(EndingValue/BeginningValue)^(1/NumberOfYears)-1

Excel Example

=(B7/B2)^(1/5)-1

CAGR represents the constant annual growth rate that would connect the beginning value to the ending value over the specified period. It does not show fluctuations within that period.

21. Moving Average Forecast

A moving average reduces short-term variation and helps reveal the underlying trend.

Three-Month Moving Average

If sales are stored in column B, enter the following formula for the first complete three-month period:

=AVERAGE(B2:B4)

Copy the formula downward to calculate a rolling three-month average.

Six-Month Moving Average

=AVERAGE(B2:B7)

When to Use Moving Averages

• To smooth monthly or weekly fluctuations
• To identify the general direction of performance
• To build a simple short-term estimate
• To compare actual results with a smoothed trend

22. Add a Trendline to a Chart

1. Create a line or scatter chart from historical data.
2. Select the data series.
3. Right-click and select Add Trendline.
4. Select Linear, Exponential, Moving Average, or another suitable type.
5. Optionally extend the trendline forward.
6. Optionally display the equation and R-squared value.

A higher R-squared value indicates that the selected trendline explains more of the variation in the observed data. It does not prove that future results will follow the same pattern.

23. Actual vs Forecast Comparison

Month	Actual Sales	Forecast Sales	Variance	Accuracy
Jan	520000	500000	20000	96.15%
Feb	545000	535000	10000	98.17%
Mar	510000	530000	-20000	96.08%

Variance Formula

=B2-C2

Absolute Error

=ABS(B2-C2)

Absolute Percentage Error

=IFERROR(ABS(B2-C2)/B2,0)

Simple Forecast Accuracy

=1-IFERROR(ABS(B2-C2)/B2,0)

Accuracy calculations require careful interpretation, especially when actual values are zero, very small, or negative.

24. Choosing the Correct Tool

Requirement	Recommended Tool
Find one input required for a target result	Goal Seek
Compare best, expected, and worst cases	Scenario Manager
Test many values for one input	One-variable Data Table
Test combinations of two inputs	Two-variable Data Table
Project a straight-line trend	FORECAST.LINEAR or TREND
Forecast seasonal time-series data	FORECAST.ETS or Forecast Sheet
Optimize several inputs with constraints	Solver

25. Forecasting Best Practices

• Use sufficient historical data.
• Maintain consistent time intervals.
• Investigate missing periods and outliers.
• Separate actual values from forecast values.
• Document assumptions used in the model.
• Display confidence boundaries where appropriate.
• Compare previous forecasts with actual results.
• Update forecasts when new data becomes available.
• Use multiple scenarios for uncertain business conditions.
• Combine statistical output with business knowledge.

26. Common What-If Analysis and Forecasting Mistakes

• Using a target cell that does not contain a formula in Goal Seek.
• Selecting the wrong changing cell.
• Reversing the row and column input cells in a Data Table.
• Using text instead of valid dates.
• Applying a linear forecast to strongly seasonal data.
• Assuming that forecast results are guaranteed.
• Ignoring unexpected events and structural market changes.
• Using too little historical data.
• Failing to review unrealistic or impossible forecast values.
• Presenting a forecast without explaining its assumptions.

27. Practical Project: Sales Planning and Forecasting Model

Create an Excel planning model that helps management evaluate profit possibilities and forecast future sales.

Project Requirements

• Create a revenue, cost, and profit model.
• Use Goal Seek to calculate the quantity required for a target profit.
• Create Best Case, Expected Case, and Worst Case scenarios.
• Generate a Scenario Summary report.
• Create a one-variable Data Table for quantity and profit.
• Create a two-variable Data Table for price and quantity.
• Prepare at least 12 months of historical sales data.
• Create linear and seasonal forecasts.
• Create a Forecast Sheet.
• Calculate lower and upper confidence boundaries.
• Create an Actual vs Forecast chart.
• Write a short management recommendation.

28. Practice Exercises

1. Use Goal Seek to calculate the selling price required for ₹600,000 profit.
2. Create optimistic, expected, and pessimistic cost scenarios.
3. Create a Scenario Summary showing Revenue and Profit.
4. Build a one-variable Data Table for sales quantities from 500 to 2,500.
5. Build a two-variable Data Table using quantity and variable cost.
6. Create a loan EMI table using interest rates and loan terms.
7. Calculate a three-month moving average.
8. Forecast the next six months using FORECAST.LINEAR.
9. Forecast the same period using FORECAST.ETS.
10. Compare the two forecast methods and explain the difference.

29. Chapter 15 Quiz

Question 1

Which tool finds the input required to produce a specific formula result?

• A. Scenario Manager
• B. Goal Seek
• C. PivotTable
• D. Flash Fill

Answer: B. Goal Seek

Question 2

Which tool compares groups of assumptions such as Best Case and Worst Case?

• A. Scenario Manager
• B. Goal Seek
• C. Sort
• D. Text to Columns

Answer: A. Scenario Manager

Question 3

How many changing input cells does a two-variable Data Table evaluate?

• A. One
• B. Two
• C. Three
• D. Unlimited

Answer: B. Two

Question 4

Which function is designed for seasonal time-series forecasting?

• A. SUMIFS
• B. XLOOKUP
• C. FORECAST.ETS
• D. COUNTBLANK

Answer: C. FORECAST.ETS

Question 5

What does a confidence interval communicate?

• A. The exact guaranteed future result
• B. The uncertainty surrounding a forecast
• C. The worksheet password
• D. The number of scenarios

Answer: B. The uncertainty surrounding a forecast

30. Frequently Asked Questions

What is the difference between Goal Seek and Scenario Manager?

Goal Seek finds one input required to produce a specified result. Scenario Manager stores and compares multiple sets of input assumptions.

What is the difference between Goal Seek and Solver?

Goal Seek changes one input to reach one formula result. Solver can optimize a result by changing multiple inputs while applying constraints.

What is the difference between FORECAST.LINEAR and FORECAST.ETS?

FORECAST.LINEAR projects a straight-line relationship. FORECAST.ETS is intended for time-series forecasting and can account for repeating seasonal patterns.

Can forecasts guarantee future sales?

No. Forecasts are estimates based on historical patterns and model assumptions. Market changes, pricing decisions, competitor activity, and unexpected events can produce different results.

Why does my Data Table show the same result in every cell?

The formula reference or input-cell selection may be incorrect. Confirm that the top formula references the result cell and that the row or column input cell points to the original model input.

Can Forecast Sheet work with missing data points?

Forecast settings can provide options for handling missing points and aggregating duplicate timeline values. However, you should still investigate data-quality problems before relying on the forecast.

31. Chapter 15 Summary

In Advanced Excel Chapter 15, you learned how to use Goal Seek, Scenario Manager, and Data Tables to evaluate business alternatives. You also learned how to forecast future values using FORECAST.LINEAR, TREND, FORECAST.ETS, moving averages, trendlines, and Forecast Sheet.

These tools help managers evaluate risk, plan budgets, test pricing decisions, calculate required performance, and prepare evidence-based business forecasts.

Next Chapter

Advanced Excel Chapter 16: Macros and VBA Automation will explain how to record macros, use the Visual Basic Editor, understand VBA objects, write procedures, automate repetitive tasks, and build button-controlled Excel workflows.

Official References

• Microsoft Support: Use Goal Seek
• Microsoft Support: Use Scenarios
• Microsoft Support: Calculate Multiple Results with a Data Table
• Microsoft Support: Create a Forecast in Excel
• Microsoft Support: FORECAST.ETS Function