The Constorium of Greater Minds

I do a lot of work in Excel. Call me a nerd or whatever, but I enjoy it.

Everytime I’m faced with an Excel task (and most tasks actually), I veiw it is a brain-teaser. I ask myself, “How can I tackle this problem such that I have the least chance of making an error in the shortest amount of time possible within the given output/analysis constraints.” Thanks to some great Excel teachers at NERA, I’ve built a great toolbox of methods to use.

One of my main responsibilities at work is financial and operational planning which comes into focus around the end of the calendar year. This is when we put our heads together to try to figure out what we think next year will look like. The process requires many iterations, as strategies, storylines, assumptions, expenses, and tactics are fleshed out. Once, we have a good baseline forecast we run it through various scenarios to check out the “what ifs.” Easily jumping between scenarios, changing scenario assumptions, and easily comparing outputs is a critical step in the process and makes for an interesting Excel challenge.

The solution I use revolves around the Data Validation feature which allows you to create a dropdown menu in a cell. This post assumes you have an intermediate level of Excel knowledge.

#tldr

Problem: Use Excel to view the total sales from three predetermined 12 month sales scenarios

Solution:

1. Create an in-cell drop down menu using Data->Data Validation to select the name of a scenario
2. Use the text value selected by the dropdown as the lookup value in a match() function to return that scneario’s row number in a source table
3. Pass that row number to an hlookup() function that uses the month as its lookup value to pull the sales number for a given month and scenario

Download example Excel file

The Problem

Let’s say you have three simple sales scenarios, called Plan A, Plan B, and Plan C. Let’s also say that these scenarios each represent the next 12 months of monthly sales under different assupmtions and you already have a model that uses sales figures as inputs to create some outputs (say variable costs, revenue, or cash). Your task is to see how the model’s outputs change with each different sales scenrio.

I’ve set up an simple example book that has a sheet called “Sales scenarios” with a table of three rows representing our scnearios and columns representing each month of the year.

Selecting the Inputs

As I mentioned above, the solution here revolves around an in-cell dropdown menu created using Excel’s Data Validation feature. Let’s create a new sheet in our model and call it “Inputs and summary”. We’ll use this sheet to select a scenario and quickly view a summary of outputs from the model.

In another sheet, called “Sales scenarios”, I created a simple table with the names of the three scenarios. The columns are months, and the rows are the different scenarios. Each cell in the table has a sales number corresponding to a certain scenario and month.

Back on the inputs sheet, let’s create a list of scenario names. The scenario names in this list must be exactly the same as the ones used in the Sales scenarios sheet to lable the table rows.

To create the dropdown, select an empty cell and go to Data->Data Validation->Data Validation on the Excel ribbon. In the resulting dialog box, under “Allow:”, select “List.” For the “Source:”, select the range that contains the scenario names on the “Inputs and summary” sheet. Make sure the checkbox on the right that says In-Cell  dropdown is checked and hit OK. The cell should now have a dropdown menu showing the three scneario names when selected. We’ll use the value of the dropdown in the next section to grab the correct sales figures.

Grabbing the Scenario Data

Grabbing the data will involve using the text in the dropdown menu we created above to dynamically lookup the values from our “Sales scenarios” sheet. Depending on the complexity of the situation, this might involve an index() function, but the way our example is set up, a simple hlookup() function will do.

Since both our “Output” and “Sales scenarios” sheets are set up with months as the labeling the columns and the months labels are all in the same Excel row that our table is in a horizontal format. This requires the hlookup() as opposed to the vlookup(). The vlookup() function is used when the index values are arranged in the same Excel column.

Since the dates are common to both tables, we’ll use that as the lookup value. The table array will be the range containing our “Sales scenarios” table. The only tricky part is figuring out the row_index_num, since the row_index_num will be different depending on which scenario is selected in the dropdown menu. To accomodate that, we’ll nest in a match() function. The match function searches a list (arranged as either a single row or column of values) and tells you which position in the list it finds the given input. In our example, we want to know which row in the sales scenarios table our selected scenario resides. If we think of the row labels as a list of values arranged in a column, we can use the match() function to tell us which row our desired sales figures are since the position in the list corresponds to rows in the table.

It’s important to note, the way I’ve set up the tables. The sales table is 4 rows by 12 columns. It’s 4 rows because we have to count the date index row. To have the match() function return the proper row to the hlookup() function, we have to make sure that the table in the match function has the same number of rows as the table used in the hlookup function. In our example, both the sales_scenarios and the scenario_names ranges have 4 rows.

The lookup value for the match() function here is scenario_inputs which a reference to the value selected in the dropdown menu on the Input sheet. When the value in the dropdown changes, the results of the match() function change as well. This will pull a different row from the scenarios table into our output table.

My scenario_names range contains just the first column of the sales scenarios table. This column has the names of the scenarios that match the values in the dropdown input cell.

Outputs

For convience, I like to put a copule key outputs next to the input selector. That way, if someone doesn’t have two monitors or doesn’t want to open two windows of the same book, they can view the inputs and the outputs together in one place without having to bounce between sheets.

In my example, the output of the model is simply the total sales for 12 months. In the example workbook, you can see that by changing the dropdown menu, the total sales output cell on the same sheet will display the appropriate number for that scenario.

Conclusion

Feel free to leave questions and suggestions in the comments. What other tricky Excel situations have people run into?