Using Excel for Business Analysis A Guide to Financial Modelling Fundamentals

1. 1Plan the design before you open a formula. Spend time on layout, structure and a project plan first, because even a simple model becomes complex if poorly designed. The target is a model so logically laid out that it speaks for itself.
2. 2Document every assumption where the model uses it. Take model input data at face value only if you record its source in the assumptions. Where the data matters, run it through an internal QA first and tell the data provider in advance.
3. 3Separate inputs from calculations and outputs. Put each input in one place and enter it once. Everything downstream links to that cell and never retypes the number. A model is defined as a structure with inputs and outputs that is flexible and dynamic, so changing an input must flow through to the outputs without manual edits.
4. 4Build the same formula across a whole row. Keep formulas as simple as you can, because mastering the logic and following best practice matter more than clever formulas. Do not apply best practice only to 'real' financial models: scorecards and reports built in Excel should still follow linking and assumptions documentation.
5. 5Format and label clearly so another person can follow the model. The book's Chapter 3 outline lists 'Format and Label Clearly' and 'Avoid Bad Habits' as separate principles from consistent formulas and linking, so treat them as their own checks.
6. 6Feed data in through designed channels. Use import and export sheets or data-collection sheets that go into the model by copy-paste or links, with no manual data entry. Protect those sheets, because an inserted or deleted row, column or formula can be disastrous.
7. 7Build error checks and sense-checks into the model. Test that outputs behave sensibly when inputs change. For example, does the balance sheet still balance after you change inputs? Do rough mental estimates of key outputs to catch magnitude errors.
8. 8Control versions. Issue a new version only for structural changes to design or new calculations, not for changes to input values. Keep a version log. When you audit a model, use Show Formulas (CTRL+`) to see the logic and anomalies, and disable direct editing so you cannot overwrite formulas by accident.

The passages give a sense-check case. If you sell 450 units at $800 each, revenue should be $360,000, not $3.6 million. Apply it to a model built to best practice. The 450 and the $800 each sit once in an inputs area with their source noted, and the revenue line is a single formula (units × price) copied across every period. The output shows $3.6 million. Because the inputs are entered once and the formula is consistent, you can trace the error to one place instead of searching for retyped numbers. Show Formulas (CTRL+`) displays the whole sheet's logic at once, and an off-pattern cell stands out. The numerate modeller's rough estimate of $360,000 flags the problem before anyone else sees it. A second test comes from the accountant example in the passages: change an input and check that the balance sheet still balances. If it does not, the statements are laid out correctly but not linked properly.

• If a number appears in more than one cell, then keep one input cell and link every other use to it.
• If an input comes from another party, then document its source in the assumptions. If completeness or accuracy matters, run it through internal QA and tell the provider beforehand.
• If a formula is getting long or complicated, then simplify it. The passages say to keep formulas as simple as possible, since logic and best practice matter more than complex formulas.
• If the change alters structure or adds calculations, then issue a new model version. If it only changes input values, then it is not a new version.
• If you are auditing an inherited file, then disable direct editing and use Show Formulas before you change anything. If third parties will enter data, then protect the worksheets.

• Hard-coding numbers into formulas or retyping inputs. The tell: changing an assumption leaves some outputs unchanged, or the same figure differs between sheets.
• Inconsistent formulas across a row. The tell: one cell looks different under Show Formulas, or a total quietly misstates results.
• Undocumented inputs. The tell: nobody can say where a figure came from or why it was chosen when asked.
• Statements that look right but are not linked. The tell: you change an input and the balance sheet no longer balances.
• Overcomplicated formulas or layout. The tell: the model cannot be followed without its builder, and inherited files are slow and unwieldy.
• Unprotected data-entry areas and unmanaged versions. The tell: rows or formulas get inserted or deleted during pasting, and no one can say whether an input change or a structural change produced the new numbers.

