Financial Modeling

A Standardized Template to Present Financials Investors Will Like

While concept, business model, impact, and team are the core of any business, financial modeling is the driver of an investor’s decision whether to fund or not.  It’s said that in a business plan, most investors read the executive summary then go right to the back, to see the financials.

Not only do the numbers need to make sense, but they need to be presented in a way that is familiar, and digestible, to investors.
Unfortunately, financial modeling requires significant expertise in accounting, planning, and spreadsheet creation and use.  Few entrepreneurs have the skills or experience to tackle this task.  But it must be done, and it must be done well.

This section provides a framework of how we would like to see your financials presented.  It is as much for our “Deal Leads” and temporary CFO expert partners as it is for entrepreneurs.  If you think you’re up for it, bravo.  If you need some help, let’s talk.  We can introduce you to folks that can review your existing actuals and plans and help you think through the implications of one set of initiatives or another.

Create a Financial Planning Spreadsheet

In Excel (or Numbers for Mac users) create a spreadsheet with multiple “tabs”:

  1. ​Summary
  2. Actuals
  3. Business Model
  4. Sensitivity
  5. You may create other tabs as needed

Gather Your Actuals

From your accounting system, Quickbooks for example, find reports for your P&L, Balance Sheet, and Cash Flow Statements.  Set those reports to show Annual numbers… i.e. 2016, 2017, etc.

Get the two full prior years, and current year-to-date.

Use the “Expanded Mode”, so all the detailed line items show.   Don’t use the “transaction detail” mode, we don’t need to see every entry.

Export each of those reports (P&L, Balance Sheet, Cash Flow) to Excel.  Copy those sheets into the Financial Planning Spreadsheet you created in Step 1, in the “Actuals” tab.  Be sure that the column headings line up (so all the 2017 figures are in the 2017 column, for example).

If you don’t have an accounting system, you’re not ready to even begin thinking about investors.  Rather, contact SCORE to get help setting up and accounting system.

Download the AFI Financial Summary Template

We developed this template for two reasons:

  1. To provide Entrepreneurs with a high level summary expectation of the numbers we want to see, and the format we want to see them in; and
  2. To allow our Investor Network, who look at a lot of deals, to see financials from many different companies all in the same format.

About the AFI Financial Summary Template

You’ll notice that we have included ALL the core financial statements on one page, but that each statement is very short and very high level.  It is extremely valuable to be able to tell the story of your company, with numbers, on one page.  The detail can be there, in backup reports (and on detail-level tabs in your master spreadsheet), but investors want to quickly grasp the high level position of the company.

  • Profit & Loss (P&L) tells us what the scale of your business is, what your cost structure is, and how profitable you are;
  • Balance Sheet tells us what level of assets, and liabilities, you have;
  • Cash Flow shows the in’s and out’s of cash… what monies did you get – or do you expect to get – from fundraising?  What owner distributions are you making?  Where’s all that cash going?
  • Use of Funds describes not just what you’re going to use the investment money for, but, in AFI’s template, how you expect that project to help you grow sales, or cut costs, or increase your impact.
  • Source of Funds schedules out where you expect the investment money to come from, including AFI, maybe other investors you have potentially lined up, maybe capital contributions by you and the other owners, maybe from a crowdfunding campaign, maybe from a grant.
  • Debt Holders is really important.  It shows who you owe money to, and how much.
  • Ownership Structure is another name for “Cap Table”.  It shows who owns your company; who has how much stock or ownership percentage.
  • And lastly Valuation gives us an idea of how much you think your company is worth.

Actuals and Forecasts Together

Oftentimes we’ll see company financials with the “actuals” and “forecasts” presented separately.  Investors hate that.  One thing investors always look at is change from period to period.  “Is it reasonable this company is going from $5,000 to $5M in sales in one year?”, we’ll ask?  (Spoiler alert:  probably not…) Similarly, the first step in analyzing financial forecasts is looking at how they compare to actual past performance.

Forecast the Business

Financial forecasting starts with 4 things:

  1. an Operational Plan that documents what you’d like to accomplish, when, and how;
  2. the amount of money you’re planning on raising, and what you’re going to use that money for (this is called the “Use of Funds“);
  3. a spreadsheet that translates the company’s “Business Model (i.e. how the company makes and spends money) into financial statements;
  4.  a set of Key Assumptions that drive that business model.

If you don’t have those 4 things, create them before starting to do financial forecasting.

With those 4 things, open the “Business Model” tab and either copy in your existing financial forecasting work, or create it.

While creating that detailed forecast, here are a couple hopefully helpful hints:

  • Put Dates in row 1 & Titles in column 1 and freeze those rows & columns to make it easier for us to review later.
  • You’ll probably model the business on either a monthly or quarterly basis, choose the one most appropriate for your business.
  • Near the top list the key assumptions driving the model, using a cel or cells to show the variables associated with those assumptions.
  • Don’t type the results of your forecasting work into the Summary tab; rather, use links.  No doubt as you work and we review things will change.  It is easier to have that automatically update the Summary by using links rather than hardcoding numbers.

Highlight the Effects of the Proposed Investment

Within the P&L section of the Template, you’ll notice that both Revenue and COGS have two line items: one is for your current, existing business and the other is for the incremental Revenue or COGS that you expect to happen enabled by the proposed investment.

So, put all your historical actuals (including “Current YTD”) in the row “Revenue: Current and Growth of Current Business”, as well as your forecasts for the future for that part of your business. All the revenue you expect the investment will enable should go in the row “Change in Revenue (+/-) based on Use of Funds”.

