How to Create Clear Financial Models in Excel [7-Step Framework]

Read time - 5 mins

If you're worried your models are getting messy, or may be difficult for others to understand, keep reading.

Truth time…

I’ve never built a financial model in Excel…

I know, I know. I can feel you judging me 🤣

I’ve used a few tools for modelling (you know me).

But not everyone has the time to setup a tool, and the money to spend on tech.

Enter Excel.

Modelling in Excel

Recently, I had the pleasure of welcoming Danielle Stein Fairhurst onto the podcast.

She’s a Microsoft MVP and trains people on how to build financial models in Excel.

Before we go into her framework, there’s a couple of things to note:

1. Don’t use AI to build models for you - (Did I really just say that 😂) - This is a high risk move given AI lacks knowledge about your business and may not be able to make correct assumptions.
2. Use AI as a coach & learning resource - Instead, use AI as a coach and learning resource to explain, break down and troubleshoot the modelling process.

Remember, you’re not learning a skill if you try and get AI to DO stuff for you. Focus on augmenting your own skills instead.

Right. 7-Step Framework. Lets get rocking…

The Framework

1. Definition

What’s the model for?

Sounds simple, but having one model for one purpose that can be clearly understood by others is crucial.

• Cash flow forecasts
• Pricing analysis
• Headcount

Define your model.

2. Data / Inputs

Next we need to document what data will be coming in as inputs.

For example, for short-term cash management you’d need:

• Cash balances
• Outstanding AR & AP
• Revenue and expense forecasts
• Upcoming investment activities

Prompt - “I am building a financial model to achieve [outcome], please provide some examples of input data I can use to form an excellent model.”

3. Assumptions

Then we’ll need some assumptions.

Staying with the short-term cash management example:

• Average time to pay from customers
• Variability, e.g unexpected expenses
• Economic factors, e.g interest rates
• Timing of payments to suppliers

Prompt - “I am building a model to achieve [outcome] with this input data [data]. Walk me through some example assumptions that I can build into the model to improve it’s accuracy.”

4. Documentation

This doesn’t have to be war and peace.

Tying this into step 1, ideally you want to get to the point where you can say.

“This model is built to show [outcome]. We are using [data] for this reason, and we have assumed [assumption].”

This may also be a business partnering opportunity, as if you share this across departments they may be able offer their perspective on other useful data points and assumptions.

5. Linking Formulas

One of the common mistakes when building models is hard-coding your input data into formulas.

This is a nightmare if your input data changes.

The recommended route is to reference other cells or ranges.

Simple Example - Cell Reference:

1. Hard-Coded Approach:
   Suppose you want to calculate the future value of an investment with an annual interest rate of 5% compounded annually. You directly input 0.05 into your formula.
   Formula: =A1 * (1 + 0.05)^5
   
   
2. Dynamic Formula Approach:
   ****Instead of hard-coding the interest rate, link to a cell (e.g., B1) where you input the interest rate (e.g., 0.05).
   Formula: =A1 * (1 + B1)^5
   
   

Intermediate Example - VLookup:

1. Hard-Coded Approach:
   You’re building a budget spreadsheet. Instead of using a dropdown list for expense categories, you manually type them into each cell.
   
   
2. Dynamic Formula Approach:
   Create a dropdown list in a separate column (e.g., `Category`). Then, use a formula to look up the corresponding expense amount based on the selected category.
   Formula: `=VLOOKUP(D1, ExpenseTable, 2, FALSE)`
   
   

Advanced Example - Dynamic Arrays:

Dynamic Arrays are a powerful feature introduced in Excel 365 that allow you to work with multiple values at once, without needing to specify a fixed range. They automatically spill results into adjacent cells based on the size of the data.

Suppose we have a list of sales transactions with the following data:

Table

We want to calculate the total revenue for each transaction (Quantity Sold × Unit Price) using a dynamic array formula.

1. Static Approach (Non-Dynamic):
   If we were using traditional formulas, we’d need to create a separate column for the calculated revenue. Let’s say we put the formula in cell `E2`:
   - Formula in cell `E2`: `=C2 * D2`
   - Drag down the formula to fill the entire column.
   
   
2. Dynamic Array Approach:
   We can achieve the same result using a single formula that automatically spills the results:
   - In cell `F2`, enter the following formula:
   `=C2:C4 * D2:D4` - Press `Enter`.
   - Excel will automatically spill the results into cells `F2:F4`.

[NOTE - This section of the framework was generated almost entirely by Copilot, I’ll let you judge how useful it is 😉]

6. Presentation

Once your model is built, it’s time to make it presentable.

Add annotations, commentary, colours etc to make it immediately understandable to anyone who didn’t build it.

Brownie points if it’s in line with your company’s branding.

7. Iteration

They key with anything is to start simple.

Once you’re comfortable you’ve produced a scalable model with a limited number of inputs and assumptions, you can continue to add to it as you start getting more feedback.

Putting it into practise

1. Define what you want the model achieve
2. Copy and paste the prompts I’ve given in steps 2 and 3 into an AI of your choosing
3. If you’re wanting to learn more about Dynamic Arrays watch Danielle’s video here.

Then you can:

1. Take Danielle’s free financial modelling course here.
2. Listen to our Podcast conversation here.

You’ll be creating clear financial models in no time.

That’s it for this week.

See you soon.

Adam

2 Ways I can help

When you're ready here's how I can help you:

• Book a 1:1 coaching session - We'll turn your tech confusion into tech confidence. Start crushing it here
• Join the AI & Automation Academy Waitlist - Get early bird access to my up and coming course here