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

Apr 12, 2024

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

Transaction ID Product Quantity Sold Unit Price
101 Apples 50 $1.20
102 Bananas 30 $0.80
103 Oranges 40 $1.50

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

Subscribe to Framework Friday

1 tech, productivity, or mental framework to your inbox most Fridays. Access to my tech toolkit, 76 tools and how I've used them β†’ Many of them free. Links to all my 100s of LinkedIn posts β†’ Updated weekly

Subscribe for free withΒ just your e-mail:

I respect your privacy. Opt-out any time.