Financial data can be added to Datarails either manually or through system integrations. However, you may encounter scenarios where not all year-to-date data is available:

Monthly Activities Only: you have records of monthly activities but lack the monthly beginning balances. 
Beginning Balances Only: you have the beginning balances but no detailed activities data for the months.

To address these gaps, Datarails provides calculation models to automate the creation of monthly figures:

The Cumulative to M model is used when you only have monthly beginning balances and need to generate monthly activities, typically for P&L figures.

Calculation Method

Monthly Activity = YTD Data - Previous YTD Data

Example: 

• June 2024 Beginning Balance: $12,000
• May 2024 Beginning Balance: $10,000
• Calculated Activity for May 2024: $2,000

In this example, the model calculates the activity for May 2024 by subtracting the May beginning balance ($10,000) from the June beginning balance ($12,000), resulting in an activity of $2,000.

Create Cumulative to M model

Before creating a new Calculation model, ensure the following conditions are met:

• The table must include a 'Reporting Month' field. This field should represent the end of the month.
• The table must include a 'Data Type' field. The values should be 'Activity' and 'Beginning Balance'.

Steps to Create a New Model

1. Go to the left side panel and select 'Calculation Models'.
2. Click the plus icon and choose 'Cumulative to M', then click Next.
3. In the Input section, select the table from which you want to use data. A list of all table fields will be displayed.
   
4. Drag the desired field to the value section. Default is "Posting Amount" field, but you can change this to any numeric field.
5. Add relevant fields to the Filters section and use the filter icon to include or exclude values. Some default fields will be added automatically, which you can adjust as needed.
6. In the top right corner, select the model run dates. Set the "From Date" to specify when the model should start running. If needed, set a "To Date" to define when the model should stop.
   

   Ensure the calculation starts from the specified 'From Date' by filtering out previous dates. Drag the 'Reporting Month' field to the Filters section and exclude all dates before the 'From Date'. Alternatively, 'From Date' will include all amounts from prior dates as it's beginning balance. 

7. Review the Calculation section to see how the model processed your data.
   
8. In the Output section, select the table where you want to extract the data. 
9. Click the refresh icon or enable the Real-Time Update toggle to preview the calculation results.
10. The preview displays new entries created by the model (e.g., for Account ID "1000", Reporting Month = 31/05/2024, Amount = $2,000, Data Type = Activity).
11. Columns are color-coded: Purple for table fields, Pink for values, Yellow for custom columns, and Green for lookup fields.
12. The Granularity section lists all fields included in the preview. Add relevant fields for detailed insights, but avoid overly detailed fields to maintain performance.
    

    To ensure optimal performance of your model, drag and drop only the essential fields and avoid overly detailed fields. Each key field will also bring associated Lookup fields.

13. Click the plus icon in the 'Adding Columns' section to add custom columns.
14. In the 'Add Custom Column' window, select a field name from the table and insert a formula in the formula section.
15. If you used the 'Posting Amount' as the value in the Input section, tick the Amount box under the 'Adding Columns' section, and adjust the formula.
    
16. Ensure the calculation model contains all relevant data. Click "Save" to finalize the model.

Q&A

How can I verify that the calculation model is functioning correctly?

To verify that the calculation model is functioning correctly, you can create a validation table or widget. Follow steps outlined in the Q&A section of the 'Calculation Models: Beginning Balance One time to M' article. To validate the Cumulative to Monthly calculation model, filter 'Account Group L0' to "P&L" instead of "Balance Sheet" and ensure that Activity figures are created correctly.

Updated July 11, 2024 09:25