Insights

I recently received a question from a user that highlighted the first error identified on the site since we launched in 2013:

Q: “I really appreciate your site and model. I’ve found it very valuable. I was rebuilding your model for an additional company and believe you may be double counting the RLOC interest. On row 28 of the completed Home Depot model you are adding rows 168 and 253 which are the RLOC interest rows and total interest rows. The total interest row (row 253) adds rows 168 and 251, thus I believe row 168 is being included twice. Am I overlooking something?”

This is a follow up to the post titled “Excel: Using F9 to Audit Formulas.” This video will explain how to extract nonblank or nonzero data from an array without using an array formula.

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.

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.)

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: