<ul><li>1A. AE Rep Productivity by Segment. AEs are direct outbound sales reps with variable ramping schedules and quotas depending on their segment. </li><li>1B. Inside Sales Rep Productivity</li><li>1C. Average ACV by Segment</li><li>1D. Accounts</li><li>1E. ARR by Segment</li><li>1F. Sales Expense by Segment</li></ul>

Modeling ARR and sales: Start by building a Bottoms Up Model

<p>When there is no CFO in place, planning out a sales-driven ARR forecast that takes into account a mix of new and ramped sales reps with various quotas can get difficult.</p><p>As a founder, you might want to know what SaaS metrics are realistically achievable based on a proposed hiring plan and productivity goals. </p>

SaaS Metrics

<figure><img alt="" src="https://miro.medium.com/max/500/1*zDmOjg-iTwhgX7QUfdxOx" width="680" height="374"><figcaption class="hp hq cr cp cq hr hs bo b fr bq br" data-selectable-paragraph="" dir="ltr">Photo by <a href="https://www.vectorstock.com/royalty-free-vectors/vectors-by_prostockstudio" class="bu ht" rel="noopener" dir="ltr">prostockstudio</a> on <a href="https://www.vectorstock.com/" class="bu ht" rel="noopener" dir="ltr">VectorStock</a></figcaption></figure><p data-selectable-paragraph="">When I joined Lightspeed, a founder of a Series B SaaS startup asked me: “Natalie — since you meet growth stage SaaS companies every day, how many sales reps should I hire to hit my ARR targets?”</p><p data-selectable-paragraph="">I started to get this question fairly often from SaaS founders beginning to hit their stride with a repeatable sales motion. When there is no CFO in place, planning out a sales-driven ARR forecast that takes into account a mix of new and ramped sales reps with various quotas can get difficult.</p><p data-selectable-paragraph="" dir="ltr">For founders who want to plan their budget properly as it relates to projections, we suggest modeling ARR based on sales rep productivity because it closely follows how a SaaS business operates. When we dig into SaaS businesses with a repeatable go-to-market, we often create an operating model based on hiring plan forecasts and expected sales rep quota attainment. As investors in numerous scaling SaaS companies (<a href="https://carta.com/" rel="noopener" dir="ltr">Carta</a>, <a href="https://seismic.com/" rel="noopener" dir="ltr">Seismic</a>, <a href="https://www.betterup.co/" rel="noopener" dir="ltr">BetterUp</a>, <a href="https://tripactions.com/" rel="noopener" dir="ltr">TripActions</a>, <a href="https://www.rubrik.com/" rel="noopener" dir="ltr">Rubrik</a>, to name a few), we found ourselves building this model enough times over that I created a template.</p><figure><img alt="" src="https://miro.medium.com/max/500/1*yUtLKA-idk4kQbDHgT0QZg.png" width="680" height="130"><figcaption dir="auto">A handful of Lightspeed’s growth-stage SaaS portfolio companies.</figcaption></figure><p data-selectable-paragraph="" dir="ltr">To help founders think through hiring and setting quotas, I built a basic template for which I have shared the link below. It includes three sections: the Bottoms Up Model, SaaS Metrics, and Summary P&amp;L. The most important tab to focus on is the Bottoms Up Model which is driven by inputs such as sales reps, quotas, expected attainment, account growth, and other sales metrics. Below I describe each section of the model and how they interrelate, followed by an Appendix with a step-by-step guide. I’ve shared the link to the spreadsheet for you to download <strong dir="ltr">here</strong>:</p><h2 data-selectable-paragraph=""><strong>Start by building a Bottoms Up Model.</strong></h2><h2 data-selectable-paragraph=""><strong>1A. AE Rep Productivity by Segment</strong></h2><p data-selectable-paragraph="">Going from the top row down, start with entering historical and projected headcount of Account Executives (AEs). AEs are direct outbound sales reps with variable ramping schedules and quotas depending on their segment. A segment refers to the target customer base of each type of rep, usually ranging from SMB to Mid Market to Enterprise.</p><figure><img alt="" src="https://miro.medium.com/max/500/1*A7daediHwfQbU7xYW_H0F" width="680" height="125"><figcaption dir="auto">A screenshot of a hiring plan, ramping schedule and expected ARR of SMB AEs with fictitious numbers.</figcaption></figure><h2 data-selectable-paragraph="">1B. Inside Sales Rep Productivity</h2><p data-selectable-paragraph="">There is also a section to help plan for Inside Sales Reps who answer inbound requests from potential customers. This portion only reflects the ending number of inside sales reps and does not include separate sections for new and churned reps.</p><p data-selectable-paragraph="">In this case, the startup would offer potential customers a trial before committing to a contract and the Inside Sales Rep would manage the customer relationship. We understand this may not be needed for every SaaS startup, but created this section in case it is helpful.</p><figure><img alt="" src="https://miro.medium.com/max/500/1*od1figu7yKgiKGPPw2i8FA.png" width="680" height="178"><figcaption dir="auto">Headcount by geography, trials generated, and conversion rates of Inside Sales Reps with placeholder data.</figcaption></figure><h2 data-selectable-paragraph="">1C. Average ACV by Segment</h2><p data-selectable-paragraph="">In our model, we use inputs to drive trends of average Annual Contract Value (ACV) per Account over time. As a quick sanity check while working on this section: if a SaaS business is trying to sell more into the large enterprise segment, for example, it should see average ACV increase over time. Conversely, if a SaaS business selling more to the SMB or focusing on self-service (which tend to be smaller accounts), then it should expect ACV to decrease over time.</p><h2 data-selectable-paragraph="">1D. Accounts</h2><p data-selectable-paragraph="">Our model includes sections for New Accounts and Ending (Total) Accounts which are both calculated, and Cancelled Accounts which are inputs. You may prefer to calculate Cancelled Logos based on an assumption of Logo Churn Rate, but we used inputs for the purposes of this template.</p><h2 data-selectable-paragraph="">1E. ARR by Segment</h2><p data-selectable-paragraph="">For a SaaS startup that sells to different segments, we include the split of ARR across segments to understand the effectiveness of the go-to-market (GTM) strategy among account types. This section shows New, Expansion, Churn, and Total ARR by Segment and from Trials.</p><p data-selectable-paragraph="">For the inputs in the leftmost column (Column A), the yellow shaded cells are assumptions for annualized Upsell and Churn rates. We recommend using the last year’s historical annualized gross churn and upsell rates as a conservative estimate.</p><figure><img alt="" src="https://miro.medium.com/max/500/1*eNbD7MPzUahQtRQHvDTz6Q.png" width="680" height="201"><figcaption dir="auto">Zoomed out view of New, Expansion, and Churn ARR by Segment.</figcaption></figure><h2 data-selectable-paragraph="">1F. Sales Expense by Segment</h2><p data-selectable-paragraph="">Based on the hiring plan in Section 1A (AE Rep Productivity by Segment), our model also estimates expenses to hire sales reps using average salaries. Note that we did not include commissions in our template as it may vary, but we encourage you to add an additional section to include sales commissions if it applies to your startup.</p><h2 data-selectable-paragraph="">Next tab: SaaS Metrics</h2><p data-selectable-paragraph="">We include a dashboard of SaaS metrics to show a summary of how all the assumptions in the Bottoms Up Model affect your SaaS metrics. As a founder, you might want to know what SaaS metrics are realistically achievable based on a proposed hiring plan and productivity goals. A tab like this SaaS metrics dashboard will help you benchmark against companies in your industry.</p><figure><img alt="" src="https://miro.medium.com/max/500/1*BRdEOfIKuvxXckqfNG9mCw.png" width="680" height="341"><figcaption dir="auto">A dashboard of SaaS metrics including Annualized Net Retention, ARR Churn, Logo Churn, and Sales Efficiency.</figcaption></figure><p data-selectable-paragraph="">Below the dashboard, you’ll see a section that calculates Sales &amp; Marketing Expense based New Bookings from the Bottoms Up Model divided by Sales Efficiency ratio assumptions. For the purposes of this template, we assume the Sales Efficiency ratio calculated from the Sales &amp; Marketing Expense in the same quarter. The inputs for future projections of sales efficiency should follow the trend of the historical Sales Efficiency ratio.</p><p data-selectable-paragraph="">Since we recognize that companies with longer sales cycles may use Sales &amp; Marketing from the prior quarter to calculate Sales Efficiency ratio, we recommend choosing the best method that fits your startup.</p><h2 data-selectable-paragraph="">Rolling up into the Summary P&amp;L</h2><p data-selectable-paragraph="">In our model, Revenue is approximated as a % of Ending ARR. If a SaaS company is 1) completing implementation to go live in a consistent window of time across customers and 2) has contract terms for all their customers, Revenue should be approximately proportional to ARR. We recommend that you make assumptions that are based on the trends of historical data and conditions (1) &amp; (2) above.</p><p data-selectable-paragraph="">For R&amp;D, G&amp;A and Other operating expenses, our model assumes a % of Revenue to calculate these line items. A more detailed model might calculate these operating expenses based on ratios of managers and directors, and we encourage readers to extend the template in this regard.</p><p data-selectable-paragraph="">We’ve also included a section for Estimated Cash Flow / (Burn), which references the row for Operating Income (Row 26) in our model. Because software companies tend to have low capital expenditure, Operating Income can be a close approximation for Cash Burn. This is not exact but can be directionally helpful when considering how much money to raise for your growth round or when asked for operating cash burn if your startup is not yet profitable.</p><figure><img alt="" src="https://miro.medium.com/max/500/1*g4GG6C9Ql4Xt4CeDXE30Fw.png" width="680" height="310"><figcaption dir="auto">Zoomed out view of the Summary P&amp;L.</figcaption></figure><h2 data-selectable-paragraph="">A final note and extra credit (+1)</h2><p data-selectable-paragraph="">If you’re a founder of a SaaS startup in the phase of scaling your go-to-market strategy, you’ve probably thought of parts of this model before. Hopefully, this template for SaaS operating model is a swiss army knife in your toolbox of financial planning for your startup.</p><p data-selectable-paragraph="" dir="ltr">I’ll leave extending this template with additional sections, a balance sheet, and a cash flow statement as an exercise to the reader! If you do, please send it to me at <a href="mailto:ataussig@lsvp.com" rel="noopener" dir="ltr">natalie@lsvp.com</a> and I would love to look it over. We’ll update this post with the extended model if it is exceptionally good.</p><p data-selectable-paragraph="" dir="ltr"><em dir="ltr">A special thanks to Adam Smith, </em><a rel="noopener follow" href="https://medium.com/@merci" dir="ltr"><em dir="ltr">Merci Grace</em></a><em dir="ltr">, </em><a rel="noopener follow" href="https://medium.com/@ataussig" dir="ltr"><em dir="ltr">Alex Taussig</em></a><em dir="ltr">, </em><a rel="noopener follow" href="https://medium.com/@bradtwohig" dir="ltr"><em dir="ltr">Brad Twohig</em></a><em dir="ltr">, and </em><a rel="noopener follow" href="https://medium.com/@astasia" dir="ltr"><em dir="ltr">Astasia Myers</em></a><em dir="ltr"> for reading drafts of this post.</em></p><h1 data-selectable-paragraph="">Appendix: a step-by-step guide for this model</h1><h2 data-selectable-paragraph="">Legend</h2><UL><LI>Blue: inputs</LI><LI>Blue with yellow shading: key model driver inputs</LI><LI>Black: values calculated by an equation</LI><LI>Purple: references to another cell on the same sheet</LI><LI>Green: includes references to cells on another sheet</LI></UL><h2 data-selectable-paragraph="">I. Bottoms Up Model tab</h2><p data-selectable-paragraph="">Inputs in this model can be entered in Columns A–W, which are variables that are used to calculate the Annual roll up in Columns Y–AC.</p><h2 data-selectable-paragraph="">1A. AE Rep Productivity by Segment</h2><UL><LI>In Column D–G, input historical New and Churned AEs for SMB in Rows 7–8, Mid Market in Rows 19–10, and Enterprise in Rows 32–33.</LI><LI>Column A in Rows 11–39 are inputs for expected ramp after 1, 2, 3, or 4 quarters after the AE was hired.</LI><LI>In Column D–G, input historical Quota per AE and expected Productivity Factor for each segment for SMB in Rows 14–15, Mid Market in Rows 27–28, and Enterprise in Rows 41–42.</LI><LI>The yellow shaded cells in Column H–W are inputs for assumptions of New AEs to be hired, expected Churn of AEs, Quotas, and Productivity Factors.</LI></UL><h2 data-selectable-paragraph="">1B. Inside Sales Rep Productivity</h2><UL><LI>In Column D–G, enter the historical number of Inside Sales Reps by Region in Rows 45–47 and Trials generated per Inside Sales Rep in Rows 53–55.</LI><LI>In Column H–W, enter the expected number of Inside Sales Reps by Region in Rows 45–47 and Trial generated per Inside Sales Rep in Rows 53–55.</LI><LI>Based on the total number of trials and historical conversion of trials to paid accounts, enter the historical Quarterly Conversion Rate of trials to paid accounts in Cells D62–G62. Then, input the forecast Quarterly Conversion Rate in Cells H62–W62. Typically, this should reflect a trend similar to the historical conversion rates assuming the sales process is consistent going forward.</LI></UL><h2 data-selectable-paragraph="">1C. Average ACV by Segment</h2><UL><LI>In Row 66, input the expected trend of Average ACV for New Accounts Converted From Trials in Columns H–W.</LI><LI>In Row 67–69, input the expected trend of Average ACV for New Accounts for each segment in Columns H–W.</LI><LI>Rows 73–77 automatically calculate Average ACV across All Accounts based on Ending ARR (Rows 125–130) divided by Ending Accounts (Rows 94–98) for each segment.</LI></UL><h2 data-selectable-paragraph="">1D. Accounts</h2><p data-selectable-paragraph="">In Rows 80–98, there are sections for New Accounts, Cancelled Accounts, and Ending (Total) Accounts.</p><UL><LI>Rows 80 represents New Accounts from Trials, which are referenced from Row 63 which shows calculated New Accounts based on Trial Conversion Rates in the section above.</LI><LI>In Rows 81–83 from Columns D–G, input the historical number of New Accounts for each segment. In Columns H–W, the expected number of New Accounts is calculated as New ARR (Section 1A) divided by Average ACV per New Account (Section 1C).</LI><LI>In Rows 87–90, input the historical and project number of Cancelled Accounts for each segment. An alternative method to modeling Cancelled (aka Churned) Accounts is to drive off an expected Churn Rate % of Ending Accounts from the prior quarter. (I encourage you to modify this section for extra credit!)</LI><LI>In Cells A94–A97, enter the Ending Accounts for each segment as of the end of 2017, which is before the time axis of this model starts.</LI><LI>Rows 94–97 from Columns D–W automatically calculating Ending Accounts by adding New and Cancelled Accounts.</LI></UL><h2 data-selectable-paragraph="">1E. ARR by Segment</h2><p data-selectable-paragraph="">In this section, input historicals in Columns D–G. Forecasts of New, Expansion, and Churn ARR by Segment are in Columns H–W.</p><UL><LI>Rows 101–103 reference the New ARR from each segment based on the “AE Rep Productivity by Segment” section.</LI><LI>Row 105 shows New ARR from Trials, which is calculated by the number of expected trials (Row 66) times Average ACV per New Account from Trials (Row 80).</LI><LI>Rows 109–111 calculates the Expansion ARR based on Ending ARR from 4 quarters prior (Rows 125–127) multiplied by the Upsell % in Cells A109–A111.</LI><LI>Row 113 calculates the Expansion ARR from Trials, which is calculated by Ending ARR from 4 quarters prior (Rows 129) multiplied by the Upsell % in Cell A113.</LI><LI>Rows 117–119 calculates the Churn ARR based on Ending ARR from 4 quarters prior (Rows 125–127) multiplied by -1 * Churn % in Cells A117–A119.</LI><LI>Row 121 calculates the Churn ARR from Trials based on Ending ARR from 4 quarters prior (Rows 129) multiplied by -1 * Churn % in Cell A121.</LI></UL><h2 data-selectable-paragraph="">1F. Sales Expense by Segment</h2><UL><LI>Row 133 represents the estimated cost to hire Inside Sales Reps, which is the average salary of an Inside Sales Rep (Cell A133) multiplied by Ending Inside Sales Reps in Row 50.</LI><LI>Row 134–136 represents the estimated cost to hire AEs, which is the average salary of AEs by Segment (Cells A134–A136) multiplied by Ending Reps in Row 9 (SMB), Row 21 (Mid Market), and Row 34 (Enterprise).</LI></UL><h2 data-selectable-paragraph="">II. Next tab: SaaS Metrics</h2><p data-selectable-paragraph="">A review of how the SaaS metrics are calculated on this tab:</p><UL><LI>Annual Net Retention Rate: this calculates the average Net ARR growth expected in 1 year across cohorts. We calculate this by adding the last 4 quarters of Expansion ARR minus Churn ARR, which is total Net Expansion ARR. Then, divide by Ending ARR from 4 quarters prior and add 1 to the end result. This is an approximation for Annual Net Retention Rate, but a more accurate way to represent this metric is by averaging 12-month Net Retention by each monthly cohort.</LI><LI>Annual ARR Churn Rate: this divides Churn ARR by the Ending ARR from the quarter prior, multiplied by -4. This definition represents Gross Churn ARR which ≥ 0%.</LI><LI>Annual Logo Churn Rate: this is calculated as 1 – (Gross Logo Retention in the Quarter)⁴.</LI></UL><h2 data-selectable-paragraph="">III. Last tab: Summary P&amp;L</h2><UL><LI>Revenue is approximated as a % of Ending ARR.</LI><LI>Gross Profit is based on the historical trend of Gross Margin % multiplied by Revenue.</LI><LI>Sales Expense is estimated based on Section 1F. Sales Expense by Segment in the Bottoms Up Model.</LI><LI>Marketing Expense is equal to the total Sales &amp; Marketing Expense calculated on the SaaS Metrics tab minus Sales Expense.</LI><LI>R&amp;D, G&amp;A and Other operating expenses are based on assumptions of margins in Rows 22–23.</LI><LI>Estimated Cash Flow / (Burn) references Operating Income (Row 26).</LI></UL>

