While Excel is often used in accounting to report past results, it can also be used to model future outcomes. When building a model to predict the next quarter’s sales, you might want to create hundreds of scenarios and model all of them to figure out the best possible outcome, the worst possible outcome, and the median outcome.
One of the steps in building this model is to generate a list of all possible scenarios. Let’s say that your model relies on three events, and each event could have three possible outcomes: low, mid, and high. With three events and three outcomes, there would be 27 scenarios to model, from “low-low-low” to “high-high-high.”
For me, generating the list of all possible scenarios takes a lot of thought. In the past, I’ve used a combination of nonintuitive formulas to make column E loop through low, mid, and high. Then an IF statement in column D decides if the next row should be the same as the row above or jump to the next level. A similar formula in column C uses “low” for nine rows, then “mid” for nine rows, and so on.
Any time I go through these convoluted steps on my YouTube channel, an astute viewer named Kyle reminds me that I can easily create the combinations in Excel using the BASE function. The goal of this article is to show you how to easily generate all combinations of several input values in order to create a list of all scenarios.
Mentally, I classify the BASE function as a function mostly of interest to mathematicians or electrical engineers. It’s a function that helps you when converting a decimal number to another base numbering system.
NUMBER SYSTEMS IN EXCEL
Historically, Excel was designed to easily convert between decimal, binary, octal, and hexadecimal numbering systems. If you wanted to convert from decimal to binary, you would use =DEC2BIN. If you needed to go from octal to hexadecimal, you would use =OCT2HEX. A total of 12 functions handled the conversion between binary, octal, decimal, and hexadecimal.
Excel 2013 introduced two new functions: The BASE function would convert any decimal number to any numbering system from base 2 to base 36, and the DECIMAL function would convert a number in an alternate numbering system back to decimal.
The syntax for BASE is =BASE(number, radix, [min_length]). In this context, “radix” tells you the base of the numbering system. If you were using numbers in base 12, the radix would be 12. The optional argument for minimum length specifies that you might always want eight binary digits in the result.
If you’re of a certain age, you might remember the three-minute Saturday morning Schoolhouse Rock! episode where your “cousin on another planet” had 12 toes and would count seven, eight, nine, dek, el, doh. While that inventive cartoon did a great job of introducing base 12 to a generation, in practical terms, we don’t have mystery digits on a keyboard, so base 12 uses the digits 0 to 9 as well as the letters A and B.
Column D shows the digits used in base 2 through base 36 in Excel.
GENERATING ALL COMBINATIONS USING BASE
The first step is to figure out how many scenarios you need to model. Say that you had four input cells with three combinations each. To figure out how many scenarios you need to model, you would calculate 3 raised to the fourth power. In Excel, this is =3^4 and calculates as 81 possible scenarios. Below is a simple spreadsheet where you enter the number of input cells and the number of alternatives for each cell. The formula in B3 is =B2^B1.
The second step is to generate a list of numbers with one number in each row. But instead of numbering the scenarios from 1 to 81, it’s better to number them from 0 to 80. The new SEQUENCE function generates a sequence of numbers. =SEQUENCE(81) generates the numbers from 1 to 81. To have the list of numbers start at a number other than 1, specify the starting number as an optional third argument: =SEQUENCE(81,,0).
Below, a simpler model with three events and two outcomes each needs only eight numbers. The formula =SEQUENCE(B3,,0) in cell A6 generates the numbers 0 to 7.
Note that the SEQUENCE function is new and only available in Office 2021 or Microsoft 365. If you’re still using Excel 2019 or newer, you would replace the one formula in A6 with a zero and then a series of formulas starting with =A7+1 in cell A8 and copied down.
The BASE function is used in the third step. You want to convert the decimal numbers in column A to the list of possible scenarios. With two outcomes for three input cells, you would specify a radix of 2 and a minimum length of 3.
If you have the SEQUENCE function in A6, you can simply specify A6# as the number, and one formula =BASE(A6#,B2,B1) would generate all of the scenarios.
If you’re using Excel 2019 or older, use =BASE(A6,$B$2,$B$1) in B6 and copy down to all of the rows.
OPTIONALLY ADDING 1 TO EACH DIGIT OF THE RESULT
In the original risk model, the three possible outcomes were named “low,” “mid,” and “high.” The BASE function is using 0 for low, 1 for medium, and 2 for high. Personally, I prefer my lists to be numbers 1, 2, and 3 instead of 0, 1, and 2.
Below, a REPT(1, formula in B4 generates a number with a series of the digit 1. When you add this “111” to the formula in B6, you've generated every possible combination of 1, 2, and 3.
COMBINING FORMULAS
In the previous examples, the SEQUENCE function is shown separately in A6 so you can visualize how the scenarios start at 0 and increase by 1. In the final model, you could embed the SEQUENCE logic directly into the B6 formula: =BASE(SEQUENCE(B3,,0),B2,B1)+B4.
The REPT function in B4 could also move directly into the B6 formula: =BASE(SEQUENCE(B3,,0),B2,B1)+REPT(1,B1).
Finally, the calculation for the number of scenarios could move from B3 and be included as the first argument of the SEQUENCE function: =BASE(SEQUENCE(B2^B1,,0),B2,B1)+REPT(1,B1).
The final model here uses two input cells and one formula. Below, the formula in B6 generates all 625 possible scenarios of four events with five outcomes each.
YOUR LOGIC WILL VARY
Now that you have the list of all possible scenario combinations, you would use any combination of LEFT, MID, RIGHT, CHOOSE, IF, and INDEX to perform calculations. The model shown below multiplies prior-year revenue by five different factors depending on the scenario. Descriptive statistics in cells E5:F9 describe possible outcomes.
While your final model will use other logic, the main point is using the somewhat obscure BASE function to generate all possible combinations of several input variables.
August 2023
