Audit Trail: Making Analytical Work Reproducible

An audit trail for analytical work is the record that lets a second person rebuild the conclusion from the same inputs by the same steps and get the same number. The trail captures the inputs, the steps, the versions and every manual override. The test is reproducibility, not tidiness: a beautiful model that cannot be rebuilt has no trail. With a trail, a review becomes a check instead of an argument.

A conclusion nobody can rebuild can only be believed or disbelieved. A conclusion anyone can rebuild can be checked. The audit trailThe record kept alongside an analysis showing where every number came from and how it was turned into the conclusion, so that someone else can retrace the work. is the difference between the two, and it is a smaller, humbler thing than the name suggests: not a document written afterwards to look careful, but the residue of working in a way that leaves footprints. Ishaan Verma's model for the Kaveri Cold Chain loan, an invented case, faces the only question that matters twelve months on: could a second person, sitting down with what Ishaan left behind, arrive at his forecast of Rs 4,20,00,000 of profit before tax without asking him anything? Answering that means knowing what the trail must capture, why a neat model can still fail the test, where trails actually break, and how much of Ishaan's number a reviewer really could rebuild.

What is an audit trail for analytical work, and what is its one test?

Start with a recipe. A grandmother's dal is famous in the household, and when a cousin asks how it is made the answer is 'a pinch of this, cook until it looks right'. The cousin cannot make that dal. Not because the recipe is wrong, but because nothing about it can be followed by a second pair of hands. Now take the same dal written as quantities, order and timings, with a note that the tamarind was reduced last winter because the batch was sour. The cousin makes it, tastes it, and either matches the original or knows exactly which step went differently. The second recipe has a trail. The first has only a result and a reputation.

An audit trail is any record that lets a second person rebuild the conclusion from the same inputs by the same steps and match the number, and that rebuild-and-match is its one test. Notice what the test does not ask. The test does not ask whether the analyst is trustworthy, whether the model is well laid out, or whether the conclusion turned out right. The test asks only whether the path from inputs to number can be walked again by someone who was not there. If the walk ends at the same figure, the work is reproducibleAble to be done again by a different person from the same starting materials and get the same result. Borrowed from laboratory science, where a result nobody else can repeat does not count.. If the walk hits a point where the only way forward is to ask the original analyst, the trail ends there, whatever else is written down.

For Ishaan Verma's Kaveri Cold Chain forecast, the walk starts at five recorded inputs, current occupancy of 62 per cent, a signed pharma contract worth 14 points of capacity, a further 4 points of spillover, a tariff of Rs 1,150 per pallet per month held flat, and power cost of Rs 1,80,00,000 held flat. The walk passes through the arithmetic that turns occupancy into contribution and contribution into profit before tax, and it ends at Rs 4,20,00,000. A reviewerThe second person who checks an analysis: a colleague, a committee member, a risk officer or the analyst's own successor a year later. Not the person who built it. walking that path a year later is doing the test, whether or not anyone calls it that.

What must the trail capture: inputs, steps, versions, overrides?

Think about a wedding budget kept by one uncle. Months later a cousin is asked to check whether the caterer was overpaid. The cousin needs four things: the quotes the uncle started from, the arithmetic he did with them, which draft of the budget was the one everybody agreed to, and every place a phone call about a discount made him change a number by hand. Miss any one of the four and the cousin is guessing.

The trail must capture four things, and an empty box in any one of them is a broken trail, not a slightly weaker one. The inputs are the starting values with their provenanceWhere a number came from: the document, person or measurement that produced it, and the date. A figure without provenance cannot be checked against anything., which is what an assumption register carries and is covered separately. The steps are how the inputs were turned into the conclusion, the formulas and the order they run in, kept as formulas rather than pasted results. The versionsSuccessive saved states of the same work, each dated, so that anyone can see what the analysis said on a given day and what changed between one save and the next. are the dated saves of the work, so a reviewer can tell which state of the model the decision actually rested on. And the overrides are every place a person typed a number where a formula or a linked input would otherwise have been, together with a note saying why. Four boxes. Ishaan Verma's model filled the first and most of the third, and left the other two partly empty.

Why is reproducibility the test rather than tidiness?

Here is the mistake almost everyone makes on first meeting this idea: they hear audit trail and picture a neat model. Colour-coded inputs, labelled tabs, a cover sheet, no stray cells. Neat formatting is pleasant, and it helps a reviewer find their way around. But a street vendor's tally, scrawled in pencil on the back of a carton, can be perfectly reproducible if it lists what was bought, at what price, what was sold and at what price, so that anyone can redo the day's profit. And a beautifully formatted model can be entirely unreproducible if its headline cell was typed in on the last night.

Reproducibility is the test because the reviewer's question is 'can I get there too', and tidiness answers a different question, 'is this pleasant to read'. A model can pass one and fail the other in either direction. Appearance buys trust that the contents have not earned, so the tidy-but-unrebuildable model is the dangerous one. The reviewer sees the colour code, assumes the discipline behind it, and does not push on the number. A reviewer who pushes on the messy-but-rebuildable model, by contrast, finds solid ground. The messy model earns less trust than it deserves, and the shortfall is a cost too, but a smaller one. Ishaan Verma's version 3 was tidy. Every input cell was shaded, the register sat on its own tab, and the tab names carried dates. Version 3 still could not be fully rebuilt, for three ordinary reasons.

Where do trails break in practice?

