In this handbook, I introduce and discuss key corporate finance concepts. The framework is intended to give a broad overview, as well as some specifics for each topic, to aid people working on building models for FP&A and related operations. While it is primarily geared toward developers, it also serves to help the finance subject matter experts understand the bridge terminology between finance and technology.

Gaining a general understanding of basic Finance and Accounting concepts as well as Financial Statement Analysis will increase the usefulness and relevance of the models we build. And Finance teams will appreciate the modeling examples, as it helps them learn the language of the modeling components.

The focus of this document is on teaching the common financial concepts we run into every day building TM1 models. (Note that IBM Planning Analytics is referred to as “TM1” for short in this document.)

This is not a best practices document for TM1 development.

The examples given in the "TM1 Modeling tips" are meant to be illustrative and help the developer bridge the finance knowledge to TM1. Since the goal is to “connect the dots” between finance concepts and TM1 modeling concepts, only a few examples are provided, and they are generally the simpler examples. My goal is to build a bridge for communication here, not write the final word on TM1 architecture.

If you have TM1 examples that you think would be helpful to include, please email Robin Stevens rstevens@cubewise.com

There are many factors that contribute to architecting a multi-dimensional model, it’s not a “one approach suits all” situation. Factors for great model architecture (which is beyond the scope of this document) and determining best practices include:
• Size of data set
• Number of users
• Complexity of calculations
• Use cases for model (reporting? Journal entries?)
• Interplay between model being designed and other models/systems

In addition, my experience is primarily with U.S. based companies and their policies. Most accounting practices internationally are similar to the U.S. GAAP standards, although there are differences. And, worldwide, all companies track expenses, capital, people, products/services, although approaches may vary.

Lastly, I wanted to mention that although the examples within are for IBM Planning Analytics (aka TM1), many of the same concepts of how to model finances apply regardless of the software utilized. So, my hope is that this guide is useful to developers and finance teams across the world.

The Chart of Accounts (CoA) is the structural foundation of any financial model, including those built in IBM Planning Analytics (TM1). It lists every account used in the client’s financial system, mapping each account to a financial statement (either the Balance Sheet or the Income Statement) and to a logical section within that statement (e.g., Cash & Equivalents, Accounts Receivable, Inventory, etc.).

Here is a sample of a Chart of Accounts:

Accounting Fundamentals

• Every account belongs to either the Balance Sheet (BS) or the Income Statement (IS): This classification determines how it behaves in financial statements and how it integrates with other cubes in TM1.
• The CoA is the basis of any FP&A model: All data—actuals, forecasts, and budgets—are ultimately tied to these accounts. In TM1, the CoA typically forms the primary dimension in a central reporting cube, often called the "Finance" cube.
• Account numbering is usually systematic: While each company has its own structure, numbering patterns tend to follow consistent logic. For example, asset accounts usually begin with 1XXXX, revenue with 4XXXX, liabilities with 2XXXX, and so on. These patterns make it easier to validate and group data within TM1.

TM1 Modelling Tips

• Data flows into accounts in various ways:
  • Actuals are generally imported from a source system via an automated process for all GL accounts
  • Forecast data is input directly or derived:
    • Some types of expenses may be input directly as a dollar value
    • Some GL accounts may be calculated via rules such as salary and benefits (either directly in the Finance cube or, in this case, in a supporting cube such as Workforce)
    • Some items such as revenue may be calculated by a predictive forecasting methodology
• Transaction-level detail vs. aggregated data:
  • TM1 generally sums all transactions for a given account when importing via TurboIntegrator (TI).
  • If transaction-level detail is needed, a separate Transaction Detail cube should be created to avoid bloating the core Finance cube.
• Data sign conventions matter:
  • In most ERP systems, revenue and liability data (e.g., 4XXXX and 2XXXX accounts) are stored as negative values.
  • However, for clarity and consistency, FP&A models often reverse these values on import, so all data appears in its “natural” sign (e.g., revenue as a positive number).
• The Finance cube acts as the central hub: In this document, we'll refer to the main cube that holds the Chart of Accounts and measures (actuals, budget, forecast) as the "Finance" cube. It integrates with other cubes—such as CapEx, Workforce, and Revenue—to create a full planning ecosystem.
  

The Trial Balance is a core input into any TM1 model that deals with FP&A data. It represents the financial state of a company at a point in time and includes the activity and balances of all accounts—both income statement and balance sheet. It’s often delivered monthly and may be segmented by other fields such as department or legal entity.

This report is usually structured with one row per account and columns for various balance types (e.g., beginning balance, activity, ending balance, debits, credits). It reflects the debits and credits for each account and follows fundamental accounting rules that must be understood when importing into TM1:

Debits & Credits

• Asset accounts: A debit increases the balance, a credit decreases it.
• Liability accounts: A debit decreases the balance, a credit increases it.
• Equity accounts: A debit decreases the balance, a credit increases it.
• Revenue accounts: A debit decreases the balance, a credit increases it.
• Expense accounts: A debit increases the balance, a credit decreases it.

