How To Build a Financial Model in Excel: Pro Tips from Expert CFOs

A solid financial model is a necessity for any founder who wants to raise funds or optimize their business operations. A well-crafted model helps you make better decisions, impress investors, and see a glimpse of your financial future.

Today’s best financial models are built using fully-featured software like Forecastr that allows you to build complex models, what-if scenarios, and complicated revenue streams.

However, many founders aren’t ready to adopt a dedicated financial modeling software yet, for one reason or another. If that’s you, then you’re probably planning to build your model in Excel, Google Sheets, or another spreadsheet application.

Building a financial model in Excel can be a challenge, especially if you don’t have a lot of experience with complex spreadsheets. But don’t worry, we’ve got you covered with some great templates and several tips to help you build a useful model that will give you a lot of good insight into your business.

What is Financial Modeling in Excel?

Financial modeling in Excel is the process of creating a structured representation of a company's financial performance, using spreadsheets to forecast future earnings, expenses, and other metrics. Excel's powerful functions and features make it an ideal tool for building and analyzing financial models, helping businesses make informed decisions and plan for the future.

Table of Contents

• Financial Modeling Templates
• Before You Build
• Structuring Your Financial Model in Excel
• Populating Your Financial Model in Excel
• Healthy Model, Healthy Business

Excel Financial Modeling Templates

We help a lot of founders build great financial models. In the process, we see a lot of common mistakes. Some of these mistakes just make the model hard to read, but others can cause inaccuracies in your data that could potentially impact your decision-making or fundraising.

The tips we’re sharing below will help you steer clear of those common mistakes and create a reliable Excel model that’s easy for anyone to read and understand.

To make things even easier, we also have some free spreadsheet templates you can use as a starting point. If you start with one of our templates and follow the tips below, you should be in a good position to start using your model right away.

Before You Build a Business Model in Excel

Before you dive in and start importing your data into one of our templates, take a few minutes to read through these best practices, as well as the best practices for adding data to your model, below. These will keep you on the right track and keep any re-work to a minimum.

Match Your Financial Model to Your Business Model in Excel

You need your financial model to reflect your business model as closely as possible. Spend a little time thinking about your operation and make sure that all of the important pieces are included.

Ask yourself these questions:

• What are your revenue streams?
• Do you monetize customers from different channels in different ways?
• Is your cost to acquire a customer different across channels?
• Is your retention rate different across channels?
• How do you generate profit? How will you generate profit in the future?
• Do you have any planned hires and are they scheduled for specific dates?
• Which of your expenses are directly related to providing your product or service?
• What other expenses do you incur in the process of running your business?

Knowing the answers to these questions will make it much easier to build a financial model that accurately reflects your business.

Keeping The Revenue Model in Excel Simple

You can turn yourself inside out thinking about all the different behaviors your customers could exhibit (refunds, introductory discounts, churned customers returning in the future, etc.). These things may be important to your business, but they don’t necessarily need to be shown in your financial model.

It’s extremely challenging to accurately model complex customer behaviors. You’ll create a better model that’s easier to maintain and use if you avoid diving too deep into the details.

If you do need to create customizations for any unique requirements, this might come in helpful: Excel Formulas for Financial Modeling.

Remember That It’s Just a Model

100% of financial models are wrong. That’s why they’re called models.

However, with consistent use and frequent updates, you can create a financial model that’s an accurate and reliable representation of your business. Analyze your forecast against your actual financial performance every month, and you’ll dial your model in and increase its accuracy over time. More on this below.

Structuring Your Financial Model in Excel

How should you structure the various elements of your Excel financial model? What’s the best layout to use? How should you format your data? Here's how to do financial modeling in Excel:

The best approach is to keep your structure clean and simple. This will make it easier and less time-consuming to use and update each month.

At Forecastr, we like to break our spreadsheet revenue models down into 3 or 4 tabs (or sheets).

First Tab: Cover Page and Table of Contents

Your cover page should include your name and logo, along with a link to your website and your direct contact information.

Including a table of contents with links to the corresponding sections makes your model user-friendly for other people. Use the remaining space to provide a brief overview of what’s included in the model and how it works.

This might seem unnecessary, but it adds a lot of value when you share the model with investors and other stakeholders who haven’t seen it before.

Second Tab: Assumptions

Use the second tab to host all of the assumptions that will drive the calculations in your revenue model.

In our templates, the top four rows are used for date counters. This allows you to create formulas based on dates. We typically include projections for 5 years or 60 months.

It’s useful to separate your assumptions into several sections. The right section designations might be different depending on your business model, but these sections will fit most use cases:

• Customer Acquisition: How do you acquire customers? Do you use a variety of channels to get customers in the door, and how many customers enter through each of those channels?
• Revenue: How do you monetize your customers? Do you rely on one-off transactions? Do you sell subscriptions? Do you run a marketplace and take a percentage of the gross transaction value?
• People: Your existing staff and hiring plan. What positions will you be hiring for, and on what schedule? What positions drive your customer acquisition and revenue?
• Expenses: The expenses associated with operating your business: rent, utilities, payment processing, marketing, professional fees, etc.
• Balance Sheet: Your assets, liabilities, and equity. Assets are where you should track cash and other things that add value to your company like equipment and inventory. Liabilities track everything that detracts value from your company like long-term debt and accounts payable. Equity is where you will forecast accumulated retained earnings and equity held by current shareholders. Future investments are also tracked as equity.

Third Tab: Monthly Summary

The monthly summary showcases your three financial statements every month.

Income Statement

Without getting into too much detail, the Income Statement should follow a standard flow and include the following:

• Revenue
• Cost of Goods Sold
  All costs that are directly associated with providing your product. As an example, COGS for potato chips might include potatoes, salt, and oil.
• Gross Profit (Revenue – COGS)
• Operating Expenses (Subtracted from Gross Profit)
  Operating expenses include salaries and all other non-COGS expenses. Some examples include rent, utilities, marketing, and professional fees.
• EBITDA/Operating Profit (Gross Profit – Operating Expenses)
• Depreciation and Amortization
• EBIT (EBITDA – Depreciation and Amortization)
• Taxes
• Net Income (EBIT – Taxes)
• Net Margin (Net Income / Revenue)

Balance Sheet

The Balance Sheet keeps track of the balance of your assets, liabilities, and equity at any point during the forecast period.

The balance of assets must equal the balance of the sum of liabilities and equity. Our templates include a formula to indicate whether or not the balance is correct for each period.

Cash Flow Statement

The Cash Flow Statement keeps track of the cash in your business. Start with your beginning cash, then factor in financing, investing, and operating activities. These are your change in cash. Add (or subtract) your change in cash to your beginning cash to arrive at your ending cash.

The Cash Flow Statement tells you your burn rate, your runway, whether or not you’re operating at a loss, and how much money you might need to raise. As you can see, it’s very important.

Tab 4: Annual Summary/Graphs

This tab is optional, but it’s often useful to see a visual summary of your forecast. This is especially helpful for investors and stakeholders who want a snapshot of your projections without digging into the details.

You can use this tab to add any other graphs or visuals that would help an investor interpret your data or support the narrative you’re telling them in your pitch.

Populating Your Financial Model in Excel

Now that you understand the basic structure of an Excel financial model, you’re ready to start inputting data to build the model. If you have good accounting data to work with, this part will be pretty straightforward.

Here are three best practices to help keep you on track while you populate your model:

Start with Customer Acquisition

Customer acquisition drives the entire model. Consider all of the strategies that you are using to acquire new customers, and include any strategies that you plan to add during the forecast period.

Some common examples of customer acquisition channels include outbound sales, paid ads, content marketing, influencer marketing, partnerships, etc.

Build Your Excel Revenue Model with Your Real Data

What is your cost per click? What is your conversion rate on those clicks? Base your model on the data you have available today, and avoid making up numbers to fill in the blanks. If the real number is zero, enter a zero in the model.

As you run your business and gather real data, come back and update your model. This shouldn’t be something that you build, use once, and never use again.

Each month, compare your forecast for key figures like customers, revenue, expenses, and payroll and compare it against your actual performance. If your forecast was way off, update your assumptions.

This is the way you refine your financial model over time to achieve an accurate forecast that’s representative of your business. Besides being a great tool for fundraising, the ultimate goal of a financial model should be to use it as a tool to help you make better business decisions.

To do this, you need your model to be based on your real data.

Always Build From the Bottom Up

Here’s an example of a top-down assumption: “There are 8 billion people in the world; we can get 10% of them.”

Investors will immediately recognize assumptions like this as a red flag and you should too. Top-down assumptions can lead you astray in your decision-making and planning. It’s best to avoid them.

Here’s an example of a bottom-up model assumption: “If we make ten sales calls a month, we can convert 20% of those leads into customers. That means we get two customers a month. In the third year, we’ll have a larger team and we will make one hundred sales calls a month and get twenty customers.”

It’s OK to start your data analysis with small numbers. Investors would much rather see your real data with realistic growth assumptions. This approach will also give you a better understanding of your operation so you can make better decisions.

Healthy Model, Healthy Business

Optimal performance for your startup depends on having a reliable financial model that gives you insight and confidence as a founder.

A solid model is also key during the fundraising process. A well-built and detailed model shows investors that you have a strong command of your business and are capable of making the best decisions.

Building a financial model in Excel can be challenging, even for an experienced entrepreneur. Our spreadsheet templates will help. They’re a great resource and we encourage you to use them.

When you’re ready to upgrade to an online financial modeling solution, we’ll be here for you. Forecastr gives you a simple and convenient interface with great features and analysis that make your model easy to share and present.

When you sign up with Forecastr, a pair of experienced financial analysts work with you to build and maintain an amazing financial model with accurate data. You get ongoing support as needed, and we check in to help update your model every quarter. Reach out today to learn more and schedule a demo.