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 Beginning Balance Y to M model is used when you have the beginning balance for the start of each year and monthly activities. This model helps generate monthly beginning balances, typically for Balance Sheet figures.

Calculation Method

Monthly Beginning Balance = Yearly Beginning Balance + YTD Activity 

Example: 

• January 2024 Beginning Balance: $10,000
• January 2024 Activity: $2,000
• Calculated Beginning Balance for February 2024: $12,000

In this example, the model calculates the February 2024 beginning balance by adding the January activity ($2,000) to the January beginning balance ($10,000), resulting in a beginning balance of $12,000 for February 2024.

Create Beginning Balance Y 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 'Beginning Balance Y 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 and navigate to the Output section. 
   
   
8. Click the refresh icon or enable the Real-Time Update toggle to preview the calculation results.
9. The preview displays new entries created by the model. Each row represents a new entry.
   For example, if the beginning balance for Account ID "1000" was $12,000 in January 2024 and the Activity in January 2024 was $2,000, the entry will be:
   Account ID = 1000, Reporting Month = 31/02/2024, Amount= $14,000, Data Type = Beginning Balance.
10. Columns are color-coded: Purple for table fields, Pink for values, Yellow for custom columns, and Green for lookup fields.
11. 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.

12. Click the plus icon in the 'Adding Columns' section to add custom columns.
13. In the 'Add Custom Column' window, select a field name from the table and insert a formula in the formula section.
14. 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.
    
15. 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. 

Updated August 29, 2024 12:17