Common Formulas

• Note that both Start date and End date are broken down by the Employee dimension.
• End date will have to have Empty Value setting of None (this is the default for variables formatted as Dates).
• The result is a flag, which returns a 1 for each month that the employee that is active with the company, and 0 when they are not.
• The Total of all Employees will reflect Total Headcount for each month for the company.

• The first part of the formula sets the bonus month to January (1).
• Start Date < date - 6 ensures employees have been with the company at least 6 months before receiving a bonus.
• Annual Salary * Bonus Target % results in the bonus amount. Multiplying by Headcount will ensure that the bonus is paid only if the employee is active (see Headcount formula above)
• Note that the Bonus Target % can be set at Department level, seniority, or any other level depending on how you map your dimensions.

• Simply set a per employee assumption (can be by department, etc.) as a flat rate
• Multiply that rate by your headcount

• Simply apply a % rate to the applicable cost base.
• Like all inputs, they can be split into dimensions for more granular approaches.

There are many approaches to seasonality in xP&A. The following two are the most used, but feel free to take an approach that best fits your needs. Both approaches use the concept of relative time so familiarize yourself with it if you have not already.

In order to understand your customers at a deeper level, you'll often want to track them on a more granular basis, using time-based cohorts. This is because customers can behave differently at different points in time, for example:

• Retention and expansion rates often follow a pattern depending on how many months they've been a customer.
• Churn rates may differ depending on what time of year a customer joined.
• Spend or engagement may be higher in initial months after sign-up, then drop off.
• If you have a new onboarding flow, more customers might retain in the 2nd month, (vs. when there was no new onboarding flow).
   

To forecast on this cohort basis, you need to be able to split your customers into different cohorts, so you can apply the correct assumptions (e.g. churn, retention etc) at different points in time.

In xP&A, cohorts act as a dimension that reflects the time period of the model. For example, if a monthly model goes from Jan '22 to Dec '22, adding cohorts would add 12 items, one for each month in the model.

• By putting cohort as the time modifier of the New sign-ups variable, we are telling xP&A to use the new sign-ups for January in the Jan cohort, February in the Feb cohort, etc.
• By using t-cohort as the time modifier of the Activations variable, we are telling xP&A to use the 1st month activation rate (i.e. 45%) for the first month of the cohort (Jan'22 for the Jan'22 cohort), the second activation rate (25%) for the second month of that cohort (Feb'22 for the Jan'22 cohort), and so on.

You can connect your historic cohort data to xP&A via:

• A spreadsheet. If you go the spreadsheet route, there are two formats that are compatible with xP&A: Time-Series format and Transactions format. For both, an example can be found below.
• Directly from your data warehouse

This is an example of a Time-series format spreadsheet for cohorts:

• Each row represents a single variable for a single dimension item.
• The columns can be split into 3 section: Variable Names, Dimensions, and Values
  • The first section is just the first column:
    • It must contain the Variable Names.
    • In the example above, column A contains the variable Total Billed. In this case, it was the only variable.
  • The second section proceeds the first column:
    • It contains the Dimensions.
    • The first row must have the name of the dimension and the rows below may have the name of an item in that dimension.
    • In the example above, column B contains the Cohort dimension. In this case, it was the only dimension. The cohort names (e.g. August 2018) must be formatted as Dates to be recognized by xP&A.
  • The third section follows after all of the columns involved in the second section:
    • It contains the Values.
    • The first row must contain the dates, and the rows below are the values themselves (must be number format, not text)
    • In the example of above, this section ranges from column C to column G.

Here are some common SaaS metrics you might want to track, and how to build them in xP&A:

Customer Acquisition Cost calculates how much it costs to acquire a Customer in the period. Calculated using direct Marketing Spend and Sales/Marketing Staff Costs in the appropriate period.