• ▸Model name and version: record the version, and note whether the last change was structural or input-only.
• ▸Assumptions documented: list each key input with its source, and mark any without a documented source.
• ▸Inputs entered once: check that each input lives in one cell and name any duplicates found.
• ▸Hard-codes found: list cells where numbers are typed inside formulas (Show Formulas, CTRL+`).
• ▸Formula consistency: note any row or column where the formula pattern breaks.
• ▸Layout and labelling: confirm inputs, calculations and outputs are separated and clearly labelled.
• ▸Error and sense-checks: record the balance check and one rough-estimate check on a key output, with pass/fail.
• ▸Data-entry protection: state which sheets are protected and how external data enters the model.
• ▸Reviewer sign-off and issues log: record the reviewer, the date and open issues.

1. 1State the task in a plain sentence before you touch a formula. Example: "Show me the value, but only if the test cell is greater than or equal to today's date." Excel technique is a translation of that sentence, so write it first.
2. 2List the candidate tools from the toolkit the source treats as must-know: aggregation functions, the LOOKUP family, nested INDEX and MATCH, OFFSET, CHOOSE, named ranges, and the scenario and sensitivity methods. Name at least two candidates for the task rather than defaulting to the one you know best.
3. 3Pick the simplest candidate that completes the task, applying "as simple as possible and as complex as necessary." The source says many of the best models use only core functionality. Choose complexity only when the task demands it, not because it is all you know or because it shows skill.
4. 4Build the formula so the logic stays readable. Use named ranges and consistent cell referencing, link to assumptions instead of hard coding, and keep formulas short. If a nested formula is hard to follow, split it or rethink the layout.
5. 5Run a magnitude check on the result. Ask whether 450 units at $800 gives $3.6 million or $360,000. Then stress the logic, such as timing: 30,000 customers a year cannot all earn revenue from day one of Year 1.
6. 6Add an error check that tests the technique's output against an independent figure. The source gives error avoidance and error-check building their own chapter (Chapter 4) because Excel errors are silent.
7. 7For sensitivity and scenario work, review the several methods in Chapter 11 and choose the one whose output and set-up cost fit the question. The source calls this "basically the whole point of financial modelling."
8. 8Check the environment before you ship. Confirm the Excel version, and confirm any tool you build works on both 32-bit and 64-bit Excel. Move to 64-bit only if you are a data-hungry Power Pivot user generating files over 2GB.

Task: show a value only if a test cell falls in the future. First vocalise it: "Show me the value, but only if the test cell is greater than or equal to today's date." The translation is =IF(test_cell>=today,value,0). The IF is the simplest tool that does the job, so no nesting or lookup is needed. Next, apply the magnitude check the source recommends: if 450 units sell at $800 each, revenue must be $360,000, not $3.6 million. A modeller who is comfortable with numbers spots a slip of that size at once. Then test the logic, not just the syntax. A new insurance product acquiring 30,000 customers a year cannot earn revenue on all 30,000 for the full year, because customers arrive gradually. The formula must use an average, otherwise revenue is overstated by a substantial amount. The correct function with wrong logic is still a wrong model.

• If a core function (IF, a basic lookup, simple aggregation) completes the task, use it. Reserve advanced or nested constructs for cases where the simple route cannot do the job.
• If you cannot say what the formula does in one plain sentence, fix the logic or the layout before refining the formula.
• If you are building a tool for other people, make it work on both 32-bit and 64-bit Excel. Adoption matters more than using the newest capability.
• If a technique's result cannot be sanity-checked by a quick mental calculation or an independent check, add an error check before relying on it.
• If the task is scenario or sensitivity analysis, compare the available methods (Chapter 11) and choose by the question being asked, not by the method you know best.

• Complexity as showmanship: nested, long formulas where a simple tool would do. The tell is that colleagues cannot follow the formula, and you built it that way because it was all you knew.
• Right function, wrong logic: the formula calculates correctly but the assumption is wrong, such as full-year revenue for customers acquired gradually. The tell is output that looks plausible but is substantially overstated.
• Skipping the sanity check: no one notices a $3.6 million versus $360,000 error. The tell is a result nobody compared against a rough mental estimate.
• Treating Excel skill as the whole job: polishing formulas while neglecting error-checking, stress-testing and best-practice structure. The tell is an advanced-looking model with no checks.
• Ignoring the environment: building a solution that only works on 64-bit Excel or in code that is not compatible. The tell is that other users cannot open or run the tool.

• ▸Task in plain words: write the analytical need as a spoken sentence (e.g., "show the value only if the date is in the future").
• ▸Candidate tools (2+): list the Excel functions or features that could do it (IF, lookup, INDEX/MATCH, OFFSET, CHOOSE, named range, sensitivity method).
• ▸Chosen tool and why: name the simplest candidate that works and give the reason in one line.
• ▸Formula or set-up: record the formula or feature settings, using named ranges and links rather than hard-coded values.
• ▸Assumptions linked: list the input cells the technique depends on and confirm none are hard coded.
• ▸Magnitude check: write a rough mental estimate (e.g., 450 × $800 = $360,000) and whether the result matches.
• ▸Logic/timing check: note any timing or averaging issue (e.g., customers acquired gradually) and how the formula handles it.
• ▸Error check added: describe the independent check and where it lives in the model.
• ▸Compatibility check: record the Excel version and whether it works on 32-bit and 64-bit.

1. 1Define the model's objective before opening Excel. State the decision the output supports, who uses it, and what inputs and calculations that requires. Then confirm Excel is the right tool: the passages say some solutions are better built in other software or with add-ins, and Power Pivot raises the data-size limit for Excel 2010 and above.
2. 2Plan the design before building. Sketch the layout and flow chart, decide the workbook anatomy, and set a project plan with a timeline. A well-designed model is 'so straightforwardly logical that it will simply speak for itself'; even a simple model becomes complex if poorly designed.
3. 3Build assumptions for flexibility from the start. A single growth assumption restricts you to one rate (Layout Option 1 in the 5-year forecast example: 15,065 customers in 2017, growing 5 percent a year). Give each period its own assumption cell so scenarios can vary it.
4. 4Apply the best-practice rules while building: document every assumption, link rather than hard code, enter each data item once, use consistent formulas across a row, and label units and currency clearly. Keep columns consistent across sheets (for example, column F is 2017 on every sheet).
5. 5Test continuously during the build, not only at the end. Add error checks, as the passages' Chapter 4 prescribes. Stress-test inputs with a wide range of values to check the model's inner workings.
6. 6Run a peer draft review before formal QA. Walk through the calculations at a high level and ask four questions: do the assumptions and source data clearly drive the output, does the model solve the stated problem, is the flow easy to follow, and does the end user understand and accept it. Treat this as your last chance to find formula or logic errors.
7. 7Add sensitivities and scenarios only after the base model works. Test the inputs against the finished base model to decide what the scenario analysis should show.
8. 8Submit for formal QA only when you are confident the model is mistake-free. QA is a formal check, not a debugging session, and finding formula or logic errors at this stage raises questions about the modeller's competence.
9. 9Control incoming data. Design information requests so data pastes or links straight into the model with no manual entry. State the data-quality expectations, allow time to check what comes back, and expect that people may insert or delete rows, columns, or formulas in the templates.

The source's 5-year forecast starts with 15,065 customers in 2017 and an expected 5 percent annual increase. Layout Option 1 holds one growth assumption, so every year is forced to grow at the same rate. That layout is rigid and cannot show a scenario where growth differs by year. The fix is to give each year its own growth input and have the customer line link to those inputs. The 5 percent is entered once as a labelled assumption, and no growth figure is typed into a formula. Later, scenario analysis can change any year's rate and every dependent output moves with it. The peer review then checks the model against its four questions, and formal QA stress-tests the growth inputs with a wide range of values to confirm the calculations hold.

• If the model uses one assumption cell for an input that could vary by period, then split it into per-period inputs before building scenarios.
• If a number appears in a formula as a typed constant, then move it to a documented input cell and link to it.
• If you find a formula or logic error at formal QA, then your build-stage testing was inadequate. Send the model back to the build and testing steps before resubmitting.
• If input data comes from other parties, then provide a template that pastes or links directly into the model and subject the data to a QA check. Warn the provider in advance.
• If the output feels unclear or the layout hard to follow, then fix the design rather than adding documentation. A well-designed model speaks for itself.

• Diving straight into building without a design or plan. The tell: the model grows complex, the flow is hard to follow, and structural changes are needed late.
• Hard-coding a single assumption, such as one fixed growth rate. The tell: you cannot run a scenario without rewriting formulas.
• Treating formal QA as the first real test. The tell: QA turns up formula or logic errors, which the passages say should not happen at that stage.
• Taking supplied input data at face value. The tell: the source is documented but the data is never checked for completeness, accuracy, and consistency.
• Letting data providers alter the template structure. The tell: inserted or deleted rows, columns, or formulas break links, and manual data entry creeps back in.

• ▸Model objective and decision supported: state the decision and the user in one line.
• ▸Tool choice rationale: why Excel (or another tool or add-in) fits the data volume and complexity.
• ▸Assumptions documented (Y/N): confirm every input is listed with its source.
• ▸Hard-coded values found: list any typed constants in formulas and where each was moved to an input cell.
• ▸Formula consistency check: note rows or columns where a formula differs, and the fix.
• ▸Error checks built: list the checks in the model and their pass/fail status.
• ▸Stress-test inputs and results: record the range of input values tested and what happened to the outputs.
• ▸Peer review answers: do the assumptions drive the output, does the model solve the problem, is the flow easy to follow, and does the end user accept it?
• ▸Scenario and sensitivity readiness: can each key input vary by period without changing formulas?
• ▸QA sign-off: reviewer, date, and errors found (should be zero).

1. 1Write the decision before you build anything. The passages state that a model's objective depends on the specific decisions to be made from its output, so name the decision (go or no-go on a project, how much to bid on a tender, whether to enter a joint venture) and the person who will make it.
2. 2Derive the outputs from that decision. The output required depends on who will use the model and which decisions they face, so list the exact output figures the decision-maker needs and cut everything else.
3. 3Build the model as inputs plus formulas that produce outputs, with cells linked rather than hard-coded. Enter each data item once and document each assumption, so a reviewer can trace any output back to its inputs.
4. 4Make the model answer decision questions by changing inputs. Run sensitivity tests and scenarios on the questions the passages pose: what happens if interest rates rise, and how much can we discount before we start making a loss?
5. 5Test accuracy before you present. The passages cite errors in approximately 94 percent of spreadsheets, so build error checks and review the assumptions and the construction before anyone relies on the numbers.
6. 6Match sophistication to the decision. The passages note that a more sophisticated model costs more to maintain and recommend the lowest level of sophistication that delivers the solution. Check whether plain Excel is enough, and consider an add-in or other software only if you need more functionality.
7. 7Check that the intended users can operate the model without a specialist. Computer literacy is one of the tool-selection criteria, and a model that only its builder can change cannot support timely decisions.
8. 8Present the output as one input to the decision, not the answer. Show the assumptions beside the result and invite the savvy manager's challenge, because models should be one tool in the decision process rather than the definitive solution.

The passages give a revenue relationship you can turn into a decision model: Revenue in Month 4 = Sales Price × Quantity Sold Prior Month × Monthly Growth in Quantities Sold. Take the decision as whether a pricing proposal is viable. Put the three drivers (price, prior-month quantity, monthly growth) in a documented assumptions area and link the revenue formula to them without hard-coding. The end user then explores different mixes of the three factors and decides which reflects the business best. Next, run the questions the passages recommend: how much can we discount before we start making a loss, and what if interest rates increase by half a basis point? Before the output goes to the decision-maker, run error checks and list the assumptions beside the result. The decision-maker queries the assumptions, as the passages advise, and uses the figures as one tool in the decision, not as a final verdict.

• If you cannot name the decision and the decision-maker, then do not build yet. The model's objective, and therefore its outputs, depend on the decisions to be made from them.
• If an output cannot change when an input changes, then it is a static, direct-input spreadsheet and cannot support scenario or sensitivity questions. Rebuild it as dynamic and flexible.
• If the model has not passed error checks and an assumption review, then do not present it. Errors are common, and the answers should not be taken at face value.
• If a simpler tool delivers the required solution, then use it. Added sophistication raises maintenance cost, and plain Excel is recommended where it is adequate.
• If the people who must use the model need a specialist to change inputs, then simplify it or train the users. Users should be able to manipulate the model without a specialist.

• Treating the model as the answer to all business decisions. The tell: the presentation offers a single number with no assumptions shown and no invitation to question it.
• Trusting the data without verification. The tell: no error checks exist, and no one has queried the assumptions or the way the model was built, despite errors being found in approximately 94 percent of spreadsheets.
• Building a static spreadsheet and calling it a model. The tell: values are typed in directly, and you cannot test what happens if interest rates move or discounts deepen.
• Over-engineering. The tell: the model is more sophisticated than the decision requires, maintenance cost is high, and the update lags the decision date.
• Choosing the tool or outputs before the objective. The tell: outputs do not match what the intended user or decision needs, or the user cannot operate the model without the builder.

• ▸Decision to be made: state it in one sentence (e.g., go or no-go on a project, tender bid amount).
• ▸Decision-maker and user: name who will use the output and their Excel skill level.
• ▸Required outputs: list the specific figures the decision needs, nothing more.
• ▸Key input variables: list each driver, its source, and where it is documented.
• ▸Sensitivity and scenario questions: write the what-if questions (e.g., how much can we discount before a loss).
• ▸Error checks performed: record the checks run and the result of each.
• ▸Assumptions reviewed by: name the reviewer and the date the assumptions and build were queried.
• ▸Sophistication and tool justification: explain why this level of complexity and this tool is the lowest that delivers the solution.
• ▸Decision caveats: note how the output will be positioned as one tool, not the definitive answer.

1. 1Define the model as inputs, calculations and outputs in a flexible, dynamic structure, and build it in plain-vanilla Excel. The source gives the reason: little training is needed, so other people can drive and understand your model. Any analyst can then audit or modify it without a specialist.
2. 2Put every assumption in its own documented input cell and link all calculations to those cells. The source ties best-practice linking and assumptions documentation to saving 'time, effort, and anguish in the long run.' A change then means editing one cell, not hunting through formulas.
3. 3Write logic so a stranger can follow and check it. Translate each business rule into a plain formula, for example =IF(test_cell>=today,value,0), and document the intent next to it. Check timing logic explicitly: annual volumes arriving gradually through the year cannot be counted as a full-year figure.
4. 4Use named ranges for key inputs and data blocks. When data is added and a name no longer covers the right cells, open the Name Manager (Formulas tab, Defined Names group; Insert > Name > Define in Excel 2011 for Mac) and redefine it. One edit corrects every formula that uses the name. In Windows you can also add a comment to each name.
5. 5Choose the simplest tool that does the job. Use a PivotTable, array formula or macro only when necessary. Use aggregation functions (COUNTIF, SUMIF, COUNTIFS, SUMIFS) to gather data. Use a data table for sensitivity analysis only when there are at most two input variables and one output.
6. 6Build speed into your working habits. Learn keyboard shortcuts in place of the mouse: CTRL-N replaces File > New > Blank Workbook and is distinctly faster. Keystrokes are quicker and put less strain on wrists and shoulders.
7. 7Manage file performance before it slows every edit. The source says files above about 50MB do not work efficiently on 32-bit Excel, and most files rarely exceed 20MB unless Power Pivot is used. Upgrade to 64-bit only if you are a heavy file-size user. Otherwise see 'Improving Model Performance' in Chapter 10.
8. 8Before anyone sees the output, change the inputs and test all possible values in advance. A #REF! error during a live sensitivity analysis is the failure to avoid. A correctly built model lets the audience watch a single input change flow through in real time.

The source's insurance example shows how one logic error costs time. The assumption is that the new product acquires 30,000 customers every year. A modeller who books revenue on all 30,000 for the full year has ignored that customers join gradually, so revenue is overestimated 'by a substantial amount.' Found late, this error forces rework across every dependent calculation and every report built on it. Found early, it is a one-cell fix. The productive build puts the 30,000 in a labelled assumption cell and writes the timing adjustment (an average over the year) as a visible, documented formula. A reviewer can then check the logic in seconds, not trace it. If the customer volume changes, one edit updates the whole model. The source adds that modellers who make this kind of error learn quickly and then document their logic clearly for others to follow and check.

• If a rule or range is used in more than one formula, then put it in one input cell or named range. When data grows, redefine the name in the Name Manager rather than editing formulas.
• If a task needs more than two input variables on a single output, then do not use a data table. Choose a different scenario tool, since the scenario logic stays the same across tools.
• If a PivotTable, array formula or macro is not necessary, then use a simple formula. The simpler build is faster to audit and easier for others to understand.
• If a file is well under 50MB, then stay on 32-bit Excel and look at model-performance fixes first. Upgrade to 64-bit only for heavy file-size work such as Power Pivot.
• If the model will be shown live to management, then test all possible inputs beforehand. Show the scenario changes in real time only once no error can appear.

• Hard-coded or undocumented assumptions. The tell: a single input change requires edits in several places, and reviewers cannot tell why a figure is what it is.
• Timing and logic errors, such as counting all 30,000 customers from day one. The tell: revenue looks too high against a gradual-acquisition reality, and you find it only after downstream work is built.
• Stale named ranges after data is added. The tell: totals and lookups silently miss the new rows until someone redefines the name in the Name Manager.
• Over-engineering with advanced tools. The tell: a macro or array formula is used where a simple formula would work, and colleagues cannot follow or modify the model.
• Untested inputs before a live demonstration. The tell: a #REF! error appears mid-presentation when someone changes an assumption.

• ▸Model name and purpose: one line stating the decision the model supports.
• ▸Assumption cells: list each key input, where it lives, and whether it is documented (Y/N).
• ▸Named ranges: list each name, its current range, and the date you last checked it against the data.
• ▸Logic checks: list each timing or business rule (for example, gradual customer acquisition) and the formula that implements it.
• ▸Tool choice: for each non-trivial technique (PivotTable, array, macro, data table), write why a simpler formula would not do.
• ▸File size and performance: record current size in MB and whether it is under the roughly 50MB 32-bit threshold.
• ▸Shortcuts adopted: list the keyboard shortcuts you now use in place of the mouse (for example, CTRL-N).
• ▸Pre-presentation test log: list the input values tested and any errors, such as #REF!, found and fixed.
• ▸Reviewer sign-off: name a second person who has checked the logic and the date.