Co-Founder at Career Principles | Microsoft MVP
Excel’s financial modeling tools can help you monitor outputs, catch input errors and test scenarios without repeatedly navigating between worksheets. They are useful for analysts and other Excel users who build or review forecasts with changing assumptions. Kenji Farré (Kenji Explains) [MVP] demonstrates these techniques in a YouTube video.
These techniques focus on making a financial model easier to use and inspect rather than replacing its underlying calculations. The Watch Window tracks selected cells as you change assumptions elsewhere, while Freeze Panes and grouped rows or columns make larger workbooks easier to navigate.
Model checks can flag conditions such as negative cash or a balance sheet that does not balance. Dynamic dates and dropdowns reduce repetitive edits and typos. For analysis, Goal Seek works backward from a target result, while a two-variable Data Table shows how an output changes across combinations of assumptions.
The techniques are aimed at people working with financial models in Excel, including analysts who change inputs and review outputs across worksheets. They start with an existing workbook containing model assumptions, calculations and results; the examples use annual and monthly figures, scenario names, cash balances and profit.
The steps use Excel features found on the Formulas, View, Data and File tabs. A few steps depend on the model being set up appropriately: Goal Seek needs a target result cell and a cell it can change, and a Data Table needs an output linked to the model plus input cells corresponding to its row and column assumptions.
A reusable formatting guide can also standardize colors and number formats. The demonstration saves a workbook template as an .xltx file named “Book” in the Excel XLSTART folder, so it opens with new workbooks. A format painter can copy a custom number format, such as displaying a negative percentage in parentheses.
These tools do not substitute for checking the model’s logic. A comparison that returns TRUE or FALSE only tests whether the two referenced cells are equal; it does not verify every part of a balance sheet or explain a mismatch. Likewise, an “OK” cash check depends on pointing the formula at the intended cash value and testing the condition the model needs.
Goal Seek changes one input cell to reach one target in a result cell. When the question involves two assumptions at once, a Data Table is the demonstrated alternative. Set its row and column input cells carefully: swapping them can make the sensitivity grid represent the wrong assumptions. Freeze Panes also depends on selecting the correct split point before applying it.
Select the result cells and choose Formulas > Watch Window. The selected values remain visible and update as you change inputs, including on another worksheet.
Choose Data > What-If Analysis > Goal Seek. Set the result cell, enter the target value, and select the input cell Excel should change to reach it.
Link the table’s output corner to the model result, select the table, and choose Data > What-If Analysis > Data Table. Assign the row and column input cells to the matching assumptions.
Use Data > Data Validation and select List. Enter or select the allowed scenario names so the cell is restricted to those options.