Changelog

Launched the new How to Model Venture Funds course, with the same content, slides, spreadsheets, and materials as before, now a self-paced only course at 1/3 the previous cost. Take the course on your own pace, schedule a 1:1 with me, and ask questions by email, I'm happy to help.

• Edited Key Metrics, Total Employees, to pull it from the corresponding line on the Forecast sheet

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 Investor 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 Investor 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 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.

• Added new category for Other Current Assets to Forecast, Statements, and Summary sheets, to provide an extra asset account to break it out from potentially amortized long term other assets.
• Edited Forecast sheet to create a new build for Amortization of Other Assets, to match the structure for Depreciation. This was done to create a bit more flexibility in the assets and amortization accounts.
• Edited Statements sheet to change the reporting of net income and retained earnings on the balance sheet; previously this reported current month net income, and each month (or each period, if using quarterly or annual timescale) it added net income to retained earnings. Changed so that it now tracks net income over a fiscal year, and adds to retained earnings at the start of the next fiscal year. No change to the net of retained earnings and net income, just a shift between them, to balance it out differently.

Change to all Venture Capital Models:

• Edited Forecast sheet calculation of RVPI to estimate the value of carried interest. Previously RVPI was reported on a gross basis, while DPI was reported on a net basis, creating a TVPI that reflected a net DPI and gross RVPI, slightly overestimating TVPI because it did not estimate future carried interest incurred when the unrealized gains were realized. A small edit that removed the slight change in TVPI on the last period where RVPI goes to zero. Easy to replicate into any existing model by copying the new formula.

A Foresight milestone: $1 million in sales from templates and tools. Details →

• Edited Forecast sheet to add a new Runway calculation, to calculate the number of months runway based on cash at the beginning of the month and future expenses. The model calculates runway two ways, one just a simple current cash divided by net burn that month, the second way looking at how long current cash lasts based on future cash flows, thus accounting for changes in net burn in the future. Both can be valuable in thinking about runway.
• New sheet called Snapshot, that is intended to help with analysis. Pick a date, and the model will report expected quarterly revenues, runway, cash in the bank at the end of the selected month, and create a table and chart showing major changes in cash over the previous 3 months and next three months from the selected date. Goal is to help with analysis, this is a first version and expect this to change over time.

Relaunch of the Standard Financial Model for Services. The release of this model leverages two key additions from the last couple updates to the Standard Model - adding seperate billing structures, and introducing bundling of new signups to start of revenues - in adding the last features necessary to make the Standard Model a good fit for services and consulting businesses.

Announced the next dates for How to Model Venture Funds and Cap Table and Exit Waterfall Masterclass, as well as the launch of cap table short courses.

Updates to the Standard Financial Model:

• Formula rationalization, reducing use of OFFSET and replacing with INDEX. Goal is to reduce use of volatile functions and improve performance (speed of recalculations). Forecast, Revenues, and Hiring Plan sheets first edit, will replace more OFFSET over time.
• Get Started and Revenues sheets, added new option for % billed upfront. Most cases it will be 100%, but in some cases (services, consulting, some enterprise contracts) there is a need for contracts to be paid at multiple times, on a non-every month or all billed upfront option. This implements a billing option where you can select to bill a % upfront in first month of contract, and the remainder at the end of the contract (on the last month of the contract)

Updates to the Standard Financial Model:

• Edits to Revenues sheet to create a new "bundling" feature. line 114 calculates the conversions after the conversion lag, line 115 is a bundling input, line 116 calculates conversions after bundling. some businesses may make sales over time, but when the sales start may not be the same period, but may bundle them together into groups. an example is in education, where you often sign up users but don't start charging them until school is in session, for example. this input is designed to say "yes" or "no" using a "1" or "0", respectively, to say whether the conversions should flow through (a "1") or be bundled and held until the next event ("0" until the period for them to flow through into the next "1"). Kind of specific for certain businesses; if you don't understand it, then it doesn't apply to you :)

Update to the Angel and Venture Fund Portfolio Tracking, updating the Actuals sheet for a calculation of GP catchup, to handle the situation where there is a preferred return and not GP catchup (no impact if not using the preferred return input).

Update to the Venture Capital Model, Annual Forecast, Detailed Investment Strategy, adding in prebuilt scenario analysis. Video with how to think about scenario analysis and how to use it is linked at the product page and inside the model, and below.

• Update to the Standard Financial Model, Runway Budgeting Tool, and Starter Financial Model, edited the timescale on Forecast sheet row 7, so that it corrects the fiscal year for the Summary sheet when the calendar year-end is not the same as the fiscal year-end. Previously, the Summary sheet would sum up the years still using calendar years rather than fiscal years, a simple change to the formula on row 7 updates this.
• Update to the Standard Financial Model, edited Hiring Plan so it uses the timescale from Forecast sheet, instead of recalculating the timescale. All sheets link to the timescale from Forecast so that they are all consistent, previously this one was independent but now it's tied to the Forecast sheet.
• Update to the Standard Financial Model, added a new assumption to Get Started for the revenue calculations on Revenues sheet so that the input for the starting number of revenue units is exposed on Get Started. No change in model functionality, just a slight addition to make it clearer where to make that input.

Added new page, Pricing, to make it easier to see all products, and updated homepage with a new product list and filter system,

