Transcript

Hi there, and welcome to this video on Advanced Excel skills for financial modeling. I’m Ian Schnoor, and I’m looking forward to spending the next hour with you, showing you some amazing, powerful techniques that you can use in financial models—and in many other spreadsheet tools you build in your job. So let’s dive right in. I’m going to share my screen and start by showing you what we’ll cover.

First of all, I hope you’ve had a chance to watch the prior videos I recorded on financial-modeling best practices and on skills to check and review a model. This is the next one in the series: Advanced Skills You Can Include in a Financial Model. If you didn’t see the other ones, here’s a quick introduction: my name is Ian Schnoor. I’m based in Toronto, Canada, and I’m the Executive Director of the Financial Modeling Institute, the world’s only financial-modeling accreditation organization. I’ve spent my entire career building financial models and teaching people how to build them: I was an investment banker, then founded a training company that I ran for 20 years, teaching thousands of modelers around the world how to build best-in-class models. I also created the Financial Modeling Practical Skills module now used in the CFA program, and I teach as an adjunct lecturer at Queen’s University in their Master of Finance program. I’ve been thinking about modeling for a long time, and I hope to share some ideas with you in this video that you’ll find very helpful, very quickly.

As I mentioned, FMI is the world’s only financial-modeling accreditation organization. Candidates prepare with our learning materials, learn modeling, then sit our four-hour Level 1 AFM exam—during which they must build a complete financial model of a company from scratch. Those who achieve the AFM designation can truly attest to their excellence as modelers: anyone who gets through that exam has extremely strong Excel skills and has learned many of the techniques I’ll share today.

So let’s dive into the skills. I’m shifting over to a notes file and a sample model. It’s the same one from the prior videos—one of our Level 1 AFM sample models for Henderson Manufacturing. Candidates are expected to build this model from scratch in under four hours on the AFM exam, and I’ll use it to demonstrate critical Excel techniques.

Here are the skills I want to leave you with in today’s video:

1. Centering — how to properly center titles and the pitfalls of “Merge & Center.”

2. Advanced Formatting Techniques — powerful ideas around custom number formats and multi-condition formatting.

3. Managing Text in Models — dynamic sentences, footnotes, and how to automate them.

4. Building Scenarios — a refresher on scenarios from the best-practices video, plus a couple of switching mechanisms.

5. Switching Mechanisms — elegant, user-friendly switches for your scenario page.

1. Centering

Centering titles is very common in models: on the cover sheet, the scenarios page, and throughout each sheet. Many people use Excel’s Merge & Center, which merges all selected cells into one giant cell and centers the text. It looks fine, but merged cells make it hard to insert or delete columns, and they break formulas. Instead, use Center Across Selection:

• Select the range (e.g., columns B through Q).

• Press Ctrl + 1 to open the Format Cells dialog.

• On the Alignment tab, choose Center Across Selection under Horizontal alignment.

This centers your title across the range without merging any cells, making the model much more maintainable.

2. Advanced Formatting Techniques

Every cell in a financial model must be crystal clear so your reader knows exactly what each number means. If you type “25 years” or “< 4.5 x” directly, Excel treats it as text, not a numeric value you can use in formulas. Instead:

1. Enter the numeric value only (e.g., 25 or 4.5).

2. Press Ctrl + 1, choose Custom, and build formats like:

   • 0" years" (shows “25 years” while storing 25)

   • "<"0.0"x" (shows “< 4.5 x” while storing 4.5)

You can define up to four formats in one custom string—positive; negative; zero; text—such as:

+0.0; (0.0); –; "This cell should contain a value"

This shows a plus sign for positives, parentheses for negatives, a dash for zero, and a message if text is entered.

3. Managing Text in Models

Often you need dynamic sentences like “This model forecasts from 2025 to 2029” or “The tax rate used in the model is 25%.” Typing these directly makes them static. Instead, use CONCATENATE (or &) and, to preserve number formatting, wrap references in the TEXT function:

Now, if you change the first year, last year, or tax rate, the sentence updates automatically.

4. Building Scenarios

Every model needs a scenarios page to run base, best, and worst cases. Three steps:

1. Assumptions table: list base, best, worst inputs (blue cells).

2. Switch cell: enter 1, 2, or 3 to choose the case.

3. Live-case row: retrieve the active case using a lookup.

Avoid long nested IF statements. Instead, use:

• CHOOSE: =CHOOSE(switch, base, best, worst)

• INDEX: =INDEX(range, switch)

• OFFSET: =OFFSET(reference, switch – 1, 0)

5. Switching Mechanisms

In the earlier best-practices video, I showed a combo-box form control so users can click Base, Best, or Worst, and the control automatically enters the corresponding number in the switch cell. To set it up:

1. Enable the Developer tab (File → Options → Customize Ribbon → check Developer).

2. Developer → Insert → Combo Box (Form Control), then draw it.

3. Right-click the combo box → Format Control, set the Input Range to your three labels and the Cell Link to the switch cell.

4. Resize and position it over the switch cell for an elegant, user-friendly interface.

Some people prefer a Data Validation dropdown (Alt +A V V → List → point to your labels). That creates a dropdown in the cell, but it puts text in the switch cell—so you’d need a VLOOKUP or similar to map it back to a number, and it’s less obvious to users that it’s a switch. However, Data Validation excels at restricting inputs (e.g., enforce a tax rate between 25% and 40%) with custom input and error messages.

That wraps up this video. We’ve covered centering techniques, advanced formatting, dynamic text and footnotes, scenario building with lookup functions, and switching mechanisms. I hope you’ll put these tools to work to enhance the clarity, flexibility, and confidence of all your spreadsheets. Thanks for watching, and I look forward to sharing more modeling topics with you down the road!