Here is a sample showing the first few rows of a Trial Balance:

TM1 Modeling Tips

• IS vs. BS Handling:
  • For Income Statement (IS) accounts, TM1 models typically load monthly activity. If revenue accounts are shown as negatives in the source system (e.g., ERP), the signs are reversed so all values are positive in TM1.
  • For Balance Sheet (BS) accounts, it’s more common to load the ending balance. This creates a snapshot for each month and aligns reporting with how the business utilizes balance sheet information.
• Handling Period Aggregations:
  • When loading ending balances for BS accounts, a TM1 rule is often required to ensure that aggregated periods (like quarters) reflect the last month’s value (e.g., Q1 = March ending balance).
• Sign Reversals:
  • For both Revenue and Liability accounts, the signs are usually reversed during the import process so that everything appears positive and intuitive in TM1.
• When Only Debits/Credits Are Available:
  • Sometimes, source systems provide only DR/CR columns. In this case, the activity (for IS accounts) can be derived easily.
  • For BS accounts, deriving an ending balance is more complicated. You must accumulate all activity from the inception of the account, which is not practical in most FP&A use cases.
• Alternate Approach for BS:
  • One common workaround is a two-step load:
    1. Load prior month’s balance into the current month.
    2. Add the current month’s activity on top of that.
  • This simulates a running balance without requiring full history.
• Loading Activity for BS:
  • You can load BS accounts using monthly activity instead of ending balance, but this requires users to report on inception-to-date (ITD) values. While mathematically valid, this method is less intuitive for business users, who don’t typically think of the balance sheet in terms of “activity.”

The Income Statement (IS), also commonly referred to as the P&L (Profit & Loss) is the financial report that shows how much money a company earned and spent during a given period—typically a month, quarter, or year. In TM1 and most FP&A models, the values for the income statement represent the “activity” for that period (as described in previous sections.)

Of all financial statements, the income statement is the most scrutinized—especially in a corporate planning context—because it directly reflects profitability and business performance.

Accounting Fundamentals

• Revenue vs. Sales:
  • The terms Revenue and Sales are often used interchangeably, but technically:
    • Revenue is the amount that can be booked in a given month.
    • Sales is the transaction that occurred—what was sold.
    • If a product is delivered all at once, they’re usually the same. But in subscription or delivery-over-time models, only the portion delivered can be booked each month.
• Margins:
  • All margin calculations are based on Net Revenue, not Gross Revenue.
  • Be aware of the distinction between:
    • Profit: A dollar amount.
    • Margin: A percentage.
  • These terms are frequently misused or misunderstood in reporting, so clarity is key.
  • The 3 key margins in an Income Statement are:
    • Gross Margin: Sales – Direct Costs – measures how efficiently the company is making its products
    • Operating Margin: Gross margin – Operating Costs – measures how efficiently the company manages it’s overall operations (including Sales, marketing, IT, Facilities, Admin, and Corporate functions)
    • Net Margin: Operating Margin – Interest & Taxes – measures the “final” profit contribution of the company after financing and taxes have been considered

TM1 Modeling Tips

• Department-Level Splits:
  • Many IS layouts have hidden columns for Departments, especially when breaking out costs like Direct vs. Indirect Labor.
  • Often, all salary data exists in one account, but by tagging employees to departments (e.g., via a Department dimension), the data can be split correctly in the IS.
• Feeding from Other Model Components:
  • It's crucial to note which accounts are fed from other cubes. For example:
    • Salary expense may be fed from a Headcount or Workforce Planning cube.
    • Depreciation may be fed from CapEx.
    • COGS may come from Inventory or BOM (Bill of Materials) logic.
  • This mapping enables proper traceability and allows TM1 to connect operational drivers to financial outcomes.
• Direct vs. Indirect Labor Forecasting:
  • In manufacturing or production-oriented businesses, Direct Labor is typically part of COGS, while Indirect Labor is part of Operating Expenses.
  • Forecasting approaches differ:
    • Indirect Labor is often forecast by employee.
    • Direct Labor is typically forecast based on unit output, using the product forecast to derive how much labor is needed.
  • These should use separate accounts even if sourced from the same data, to align with the IS layout.
• Hierarchy and Signs:
  • The Income Statement usually has a ragged hierarchy—some items roll up, others don’t follow clean levels.
  • Pay close attention to:
    • The signs of each line item (negative or positive).
    • Whether the value should be added or subtracted in rollups.
  • The ultimate rollup is Net Income (sometimes labeled as Comprehensive Gain)

• A Balance Sheet records what you own (assets) and what you owe (liabilities and equity).
• Unlike the Income Statement, the Balance Sheet shows balances, not activity. These balances represent the accumulated value of assets and liabilities as of the time they were booked.
• A Balance Sheet always has three sections:
  • Assets
  • Liabilities
  • Equity
• The Balance Sheet is said to "balance" because:
  • Assets = Liabilities + Equity
• Assets are the resources a company owns (cash, buildings, etc.), while Liabilities are obligations (loans, payables, etc.).
• Equity represents the portion owned by shareholders. For a corporation, equity is treated as part of what the company owes (to shareholders), not what it owns.

TM1 Modeling Tips

• To load Balance Sheet data in TM1, you must provide a starting balance for each account to allow carryforward logic to work.
• PP&E items (such as asset values and accumulated depreciation) typically come from the Capex model.
• Some accounts (like payables, receivables, and inventory) are often forecasted using drivers, such as:
  • AP Days
  • AR Days
  • Inventory Turnover
• Identify which Balance Sheet accounts the Finance team plans to actively forecast. Other accounts can be handled with carryforward rules.
• It is common to include a balancing account to force the Balance Sheet to reconcile. This is acceptable but must be monitored by a TM1 admin to ensure the balance doesn't grow too large.
  • In global companies, this account often captures currency translation restatements (due to differences in FX rates used in the IS vs. the BS).
• TM1 rollups for the Balance Sheet can be set up to validate that:
  • Assets = Liabilities + Equity
• Be sure to note which accounts are being fed from other components of the model.

A Cash Flow Statement consists of 3 areas:

1. Operations: This is basically Net Income, adjusted for non-cash transactions on the IS (such as Depreciation) and cash transactions on the BS (such as the change in Accounts receivable). This number shows us how much cash is generated from the general operations of the business.
2. Investing: This shows the cash inflow from receiving outside investments netted against the cash outflow from investments outside the company’s primary business.
3. Financing: This summarizes cash transactions from raising, borrowing, or repaying capital (this can be equity – such as shares, as well as liabilities – such as debt).

1. The Cash Flow statement relies primarily on the Income Statement (IS) and Balance Sheet (BS) to calculate cash flow for the period.
2. This statement is highly scrutinized, especially for fast-growing or acquisitive companies with volatile cash positions.
3. Companies that are not capital intensive often use Net Income or EBITDA as a proxy for cash flow.
4. In the very long run, Net Income ≈ Cash Flow, but this is not true in the short term.

TM1 Modeling Tips

• Cash Flow account structures can be included in the primary Account dimension, alongside the IS and BS rollups.
• Mix of input and rule-driven entries is typical and client-specific.
• Net Income (top line of cash flow) should ideally be pulled from the P&L into a dedicated account and rules-driven, not nested under the Cash Flow section — nesting makes it confusing for end-users.
• Capital expenditure forecast can be pulled from the Capex model
• Generally, financing changes must be input as they are not isolated in the existing IS and BS.
• Ending Cash = Starting Cash + Net Cash Flow (Operating + Investing + Financing).
• TM1 Cash Flow Cube should be designed with proper account sourcing and flags for input vs. rule-driven logic.

Sample Cash Flow Structure showing data sources

The Income Statement and the Balance Sheet carry all the day to day transactions for a given company, and allow us to review trends, analyze performance, and better understand the workings of an organization.

While much attention is paid to profit margins, which are calculated based on Income Statement data only, key ratios are often comprised of data that comes from both the Income Statement and the Balance Sheet. These ratios are helpful in summarizing large amounts of financial data spread across multiple reports, and include things like ROA (Return on Assets), Inventory Turnover, and Accounts Receivable Days. See Common Key Performance Indicators for more on this topic.

Both statements provide most of the information needed to derive a Cash Flow statement as well. The diagram below shows the sources for different key parts of the Cash Flow statement.

Interplay Between Statements

• The three main financial statements are:
  • Income Statement (IS) – Performance: revenue & profit over time
  • Balance Sheet (BS) – Financial position at a point in time
  • Cash Flow Statement (CF) – Movement of cash during the period
• The entire trial balance is effectively covered across the IS and BS.
• Cash Flow is largely a rearrangement of IS and BS data, with a few direct inputs (e.g. Capex, financing activity).

TM1 Modeling Tips

• CF accounts can be added directly to the Account dimension in the Finance cube.
• No need to model accrual vs. cash separately – a well-structured CF statement solves for that.
• This structure supports automated rule-driven builds of the CF statement using delta logic and pull-throughs from IS and BS.

Definition & Formula

• Retained Earnings (RE) = Net Income – Dividends Paid
• For planning purposes, YTD Net Income is a proxy for RE (assuming no dividends modeled).

Booking Timing

• Current Year RE builds month-to-month during the year based on YTD Net Income.
• At year-end, the Current Year RE is rolled into Cumulative RE and reset in January.

General Ledger Behavior

• Most GL systems only book RE at year-end, so for modeling in TM1, you need:
  • A rule or TI process to accumulate YTD RE.
  • A year-end roll-forward mechanism to move Current Year RE into Cumulative RE.

IS → BS Link

Retained Earnings links together the results from any given year of the Income Statement to the Balance Sheet. Basically, Net Income is deemed to be value generated by the company. They can either pay out the proceeds in dividends (which has become much less common over time as companies favor growth) or reinvest the money in the company, at which point it becomes equity.

Example:

