How to Build Powerful Financial Models

    Modeling is a Discipline

    A financial model is a critical decision-making tool that allows users to make accurate and informed financial decisions about a company. A financial model needs to serve as a powerful communication tool to clearly and effectively tell the story of a company to executives and stakeholders.

    A builder of financial models requires skills in accounting, finance, spreadsheets and overall business knowledge in order to effectively forecast a company's financial statements into the future.

    With excellent modeling skills, a professional can:

    • Read and properly understand a company’s historical financial statements.
    • Ask strategic questions to help frame thinking about the future.
    • Perform effective research in order to determine optimal assumptions.
    • Challenge conventional thinking and adopt alternate approaches.
    • Coordinate the efforts of all contributing professionals.

    A strong financial modeler will be able to:

    • Demonstrate exceptional Excel skills and keyboard proficiency.
    • Design and build models with precision, to be used for optimal communication.
    • Incorporate scenario and sensitivity analysis to manage risk.

    Mastering the discipline of financial modeling facilitates the communication of outputs and recommendations with poise and confidence.

    The Key Attributes of a Model

    To achieve the goals set out above, a model needs to be developed with the following attributes:

    Flexible: The model contains scenarios with multiple cases (i.e. base case, best case, worst case) for the most critical variables.

    Intuitive: The reader can easily follow and understand the model based on the flow of the pages.

    Transparent: Every formula can be understood whether the reader clicks into the cell, or looks at a printout on paper.

    Dynamic: Any changes to the assumptions should properly flow through the model.

    Tells a story: The model is a powerful communication tool that allows for optimal decision making.

    Transferable: When a model is well designed and well built, colleagues and clients will be able to use the file as if they built it themselves.

    When a model achieves the criteria above, it creates tremendous credibility for the builder and inspired confidence with the reader.

    A well-designed model needs to work in two ways:

    • Electronically: A model must be simple to review / audit / manipulate in a spreadsheet to ensure it can be used as a tool in the decision-making process.
    • In printed format: Many senior executives and other decision makers prefer to review financial analysis on paper or as PDF (rather than reviewing a spreadsheet).

    Model Planning

    It is critical to properly plan and design a model before it can be built.

    Whenever a model becomes filled with errors, it is often because the modeler did not properly devise a model plan. A model built without forethought is often chaotic and requires many additions on a weak foundation.

    A model builder needs to first understand how a business operates, which requires the following:

    Review historical financial statements: Review management discussion, analysis and financial statement notes.

    Learn about the company's industry: Reading industry journals and equity research reports.

    Decide on key drivers: Key drivers that are critical success factors for the industry / company.

    Understand the purpose of the model: Is the model used for valuation, credit assessment, project evaluation, etc?

    Identify required schedules and components: Plan for all specific calculations such as revenues, costs, working capital, etc.

    Identify the required schedules and components to be included in the model.

    • Schedules should be planned for all specific calculations needed in the model, such as revenues, costs, depreciation, working capital, etc.
    • Take some time to map out these components and model flow, the same way you would when creating and outline for a presentation. Create a skeleton of your model.

    As part of the planning process, the model builder should also be able to answer the following questions:

    • Can a reader of the model understand the operations of the company?
    • Have the assumptions been challenged and vetted?
    • Which assumptions are key drivers to be tested as scenarios?
    • Is there enough historical detail to validate the required amount of forecast detail?
    • What is the important output that the users want to know?
    • What metric is used to measure results?
    • What's the appropriate periodicity for the model (e.g monthly, quarterly, annual)?
    • How many time periods should be included in the forecast? Why?
    • What is the most logical order to present the data?

    Download As PDF