Similarly, all of your historical cost of goods sold (including “Current YTD”) should go in the row “COGS”, and all the COGs associated with the “Change in Revenue…” row should go in the “COGS Change (+/-) based on Use of Funds” row.

It might be that you have more than one bundle of activities (what we think of as “Projects”) you’ll use the investment money for.  If so, in the “Use of Funds” section, list each of those Projects, list how much you’ll spend on that Project, and show how it will affect Revenue, COGS, or Expenses in the forecast years.

In the example above, the company is raising $100k to do three projects:  buy production equipment, hire a sales rep, and re-engineer the factory floor for greater production efficiencies.  They expect:

  • The production equipment will cost $50k (which hits on line 18, Long Term Assets and on line 34, CapEx (capital expenditure) but it will enable $3k of increased revenue in the current year and then $40k of higher revenues for the next couple years.  And, we’ll assume for this example that the production equipment actually has even more capacity to handle even more revenue.
  • The new sales rep will cost $60k a year, but he/she will only be on for half of the current year, then all year for the next several years.  In that first 6 months we won’t expect any real sales (or rather, any we get will be gravy) so prior to “paying for him/her self” in the out years we need to fund half a year of pay, or $30k.  That hits line 12, “Change in Operating Funds due to Use of Funds”.  But, hey hey, we’re expecting this person to generate $180k a year in new sales.  That’s great, but since higher sales mean higher COS, we have to reflect that too (we’re assuming a 50% COS rate), so COS goes up $90k/yr.
  • Re-engineering the factory floor is expected to cost $10k in consulting fees and another $10k in project expenses, for a total of $20k this year, but we’re thinking that will actually reduce COS by $5k this year and then increasing amounts ($10k, $20k, & $30k in Years 1, 2, & 3) because it will be more efficient.

The net result of all this?

  • We can see that each project has a positive, double digit IRR, meaning each is worth doing.
  • For the $100k investment net income goes up in each of the out years far in excess of the investment amount, generating a really healthy IRR.

Why do this?  We’re trying to get a handle on the efficiency of capital usage, in other words, how much bang do you expect to get for the buck.

How We Review Your Financials

Revenue

Revenue typically drives financial results.  At a high level, we’ll look at the relative size of the business (“is this a $20k a year business or a $20M a year business”) and the growth rates from period to period.  Super high growth rates, particularly between actuals and forecasts, always raise red flags.

At a detail level, on your “Business Model” tab, we’ll want to see Units and Selling Price/Unit per product line, with particular attention to period to period changes in both and the mix of product lines within the total.

Since revenue is so important, we’ll look hard at the amount of new revenue you expect to generate because of the investment.


Cost of Goods Sold (COGS) & Gross Margin

The main problem we typically see with COGS projections are wildly optimistic projections of how fast, and how much, COGS will change because of the Projects enabled by the Investment. 

Our first pass look at this will be reviewing Gross Margin %, which is the ratio of Gross Margin (Rev – COGS) to Revenue.  Gross Margin %’s typically change slower, and less, than people think they’re going to.  If you have big changes in Gross Margin %, be prepared to justify those with more spreadsheet detail.

Gross Margin % also impacts the eventual choice of financing vehicle.  For example, investors typically think businesses suitable for Revenue-Based Financing should have Gross Margin % above 60%.


Expenses & Spending

Here’s what Investors like to see:

  • spending that directly, with high likelihood, results in higher revenue, lower COGs, or lower later spending;
  • evidence of scrappiness, the ability to get by on little.

Here’s what Investors don’t like to see:

  • high owner’s salaries prior to being cash positive
  • spending on anything that doesn’t directly result in higher revenue or lower COGS or lower later spending, like new carpet for the office, or customer acquisition costs exceeding the lifetime value of those customers.

Balance Sheet

Managing Receivables, Payables, and Inventory can have a significant effect on a company’s cash flow.  We’ll look to see the level of these items.  If they’re material, we’ll want to investigate further.

Existing debt load is very, very important to us.  We need to know the number and amounts of all the debt you hold, including:

  • Loans you, or other founders made to the company
  • Loans family or friends made to the company (if they’re not documented we’ll want to talk about that)
  • Personal credit card debt that was really used for company purposes
  • Vendor financing, customer advances, leases
  • Bank or private debt, loans, or advances

Cash Flow Statement

The Cash Flow Statement is probably the most important of all the financial statements.  When you run out of cash, you’re toast!  When you have lots of extra cash, you can invest in the business, or take money off the table and enjoy a well earned vacation.

Many accounting programs, like Quickbooks, create a Cash Flow Statement that is super hard to read and understand.  We prefer to break out the cash flow story into three sub-stories:

  • Cashflow from Operations, in other words what monies come in and go out as part of the normal everyday course of business;
  • Cashflow from Capital Purchases (abbreviated CapEx), which are typically big asset items that don’t hit expenses, but do hit cash (out) and long term assets; and
  • Cashflow from Financing Activities, like taking out loans and paying them back, securing investment, or paying dividends or distributions.

What we’re looking for here is that you understand you cash situation, both by managing it well in history and in balancing the level of investment with the cash needs of the business.

Oftentimes we’ll see Ending Cash Balances go up very dramatically.  You’d think this would be good, but in reality it usually signals an unrealistic revenue/margins forecast, or the lack of thinking about re-investing excess cash in the business, or forgetting that a growing business needs “working capital”.