S L A T N E A D N U F G N I L E D O M L A I C N Financial Modelling A N .

Financial Statements Modelling 1.2.1. Links Between The Financial Statements One of the most common causes of confusion when modelling financial statements is misunderstanding the links between the three financial statements. When using a Modular Spreadsheet Development approach, only two links are required between the three Financial Statements Modules, as follows: Net Profit After Tax (NPAT) from the Income Statement links into the Equity section of the Balance Sheet, adding to Retained Profits; and The Net Change in Cash Held from the Cash Flow Statement links into the Current Assets section of the Balance Sheet, adding to Cash. All other links into each of the three Financial Statements Modules should be sourced from their applicable precedent modules. These two links between the three financial statements are illustrated in the financial statement impacts schematic shown below, which uses the Revenue Module direct financial statement impacts as a simple example: Links Between The Financial Statements – Revenue Module Example Income Statement Revenue COGS Gross Margin Operating Expenditure EBITDA Depn. & Amort. EBIT Interest Expense NPBT Tax Expense NPAT Balance Sheet 1,000 1,000 1,000 1,000 1,000 1,000 Cash Flow Statement Opening Cash Change in Cash Held Cash Current Assets Non-Current Assets Total Assets Current Liablities Non-Current Liabilities Total Liabilties Ordinary Equity Opening Retained Profits Net Profit During Period Retained Profits Total Equity 1,000 1,000 1,000 1,000 1,000 1,000 1,000 Net Assets 1,000 Cash Receipts Cash Payments Interest Paid Tax Paid Operating Cash Flow s 1,000 1,000 Capital Expenditure Investing Cash Flow s - Debt Draw dow ns Debt Repayments Financing Cash Flow s - Change in Cash Held 1,000 Note from this simple example that the change in Total Equity resulting from the change in Retained Profits due to NPAT is offset by the change in Total Assets resulting from the change in Cash due to the Change in Cash Held. If this relationship is maintained, and each set of links into the financial statements from each financial statements precedent module is correct and logical, the Balance Sheet should never unbalance – i.e. Total Equity will always equal Net Assets. Importantly, these principles remain applicable regardless of the number of precedent modules linked into the Financial Statements Modules, and regardless of the customisation of the financial statements which may be undertaken. Shown below is the three linked Financial Statements Modules including the impacts of links in from Revenue, Cost of Goods Sold, Operating Expenditure, Book Assets, Debt, Ordinary Equity and Taxation Modules: www.bestpracticemodelling.com Page 6 of 40

Financial Statements Modelling Links Between The Financial Statements – Multiple Precedent Modules Example Income Statement Revenue COGS Gross Margin Operating Expenditure EBITDA Depn. & Amort. EBIT Interest Expense NPBT Tax Expense NPAT Balance Sheet 1,000 (250) 750 (375) 375 (175) 200 (98) 103 (31) 72 Cash Flow Statement Opening Cash Change in Cash Held Cash Current Assets Non-Current Assets Total Assets Current Liablities Non-Current Liabilities Total Liabilties Ordinary Equity Opening Retained Profits Net Profit During Period Retained Profits Total Equity 275 172 447 447 2,500 2,947 45 1,500 1,545 750 580 72 652 1,402 Net Assets 1,402 Cash Receipts Cash Payments Interest Paid Tax Paid Operating Cash Flow s 1,000 (625) (98) (31) 247 Capital Expenditure Investing Cash Flow s (175) (175) Debt Draw dow ns Debt Repayments Financing Cash Flow s 200 (100) 100 Change in Cash Held 172 From these examples, it can be seen that the challenges surrounding the development of live, linked financial statements do not result from the relationships between the three financial statements. Instead, the challenges result from the need to understand the financial statement impacts of each potential financial statements precedent module, and the ways in which the Financial Statements Modules can be customised without affecting their integrity and correctness. These concepts are discussed in detail in the following sections. 1.2.2. Financial Statement Impacts As discussed in Section 1.2.1, understanding the ways in which different types of information (often from different precedent modules) impact the three financial statements is the key to the development of live, linked financial statements. Understanding these concepts will ensure that each time a precedent module is linked into any of the Financial Statements Modules, or the Financial Statements Modules are customised to reflect certain information, the integrity and correctness of the financial statements is maintained. Generally, there are five different ways in which information may correctly impact the financial statements, as follows: Impact Type Description Example Income Statement & Balance Sheet A revenue or expense is reported on the Income Statement, resulting in the creation of an asset or liability on the Balance Sheet. No impact on cash. An Income Statement & Balance Sheet impact is often unwound by a Balance Sheet & Cash Flow Statement impact – e.g. when the asset or liability is removed from the Balance Sheet. www.bestpracticemodelling.com Employee entitlements are recorded as an operating expenditure on the Income Statement, and result in a Provision for Employee Entitlements (Liability) on the Balance Sheet. Page 7 of 40

