Common Formulas in Lucanet xP&A

This example calculates Active Headcount in Lucanet xP&A by comparing each employee's Start date and End date to the current date.

As long as your employees are mapped to a Department, you can reference any variable broken down by Employee and grouped by Department(or any other mappings).

Returns a 1 on the employees start date, useful for things like New Hire Capex (e.g. Laptops).

You can create if then statements based off dimension items, xP&A will automatically apply the correct assumptions to the formula. No need to set any filters!

There are many approaches to seasonality in Lucanet 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:

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.

You can access cohorts by explicitly adding the Cohort dimension in the variable or by referencing cohorts in the formula. Below is a simple example of using cohorts:

Example Inputs:

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

The one rule that must be satisfied is that the dates in the Cohort column must fall between the range of the Date column.

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

This is an example of a Transactions format spreadsheet for cohorts:

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:

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:

The following items assume a connection to a balance sheet: