You’re a spreadsheet ninja. You can whip up an awesome model in your sleep. But, spreadsheet modeling isn’t about showing off your skills. It’s about assisting the end users in utilizing the data to be able to make decisions effectively and efficiently. Something that is often overlooked! 

Imagine this scenario. You have spent weeks building an amazing spreadsheet model, with lots of complex formulas and formatting. You share it with the wider team, and… you spend a day fielding questions, explaining your methods, or worse correcting mistakes.

Then, there’s scenario 2. You are revisiting a spreadsheet from a previous quarter. Can you remember why you used a particular formula? Or, why you chose to use it?

So, whenever you’re designing a spreadsheet model, the number 1 thing to keep in mind is… does the output reflect the needs of its end user? If not, it’s time to rethink.

4 best practices for advanced spreadsheet modeling

We like to follow Liam Bastick’s CRaFT principles when planning and designing our models:

• Consistency
• Robustness
• Flexibility
• Transparency

Here’s what it looks like in practice…

Keep your spreadsheet models consistent 

Consistency is key when it comes to spreadsheet modeling. So, it’s important to adopt a consistent standard, and stick to it. This could be using company guidelines on heading structure, colors, fonts, alignment, etc, or create your own. Whatever it is, you want people to be able to look at your model and immediately understand how it works. 

Consistency also helps prevent errors by reducing complexity for users—they don’t have to keep track of different rules across different places in your model!

Tips for making a spreadsheet model more consistent:

• Copying formulas across ranges makes it easier to add or remove periods or categories as necessary. 
• Sheet titles and hyperlinks should be placed in a way that facilitates navigation and provides details about the worksheet’s content and purpose.
• Consider utilizing cell styles to indicate which cells are input, which are calculations, and which contain headers and warning messages.
• Dates should be positioned and formatted consistently (i.e. always using YYYY/MM/DD).

Test your spreadsheet model for robustness

Spreadsheet models should be mathematically accurate, and readily auditable. No one wants to open a spreadsheet see #DIV/0!, #VALUE!, #REF!, etc.

Many modelers add checks as an afterthought. And, by that time it’s too late! So, when building your spreadsheet model keep in the mind the situations that might break a formula, and create a check for it. 

Your checks will generally fall into these three categories:

Error checks: does the model contain flawed logic and/or errors?

Sensitivity checks: are any of the outputs derived from the inputs that shouldn’t be part of the base case (using “best case” instead of the base case)?

Alert checks: anything that doesn’t fall into the other two categories (i.e. revenues are negative, debt covenants are breached, etc).

Tips for making a spreadsheet model more robust:

• Create and evaluate spreadsheets with the goal of keeping things simple, consistent, and transparent.
• Each model should have a high degree of structural, logical, and formula sturdiness built in.
• If other people are going to be entering data into your model, utilize data validation and protect cells to avoid input errors.
• Avoid using any hidden macros.
• Create a dedicated worksheet with a check summary.

Ensure your spreadsheet models are flexible

It’s easy to get carried away inputting data. But, what happens if one of the variables changes a month or six months down line? Are you able to go in and just change one cell, or will you have to trawl through all of the formulas to make the change?

Let’s say for example you have a spreadsheet that calculates sales prices. The current sales TAX for each product is 5%. If you’ve hard-coded ‘5%’ into the SUM calculation you will have to find EVERY formula, and change the TAX rate individually. Instead, use cell references. That way, if the sales TAX rate changes you will only need to update a single cell.

Tips for making a spreadsheet model more flexible:

• Create a structure that provides invaluable “what if” scenarios/sensitivity analysis.
• Ensure end users have the ability to adjust key assumptions within agreed parameters.
• Use cell references where possible instead of hard coding.

Be transparent with your spreadsheet models

Arguably one of the most important principles! Your spreadsheet models should be clear, concise, and fit for the purpose intended. If you can follow it on a piece of paper (ie, no Formula bar), it’s transparent.

The best way to keep track of different calculations is to create a new worksheet for each one. For example, keeping your revenues spreadsheet separate from costs and CAPEX. 

Combining data and visuals on the same worksheet can create confusion. To aid user experience, keep visuals such as PIVOTS and charts on a separate sheet.

Tips for making a spreadsheet model more transparent:

• Create instantly recognizable assumptions, calculations, and outputs that are user-friendly.
• Consider including a simple flowchart that explains how your model works to aid other users (and yourself should you need to review it again later on down the line).
• Add a welcome sheet with instructions on how to use the spreadsheet. This can be extremely helpful for users reviewing the model for the first time. 

Final thoughts

Spreadsheet modeling is a lot like telling a story. And, knowing the intended audience is crucial to its success. Think about who will be looking at it. Is it just you? Your manager? Internal stakeholders? Or, maybe even external customers? Knowing your audience from the get-go will help you create a narrative that increases interest and engagement, as well as easing the comprehension of the data!

With SheetRocks SaaS you can do advanced spreadsheet modeling and build complex spreadsheet models with ease. It’s quick (100x faster than legacy spreadsheets), and it allows you to collaborate with others in real-time without causing headaches. So, if you’re ready to wave “bye-bye” to slow and sluggish models, click here to find out more and become one of our founding members.