Launched the Talent Collective to help users find professionals to assist with financial models for startups and funds, business plans, valuations, and more. Details here.

Adding Search to the header to make it easier to find content and documentation specific to your needs.

New addition to product pages, adding category groupings (all venture capital models and all startup models) to reviews and ratings. Previously the ratings and reviews reported the ratings and reviews for each product and all products, now I've added a third section (in the middle of the ratings options) that displays the category-specific ratings and reviews for that product's category.

Created a Changelog (this page!) to track updates to products, classes, and offerings from Foresight. This page will highlight improvements to the models by surfacing the notes in each model's Changelog to the website, as well as broader updates about the company.

Thank you to Linear and Tailwind for the inspiration.

Kicked off Cohort 7 of the Cap Table and Exit Waterfall Masterclass. Next cohort to be scheduled soon.

Kicked off Cohort 3 of the How to Model Venture Funds. Next cohort to be scheduled soon.

New opportunity for financial modelers to be listed for referrals for projects. Details at Working with Foresight

new: https://t.co/DR9ZAl8zaN

— Foresight by @tdavidson (@ForesightHQ) April 13, 2023

Hosted a free, live workshop on modeling anti-dilution and their impact on cap tables. Understanding how down rounds impact dilution is an important area for companies in today's climate, I covered the theory and practice in modeling anti-dilution protection, with practical examples for you to use. Download the slides and spreadsheet here.

For more on modeling cap tables, I teach the Cap Table and Exit Waterfall Masterclass, a four session course that dives deeper into the topic.

New post covering How to Model Hardware Businesses using the Standard Financial Model.

• On Exit Waterfall, introduced a new model settings at the top, to set the formula structure in a few situations. Basically, some aspects of the Exit Waterfall create circulars that can be difficult for Excel and Google Sheets to calculate correctly (yes, spreadsheet programs have limitations, especially when it comes to circular references). By default, the model uses simpler formulas unless the more advanced options are required, for example for pari-passu or for down rounds (introduced in the previous version), in which cases the model turns on the more complicated formulas. Hopefully this makes it better for a wider range of people to use without issue, and still keeps the more advanced functionality for those that need it.

• On Exit Waterfall, changed the liquidation preference calculation for each share class to use the proceeds per share to common instead of the residual per share from the section above. The previous method did not work as well in conditions where the liquidation price per share decreased in future rounds. This method does make the calculations even more circular, but more robust for that situation.

• Adding prebuilt anti-dilution protection to the default Cap Table sheet.
• This is a beta release of the feature, and will change based on feedback, but aims to add in full ratchet and weighted average (broad-based and narrow-based) if selected and if applicable based on new share prices.
• The feature is not fully automatic, as you will need to select the type of anti-dilution protection and input the purchase price from the previous equity round.
• This calculates the same protection for all preferred shareholders, although if shareholders have different original purchase prices their anti-dilution protection will be different.
• I do expect to change the approach to make it more flexible and automatic based on user feedback and use.

More at Modeling Anti-Dilution

• Fixed estimated taxes payable to use Taxes instead of Salaries lines, row 420 and 421 in the current Forecast default (thank you to user for finding this).

This was a webinar with Tactyc on how to use the forecast you created for fundraising your venture fund to actively manage your investing activity.

Tactyc is a platform for forecasting and scenario planning platform for venture capitalists. The software enables funds to construct, manage and forecast venture portfolios and empowers GPs to be more data-driven in capital and reserve allocations. Learn more at Tactyc.

• Fixed COGS calculation in the inventory section (row 449 in the current default) on Forecast to add to the SUMIFS to also look for Cost of Sales, so that it didn't accidentally pick up advertising and marketing costs used for CAC analysis.

• Extended the Revenues build (with additional assumptions on Get Started) to allow for a % of new revenue units to select two different churn / repeat cycles with separate revenue assumptions. This allows you to segment new customers/users/subscribers/clients into two separate bases, allowing you to assume annual/monthly billing, for example.
• Edited Accounts Payable and Accrued Liabilites on Balance Sheet on Statements sheet to include previous period, so that the formulas are change per period + previous period (Thank you to user for finding this; while the Starter Model shares the same build for the Statements, this was only in the Standard Model).

• Edited Get Started, E26 to calculate the number of follow-on checks differently for the Venture Investor Model. No change to returns or cash flows, no other impact to the model other than reporting the number of follow-on checks differently.

• Edited Forecast sheet to include a new input line for "Changes in Accounts Receivable", and edited the accounts receivable calc to use that amount if used in the model. What this does is make it easier to use the actual changes in accounts receivable in the model; previously you had to compute cash collected to back into the change in accounts receivable, now it will do it directly using the change in AR you calculate from your actual balance sheet

New post about the tools, processes, and expectations around Special Purpose Vehicles (SPVs) for investments.

• Edited line 89 (Taxes, forecast) and line 195 (Taxes, actuals + forecast) on Forecast sheet to correct an error introduced in previous version. Simple to change by adjusting those two lines.

• Added a new Key Metrics Sheet (focused on SaaS businesses) and a few new SaaS-specific metrics on the Forecast sheet.
• Added new Hiring Plan sheet to aid in detailing a hiring plan by putting it on a separate sheet.