The Financial Model as a Deliverable: Structure and Output

A model becomes a deliverable when somebody other than its author can change an input and trust the answer. A deliverable needs three things: inputs gathered in one place and never typed inside a formula, working separated from output, and a checks section that fails loudly. The output is the small part a reader sees, and it never travels without its inputs.

Two files can hold identical arithmetic and only one of them is a deliverable. A working file is a thinking tool. A working file belongs to the person building it, it carries their shortcuts, and it is finished the moment they have their answer. A deliverable is an instrument somebody else picks up, usually on a day when the person who built it is unreachable and the question will not wait. Every convention that follows closes one specific way a file can be wrong without looking wrong. None of them is about tidiness.

What makes a model a deliverable rather than a working file?

One question settles it. Can somebody who did not build the file change one input and trust the number that comes back? A financial modelA file that computes an answer from inputs somebody can change. Move an input, and the answer moves with it. that passes that question is a deliverable. A file that fails it is a working file, and it stays a working file no matter how carefully it has been formatted.

Think about how a cook writes down a dish. For themselves, three words on the back of an envelope are enough. The cook already knows the quantities and knows which step is the one that goes wrong. Hand those three words to somebody else and the dish fails, and the cook has to stand next to them explaining. A recipe written for another person puts every quantity in one list at the top, in the order they are needed, and it says what the dish should look like at the halfway point so the cook can tell whether it is going well. The envelope and the recipe describe the same dish, and only the recipe can be handed over.

People usually offer the file's appearance instead. Consistent colour coding for inputs, a title tab, frozen panes, no stray notes in column Z. All of that is worth doing and none of it is the test. A file can be immaculate and still hide a rate inside a formula on the fourth tab, and a file can look plain and be perfectly safe to hand over. The test is about the next person and never about the file. A beautiful file can fail it and an unglamorous one can pass.

In the assignment behind this guide, the model was built by Sharada Iyer at the Kavery research desk inside Kavery Capital Services Private Limited, an invented firm, reviewed by Prakash Nadar, and used in a decision taken by Latha Menon on 12 March. Meenakshi Tubes Private Limited, the business the model describes, and every figure below belong to that constructed case. Three people touched one file, so two of them had to be able to open it, move something and believe the result without Sharada Iyer sitting beside them. The requirement that two colleagues could work the file alone, and nothing more elevated than that, produced every rule that follows.

Where does every number the model uses actually live?

In one place, on one tab, and nowhere else. The Inputs tabThe single tab where every raw number in a file is typed, each one tied to the document or the assumption it came from. in the workbook behind this assignment holds 31 numbers. Twenty two of them were filed by somebody, so each one traces back to a document and a date. Nine of them were chosen by the desk, and those nine are exactly the nine rows of the assumption register that the earlier stage of this work produced. Every other cell in the file, on every other tab, points back at one of those 31 cells.

The reason is completely practical. An assumption has to be found before it can be changed, and a number typed in seven places is a number that will be changed in six. A household does the same thing without calling it anything: the sheet stuck to the fridge with the meter reading, the due dates and the rates on it, rather than figures written on whatever envelope was nearest at the time. When the rate changes, the fridge is where anybody looks. One place to change means one edit, and one edit means the whole file moves together or not at all.

The one-place rule generates most of the rest. The second formula convention below says exactly that: if every raw number is on the Inputs tab, then no working cell may type one. If every input carries a source or a register row, then the reviewer's question is answerable without a conversation. And if the nine chosen numbers are visibly separated from the twenty two filed ones, a reader can see at a glance how much of the answer rests on judgement rather than on documents.

How is a model laid out so somebody else can use it?

In four zones, kept apart on purpose: inputs, working, output and checks. The discipline is that a cell belongs to exactly one of them. A cell that both holds a raw number and does arithmetic on it belongs to two zones at once, and the file has quietly stopped being traceable at that cell.