When I joined Lightspeed, a founder of a Series B SaaS startup asked me: “Natalie — since you meet growth stage SaaS companies every day, how many sales reps should I hire to hit my ARR targets?” I…

marketingandsales

Marketing & Sales

business

Business

startup

Startups

<p>When I joined Lightspeed, a founder of a Series B SaaS startup asked me: “Natalie — since you meet growth stage SaaS companies every day, how many sales reps should I hire to hit my ARR targets?” </p>

<p>I started to get this question fairly often from SaaS founders beginning to hit their stride with a repeatable sales motion. When there is no CFO in place, planning out a sales-driven ARR forecast that takes into account a mix of new and ramped sales reps with various quotas can get difficult. </p>

<p>For founders who want to plan their budget properly as it relates to projections, we suggest modeling ARR based on sales rep productivity because it closely follows how a SaaS business operates. When we dig into SaaS businesses with a repeatable go-to-market, we often create an operating model based on hiring plan forecasts and expected sales rep quota attainment. As investors in numerous scaling SaaS companies (<a href="https://carta.com/">Carta </a>, <a href="https://seismic.com/">Seismic </a>, <a href="https://www.betterup.co/">BetterUp </a>, <a href="https://tripactions.com/">TripActions </a>, <a href="https://www.rubrik.com/">Rubrik </a>, to name a few), we found ourselves building this model enough times over that I created a template. </p>

