Building a Formula with Assumptions & KPIs
Learn how to create a formula that is based off of your Assumptions and KPIs
The Assumptions & KPIs tab in combination with the Formula projection method allows you to create custom calculations that connect non-financial drivers to your Income Statement and Balance Sheet.
This method is ideal for:
- 
Bottom-up forecasting 
- 
Building driver-based models 
- 
Creating custom KPIs 
- 
Accurately linking operational metrics to financial results 
1️⃣ Why Use the Formula Builder?
Formula Builder lets you:
- 
Construct custom calculations using financial and non-financial drivers. 
- 
Link assumptions to accounts for dynamic updates. 
- 
Create both simple multiplications (e.g., Units × Price) and complex formulas with conditions, accumulations, or multi-step logic. 
2️⃣ Creating Assumptions
- 
Navigate to Assumptions & KPIs. 
- 
(Optional) Create a new tab (e.g., “Services”) to organize your logic. 
- 
Right-click → Add Line. 
- 
Define: - 
Description 
- 
Projection Method (Manual Entry, Growth, Average, etc.) 
- 
Full Year Column setting (Total, Average, or Balance) 
- 
Number format (Whole number, Decimal, or Percentage) 
 
- 

3️⃣ Example 1: Revenue from Billable Hours
Goal: Forecast revenue = Billable Hours × Billing Rate
Steps:
- 
Add line: Billable Hours (Manual Entry, Whole Number). 
- 
Add line: Billing Rate (Manual Entry, Average, Decimal). 
- 
Add line: Billing Revenue → choose Formula Method. - 
Use Formula Builder to multiply Billable Hours × Billing Rate. 
- 
Click Apply to save. 
 
- 
- 
Link Billing Revenue to the appropriate Income Statement account using Link to Assumptions & KPIs. 

4️⃣ Formula Builder Features
Within Formula Builder you can:
- 
Select accounts from the Income Statement/Balance Sheet. 
- 
Select assumption lines. 
- 
Use operators (+, –, ×, ÷, parentheses). 
- 
Add fixed values with Add Amount. 
- 
Apply functions (Average, Min, Max, IF/THEN logic). 
- 
Define Month-to-Use (current, prior, or subsequent month). 
- 
Choose Accumulate Amount (for running totals on Balance Sheet accounts). 
- 
Apply formulas to forecast, actuals, and historical periods. 

5️⃣ Example 2: Deferred Revenue Recognition
Goal: Recognize revenue from contracts paid upfront but earned over 3 months.
Steps:
- 
Add assumptions for: - 
Contracts Sold 
- 
Price per Contract 
- 
Bookings (Contracts × Price). 
 
- 
- 
Add % allocation lines (Manual Entry, set as Percentages). 
- 
Create formula lines to allocate ⅓ of revenue from: - 
Current month bookings. 
- 
Prior month bookings. 
- 
Two months prior. 
 
- 
- 
Add a final line = sum of 3 above = Earned Revenue. 
- 
Link Earned Revenue to the Income Statement revenue account. 
- 
Create a line for Change to Deferred Revenue = Bookings – Earned Revenue. 
- 
Link to Balance Sheet Deferred Revenue account with Accumulate Amount checked. 

6️⃣ Example 3: Running Total with Accumulate Amount
Goal: Project customer count in a SaaS model.
Steps:
- 
Add assumptions: - 
New Customers 
- 
Lost Customers 
 
- 
- 
Add line: Total Customers (Balance). 
- 
Formula: New Customers – Lost Customers. 
- 
Enable Accumulate Amount so monthly totals build on prior balances. 

7️⃣ Editing a Formula
- 
To edit, double-click or right-click on the formula line. 
- 
Select Edit Formula to reopen Formula Builder. 
- 
Update inputs, operators, or logic as needed. 
