Financial Modeling & Projections

If you need capital, you need solid financial projections.  This is as true now as it was 8 years ago when we wrote this.  A good model not only forecasts your company’s finances, it also gives lenders and investors confidence that you have them under control.

Established companies generally use “top down” forecasts based on numbers from last month, quarter or year.  The future is the same as the past, plus or minus whatever we expect to change.  If the CEO wants payroll cut 5%, you can find the granular details to meet that target.

In contrast, startups need to build their models from the “bottom up”. Next year’s expenditures can be 5x this year, doubling again the next year.  These increases are driven by brand new costs and obligations, whether signing another lease or tooling up a new factory production line.  Managers cannot set meaningful percentage targets in this environment.  Instead, they face go/no-go decisions:  Should the launch be delayed?  Should a Chief Revenue Officer be hired now, later or never?

Small details can make a huge impact on your “bottom up” projections.  How many units will your customers buy?  How many can you manufacture, ship, and keep as inventory?  How much working capital will it take to finance this new production before customer payments catch up?  If you need to lease more space, have you considered the capital needed for security deposits, build-out, relocation, etc?

Who cares?

Investors.  Lenders.  Your bank.  Your board.  Your landlord.  Other stakeholders that depend on you, such as key suppliers, major customers, JV partners, your management team, potential hires, etc etc etc…

They care because your financial model is the best way to demonstrate:

  • Financial Stability: Strong margins, a large working capital cushion, and access to reserves to cover downside surprises.
  • Plausibility: Overly optimistic assumptions will stand out like sore thumbs: Sales volumes & prices, growth rates, staffing needs and compensation costs, production/distribution/sales expenses, rent and capital expenditures, etc
  • Efficiency: Are your costs in line with the market?  Are you paying MBA’s to do office assistant work?  Are you paying Hong Kong salaries for roles competitors base in cheaper labor markets (or automate entirely)?
  • Completeness:  Have you left out significant expenditures? Legal & audit fees, taxes, sales commissions, software licenses, recruiting & on-boarding costs, and easily overlooked items?
  • Accountability: Have actual results come in close to model predictions? Can you explain the variances?  Have you updated your latest projections with the most recent actuals?  If so, you appear more credible and build stakeholder confidence in your model…  Which will soon be called your “budget” or “forecast” as it becomes more reliable and less speculative.

It sounds easy

Guesstimating is easy. Modeling is hard.

Start with your real balance sheet.  Layer every line item of income and expense.  Adjust these inflows/outflows to reflect actual timing of cash receipts/expenditures to calculate your working capital needs.

For example, you might need to pay for your widgets before you can sell them.  They might sit in inventory before they are sold.  Then your customers might take a month (or more) to pay for their purchases.  NOTE: You can grow your way into a cash crunch even if every sale is profitable!  

A good model has all of the following:

  • Granularity:  Details at useful level of analysis, whether product line, distribution channel, region, sales outlet, department.  Ability to “drill down” into factors that influence results.
  • Standardization & “Modularization”:  Do not customize the model for each restaurant / store / outlet / product line.  Make one standard model that includes all the necessary line items, and roll it out for all units/sheets.  (e.g. Don’t remove a VAT / sales tax line in HK, just leave it blank.)  Cell Q97 will be the same line item and time period in all units/sheets.  This makes it easier to extract data, build queries, make edits, and eventually import your data into something more sophisticated than a spreadsheet.
  • Input sheets: Consolidate all key variables in one place that’s easy to update and control, and have the numbers flow through to individual units/sheets.  Avoid “hard coding” anywhere.  Individual units/sheets should have clearly marked standard input fields where overrides are necessary.
  • Output sheets:  Standardize all the reports and charts you need.  Make them adjustable so you don’t need to manually rebuild anything later on: adjust dates, units/sheets covered, etc.  Include both summaries and detailed information; include monthly / quarterly / annual slices of data, with budget vs. actual if possible.  Include balance sheet, cash flow, and income statement views.
  • Data Validation: One small mistake can cause big problems.  Prevent mistakes where possible (clearly marked/shaded input fields and lock calculation cells).  Build redundant checks everywhere else.  Does the balance sheet balance?  Are there spikes/gaps in trends?  Is there an inconsistency from one column to the next (eg a number instead of a formula)?  Make the model find these issues for you.  Make them stand out.  Yes, you still need to be vigilant, but your eyes should be the last line of defense, not the only one.

Happy with your model? Good. Now mess it up.

Reality may not cooperate with your model.  Bad things can happen.  Even good news might catch you by surprise.  Will you  be able to ramp up production if sales exceed projections?  Or will you leave an opening for competitors? 

Therefore you should “stress test” your model. Run scenario analyses that reveal your firm’s sensitivity to changing assumptions:  Sales volumes, production costs, days to collect receivables, staff turnover, financing costs (or even funding availability), and more.  Make a worst-case “bear” scenario, and keep adjusting variables to reveal the limits of your business model its vulnerabilities.   How much belt-tightening can you afford before it affects your customers, suppliers, and lenders?

You will be a batter manager if you understand your company’s risk profile.  Lenders and investors will be more receptive to your funding pitch if you share this information and have solid plans to address these downside risks.   It is easy to sell them on the upside vision.  But you won’t get their money unless you address their concerns.   

Contact Deep Water Management to discuss your financial modeling needs.

 

Listen to Right Said Fred to for their thoughts on modeling.  Catchy.