Most of the financial model templates you see here on the site can be used as a startup forecasting tool. One of the more complicated logical things to build is the initial investment requirement. It may seem simple at first, but once you get down into the nitty gritty things can become less clear.
Note, the most important thing is that you figure out the monthly cash flows per period (positive and negative) based on all the assumptions. That data point can then be used in many ways. The below methodologies are the ways I use it.
Method 1 - Lowest Cash Position Determines Investment - Best for converting monthly activity to an accurate annual cash flow analysis / IRR / NPV.
I like this one and use it in nearly all the traditional businesses I've built models for. It is a comprehensive number that accounts for startup costs and burn over time. Things like a car wash, a SaaS startup, a construction company, or more general businesses. I never use this method for real estate and we'll get into that later. It works pretty well and is defensible in most of the situations I've gotten into with previous clients.
This calculation sits above any equity contributions. The output calculation we want to use for this is the total cash flow per period (usually monthly periods are the best 'period' to use). You can use a 'period 0' as well if you want to show an investment happening prior to period 1 and that is usually going to account for debt funding, initial non-regular operating expenses, and initial other capex items. However, you can start everything in period 1 and the methodology will work just fine.
What you do is figure out all the cash in / out per period and sum up the cumulative effect of all the cash flows that happen over the entire forecast. Since this is a forecast and you never know exactly when or what the negative cash flows are going to be and how revenue will offset the initial operating costs, we are seeking to figure out what the maximum negative cash position is going to be. If we know that, then we know how much money the startup is ever going to need (in theory per all the assumptions one has entered).
What I like to do is take the data from the monthly cash flows and summarize it in a period 0 through N tab that sits by itself. On this tab I'll have the DCF Analysis that is based on annual periods. The formulas are tricky, but I'll try to explain them here. You reference the maximum negative cash position that we calculated above and that becomes the equity requirement or initial investment required. We must track how much of this equity requirement has been used in each period.
In period 0, I want to know the total initial investment as well as the 'remaining equity to use' which is just the initial investment less negative future cash flows that were covered by the initial investment. For 'remaining equity to use' simply take the total investment required plus the cash flow in period 0 (which will likely be initial startup costs less debt funding).
Here is a screenshot of this happening in a real financial model I've done before:
In the template example above, there are larger negative cash flows in each year because inventory is being purchased ahead of time, so there is a negative cash flow month followed by multiple positive cash flows. When you take the cumulative effect of all this, it is easier to understand the actual investment requirement.
So, trying to figure out the actual cash flows, you have period 0 as the maximum investment, and period 1 is the total positive cash flows for the year less the total negative cash flows for the year and the negative cash flows for the year are offset by available equity to use. The number you get after that is the actual contribution / distribution required.
This methodology was the best style I could come up with when being able to account for any combination of cash flows over time arbitrarily and converting a monthly detail into the most accurate annual cash flow analysis report. In the screenshot above, the total 'cash flow' row will always add up to the same as the total monthly cash flows from the pro forma detail.
Method 2 - Use Monthly Cash Flow
This is a simpler view that simply assumes any negative cash flows in a given period are investments and nay positive cash flows in a period are distributions. You can run an IRR on that and convert it to an annual basis. Usually, it is not viable to play it by ear and that is why I like methodology one better since you basically have a cash flow reserve and see how it changes over time based on operations.
However, this is still a common way that people analyze a new business and if the expected cash flows are positive after the first few periods then it won't make much of a difference.
Method 2.5 - Use Monthly Cash Flows + Manual Investment Entry
I have seen this a few times and all you are doing is looking at the monthly cash in/out and then based on that you manually enter the investment in each period and do a separate row that shows cash flow after equity calculation as well as a running cash position balance based on that. It is less automated but makes it easier to plan strategic cash infusions. Simply make sure the running cash position doesn't go below zero.
In the real estate models, I will use method two and plug that into a cash flow distribution waterfall (IRR Hurdles / preferred return / preferred equity).
Article found in Accounting and Finance.