Inputs hold the 31 numbers and do no arithmetic. Working does every calculation and holds no raw number at all. Output holds the small result a reader is meant to read. And the checks sectionCells whose only job is to go wrong visibly when something in the file is wrong, so an error announces itself instead of waiting to be found. holds cells that exist for no purpose other than to break loudly when something else has broken. Nothing on the checks tab feeds the answer. A section that feeds nothing is the one section that can be trusted to test everything else.

The separation is not a filing preference. Separating the zones is what makes a change traceable in one direction. When Prakash Nadar wants to know why the answer moved between Tuesday and Thursday, four zones let him walk backwards from the output to the working to the one input that changed. A file with the zones mixed together can only be re-read, never traced, and re-reading a file is how a reviewer ends up rebuilding it.

What Is the Output, and Why Is It the Smallest Part of the File?

The output tabThe small, separated part of a file that a reader is actually meant to read. Everything that produced it lives elsewhere. is the part of a model somebody is meant to read, and in a well built file it is the smallest of the four zones. In the assignment behind this guide, the whole output is one figure: the operating margin under the scenario the desk was asked to test, 5.0 per cent when 14 per cent of revenue is treated as non-repeating, against 10.8 per cent when nothing is removed. One figure is the entire result. Everything else in the file exists to produce it or to test it.

A margin computed under an assumption describes the assumption, not the business behind it. Where such a figure sits in a file is a question about structure, and the meaning of the figure itself belongs to a different subject.

Why so small? Because an output tab that also shows the working has handed the reader a sorting job. A reader who has to decide which of eleven numbers on the screen is the answer is doing the work the file was built to do for them. The output is small because the whole point of a model is to reduce a great deal of arithmetic to the one figure the reader asked for, and a large output tab has undone that reduction. The working is not hidden. The working is one click away, in a zone built to be walked through.

The output tab is not a bare figure floating on a white tab. Five lines stand beside the number: the name of the measure and the period it covers, the share removed to get it, the assumed cost split behind it, the two filed figures it starts from, and the label saying this is a constructed case. Putting the five lines inside the same block as the figure matters more than it sounds like it should, and the failure block below is entirely about why.

Which formula conventions actually stop a file being wrong without looking wrong?

Four of them, and each one closes a different way of being invisibly wrong. The four conventions are not style preferences and not a matter of elegance. Each convention exists because a specific class of error is undetectable by reading the screen, and the convention makes that error visible or impossible.

Convention one: one row, one calculation, carried across

A row in the working zone does one thing, and it does that same one thing in every column it spans. Any cell in the row shows the same formula, pointing at the column above it. The moment one cell in a row does something the others do not, the row has stopped being a rule and become a set of special cases, and nobody reading the values can tell.

Here is what that looks like with the assignment's own quarters. The current year splits as Rs 4,80,00,000, Rs 5,20,00,000, Rs 6,40,00,000 and Rs 4,80,00,000, summing to the filed Rs 21,20,00,000. Two rows can be built to strip out the non-repeating share, and both of them total Rs 18,23,20,000. One applies the same calculation to all four quarters. The December order sits in the third quarter, so the other row leaves three quarters alone and subtracts Rs 2,96,80,000 from the third by hand. The totals agree to the rupee. Agreement is precisely why nobody notices that only one of the two rows will still be true tomorrow.

Which of the two treatments is better finance belongs to the subject the model is about rather than to its structure. The convention is narrower than that and holds either way: if a quarter genuinely needs different treatment, it gets its own labelled row saying so, rather than an exception typed silently inside a row that claims to be doing one thing.

Convention two: no number typed inside a formula, ever

A hardcodeA number typed inside a formula instead of referenced from an input cell. It computes correctly and cannot be seen without clicking the cell. is a number sitting inside a calculation rather than pointing at the Inputs tab. A hardcode is the fault that hides best in any file, and the reason is worth stating plainly: a hardcode is completely invisible in the rendered sheet. The cell shows a value. The value is correct. Nothing is coloured differently, nothing is flagged, and no amount of careful reading of the screen will find it. The cell has to be clicked.

Take the working row that computes variable cost after the scenario. Written properly it reads as the variable cost input multiplied by one minus the share input, and it returns Rs 9,75,24,000. Written with the share typed in, it reads as the variable cost input multiplied by 0.86, and it returns Rs 9,75,24,000. The two cells are indistinguishable, today.

