Top 10 Tips for Financial Modeling

Finance and accounting professionals develop, build, and work with spreadsheet models, in one form or another, throughout all stages of their careers. Models are used in almost all industries and across all functions.

Primarily we utilize spreadsheet models to:

• Proof of concept – Test a hypothesis that we have in our heads and check its validity.
• Discovery – Find some insights from the data and present a coherent/logical theory.
• Optimization – Improve an existing situation and experiment with different parameter ranges.

One downfall about model creation is that many times they become quite complicated, hard to follow, and convoluted.  It may be challenging for a new person to quickly learn and make sense of the inputs and outputs of models.

Einstein said, “If you can’t explain it to a six-year-old, you don’t understand it yourself.”

So how can we simplify?

Below are 10 model creation criteria that we have found very effective in our consulting engagements. Some are “must haves,” and others are “must dos.”

Must-Haves

1. Summary – This tab will have a very brief description of what the model is for and how it works. Example: This model predicts the number of students who could enroll in an academic year at an educational institution based on our marketing budget and historical trends. We adjust our results based on macro-economic factors such as the state of the economy, job market, unemployment rate, local demography, and fiscal policies.
2. List of Sources – This tab will list all source files from where data is imported, their locations, update frequency, and the person responsible. You can add any other information you feel relevant.  The source list will act as a helpful checklist to go through before we start working on/updating the project.
3. Update Instructions + Data Flow Manual – This tab will have a complete set of instructions on how to update the model and how the mechanics of the model work.  The best way to present that is through a flow chart or a block diagram.

4. Initial Input & Final Output – We could have 50 intermediate analysis tabs in the model. Still, we must have the initial inputs that the user controls and the final output on a single page.  The outputs are a compelling way to make your decisions dynamic based on changing data.

5. Separator Worksheets – Please create separator tabs that create spacing between groups of similar tabs, i.e., Inputs Vs. Outputs, Lines of Business, etc.  In the below example, we have 1,2,3,4, but you can add as much information as possible to the worksheet names to help the reader.

6. No Hard Coding – Please don’t hard code any data except for user inputs. If you must have them, please add comments or keep them in a different tab altogether. Every piece of information should have a clear source.

7. Colors – Colors can be used to convey a lot of information without the need for any text. Use aesthetically and to differentiate between types of data. Totals vs. Subtotals, historical vs. new, input vs. output, absolute vs. formulae, actual vs. forecast, etc.

8. Automation – Many repetitive tasks can be automated using simple Macros. Even if you are not proficient in VBA programming, you can start by recording a Macro. That will create some basic code, and there are millions of resources online that you can use to enhance and customize your code further. The last step is to create some ActiveX controls and have clickable buttons.

9. Pictures vs. Words – A picture is worth a thousand words. Graphs and charts can explain a finding more engagingly than a lengthy explanation. It also retains the interest of the end-user. The more interactive, the better!

10. Worksheet Quick Summary – Last but not least, create a box in the top left corner of every worksheet that you have in your model and write a quick summary on the purpose of that specific tab. Any assumptions or hardcoded data can also be explained here. The summary will help make navigating a model a breeze.