Financial Statement Modelling 1

Ever wondered how companies forecast their earnings or decide on expansion plans? Financial statement modelling is at the heart of these decisions. 

Financial statement modelling represents one of the most essential competencies in the field of finance, enabling analysts, investors, and business leaders to make informed decisions grounded in projected financial performance. Whether one seeks to evaluate the viability of a business investment, anticipate a company’s growth, or execute critical strategic decisions, financial modelling offers the data-driven insights necessary for such analyses.

In this weekly series, we will analyse, in a step-by-step manner, the financial modelling processes of companies that capture our interest. We will delve into the requisite skills, methodologies, and best practices that will provide a comprehensive understanding of the complexities and nuances of a company.

Today, we will introduce the concept of financial statement modelling, discuss its significance, outline our financial modelling framework, and ultimately conclude with a rough schedule for our forthcoming blog posts.

What is Financial Statement Modelling?

Financial statement modelling is a structured process used to create a detailed forecast of a company’s financial performance. The typical duration encompassed by such models ranges from three to five years, although certain models extend their projections even further. These models are constructed by integrating the three primary financial statements—the income statement, balance sheet, and statement of cash flows—to effectively forecast future revenues, expenses, profits, asset valuations, liabilities, and cash flows.

Financial modelling models the complexities of a company’s reality and breaks them down into a generalisation of that reality. When developing a model, it is imperative to ensure that it achieves an optimal equilibrium between being excessively simplistic, which results in an underfitted model, and being overly complex, which leads to an overfitted model. Underfitted models aren’t particularly useful, and overfitted models are difficult to maintain and exhibit a suboptimal reward-to-work ratio.

Why is Financial Modelling Important?

Financial modelling is not just for accountants or finance professionals; rather, it is a skill that can empower managers, entrepreneurs, and investors with the necessary tools to make informed decisions regarding the future. The following outlines its significant value:

• Decision-Making: Modelling helps assess the potential outcomes of strategic decisions, such as launching a new product, expanding into a new market, or acquiring another company.
• Budgeting and Forecasting: Financial models allow businesses to plan ahead, identifying when cash might be tight or when they might need external financing.
• Valuation: Investors use financial models to determine whether a stock is over- or undervalued. This helps guide buy/sell decisions.

Financial Modelling Framework

1^st Step

The initial stage of the framework involves performing a comprehensive analysis of the company and its environment. A significant portion of our efforts will be dedicated to this stage, as it is anticipated to represent approximately 60 to 70 percent of our total time allocation.

To construct an effective financial statement model for a company, it is important to attain a comprehensive understanding of the company’s operations, management practices, strategic initiatives, external environment, and historical performance. Therefore, we shall commence with an examination of the selected company and its environment, encompassing its industry, primary products, strategic positioning, management structure, competitors, suppliers, and customers. 

We will also need to collect the company’s historical financial statements, typically covering a period of at least three to five years, if feasible. This collection provides a foundational basis for understanding trends and formulating realistic forecasts. Specifically, we will seek historical data pertaining to revenue, cost of goods sold, operating expenses, working capital, as well as debt and equity balances.

Utilising this information, we shall identify the key revenue and cost drivers and evaluate the probable impacts of relevant trends, including economic conditions and technological advancements. Our understanding of the business’s fundamental drivers and our assessment of future events will provide the basis for forecast model inputs.

2^nd Step

Upon achieving a comprehensive understanding of the company and the environment in which it operates, we are going to try and break down the complexities and intricacies of the real world into a more simplified representation by developing a model. This model will serve as our algorithm to facilitate the forecasting of the company’s financial health.

At this stage, we shall transition to utilising Microsoft Excel to consolidate all the information we have gathered into a financial statement model. Our objective is to construct a model that strikes a balance between excessive simplicity and unnecessary complexity, thereby ensuring that the model will generalise effectively and remain reusable.  Finding this balance will require planning.  

We’ll also take time to think carefully through the design of our financial model. A section of our spreadsheet will be designated to accommodate our assumptions and drivers. Additionally, we will organize historical data for ease of reference and trend analysis, as well as incorporate columns that will facilitate forecasts spanning three to five years into the future, thereby establishing a forecast timeline.

Furthermore, it’s also useful to incorporate colour coding (for instance, blue for inputs and black for calculations) to enhance readability and clarity.

Subsequently, we shall proceed to project a model of our financial statements, commencing with the income statement, followed by the statement of cash flows, and concluding with the balance sheet. We will use cash/cash equivalents as our balance sheet plug to ensure that our balance sheet is indeed balanced.

Before proceeding to the next step, we will complete our schedules (e.g., revenue schedule, cost schedule, working capital schedule, etc.) that will provide relevant data to feed back into our financial statement model.

Once we’ve completed our forecasts of the three financial statements, we will proceed to the next part of this modelling process. 

3^rd Step

The third and final step of our framework involves conducting various tests to evaluate the performance of our model in response to changes in assumptions.

We shall commence with a sensitivity analysis, which involves evaluating the effects of various assumptions on our financial projections. We may investigate alterations in revenue growth, cost assumptions, or financing terms to ascertain their impact on profitability, cash flow, or valuation.

Furthermore, we shall conduct a scenario analysis, which, akin to stress analysis, takes into account specific business cases (for instance, best-case, base-case, and worst-case scenarios). This approach aids in comprehending potential risks and preparing for various business conditions.

In conclusion, we shall validate and conduct a stress test on our model. This process will encompass a thorough review of all underlying assumptions to ensure their realism and accuracy, in addition to cross-referencing with industry benchmarks or management guidance whenever feasible. Conducting a stress test by varying key inputs will enable us to assess the model’s robustness and identify any potential issues that may arise.

Conclusion

Financial statement modelling represents a critical competency that amalgamates technical proficiency with strategic insight. As previously noted, it serves as an indispensable tool for forecasting, valuation, investment analysis, and strategic planning, demonstrating considerable value to finance professionals, investors, and business leaders. In this series, we will undertake a thorough exploration of each component of a financial model, diligently learning how to construct projections, interconnect the financial statements, and analyse the results.

To make this undertaking more tangible, we’ll kick things off by constructing a comprehensive financial statement model for Walmart. Renowned for its extensive global presence, remarkable revenue growth, and intricate operations, Walmart serves as an exemplary real-world case study to illustrate the modelling techniques and financial insights that facilitate successful decision-making.

Stay tuned! Next week, we will commence with a quick synopsis on where we wil collect our financial information to aid us in creating or financial model. Let us embark on this engaging journey into financial modelling!