Seperation of Concerns in a Financial Model

Here’s a targeted interview question to test a job candidate’s financial modeling skills:

“Should you place inputs, calculations and outputs CLOSE TOGETHER in your model?”

The answer should be NO. Your model should clearly distinguish between inputs, calculations and outputs. “Separation of concerns (SoC) is a design principle for separating a computer program into distinct sections, such that each section addresses a separate concern. The value of separation of concerns is simplifying development and maintenance of computer programs” (source: Wikipedia). This principle doesn’t just apply to computer programs. It applies to any system, including a spreadsheet.

You can separate inputs and calculations in several ways:

  • Have one sheet to input assumptions (i.e. constant values or retrieves from external data sources), and a second sheet where it’s purely calculations with no manual inputs.
  • Put assumptions and calculations in a different section of the same sheet, clearly marked.
  • Format cells that contain assumptions a different colour to cells that contain calculations.

To understand why the separation is important, suppose you’re calculating the NPV of a project. You hardcode the discount rate of 6% into every cell in row “Net DCF” using the formula:

Net_Cash_Flow * 1 / 1.06 ^ n
where n = 0 … 4

What happens if the discount rate changes? How do you perform sensitivity analysis to assess the risk of the project failing to generate the required benefits? You would need to change the formula in row “Net DCF” and drag the cells across to get the updated NPV. If you have 20 scenarios in your sensitivity analysis, you would need to do this 20 times. Whereas if you moved the discount rate assumption to a separate cell, you can simply change the value of that cell or use an Excel macro to automatically change the cell value and graph the result based on different discount rates.

How does someone else not familiar with your model know that you have used 6% as your NPV? They will need to find the 6% that is hidden in row “Net DCF” rather than referring to a separate input sheet. Also, you might change the discount rate to 10% in the formula, and forget to change the label to “NPV @ 10%”.

This is why you should keep inputs and calculations separate. You should be able to change your inputs without touching any formulas.

The second part of the answer is that you should separate outputs from your inputs and calculations. Let’s say that you have several debt facilities, and one of them is called “SGF” (Shareholder Guaranteed Facility). For August, you calculate the monthly interest expense as ( 2.00% + 1.88% ) * 0.0833 * $1,700m = $5.5m, as below:

However, management wants you to produce a report that looks like the below:

If you decided to change the layout of your calculations to match the management report, it would be difficult for other people to understand your model (and perhaps yourself too). This is because mixing presentation with calculations make it difficult to break complex formulas into multiple rows of simple formulas. It encourages you to put calculations in hidden cells, or keep a complex formula in a single cell so that your workings are hidden in the report.

Also, notice how there is a break between the “Prior Months” and “Current Month” heading. What if you want to drag your formulas across to December 2017? It’s much more difficult to maintain your model because the layout is now inconsistent. It’s now used to fit as much information as possible into a single page for readability (rather than usability). It’s no longer designed to layout complex calculations into smaller, consistent and more manageable blocks.

The preferred way is to have a separate sheet for presentation, which simply links to the calculations in your model. This allows you to present your information to management (who aren’t interested in all the detailed calculations), without having to worry about touching any calculations or making your model difficult to use. You should be free to layout your calculations so it’s easy to use and audit, i.e. flows from left to right, top to bottom, and each “block” (or group) of formulas has only one output (in this case the output is “SGF Interest Expense”).

The separation ensures that you’re only focused on what is relevant, and not have to worry about making a change that will break other parts of your model. If you’re changing the assumptions, you don’t want to think about the calculations. If you’re changing the logic of your calculations, you don’t want to think about how to layout the management report. If you’re changing the report to make it look nice, you don’t want that to affect your calculations. Your model should be modular, and each group of cells, and each sheet, should make sense if viewed in isolation and should have a single clear purpose.

Leave a Comment