The best Excel tip you've never heard of. Try Excel’s Watch Window explained by FMI’s Executive Director, Ian Schnoor, CFA, CFM, and use it to monitor how changes to your assumptions impact key metrics like KPIs.

Transcript

Well, here’s a problem that some people get into: you make a change to your assumptions, but you have to bounce around your workbook to see what effect it’s had. For example, if I return to the assumptions sheet and discover that the raw material cost is no longer $226 per unit but $250, I update the cell to 250 and press Enter. Great—but what happened to my key outputs? What did that do to net income or EBITDA? I don’t know until I switch back to the model sheet, hunt down those results, and then jump back to assumptions if I want to try another figure, say $240. Every single change forces this frustrating back-and-forth, and many people wish they could adjust assumptions and immediately see the impact on their answers.

The good news is that Excel has a built-in feature to solve exactly this problem: the Watch Window. You’ll find it under the Formulas menu—shortcut Alt M W. Let’s add a few watches. First, I open the Watch Window box and click “Add Watch,” then select the first three years of EBITDA on my model sheet and hit “Add.” Presto: those three values now appear in the Watch Window with their current figures. Next, I click “Add Watch” again, select the three years of net income, and add them. Now I have both EBITDA and net income snapshots living in that little floating box.

What makes this so powerful is that you can collect any outputs from anywhere in your workbook—valuation sheets, ratio pages, you name it—and they all reside in one place. Watch this: I return to assumptions, change raw materials back to $250, and press Enter. Instantly, every number in the Watch Window updates. No more hunting around. Every time you tweak an input, all watched answers adapt automatically, giving you an “executive summary” of results without losing flow.

Sometimes you might forget which line is EBITDA and which is net income. No problem—name your cells. In the Watch Window list there’s a column called “Name.” Go to your financial statements, click the EBITDA cell for Year 1, click into the Name Box at the top left, type “EBITDA,” and press Enter. That cell is now labeled “EBITDA,” and the Watch Window shows “EBITDA” instead of “K92.” Do the same for the net income cell—label it “Net Income”—and voilà. Now, any assumption change upfront instantly shows you exactly what’s happening deeper in your model, with clear labels. Change the material cost back to $226, hit Enter, and watch all your named outputs update in real time.

The Watch Window is an incredibly powerful tool that streamlines model review by letting you set up an instant dashboard of critical results. I highly encourage you to use it in all your Excel spreadsheets—it creates optimal flow by keeping both assumptions and answers visible at once. Close it out, and return to your notes—now you know exactly what’s happening to your answers as soon as you make a change to your assumptions. I hope you find it as helpful as I do.