Trails rarely break because someone decided not to keep one. Trails break in three small, ordinary ways, usually under time pressure, and each one looks harmless at the moment it happens. The first is the hard-codedA number typed directly into a cell or line of a model in place of a formula or a link to a source, so that it no longer changes when its inputs change and carries no record of where it came from. cell: a formula replaced by the number it used to produce, or a number typed where a link to the register should be. The second is the silent overrideA deliberate manual change to a value the model would otherwise have calculated or pulled from a source. Legitimate when noted with a reason; a break in the trail when not.: a value changed by hand for a reason that seemed obvious that evening and is unrecoverable a year later. The third is the undated version: a copy saved as 'final' or 'committee' with no date and no change logA short list kept with each version saying what was changed since the previous one and why. A change log lets a reviewer follow the work forward in time rather than compare two files cell by cell., so nobody can say which state of the model the decision was taken on or what moved between two saves.

Every break has the same shape: a point where the record states the value but not its origin, so the reviewer can copy the number but cannot regenerate it. Look at how the three appear on Ishaan Verma's version 3, drawn below. The tariff cell reads Rs 1,150 and is a link to the register row, so a reviewer can follow it back to the rate card: rebuildable. The spillover cell reads 4 and is a typed value with no note; version 2 of the same cell held a formula, six points of enquiries in the sales log times a 50 per cent conversion, giving 3. The power cell reads Rs 1,80,00,000 typed; in version 2 it was last year's actual times 1.20 for the colder pharma chamber, Rs 2,16,00,000, and the change log for version 3 is blank. And a tab called 'final' sits after version 3 with no date on it at all. Same sheet, same fortnight, three breaks.

How does a trail change what a review can do?

Picture two households arguing about a month's spending. In the first there is no record; one person says the money went on repairs and the other says it went on eating out, and the argument is settled by whoever is louder or more senior. In the second there is a ledger, and both can add it up. The two still disagree about whether the repairs were worth it, but nobody disputes what was spent. The ledger did not end the disagreement. The ledger moved it to the place where a disagreement can be useful.

With a trail, a review is a check: the reviewer rebuilds, matches or does not, and any gap is located to a specific cell, step or version, so the conversation is about that thing rather than about who is more credible. Without a trail, a review is an argument. Neither side can point to where the other's number comes from. The reviewer can say '80 per cent occupancy feels optimistic', the analyst can say 'I stand by it', and there the matter rests, decided by seniority or mood. A located gap is why the trail matters more to a junior analyst than to anyone else: a located gap is the only thing that lets a correct junior number survive contact with a sceptical senior. Twelve months on, when Kaveri Cold Chain's occupancy came in at 71 per cent and profit before tax at Rs 1,10,00,000, the reviewer's job was to work out how much of the miss was the world and how much was the model. Splitting the miss is a check if the trail holds and an argument if it does not.

Could a reviewer rebuild Ishaan Verma's Kaveri number?

Now do the walk. A reviewer opens version 3 twelve months on. Here is what the trail supplies. Kaveri Cold Chain has capacity for 20,000 pallets, so each point of occupancy is 200 pallets, and at Rs 1,150 per pallet-month less Rs 150 of variable handling that is Rs 1,000 of contribution per pallet-month, or Rs 24,00,000 per occupancy point per year. At 62 per cent the model's baseline sits at break-even, so every added point falls almost straight to profit. The pharma contract's 14 points give Rs 3,36,00,000, less Rs 12,00,000 for the dedicated compliance staff the contract requires: Rs 3,24,00,000, every cell of it linked to the register and every formula intact. The spillover formula that survives in dated version 2 gives 3 points, another Rs 72,00,000. And version 2's power formula still charges Rs 36,00,000 for the colder chamber. Following the recorded inputs by the recorded steps, the reviewer arrives at Rs 3,60,00,000.

The reviewer can rebuild Rs 3,60,00,000 of Ishaan Verma's Rs 4,20,00,000 from the recorded inputs and steps, and the last Rs 60,00,000 exists only as two typed numbers with no note. The typed 4 in the spillover cell adds a fourth point worth Rs 24,00,000 that no recorded step produces. The typed Rs 1,80,00,000 in the power cell removes the Rs 36,00,000 uplift with no change log to say why. Perhaps both changes were right: two more enquiries came in on the 12th, and the engineer said the chamber load was already in last year's bill. Perhaps neither was. The point is that the trail cannot say, so the reviewer can check Rs 3,60,00,000 and can only believe or disbelieve Rs 60,00,000. Partial trail, partial check.

How do lenders, analysts and investors actually use a trail?

A lender's credit team uses the trail at renewal. When the Kaveri Cold Chain loan comes up for review a year on, the officer who inherits the file was not in the January room. Only a model that can be re-run with the actual inputs dropped in lets that officer say 'the forecast missed by Rs 3,10,00,000 and here is how much of that was the competitor, how much the power tariff and how much the spillover that never came'. Without a trail nothing more can be shown, so the renewal memo says 'performance below forecast' and stops.

An analyst uses it to survive being wrong. Every analyst is wrong regularly; the ones who last are the ones whose wrongness can be examined. Practitioners keep a trail less to prove they were right than to make their errors legible, because a legible error is a lesson and an illegible one is just a loss. The regulatory frame points the same way. The Securities and Exchange Board of India (SEBI) regulations for research analysts require the basis of a recommendation to be recorded and kept, which is a trail requirement in all but name, and the regulation itself fixes how long the record must be kept.

A household investor meets the idea in a smaller form. Keep the receipts, the bank statement and the working for a tax return in one folder, and a query two years later can be answered by rebuilding the return rather than remembering it. The folder is an audit trail. The habit is the same at every scale: work in a way that leaves the path behind, because the path will not be remembered and the person asking may be someone else.

Kaveri Cold Chain Private Limited and Ishaan Verma are invented.
Educational material. Not advice on any investment, tax, budget or market position.