Financial Statements Modelling Impact Type Description Example Income Statement & Cash Flow Statement A revenue or expense is reported on the Income Statement and is received or paid in cash in the same accounting period (and therefore recorded as a change in cash on the Cash Flow Statement). No direct impact on assets, liabilities or equity. Revenue earned during a period is received as cash during the period, being reported on both the Income Statement and the Cash Flow Statement. A change in cash results in the movement in an asset, liability or equity account on the Balance Sheet. No impact on earnings. The cash receipt of revenue earned in a prior period results in a corresponding reduction in Accounts Receivable (Asset) on the Balance Sheet. A movement in an asset, liability or equity account on the Balance Sheet is offset by a counter-acting movement in another asset, liability or equity account on the Balance Sheet. No impacts on earnings or cash. An asset is re-valued, resulting in an offsetting movement in an Asset Revaluation Reserve (Equity) account. A revenue or expense is reported on the Income Statement, a change in cash is reported on the Cash Flow Statement and an asset, liability or equity account is created on the Balance Sheet. Directly impacts earnings, cash and Balance Sheet accounts. Capital Expenditure (on the Cash Flow Statement) is used to acquire an asset (on the Balance Sheet), which is then depreciated (on the Income Statement). Balance Sheet & Cash Flow Statement Balance Sheet Only All Financial Statements It is important to understand each of these types of financial statement impacts because each financial statement precedent module will usually impact the financial statements in one of these ways. Additionally, any customisation of the Financial Statements modules should always be undertaken in accordance with one of these impact types, to ensure that the financial statements remain logical and correct. Each of these types of financial statement impacts will be discussed in turn. Income Statement & Balance Sheet Impact When information impacts the financial statements via the Income Statement and Balance Sheet, a revenue or expense is reported on the Income Statement, resulting in the creation of an asset or liability on the Balance Sheet. This type of impact on the financial statements does not impact cash flow, because it does not result in a change in cash on the Cash Flow Statement. The following financial statement impacts schematic shows how information might impact the financial statements via the Income Statement and Balance Sheet. In this example, employee entitlements of 100m have been reported on the Income Statement, but have not been paid out to employees during the accounting period. Hence, a non-current asset provision called „Provision for Employee Entitlements‟ has been created on the Balance Sheet: www.bestpracticemodelling.com Page 8 of 40

