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 (Edited for Clarity)
Here’s a problem that many people run into: you make a change to your assumptions, but you have to jump around your workbook to see the effect. For example, suppose raw material costs are no longer $226 per unit but $250. I update the cell to 250 and press Enter. Fine, 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, track down those results, and then flip back to assumptions if I want to test another figure, say $240. Every change forces this back and forth, and it can be frustrating. Many people wish they could adjust assumptions and immediately see the impact on the answers.
The good news is that Excel has a built-in feature for exactly this problem: the Watch Window. You will find it under the Formulas menu, shortcut Alt + M + W. Let’s add a few watches. First, I open the Watch Window and click "Add Watch," then select the first three years of EBITDA on my model sheet and hit "Add." Those three values now appear in the Watch Window with their current figures. Next, I click "Add Watch" again, select three years of net income, and add them as well. Now I have both EBITDA and net income snapshots sitting in that floating box.
What makes this so powerful is that you can collect outputs from anywhere in your workbook, including valuation sheets or ratio pages, and keep them all 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. Each time you tweak an input, all watched outputs adapt automatically, giving you an "executive summary" without losing flow.
Sometimes you might forget which line is EBITDA and which is net income. That is easy to fix by naming your cells. In the Watch Window list there is a column called "Name." On your financial statements, click the EBITDA cell for Year 1, go to the Name Box at the top left, type "EBITDA," and press Enter. Now that cell is labeled, and the Watch Window shows "EBITDA" instead of "K92." Do the same for the net income cell and label it "Net Income." From then on, any assumption change instantly shows you exactly what is happening in your model with clear labels. Change the material cost back to $226, press Enter, and watch both EBITDA and net income update in real time.
The Watch Window is a powerful tool that streamlines model review by letting you set up an instant dashboard of critical results. I 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, return to your notes, and you will know exactly what is happening to your outputs as soon as you make a change.
