Cell References In A Formula Are Called Assumptions And Critical Thinking

To Analyze Thinking We Must Identify and Question its Elemental Structures

Standard: Clarity
understandable, the meaning can be grasped

  • Could you elaborate further?
  • Could you give me an example?
  • Could you illustrate what you mean?
  • Standard: Accuracy
    free from errors or distortions, true

  • How could we check on that?
  • How could we find out if that is true?
  • How could we verify or test that?
  • Standard: Precision
    exact to the necessary level of detail

  • Could you be more specific?
  • Could you give me more details?
  • Could you be more exact?
  • Standard: Relevance
    relating to the matter at hand

  • How does that relate to the problem?
  • How does that bear on the question?
  • How does that help us with the issue?
  • Standard: Depth
    containing complexities and multiple interrelationships

  • What factors make this a difficult problem?
  • What are some of the complexities of this question?
  • What are some of the difficulties we need to deal with?
  • Standard: Breadth
    encompassing multiple viewpoints

  • Do we need to look at this from another perspective?
  • Do we need to consider another point of view?
  • Do we need to look at this in other ways?
  • Standard: Logic
    the parts make sense together, no contradictions

  • Does all this make sense together?
  • Does your first paragraph fit in with your last?
  • Does what you say follow from the evidence?
  • Standard: Significance
    focusing on the important, not trivial

  • Is this the most important problem to consider?
  • Is this the central idea to focus on?
  • Which of these facts are most important?
  • Standard: Fairness
    Justifiable, not self-serving or one-sided

  • Do I have any vested interest in this issue?
  • Am I sympathetically representing the viewpoints of others?
  • More Standards:
    There are numerous other standards that may be applied to elements on a contextual basis. Here are just a few:
    Completeness, Validity, Rationality, Sufficiency, Necessity, Feasabilty, Consistency, Authenticity, Effectiveness, Efficiency
    Can you identify others standards relevant to your situation?

    RESET
     VIEW

    Why the Analysis of Thinking is Important

    Everyone thinks; it is our nature to do so. But much of our thinking, left to itself, is biased, distorted, partial, uninformed, or downright prejudiced. If we want to think well, we must understand at least the rudiments of thought, the most basic structures out of which all thinking is made. We must learn how to take thinking apart.

    All Thinking Is Defined by the Eight Elements That Make It Up. Eight basic structures are present in all thinking: Whenever we think, we think for a purpose within a point of view based on assumptions leading to implications and consequences. We use concepts, ideas and theories to interpret data, facts, and experiences in order to answer questions, solve problems, and resolve issues.

    Thinking, then:
    • generates purposes
    • raises questions
    • uses information
    • utilizes concepts
    • makes inferences
    • makes assumptions
    • generates implications
    • embodies a point of view
    Simply "Mouse Over" any object on the page to learn more about it.

    Element: Purpose    All reasoning has a PURPOSE.

  • Take time to state your purpose clearly.
  • Distinguish your purpose from related purposes.
  • Check periodically to be sure you are still on target.
  • Choose significant and realistic purposes.
  • Element: Question    All reasoning is an attempt to figure something out, to settle some QUESTION, to solve some problem.

  • State the question at issue clearly and precisely.
  • Express the question in several ways to clarify its meaning.
  • Break the question into sub-questions.
  • Distinguish questions that have definitive answers from those that are a matter of opinion or that require multiple viewpoints.
  • Element: Information    All reasoning is based on DATA, INFORMATION and EVIDENCE.

  • Restrict your claims to those supported by the data you have.
  • Search for information that opposes your position as well as information that supports it.
  • Make sure that all information used is clear, accurate and relevant.
  • Make sure you have gathered sufficient information.
  • Element: Interpretation and Inference    All reasoning contains INFERENCES or INTERPRETATIONS by which we draw CONCLUSIONS and give meaning to data.

  • Infer only what the evidence implies.
  • Check inferences for their consistency with each other.
  • Identify assumptions underlying your inferences.
  • Element: Concepts    All reasoning is expressed through, and shaped by, CONCEPTS and IDEAS.

  • Identify key concepts and explain them clearly.
  • Consider alternative concepts or alternative definitions of concepts.
  • Make sure you are using concepts with precision.
  • Element: Assumptions
    All reasoning is based on ASSUMPTIONS.

  • Clearly identify your assumptions and determine whether they are justifiable.
  • Consider how your assumptions are shaping your point of view.
  • Element: Implications    All reasoning leads somewhere or has IMPLICATIONS and CONSEQUENCES.

  • Trace the implications and consequences that follow from your reasoning.
  • Search for negative as well as positive implications.
  • Consider all possible consequences.
  • Element: Point Of View    All reasoning is done from some POINT OF VIEW.

  • Identify your point of view.
  • Seek other points of view and identify their strengths as well as weaknesses.
  • Strive to be fairminded in evaluating all points of view.
  • Key Concept:

    Think About... Purpose

    Your purpose is your goal, your objective,
    what you are trying to accomplish. We also use the term to include functions, motives, and intentions.


    You should be clear about your purpose, and your purpose should be justifiable.

    Questions which target purpose
  • What is your, my, their purpose in doing________?
  • What is the objective of this assignment (task, job, experiment, policy, strategy, etc.)?
  • Should we question, refine, modify our purpose (goal, objective, etc.)?
  • What is the purpose of this meeting (chapter, relationship, action)?
  • What is your central aim in this line of thought?
  • What is the purpose of education?
  • Why did you say…?
  • State the Question

    The question lays out the problem or issue and
    guides our thinking. When the question is vague, our thinking will lack clarity and distinctness.


    The question should be clear and precise enough to productively guide our thinking.
    Questions which target the question
  • What is the question I am trying to answer?
  • What important questions are embedded in the issue?
  • Is there a better way to put the question?
  • Is this question clear? Is it complex?
  • I am not sure exactly what question you are asking. Could you explain it?
  • The question in my mind is this: How do you see the question?
  • What kind of question is this? Historical? Scientific? Ethical? Political? Economic? Or…?
  • What would we have to do to settle this question?
  • Gather... Information

    Information includes the facts, data, evidence, or experiences we use to figure things out. It does not necessarily imply accuracy or correctness.

    The information you use should be accurate and relevant to the question or issue you are addressing.
    Questions which target information
  • What information do I need to answer this question?
  • What data are relevant to this problem?
  • Do we need to gather more information?
  • Is this information relevant to our purpose or goal?
  • On what information are you basing that comment?
  • What experience convinced you of this? Could your experience be distorted?
  • How do we know this information (data, testimony) is accurate?
  • Have we left out any important information that we need to consider?
  • Watch Your... Inferences

    Inferences are interpretations or conclusions you come to. Inferring is what the mind does in figuring something out.
    Inferences should logically follow from the evidence. Infer no more or less than what is implied in the situation.
    Questions to check your inferences
  • What conclusions am I coming to?
  • Is my inference logical?
  • Are there other conclusions I should consider?
  • Does this interpretation make sense?
  • Does our solution necessarily follow from our data?
  • How did you reach that conclusion?
  • What are you basing your reasoning on?
  • Is there an alternative plausible conclusion?
  • Given all the facts what is the best possible conclusion?
  • How shall we interpret these data?
  • Clarify Your... Concepts

    Concepts are ideas, theories, laws, principles, or hypotheses we use in thinking to make sense of things.
    Be clear about the concepts you are using and use them justifiably.
    Questions you can ask about concepts
  • What idea am I using in my thinking? Is this idea causing problems for me or for others?
  • I think this is a good theory, but could you explain it more fully?
  • What is the main hypothesis you are using in your reasoning?
  • Are you using this term in keeping with established usage?
  • What main distinctions should we draw in reasoning through this problem?
  • What idea is this author using in his or her thinking? Is there a problem with it?
  • Check Your... Assumptions


    Assumptions are beliefs you take for granted. They usually operate at the subconscious or unconscious level of thought.
    Make sure that you are clear about your assumptions and they are justified by sound evidence.
    Questions you can ask about assumptions
  • What am I assuming or taking for granted?
  • Am I assuming something I shouldn’t?
  • What assumption is leading me to this conclusion?
  • What is… (this policy, strategy, explanation) assuming?
  • What exactly do sociologists (historians, mathematicians, etc.) take for granted?
  • What is being presupposed in this theory?
  • What are some important assumptions I make about my roommate, my friends, my parents, my instructors, my country?
  • Think Through the...
    Implications and Consequences

    Implications are claims or truths that logically follow from other claims or truths. Implications follow from thoughts. Consequences follow from actions.
    Implications are inherent in your thoughts, whether you see them or not. The best thinkers think through the logical implications in a situation before acting.

    Questions you can ask about implications
  • If I decide to do “X”, what things might happen?
  • If I decide not to do “X”, what things might happen?
  • What are you implying when you say that?
  • What is likely to happen if we do this versus that?
  • Are you implying that…?
  • How significant are the implications of this decision?
  • What, if anything, is implied by the fact that a much higher percentage of poor people are in jail than wealthy people?
  • Understand Your...
    Point of View

    Point of view is literally “the place” from which you
    view something. It includes what you are looking at and the way you are seeing it.

    Make sure you understand the limitations of your point of view and that you fully consider other relevant viewpoints.
    Questions to check your point of view
  • How am I looking at this situation? Is there another way to look at it that I should consider?
  • What exactly am I focused on? And how am I seeing it?
  • Is my view the only reasonable view? What does my point of view ignore?
  • Have you ever considered the way ____(Japanese, Muslims, South Americans, etc.) view this?
  • Which of these possible viewpoints makes the most sense given the situation?
  • Am I having difficulty looking at this situation from a viewpoint with which I disagree?
  • What is the point of view of the author of this story?
  • Do I study viewpoints that challenge my personal beliefs?
  • Introduction

    Like many computer programmers, people who build financial models can get quite opinionated about the “right way” to do it.

    In fact, there is surprisingly little consistency across Wall Street around the structure of financial models. One reason is that models can vary widely in purpose. For example, if your task was to build a discounted cash flow (DCF) model to be used in a preliminary pitch book as a valuation for one of 5 potential acquisition targets, it would likely be a waste of time to build a highly complex and feature-rich model. The time required to build a super complex DCF model isn’t justified given the model’s purpose.

    On the other hand, a leveraged finance model used to make thousands of loan approval decisions for a variety of loan types under a variety of scenarios necessitates a great deal of complexity.

    Understanding the purpose of the model is key to determining its optimal structure. There are two primary determinants of a model’s ideal structure: granularity and flexibility. Let’s consider the following 5 common financial models:

    ModelPurposeGranularityFlexibility
    One page DCFUsed in a buy side pitch book to provide a valuation range for one of several potential acquisition targets.Low. Ball-park valuation range is sufficient) / Small. Entire analysis can fit on one worksheet < 300 rows)Low. Not reusable without structural modifications. Will be used in a specific pitch and circulated between just 1-3 deal team members.
    Fully integrated DCFUsed to value target company in a fairness opinion presented to the acquiring company board of directorsMediumLow. Not reusable without structural modifications. Will be tailored for use in the fairness opinion and circulated between deal time members.
    Comps model templateUsed as the standard model by the entire industrials team at a bulge bracket bankMediumHigh. Reusable without structural modifications. A template to be used for a variety of pitches and deals by many analysts and associates, possibly other stakeholders. Will be used by people with varying levels of Excel skill.
    Restructuring modelBuilt specifically for a multinational corporation to stress test the impact of selling 1 or more businesses as part of a restructuring advisory engagementHighMedium. Some re-usability but not quite a template. Will be used by both the deal team and counterparts at the client firm.
    Leveraged finance modelUsed in the loan approval process to analyze loan performance under various operating scenarios and credit eventsHighHigh. Reusable without structural modifications. A template to be used group wide.

    Financial model granularity

    A critical determinant of the model’s structure is granularity. Granularity refers to how detailed a model needs to be. For example, imagine you are tasked with performing an LBO analysis for Disney. If the purpose is to provide a back-of-the-envelope floor valuation range to be used in a preliminary pitch book, it might be perfectly appropriate to perform a “high level” LBO analysis, using consolidated data and making very simple assumptions for financing.

    Master financial modeling!Used at top investment banks, Wall Street Prep's Premium Package will you set you apart in interviews and on the job.

    Learn More

    If, however, your model is a key decision making tool for financing requirements in a potential recapitalization of Disney, a far higher degree of accuracy is incredibly important. The differences in these two examples might involve things like:

    • Forecasting revenue and cost of goods segment by segment and using price-per-unit and #-units-sold drivers instead of aggregate forecasts
    • Forecasting financials across different business units as opposed to looking only at consolidated financials
    • Analyzing assets and liabilities in more detail (i.e. leases, pensions, PP&E, etc.)
    • Breaking out financing into various tranches with more realistic pricing
    • Looking at quarterly or monthly results instead of annual results

    Practically speaking, the more granular a model, the longer and more difficult it will be to understand. In addition, the likelihood of errors grows exponentially by virtue of having more data. Therefore, thinking about the model’s structure — from the layout of the worksheets to the layout of individual sections, formulas, rows and columns — is critical for granular models. In addition, integrating formal error and “integrity” checks can mitigate errors.

    Financial model flexibility

    The other main determinant for how to structure a model is its required flexibility. A model’s flexibility stems from how often it will be used, by how many users, and for how many different uses. A model designed for a specific transaction or for a particular company requires far less flexibility than one designed for heavy reuse (often called a template).

    As you can imagine, a template must be far more flexible than a company specific or “transaction specific model. For example, say that you are tasked with building a merger model. If the purpose of the model is to analyze the potential acquisition of Disney by Apple, you would build in far less functionality than if its purpose was to build a merger model that can handle any two companies. Specifically, a merger model template might require the following items that are not required in the deal-specific model:

    1. Adjustments to acquirer currency
    2. Dynamic calendarization (to set target’s financials to acquirer’s fiscal year)
    3. Placeholders for a variety of income statement, balance sheet and cash flow statement line items that don’t appear on Disney or Apple financials
    4. Net operating loss analysis (neither Disney or Apple have NOLs)

    Together, granularity and flexibility largely determine the structural requirements of a model. Structural requirements for models with low granularity and a limited user base are quite low. Remember, there is a trade-off to building a highly structured model: time. If you don’t need to build in bells and whistles, don’t. As you add granularity and flexibility, structure and error proofing becomes critical.

    The table below shows the granularity/flexibility levels of common investment banking models.

    High flexibilityLow flexibility
    High granularity
    • Leveraged finance credit model
    • Merger model template “one size fits all”
    • Integrated LBO model
    • Integrated DCF model
    • Integrated Merger Model
    • Integrated operating model
    Low granularity
    • Trading comps template
    • Transaction comps template
    • “Back of the envelope” accretion/dilution model
    • DCF “one pager”
    • LBO “one pager”
    • Simple operating model

    Financial model present-ability

    Regardless of granularity and flexibility, a financial model is a tool designed to aid decision making. Therefore, all models must have clearly presented outputs and conclusions. Since virtually all financial models will aid in decision-making within a variety of assumptions and forecasts, an effective model will allow users to easily modify and sensitize a variety of scenarios and present information in a variety of ways.

    Now that we have established a simple framework for structuring models, it’s time to discuss specific features of model architecture, error proofing, flexibility and presentation.

    Financial model structure

    Below, we lay out the key elements of an effectively structured model, most of which will go a long to way to improve the model’s transparency. As a model becomes more complex (due to higher granularity and flexibility), it naturally becomes less transparent. The best practices below will help to fix this.

    Formatting

    Color coding

    Just about everyone agrees that color coding cells based on whether it holds a hard coded number or a formula is critical. Without color coding, it is extremely difficult to visually distinguish between cells that should be modified and cells that should not ( i.e. formulas). Well built models will further distinguish between formulas that link to other worksheets and workbooks as well as cells that link to data services.

    While different investment banks have different house styles, blue is typically used to color inputs and black is used for formulas. The table below shows our recommended color coding scheme.

    Type of cellsExcel formulaColor
    Hard-coded numbers (inputs)=1234Blue
    Formulas (calculations)=A1*A2Black
    Links to other worksheets=Sheet2!A1Green
    Links to other files=[Book2]Sheet1!$A$1Red
    Links to data providers (i.e. CIQ, Factset)=CIQ(IQ_TOTAL_REV)Dark Red

    While everyone agrees that color coding is very important, keeping up with it can be a pain in native Excel. It’s not easy to format cells based on whether they are inputs or formulas, but it can be done. One option is to use Excel’s “Go To Special” (covered in our Excel Crash Course, which you can enroll in here). Alternatively, color coding is dramatically simplified with a third party Excel add-in like our own Boost, Capital IQ or Factset, which all allow you to “autocolor” an entire worksheet in one click.

    Comments

    Inserting comments (Shortcut Shift F2, see our Essential Excel Shortcuts List) in cells is critical for footnoting sources and adding clarity to data in a model.

    For example, a cell containing an assumption on revenue growth that came from an equity research report should include a comment with a reference to the research report. So how much commenting do you need? Always err on the side of over commenting. No managing director will ever complain that a model has too many comments. Additionally, if you’re on a conference call and someone asks how you came up with the number in cell AC1238 and you blank, you’ll regret not commenting.

    Sign convention

    The decision on whether to use positive or negative sign conventions must be made before the model is built. Models in practice are all over the place on this one. The modeler should choose from and clearly identify one of the following 3 approaches:

    Convention 1: All income positive, all expenses negative.

    • Advantage: logical, consistent, makes subtotal calculations less error-prone
    • Disadvantage: Doesn’t align with conventions used by public filings, % margin calculations appear negative

    Convention 2: All expenses positive; non-operating income negative.

    • Advantage: Consistent with public filings, % margin calculations appear positive
    • Disadvantage: Negative non-operating income is confusing, subtotal calculations are error-prone, proper labeling is critical

    Convention 3: All expenses positive except non-operating expenses.

    • Advantage: Avoids negative non-operating income presentation; margins evaluate to positive
    • Disadvantage: Presentation not internally consistent. Proper labeling is critical.

    Our recommendation is Convention 1. The reduced likelihood of error from easier subtotaling alone makes this our clear choice. In addition, one of the most common mistakes in modeling is forgetting to switch the sign from positive to negative or vice versa when linking data across financial statements. Convention 1, by virtue of being the most visibly transparent approach, makes it easier to track down sign-related mistakes.

    Formulas

    Avoid partial inputs (all models)

    Hard coded numbers (constants) should never be embedded into a cell reference. The danger here is that you’ll likely forget there is an assumption inside a formula. Inputs must be clearly separated from calculations (see below).

    One row, one calculation

    Most investment banking models, like the 3-statement model, rely on historical data to drive forecasts. Data should be presented from left to right. The right of the historical columns are the forecast columns. The formulas in the forecast columns should be consistent across the row.

    Use roll-forward (“BASE” or “cork-screw”) calculations

    Roll-forwards refers to a forecasting approach that connects the current period forecast to the prior period.

    This approach is very useful in adding transparency to how schedules are constructed. Maintaining strict adherence to the roll-forward approach improves a user’s ability to audit the model and reduces the likelihood of linking errors.

    Write good (and simple) formulas

    There is a temptation when working in Excel to create complicated formulas. While it may feel good to craft a super complex formula, the obvious disadvantage is that no one (including the author after being away from the model for a bit) will understand it. Because transparency should drive structure, complicated formulas should be avoided at all cost. A complicated formula can often be broken down into multiple cells and simplified. Remember, Microsoft doesn’t charge you extra for using more cells! So take advantage of that. Below are some common traps to avoid:

    1. Simplify IF statements and avoid nested IFs
    2. Consider using flags

    Simplify IF statements

    IF statements, while intuitive and well understood by most Excel users, can become long and difficult to audit. There are several excellent alternatives to IF that top-notch modelers frequently use. They include using Boolean logic along with a variety of reference functions, including MAX, MIN, AND, OR, VLOOKUP, HLOOKUP, OFFSET.

    Below is a real-world example of how an IF statement can be simplified. Cell F298 uses any surplus cash generated during the year to pay down the revolver, up until the revolver is fully paid down. However, if deficits are generated during the year, we want the revolver to grow. While an IF statement accomplishes this, a MIN function does it more elegantly:

    Revolver formula using IF statement

    Revolver formula using MIN

    The revolver formula using MIN as an alternative to IF also holds up better when additional complexity is required. Imagine that there’s a limit on annual revolver draw of $50,000. Look at how we have to modify both formulas to accommodate this:

    Revolver formula using IF statement

    Revolver formula using MIN

    While both formulas are challenging to audit, the formula using IF statements is more difficult to audit and is more vulnerable to getting completely out of hand with additional modifications. It uses nested (or embedded) IF statements, which our feeble human brains have a hard time with once there’s more than one or two.

    Fortunately, Excel has made this a bit easier in 2016 with the introduction of the IFS function, but our preference for relying on more elegant functions remains. We spend a lot of time in our Excel Crash Course going over the many ways “IF alternative” functions can be used to power-charge Excel.

    Reduce date-related formula complexity using flags

    Flags refer to a modeling technique most useful for modeling transitions across phases of a company, project or transaction over time without violating the “one row/one calculation” consistency rule. Imagine you’re building a model for a company that’s contemplating bankruptcy. Each phase of the restructuring process has its own distinct borrowing and operating characteristics.

    In our example below, the company’s revolver “freezes” once it goes into bankruptcy and a new type of borrowing (“DIP”) acts as the new revolver until the company emerges from bankruptcy. Additionally, a new “Exit” facility replaces the DIP. We insert 3 “flags” in rows 8-10 to output “TRUE/FALSE” based on the phase we’re in. This enables us to build very simple, consistent formulas for each revolver without having to embed IF statements into each calculation.

    In cell F16 the formula is =F13*F8. Whenever you apply an operator (like multiplication) on a TRUE, the TRUE is treated like a “1” while a FALSE is treated like a “0.” This means that the pre-bankruptcy revolver is the de facto revolver when the pre-bankruptcy flag evaluates to TRUE and becomes 0 once the flag evaluates to FALSE (starting in column I in our example below).

    The main benefit is that with the use of just an extra 3 rows, we’ve avoided having to insert any sort of conditional tests within the calculations. The same applies to the formulas in rows 20 and 204 — the flags have prevented a lot of extra code.

    Names and named ranges

    Another way many modelers reduce formula complexity is by using names and named ranges. We strongly caution against using names and named ranges. As you’re probably beginning to sense, there is always some kind of tradeoff with Excel. In the case of names, the tradeoff is that when you name a cell, you no longer know exactly where it is without going to the name manager. In addition, unless you are proactively deleting names (you aren’t), Excel will retain these names even when you delete the named cell. The result is that a file you’re using today to build a DCF contains dozens of phantom names from prior versions of the model, leading to warning messages and confusion.

    Don’t calculate on the balance sheet — link from supporting schedules.

    In investment banking, your financial models will frequently involve financial statements. Ideally, your calculations are done in schedules separate from the output you’re working towards. For example, it’s preferable that you don’t perform any calculations on the model’s balance sheet. Instead, balance sheet forecasts should be determined in separate schedules and linked into the balance sheet as illustrated below. This consistency helps in the transparency and auditing of a model.

    How to reference cells

    Never re-enter the same input in different places

    For example, if you’ve inputted a company name in the first worksheet of the model, reference that worksheet name — don’t re-type it into the other worksheets. The same goes for years and dates entered into a column header or a discount rate assumption used in a variety of different places in the model. A more subtle example of this is hard coding subtotals or EPS when you can calculate it. In other words, calculate whenever possible.

    Always link directly to a source cell as it is more difficult to audit “daisy chained” data

    The one major exception to this is when “straight-lining” base period assumptions. For this, go ahead and daisy chain. The reason is that straight-lining base period assumptions is an implicit assumption, which can change, thus making it possible for certain years in the forecast to ultimately end of with different assumptions than other years.

    Avoid formulas that contain references to multiple worksheets

    Compare the two images below. It is more difficult to audit the formula in the first image because you’ll need to bounce around to different worksheets to view the precedent cells. Whenever possible, bring the data from other worksheets into the active worksheet where the calculation is made.

    Link assumptions into standalone cells in the calculation and output sheets

    If you’re working with larger models and you have assumptions that need to be referenced from a separate worksheet, consider linking assumptions directly into the worksheet where you’re using them, and color code them as a distinct worksheet reference link. In other words, don’t have an input reference embedded into a calculation (i.e. =D13*input!C7). Instead, use a clean reference =input!C7 and a separate cell for the calculation. While this creates a redundant cell reference, it preserves the visual audit-ability of the model tab and reduces the likelihood of error.

    Avoid linking files

    Excel allows you to link to other Excel files, but others might not have access to the linked-to files, or these files may get inadvertently moved. Therefore, avoid linking to other files whenever possible. If linking to other files is a must, be vigilant about color coding all cell references to other files.

    Worksheets

    One long sheet beats many short sheets

    A long worksheet means a lot of scrolling and less visual compartmentalizing of sections. On the other hand, multiple worksheets significantly increases the likelihood of linking errors. There’s no hard and fast rule about this, but the general bias should be toward a longer sheet over multiple, shorter worksheets. The dangers of mis-linking across worksheets is quite real and hard to mitigate, while the issues of cumbersome scrolling and lack of compartmentalization associated with long worksheets can be drastically mitigated with Excel’s split screen functionality, clear headers and links from a cover sheet or table of contents.

    Don’t ‘hide’ rows — ‘group’ them (and do it sparingly)

    A model often has rows with data and calculations that you do not want to show when the model is printed or when you paste the data into a presentation. In this situation, it’s often tempting to hide rows and columns for a “cleaner” presentation of results. The danger is that when the model is passed around, it is very easy to miss (and potentially paste over) the hidden data.

    Keeping inputs (assumptions) together (for high-granularity models)

    Nearly every financial modeling expert recommends a standard that isolates all of the model’s hard-coded assumptions (things like revenue growth, WACC, operating margin, interest rates, etc…) in one clearly defined section of a model — typically on a dedicated tab called ‘inputs.’ These should never be commingled with the model’s calculations (i.e. balance sheet schedules, the financial statements) or outputs (i.e. credit and financial ratios, charts and summary tables). In other words, think of a model as comprised of three clearly identified and physically separated components:

    • Assumptions → Calculations → Output 

    Advantages:

    • Consistent, reliable architecture: Once a model is built, the user has only one place they need to go to change any assumptions. This creates a consistent distinction between areas in the model that the user works in vs. areas the computer works in.
    • Error mitigation: Storing all assumptions in one place makes it far less likely that you’ll forget to remove old assumptions from a prior analysis and inadvertently bring them into a new analysis.

    Yet despite these advantages, this practice has never been widely adopted in investment banking.

    One reason is simply poor practice. Some models would clearly benefit from an input/calculation/output separation, but are often built with no forethought given to structure. Imagine building a house without any pre-planning. Sure, you’ll avoid the pain of all that planning, but you’ll encounter unforeseen problems and end up redoing work or adding complexity by working around what’s already been done. This problem is rampant in investment banking models.

    Another reason is that many investment banking models are simply not granular enough to merit the additional audit trail and legwork. The analyses bankers perform are often broader than they are deep. For example, a pitch book might present a valuation using 4 different valuation models, but none of them will be overly granular. Common investment banking analyses like accretion dilution models, LBO models, operating models and DCF models usually don’t delve into detail beyond the limits of public filings and basic forecasting. In this case, moving back and forth from input to calculation to output tabs is unnecessarily cumbersome. As long as you’re diligent about color coding, placing assumptions on the same sheet and right below calculations is preferable in smaller models because your assumptions are visually right next to the output, making it easy to see what’s driving what.

    The other consideration is the number of a model’s users. The advantages of the “inputs together” approach grow with the number of a model’s intended users. When you have many users, your model will inevitably be used by people with a wide range of modeling proficiency. In this case, a consistent and reliable structure that prevents users from getting into the guts of the model will reduce error. In addition, it will also reduce the amount of time a user has to spend in the model — a user can simply locate the area for inputs, fill them in, and the model (in theory) will work. That said, despite attempts by IB teams to standardize models, many investment banking models are essentially “one-offs” that get materially modified for each new use. Aside from comps models which lend themselves to becoming templates, most models are used primarily by their original authors (usually an analyst and associate) who understand the model well.

    The bottom line on keeping inputs all together

    Unfortunately, there’s no established benchmark for when it makes sense to separate out assumptions. The ideal approach depends on the scope and goal of the model. For a simple 1-page discounted cash flow analysis not intended for frequent reuse, it is preferable to embed inputs throughout the page. However, for a large fully-integrated LBO model with many debt tranches to be used a group-wide template, the benefits of keeping all inputs together will outweigh the costs.

    No spacer columns between data

    Elevator jumps

    In long worksheets, dedicating the leftmost column for placing an “x” or another character at the start of schedules will make it easy to quickly navigate from section to section.

    Annual vs quarterly data (periodicity)

    Most investment banking models are either quarterly or annual. For example, a U.S. equity research earnings model will always be a quarterly model because one of its key purposes is to forecast upcoming earnings, which are reported by firms quarterly. Similarly, a restructuring model is usually a quarterly model (or even a monthly or weekly model) because a key purpose of this model is to understand the cash flow impact of operational and financing changes over the next 1-2 years. On the other hand, a DCF valuation is a long term analysis, with at least 4-5 years of explicit forecasts required. In this case, an annual model is appropriate.

    There are also models for which both quarterly and annual periods are useful. For example, a merger model usually needs a quarterly period because a key goal is to understand the impact of the acquisition on the acquirer’s financial statements over the next 2 years. However, attaching a DCF valuation to the combined merged companies may also be desired. In this case, a possible solution is to roll up the quarters into an annual model and extend those annual forecasts further out.

    When determining a model’s periodicity, keep in mind the following:

    1. The model must be set up with the smallest unit of time desired, with longer time periods being aggregated (rolled up) from those shorter time periods. If you’re building an integrated financial statement model in which you want to see quarterly and annual data, forecast the quarterly data first.
    2. Keep the quarterly and annual data in separate worksheets. It is easier to audit what’s going on when periods aren’t commingled. Additionally, commingling quarterly and annual data in one worksheet will either A) force you to violate the one row/one formula consistency best practice or B) you will have to jump through some crazy hoops to maintain the consistency.

    Circularity refers to a cell referring to itself (directly or indirectly). Usually, this is an unintentional mistake. In the simple example below, the user has accidentally included the sum total (D5) in the sum formula. Notice how Excel becomes confused:

    But sometimes a circularity is intentional. For example, if a model calculates a company’s interest expense based on a cell that calculates the company’s revolving debt balance, but that revolving debt balance is itself determined by (among other things) the company’s expenses (including interest expense), then we have a circularity:

    The logic of such a calculation is sound: A company’s borrowing needs should take into account the interest expense. As such, many investment banking models contain intentional circularities like these.

    Since unintentional circularity is a mistake to avoid, the usage of intentional circularity in financial models is controversial. The problem with intentional circularity is that a special setting must be selected within ‘Excel Options’ to prevent Excel from misbehaving when a circularity exists:

    Even with these settings selected, Excel can become unstable when handling circularity and often leads to a model “blowing up” (i.e. the model short-circuits and populates the spreadsheet with errors), requiring manual intervention to zero out the cells containing the source of circularity:

    While the underlying logic for wanting to incorporate a circularity into a model may be valid, circularity problems can lead to minutes, if not hours, of wasted auditing time trying to locate the source(s) of circularity to zero them out. There are several things modelers can do to better cope with circularity, most notably the creation of a simple circuit breaker, which creates a central place in the model that “resets” any cell containing a circularity or wrapping an error-trap formula (IFERROR) around the formula that is the source of the circularity.

    Circuit breaker or an IFERROR error-trap  

    When building an intentional circularity, you MUST build a circuit breaker and clearly identify all the circularities in your model. In our simple example, we placed a circuit breaker in D17 and altered the formula in D8 so the circularity is zeroed out when the user switches the breaker to “ON”:

    Approach 1: Adding a circuit breaker toggle 

    An alternative approach is to simply wrap an IFERROR function around the source of the circuilarity.  When the model short circuits, the IFERROR function evaluates to the FALSE condition and populates the model with 0s automatically. The primary downside to this approach is that they make finding unintentional circularities harder. That’s because you can never explicitly turn the breaker on or off – the IFERROR does it automatically. That said, as long as all circs are handled with an IFERROR function, the model will never blow up.

    Approach 2: Adding an error trap using the IFERROR function

    Bottom line: To circ or not to circ?

    Despite the circuit breaker and error trap solutions, many believe it is preferable to simply outlaw all circularity from financial models. For example, the way to altogether avoid the intentional circularity in the example above is to calculate interest expense using beginning debt balance. For quarterly and monthly models with minor debt fluctuations, this is desirable, but for an annual model with a large forecasted change in debt, the “fix” can lead to a materially different result. Therefore, we do not believe in a blanket “ban.” Instead, we provide the following simple guideline:

    A circularity is only OK if all the following conditions are met.

    1. It is intentional: At risk of stating the obvious, you must understand exactly why, where, and how the circularity exists.  The example described above is the most common source of circularity in financial models.
    2. You have “enable iterative calculation” selected in your Excel settings: This tells Excel the circularity is intentional and ensures Excel doesn’t throw up an error and populate the entire model with random zeros everywhere.
    3. You have a circuit breaker or error trap formula: A circuit breaker or error trap formula ensures that if the file gets unstable and #DIV/0!s start populating the model, there is an easy and clear way to fix it.
    4. The model will not be shared with Excel novices: Circularities, even with a circuit breaker, can create confusion for Excel users not familiar with it.  If the model you are building will be shared with clients (or a managing director) that like to get into the model but are generally unfamiliar with Excel, avoid the circularity and save yourself the headache.

    Don’t use macros

    Keep macros to an absolute minimum. Very few people know how macros work, and some users cannot open files that use macros. Every additional macro is a step closer to making your model a “black box.” In investment banking, this is never a good thing. The only macros regularly tolerated in banking models are print macros.

    Error checking

    Excel is an amazing tool. Unlike software specifically designed to perform a particular set of tasks (i.e. real estate investment software, bookkeeping software), Excel is a blank canvas, which makes it easy to perform extremely complicated analyses and quickly develop invaluable tools to help in financial decision making. The downside here is that Excel analyses are only as good as the model builder (i.e. “Garbage in = garbage”). Model error is absolutely rampant and has serious consequences. Let’s break up the most common modeling errors:

    1. Bad assumptions: If your assumptions are faulty, the model’s output will be incorrect regardless of how well it is structured.
    2. Bad structure: Even if your model’s assumptions are great, mistakes in calculations and structure will lead to incorrect conclusions.

    The key to mitigating #1 is to present results with clearly defined ranges of assumptions (scenarios and sensitivities) and make the assumptions clearly defined and transparent. Breaking models out into inputs→calculation→output helps others quickly identify and challenge your assumptions (Addressed in detail in the “Presentation” section above). The far more pernicious modeling error is #2 because it’s much more difficult to find. As you might imagine, the problem grows exponentially as the model’s granularity increases. This is why building error checks into your model is a critical part of model building.

    Build in error checks

    The most common error check in a financial model is the balance check — a formula testing that assets = liabilities + equity:

    Anyone who has built an integrated financial statement model knows it is quite easy to make a simple mistake that prevents the model from balancing. The balance check clearly identifies to the user that a mistake has been made and further investigation is required. However, there are many other areas of models that are prone to error and thus could merit error checks. While every model will need its own checks, some of the more common ones include:

    • Ensuring sources of funds = uses of funds
    • Ensuring the quarterly results add up to annual results
    • Total forecast depreciation expense does not exceed PP&E
    • Debt pay-down does not exceed outstanding principal

    Favor direct calculations over “plugs”

    Below we show two common ways that users set up a sources & uses of funds table in financial models.  In both approaches, the user accidentally references intangible assets. In approach 1, the incorrect data is linked into D37. The model notices that sources do not equal uses and throws an error message in D41. The second (and equally common) approach structurally sets D52 equal to D47 and uses D49 as a plug to ensure sources and uses always equal.  Which approach do you think is preferable? If you guessed the first approach, you are correct. The problem the second (“plug”) approach is that because of the mis-linking in D50, the model incorrectly calculates the amount of secured loans required for the transaction, and no error is identified.

    Whenever a direct calculation is possible, use it, along with an error check (i.e. “do sources equal uses?”) instead of building plugs.

    Aggregate error checks into one area

    Place error checks close to where the relevant calculation is taking place, but aggregate all error checks into a central easy to see “error dashboard” that clearly show any errors in the model.

    Error trapping

    Models that require a lot of flexibility (templates) often contain areas that a user may not need now, but will need down the road. This includes extra line items, extra functionality, etc. This creates room for error because Excel is dealing with blank values. Formulas like IFERROR (and ISERROR), ISNUMBER, ISTEXT, ISBLANK are all useful functions for trapping errors, especially in templates.

    Present-ability

    Cover Page and TOC

    When a model is designed for use by more than just the model builder, include a cover page. Cover page should include:

    1. Company and/or project name
    2. Description of the model
    3. Modeler and team contact information

    Include a table of contents when the model is sufficiently large to merit it (a good rule of thumb is more than 5 worksheets).

    Worksheet design

    Label worksheets by the nature of the analysis (i.e. DCF, LBO, FinStatements, etc…). Tabs should flow logically from left to right. When following the inputs→calculations→output approach, color the worksheet tabs based on this division:

    1. Include the company name on top left of every sheet
    2. Include the sheet purpose, scenario selected (when relevant), scale and currency prominently below the company name on each sheet
    3. Page setup for printing: When a sheet is too long to fit in one page, the top rows containing company name, purpose of the page, currency and scale should be displayed on top of each page (select “rows to repeat at top” (Page Layout>Page Setup>Sheet)
    4. Include file path, page number and date in footer

    Scenarios and sensitivities

    The purpose of building a model is to provide actionable insight that wasn’t otherwise readily visible. Financial models shed light on variety of critical business decisions:

    • How does an acquisition change the financial statements of an acquirer (accretion/dilution)?
    • What is a company’s intrinsic value?
    • How much should an investor contribute to a project given specified return requirements and risk tolerances?

    Virtually all investment banking models rely on forecasting and assumptions to arrive at the outputs presented to clients. Because assumptions are by definition uncertain, presenting the financial model’s output in ranges and based on a variety of different scenarios and sensitivities is critical. In this post about scenario analysis and this post about using data tables for sensitivity analysis, we address the two most effective ways to present financial outputs in financial models.

    Conclusion and further reading

    We wrote this guide to provide a framework applicable to investment banking models. For those that want to dive deeper into building specific investment banking models, consider enrolling in our flagship financial modeling program. For those that want to get into the weeds of modeling theory, I recommend the following texts:

    Master financial modeling!Used at top investment banks, Wall Street Prep's Premium Package will you set you apart in interviews and on the job.

    Learn More

    0 thoughts on “Cell References In A Formula Are Called Assumptions And Critical Thinking

    Leave a Reply

    Your email address will not be published. Required fields are marked *