Financial Statements Modelling Income Statement & Balance Sheet Impact Example Income Statement Revenue COGS Gross Margin Employee Entitlements Operating Expenditure EBITDA Depn. & Amort. EBIT Interest Expense NPBT Tax Expense NPAT Balance Sheet (100) (100) (100) (100) (100) (100) Cash Flow Statement Opening Cash Change in Cash Held Cash Current Assets Non-Current Assets Total Assets Current Liablities Provision for Empl. E'ments Non-Current Liabilities Total Liabilties Ordinary Equity Opening Retained Profits Net Profit During Period Retained Profits Total Equity 100 100 100 (100) (100) (100) Net Assets (100) Cash Receipts Cash Payments Interest Paid Tax Paid Operating Cash Flow s - Investing Cash Flow s - Financing Cash Flow s - Change in Cash Held - Note from this example that the Employee Entitlements expense on the Income Statement does not affect cash on the Cash Flow Statement, because none of these entitlements were actually paid as cash to employees during the period. However, the entity underlying these financial statements has incurred a legal liability to pay out these entitlements to employees at some stage in the future, which is recorded on the Balance Sheet as a liability provision called „Provision for Employee Entitlements‟. Importantly, when these entitlements are actually paid in cash to employees, the Provision for Employee Entitlements liability will be reduced accordingly, and a decrease in operating cash flows will be reported on the Cash Flow Statement – i.e. the unwinding of the Income Statement and Balance Sheet impact in this example will take place via a Balance Sheet and Cash Flow Statement impact. See below for a discussion of the Balance Sheet and Cash Flow Statement financial statements impact type. Income Statement & Cash Flow Statement Impact When information impacts the financial statements via the Income Statement and Cash Flow Statement, a revenue or expense is reported on the Income Statement and is received or paid in cash in the same accounting period (and therefore recorded as a change in cash on the Cash Flow Statement). This type of impact on the financial statements does not directly impact assets, liabilities or equity on the Balance Sheet – i.e. all Balance Sheet impacts take place indirectly via Net Profit After Tax (NPAT) from the Income Statement and the change in cash on the Cash Flow Statement. The following financial statement impacts schematic shows how information might impact the financial statements via the Income Statement and Cash Flow Statement. In this example, revenue of 1,000m earned during a period is received in full in cash during the period. Hence, cash receipts equal to the revenue earned are recorded as operating cash flows on the Cash Flow Statement: www.bestpracticemodelling.com Page 9 of 40

Financial Statements Modelling Income Statement & Cash Flow Statement Impact Example Income Statement Revenue COGS Gross Margin Operating Expenditure EBITDA Depn. & Amort. EBIT Interest Expense NPBT Tax Expense NPAT Balance Sheet 1,000 1,000 1,000 1,000 1,000 1,000 Cash Flow Statement Opening Cash Change in Cash Held Cash Current Assets Non-Current Assets Total Assets Current Liablities Non-Current Liabilities Total Liabilties Ordinary Equity Opening Retained Profits Net Profit During Period Retained Profits Total Equity 1,000 1,000 1,000 1,000 1,000 1,000 1,000 Net Assets 1,000 Revenue Cash Receipts Cash Payments Interest Paid Tax Paid Operating Cash Flow s 1,000 1,000 1,000 Investing Cash Flow s - Financing Cash Flow s - Change in Cash Held 1,000 Note from this example that the revenue which is received as cash does not directly affect assets, liabilities or equity on the Balance Sheet – i.e. the 1,000m increase in Retained Profits (from NPAT on the Income Statement) is offset by a 1,000m increase in cash on the Balance Sheet. In reality, not all revenues and expenses reported on the Income Statement are received or paid as cash in the accounting period in which there are reported. This results in the need to record working capital assets, which reflect revenues to be received and/or expenses to be paid using cash in future accounting periods. In such cases, working capital assets and liabilities will be recorded as a result of an Income Statement and Balance Sheet financial statements impact (discussed above) and will be reduced when cash is received or paid, which will be reflected by a Balance Sheet and Cash Flow Statement financial statements impact (discussed below). Balance Sheet & Cash Flow Statement Impact When information impacts the financial statements via the Balance Sheet and Cash Flow Statement, a cash inflow or outflow causes a movement in an asset, liability or equity account on the Balance Sheet. This type of impact on the financial statements does not impact earnings on the Income Statement. The following financial statement impacts schematic shows how information might impact the financial statements via the Balance Sheet and Cash Flow Statement. In this example, cash of 100m has been received as a result of revenue earned in a period accounting period. When this revenue was earned and not received in the prior accounting period, an Operating Receivable asset will have been created, which in this example is reduced upon the receipt of the corresponding cash receipts. The schematic diagram below shows the Balance Sheet and Cash Flow Statement impacts of receiving this cash: www.bestpracticemodelling.com Page 10 of 40

