Financial modeling

The topics in this section relate to the underlying concepts that go into creating and maintaining a model.

All OLAP (Online Analytical Processing) cubes include financially oriented design elements. For example, accounts in a cube can each have their own calculation method. These are easily defined without needing to learn a programming language. All the structural elements of an OLAP model are managed within FP&A including dimension structures, account definitions, automatic movement of data between cubes, and the relationships between data versions such as actual data and planning data.

A financial model naturally includes data from the General Ledger and can also include data that is imported from subsidiary ledgers such as Accounts Payable and Accounts Receivable. You can also include non-financial data, such as headcount or units sold, as well as key metrics used to measure the overall performance of the business.

This flexibility allows you to design models that consist of multiple cubes for various purposes and different calculation rules. For example, the equation can be calculated automatically for hundreds or even thousands of entities such as products or business units. This results in a business model that does much more than simply add up data in accounts and cost centers.

The Calculation Rule Sets allow you to have different calculations for different versions of your data. For example, the annual budget exercise may involve simply aggregating data, whereas a five-year plan may require more complex calculations that are based on the metrics that drive your business.

Models and cubes

A model represents a business model and is used for analysis, budgeting, planning, and reporting—in short, for understanding the organization's financial operations. For example, a company that produces consumer packaged goods might have one model for budgeting at the detailed product/customer level and another model for budgeting high-level revenues and expenses at the business unit or cost center level. Some companies might choose to have different models for different business divisions or locations.

A model and its data are stored in an OLAP (Online Analytical Processing) cube. A cube can be thought of as a spreadsheet, but one that can have many more than just two dimensions or axes. For example, where a typical spreadsheet might have the dimensions Time and Account, a cube can contain Time, Cost Center, and Account:

It is important to remember that unlike a literal cube, which can only exist in three dimensions, an OLAP cube can have an unlimited number of dimensions. For example, a company might want to analyze some financial data by product, time period, city, type of revenue and cost, and actual and budget data comparison. All these additional methods of analyzing the data are also dimensions, and can exist simultaneously in the same OLAP cube.

The four dimensions that are present in all models are Time, Time Perspective, Version, and Account. Other dimensions, such as Organizations, Projects, and Products, vary from model to model, as they are user defined.

 Note:  The terms cube and model are often used interchangeably; however, in FP&A, the two terms are related but not the same. A FP&A model consists of one or more cubes that may be automatically linked together.

A FP&A model interfaces with other financial systems such as the general ledger or a sales order system. Typically,FP&A models receive historical data from other systems and allow third-party access to forecast data. They also interface with other FP&A models. For example, summarized data from a revenue model can be read into an expense model to calculate a full income statement.

OLAP

OLAP databases have been in use since the 1980s and are ideal for applications such as planning, reporting, and analysis. OLAP databases differ from relational databases, which are used for transactional applications such as accounting and ERP systems. FP&A uses Microsoft’s OLAP database, SQL Server Analysis Services (SSAS), which is part of the SQL Server database product. The arrangement of data into cubes overcomes a limitation of relational databases, which are not well suited for near instantaneous analysis and display of large amounts of data. OLAP was built not only for comparing groups of numbers, but also for reporting across a large array of data.

FP&A enhances SASS technology by providing the following functionality:

  • Non-technical users can easily build and maintain a financial OLAP model
  • Users can import not only data but also dimensional structures to OLAP cubes
  • Multiple users can enter data to cubes
  • Data entry can be incorporated into multi-user workflows
  • Scheduled processes can be run that manipulate large volumes of data within an OLAP model

FP&A makes it easy to build and maintain a multi-user OLAP planning and reporting environment. It also allows for functional expansion because of its open architecture. This has many advantages, as illustrated by the following examples:

  • Finance professionals can easily define standard calculations in a FP&A model. More complex calculations can be defined using MDX (the standard OLAP language used by Microsoft's SQL Server Analysis Services database).
  • FP&A reports, including formatting, calculations, and charts, are easy to define, in a spreadsheet-style interface. Alternatively, third party products, including spreadsheets, can report directly from the Analysis Services database.
  • FP&A enables drill through to actuals. When analyzing key reports, users can view the transactional level detail with the click of a button.
  • FP&A's data import functionality is specially designed for importing financial data. Alternatively, data can be imported using any ETL (Extract Transform & Load) software product that works with Microsoft's SQL Server database, including Microsoft's DTS product.