• Net Income flows from the Income Statement to the Balance Sheet as Current Year RE.

Currency Note (for Multicurrency TM1 Models)

• IS is translated using average FX rate
• BS is translated using spot FX rate
• This can cause a mismatch → requires a Currency Translation Adjustment (CTA) booked to Equity (usually a dedicated CTA line).

TM1 Modeling Tips

• Account dimension should include:
  • Cumulative Retained Earnings
  • Current Year Retained Earnings
  • Currency Translation Adjustment (if needed)
• Rule for RE Accumulation:
  • Either at month-end or dynamically YTD
• TI Process at Year-End:
  • Transfer Current Year RE to Cumulative RE
  • Reset Current Year RE to 0 in January (or first month of Fiscal Year)
• Optional: Use a "plug" equity account to catch rounding or mismatch issues.

Expense Allocations are a common calculation for both Actual and Forecast data for most companies. While Direct Costs are generally easily matched to the revenues they produce, a large portion of expenses are spread across the activities of the entire organization. These costs are Operating Costs.

When measuring the success of a business, it is helpful to allocate a portion of these Operating Costs to different products/regions/business activities to get a better sense of overall profitability. Gross Margin will tell us the contribution made after making and selling products, but the activities of marketing, offices, management, IT, and more are not taken into account in Gross Margin.

Allocations often cause a significant amount of angst amongst department managers, as it is very hard to agree on an allocation methodology that feels “fair” to all. But they are a useful analytical tool, and as such, it’s best to keep allocation methods as simple as possible, and avoid the temptation to make too many exceptions to your general allocation rubric.

Generally, operating costs are allocated based on another metric such as Revenue, Expense, Headcount, or Square Footage. Here are some common examples:

• IT costs are frequently allocated based on Headcount
• Facilities costs are frequently allocated based on Square Footage
• Marketing costs may be allocated based on product revenue
• Admin costs may be allocated as a percent of total expense

Discussing allocations can get confusing, so it’s best to agree on some terminology when trying to understand a company’s allocation methodology. Here is a helpful rubric:

TM1 Modeling Tips

• Allocations are generally siloed by Entity (e.g., by country or company)
• Ensure the denominator for the allocation basis (such as Headcount) only includes the target departments — not the full organization
• When possible, feed with the Basis value instead of All Departments — this enables 1-to-1 feeders and improves rule performance
• Using the allocation rubric above makes it easier to gather and structure requirements

The example below shows Facilities and IT expenses being allocated to the other departments in the organization, using Headcount as a Basis:

When testing allocations, use these two methods:

KPI stands for Key Performance Indicator. It is a broadly used term that can refer to any metric—ratios, percentages, dollar values, or units—that helps assess business performance.

KPIs are used to:

• Benchmark performance over time
• Compare results against targets or industry standards
• Summarize and simplify complex financial activity for analysts and decision-makers

There are many industry-specific KPIs, but most planning models include a set of common corporate finance KPIs. These should be clearly defined, consistently calculated, and easily explainable to stakeholders.

Common KPIs in Corporate Planning Models

The table below describes some of the most common KPIs, and their purpose.

The importance of Financial Statement analysis can be summarized by reviewing the set of KPIs known as “Dupont Analysis”, which is shown below. Basically, it breaks apart a typical ROE equation into a profit component and an asset component. By reviewing the pieces of ROE, you can determine if a company is good at managing costs, and/or good at managing assets. This breakdown also makes comparisons to competitors more meaningful.

TM1 Modeling Tips

• Add KPI calculations as a separate roll-up in the Finance cube or Reporting cube for clarity
• Feed KPIs from the appropriate source modules (IS, BS, Capex, etc.) using rules or TI
• Use versioning to compare KPIs across Actuals, Forecasts, and Plans
• Most KPI’s are rules based since they are ratios often calculated at the consolidated level on one or more dimensions, so the rules do not require an N: or C: qualifier
• Often, ratios are calculated for Actuals, and used as inputs (drivers) for the Forecast/Plan
• Keep definitions centralized to ensure reporting consistency across dashboards and teams

Revenue recognition is one of the most foundational accounting principles in corporate finance. It determines when and how a company recognizes revenue on its financial statements. Under accrual accounting, revenue is recognized when it is earned and realized—not necessarily when the cash is received.

This becomes especially important in situations like long-term contracts, subscription services, or multi-year software licenses.

Accounting Fundamentals

• Revenue Recognition Principle
  Revenue should be recognized when it is earned (service delivered or product transferred), not when the cash is received.
  Example: If a subscription service is sold for $12,000/year, $1,000/month should be recognized over 12 months.
• Deferred Revenue
  Also known as unearned revenue, this refers to cash received for goods or services not yet delivered. It appears as a liability on the balance sheet and is reduced as the company earns the revenue over time.
• Subscription Revenue Recognition
  Subscription models typically involve upfront payments for services delivered over time. As each period passes, you reduce the liability (deferred revenue) and recognize earned revenue.
  Accounting Entry:
  Debit: Unearned Revenue (Liability)
  Credit: Revenue (Income Statement)

Sales vs. Revenue

Though sometimes used interchangeably, Sales and Revenue are distinct:

• Sales refer to income from direct transactions (e.g., selling a product).
• Revenue is broader and includes all income sources, such as services, royalties, interest, or rent.

Sales Forecasting

Sales forecasting is the estimation of future revenue, based on historical trends, current pipeline data, and industry conditions. It’s a critical input to revenue planning models.

Common Forecasting Inputs:

• Historical sales patterns
• CRM pipeline data
• Macroeconomic indicators
• Seasonality and market trends

Methods Include:

• Historical trend analysis
• Opportunity-stage weighted pipeline
• Bottom-up forecasting by salesperson or product line
• External benchmarks or market modeling

The method selected will depend on:

• The time horizon (short- vs. long-term)
• Forecast accuracy requirements
• Availability of reliable historical data
• Industry volatility

TM1 Modeling Tips

• Separate Bookings, Billings, and Revenue accounts to maintain clarity in FP&A models
• Use rules to amortize revenue over time for subscription or service contracts
• Build a contract calendar or subscription schedule cube to support revenue timing logic
• Deferred revenue logic often requires carryforward rules, particularly for forecasted months
• Often, it is helpful to build a “waterfall” type of cube—this generally includes 2 time dimensions, one for purchase date, and another to calculate the revenue spread over time for each product/line item

Capital Expenditures (Capex) refer to the funds used by a company to acquire, upgrade, and maintain physical assets such as property, buildings, or equipment. These are long-term investments that are capitalized on the balance sheet and depreciated over time rather than being expensed immediately.

Capex differs from operational expenses (Opex), which are short-term costs incurred in the day-to-day functioning of the business. Capex decisions are often tied to strategic initiatives like growth, capacity expansion, or digital transformation.

Depreciation, which is expensed each month, can be thought of as the cost of using a particular asset for that time. While there are many methods of depreciation, a simple straight-line depreciation is the most common. This simply takes the asset value divided by the asset life. For example, new cubicles would be considered “leasehold improvements” and might be depreciated over 10 years. So, $1m invested in new cubicles would be recorded on the Balance Sheet, and $100,000 per year would be funnelled through the Income Statement as an expense (and deprecate the investment on the Balance Sheet by the same amount.)

Accounting Treatment:

• Capex is recorded on the Balance Sheet under “Property, Plant & Equipment (PP&E).”
• The depreciation expense flows to the Income Statement over time.
• The cash outflow for Capex is reflected in the Cash Flow Statement under “Cash Flow from Investing Activities.”
• While there can be some variety in the categorization of assets, generally the following groupings are used (with the current common depreciation life listed in years in parentheses):
  • Land (does not depreciate, remains an asset on the Balance Sheet)
  • Buildings (25-40 years)
  • Machinery & Equipment (5-10 years)
  • Furniture & Fixtures (3-5 years)
  • Leasehold Improvements (5-10 years)
  • Computer hardware (3-5 years)
  • Computer software (1-3 years)

TM1 Modeling Tips

• Capex is typically managed in a dedicated Capex model (separate cube) that includes:
  • Asset class (e.g., Machinery, IT Equipment)
  • Capital project or investment ID
  • Timing (projected spend by month)
• Depreciation should be rule-driven, based on:
  • Start month of the asset
  • Useful life (months or years, generally set by asset type—see above)
• Capex model should feed into:
  • BS for new asset value and accumulated depreciation
  • IS for monthly depreciation expense
  • CF for investment cash outflows (Capex spend)
• For Actuals, Capex spend is usually input manually or loaded via data integration from ERP/AP systems.
• For forecasting, Capex should align with planned projects and initiatives, not just trend-based projections.

General Info

Seasonality refers to the predictable, recurring changes in business activity that occur generally within a one-year period. These fluctuations are driven by calendar events, commercial cycles, or environmental factors such as weather. Understanding and modeling seasonality is essential for accurate forecasting, especially for companies whose demand or spending patterns vary significantly throughout the year.

Examples:

• Retailers hiring additional staff or increasing inventory in the holiday season.
• Utility companies experiencing revenue spikes in summer or winter months.
• Consumer products that sell primarily in specific quarters (e.g., sunscreen or school supplies).
• City budgets with increased summer programming such as camps, swim lessons, etc.

Seasonality can affect not just Revenue, but also Expenses, Headcount, Cash Flow, and even Capex timing. Correctly modeling it helps ensure that forecasted results align with operational realities.

Approaches to Calculating Seasonality

Approach 1: Mechanical (TM1 Rules-Based)

This is the most common method used in financial planning systems when prior-year data is available.

1. Calculate the Seasonality Index:
   Seasonality Index = Monthly Revenue / Last Year Average Revenue
2. Apply the Index to Create a Forecast:
   Seasonal Revenue Forecast = Seasonality Index × Prior Year Actual Revenue

