8 Ways To Make More Powerful Excel Models

“So, Brett, what does GALOP say?”

During my days as a quantitative investment banker, my colleagues would often ask me to convey the results of my financial models so they would know what product or strategy to offer a particular client. Programmed in Excel, my GALOP models (which stood for “Global Asset-Liability Optimization Program”), would analyze a client’s balance sheet and suggest changes to improve cash flow and reduce risk. I was struck by how my model could give me, in effect, a second vote in formulating client strategies.

I believe more people should use spreadsheets to help make decisions:

• As I described above, quantitative models are persuasive. In my experience, people find models and numerical output inherently objective, compelling and trustworthy.
• Done well, models are rigorous and transparent simplifications of reality that teach you something new. Building a model forces you to understand deeply the decision you are facing.
• Modeling differentiates you from the vast majority of folks who are uncomfortable with modeling and numerical analysis in general, increasing your value on any team.

Here are some best practices that have worked well for me in creating models to support decision-making:

1. Use design thinking. Have a clear problem statement, crafted from the user’s point of view. Then, create and test many versions with potential users, iterating based on objective feedback, so that each version gets better. Our prototype for Decision Fish, the online financial wellness program, is an Excel model. We tested and iterated more than two hundred times with prospective users.
2. Separate the inputs, calculations and outputs. This way you can validate the inputs to limit the risk that users enter bad or mistaken data. It will also make it easier to audit the calculations and for people to follow your logic.
3. Check for errors. As tedious as it is crucial, you must make sure there are no math or logic errors. First, try to roughly estimate the result, given the inputs. Is the model’s result in the ballpark? If so, then check the intermediate calculations with a hand calculator. (I like to print out the model and check the calculations by hand in a separate location.) Review the formulas (with Control-’), their precedents and dependents. Add some checks, for example, that assets equal liabilities. Use conditional formatting to highlight extreme results. Don’t forget to spell-check. It’s also helpful to have a colleague check for errors: it’s much easier to admit someone else’s error than your own!
4. Format for clarity. Apply some basic graphic design principles to make sure the assumptions and results are easy to read. Align and format things consistently so it’s clear how things are organized and what’s important. Invest a little more time to make it attractive: this builds trust by signaling that you likely also spent time to ensure the model is sound too.
5. Build minimally and flexibly. Use named ranges so you (and others) can better understand your model in the future. Don’t use constants (numbers) in your formulas. What if these numbers change in the future? Put constants in a separate table that you can reference from the formula. Use the right functions. Absolute references can save a lot of time and space.
6. Choose the right charts for the data. Line charts are good for trends, bar charts for comparisons, pie charts, well, pie charts should generally be avoided. A stacked bar chart is easier to decode than wedges. Abbreviate numbers e.g., “$25 billion”, not “$25,000,000,000.00” Use chart titles that summarize its meaning or at least the data. Also, minimize ink and extra formatting (like 3-D effects), also known as, “chartjunk.”
7. Use macros to save time and reduce errors. They will save you a lot of time in the long run. You can record a set of tasks that you do frequently or do an Internet search for code that will do what you’d like, e.g., “Excel VBA Sort Column Delete Duplicates”. Be sure to check the code does what you want it to do!
8. Be honest. Complex models almost always include design choices and default assumptions that can have big effects on the results. For example, Decision Fish assumes consistent investment returns throughout the user’s lifetime. While our assumption is conservative (low), we provide a sensitivity analysis so users can see how important this assumption really is. Here’s a good test: if you were a user who knew what you as modeler knew, would you still make the same design and assumption choices?

Creating a spreadsheet model is a lot like telling a story. You are communicating a certain perspective on the world, a preference for making decisions a certain way and persuading people that a particular choice is best because your model is trustworthy. In both writing and modeling, you begin with your end in mind. And you end by making sure you’ve achieved that end effectively, honestly, elegantly and with respect for the people who will benefit from your work. Then, you have given your model its voice.

This article originally appeared on September 6, 2018 at Forbes.com.