Formatting Conventions

Importance of Formatting in Models

  • An unformatted model is considered a deal breaker in investment banking.
    • Difficult to Understand: If a model is poorly formatted, it becomes challenging to interpret.
    • Negative Impression: Reflects poorly on the individual and the firm, especially if shared with clients.
    • Internal Use: Even for internal use, an unformatted model might be assumed as subpar, risking its utility despite excellence in analysis.

Best Practices for Formatting

  • Consistency is Key:

    • Essential to maintain uniformity in formatting to ensure clarity.

  • Format Cells:

    • Use Control + 1 to open the format cells dialog box.
    • Remember shortcuts for efficient formatting.

Color Coding Conventions

  • Hard Coded Numbers:

    • Color: Blue

  • Formulas:

    • Color: Black

  • Links to Other Worksheets:

    • Color: Green

  • Links to Other Files:

    • Color: Red

  • Links to Data Providers (e.g., Capital IQ, FactSet, Thompson):

    • Distinguish using unique formatting to indicate reliance on online servers.
    • Important for quick identification of cell status and potential connectivity issues.

Use of Boost for Formatting

  • Automation of Formatting:
    • Boost can auto color code inputs, reducing manual effort and ensuring consistency.

Custom Formatting Tips

  • Multiple Representation:

    • To represent a multiple (e.g., 8.5 times EBITDA), use a custom format.
    • Input: Enter number (e.g., 8.5) and format it as 0.0extx0.0 ext{x} to retain numerical integrity.
    • Using this method allows for multiplication without errors, retaining properties of a number.

  • Formatting for Alignment:

    • The underscore (_) in custom formats creates space for alignment between negative and positive numbers, improving readability.
    • Syntax Example: 0.0 ext{x} for positive; 0.0 ext{x}; (0.0 ext{x}) for negative aligned formatting.

  • Using Quotes:

    • Any letters other than 'x' need quotes in formatting to keep their integrity as numbers (e.g., "days").

    • Example for Custom Row Headers: Use 30 and format it as 0 "days" to keep it as a number while displaying as "30 days". This makes it useful for calculations without compromising its numeric status.

General Formatting Consistency

  • Maintain standard formats across numbers (e.g., one decimal place for all relevant data).
    • Per Share Data: Often presented in two decimal places (e.g., EPS of 2.16).
  • Maintain consistent representation of negative numbers as parentheses rather than minus signs.
  • Standardize column spacing and headers for uniformity, enhancing model professionalism and clarity.

Excel Efficiency Techniques

  • Change column widths in bulk:

    • Shortcut: Alt + O + C + W allows modification of width from 8.43 to 12.

  • Adjust row heights efficiently:

    • Use Shift + Spacebar to select, then Alt + O + R + E to modify row heights uniformly.

Modeling Rules of Thumb

  • Avoid Redundant Inputs:
    • Reentering the same input in different locations leads to static models. Always reference the original calculations to maintain dynamics.
  • WACC Calculation Example:
    • Avoid hard coding WACC in multiple areas; always link back to the original calculation for accuracy and ease of updates.