Do you know how to create pro forma financial statements, key metrics, and calculate valuation, dilution, and ROI? How many strategic financial planning models have you created? One, two, none?
Building a strategic financial planning model, determining cash flow, capital needs, founder dilution, and ROI is one of the most difficult parts of dealing with investors and raising capital. We’ll cover high-level issues related to financial modeling this week, breaking the model into three manageable chunks.
This is usually one page in your Excel workbook that contains all the inputs. I like to color all inputs in blue and put borders around these cells to make it clear which numbers are inputs. Inputs are further broken down into “facts” and “picks”.
Facts: An input that’s indisputable, such as the speed of sound in air at a given temperature.
Picks: An input that may be a guess, assumption, appraisal, assessment, or estimate, though it should have some ground to justify the number to investors.
Investors always want to review your picks during diligence, as those are the assumptions that you’ve made to obtain your current business case. Many investors will want to change your picks, varying the assumptions to understand the effect on your business. You’ll need to know before investors ask how changing each pick effects growth, cash flow, capital needs into the future, ROI, and valuation.
Ideally, you want the investors to believe that your picks are valid based on your business strategy, giving them more reason to believe that you’re trustworthy and competent.
Key point: Don’t give an investor your financial model. Instead, work side by side and run as many different business cases as they need to believe your picks, facts, and business strategy.
This is one or more tabs in your financial model where all the calculations are performed. There should never be hardcoded—entered—numbers in the calculations sections other than unit conversions—dividing by 1000, for example, to change units into thousands. Hardcoded inputs in the calculations section will get you tossed by investors, as it’s virtually impossible to trace hardcoded numbers buried in equations and it appears dishonest.
An additional rule of thumbs on calculations:
Each calculation should only be performed once, referencing the output as many times as needed in other equations or outputs. This helps avoid errors.
Array calculations should be used throughout. This ensures equation consistency between columns, reduces errors, speeds calculations, and reduces file size.
Keep formulas simple, using additional rows to handle complex calculations. This keeps errors to a minimum, makes troubleshooting easier, and allows easier modifications in the future.
Ensure that your calculation architecture flows down the page, simplifying flow and speeding up recalculation time.
Reports consist of multiple tabs in your model. Each tab should have a standard output structure with at least the following time period outputs:
Monthly Plus Annual Totals
Quarterly Plus Annual Totals
In addition to a summary and detailed income statement, you’ll also need a complete balance sheet and cash flow statement along with a set of metrics that are standard for your business and industry.
If you construct a model that allows actual historical inputs to be entered over time, the reports tabs should alert you immediately when you are off track and need some correction.
Key Point: Reports should always reference outputs from the calculations tabs.
Building an effective financial model takes years of experience and going through the process dozens of times. A good financial model will give investors confidence in you, your business, overall strategy, and a level of comfort regarding future cash needs as you grow, ownership over time, and return on investment. These are all important as you raise capital.
Are you unsure about your team’s ability to build a robust financial model? Want help vetting a model that your team has built? Schedule a one-hour call with us here to review your options before meeting with investors.
Until next week!
All the best,
Did someone forward this to you? Great! Sign up here so that you don’t miss the next article in our series.