How to Build a 5-Year Financial Model

This step-by-step guide will help you create a financial model that will allow you to look into your company's future.
President and CFO, CFOwise
October 30, 2012

As a child I loved to assemble models of cars, planes and boats. The parts were made of plastic, and special glue held all of the pieces together, bringing the model to life. Building a financial plan, or financial model, of a business is a surprisingly similar process.

Like the plastic parts, every financial model has several parts that need to be put together. It also requires many assumptions, which act as the glue holding the parts together. The model allows us to contemplate what the future of our business will look like, a projection of what might be.

The Key Pieces of the Model

Most of the financial models I have built during my career have been in Excel, with many worksheets linked together. Every financial plan needs, at a minimum, the following components, or worksheets:

  • Marketing
  • Sales
  • Cost of Goods Sold (COGS)
  • Payroll
  • Income Statement
  • Balance Sheet
  • Statement of Cash Flows

Every industry will use these components a little differently, but here’s a brief overview of each.

Marketing

Marketing is about generating leads, so your plan needs to show how many leads your marketing efforts will create.

Sales

The sales part of your financial plan will demonstrate your conversion success of qualified prospects into paying customers. In addition, your sales model should break down revenue by major product or service lines, region, industry and other relevant verticals. The more detail the better to force you to make realistic assumptions in lieu of the standard shortcut statement: “Let’s just forecast sales growth at 20 percent across the company.”

Cost of Goods Sold

The next step is to add the costs of goods sold (COGS) into the model based on the mix of products and services, deriving your projected gross profit and margin.

Payroll

Your financial plan needs a detailed payroll worksheet that models changes in employee headcount and costs.

Income statement

Your entire income statement includes adding in your selling, general and administrative costs and other income and expenses to arrive at your net profit.

Balance sheet and statement of cash flows

With the income statement modeled, it’s time to get into the most challenging part—modeling the balance sheet and statement of cash flows. Why bother? Because this part of your plan will illuminate what will happen to your cash flow over the period of the model. This requires you to plan for how all of the balance sheet accounts will change during the next five years, including capital expenditures, inventory, receivables, debt payments and more. Finally, all of this information is used to create a forecasted cash flow statement.

Your Assumptions

You’ve likely realized that this model is a prediction of what will happen in the future, meaning its value hinges on the validity of the assumptions you make as you build it. That’s why the assumptions are the glue that holds the financial model together. The weaker the glue, or the assumptions, the quicker it all falls apart. The strength and value of your financial model is directly correlated with the validity and strength of your assumptions.

The best way to organize the dozens of assumptions required to build a financial plan is to dedicate one “assumptions” worksheet wherein you list all of the key drivers for your business. Here is a list of what some of these might include for each component, or worksheet, of your financial plan:

  • Marketing—lead conversion rates by activity, cost per lead, etc.
  • Sales—close rates, average order size, prices, etc.
  • COGS—increases and decreases in material cost, economies of scale as quantities increase, etc.
  • Payroll—revenue per employee, annual raises, employer-paid items, etc.
  • Income statement—growth rates of SG&A expenses, commission rates, etc.
  • Balance sheet—days in inventory, receivable days, payable days, capital expenditures, etc.
  • Statement of cash flows—no assumptions required because everything else in the model will flow into this worksheet and show final results.

As you list the key drivers for your business and then make assumptions about them in the future, you will want to use them in your formulas for calculating the different components of your plan. This serves two major purposes: First, you will have one place where you can easily review and show others your assumptions, and second, you can easily make changes to your assumptions in one place and see how it impacts the entire financial model. (The car models I built as a child did not afford this kind of flexibility!)

What the Future Holds

Just like the plastic models I used to build, a financial model is never a perfect representation of what actually happens in business. Its value comes from allowing you to look into the future and understand the best configuration of what your business will become along with the ability to either validate or invalidate the key driver assumptions. Drilling down on the key drivers of the business will teach you the intricacies of the business, improve your decision-making, and, ultimately, focus your attention on the most important actions you can take to improve outcomes, like profit and cash flow, in your business.

Ken Kaufman is the President & CFO of Aribex®, an innovator of handheld devices disrupting medical imaging globally. As an award-winning executive with almost two decades of experience starting, growing, leading, and financing dozens of organizations, Ken is a highly sought-after speaker and author, including a best-seller, Impact Your Business.