- About Quantrix
- Solutions
- Products
- Services
- Sample Models
- Learning
- Learning Overview
- Video Demos
- Webinar Series
- Quick Guide
- User Guides
- Modeler Notes
- Mortgage Modification Modeler Note
- Election Tax Model
- Marketing Response Curve Model
- Pivoting Data
- Quantrix in Corporate Budgeting
- Using Expressions in Quantrix
- Performing a Sales Analysis
- Indirect() w/Capex & Depreciation
- Analyzing ARM vs. Fixed Mortgages
- Integrated Financials Models
- Leveraging the @ and # Operators
- Using Circular References
- Converting Spreadsheets to Quantrix
- Using Functions with Black-Scholes
- Using Statistical Functions
- Using Quantrix in Capital Budgeting
- Working with Scenario Categories
- Whitepapers
- FAQ
- Support
- Store
Lesson Three: Recurrence Formulas
A recurrence (also known as recursion) formula is one that refers to cells based on their relationships with other cells using keywords such as [THIS], [PREV], [NEXT], [FIRST] and [LAST].
Here's an example: Quarter[THIS] = Quarter[PREV]*1.2. This formula says: "This quarter equals the previous quarter multiplied by 1.2".
New users commonly ask: "If there isn't a specific cell reference, how does Quantrix know to which cell "[THIS]" is referring?" The answer: [THIS] is a moving target. When the formula is computing Quarter2, [THIS] is Quarter2 and [PREV] is Quarter1. When computing Quarter3, [THIS] is Quarter3 and [PREV] is Quarter2. The next Illustration has an example recurring formula.
Goal of Formula: Assume that car sales will be the same each quarter.
Describe in Real Language: This quarter equals the previous quarter. Do not include Total or YTD.
Formula: Quarter[THIS]=Quarter[PREV]skip YTD, Total
It is likely that a growth rate factor needs to be incorporated into the formula. Taking that into consideration:
Goal of Formula: Assume that car sales will grow by 20% each quarter.
Describe in Real Language: This quarter equals the previous quarter multiplied by 1.2. Do not include Total or YTD.
Formula: Quarter[THIS]=Quarter[PREV] * 1.2 skip YTD, Total
Before going back to your model, take a minute to describe the goal and write out the formula you need on a piece of paper, as per the above example. Assume a quarterly product growth rate of 1.2, do not include Total or YTD.
Now return to the Sales Projection matrix in your Widgets file. Use the following steps to write a formula that assumes a monthly growth rate of 1.2 for all products.
- Step 1. Add formula field #3 to the Formula Editor.
- Click on formula 2 (it will turn blue) and hit ENTER. You could also use the "Insert Formula" button in the tool bar at the top of the screen.
- Double-click next to the "3." in the Formula Editor.
- Enter the formula, either by typing it in or by clicking on the Formula Bar items to add them to the formula. Hit ENTER.
- To format the number of decimals, use Format > Numbers in the menu bar or the format decimals button in the toolbar.
- Click File > Save.
