The Workbook: Layout Conventions That Survive Review
A workbook survives review when its shape answers a reviewer's questions before anybody asks them. Six tabs in one fixed order: cover, inputs, source log, working, output, checks. Numbers somebody filed sit apart from numbers somebody chose. A formula check asks whether a cell does what it claims; a reconciliation asks whether two independently built totals agree.
One slightly uncomfortable fact sits underneath every workbook convention. A reviewer has an hour, and the file has hundreds of cells. Nobody opens two hundred formulas one at a time and reads each of them. A reviewer opens perhaps twenty, and how the file is arranged decides almost entirely which twenty. Review is a sampling exercise whether anybody says so out loud or not, and layout aims the sample.
One assignment carries every convention below. The Kavery research desk, four people inside the invented firm Kavery Capital Services Private Limited, was asked a question about Meenakshi Tubes Private Limited, an invented manufacturer. Sharada Iyer built the file, Prakash Nadar reviewed it, and Latha Menon decided on the strength of it.
The subject of the work does not matter. Substitute a bond, a fund, a warehouse or a single machine for Meenakshi Tubes Private Limited and not one convention below moves. Layout is a property of the container, not the contents. The arithmetic inside the file, what it should compute and whether that computation answers the question asked, is a separate subject.
What Are the Six Tabs, and Why Is the Order Worth Being Rigid About?
A workbookThe file holding the work, laid out so that somebody else can follow it without being told how. is not one sheet with everything on it, and it is not fifteen sheets named after whoever made them. The file is six sheets, each doing one job, in an order that never changes between files. Cover, inputs, source log, working, output, checks. Sharada Iyer used that order on this assignment and on the one before it, and Prakash Nadar could therefore start reviewing without a conversation about where anything lives.
Think about a hotel kitchen for a moment. Every kitchen in the country cooks something different. A new cook walking into a well run one on their first shift still finds the knives. Knives live where knives go. The menu is the interesting part and the menu is not the part that has to be standard. The tab order is arbitrary and the consistency is not. Arguing about which order is best wastes an afternoon. Adopting one is worth an hour.
Each tab answers one question a reviewer would otherwise have to ask a person. The cover answers what am I looking at. The inputs tab answers where did the numbers come from. The source log answers can I get from a cell to a document. The working tab answers does the arithmetic do what it claims. The output tab answers which figures actually left the file. The checks tab answers whether anybody tested this before the reviewer arrived. Tab orderThe fixed sequence of sheets in a file, so that a reader knows where to look before opening anything. is not decoration on top of that; it is what lets a reviewer skip straight to the tab holding the question they came with.
What Does the Cover Tab Have to Say Before Anybody Scrolls?
The cover is the shortest tab and the one most often skipped. Skipping it is a mistake worth naming. Four lines do the work. One sentence a stranger can read, saying which question the file answers. Who built it and who reviewed it, by name. The date it was last edited. And one line saying which question the file must not be reused for. A file built to test one question gets reused for a different question within about a month, and the only defence is a sentence sitting where the person doing the reusing has to scroll past it.
Why insist on the same tab order in every file the desk builds, rather than letting each person arrange their own?
Input vs Assumption: Which Numbers Did Somebody File, and Which Did Somebody Choose?
Both of these words describe a number sitting on the Inputs tab, and that is exactly why they get confused. Take each one separately before putting them side by side. An inputA number somebody filed, observed or measured, tied to the source it came from. is a number that arrived from outside the desk. Somebody else filed it, observed it or measured it, and it exists in a document that can be opened by a person who has never met the analyst. Revenue of Rs 21,20,00,000 for the year just reported is an input. The number sits in the filed accounts, and anybody with the filing lands on the same one.
An assumptionA number somebody on the desk chose, because no document supplied one, tied to its register row. is a number that arrived from inside the desk. No document supplied it, so a person decided it. The split that puts 60 per cent of the Rs 18,90,00,000 total cost in the variable column and 40 per cent in the fixed one is an assumption. Nobody filed a split, and Sharada Iyer chose one. The split may be sensible, consistent and defensible, and it is still a choice made by a person on a Tuesday.
Now put the two side by side. The contrast is the useful part. On the Inputs tab of this file there were 31 numbers. Twenty two of them were filed by somebody, and nine were chosen by the desk. Twenty two plus nine is thirty one, and the nine chosen ones are exactly the nine rows of the assumption register, referenced by row number rather than retyped. Nothing about the cells themselves showed which was which, and that is the whole reason the split has to be made visible by hand. The number 60 in a cell looks precisely as authoritative as the number 21,20,00,000, and one of them would survive a stranger opening a document while the other one would not.
The household version of this is a budget sheet on a kitchen table. The electricity bill for last month is an input. The bill exists, and anybody can pick it up. The line saying school fees will rise about eight per cent next year is an assumption. No letter has arrived saying so, and somebody in the household decided it. Both are numbers written in the same handwriting on the same sheet. If a fight starts three months later about why the plan did not work, the useful question is which of the numbers was chosen, and the sheet has to answer it without anybody remembering.
Revenue of Rs 21,20,00,000 taken from the filed accounts, and a split putting 60 per cent of the Rs 18,90,00,000 total cost in the variable column because the desk decided it. Which is which?
Why Does the Split Have to Be Visible on the Screen, Not Only in the Structure?
Keeping the two kinds of number on one tab is the structural half. Making them tell themselves apart at a glance is the other half, and it is the half that gets dropped. Three conventions do it, and none of them is clever. The chosen numbers sit together in one block rather than being scattered among the filed ones. The chosen numbers carry a fill colour the filed ones do not have. And the column that holds a source log item number is simply empty on every chosen number, so a reviewer scanning one narrow column down the tab finds all nine of them without reading a word.
The test for the visual half is whether a reviewer can count the chosen numbers from across the room, and if they have to click cells to count them, the split exists in the builder's head rather than in the file. Nine is a small number, and a reviewer who knows there are nine can decide in about a minute which of the nine are worth arguing about. Thirty one undifferentiated numbers offer no such decision. The reviewer either checks everything, an impossibility in an hour, or checks the ones nearest the top. Checking the top ones is the worse of the two: it is arbitrary and it feels thorough.
How to Document Assumptions in a Financial Workbook: Where Does the Note Live?
An assumption needs a record of who chose it, when, on what basis, and what would change it. The full record already exists, one row per assumption, in the assumption register. The narrower question, and the one more often got wrong, is what goes into the workbook itself, right beside the number.
The answer is a pointer and not a copy. The cell next to the 60 per cent holds the characters REG 4, for register (REG) row four, and nothing else. The cell does not hold the reasoning. Nor does it hold two sentences summarising the reasoning. Copying the register into the file leaves two versions of the same sentence that can disagree, and a pointer leaves one version that cannot. The pointer costs six characters and survives every edit anybody makes to either the model or the register. A row number does not go stale when the text of the row is improved.
Three things then follow from the pointer being where it is. A reviewer clicking any suspicious number sees immediately whether it is chosen. A register reference is either there or it is not. A person editing the file cannot change a chosen number without looking straight at a reference telling them a register row is now out of date. And anybody counting chosen numbers counts references rather than opening cells. The convention is small enough that people skip it, and the cost of skipping it lands months later on somebody who was not in the room.
Why not keep the assumption documentation in a well written document sitting beside the workbook instead of inside it?
Why Does the Note Have to Sit Inside the File Rather Than Beside It?
Because numbers get edited where they live, and documentation kept somewhere else does not get edited at the same moment. Editing in one place and not the other is the entire mechanism, and it has nothing to do with anybody being careless. Somebody opens the model on a Thursday afternoon to answer a follow-up question, changes a chosen number because that is where the number is, saves the file and closes it. Every step of that is correct. The document sitting in the next folder was never opened, so it was never updated, and now it describes a file that no longer exists.
The rule that prevents drift is proximity, not discipline. Proximity works on the days when discipline does not. A note in the cell beside the number is in front of the person editing the number, so ignoring it is an act rather than an omission. A note in another file is out of sight of the person editing, so keeping the pair aligned depends on somebody remembering an obligation while they are thinking about something else. Anybody who has kept two calendars in a household knows how that ends, and it does not end with two calendars agreeing.
The assumption documentation is a separate file, well written, complete and accurate on the day it is finished. How long before it says something the workbook does not?
The failure: two files, both correct, that stopped agreeing
Nothing in this failure is a wrong number. Sharada Iyer wrote the assumption documentation as a separate document, and it was a good one: every chosen number explained, the reasoning laid out, the whole thing complete and accurate on the day it was written. The document sat in the same folder as the workbook. The model is where the number lives, so two weeks later somebody revised the cost split inside it, saved the file, and did not open the document. Neither file is wrong on its own. The workbook holds a revised split with no note beside it, and the document holds a careful explanation of a split the model no longer uses.
The cost is paid by a third person. Prakash Nadar, reading the document, has been told something the file does not do, and he has no way to know it because the document reads as authoritative and is dated. He reviews an explanation of a model nobody is running any more. A document beside a file is correct on the day it is written and quietly wrong within a fortnight, and the failure leaves no artefact anybody can point at. The fix is not a rule about remembering. The fix is moving the note into the cell beside the number, where the pair physically cannot separate.
The Formula Check: Does This Cell Do What It Says?
A formula checkTesting whether a cell computes what the label beside it claims it computes. is one question asked of one cell at a time. Read the label beside the cell, read the formula inside it, and decide whether the second delivers the first. A formula check is nothing more than that. The check requires no second source, no external document and no judgement about whether the number is plausible. A formula check compares a claim against a mechanism, and both are already in the file.
Four things turn up. A formula that references the wrong row, so it computes something correct about the wrong item. A total that stops one row short of the block it claims to sum. A sign that runs the wrong way. And most commonly of all, a hardcodeA number typed inside a formula instead of referenced from the tab that holds it.. A hardcode is a number typed straight into a formula rather than pointed at on the Inputs tab, and it is not wrong on the day it is typed. A hardcode goes wrong on the day somebody updates the Inputs tab and the file quietly keeps using the old number. A typed number does not know that anything changed.
Before reading on. Of 214 formulas in a file built carefully by somebody who knows the conventions, how many hold a number typed inside them?
Here is what the check found in this file. Sharada Iyer ran it over all 214 formulas on the working tab, and 11 of them held a number typed inside. Eleven in two hundred and fourteen is 5.1 per cent. The 5.1 per cent is the ordinary rate rather than the mark of a bad day, and knowing it is ordinary is what makes the check get run rather than dodged. A person expecting zero treats every hardcode found as an accusation, argues about it, and eventually stops running the check to avoid the argument. A person expecting about one in twenty finds eleven, fixes eleven, writes down that eleven were found, and the file is better by lunchtime.
The formula check passes. Every single cell in the file computes exactly what the label beside it claims. Is the answer right?
Formula Check vs Reconciliation: Which Question Is Each One Asking?
Define the second one properly before setting it against the first. A reconciliation is not a stricter formula check, and treating it as one is where people go wrong. A reconciliationTesting whether two totals, built by genuinely separate routes, arrive at the same figure. builds the same total twice, by two routes that share nothing, and asks whether the two arrive at the same place. The word doing the work is independent. If the second route reads the same cell, uses the same formula or comes from the same document, it is not a second route. A second route like that is the first route run twice, and running something twice shows only that the hands were steady.
On this file the reconciliation was set up on revenue. Route one is the total as filed, Rs 21,20,00,000, typed once onto the Inputs tab from the document. Route two is built inside the file by adding the four quarters: Rs 4,80,00,000 plus Rs 5,20,00,000 plus Rs 6,40,00,000 plus Rs 4,80,00,000. The four quarters sum to Rs 21,20,00,000. The two agree to the rupee, and the difference line on the checks tab reads nil. Why the four quarters are unequal, and what any of them means, belongs to the work rather than to the file; the file only has to make the two routes meet.
Now set the two checks against each other. The useful fact is that each one passes exactly the error the other catches. A formula check asks a question about a cell. A reconciliation asks a question about a total. Suppose the whole file had been built on last year's revenue by mistake. Every formula would still reference the Inputs tab correctly, every label would still describe its cell honestly, and the formula check would pass without a single mark against it. The reconciliation would fail on the first line. The four quarters would sum to one figure and the input cell would hold another. Running one of the two is not a shortcut to running both. The two are not stronger and weaker versions of one test.
| The formula check | The reconciliation | |
|---|---|---|
| The question | Does this cell compute what the label beside it claims? | Do two totals, built by separate routes, arrive at the same figure? |
| Where it looks | One cell at a time, and nothing outside the file | Two whole routes, at least one of which comes from outside |
| What it catches | A typed number, a wrong reference, a total short by a row, a flipped sign | A file built correctly on the wrong figure entirely |
| What it misses | Right arithmetic performed on the wrong input | A cell doing something other than its label says, inside a route that still totals correctly |
| On this file | 214 formulas checked, 11 held a typed number, 5.1 per cent | Rs 21,20,00,000 against Rs 21,20,00,000, agreeing to the rupee |
| Time it took | The longer of the two, cell by cell | Minutes, once the second route exists |
A reconciliation has to be designed for revenue in this file. Which of these three is actually one?
What Did the Finished File Actually Look Like When It Was Handed Over?
Everything above lands in one file, so here is that file counted rather than described. One assignment produced this whole workbook, and it is smaller than people expect a reviewable file to be.
| Item counted | Count | What it means for a reviewer |
|---|---|---|
| Tabs, in the fixed order | 6 | They know where to start without asking anybody |
| Numbers on the Inputs tab | 31 | Every raw number in the file, each written exactly once |
| Of those, filed by somebody | 22 | Each carries a source log item number, so it can be opened |
| Of those, chosen by the desk | 9 | Each carries a register row number, and they are the nine register rows |
| Formulas on the working tab | 214 | The whole surface the formula check had to walk |
| Formulas holding a typed number | 11 | 5.1 per cent, found and fixed, and written down as found |
| Reconciliations run | 1 | Rs 21,20,00,000 by two routes, agreeing to the rupee |
Two arithmetic facts in that table are worth pausing on. Both are what make the file checkable rather than merely tidy. Twenty two plus nine is thirty one, so every number on the Inputs tab is accounted for and none is sitting there unclassified. And the nine chosen numbers are the same nine as the register rows, not nine numbers that happen to resemble them. A reviewer can hold the register beside the file and match them one for one. A count that reconciles is worth more than a convention that is merely followed. A count can be checked by somebody who does not trust the builder.
The Inputs tab holds 31 numbers and the assumption register runs to 9 rows. How many of the 31 should be carrying a source log item number rather than a register row?
Who says a file like this has to be kept at all?
Everything above is craft and is universal, but the duty to retain working papers and to produce them on request is not. In India, record-keeping and disclosure duties for registered intermediaries come from the Securities and Exchange Board of India, and documentation and engagement review standards come from the Institute of Chartered Accountants of India. Retention periods, applicability tests and thresholds move. Check the current requirement against the regulator's own text and note the date of the check. A remembered requirement is worse than an absent one.
How Spreadsheet Design Affects Financial Review: What Can a Reviewer Find in an Hour?
The uncomfortable fact from the opening now has numbers attached to it. A reviewer has an hour. The file has 214 formulas and 31 inputs. Nobody works through that in an hour, and nobody claims to. In practice the reviewer opens perhaps twenty cells, and the twenty they open are chosen by whatever the file makes easy to reach. Layout does not change the chance that any cell is wrong; it changes the chance that an hour of somebody else's attention lands on the cells that could be.
Picture two versions of the same work with the same numbers in them. In the first, the nine chosen numbers are scattered through three working sheets, wherever each one was first needed, and there is no checks tab. A reviewer opening that file goes where the file invites them. The invitation is the neat output sheet at the front with the totals on it. The reviewer spends the hour on numbers that are almost certainly right, and leaves satisfied. In the second, the nine chosen numbers sit in one shaded block on the Inputs tab and the checks tab says what was run and what it found. The reviewer spends ten minutes on the nine and fifty minutes on the three of them that actually carry the answer.
Both reviewers worked the same hour with the same skill and the same care. One of them looked at the cells where an error would matter and one of them did not, and the only thing separating the two was how the file had been laid out. Layout is therefore worth being pedantic about even when the person building the file will never be reviewed by anybody. The person opening the file in six months is often the person who built it, and they will have forgotten just as thoroughly as a stranger.
A reviewer has one hour and the file holds 214 formulas. So what has the layout actually done for them?
How Does a Lender, an Auditor or Somebody Working Alone Use a File Laid Out This Way?
A credit officer at a lender does not read a workbook front to back, and would be doing it wrong if they did. A credit officer goes straight for the chosen numbers. Filed numbers are cheap to verify, and chosen numbers are where a file has been steered, whether deliberately or not. The first question against any file is which numbers here were chosen rather than found, and a file that answers that in one shaded block on one tab has just saved a credit officer an afternoon. The second question is which of the chosen numbers the conclusion cannot survive without. The file cannot answer that one, and that is exactly why the first has to be free.
An auditor uses the checks tab and uses it as evidence about the builder rather than about the numbers. A checks tab saying a formula check was run on a date and found eleven hardcodes is far more reassuring than one saying nothing was found. A tab reporting nothing reads as either a perfect file or an unrun check, and there is no way to tell which. A check that reports finding nothing carries less weight than a check that reports finding a normal amount. Only one of the two is evidence that the check happened.
An analyst inheriting the file uses the tab order and nothing else, at first. The new analyst opens the cover to learn what they are holding, the checks tab to learn whether it has been tested, and then the Inputs tab to see what the previous person had to choose. Three tabs and about four minutes, and they know whether to trust the file enough to build on it. Without the order, the same four minutes goes on working out which of eleven sheets is the current one.
Somebody working alone gets more from these conventions than a desk of four does, not less, and that is worth saying plainly because layout looks like something done for other people. There is no reviewer, so the layout is the reviewer. A person running the numbers for a household decision, a shop owner working out whether a second outlet pays for itself, a student building a case for a competition: each of them is going to reopen their own file after a gap and have no memory of which numbers were looked up and which were guessed. Putting the guessed ones in one shaded block with a note beside each, and running the two checks, hands the later self a file that answers questions instead of raising them.
References
| Source | Document | Where |
|---|---|---|
| Securities and Exchange Board of India | Record-keeping, conduct and disclosure duties applying to registered intermediaries | sebi.gov.in |
| Institute of Chartered Accountants of India | Documentation and engagement review standards, covering the requirement to document work in a form another person can follow and to review it | icai.org |
| International Organization of Securities Commissions | Published conduct principles that several national regimes draw on, covering cross-border expectations around working records | iosco.org |
The Kavery research desk, Kavery Capital Services Private Limited, Meenakshi Tubes Private Limited, Sharada Iyer, Prakash Nadar and Latha Menon are invented.
Educational material. Not advice on any investment, tax, budget or market position.