Let's break down the components of a formula
Formulas involve three components:
Variables – Variables are the inputs or metrics your financial model is built on. Whether it’s your number of customers, cost of goods sold (COGS), salaries, or any other metrics, they’re all variables that determine the current and future financial performance of your business. You can use custom variables and/or system variables in your Finmark formulas. Variables are calculated at a monthly level.
Offsets – If you are building a formula to a past or future month, you can use offsets to shift the formula timeline. Add or subtract up to 60 months in either direction using a positive or negative number (of months) within a set of square brackets. For example, you can suffix a variable with “[3]” to tailor a formula to the third month in the future with respect to the current month. Or, use “[-3]” to tailor it to the third previous month in the past.
Operators – You can use basic arithmetic operators, such as addition “+”, subtraction “-”, multiplication “*”, and division “/”. You can also use parentheses “(...)” to further customize your formulas. If you use multiple sets of parenthetical combinations in a single formula, then they are calculated from left to right (PEMDAS).
Build a formula
Now, let’s walk through building a formula step by step.
1. Determine your variable
First, determine your variable. You can pick from standard system variables curated by Finmark, or create custom variables.
2. Set your timeframe
3. Add an operator
Additional resources:
Variables & Formulas Blog Post
REVENUE
Revenue Stream
Scenario
Say you want to forecast the number of premium products to be sold to new customers in a month. The forecast depends on the number of account executive selling the premium product, the number of account managers (who also meet with new customers buying the premium product, but less frequently than account executives) and the number of initial meetings with prospects in a month. Your formula may look like this
Formula
“(Headcount.AccountExecutivePremium.Total + (Headcount.AccountManager.Total[-1] + Headcount.AccountManager.Total) / 2 * 0.25) * 10”
OK, so let’s break this down …
Price Plan
Scenario
Say you want to forecast the price of a product that changes over time. Suppose the price of a product increases monthly by 0.5% over the average price over the prior to months.You use a custom variable to calculate the price. Your formula may look like this:
Formula
“(Custom.WidgetPrice[-1] + Custom.WidgetPrice[-2] ) / 2 * 1.005”
OK, so let’s break this down …
Churn
Scenario
Say you want to forecast the churn of a product that changes over time. Suppose the churn is 3% of the total number of new customers that have purchased a specific product within the 3 months. Your formula may look like this:
Formula
“(Customers.WidgetNew[-1] + Customers.WidgetNew[-2] + Customers.WidgetNew[-3]) * 0.03”
OK, so let’s break this down …