This approach uses simple proportional logic to spread forecasted annual revenue across months based on historical patterns. It works well when the company’s seasonality is stable year-over-year.

Approach 2: AI-Driven (Advanced Forecasting)

For more complex or volatile businesses, an AI-based approach can produce more refined seasonal forecasts.

1. Upload prior-year revenue data, forecasts, and known seasonal drivers to a statistical or machine learning model (R or Python).
2. Train the model to detect seasonal patterns and anomalies using algorithms such as ARIMA, Prophet, or exponential smoothing.
3. Export predicted seasonal factors back to TM1 via TurboIntegrator (TI).
4. Use these factors to generate a seasonally adjusted forecast inside the Finance cube.

This method is particularly valuable when external data (e.g., weather, promotions, or macroeconomic indicators) influences performance.

TM1 Modeling Tips

• Separate Seasonality Drivers:
  Store seasonality factors in a dedicated cube (e.g., Seasonality_Factor) so they can be reused across different models—Revenue, Headcount, or OpEx.
• Dynamic Rules:
  Create rules that automatically multiply the seasonality factor by base forecast values (e.g., annual plan or last year’s actuals).
• User Flexibility:
  Allow planners to override seasonality manually by entering adjustments for months, quarters, or business units.
• Data Source Integration:
  Use TI processes to import historical data from ERP systems or data warehouses to calculate indices directly within TM1.
• Version Control:
  Keep seasonality versions (e.g., “Base,” “Updated,” “AI-Adjusted”) to compare the impact of changing trends or assumptions.
• Visualization:
  Build dashboards or line charts to display the monthly or quarterly trend, highlighting seasonal peaks and troughs for management review.
• Auditability:
  Ensure stored indices are timestamped and traceable—critical for explaining forecast variances to Finance and Audit teams.
  
  

FP&A Overview

Employee benefits represent the non-salary compensation that companies provide to employees, including bonuses, insurance, retirement contributions, and other employment-related costs.
In financial planning, benefits are often modeled as a combination of percentages of salary and fixed costs per employee (FTE). Accurate modeling helps ensure total personnel expense forecasts are realistic and aligned with company policies and statutory requirements.

Common Benefit Types and Calculation Methods

• Merit Increases:
  Calculated as Salary × Merit % (typically from a global rate or corporate assumption).
• Bonuses:
  For existing employees: Salary × Individual Bonus %
  For new hires: Salary × Bonus % by Level
• Stock Compensation:
  Salary × Stock Option % (from global assumption tables).
• Medicare:
  Salary × Medicare % (from statutory or corporate rates).
• Pension:
  Salary × Pension %, subject to an annual per-person cap.
• FUTA / SUTA (U.S. Payroll Taxes):
  Each based on a % of salary, with annual maximum contribution caps.
• Employer Insurance:
  Salary × Insurance %, subject to annual per-person maximums.
• Social Security:
  Salary × Social Security %, capped annually at the wage base limit.
• Employee-Related Expenses:
  FTE × Fixed $ amount (e.g., training, communications, equipment).
• Paid Time Off (PTO):
  FTE × (Annual PTO Days ÷ 12) for monthly accrual.

Timing Considerations

• Many benefit calculations use year-to-date (YTD) comparisons against annual caps.
• Once an employee reaches the annual maximum for a benefit, subsequent months reflect only the remaining available balance or zero payout if the limit is reached.

TM1 Modeling Tips

Building employee benefit calculations in TM1 (Planning Analytics) should balance accuracy and model simplicity.

• Not all benefits need to be modeled at the employee level — aggregate by department when possible, using historical averages for benefit rates
  • These calculations can be done in the “Finance” or “P&L” cube which is done at the GL account level, simplifying the modeling significantly
• Determine whether each benefit should be calculated as:
  • A “% of Salary” metric (e.g., Bonus, Social Security, Medicare)
  • A “per Headcount” metric (e.g., Healthcare, Training)
• Caps (e.g., FUTA/SUTA wage limits) often have immaterial impact on total forecasts and can be omitted for simplicity. Instead, for departments with mostly higher-paid employees where caps are hit early in the year, the rates can be tapered down or set to zero.
• Store all benefit rate assumptions in a central “Global Rate” cube to ensure consistency across calculations.
• Use attributes or flags to identify which benefits apply by country, department, or employee type for multi-entity models.

FP&A Overview

A Bill of Materials (BOM) lists every component, material, and sub-assembly required to produce a finished good. It is central to operational finance and manufacturing planning because it drives both inventory management and cost calculation.
For example, a car’s BOM might include thousands of parts—everything from the cloth for the seats and the plastic for the dashboard to the screws that hold everything together. Managing this complexity efficiently helps balance production capacity, cash flow, and inventory costs.

Key considerations include:

• Forecasting demand for finished products to plan materials purchases.
• Lead times for each part in the BOM.
• Current inventory levels and reorder points.
• Defect rates or scrap factors for each part.
• Production time for each product and component.
• Distribution timing to customers or warehouses.