<p>To help founders think through hiring and setting quotas, I built a basic template for which I have shared the link below. It includes three sections: the Bottoms Up Model, SaaS Metrics, and Summary P&L. The most important tab to focus on is the Bottoms Up Model which is driven by inputs such as sales reps, quotas, expected attainment, account growth, and other sales metrics. Below I describe each section of the model and how they interrelate, followed by an Appendix with a step-by-step guide. I’ve shared the link to the spreadsheet for you to download <strong>here </strong>: </p>

<p>Going from the top row down, start with entering historical and projected headcount of Account Executives (AEs). AEs are direct outbound sales reps with variable ramping schedules and quotas depending on their segment. A segment refers to the target customer base of each type of rep, usually ranging from SMB to Mid Market to Enterprise. </p>

<p>There is also a section to help plan for Inside Sales Reps who answer inbound requests from potential customers. This portion only reflects the ending number of inside sales reps and does not include separate sections for new and churned reps. </p>

<p>In this case, the startup would offer potential customers a trial before committing to a contract and the Inside Sales Rep would manage the customer relationship. We understand this may not be needed for every SaaS startup, but created this section in case it is helpful. </p>

<p>In our model, we use inputs to drive trends of average Annual Contract Value (ACV) per Account over time. As a quick sanity check while working on this section: if a SaaS business is trying to sell more into the large enterprise segment, for example, it should see average ACV increase over time. Conversely, if a SaaS business selling more to the SMB or focusing on self-service (which tend to be smaller accounts), then it should expect ACV to decrease over time. </p>