Financial Statements Modelling Balance Sheet & Cash Flow Statement Impact Example Income Statement Revenue COGS Gross Margin Operating Expenditure EBITDA Depn. & Amort. EBIT Interest Expense NPBT Tax Expense NPAT Balance Sheet - Opening Cash Change in Cash Held Cash Operating Receivables Current Assets Non-Current Assets Total Assets Current Liablities Non-Current Liabilities Total Liabilties Ordinary Equity Opening Retained Profits Net Profit During Period Retained Profits Total Equity Net Assets Cash Flow Statement 100 100 (100) - Cash Receipts Cash Payments Dec. in Operating Receivables Interest Paid Tax Paid Operating Cash Flow s 100 100 Investing Cash Flow s - Financing Cash Flow s - Change in Cash Held 100 - Note from this example that the cash received is not reported on the Income Statement and therefore does no impact earnings during the accounting period. This is because the revenue with which the cash receipts are associated has already been reported on the Income Statement in the period in which it was earned, and is therefore already included in the Retained Profits of the entity. Hence, the receipt of the cash associated with this prior period revenue is recorded as an operating cash inflow, and is offset by a reduction in the associated Operating Receivables asset that was created in the period in which the revenue was earned. The reduction in a working capital asset or liability in this way would therefore usually take place in a period subsequent to a period in which revenues or expenses were reported on the Income Statement but not received or paid as cash, resulting in the creation of an associated working capital asset or liability. Hence, a Balance Sheet and Cash Flow financial statements impact would often follow a prior period Income Statement and Balance Sheet financial statements impact. Balance Sheet Only Impact When information impacts the financial statements via the Balance Sheet only, a movement in an asset, liability or equity account on the Balance Sheet is offset by a counter-acting movement in another asset, liability or equity account on the Balance Sheet. This type of financial statements impact has no impact on earnings or cash, and therefore nothing is reported on the Income Statement of Cash Flow Statement. The following financial statement impacts schematic shows how information might impact the financial statements via the Balance Sheet only. In this example, a non-current called asset called „Machinery‟ has been revaluated downwards by 100m. This decrease in assets is offset by a decrease in the Asset Revaluation (Equity) account: www.bestpracticemodelling.com Page 11 of 40

Financial Statements Modelling Balance Sheet Only Impact Example Income Statement Revenue COGS Gross Margin Operating Expenditure EBITDA Depn. & Amort. EBIT Interest Expense NPBT Tax Expense NPAT Balance Sheet - Cash Flow Statement Opening Cash Change in Cash Held Cash Current Assets Machinery Non-Current Assets Total Assets Current Liablities Non-Current Liabilities Total Liabilties Asset Revaluation Reserve Ordinary Equity Opening Retained Profits Net Profit During Period Retained Profits Total Equity (100) (100) (100) (100) (100) Net Assets (100) Cash Receipts Cash Payments Interest Paid Tax Paid Operating Cash Flow s - Investing Cash Flow s - Financing Cash Flow s - Change in Cash Held - Note from this example that the movement in the asset on the Balance Sheet is not driven by earnings on the Income Statement or cash on the Cash Flow Statement. Instead, the asset revaluation has been offset by a corresponding decrease in Total Equity (via a reduction of the Asset Revaluation Reserve) and is thereby reflected in a 100m reduction in the Net Assets of the entity. All Financial Statements Impact When information impa

Financial Statements Modelling www.bestpracticemodelling.com Page 5 of 40 Financial Statements Module Location 1.2. Financial Statements Modelling Overview The modelling of the financial statements components of an entity is a unique area of spreadsheet modelling, because it involves the systematic linking in of information from