Efficient management of these factors allows for just-in-time inventory, minimal waste, and optimal cash utilization—all key outcomes for FP&A teams monitoring working capital and cost of goods sold (COGS).

Because BOMs are hierarchical (e.g., the car seat has its own sub-BOM for covers, foam, and metal), financial models may need to summarize or “roll up” costs across multiple levels of the hierarchy.

TM1 Modeling Tips

In TM1 or other planning systems, BOMs can be modeled at varying levels of detail depending on the use case:

• Flatten the BOM for finance reporting purposes—combine all component parts into a single list per finished product to simplify costing, forecasting, and scenario analysis.
• For greater precision, retain hierarchical BOM structures using parent-child relationships between components. This allows for dynamic roll-ups of cost and quantity.
  • Alternatively, separate cubes can be maintained for each “level” of the BOM and daisy chained together with rules or TI calculations to obtain the final quantities for ordering, tracking, forecasting, etc.
• Integrate operational data feeds (from ERP or MRP systems) for quantities, lead times, and costs, enabling real-time analysis of inventory and production needs.
• Use optimization rules or scripts (potentially leveraging historical demand or external data) to refine purchasing and production schedules.
• Track variances between standard and actual costs by component to monitor efficiency and waste.
• Build driver-based calculations that link material usage and purchasing costs directly to production volumes.

When done well, a TM1 BOM model can connect operational manufacturing data to the financial forecast, providing valuable insights into COGS, cash flow, and margin performance.

Accounting Overview

Standard costing is a cornerstone of manufacturing accounting and works hand-in-hand with the Bill of Materials (BOM). It allows businesses to assign consistent, pre-determined costs to products, making budgeting, forecasting, and variance analysis more efficient.

Each period (typically annually), companies set standard costs for each component of production based on historical data, supplier pricing, and operational expectations. These standards are used to calculate the Cost of Goods Sold (COGS) and are applied to the actual number of units produced.

Standard costs are usually divided into three categories:

• Parts (BOM): The materials and components that make up the finished good.
• Labor: Standard labor hours and costs, often broken down by job type (e.g., machinist, technician, supervisor).
• Overhead: Indirect costs such as utilities, depreciation, and facility expenses, generally applied as a multiplier on material or labor costs.

As production occurs, actual costs are tracked and compared against these standards. The differences—variances—are analyzed to understand operational performance and cost control effectiveness. Variances are recorded in both the Income Statement (COGS section) and the Balance Sheet (Inventory accounts).

Typical variances include:

• Materials Variance – Difference between actual and standard cost of materials.
• Labor Variance – Difference between actual and standard labor cost.
• Overhead Variance – Difference between actual and applied overhead.

TM1 Modeling Tips

In most systems, the ERP handles actual costing (including variances), while TM1 handles forecasting and planning. The modeling objective is to make standards flexible and responsive to volume or price changes.

Key modeling tips:

• For actuals, import both standard and variance values directly from the ERP system, as well as historical standards.
• For forecasting, variance accounts are typically not forecast—focus on standards and volumes.
• Create a cube or data structure that stores Standard Costs by category (Materials, Labor, Overhead) for each product.
• Multiply Standard Cost × Forecasted Units to calculate total forecasted cost.
  • Include calculates that back into the Actual standards to show trending from actuals and provide a framework for reviewing standards.
• Allow users to update standards during the forecast cycle—this enables what-if analysis on pricing, labor rates, or supplier changes.
• Incorporate dynamic standard cost calculations, so forecasts update automatically as volume or assumptions change.

This approach ensures the forecasting model mirrors operational realities while remaining easy to adjust and maintain.

• Most companies operate in multiple countries and currencies, requiring translation into a single reporting currency for consolidated FP&A.
• Local teams should plan in their local currency to improve accuracy, familiarity, and meaningful variance analysis.
• FP&A consolidations translate all local-currency forecasts/actuals into the parent company’s reporting currency.

Key FX Rates

• Spot Rate: Month-end rate; used for Balance Sheet translation.
• Average Rate: Average over the month (often daily average); used for Income Statement translation.

Model Types

Examples:

Multi-currency matrix:

Reporting-currency mapping:

Constant Currency

Constant Currency is used in FP&A to isolate true operational performance by removing the impact of foreign exchange (FX) rate movements. Instead of translating results using current-period FX rates, all periods are translated using a fixed reference rate (often prior year actuals, prior forecast, or a defined budget rate).

From a business perspective, constant currency allows finance and business partners to separate out variances caused by business activities (higher sales, deferred expenses, etc.) from those caused by currency fluctuations.

TM1 Modeling Tips

1. Entity Dimension
   1. Include a dimension for Entity.
   2. Assign each entity a Currency attribute (e.g., Currency="EUR").
2. FX Rate Cube Setup
   1. Multi-Currency: Cube includes both Entity and Currency dimensions so rates can be entered for all currency pairs.
   2. Reporting Currency: Use a simple FX Type dimension with just:
      1. ReportingCurrency (e.g., USD)
      2. Local
         All rate inputs go into Local, and TM1 rules/TI compute the Reporting Currency values
