Modeling Historical Data
Q: My business is already in operation. Can I include my historical data in your model?
A: First of all, the Pro-Forma model is purely a projection-based model. It is not an accounting solution for ongoing business operations. The model makes forecasts based on your predicted sales inputs, not based on historical data.
However, here are some features and suggestions for companies with historical data:
Show historical financials by adding columns. While the rest of the model will not be linked to this data, you can always add columns to the left of the projected years and manually enter the historical values that you would like to show.
Showing historical investment. You may have already received a round of investment that you would like to show, but you have already used some of the funds. You can still enter this in the model indirectly. Enter the investment round in the capitalization section with the appropriate number of shares, showing that it will come in “Month 1” of the model. Then, in the Balance Sheet Assumptions section of the input tab, you will offset the cash coming in by subtracting the investment amount from the “Opening Cash Balance” input. It is okay if the “Opening Cash Balance” shows a negative number.
Example: You received a seed round of $100k last year and will have spent $70k of that in startup expenses by the start of the year. You also have $10k invested by the founders.
Enter the seed round with the appropriate number of shares as coming in Month 1.
Your opening cash balance at the start of the fiscal year would be $40k. However, the model will show an additional $100k of cash from the investment in Month 1.
To negate this, subtract the investment amount from the actual value in “Opening Cash Balance”. In this case, $40k - $100k = -$60k
This way, the Cash Flow statement will show a starting value of negative cash for the first month, but a positive financing cash flow that will bring cash to the correct amount after that.
Showing historical asset investment. If you would like to show depreciation for an asset that has already been purchased, enter that in the Asset Investment Section as coming in month 1, and add back the purchase amount to your Opening Cash Balance. The model will start the depreciation in the first month of the model.