Friday, August 14, 2009

Three Worksheets Your Financial Model Must Have

A good financial model should feed into three main pages, which happen to match the three main financial statements of a business - profit & loss, balance sheet, and statement of cash flow.  These three components of the model should never be circumvented nor should we ever try to short-cut the need for all three.

With these three pro forma statements as the final deliverable, there are at least three additional worksheets that need to be part of the model. They include assumptions, marketing/sales/COGS, and payroll.

Every working financial model should have one page that contains a majority of the assumptions for the model. Assuming we are using Excel or another spreadsheet template to create the model, these assumptions should be linked throughout the model. This gives us the ability to make a change to any one of our assumptions and then see how that changes our profitability and cash flow outcomes.

We need detail! It is not sufficient to say we are going to grow sales by 50%. What are the marketing activities that will drive that growth? How many leads will we need to generate a sale? What is the cost of these leads and other marketing activities? Which product or service lines will grow more than others? How does our gross margin differ on these lines as compared to slower-growth or even obsolete lines? Is there a difference in both the collections and the payment for costs of goods sold between these lines? How will this impact cash flow? These are the questions we look to this worksheet to answer.

How many people is it really going to take to accomplish what out financial model projects? What are the salary and wage costs to hire all of these people? Are our hiring practices in line with the sales per employee financial ratio according to our industry benchmark? Have we correctly factored in all payroll burden and benefit costs, including FICA, FUTA, SUTA, worker's comp, other state payroll taxes, health insurance, 401(k) match, etc.? Have we correctly forecast all of the costs associated with adding these new employees, including recruiting, HR, and new office and computer equipment? These need to be factored into our plan so that we can demonstrate a realistic cost for growing our firm.

While there are many other supplemental worksheets that may be used to help build a formidable working financial model, these three are a requirement.