- About Quantrix
- Product Overview
- Quantrix Modeler
- The Quantrix Qloud
- In-Memory Analytics
- Enterprise Integration
- Sample Screenshots
- Customer Success
- Sample Models
- Learning Overview
- Video Demos
- Webinar Series
- Quick Guide
- Online Help
- 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
A Look at the Quantrix Integrated Financials Model
At the core of every business planning and budgeting exercise is a set of integrated financial statements. These financial statements allow senior management to understand the bottom line impact – both planned and actual - of internal decisions and external forces on the business. Successful planning and budgeting exercises require financial models which are robust and accurate. Just as important, financial models must be flexible to allow managers to think through the real-world effect of various and competing decisions and initiatives. Quantrix is an ideal tool for building rock-solid, dynamic financial models. The Integrated Financials model uses five powerful Quantrix concepts to create a dynamic Integrated Financials model. For a closer look at the building of this model, watch the webinar Integrated Financials with Quantrix.
This Modeler note demonstrates proven techniques for building a Quantrix Integrated Financials model, which is made up of linked Income Statement, Balance Sheet and Cash Flow matrices. The forward looking forecast is driven by Balance Sheet and Income Statement assumption matrices. A scenario category is utilized to easily compare high growth and low growth scenarios. In this example, effective techniques used include:
Using Groups with Sum(summary) Formulas to build the sections and subsections of a Balance Sheet
The use of the sum(summary) function is an effective way to calculate a summary value for all the items in a group. In the Balance Sheet, the Assets group has a Current Assets sub-group. Formula 1 calculates the sum of the Current Assets group. The Total Assets item is calculated by Formula 2, which is summing the entire Assets group.
The powerful sum(summary) function will not double count sums included within the specified range. Therefore the Total Assets item does not “double count” the Current Assets Total line. Use of groups with sum(summary) formulas in a Balance Sheet also allow you to add or subtract items (e.g., Pre-paid Expenses to Current Assets) without modifying your balance sheet logic.
Utilizing Inter-Matrix Recurrence Formulas to manage interaction between the Balance Sheet and Cash Flow
The month-to-month change of various Balance sheet accounts is needed for the Cash Flow calculation. For Accounts Receivable, one inter-matrix recurrence formula is utilized in formula #9.
This formula calculates the result of THIS year's change in Accounts Receivable by reaching into the Balance Sheet and subtracting the PREVious year's Accounts Receivable value from THIS year's Accounts Receivable value. Because the Year and Scenario categories are linked, you can take advantage of the THIS and PREV recurrence statements to calculate your net change in Accounts Receivable each month. All of this is calculated with one powerful Quantrix formula.
Use of “Skip” and “In” for Targeted Formulas
When building integrated financial models, you are often faced with the need to treat a particular item or group with two different formulas. In this example, we have an "Actuals" group and a "Forecast" group. There is a need to treat the "Cash" item differently between the two groups.
Since the Actual figures are input and are not calculated based on the forecast, the only thing we need to do is ensure the inputs are carried across all scenarios for all items including cash. This is accomplished by using the ‘In’ statement in formula 14 to say “In Actuals, Scenarion[THIS] = Scenario[PREV]…”. In this case, formula 14 is restricted to the group “Actuals”. Formula 15 then specifically addresses the calculation of Cash in the Forecast by skipping the Actuals group.
Normal and Outline Views
Quantrix offers two ways to present your data: Normal and Outline.
The Normal view gives a presentation of the data that allows you to easily view and manipulate the items and nested groups present within a model. The Outline view presents the hierarchal relationship of your model's structural elements. The outline view is a common format used when presenting financial statements in an accounting format.
Use of Scenario Categories to ask “What if?”
Often in your financial models, you need to ask the important "What-if?" question. What will happen to the model if growth is higher and/or lower than forecast?
Layering in a Scenario category and utilizing the Presentation Canvas is an effective way to easily compare and contrast different "What-if?" scenarios. In this model forecasted revenue and expenses are derived based on high-growth and low-growth assumptions set up as scenarios. Introduction of the Scenario category requires no editing of pre-existing formulas. To see the effect of the various scenarios flowing through the financial statements, one need only link the new Scenario category from the Assumptions matrix to the Financial Statement matrices. By using the Presentation Canvas, you can can bring multiple elements of your model together onto one report and use sliders to graphically manipulate the assumption cell values.
>> Integrated Financials Model