Select Page

# Insights

### Excel: Using F9 to Audit Formulas

I was recently provided a workbook containing a balance sheet with over 700 line items. Many of the line items showed a value of zero, making the challenge one of extracting all nonzero values without having to go through and delete line items one row at a time. This required a slightly more complex formula, which frankly I got wrong a handful of times before finally arriving at the solution. And in that process of auditing (or debugging) the formula, the F9 key was fantastically useful.

### Excel: =XIRR vs =IRR

Context: I recently received a question from an entrepreneur in the process of negotiating the sale of a control equity position in his business where he would remain post transaction as CEO and a minority shareholder. Most everything detailed in the offer was clear except for a footnote explaining that IRR’s referenced in the term sheet would be calculated using the =XIRR formula in Excel. This was of particular interest because his ownership had the potential to grow substantially post transaction if the business generated certain “hurdle” IRRs at exit. (As a crude example, imagine that at an IRR of 15% the entrepreneur would be entitled to an additional X number of shares, and at an IRR of 30% an additional 2X shares, etc.)

### Explain the relationship between retained earnings, net income and dividends.

This question made me realize that I should add some material demonstrating how dividends flow through the three financial statements. In the interim please see the Q&A that follows:

### Excel Hack: =CountIf with an Asterisk

A friend of mine recently emailed asking for help with a formula. In an effort to build a dashboard that would provide information on the buildings and apartments he manages, he was looking for a formula that could return the number of units he had a available for rent within a certain time period.

### Financial Projections: A Hands-On Approach

In a financial model it is common to see measures of profitability averaged historically and projected forward. This is, after all, the manner in which building a five-year projection is presented in Integrating Financial Statements on this website. As an introduction to financial modeling this is a suitable approach, but as you graduate from projections built off of fictional historical data to real-world modeling exercises, attention should be paid to the detail that comprises expenses.