<p>Our model includes sections for New Accounts and Ending (Total) Accounts which are both calculated, and Cancelled Accounts which are inputs. You may prefer to calculate Cancelled Logos based on an assumption of Logo Churn Rate, but we used inputs for the purposes of this template. </p>

<p>For a SaaS startup that sells to different segments, we include the split of ARR across segments to understand the effectiveness of the go-to-market (GTM) strategy among account types. This section shows New, Expansion, Churn, and Total ARR by Segment and from Trials. </p>

<p>For the inputs in the leftmost column (Column A), the yellow shaded cells are assumptions for annualized Upsell and Churn rates. We recommend using the last year’s historical annualized gross churn and upsell rates as a conservative estimate. </p>

<p>Based on the hiring plan in Section 1A (AE Rep Productivity by Segment), our model also estimates expenses to hire sales reps using average salaries. Note that we did not include commissions in our template as it may vary, but we encourage you to add an additional section to include sales commissions if it applies to your startup. </p>

<p>We include a dashboard of SaaS metrics to show a summary of how all the assumptions in the Bottoms Up Model affect your SaaS metrics. As a founder, you might want to know what SaaS metrics are realistically achievable based on a proposed hiring plan and productivity goals. A tab like this SaaS metrics dashboard will help you benchmark against companies in your industry. </p>

<p>Below the dashboard, you’ll see a section that calculates Sales & Marketing Expense based New Bookings from the Bottoms Up Model divided by Sales Efficiency ratio assumptions. For the purposes of this template, we assume the Sales Efficiency ratio calculated from the Sales & Marketing Expense in the same quarter. The inputs for future projections of sales efficiency should follow the trend of the historical Sales Efficiency ratio. </p>

<p>Since we recognize that companies with longer sales cycles may use Sales & Marketing from the prior quarter to calculate Sales Efficiency ratio, we recommend choosing the best method that fits your startup. </p>

<p>In our model, Revenue is approximated as a % of Ending ARR. If a SaaS company is 1) completing implementation to go live in a consistent window of time across customers and 2) has contract terms for all their customers, Revenue should be approximately proportional to ARR. We recommend that you make assumptions that are based on the trends of historical data and conditions (1) & (2) above. </p>

<p>For R&D, G&A and Other operating expenses, our model assumes a % of Revenue to calculate these line items. A more detailed model might calculate these operating expenses based on ratios of managers and directors, and we encourage readers to extend the template in this regard. </p>

<p>We’ve also included a section for Estimated Cash Flow / (Burn), which references the row for Operating Income (Row 26) in our model. Because software companies tend to have low capital expenditure, Operating Income can be a close approximation for Cash Burn. This is not exact but can be directionally helpful when considering how much money to raise for your growth round or when asked for operating cash burn if your startup is not yet profitable. </p>

<p>If you’re a founder of a SaaS startup in the phase of scaling your go-to-market strategy, you’ve probably thought of parts of this model before. Hopefully, this template for SaaS operating model is a swiss army knife in your toolbox of financial planning for your startup. </p>

<p>I’ll leave extending this template with additional sections, a balance sheet, and a cash flow statement as an exercise to the reader! If you do, please send it to me at <a href="mailto:ataussig@lsvp.com">natalie@lsvp.com </a>and I would love to look it over. We’ll update this post with the extended model if it is exceptionally good. </p>

<p><em>A special thanks to Adam Smith, </em><a href="https://medium.com/@merci"><em>Merci Grace </em> </a><em>, </em><a href="https://medium.com/@ataussig"><em>Alex Taussig </em> </a><em>, </em><a href="https://medium.com/@bradtwohig"><em>Brad Twohig </em> </a><em>, and </em><a href="https://medium.com/@astasia"><em>Astasia Myers </em> </a><em>for reading drafts of this post. </em> </p>

Appendix: a step-by-step guide for this model

<ul><li>Blue: inputs </li><li>Blue with yellow shading: key model driver inputs </li><li>Black: values calculated by an equation </li><li>Purple: references to another cell on the same sheet </li><li>Green: includes references to cells on another sheet </li> </ul>

<p>Inputs in this model can be entered in Columns A–W, which are variables that are used to calculate the Annual roll up in Columns Y–AC. </p>

<ul><li>In Column D–G, input historical New and Churned AEs for SMB in Rows 7–8, Mid Market in Rows 19–10, and Enterprise in Rows 32–33. </li><li>Column A in Rows 11–39 are inputs for expected ramp after 1, 2, 3, or 4 quarters after the AE was hired. </li><li>In Column D–G, input historical Quota per AE and expected Productivity Factor for each segment for SMB in Rows 14–15, Mid Market in Rows 27–28, and Enterprise in Rows 41–42. </li><li>The yellow shaded cells in Column H–W are inputs for assumptions of New AEs to be hired, expected Churn of AEs, Quotas, and Productivity Factors. </li> </ul>

<ul><li>In Column D–G, enter the historical number of Inside Sales Reps by Region in Rows 45–47 and Trials generated per Inside Sales Rep in Rows 53–55. </li><li>In Column H–W, enter the expected number of Inside Sales Reps by Region in Rows 45–47 and Trial generated per Inside Sales Rep in Rows 53–55. </li><li>Based on the total number of trials and historical conversion of trials to paid accounts, enter the historical Quarterly Conversion Rate of trials to paid accounts in Cells D62–G62. Then, input the forecast Quarterly Conversion Rate in Cells H62–W62. Typically, this should reflect a trend similar to the historical conversion rates assuming the sales process is consistent going forward. </li> </ul>

<ul><li>In Row 66, input the expected trend of Average ACV for New Accounts Converted From Trials in Columns H–W. </li><li>In Row 67–69, input the expected trend of Average ACV for New Accounts for each segment in Columns H–W. </li><li>Rows 73–77 automatically calculate Average ACV across All Accounts based on Ending ARR (Rows 125–130) divided by Ending Accounts (Rows 94–98) for each segment. </li> </ul>

<p>In Rows 80–98, there are sections for New Accounts, Cancelled Accounts, and Ending (Total) Accounts. </p>