3. Account Type Attribute
   1. On the Account dimension, add an attribute like AcctType = IS | BS | None.
      Helps rules determine which rate (Average vs Spot) to apply and which accounts skip translation (e.g., Headcount).
4. Translation Logic
   1. IS Accounts → Average Rate
   2. BS Accounts → Spot Rate
   3. Non-translated metrics → None
5. CTA (Currency Translation Adjustment)
   1. Needed because IS and BS use different FX rates.
   2. CTA ensures the Balance Sheet stays balanced after translation differences
6. Constant Currency
   1. Set up a version called Actuals – Constant Currency and translate this data using the Forecast exchange rates
   2. Include 2 columns in your variance reports, one for currency translation differences (Actual – Actual Constant Currency) and one for business activity differences (Forecast – Actual Constant Currency)
7. Misc
   1. Let contributors enter in local currency; translate centrally.
   2. Store Actual and Forecast rates in a single FX cube.
   3. In Reporting Currency models, simplify the FX dimension to just Local vs Reporting.
   4. Be sure to populate metrics items like Headcount in both currencies (with no translation) so that the data shows up when pivoting the cube
      

Intercompany transactions occur between two entities within the same parent company. They must be eliminated during consolidation so internal activity doesn’t distort revenue, expenses, assets, or liabilities.

Common intercompany scenarios:

1. Sale of Goods:
   1. Entity A sells to Entity B
   2. A records Revenue, B records COGS/Inventory
   3. Consolidation removes both sides
2. Shared Services / Allocations:
   1. Corporate charges entities for IT, HR, etc
   2. Consolidation eliminates internal revenue/expense
3. Loans / Transfers:
   1. One entity records a receivable, the other a payable
   2. Interest is also eliminated

Actuals vs Forecast

• Actuals: Eliminations are normally handled in the ERP, then imported into TM1.
• Forecast: FP&A typically builds and forecasts intercompany transactions directly, so TM1 must eliminate these within the model.

TM1 Modeling Tips

1. Identify Intercompany Drivers
   1. FP&A already knows which entities buy/sell or allocate costs to each other
   2. If the model uses intercompany GL accounts, eliminating them becomes much simpler.
2. Entity Dimension Setup
   1. Add an "Eliminations" element to the Entity dimension
   2. This element holds calculated eliminations
3. Elimination Logic
   1. For each intercompany pair, TM1 rules or TI processes post the opposite entry to the Eliminations entity.
   2. The parent consolidation rolls up to:
      1. US + UK + Eliminations = Consolidated

Example:

Result: No internal revenue or expense remains at consolidation.

4. GL Account Structure

• Use clear intercompany accounts (e.g., IC Sales, IC COGS, IC Receivables, IC Payables) to simplify elimination rules.
• Forecast contributors input on these accounts; TM1 offsets them automatically.

Billings, bookings, and backlog are closely related but distinct metrics that help FP&A track revenue, sales performance, and future business obligations. Understanding their differences is key for accurate forecasting and financial modeling.

Definitions and Examples

• Bookings: Orders Won
  • Definition: Total value of customer orders/contracts signed in a period, regardless of delivery
  • Business Perspective: Measures demand and future revenue potential.
  • Example: $120,000 annual software contract signed in March → Booking = $120,000 for March
• Billings: Money Requested
  • Definition: Invoices sent to customers during a period; may differ from recognized revenue.
  • Business Perspective: Reflects cash flow and ability to monetize contracts
  • Example: $10,000 monthly invoice for the above $120,000 contract → March billings = $10,000.
• Backlog: Future Revenue
  • Definition: Signed but unfulfilled orders; future work company is obligated to deliver.
  • Business Perspective: Supports forecasting of workload, staffing, and revenue streams.
  • Example: Total signed contracts = $500,000; $200,000 billed → Backlog = $300,000.

Key Relationship:

Backlog = Bookings – Billings

TM1 Modeling Tips

• Cube Design
  • Track metrics by Customer, Product, Region.
  • Separate cubes for Billings, Bookings, and Backlog are recommended:
    • Dimensionality may be similar, but co-housing can create confusion.
    • Customer definitions can differ (billing vs shipping).
    • Timing differs (contract signing vs invoice vs delivery obligation).
    • Use consistent dimensions for aggregation (Customer, Product, Region), but allow for variations where needed.
• Backlog Calculation
  • Can be calculated dynamically with rules, or, if preferred, via Turbo Integrator:
    ['Backlog'] = DB('Bookings', !Time, !Product, !Customer, 'Amount')
    - DB('Billings', !Time, !Product, !Customer, 'Amount');
• Integration with FP&A
  • Billings → Accounts Receivable: Direct impact on cash flow.
  • Bookings → Revenue Recognition: Drives revenue forecast with appropriate timing.
  • Backlog → Operational Planning: Supports forecasting of revenue and resource requirements (FTEs) separately for existing vs new contracts.

Common Acronyms