Spreadsheets can be a valuation analyst’s best friend, or a worst enemy, according to Shawn Hyde (Canyon Valuations), a valuation practitioner and Excel enthusiast. In fact, he teaches classes in the power and potential of Excel for business valuation. He recently shared some tips and tricks he’s learned from over 20 years of using spreadsheets (starting with Lotus 1-2-3!) for valuation analysis.

Check this out: “One of my biggest problems was linking a formula to a very specific number on a different spreadsheet and then, after completing my analysis, finding out that the very specific number I had selected originally had been replaced by some other number,” recalls Hyde. Then he discovered a pair of “magical functions” that solved the problem: INDEX and MATCH. “When used together, the effect is awesome because it now incorporates the very labels that I use in my spreadsheets to identify my data in the exact formulas I use to analyze that data!”

For example, a common line on Excel is labeled “Officer Compensation,” and this expense may need a normalizing adjustment, so instead of typing “=” or “+” and then clicking on the number in the spreadsheet and hoping it stays there, use the INDEX and MATCH functions to make sure the data are not being mislabeled. “If my data happens to move on my spreadsheet, the references are locked in so it doesn’t matter, as the formula will always return exactly what it says it is presenting,” says Hyde.

New book: Hyde is the author of Building the Essential BV Templates in Excel, in which he walks you through the nuts and bolts of building Excel templates for business valuation analysis. You can cut and paste formulas from the book right into your spreadsheets. Plus, you get web access to Excel template samples and a video that demonstrates the INDEX and MATCH functions! (Note: If you are a subscriber to BVResearch Pro, this book is included with your subscription.)