Tomorrow they are not. Move the non-repeating share from 14 per cent to 20 per cent and the correct cell falls to Rs 9,07,20,000 while the hardcoded cell stays at Rs 9,75,24,000, a gap of Rs 68,04,000. At that point the file reports an operating loss of Rs 35,24,000 where the correct working gives a profit of Rs 32,80,000, and the sign of the answer has flipped without a single error message. Nobody typed a wrong number. Somebody typed a right number in the wrong place, eleven weeks earlier.

Convention three: sign discipline, so a total is a sum rather than a puzzle

Sign disciplineKeeping costs consistently signed through a file, so that a total can be a plain sum rather than a mixture of additions and subtractions. means picking one convention for costs and holding it everywhere. Either every cost is entered as a negative number and the total is a plain sum, or every cost is entered positive and the total subtracts them. Both work. Mixing them also works, right up until somebody adds a row.

Look at what the mixed version costs. Two columns can both reach an operating profit of Rs 2,30,00,000 from revenue of Rs 21,20,00,000, variable cost of Rs 11,34,00,000 and fixed cost of Rs 7,56,00,000. The consistent one sums three cells. One cost was entered positive and the other negative, so the mixed column subtracts one cell and adds another. The mixed column is right today and unreviewable forever. A reader cannot confirm it by inspection, and the next person adding a cost line has a coin flip to get the sign right.

Convention four: a checks section that fails loudly rather than quietly

A check is a cell whose only job is to go wrong visibly. The check computes something whose answer is already known, compares the two, and shows something unmissable when they disagree. A check feeds nothing. If a check ever contributes to the output, it has stopped being a check and become part of the thing it was supposed to test.

The strongest check available in any model is a case whose answer is already known from somewhere else. In this assignment, that case is the scenario switched off. With the non-repeating share set to zero, nothing is removed and the model is simply recomputing a figure that was already published. The model must return the filed operating margin of 10.8 per cent, and it does. A wrong reference, a broken sign, a stranded hardcode or a mis-sized denominator will nearly all show up at the one place where the answer is already known. One point catches almost every structural error a file of this size can hold.

The check is one point on a line the model can trace all the way across. At a share of zero it gives 10.8 per cent. At the 14 per cent the desk was asked to test it gives 5.0 per cent. The line keeps falling because the fixed cost of Rs 7,56,00,000 does not fall with revenue, and somewhere past a fifth of revenue the margin reaches zero. All of those points come out of the same four zones with one input moved. Moving one input and getting a whole line is the idea of a model, and the check is simply the one point on that line where an outside answer exists to compare against.

Two terms are routinely confused, and the distinction belongs here. A formula check asks whether a cell does what it claims to do. A reconciliation asks whether two independently built totals agree. In the workbook behind this guide, the reconciliation sets revenue from the filing, Rs 21,20,00,000, against the sum of the four quarters. The four quarters also come to Rs 21,20,00,000. The two totals agree to the rupee. Different routes and different documents produced the same number, so the agreement means something a formula check never can.

What did the model in this assignment actually contain?

Very little, and that is the point worth taking from it. The model computes one figure under one scenario, using four inputs the desk could name and defend. The model is laid out below zone by zone, showing where each number sits rather than what any of it means. The arithmetic is shown because a model with its arithmetic hidden teaches nothing about structure; the measure itself belongs to a different subject entirely.

Read the working zone in both directions and it holds. Forwards: Rs 21,20,00,000 less Rs 2,96,80,000 of non-repeating revenue is Rs 18,23,20,000, and Rs 11,34,00,000 of variable cost falls in the same proportion to Rs 9,75,24,000, and Rs 7,56,00,000 of fixed cost does not move, leaving Rs 91,96,000. Backwards, with nothing removed: Rs 21,20,00,000 less Rs 11,34,00,000 less Rs 7,56,00,000 is Rs 2,30,00,000. The filed figure is the check. The two directions have to close for the file to be handed to anybody, and closing them takes about four minutes once the zones are separated.

