Mastering Financial Statement Modelling for Strategic Forecasting

Ian Wong, Director, Financial Planning and Analysis (FP&A), Johnson Electric

Ian Wong, Director, Financial Planning and Analysis (FP&A), Johnson Electric

Ian Wong is currently the Director of Financial Planning and Analysis at Johnson Electric. He has extensive experience in financial statement modeling, having spent over a decade working in the Financial Planning and Analysis area.

Financial statement modelling is a fundamental skill in the Financial Planning & Analysis area. It entails projecting the expected future view of the company’s financial position in terms of profits, cash flow and financial health (e.g. liquidity, ability to generate cash). The crux of financial statement modelling involves using historical data, then making informed or intelligent assumptions on how future financial statements are expected to pan out. The future view is usually the budget year and the projection can be done for several years (e.g. 3- 5 years) into the future

Forecasting Future Financials Starts with Normalizing Income

The starting point is usually the latest reporting period for the Income Statement which is used as the base year on which to project the future. This Income Statement from the latest reporting period should be adjusted to exclude non-recurring or exceptional items (e.g. significant one-off impairment charges) which are not expected to repeat in future years. This step is known as normalizing and its objective is to remove figures that could distort future year projections.

The first item to be projected is usually revenue where there are multiple options available for use in modelling. For instance, an uncomplicated way is to use a percentage change to calculate the projected income. This could be supplemented by reference being made to industry data (e.g. for companies operating in the automotive industry, reference could be made to the number of cars expected to be produced in the next few years) or macroeconomic data (e.g. GDP growth, inflation rates). Leveraging Artificial Intelligence (AI) powered search engines is a comprehensive source for gathering such data.

Once the sales projection is done, the associated cost of goods sold (COGS) can be worked out based on which products / services are expected to be sold (e.g. for manufactured products, this would include items such as the Bill of Materials, Direct Labor, Production Overhead whereas for services, this would include salaries of employees who directly perform the service).

“The crux of financial statement modelling involves using historical data, then making informed or intelligent assumptions on how the future financial statements are expected to pan out”

Overhead costs such as salaries for administrative employees, office rent, depreciation, utilities, legal and professional expenses are typically stable year on year, barring no major changes in the business (e.g. disposal / acquisition) and can be modelled out using inflation assumptions. Income tax expense can be estimated using the effective tax rate with consideration for changes in tax laws or utilization of tax losses / deferred tax.

Interest expenses can be estimated using the debt levels and the respective borrowing rates of each loan (fixed or floating).

Projecting Balance Sheets Requires Detail and Discipline

The key components in the projected balance sheet are: Assets and liabilities related to working capital, such as accounts receivables, account payables and inventory. The ending balances of each of these accounts can be respectively reverse engineered using days sales outstanding (DSO) days purchases outstanding (DPO) and days inventory on hand (DIO). E.g. in the case of calculating the accounts payable balance, the assumed DPO is divided by 365 (days in year) then multiplied by projected COGS.

Fixed assets are projected by starting with the opening balance, adding capital expenditures, and subtracting depreciation expense.

Retained earnings are projected based on projected net income and dividends. Debt (and Cash) would be linked to projected drawdown and repayments depending on the debt repayment schedule.

For each projected adjustment made, the balance sheet should be checked progressively to ensure it balances instead of leaving the check till the end of adjustment input to avoid what could be a lengthy and complicated checking process.

Projecting Cash Flow Starts with Sound Inputs

The cash flow statement is usually a product of the income statement and balance sheet projections. It comprises of three main sections:

• Cashflow from operating activities is derived from projected Earnings before interest, tax, depreciation and amortization (EBITDA) and working capital changes. EBITDA is mainly driven by operating profit. For tax payments, historical trends can be used to work out a representative tax payout ratio catering for any one-off impacts.

• Cashflow from investing activities could include capital expenditures, purchase and sales of financial assets and proceeds from sales of property, plant and equipment.

• Cashflow from financing activities are related to items such as debt repayments and drawdowns which can be referenced to the aforementioned debt repayment schedule as well as dividends paid.

The above are the broad guiding principles when modelling and there are numerous excel spreadsheets that are available and commonly used, integrating the income statement, balance sheet and cash flow statement into one full set of financial statements.

The views, thoughts, and opinions expressed in the text belong solely to the author, and not necessarily to the author's employer, organization, committee or other group or individual.