Scenario Analysis Functions
There are many functions you can use when it comes to building a formula to run your scenarios.
In the AFM learning materials three of the most common functions are covered:
1. INDEX
2. OFFSET
3. CHOOSE
However, there are many other functions that can be used to run the scenarios, including XLOOKUP and SWITCH.
1. CHOOSE function 
The CHOOSE function allows for multiple scenarios to be quickly toggled. The formula is simple to use and easy to understand.
In cell C5, we see the function =CHOOSE($C$1,C7,C8,C9).
There are two parts to this function, the index number $C$1, and the values (C7,C8,C9).
The index number in cell C1 dictates which value to display.
If there is a 1 in C1, it displays the value in C7 (2.0%).
If there is a 2 in C1, it displays the value in C8 (1.8%).
If there is a 3 in C1, it displays the value in C9 (2.5%).
The benefit to the CHOOSE function is that it is extremely simple and easy to understand by anyone who reviews it, which creates a lot of confidence for the person reviewing the file.
2. INDEX function
The INDEX function is a great tool to run scenarios and is especially helpful for scenarios that have many cases.
In cell C5, we see the function =INDEX(C7:C9,$C$1). There are two parts to this function, the array (C7:C9), and the row number ($C$1).
The row number checks the number in cell C1 to see which value in the array (C7:C9) to display in cell C5.
The first cell in the array will be displayed in cell C5 if the switch is set to 1, the second cell will be displayed if the switch is set to 2, and so on.
If there is a 1 in C1, it displays the value in C7 (2.0%).
If there is a 2 in C1, it displays the value in C8 (1.8%).
If there is a 3 in C1, it displays the value in C9 (2.5%).
The benefit to the INDEX function is that you can reference a range of cells instead of clicking on each cell individually.
3. OFFSET function
The OFFSET function is a great tool to run scenarios and is helpful for scenarios that have many cases and will have more cases added over time.
In cell C5, we see the function =OFFSET(C6,$C$1,0).
There are three parts to this function, the reference cell, the number of rows to drop down from the reference cell, and the number of columns to move over from the reference cell.
The reference value, C6, is the starting point. The OFFSET will drop down one row because the switch, C1, has been set to 1. Last, the OFFSET will stay in the same column because the third argument is set to zero.
If there is a 1 in C1, it displays the value in C7 (2.0%).
If there is a 2 in C1, it displays the value in C8 (1.8%).
If there is a 3 in C1, it displays the value in C9 (2.5%).
The benefit to the OFFSET function is that you can add more cases and you don’t need to change the syntax in the formula.
4. XLOOKUP function 
In cell C5, we can use the function =XLOOKUP($C$1,A7:A9,C7:C9). There are three key components to this function: the lookup value ($C$1), the lookup array (A7:A9), and the return array (C7:C9).
The function searches for the value in cell C1 within the range A7:A9, finding the value in A7, and returns the corresponding value from the range C7:C9, with the value in C7.
If there is a 1 in C1, it displays the value in C7 (2.0%).
If there is a 2 in C1, it displays the value in C8 (1.8%).
If there is a 3 in C1, it displays the value in C9 (2.5%).
The benefit of the XLOOKUP function is that it allows you to reference a range of cells for both the lookup and return arrays, simplifying the formula. Additionally, it provides clarity in auditing since it clearly displays both the lookup and return ranges, making it easier to verify the data source and output.
5. SWITCH function 
The SWITCH function is a great alternative to using multiple nested IF statements, allowing for simplified expressions when evaluating an expression against a list of values to return the corresponding result of the first matching value.
In cell C5, we see the function =SWITCH($C$1,A7,C7,A8,C8,A9,C9). There are three main parts to this function: the expression, the values, and the results.
The expression is the value or cell reference to evaluate, which in this case is $C$1. The values are the possible values of the expression to match (A7, A8, A9).
The results are the corresponding values to return (C7, C8, C9) if the expression matches one of the specified values.
If there is a 1 in C1, it displays the value in C7 (2.0%).
If there is a 2 in C1, it displays the value in C8 (1.8%).
If there is a 3 in C1, it displays the value in C9 (2.5%).
The benefit of the SWITCH function is that it simplifies decision-making logic by allowing you to check multiple conditions in a clean, readable format. This eliminates the need for complex nested IF statements, making the formula easier to audit and maintain.
With your scenarios flowing smoothly, the next step is to ensure those projections never exceed real-world limits.
Capacity Constraints with MIN
The MIN function is a simple function that takes the lowest value in a series of numbers. This feature is what makes it so useful in capacity constraints. When a business is dealing with a capacity constraint, they need to ensure that sales volume does not go above what they are able to produce. One example is a factory that can only produce a limited number of widgets.
In this example, we introduce the MIN function in cells G19:K19. In cell K19, the formula is =MIN(J19*(1+K18),K16)
Without the MIN function the annual sales volume would have been calculated as (406.8 * 1.04),
which is above the 420,000 annual factory capacity.
Using the MIN function in Column K, the output is:
= MIN(J19*(1+K18),K16)
= MIN(406.8*(1+4%),420)
= MIN(406.8*(1.04),420)
= MIN(423.072,420)
= 420
By always choosing the smaller of “projected demand” versus “maximum capacity,” you bake real-world constraints into the model—no manual overrides needed. Now that you’ve aligned your scenarios with your capabilities, let’s make your model speak with clear, automated labels.
Combining Text Strings
Why do modelers use text functions?
- Descriptive Labels: Create dynamic headers like "Revenue 2025" by combining "Revenue" with a year reference
- Dynamic Reports: Build sentences like "Net profit for 2025 is $X" by joining fixed text with cell values
- Date Management: Combine day, month, and year into one date string
- Unique Identifiers: Generate unique keys by merging various cell values
Excel offers several tools for combining text strings into a single cell
- The CONCATENATE function: A classic tool for joining text
- The CONCAT function: A more flexible option for ranges
- The TEXTJOIN function: Supports delimiters and ignores empty cells
- The “&” operator: A quick and simple way to merge text
Benefits of text functions:
- Clarity: Creates clear, descriptive labels
- Automation: Automates text creation for dynamic inputs
- Consistency: Ensures consistent labeling and reduces errors
CONCATENATE FUNCTION:
How to Use CONCATENATE:
This is the original method for combining text strings in Excel. While still functional, newer functions like CONCAT and TEXTJOIN offer more flexibility, such as the ability to handle delimiters and ranges efficiently.
The syntax is:
Example:
Generate a report title with company name and period:
=CONCATENATE("Company: ", A1, " | Period: ", B1)
Cell A1 contains: "ABC Corp", and cell B1 contains: "Q1 2025",
the result is:
“Company: ABC Corp | Period: Q1 2025"
CONCAT FUNCTION:
How to Use CONCAT:
The CONCAT function is a newer feature that allows you to combine multiple text strings, including ranges, with greater flexibility. Unlike TEXTJOIN, it does not support specifying a delimiter, making it ideal for straightforward concatenation of text without special formatting. The syntax is:
The syntax is:
Example:
Generate a report title with company name and revenue summary using an array:
=CONCAT(A1:B1, " Summary - ", C1)
Cell A1 contains "ABC Corp", and cell B1 contains "Revenue", the result is:
ABC CorpRevenue Summary - Q1 2024
Note: Using the same syntax with CONCAT as with CONCATENATE will produce the same result.
TEXTJOIN FUNCTION:
How to Use TEXTJOIN:
TEXTJOIN is a versatile function that combines multiple text strings, allowing you to specify a delimiter and include or ignore empty cells, making it ideal for advanced scenarios. The syntax is:
The syntax is:
Example:
Generate a report title with company name, period, and revenue overview separated by a dash:
=TEXTJOIN(" | ", TRUE, A1:C1)
Cell A1 contains "ABC Corp", cell B1 contains"Q1 2025", and cell C1 contains “Revenue Overview” the result is:
ABC Corp | Q1 2025 | Revenue Overview
& OPERATOR:
How to Use & OPERATOR:
It’s exactly the same as the CONCATENATE function without using the word CONCATENATE.
The syntax is:
Example:
Generate a report title with company name and period:
="Company: " & A1 & " | Period: " & B1
Cell A1 contains "ABC Corp", and cell B1 contains "Q1 2025", the result is:
Company: ABC Corp | Period: Q1 2025
