Building a Formula with Assumptions & 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

1. Navigate to Assumptions & KPIs.

2. (Optional) Create a new tab (e.g., “Services”) to organize your logic.

3. Right-click → Add Line.

4. 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:

1. Add line: Billable Hours (Manual Entry, Whole Number).

2. Add line: Billing Rate (Manual Entry, Average, Decimal).

3. Add line: Billing Revenue → choose Formula Method.

   • Use Formula Builder to multiply Billable Hours × Billing Rate.

   • Click Apply to save.

4. 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:

1. Add assumptions for:

   • Contracts Sold

   • Price per Contract

   • Bookings (Contracts × Price).

2. Add % allocation lines (Manual Entry, set as Percentages).

3. Create formula lines to allocate ⅓ of revenue from:

   • Current month bookings.

   • Prior month bookings.

   • Two months prior.

4. Add a final line = sum of 3 above = Earned Revenue.

5. Link Earned Revenue to the Income Statement revenue account.

6. Create a line for Change to Deferred Revenue = Bookings – Earned Revenue.

7. 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:

1. Add assumptions:

   • New Customers

   • Lost Customers

2. Add line: Total Customers (Balance).

3. Formula: New Customers – Lost Customers.

4. 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.