WEBVTT NOTE IDER in-house training materials. Captions follow measured phrase audio timings. Hypothetical worked cases; figures are instructional examples. 00:00:00.000 --> 00:00:05.978 A financial model becomes useful when its movements explain the business and its controls reveal mistakes 00:00:05.978 --> 00:00:08.220 before a decision depends on them. 00:00:08.340 --> 00:00:14.907 In this clinic we will connect profit, working capital, investment and financing through a complete 00:00:14.907 --> 00:00:21.474 numerical case. All amounts are hypothetical and expressed in thousands of Ghana cedis unless stated 00:00:21.474 --> 00:00:21.912 otherwise. 00:00:22.032 --> 00:00:26.746 We will deliberately introduce errors and examine which checks detect them. 00:00:26.866 --> 00:00:33.387 Keep three questions in view: does each balance roll forward from an independently supported opening 00:00:33.387 --> 00:00:39.474 position, does every cash movement have an economic explanation and do changes propagate consistently? 00:00:39.594 --> 00:00:46.620 A balance sheet that balances is necessary, but it is not evidence that the underlying revenue or 00:00:46.620 --> 00:00:47.860 assumptions are true. 00:00:48.530 --> 00:00:55.445 Start with a model map before writing formulas. Put assumptions in a clearly identified input area, give 00:00:55.445 --> 00:01:01.139 each a unit and source date, then connect operational schedules to the financial statements. 00:01:01.259 --> 00:01:07.890 A debt schedule should explain debt, interest and principal payments; a fixed-asset schedule should 00:01:07.890 --> 00:01:10.732 explain purchases, depreciation and carrying value. 00:01:10.852 --> 00:01:16.710 Avoid entering the same assumption separately in several places because later revisions will drift 00:01:16.710 --> 00:01:21.313 apart. Use a consistent time axis, sign convention and currency throughout. 00:01:21.433 --> 00:01:28.569 Define which values are historical observations and which are forecasts. Finally place diagnostic controls 00:01:28.569 --> 00:01:32.137 near the outputs a reviewer actually sees. 00:01:32.257 --> 00:01:38.086 Hidden checks on an obscure worksheet provide little protection when the investment committee reads only 00:01:38.086 --> 00:01:38.864 the summary. 00:01:39.534 --> 00:01:46.583 Our opening balance sheet contains cash of two hundred, receivables of three hundred, inventory of 00:01:46.583 --> 00:01:52.223 four hundred and equipment of one thousand. Total assets are nineteen hundred. 00:01:52.343 --> 00:01:59.402 Payables of two hundred and fifty, debt of six hundred and fifty and equity of one thousand also total 00:01:59.402 --> 00:02:00.145 nineteen hundred. 00:02:00.265 --> 00:02:07.934 Before forecasting, reconcile these opening balances to an approved ledger and supporting schedules. If an 00:02:07.934 --> 00:02:13.559 unexplained difference exists, investigate it rather than inserting a miscellaneous asset. 00:02:13.679 --> 00:02:21.369 Opening errors travel into every subsequent period and can make a plausible forecast impossible to reconcile. 00:02:21.369 --> 00:02:28.098 Record the reporting date and whether balances include tax, restricted cash or intercompany amounts. 00:02:28.218 --> 00:02:33.141 Consistent definitions matter just as much as the arithmetic agreement. 00:02:33.811 --> 00:02:39.882 Revenue is twenty four hundred and cost of sales fourteen hundred and forty, producing gross profit of 00:02:39.882 --> 00:02:41.311 nine hundred and sixty. 00:02:41.431 --> 00:02:47.893 Deduct cash operating costs of four hundred and eighty and depreciation of one hundred to obtain operating 00:02:47.893 --> 00:02:50.173 profit of three hundred and eighty. 00:02:50.293 --> 00:02:55.065 Interest of sixty five leaves profit before tax of three hundred and fifteen. 00:02:55.185 --> 00:03:02.327 Applying an illustrative twenty five percent tax rate gives seventy eight point seven five of tax and net 00:03:02.327 --> 00:03:06.296 income of two hundred and thirty six point two five. 00:03:06.416 --> 00:03:12.938 In a real model, revenue should connect to volume, price and recognition evidence; interest should 00:03:12.938 --> 00:03:19.025 connect to the financing assumptions. A hard-coded profit total prevents meaningful testing when those 00:03:19.025 --> 00:03:19.895 drivers change. 00:03:20.565 --> 00:03:23.398 Net income does not equal cash generated. 00:03:23.518 --> 00:03:30.687 Start with two hundred and thirty six point two five, add back depreciation of one hundred, subtract the one 00:03:30.687 --> 00:03:35.592 hundred increase in receivables and the eighty increase in inventory, then add the 00:03:35.712 --> 00:03:43.282 fifty increase in payables. Cash after interest and tax is two hundred and six point two five. 00:03:43.402 --> 00:03:51.239 Increased receivables mean revenue has not yet become cash; increased inventory means funds are tied up in 00:03:51.239 --> 00:03:54.466 goods; increased supplier balances temporarily preserve cash. 00:03:54.586 --> 00:04:01.318 Explain these signs using business events rather than memorising them. This is an analytical bridge for our 00:04:01.318 --> 00:04:01.714 case. 00:04:01.834 --> 00:04:09.062 Formal cash-flow presentation, including the classification of interest, must follow the applicable 00:04:09.062 --> 00:04:11.471 reporting framework and period. 00:04:12.141 --> 00:04:14.660 Now roll cash forward independently. 00:04:14.780 --> 00:04:21.755 Opening cash of two hundred plus cash generated of two hundred and six point two five, less capital 00:04:21.755 --> 00:04:27.180 expenditure of one hundred and fifty, plus new borrowing of fifty and less principal 00:04:27.300 --> 00:04:31.978 repayment of one hundred gives closing cash of two hundred and six point two five. 00:04:32.098 --> 00:04:39.210 The equality between operating cash and closing cash is a coincidence in this example; do not turn it into 00:04:39.210 --> 00:04:39.958 a formula. 00:04:40.078 --> 00:04:47.967 Investment belongs in both the asset schedule and the cash schedule, while borrowing affects debt and cash 00:04:47.967 --> 00:04:54.463 without creating revenue. Label principal separately from interest so that repayment is not accidentally 00:04:54.463 --> 00:04:54.927 expensed. 00:04:55.047 --> 00:05:02.257 Each financing movement should reconcile to an actual facility or explicitly stated forecast assumption. 00:05:02.927 --> 00:05:09.487 A colleague's model reports closing cash of three hundred and six point two five, exactly one hundred 00:05:09.487 --> 00:05:14.502 above our result. The profit statement, investment and debt movements match the case. 00:05:14.622 --> 00:05:21.256 Inspect the working-capital bridge and determine which omission could explain the difference. Do not simply 00:05:21.256 --> 00:05:24.352 overwrite closing cash with the expected answer. 00:05:24.472 --> 00:05:30.195 Write the missing business event, the required sign and the supporting record that would establish the 00:05:30.195 --> 00:05:35.561 amount. Then identify which second statement or schedule should be checked after correcting the cash 00:05:35.561 --> 00:05:35.919 formula. 00:05:36.039 --> 00:05:38.907 Pause the video while you perform the diagnosis. 00:05:39.027 --> 00:05:45.654 An effective review follows a discrepancy back to its cause and confirms the repair across all affected 00:05:45.654 --> 00:05:49.162 outputs rather than forcing one visible total to agree. 00:05:49.832 --> 00:05:56.487 The omitted adjustment is the increase in receivables from three hundred to four hundred. That additional 00:05:56.487 --> 00:06:01.895 one hundred was recognised as revenue but remained uncollected at the reporting date. 00:06:02.015 --> 00:06:07.397 It must therefore be subtracted when converting profit into cash. Restoring the adjustment reduces closing 00:06:07.397 --> 00:06:13.497 cash from three hundred and six point two five to two hundred and six point two five. 00:06:13.617 --> 00:06:19.858 Verify the receivables movement against opening and closing customer balances, then reconcile it to 00:06:19.858 --> 00:06:26.098 invoice and collection records. The closing balance sheet should carry receivables of four hundred. 00:06:26.218 --> 00:06:32.631 Also confirm that the direct collections schedule, if present, gives the same cash result. Independent 00:06:32.631 --> 00:06:38.617 routes to the same total are stronger controls than repeating an identical formula twice. 00:06:39.287 --> 00:06:47.172 Equipment closes at ten hundred and fifty: opening one thousand plus purchases of one hundred and fifty less 00:06:47.172 --> 00:06:48.924 depreciation of one hundred. 00:06:49.044 --> 00:06:54.605 Debt closes at six hundred after new borrowing of fifty and principal repayment of one hundred. 00:06:54.725 --> 00:07:01.228 With no dividends or new equity, retained profit raises equity to twelve hundred and thirty six point 00:07:01.228 --> 00:07:01.993 two five. 00:07:02.113 --> 00:07:08.571 Closing assets comprise cash of two hundred and six point two five, receivables of four hundred, inventory 00:07:08.571 --> 00:07:13.131 of four hundred and eighty and equipment of ten hundred and fifty. 00:07:13.251 --> 00:07:20.100 They total twenty one hundred and thirty six point two five, equal to payables, debt and equity. 00:07:20.220 --> 00:07:27.350 Calculate every component independently; making cash the balancing figure would conceal the error we just 00:07:27.350 --> 00:07:27.825 diagnosed. 00:07:28.495 --> 00:07:35.027 Three layers of controls serve different purposes. Mechanical checks test whether statements connect: 00:07:35.027 --> 00:07:41.057 assets equal liabilities plus equity, debt rolls forward and cash movements reconcile. 00:07:41.177 --> 00:07:49.158 Economic checks test whether outputs make sense: sales cannot exceed capacity without investment, debtor days 00:07:49.158 --> 00:07:53.414 should match collections and negative inventory requires investigation. 00:07:53.534 --> 00:08:00.270 Evidence checks trace amounts to contracts, invoices, ledgers and bank records. A model can pass the first 00:08:00.270 --> 00:08:02.648 layer while failing the other two. 00:08:02.768 --> 00:08:08.821 For example, fabricated credit sales can increase both profit and receivables while preserving balance-sheet 00:08:08.821 --> 00:08:13.577 equality. Design checks from a different route or source where possible. 00:08:13.697 --> 00:08:20.992 Give failures clear thresholds and visible messages, and prevent a presentation from quietly suppressing 00:08:20.992 --> 00:08:22.555 unresolved material exceptions. 00:08:23.225 --> 00:08:30.281 Take one significant customer transaction and trace it from contract to delivery evidence, invoice, ledger 00:08:30.281 --> 00:08:34.986 and eventual bank receipt. Each document answers a different question. 00:08:35.106 --> 00:08:41.355 A signed contract does not prove the promised service was performed; an invoice does not prove cash was 00:08:41.355 --> 00:08:45.868 collected; a bank receipt may relate to an advance or a different period. 00:08:45.988 --> 00:08:53.362 Reconcile customer identifiers, dates, amounts and currency across the chain. Investigate credit notes, 00:08:53.362 --> 00:08:57.900 returns, disputed invoices and cut-off around reporting dates. 00:08:58.020 --> 00:09:05.064 Use the relevant revenue recognition requirements for the actual contract, rather than assuming every 00:09:05.064 --> 00:09:07.076 invoice creates immediate revenue. 00:09:07.196 --> 00:09:13.418 This transaction-level walk-through helps detect errors that aggregate ratios miss and teaches junior 00:09:13.418 --> 00:09:17.726 analysts how the commercial process creates the financial statements. 00:09:18.396 --> 00:09:24.892 A scenario should modify a business driver and let connected schedules transmit its consequences. 00:09:24.892 --> 00:09:31.388 Extending a collection period changes receivables and cash, while recognised revenue can remain unchanged. 00:09:31.508 --> 00:09:38.457 Cutting price generally changes revenue, margin and tax, even if unit volume remains stable. Increasing 00:09:38.457 --> 00:09:44.944 production beyond capacity may require a discrete investment and additional staffing rather than a 00:09:44.944 --> 00:09:46.334 smooth percentage increase. 00:09:46.454 --> 00:09:52.784 After each change, inspect all statements and control totals. Freeze the scenario inputs with a descriptive 00:09:52.784 --> 00:09:56.345 version name and record why the assumptions belong together. 00:09:56.465 --> 00:10:05.118 Avoid editing output cells to create a desired result. For complex models, isolate circular dependencies such 00:10:05.118 --> 00:10:12.689 as cash-funded debt repayment and interest, then document the chosen calculation method and its 00:10:12.689 --> 00:10:13.230 limitations. 00:10:13.900 --> 00:10:19.503 Return to the complete base model. Thirty thousand cedis of already recognised sales that were expected 00:10:19.503 --> 00:10:22.654 to be collected remains outstanding at the year end. 00:10:22.774 --> 00:10:29.037 Revenue and tax are unchanged; this is purely a collection timing difference. Calculate the new 00:10:29.037 --> 00:10:34.465 closing cash balance, the new receivables balance and the effect on total assets. 00:10:34.585 --> 00:10:41.314 Explain why net income should not change in this specified exercise. Do not introduce an additional 00:10:41.314 --> 00:10:45.940 sale: the transaction is already included in the original revenue total. 00:10:46.060 --> 00:10:52.791 Then name the operational risk that a larger or repeated delay could create even though total assets 00:10:52.791 --> 00:10:53.583 remain constant. 00:10:53.703 --> 00:10:59.647 Pause now and write the three revised outputs plus a short explanation before viewing the worked answer. 00:11:00.317 --> 00:11:06.404 Closing cash falls by thirty to one hundred and seventy six point two five, while receivables rise by 00:11:06.404 --> 00:11:08.433 thirty to four hundred and thirty. 00:11:08.553 --> 00:11:15.559 Total assets remain twenty one hundred and thirty six point two five because one asset category replaces 00:11:15.559 --> 00:11:15.972 another. 00:11:16.092 --> 00:11:23.384 Net income is unchanged under the exercise assumptions: no new revenue, tax change or impairment 00:11:23.384 --> 00:11:30.190 has been introduced. Nevertheless, readily available cash has declined, which can threaten payment capacity 00:11:30.190 --> 00:11:31.649 or borrowing headroom. 00:11:31.769 --> 00:11:38.020 In an actual review, investigate whether the delay signals a dispute or credit deterioration requiring 00:11:38.020 --> 00:11:43.855 further assessment. The lesson is that a balanced statement can conceal a liquidity problem. 00:11:43.975 --> 00:11:51.299 Use ageing and subsequent collections to distinguish an ordinary timing variation from a worsening 00:11:51.299 --> 00:11:52.346 recoverability issue. 00:11:53.016 --> 00:11:59.029 Before reviewing a model, preserve the original version and record the assumptions used to generate 00:11:59.029 --> 00:11:59.831 its outputs. 00:11:59.951 --> 00:12:07.385 For each finding, capture the cell or schedule, the business explanation, supporting evidence, proposed 00:12:07.385 --> 00:12:10.040 correction and effect on decisions. 00:12:10.160 --> 00:12:17.977 Classify issues by consequence rather than cosmetic appearance: an incorrect debt maturity can matter more 00:12:17.977 --> 00:12:19.541 than inconsistent formatting. 00:12:19.661 --> 00:12:26.011 After a repair, rerun relevant scenarios and confirm that the change has not broken another schedule. 00:12:26.131 --> 00:12:32.253 A second reviewer should reproduce important calculations from independent evidence, not merely 00:12:32.253 --> 00:12:34.804 click through the author's formulas. 00:12:34.924 --> 00:12:40.839 Sign-off should identify who prepared and reviewed the work, when they did so and which limitations remain. 00:12:40.839 --> 00:12:45.710 Never invent a professional signature or imply an assurance review that did not occur. 00:12:46.380 --> 00:12:53.798 Apply this clinic by selecting a model with an upcoming decision and preparing a compact integrity 00:12:53.798 --> 00:12:59.362 review. Reconcile opening balances, rebuild a profit-to-cash bridge and independently roll forward 00:12:59.362 --> 00:13:01.217 debt, equipment and equity. 00:13:01.337 --> 00:13:08.335 Trace one material transaction through its evidence chain, then introduce a collection-delay scenario and 00:13:08.335 --> 00:13:10.335 inspect every affected output. 00:13:10.455 --> 00:13:16.890 Your deliverable should include the source register, visible checks, an exception log and a short 00:13:16.890 --> 00:13:19.464 explanation of the model's remaining limitations. 00:13:19.584 --> 00:13:25.301 Complete the six questions and two applied tasks before using the procedure on a live assignment. The 00:13:25.301 --> 00:13:29.337 goal is not a workbook with no warning lights at any cost. 00:13:29.457 --> 00:13:35.989 It is a model whose numbers, assumptions and unresolved uncertainties can be explained and 00:13:35.989 --> 00:13:36.922 challenged responsibly.