Models Formula Reference
Here's a list of helpful commonly used pre-built functions to use in the formula section.
Rounding
Round
Rounds to the nearest whole number.
Round Up
rounds up to the nearest whole number. CEIL is another name for this function.
Round Down
Rounds down to the nearest whole number. FLOOR is another name for this function.
If / Then / Else
This formula is useful if you want to set a condition and have 2 results: one if the result is true and the second if the result is false.
Format is CONDITION ? RESULT_IF_TRUE : RESULT_IF_FALSE
Condition Syntax
Use the following syntax to create condition statements:
Use Case - Keeping Track of Actual Customers
In this example, we are using an if/then/else statement to keep our number of customers accurate with actual data and use it to forecast future customers. The "Actual Customers" is a list variable of our historical customers each month. This if/then/else statement is checking that if "Actual Customers" is greater than 0 in the current month, then use that value from the list for the formula "Customers" but if "Actual Customers" is equal to or less than 0, use the previous month's value for "Customers" plus the current month's expected value for "New Customers" minus the current month's expected value for "Churned Customers" to find the number expected customers in the month.
TipYou can apply the same logic to keep track of your New Customers and Churned Customers
Use Case - Utilizing Month Variable
In this example, we are using an if/then/else statement to track our expected customers after our product launch date. The "Month Value" is a prebuilt variable that outputs the current month and year. We created a date input for our product launch date and a number input for our expected number of new customers every month. Our if/then/else statement is checking if the current month is or after the "Product Launch Date", then use the value of "Monthly New Customers" but if the current month is before the the "Product Launch Date" then use 0 since we wouldn't have any customers before we launch our product.
Ranges and Aggregates
Ranges
Specify two arguments inside the brackets.
This example produces a list of all values of "Revenue" between the cell's month and 5 months before (inclusive). In order to do math with the results, you must use a helper function to turn the list into a single value. The dates used for ranges are relative to each cell, so they can't be used to compute running totals over all time. For that, use a running total formula like:
Sum
Returns the sum of a list of values.
Average
Returns the average (mean) of a list of values.
Median
Returns the median of a list of values.
Min
Returns the smallest of a list of values.
Max
Returns the largest of a list of values.
Date Functions
Quarterly
Returns a value only on the first month of each quarter, otherwise returns zero. Passing 2 or 3 as the second argument will cause it to return a value only on the 2nd/3rd month of each quarter.
Month Number
Returns the month for the referenced column as a number between 1 and 12
Month Value
Returns the month and year for the referenced column in the format YYYY-MM.
Days in Month
Returns the number of days in the month of the referenced column.
Weekdays in Month
Returns the number of weekdays in the month of the referenced column.
Months from Today
Returns the number of months between the current date and the date of the cell being calculated.
Hiring Plan Functions
Employee Count
The employee count for active employees on the hiring plan. Can reference the total or narrow down by department or role.
New Hires
The number of new hires on the hiring plan in a given month. Can reference the total or narrow down by department or role.
Total Base Salary
The sum of all employees' salary on the hiring plan. Can reference the total or narrow down by department or role.
Contractor Count
The contractor count for active contractors on the hiring plan. Can reference the total or narrow down by department or role.
Contractor New Hires
The number of contractor new hires on the hiring plan in a given month. Can reference the total or narrow down by department or role.
TipYou can also breakdown contractor by departments similar to the employee side.
Account Balance
References the account balance amounts from your chart of accounts on the Financial Report. You can reference the account balance of any category in the past (actuals) or future (forecasts).
For past months, use x - (n) to reference a number of previous months.
For future months, use x + (n) to reference a number of future months.
Time Variable
The time variables refer to the variables next to each of the inputs and formulas
Preview Month [x]
The "x" time variable refers to the current month of the model. You can also reference the future or past months by using x+(n) or x-(n) as used in the Account Balance financial report function.
Current Month [c]
The "c" time variable refers to the current month as of today.
For this example, we are referencing the current month's Revenue Subscription, but we can also use c+(n) or c-(n) to reference the future or past month's Revenue Subscription.
Here we were able to reference the next month's expected Revenue Subscription by using the c+1 time variable.
Year to Date [ytd]
The "ytd" time variable means "year to date." Similar to the Range function, this ytd time variable will also display "Multiple Values."
In this example, we utilized the ytd time variable to reference each month's revenue subscription from January 2021 to September 2021 and then used the Average function to find the Average Revenue within this timeframe.
TipYou can adjust for Fiscal Years on the YTD time variable by adding a number {2-12}. For example, if your fiscal year ends in June 30th, use ytd7 to calculate year to date values starting from July.