Financial model integrity IDER in-house training materials Hypothetical cases and figures for teaching the methods. Learning objectives Integrate profit, working capital, cash, debt and balance-sheet movements. Diagnose errors with independent mechanical, economic and evidence controls. Test collection scenarios and document reproducible review findings. Basic familiarity with income statements, balance sheets and spreadsheet formulas. Make the model explain the business Integrated profit, balance sheet and cash Independent checks rather than balancing plugs Hypothetical case: GHS thousands A financial model becomes useful when its movements explain the business and its controls reveal mistakes before a decision depends on them. In this clinic we will connect profit, working capital, investment and financing through a complete numerical case. All amounts are hypothetical and expressed in thousands of Ghana cedis unless stated otherwise. We will deliberately introduce errors and examine which checks detect them. Keep three questions in view: does each balance roll forward from an independently supported opening position, does every cash movement have an economic explanation and do changes propagate consistently? A balance sheet that balances is necessary, but it is not evidence that the underlying revenue or assumptions are true. Separate assumptions, schedules and outputs Evidence-linked inputs Connected supporting schedules Decision outputs and independent controls Start with a model map before writing formulas. Put assumptions in a clearly identified input area, give each a unit and source date, then connect operational schedules to the financial statements. A debt schedule should explain debt, interest and principal payments; a fixed-asset schedule should explain purchases, depreciation and carrying value. Avoid entering the same assumption separately in several places because later revisions will drift apart. Use a consistent time axis, sign convention and currency throughout. Define which values are historical observations and which are forecasts. Finally place diagnostic controls near the outputs a reviewer actually sees. Hidden checks on an obscure worksheet provide little protection when the investment committee reads only the summary. Anchor the model to a reconciled balance sheet Cash 200; receivables 300; inventory 400 Equipment 1,000; payables 250; debt 650 Equity 1,000 Assets 200 + 300 + 400 + 1,000 = 1,900 = liabilities + equity Our opening balance sheet contains cash of two hundred, receivables of three hundred, inventory of four hundred and equipment of one thousand. Total assets are nineteen hundred. Payables of two hundred and fifty, debt of six hundred and fifty and equity of one thousand also total nineteen hundred. Before forecasting, reconcile these opening balances to an approved ledger and supporting schedules. If an unexplained difference exists, investigate it rather than inserting a miscellaneous asset. Opening errors travel into every subsequent period and can make a plausible forecast impossible to reconcile. Record the reporting date and whether balances include tax, restricted cash or intercompany amounts. Consistent definitions matter just as much as the arithmetic agreement. Derive earnings from transparent operating drivers Revenue 2,400; cost of sales 1,440 Cash operating costs 480; depreciation 100 Interest 65; illustrative tax 78.75 2,400 − 1,440 − 480 − 100 − 65 − 78.75 = 236.25 Revenue is twenty four hundred and cost of sales fourteen hundred and forty, producing gross profit of nine hundred and sixty. Deduct cash operating costs of four hundred and eighty and depreciation of one hundred to obtain operating profit of three hundred and eighty. Interest of sixty five leaves profit before tax of three hundred and fifteen. Applying an illustrative twenty five percent tax rate gives seventy eight point seven five of tax and net income of two hundred and thirty six point two five. In a real model, revenue should connect to volume, price and recognition evidence; interest should connect to the financing assumptions. A hard-coded profit total prevents meaningful testing when those drivers change. Follow the signs in the working-capital bridge Receivables increase 100; inventory increases 80 Payables increase 50 Cash after interest and tax 206.25 236.25 + 100 − 100 − 80 + 50 = 206.25 Net income does not equal cash generated. Start with two hundred and thirty six point two five, add back depreciation of one hundred, subtract the one hundred increase in receivables and the eighty increase in inventory, then add the fifty increase in payables. Cash after interest and tax is two hundred and six point two five. Increased receivables mean revenue has not yet become cash; increased inventory means funds are tied up in goods; increased supplier balances temporarily preserve cash. Explain these signs using business events rather than memorising them. This is an analytical bridge for our case. Formal cash-flow presentation, including the classification of interest, must follow the applicable reporting framework and period. Connect operating, investing and financing movements Opening cash 200; operating cash 206.25 Capital expenditure 150; new borrowing 50 Principal repayment 100; closing cash 206.25 200 + 206.25 − 150 + 50 − 100 = 206.25 Now roll cash forward independently. Opening cash of two hundred plus cash generated of two hundred and six point two five, less capital expenditure of one hundred and fifty, plus new borrowing of fifty and less principal repayment of one hundred gives closing cash of two hundred and six point two five. The equality between operating cash and closing cash is a coincidence in this example; do not turn it into a formula. Investment belongs in both the asset schedule and the cash schedule, while borrowing affects debt and cash without creating revenue. Label principal separately from interest so that repayment is not accidentally expensed. Each financing movement should reconcile to an actual facility or explicitly stated forecast assumption. Pause: diagnose a cash overstatement A colleague reports closing cash of 306.25 All other case assumptions remain unchanged Find the missing adjustment and explain its sign A colleague's model reports closing cash of three hundred and six point two five, exactly one hundred above our result. The profit statement, investment and debt movements match the case. Inspect the working-capital bridge and determine which omission could explain the difference. Do not simply overwrite closing cash with the expected answer. Write the missing business event, the required sign and the supporting record that would establish the amount. Then identify which second statement or schedule should be checked after correcting the cash formula. Pause the video while you perform the diagnosis. An effective review follows a discrepancy back to its cause and confirms the repair across all affected outputs rather than forcing one visible total to agree. Uncollected revenue cannot fund payments Receivables increased from 300 to 400 Subtract 100 from the cash bridge Correct closing cash: 206.25 306.25 − (400 − 300) = 206.25 The omitted adjustment is the increase in receivables from three hundred to four hundred. That additional one hundred was recognised as revenue but remained uncollected at the reporting date. It must therefore be subtracted when converting profit into cash. Restoring the adjustment reduces closing cash from three hundred and six point two five to two hundred and six point two five. Verify the receivables movement against opening and closing customer balances, then reconcile it to invoice and collection records. The closing balance sheet should carry receivables of four hundred. Also confirm that the direct collections schedule, if present, gives the same cash result. Independent routes to the same total are stronger controls than repeating an identical formula twice. Roll every balance forward without a plug Closing equipment 1,050; debt 600 Closing equity 1,236.25; payables 300 Closing assets and funding both 2,136.25 206.25 + 400 + 480 + 1,050 = 300 + 600 + 1,236.25 Equipment closes at ten hundred and fifty: opening one thousand plus purchases of one hundred and fifty less depreciation of one hundred. Debt closes at six hundred after new borrowing of fifty and principal repayment of one hundred. With no dividends or new equity, retained profit raises equity to twelve hundred and thirty six point two five. Closing assets comprise cash of two hundred and six point two five, receivables of four hundred, inventory of four hundred and eighty and equipment of ten hundred and fifty. They total twenty one hundred and thirty six point two five, equal to payables, debt and equity. Calculate every component independently; making cash the balancing figure would conceal the error we just diagnosed. Build checks with genuinely independent logic Balance-sheet difference Cash and debt roll-forward differences Commercial reasonableness and source reconciliation Three layers of controls serve different purposes. Mechanical checks test whether statements connect: assets equal liabilities plus equity, debt rolls forward and cash movements reconcile. Economic checks test whether outputs make sense: sales cannot exceed capacity without investment, debtor days should match collections and negative inventory requires investigation. Evidence checks trace amounts to contracts, invoices, ledgers and bank records. A model can pass the first layer while failing the other two. For example, fabricated credit sales can increase both profit and receivables while preserving balance-sheet equality. Design checks from a different route or source where possible. Give failures clear thresholds and visible messages, and prevent a presentation from quietly suppressing unresolved material exceptions. Trace one transaction across its full lifecycle Contract and performance evidence Invoice and receivable ledger Bank receipt and reconciliation Take one significant customer transaction and trace it from contract to delivery evidence, invoice, ledger and eventual bank receipt. Each document answers a different question. A signed contract does not prove the promised service was performed; an invoice does not prove cash was collected; a bank receipt may relate to an advance or a different period. Reconcile customer identifiers, dates, amounts and currency across the chain. Investigate credit notes, returns, disputed invoices and cut-off around reporting dates. Use the relevant revenue recognition requirements for the actual contract, rather than assuming every invoice creates immediate revenue. This transaction-level walk-through helps detect errors that aggregate ratios miss and teaches junior analysts how the commercial process creates the financial statements. Change drivers and inspect the whole system Collection timing affects cash and receivables Price changes affect profit and tax Capacity changes can trigger capital expenditure A scenario should modify a business driver and let connected schedules transmit its consequences. Extending a collection period changes receivables and cash, while recognised revenue can remain unchanged. Cutting price generally changes revenue, margin and tax, even if unit volume remains stable. Increasing production beyond capacity may require a discrete investment and additional staffing rather than a smooth percentage increase. After each change, inspect all statements and control totals. Freeze the scenario inputs with a descriptive version name and record why the assumptions belong together. Avoid editing output cells to create a desired result. For complex models, isolate circular dependencies such as cash-funded debt repayment and interest, then document the chosen calculation method and its limitations. Pause: an expected receipt remains outstanding 30 of already recognised sales remains unpaid Revenue and tax are unchanged Calculate closing cash, receivables and total assets Return to the complete base model. Thirty thousand cedis of already recognised sales that were expected to be collected remains outstanding at the year end. Revenue and tax are unchanged; this is purely a collection timing difference. Calculate the new closing cash balance, the new receivables balance and the effect on total assets. Explain why net income should not change in this specified exercise. Do not introduce an additional sale: the transaction is already included in the original revenue total. Then name the operational risk that a larger or repeated delay could create even though total assets remain constant. Pause now and write the three revised outputs plus a short explanation before viewing the worked answer. A collection delay changes asset composition Closing cash 176.25 Closing receivables 430 Total assets remain 2,136.25 Cash 206.25 − 30 = 176.25; receivables 400 + 30 = 430 Closing cash falls by thirty to one hundred and seventy six point two five, while receivables rise by thirty to four hundred and thirty. Total assets remain twenty one hundred and thirty six point two five because one asset category replaces another. Net income is unchanged under the exercise assumptions: no new revenue, tax change or impairment has been introduced. Nevertheless, readily available cash has declined, which can threaten payment capacity or borrowing headroom. In an actual review, investigate whether the delay signals a dispute or credit deterioration requiring further assessment. The lesson is that a balanced statement can conceal a liquidity problem. Use ageing and subsequent collections to distinguish an ordinary timing variation from a worsening recoverability issue. Make review findings reproducible Preserve an untouched baseline Log the cause, repair and affected outputs Separate preparer and reviewer sign-off Before reviewing a model, preserve the original version and record the assumptions used to generate its outputs. For each finding, capture the cell or schedule, the business explanation, supporting evidence, proposed correction and effect on decisions. Classify issues by consequence rather than cosmetic appearance: an incorrect debt maturity can matter more than inconsistent formatting. After a repair, rerun relevant scenarios and confirm that the change has not broken another schedule. A second reviewer should reproduce important calculations from independent evidence, not merely click through the author's formulas. Sign-off should identify who prepared and reviewed the work, when they did so and which limitations remain. Never invent a professional signature or imply an assurance review that did not occur. Use a compact model-integrity review pack Opening balances and evidence register Integrated statements with visible controls Exception log and scenario commentary Apply this clinic by selecting a model with an upcoming decision and preparing a compact integrity review. Reconcile opening balances, rebuild a profit-to-cash bridge and independently roll forward debt, equipment and equity. Trace one material transaction through its evidence chain, then introduce a collection-delay scenario and inspect every affected output. Your deliverable should include the source register, visible checks, an exception log and a short explanation of the model's remaining limitations. Complete the six questions and two applied tasks before using the procedure on a live assignment. The goal is not a workbook with no warning lights at any cost. It is a model whose numbers, assumptions and unresolved uncertainties can be explained and challenged responsibly. Check your understanding 1. Why should cash not be the balancing plug in a forecast balance sheet? A. It makes rounding impossible B. It can conceal missing operating or financing movements C. It prevents revenue recognition D. It always understates equity 2. What is net income in the case, in GHS thousands? A. 315.00 B. 380.00 C. 206.25 D. 236.25 3. How does a 100 increase in receivables affect the profit-to-cash bridge? A. Add 100 B. Subtract 100 C. No adjustment D. Add 200 4. What is closing debt after opening 650, new borrowing 50 and principal repayment 100? A. 600 B. 700 C. 800 D. 500 5. An expected 30 receipt from already recognised sales remains outstanding, with revenue/tax unchanged. What are cash and receivables? A. 236.25 and 370 B. 176.25 and 430 C. 206.25 and 430 D. 176.25 and 400 6. Which conclusion is justified when a model’s balance sheet balances? A. All revenue is authentic B. No liquidity problem exists C. The accounting equation reconciles, but evidence and economic checks remain necessary D. All assumptions are reasonable Applied task 1 Rebuild the three statements and supporting debt and equipment schedules for the numerical case. Applied task 2 Test the delayed 30 receipt and write an evidence-based review finding. Answers and explanations 1. B. Cash must roll forward from independently derived movements. A balancing plug can hide an omitted receivables adjustment or unmodelled financing need. (Slide 9) 2. D. Revenue 2,400 − cost of sales 1,440 − cash operating costs 480 − depreciation 100 − interest 65 − tax 78.75 = 236.25. (Slide 4) 3. B. Revenue already includes the sales, but the increase remains uncollected. Subtract the increase when reconciling profit to cash. (Slide 8) 4. A. Debt rolls forward as 650 + 50 − 100 = 600. Interest is a separate expense and cash movement. (Slide 9) 5. B. Cash falls from 206.25 to 176.25; receivables rise from 400 to 430. Total assets and net income are unchanged under the stated assumptions. (Slide 14) 6. C. Mechanical consistency cannot establish commercial truth. Unsupported credit sales can increase both equity and receivables while preserving equality. (Slide 10) Applied task 1 Net income is 236.25; cash after interest and tax is 206.25; closing cash is 206.25. Equipment closes at 1,050, debt at 600 and equity at 1,236.25. Total assets and liabilities plus equity both equal 2,136.25, in GHS thousands. Shows the receivables, inventory and payables signs correctly. Calculates cash, equipment and debt independently without plugs. Reconciles the closing balance sheet and explains the units. Applied task 2 With already recognised sales and unchanged revenue/tax, cash becomes 176.25 and receivables 430; total assets remain 2,136.25. Request customer ageing, dispute details and subsequent bank collections; document the liquidity implication and whether a separate impairment assessment is required. Keeps revenue and tax unchanged in the specified scenario. Traces the change through cash, receivables and control totals. Documents cause, evidence, decision effect and reviewer responsibility. References IFRS Foundation — IAS 7 Statement of Cash Flows https://www.ifrs.org/issued-standards/list-of-standards/ias-7-statement-of-cash-flows/ Cash-flow and non-cash reconciliation concepts. The worked case is an original analytical bridge, not a complete prescribed reporting presentation. IFRS Foundation — IFRS 15 Revenue from Contracts with Customers https://www.ifrs.org/issued-standards/list-of-standards/ifrs-15-revenue-from-contracts-with-customers/ Distinguishing contract performance, revenue recognition, invoicing and collection; actual contracts require the applicable standard.