--- title: "Common Formulas" description: "The following topic give you a taste of common formulas used in Lucanet xP&A, across different themes/use cases." source_url: https://support.lucanet.cloud/en/documentation/xp-a---extended-planning-and-analysis/modeling-your-data/create-andedit-formulas/common-formulas- language: en last_updated: 2023-08-16 --- # Common Formulas ## Overview The following article provides an overview of common formulas used in xP&A, across different themes and use cases. ### Headcount ### Active Headcount Active headcount - Note that both **Start date** and **End date** are broken down by the **Employee** dimension. - **End date** will have to have [Empty Value](https://support.lucanet.cloud/en/documentation/xp-a---extended-planning-and-analysis/modeling-your-data/create-edit-variables.md) 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. ### Headcount by Department Headcount per Department As long as your employees are mapped to a **Department**, you can reference any variable broken down by **Employee** and [grouped by](https://support.lucanet.cloud/en/documentation/xp-a---extended-planning-and-analysis/modeling-your-data/create-andedit-formulas/variable-modifiers.md) **Department**(or any other mappings). ### New Hire New hire Returns a **1** on the employees **start date**, useful for things like New Hire Capex (e.g. Laptops). ### Bonus Bonus - 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. ### Flat Fee Per Person Costs Flat fee per person costs - Simply set a per employee assumption (can be by department, etc.) as a flat rate - Multiply that rate by your headcount ### Percentage of Salary Costs Percentage of salary costs - Simply apply a **% rate** to the applicable cost base. - Like all inputs, they can be split into dimensions for more granular approaches. ### Geography Specific Calculations Geography specific calculations You can create [if then statements based off dimension items](https://support.lucanet.cloud/en/documentation/xp-a---extended-planning-and-analysis/modeling-your-data/create-andedit-formulas/if-statements-in-formulas.md), xP&A will automatically apply the correct assumptions to the formula. No need to set any filters! ### Seasonality 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](https://support.lucanet.cloud/en/documentation/xp-a---extended-planning-and-analysis/modeling-your-data/working-with-time.md) so familiarize yourself with it if you have not already. ### Seasonality as a % of Annual Total - In this approach you can create a **Seasonality** variable that applies a % of the annual value over 12 months (7 days, 30 days, etc) so that adding Jan-Dec would add up to 1 or 100%. - Make sure to set up the variable as relative time. Seasonality as a % of annual total - Then use another input variable for your **Annual Target**. - The resulting formula below uses **month - 1** as the [time modifier](https://support.lucanet.cloud/en/documentation/xp-a---extended-planning-and-analysis/modeling-your-data/create-andedit-formulas/variable-modifiers.md). The -1 aligns xP&A's [time step index](https://support.lucanet.cloud/en/documentation/xp-a---extended-planning-and-analysis/modeling-your-data/working-with-time.md) (which starts at 0), with xP&A's [month index](https://support.lucanet.cloud/en/documentation/xp-a---extended-planning-and-analysis/modeling-your-data/create-andedit-formulas/helper-variables.md) (1-12 for Jan-Dec). Note the **-1** is used because this model starts in **January**, if the model was to start in February, **-2** should be applied and so on for other month start dates. Seasonality with 'month-1' ### Seasonality as a Variance to the Average - The previous approach (% of annual total) divides an annual target throughout the year, whereas this approach adjusts monthly numbers for seasonality. - The **Seasonality** input in this case, should equal **100%** for “average” months and 120% or **90%** for above or below average months, respectively (and versus adding up to 100% for the year in the previous approach). Seasonality as a variance to the average ### Cohorts ### What is Cohort Modeling? 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. ### Cohorts in xP&A 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. Month cohorts 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: ### Cohort of Leads That Convert Into New Customers Cohort of leads that convert into New Customers **Example Inputs**: {% stepper %} {% stepper-step %} New sign-ups is **1,000** for our first month and grows at **5%.** {% /stepper-step %} {% stepper-step %} Activation of cohort uses [relative time](https://support.lucanet.cloud/en/documentation/xp-a---extended-planning-and-analysis/modeling-your-data/working-with-time.md) so that 1st month is **45%**, 2nd is **25%**, etc. Activation of cohorts Simply, this formula is saying "Signups multiplied by activation rate %" {% /stepper-step %} {% /stepper %} ### What is the "cohort" and "t-cohort" doing? - By putting cohort as the [time modifier](https://support.lucanet.cloud/en/documentation/xp-a---extended-planning-and-analysis/modeling-your-data/create-andedit-formulas/variable-modifiers.md) 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. {% info-box %} **Deeper dive on t-cohort:** Let's consider the Feb '22 cohort, in March '22, in a model that begins in January '22: - **cohort** is **0** for Jan '22, **1** for Feb '22, etc. - **t** is **0** for Jan '22, **1** for Feb '22, **2** for Mar '22 etc. - For our worked example, **t** is **2** and **cohort** is **1**. **t-cohort** will return **1**, so the 2nd month activation rate (**25%**) is applied. This is correct as Mar'22 is the 2nd month of my Feb'22 cohort. - If the month was instead Feb '22, then **t-cohort** would be **0 (1-1)** corresponding to the 1st month activation rate of **45%\ \ ** Note: **t** is a [helper variable](https://support.lucanet.cloud/en/documentation/xp-a---extended-planning-and-analysis/modeling-your-data/create-andedit-formulas/helper-variables.md) (also known as timestep or date). {% /info-box %} ### Importing Cohort Data 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](https://support.lucanet.cloud/en/documentation/xp-a---extended-planning-and-analysis/integrating-data.md) The one rule that must be satisfied is that the dates in the **Cohort** column must fall between the range of the **Date** column. ### Time-Series This is an example of a **Time-series** format spreadsheet for cohorts: Time series {% info-box %} [Click here to see a Google Sheets template in the time-series format](https://docs.google.com/spreadsheets/d/1mnuElYW-T-3f22q1FEL3kQiXbf5SsWuuSehtEy2R5W4/edit?usp=sharing) {% /info-box %} - 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.** ## Transactions Transactions {% info-box %} [Click here to see a Google Sheets template in the transaction format](https://docs.google.com/spreadsheets/d/1VWW6A69o2PjaXOBm2mnFWzLrWfBwQrhJ4-hseIkrc_0/edit#gid=0) {% /info-box %} This is an example of a **Transactions** format spreadsheet for cohorts: - The **first column** must have the name **Date**. - Simply add a column with the name **Cohort**. As above, the cohort names must be formatted as **Dates** to be recognized by xP&A. - The other columns can be for additional dimensions an/or data items. ### SaaS Metrics Here are some common SaaS metrics you might want to track, and how to build them in xP&A: ### Customer Acquisition Cost (CAC) **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](https://support.lucanet.cloud/en/documentation/xp-a---extended-planning-and-analysis/modeling-your-data/create-andedit-formulas/variable-modifiers.md) 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 Cost (CAC) ### CAC Payback **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. ### ACV/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. ### Magic Number 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 **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 **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: Burn multiple ### Net Revenue Retention **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 **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. ### Balance Sheet and Cashflow ### Current Year Earnings Current year earnings ### Retained Earnings Retained earnings ### Balance/Check Balance/Check ### Indirect Cashflow Statement 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: ### Ending Cashflow Ending cashflow ### Change in Working Capital Change in working capital {% info-box %} If you are looking for more fulsome templates, you can find them [here](https://www.causal.app/templates). {% /info-box %}