For example, let’s say there’s $1m Marketing Spend in Jan 22 and then $2m in Feb 22 and Mar 22. Marketing Spend takes 2 months to flow down the funnel and convert to Customers. In Mar 22, there are 4 new customers, therefore CAC in Mar 22 should be $1m/4 = $250k rather than using the Marketing Spend in Mar 22, which would result in $2m/4 = $500k, as this is not the relevant amount which determines the New Customers gained in the period.

xP&A’s Time Modifiers make implementing this logic very easy! If we use the same logic as above in regards to Payroll Cost for Sales and Marketing Staff, we can produce a formula like the image below. The formula takes Payroll Costs for the Sales and Marketing Department modified by the amount of Time spent in the funnel. This is then divided by the the New Customers in the Period to arrive at the CAC.

Customer Acquisition Payback calculates the number of periods required for a Customer to produce enough Gross Profit to pay back their respective CAC. The shorter the Payback Period the better your business is performing.

Therefore, once you have calculated CAC (see above) you can calculate your payback by finding your ARPU (Average Revenue per Account) and multiplying this by your Gross Profit Margin to find the contribution per Customer. Taking CAC and dividing by this Contribution, calculates how many periods are required for the customer to payback their CAC.

Annual Contract Value/Customer Acquisition Cost gives indication of profitability on new contracts. After calculating CAC (see above), take the Annual Contract Value in the period and divide by CAC in the period to produce this metric.

The Magic Number metric is designed to measure how efficient and effective your Sales and Marketing spend are in a specific time period.

It is calculated by taking Net New ARR or New ARR + Expansion ARR - Churned ARR and dividing it by CAC.

The following general advice is given depending on your Magic Number:

• Magic Number < 0.5: A magic number this low indicates that something is wrong with your business model. Whether it be high costs relative to performance or perhaps you have not achieved product-market fit. This is not the time to invest in Sales and Marketing, focus should be elsewhere.
• 0.5 < Magic Number < 0.75: This is generally considered to be the main threshold for the magic number. If you’re around the 0.75 mark then your sales efficiency is approaching market norms. This is a time to evaluate whether or not to increase Sales and Marketing expense and depends on the specific context of your business. ‍
• Magic Number > 0.75: A magic number over 0.75 indicates that this is the time to invest in Sales and Marketing. You likely have a proven product-market fit and solid CAC payback periods and this is the time to take advantage.

Lifetime Value is a metric that measures, you guessed it, the Lifetime Value of a cohort of New Customers. The higher your LTV the better.

You can calculate LTV by taking (Average Order Size in the Period * Average # of Orders in the Period * Contract Length) / Churn Rate. This gives LTV for an average customer in the cohort.

Burn Multiple calculates how much Cash is being used to generate new business and is used as a guide on sustainability of the current business model. The higher the Burn Multiple, the more the start-up is burning to achieve each unit of growth. The lower the Burn Multiple, the more efficient the growth is.

Calculated by taking -Net Operating Cashflow divided by Net New ARR or (New ARR + Expansion ARR - Churned ARR).

The table below gives some context to how your Burn Multiple shapes up:

Net Retention Rate is a core tracked metric for most SaaS businesses. It gives an indication of the impact of Customer Retention and Upselling on the business.

Calculated by taking (Beginning ARR - Churned ARR + Expansion ARR) / Beginning ARR.

A solid SaaS company would expect to have a Net Retention Rate of at least 100% with anything above 110% considered exceptional.

Customer Retention is a key metric for any business and is measured by how many Customers at the Start of the Period remain at the End of the Period.

It is calculated by taking (Total Customers at End of Period - New Customers in Period) / Customers at the Beginning of Period.

Of course you cannot retain more than 100% of your customers, the closer you are to 100% the better your business is performing.

For indirect cashflow statements, make sure every balance sheet item is accounted for in the cashflow statement. As a general rule:

• For Assets, cash change is asset item|previous - asset item|current
• For Liability and Equity, cash change is the opposite (current - previous).

The following items assume a connection to a balance sheet: