Changelog

Added a new Simple Venture Valuation sheet to the Venture Valuation Tool to provide an alternative example of doing the math to calculating the returns to an investment.

One of my beliefs about building tools like this is that all analysis involves tradeoffs between complexity and ease of use, and that the fundamental goal of an analyst is to develop the appropriate level of complexity for the depth of insight required to make the decision at hand.

The goal for this sheet is not to be a comprehensive analysis (that would be the Venture Valuation sheet in the tool), or to be the only way to do it, but instead provide an instructional example for how to evaluate the returns to an investment (gross multiple and gross IRR) with an intentionally limited set of data. The method in this sheet is built using a set of tradeoffs that makes sense to me to help with analysis, but will not work for all situations, so treat it as an instructional resource to help you build your own analysis for your specific situation.

Recently I've been working on a variety of website updates aimed at making it easier to find content and support. Here's a quick summary of recent changes:

• Listed Tools for Startups and Tools for Investors, new bundles of existing products to make it easier for people to download multiple tools and templates.
• Listed Token Allocation and Distribution Model, not a new product but a new listing of something that was not surfaced in the products and templates sections.
• Swapped out the technology behind the search functionality, shifting from Algolia to Pagefind, which should result in better search results in a better interface.
• New Blog and Docs pages to make it easier to find content. The new Docs page will be a base for continued documentation improvement.
• Minor reorganizations of content on product pages, additional download information, and minor tweaks to styling across the website.

• Adjusted methodology for estimating forecasted unrealized gains and losses. I could write an essay on this, but in short, this is a best estimate using the gross IRR and accounting for value of invested capital and exits. The change was to shift from a method that used the overall net IRR to estimate the annual changes in unrealized gains and losses (calculating fair market value, FMV) to a method that uses the modeled rounds on the portfolio construction sheet. Please note this model does not have sufficient tracking of intermediate changes in value of the portfolio to calculate this accurately, so this is a best estimate given the information the model has, and you may alter the logic behind this if you want to change the estimate.

For a more specific method of doing this, that requires the more advanced portfolio construction method used in the Venture Capital Model.

• Upgrading how the waterfall handles warrants. Edited the Exit Waterfall and Exit Waterfall, Extended sheets for a beta implementation of cashless warrants exercise. Select the option for cashless exercise in the pre-distribution cap table setup, and the model will adjust the proceeds from exercise and proceeds to warrant holders automatically. This is prebuilt for all of the options or warrants lines, although this would only be used for warrants.

Update to the Venture Capital Model:

• Preview version of an updated approach to Portfolio Construction, as well as a different approach to scenarios and multi-entry by strategy (e.g. first checks into multiple stages). Do not use in production until full release, looking forward to comments and feedback.

• 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 Capital 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 Capital 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.