• 034 08/27/2015 Error Identified!

    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?"

    A: I kept the positive introduction to make myself feel better about the error... The user, let's call him Craig (because that's his name), identified an error that would otherwise be difficult for user's to spot because it is (fortunately) not the focus of the educational content. But as he points out, if you are using the template for another company the error stands out.

    This provides a great opportunity to point out that you should always audit any template. I don't write this to excuse my mistake, but errors are commonplace in financial models.

    How it happened: My best guess is that I originally did not have a "Total Interest Expense" line on the debt schedule, and that I instead summed RLOC related interest and interest from other debt tranches on the income statement. Then, in proofing the model, concluded that I should total all interest expense on the debt schedule without making the corresponding change on the income statement. The template has since been updated to correct for this error (LINK).

    Effects of the correction: What you will notice now is that towards the conclusion of the video the figures for net income in the template do not match the values in the video. Fortunately, the focus of the video is to project stockholders' equity, which is demonstrated on a separate template with static figures for net income. It is not until the conclusion of the video that the supporting schedule is linked to a live model.

    Proofing at ASM: The proofing process at ASimpleModel is limited as "we" are a one-man operation. Typically the way it works is that I prepare the content and then circulate it to a handful of colleagues that work for hedge funds or private equity groups. As the site evolves and continues to gain traction I hope to expand upon this process. 

    In the interim I plan to publish errors identified, and update the materials accordingly. 



 



 



  • 032 07/21/2015 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.

    In the video that follows I recreated the scenario on a much smaller data set so that it would be easier to visualize:

    ASM Challenge: Work through the rest of the formula to better understand how it works. This process will expose you to some useful Excel functions including =IFERROR and =INDEX, as well as others that are less common, but very helpful when used in the correct context including =ISNUMBER, =ROW, =COUNTBLANK and =SMALL. 

    You can expand the worksheet (download here) to expose rows 11 through 31 by ungrouping the selection. In that grouping you will find the formula broken down into three steps to facilitate the process.

    I will post the answer as a follow up.

    Update: Click here for the solution.

 




 



Models are:
 
A) really boring
B) pretty sweet
C) super important
D) somewhat easy
E) kind of hard
F) fun
G) all of the above

 

 


*Answers a, b, c, d, e, f and g are all correct.