How to create an excel analysis like a boss, with examples

FP&A and strategic finance heroes look here

We’ve all seen it. An excel analysis that’s grown too unweildly for anyone to use, let alone audit. In my seven years working in finance roles I’ve witnessed that time and time again, excel-based calculations get overly complex and the work erodes into something that’s not useful, or even worse — dangerous.

This doesn’t have to be the case. Complex models and analyses can actually be fun to create and share. Below I lay out five simple tips for creating clear, concise, and pretty excel-based analyses that will make your CFO swoon.

5 best practices for your excel analyses (examples below)

1. Color code cells (blue for hardcoded inputs, black for formulas)
2. Make cell formulas simple and easy to audit.
3. Use clear and descriptive titles
4. Use “check” functions liberally to validate key outputs
5. Define print margins so the user knows where to focus (plus they look cool!)

The key to remember is to spend time on formatting. If your boss can’t read and follow what you’ve done, you probably can’t either. Messy models almost always lead to mistakes.

Example Analysis 1

Below we show an analysis that bridges total contract value to revenue, which is helpful for businesses driven by large, multi-year contracts. Note the use of clear titles, color coded cells, and a “check” to ensure accurate outputs.

Example Analysis 2

Here we show a billings forecast for a subscription business with two plan types. Note the easy to follow schedule to build up to subscriber count, the single location of the model drivers, and the use of formatting throughout.

If you use these tips and practice them over time, you will impress your boss and make life a lot easier for yourself.

If any additional questions on best practices, please comment below or email harry.macinnis@gmail.com. A link to the underlying excel for the examples can be found here. Happy modeling!