Notice how much is not in the file. There is no forecast, no second year, no set of drivers, no valuation. The model computes one already published figure under one changed assumption. The question the desk had been asked needed nothing more, and the model was built that way deliberately. A model earns its complexity from the question. A model does not earn complexity from the effort somebody wanted to show.

How does a model result leave the file without overstating itself?

By travelling with the inputs that produced it, in the same object. Not in an attachment, not in a link back to the file, not in a footnote at the bottom of a screen the reader will scroll past. Copy and paste is exactly what will happen to the result, so the result and the conditions have to be one thing an ordinary copy and paste cannot separate.

In sentence form, the result out of this model reads as one line: under an assumption that 14 per cent of revenue does not repeat, and an assumed split of costs into 60 per cent variable and 40 per cent fixed, the operating margin of this constructed case falls from 10.8 per cent to 5.0 per cent. The full line is longer than 5.0 per cent and it is the shortest version that is still true. The plain language version carries the same content for a reader who does not work in numbers all day: for every Rs 100/- of sales, about Rs 5/- is left after running costs under that assumption, against about Rs 10.80 with nothing removed.

A result that cannot survive being quoted alone should be written so that it never is alone. Putting the conditions in the same block is a formatting rule, and formatting rules get followed. A rule that asks people to remember to add context on a Friday afternoon does not get followed.

What happens when a correct result travels on its own?

Financial Model vs Financial Workbook: where do the two part?

The two words get swapped freely, and the swap has a cost at review time. Define both before comparing them.

A financial model is a file that computes an answer from inputs somebody can change. Its defining feature is responsiveness: move an input and the answer moves, correctly, without anybody editing a formula. Everything above this heading describes a model. Its four zones, its formula conventions and its checks all exist to protect that one property.

A financial workbookA file that holds and organises the work an assignment produced. A model may be one part of it, alongside the source log, the working and the record. is a file that holds and organises the work an assignment produced. The one behind this guide has six tabs in a fixed order: Cover, Inputs, Source log, Working, Output, Checks. The workbook carries 214 formulas and 31 input numbers. Its defining feature is not responsiveness but traceability: any figure anywhere in it can be walked back to the document or the assumption it came from.

So the model in this assignment is a part of the workbook rather than a rival to it. The Inputs, Working, Output and Checks tabs are where the model lives; the Cover and the Source log are workbook furniture that no model needs. The two are reviewed with different questions, and the practical damage of confusing them is that somebody runs the wrong review: they check that the file can be followed when what mattered was whether it still computes when an input moves, or the reverse.

How does a lender, an analyst or somebody working alone use any of this?

Watch a lender open a model that somebody else has sent and the first thing they do is not read it. The lender moves something. The lender finds the input the whole answer turns on, pushes it in the direction that hurts, and watches what happens to the output. A file that responds sensibly to that has told them more in fifteen seconds than the covering note will in fifteen minutes. A file that does not respond at all, with the assumption typed inside a formula, has told them something too, and it is not good.

An analyst reviewing somebody else's model starts from the opposite end, and reviewing is what Prakash Nadar was doing in the constructed assignment. The first move is to find a case whose answer is already known and make the model produce it. If the file cannot reproduce a figure that exists outside it, nothing else in the review matters yet. The second move is to sample formulas looking for typed numbers, and to expect to find some: about eleven in two hundred and fourteen is the ordinary rate, so a review that finds none has usually not looked.

Somebody working entirely alone, with no reviewer and no committee, gets more out of these conventions than anybody, not less. The builder is the next person. Three months on, the same file opens with no memory of why the third row does something different, and every convention here is a message left for that later version of the builder. The household version is the loan repayment sheet where somebody typed the interest rate into one cell and referenced it in another. When the rate resets, half the sheet updates and half of it silently does not, and the person who built it is the one who gets caught.

An investor or a committee member who never opens the file still depends on all of it. The output sentence is the only part that reaches them. The zones, the conventions and the checks all serve one thing: whether the number that leaves the file arrives somewhere else still meaning what it meant when it left.

Kavery Capital Services Private Limited, the Kavery research desk, 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.