Changelog

• In the Exit Waterfall, Extended sheet the calculations for the liquidation preferences for the second and third share class blocks (Series E and D in the default setup) referenced the incorrect lines from the predistribution cap table. Thank you Jesse for pointing it out; the fix is easy to make, and the updated model is available for Excel and Google Sheets.

Update to the Venture Capital Model, Average Cap Table:

• Major expansion of the model to account for multiple entry points (i.e. first checks at different stages). This is a common request that is easy to add in simple ways, hard to do in an elegant way, and this expansion took a lot of extra math. Edited `Get Started`` to add a number of new lines that now allows you to allocate capital to different first check strategies, set follow-ons per round per first check strategy, and the model will handle capital allocation, number of deals, and returns automatically. It's hard to emphasize how much extra math this took under the hood to get right, but I think it's still a fairly straightforward and easy to use model even with the new inputs and expanded structure. Enjoy!

Change to all Venture Capital Models, from 12/6/2023 to 12/18/2023:

• Edited Get Started to create new calculation of invested capital if using assets under management as the base for any period in calculating management fees. Previously the model relied on circular references to calculate fees and create a schedule of called capital, but that method sometimes failed to work reliably because of how iterative calculations work to resolve circulars. Now, if the model observes any option other than committed capital used in any period on the Forecast sheet, it will alter the estimate of invested capital so that it does not create a circular. Creating this consisted of (a) a new subcalculation routine on the Forecast sheet that calculates assets under management as a percentage of invested capital given the capital deployment strategy and investment strategy (expectations of writeoffs and exits), (b) modifications to the forecast of invested capital to allow the option to use circulars, now a checkbox on Forecast sheet, (c) removal of the manual adjustment to the invested capital introduced in most model versions this month, and (d) a new method used to forecast invested capital on Get Started. The forecast of Invested Capital is still doing the same thing (committed capital - expenses - management fees + recycled capital) but now it's using a lot more subcalculations for management fees to make it work. Now, you can select management fees to use committed capital at any point in the fund life, and the model will adjust, without requiring circulars (the option to use circulars remains as a backup). In most cases the model will result in called capital = committed capital, in some cases in the Venture Investor Model if you are not recycling 100% of management fees called capital can be calculated slightly less than committed capital due to the difficulty in estimating the budget for management fees.
• Edited Get Started and Forecast to make the model a bit smarter about handling expense overruns. Now the model will reduce distributions to handle any unbudgeted expenses, as well as call capital around recycling a bit differently. Changes to Called Capital per period, Management Fees Recycled per period, and Proceeds available for distribution (in the Waterfall) lines.
• Edited Get Started and Forecast to build new way to forecast operational expenses. Previously model assumed operational fees were not charged to the fund in the extension period, after the initial fund operations period, if the fund was not yet exited out of all investments. Now by default it does estimate that period and use that for budgeting for operational expenses.

Currently I'm working on updating the Portfolio Construction approach used in the Venture Investor Model to streamline the math and make it possible to handle using assets under management seemlessly, and hope to have that complete in early January.

• Extended the Drivers, adding new functionality to the drivers to add max value, ending period, and additional flags. This required adding columns to Forecast, Statements, Hiring Plan, and Revenues sheets
• Adjusting the formatting of the Statements, Summary, Snapshot sheets to switch signs for cash inflows and cash outflows. Done to increase the separation between the Forecast sheet (calculations) and Statements and other presentation sheets. Did not switch signs on Budget or Breakdown sheets at this time.
• Added prebuilt calc for interest income to Forecast sheet (higher interest rates FTW)
• Added a prebuilt line for the Sales (Optional) section on Revenues sheet to be added into the growth channel, and added a yes/no toggle on get started to enable that section. No change in functionality, just making the fact it exists as an option a bit easier to find by putting it on Get Started, and separating it out of the Manual Input line in the Growth calculations on Revenues makes it clearer how it works in the model.
• Added a category for Interest Income and Dividends to Forecast and Statements sheet, to improve reporting capabilities for businesses.
• Added timing and placeholder flags to the timescale used on Forecast, Revenues, and Statements, and replacing the previous formulas that used blanks to "end" or "blank out" the forecast after the custom end of the timescale. This replaces the way the model handled timescales that were < 72 periods in the Get Started assumption for number of periods. Now the model will no longer blank out calculations after the assumed period, but will calculate zeros, using the sheet more as a calculation page than a presentation page, a better structural approach. This will have little impact to how most people use the model, but will help those that want to model < 72 periods using the model.
• Bug fixes in amortization calcs (to properly account for value of fully amortized assets), VAT (switching signs on the accrual and disbursement, from switching the signs on the income statement), retained earnings (properly add in the previous y), Breakdown sheet (unallocated other expense, other income, depreciation, amortization, interest income, interest expense, taxes)

The biggest changes here were extending the drivers, switching the signs, and implementing the timeline flag, while also eliminating the use of the timeline test to output blank cells past the input number of months to model on Get Started. This involved changing > 95% of the formulas on Forecast, Revenues, and Statements, so it was a massive edit. That said, moving to the new base is not required, although if you stay with your base fixing the retained earnings bug is recommended.

• Created a video to walkthrough how to add additional calculation blocks to extend the Exit Waterfall for more share classes, and created Exit Waterfall, Extended sheet with two additional calculation blocks.

• Edited Forecast sheet to change the default debt amortization schedule, with a change to the interest expense and principal payment lines, no impact unless using interest-only payment or balloon payment, in which case the interest expense during interest-only period is slighly larger, and the balloon payment input now offers more realistic method.

A couple specials for Black Friday this year:

• Beginning Cap Tables is on sale through Dec 6, 2023, 12 PM Eastern Time for $9 ($90 off the usual $99 price, no discount code required). This provides immediate access to the most recent recorded session plus registration for the next live session on Dec 6, 2023 at 12 PM Eastern time (Zoom details after purchase).
• BLACKFRIDAY for 25% off paid templates (not pay-what-you-want templates), which includes all Standard Financial Model variants, the Starter Financial Model, the Venture Capital Model, Intermediate Portfolio Construction, and both variants of the Venture Investor Model, as well as the courses How to Model Venture Funds, the Cap Table and Exit Waterfall Masterclass, Intermediate Cap Tables, and Intermediate Exit Waterfalls, valid until November 29, 2023 at 02:59 AM Eastern time.

Terms and conditions at Coupons.

Added two new products by Graph Advisors:

• Venture Investment Tracker, an investment pipeline and portfolio company CRM tool built for Airtable
• Data Room Checklist, a Google Doc and Notion page checklist to help emerging venture capital managers provide a data room for prospective limited partners

Change to all Venture Capital Models:

• Edited Forecast sheet, adding a toggle(checkbox in Google Sheets, TRUE/FALSE in Excel) to choose whether the GP commit into the LP capital in the fund is charged management fees. By default in previous model versions the GP commit incurred management fees, by default now it does not, but there is a checkbox to toggle the treatment. Added one new line, and edited Management Fees line.

• Edited Forecast sheet for the Excel file download to fix a formula - calculation of share price per round - that was broken in the Excel download. No change to the Google Sheets version.