<ul><li>Rows 80 represents New Accounts from Trials, which are referenced from Row 63 which shows calculated New Accounts based on Trial Conversion Rates in the section above. </li><li>In Rows 81–83 from Columns D–G, input the historical number of New Accounts for each segment. In Columns H–W, the expected number of New Accounts is calculated as New ARR (Section 1A) divided by Average ACV per New Account (Section 1C). </li><li>In Rows 87–90, input the historical and project number of Cancelled Accounts for each segment. An alternative method to modeling Cancelled (aka Churned) Accounts is to drive off an expected Churn Rate % of Ending Accounts from the prior quarter. (I encourage you to modify this section for extra credit!) </li><li>In Cells A94–A97, enter the Ending Accounts for each segment as of the end of 2017, which is before the time axis of this model starts. </li><li>Rows 94–97 from Columns D–W automatically calculating Ending Accounts by adding New and Cancelled Accounts. </li> </ul>

<p>In this section, input historicals in Columns D–G. Forecasts of New, Expansion, and Churn ARR by Segment are in Columns H–W. </p>

<ul><li>Rows 101–103 reference the New ARR from each segment based on the “AE Rep Productivity by Segment” section. </li><li>Row 105 shows New ARR from Trials, which is calculated by the number of expected trials (Row 66) times Average ACV per New Account from Trials (Row 80). </li><li>Rows 109–111 calculates the Expansion ARR based on Ending ARR from 4 quarters prior (Rows 125–127) multiplied by the Upsell % in Cells A109–A111. </li><li>Row 113 calculates the Expansion ARR from Trials, which is calculated by Ending ARR from 4 quarters prior (Rows 129) multiplied by the Upsell % in Cell A113. </li><li>Rows 117–119 calculates the Churn ARR based on Ending ARR from 4 quarters prior (Rows 125–127) multiplied by -1 * Churn % in Cells A117–A119. </li><li>Row 121 calculates the Churn ARR from Trials based on Ending ARR from 4 quarters prior (Rows 129) multiplied by -1 * Churn % in Cell A121. </li> </ul>

<ul><li>Row 133 represents the estimated cost to hire Inside Sales Reps, which is the average salary of an Inside Sales Rep (Cell A133) multiplied by Ending Inside Sales Reps in Row 50. </li><li>Row 134–136 represents the estimated cost to hire AEs, which is the average salary of AEs by Segment (Cells A134–A136) multiplied by Ending Reps in Row 9 (SMB), Row 21 (Mid Market), and Row 34 (Enterprise). </li> </ul>

<p>A review of how the SaaS metrics are calculated on this tab: </p>

<ul><li>Annual Net Retention Rate: this calculates the average Net ARR growth expected in 1 year across cohorts. We calculate this by adding the last 4 quarters of Expansion ARR minus Churn ARR, which is total Net Expansion ARR. Then, divide by Ending ARR from 4 quarters prior and add 1 to the end result. This is an approximation for Annual Net Retention Rate, but a more accurate way to represent this metric is by averaging 12-month Net Retention by each monthly cohort. </li><li>Annual ARR Churn Rate: this divides Churn ARR by the Ending ARR from the quarter prior, multiplied by -4. This definition represents Gross Churn ARR which ≥ 0%. </li><li>Annual Logo Churn Rate: this is calculated as 1 – (Gross Logo Retention in the Quarter)⁴. </li> </ul>

<ul><li>Revenue is approximated as a % of Ending ARR. </li><li>Gross Profit is based on the historical trend of Gross Margin % multiplied by Revenue. </li><li>Sales Expense is estimated based on Section 1F. Sales Expense by Segment in the Bottoms Up Model. </li><li>Marketing Expense is equal to the total Sales & Marketing Expense calculated on the SaaS Metrics tab minus Sales Expense. </li><li>R&D, G&A and Other operating expenses are based on assumptions of margins in Rows 22–23. </li><li>Estimated Cash Flow / (Burn) references Operating Income (Row 26). </li> </ul>

Introducing the Lightspeed SaaS Operating Model

Modeling ARR and sales: Start by building a Bottoms Up Model

SaaS Metrics

Shelby Curry's ideas are part of this journey:

Related collections

Similar ideas