What is a financial model? A guide to financial modeling

What is a financial model?

Financial modeling is a complex term, and you can find a wide array of differing opinions as to what a financial model is exactly. The three main definitions of a financial model are:

1. The Federal Reserve definition
2. The Investopedia definition
3. Danielle Stein Fairhurst’s definition

The Federal Reserve definition of a financial model

The first definition of a financial model is given by the Federal Reserve (SR11-7).

“A quantitative method, system or approach that applies statistical, economic, financial or mathematical theories, techniques, and assumptions to process input data into quantitative estimates.” 

This is a bureaucratic definition of a financial model. It is technical and not very useful for people who are just learning financial modeling and financial planning. In other words, it’s not particularly helpful for the majority of American business owners.

The Investopedia definition of a financial model

The second definition comes to us from Investopedia:

“Financial Modeling is the process of creating a summary of a company’s expenses and earnings in the form of a spreadsheet that can be used to calculate the impact of a future event or decision.”

The second definition is much easier to understand and is closer to our own definition of a financial model.  

Danielle Stein Fairhurst’s definition of a financial model

The third definition comes to us from financial modeling expert and author Danielle Stein Fairhurst, who defined a financial model in her book, Using Excel for Businesses and Financial Modeling, as:

“A Complex Spreadsheet – As long as a spreadsheet has financial inputs and outputs, and is dynamic and flexible, I’m happy to call it a financial model!”

We like how this third definition focuses on how a model needs to (1) have financial inputs and outputs and (2) be dynamic and flexible, in order to be called a financial model. Unfortunately, as with the Federal Reserve definition, this definition doesn’t help lay people understand what financial modeling is and why it’s useful.

How would we define a financial model?

We focus on financial modeling for small businesses. Our definition of a financial model: a spreadsheet or application that helps us develop forecasts for business decisions so we can make the decision that optimizes our profits. 

Think of a financial model as the closest thing possible to a crystal ball. It attempts to peer into the future to see if choice A is superior to choice B. But instead of relying on intuition, experience, or advice, a financial model uses data to make its predictions.

A financial model could be as simple as performing an analysis of your lemonade stand to decide if it makes sense to buy a new juicer. In this case, you would use a model to figure out if buying a new juicer would be a good use of your money. You would compare the extra money you expect to make from the new juicer to the cost of the juicer. 

Profit Frog’s small business forecasting software makes financial modeling achievable for the average business owner, who may not have an MBA, be able to retain a suite of management consultants, or be a Microsoft Excel wizard. Start your free trial today. 

What information should be included in a financial model?

The information that should be included in a financial model will vary depending on the use and complexity of the model. Complex financial models, such as those commonly used by venture capital firms, are overkill for Main Street businesses. However, all financial models include the following elements:

• Data: any applicable historical data or actuals such as prior period revenue, gross profit, and expenses (operating expenses and cost of goods sold).
• Inputs and assumptions: every model will have some inputs or assumptions that are used in the model. Common inputs include:
• Time period: how far out in the future do you want the model to forecast?
• Key drivers and inputs: cost of capital, sales volume, number of people that will be needed to hire, etc.
• Other assumptions such as tax rate, inflation rate, interest rate, etc. 
• Calculations: these are the formulas used to determine the outputs. A calculation could involve multiplying the assumed price by the assumed volume sold to determine the revenue for a given time period (note that Profit Frog makes forecasting and calculation easy).
• Outputs: this is where you summarize the key takeaways from the model. In an Excel spreadsheet, this would include an executive summary tab to summarize the main calculations from the model. In a forecasting tool like Profit Frog, outputs would be delivered as the final result after the software has walked you through inputting your data and assumptions and has run its calculations.

What does a financial model look like in Excel?

Traditionally, companies used Microsoft Excel to develop financial models. Here is an example of a financial model done in Excel for Garden Foods.  

(Don’t worry: if this Excel model looks way too complicated, we offer an easier way to do financial modeling for your small business.)

[get started with Profit Frog button]

The following Excel model was used to develop a profit and loss forecast over the next 4 years based on a predetermined set of business assumptions. We then applied a discount rate to see what the current value would be of the future cash flows one could expect to earn over the next 4 years.

Garden Foods can use this financial model to decide whether to proceed with a new subscription product or not. 

Below are screenshots of each page in the model and what they represent. This example is a basic model you would expect a finance professional to build using Excel.

Cover sheet:

Models built in a spreadsheet tool should always include a cover sheet so someone can easily see what the model is, who built it, the date it was built, etc. 

Table of contents: the table of contents includes a list of each page in the worksheet, along with some basic instructions about the model, including color coding used throughout the model to separate inputs, calculations, and links to other worksheets. Depending on the complexity of the model, the table of contents and instructions may all be on one page, or on separate pages. 

These instructions help ensure people who are not as well acquainted with financial modeling are able to pick up the model and use it to guide decision-making.  

Executive summary: this provides an overview of key outputs of the model to be used by management. In the above example, key outputs include revenue, EBITDA, income, and the net present value (NPV) of cash earned from this potential opportunity. 

Assumptions: this tab includes all the assumptions in the model. In this case, the model includes assumptions on operating expenses, revenue, the cost of goods sold, etc.

Scenario: this tab is used to forecast different scenarios for the model. This simple model has a best case, a base case, and a worst case scenario. It helps us determine if the business venture still makes sense under different scenarios, as opposed to analyzing the business case using only one scenario. 

Income statement: the income statement includes all the calculations based on the financial model’s assumptions needed to show the revenue, net income, and EBITDA earned each year during the project. 

CAPEX schedule: this is a schedule used to detail expenditures that will be needed for the project. These are expenses that, from a P&L accounting perspective, will be inevitable over the next few years due to depreciation. This allows us to understand the yearly cash outlays for large purchases that will only have value several years into the future.

Financial modeling is complicated in Excel

Does the Garden Foods example look more complicated than you could see yourself doing? Many owners of small businesses feel the same way. That’s why we created Profit Frog, an easy solution for financial modeling that is tailored to small businesses. We walk you through the financial modeling process so you don’t have to be an Excel wizard. 

Get started with your free Profit Frog trial today.

Financial modeling example with Profit Frog

Now that we’ve seen a financial model in Excel, let’s look at some examples of financial modeling done in the Profit Frog software:

In the following example, we go through three screens of a Profit Frog financial model. In this example, we have a coffee shop business that has decided to launch a food truck to supplement their brick and mortar location. 

The company started the food truck in May of 2022, and the following screenshots allow us to see how the actual outcomes are tracking against the initial model.  

Profit Frog financial model screen #1

The green graph shows how the food truck actually did, while the yellow graph shows the financial model that was made before the food truck went on the road. By comparing the two graphs, we can see how the business is performing against the financial model (food truck profits are currently $15,202 behind plan).    

Profit Frog financial model screen #2

The second view includes the same information as the first, with the addition of all expenses. We can select any expense and enter our estimates by month for each expense on the bottom half of the screen. The yellow graph will update, allowing us to see how our changes to expenses impact our forecast. This allows us to easily make updates each month to our model, based on ongoing performance.  

Profit Frog financial model screen #3

This third screen shows how easy it is to review analytics based on a financial model, and to adjust our plan with a visual data illustration. The graphs illustrate gross margin and net income by product and also by customer. This screen allows us to quickly and easily see a visual representation of our profit, and to adjust the model assumptions visually by clicking on the “Adjust plan” button.  

Profit Frog, as demonstrated above, enables business owners to model various revenue and profit scenarios and then compare them to actuals. It’s easy with Profit Frog, and our customers never even need to touch Excel!