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 Onetime to M model is used when you have a single initial balance at a specific point in time along with monthly activities. This model helps generate monthly beginning balances, typically for Balance Sheet figures.

Calculation Method

Monthly Beginning Balance = Onetime Beginning Balance + To-Date Activity 

Example: 

• January 2023 Beginning Balance: $10,000
• Total 2023 Activity: $10,000
• Calculated Beginning Balance for January 2024: $20,000

In this example, the model calculates the beginning balance for January 2024 by adding the total activity for the year 2023 to the January 2023 beginning balance, resulting in a beginning balance of $20,000.

Create Beginning Balance Onetime 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 Onetime 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. 

   Review the Calculation section to see how the model processed your data and navigate to the Output section. 
   
   
7. Click the refresh icon or enable the Real-Time Update toggle to preview the calculation results.
8. The preview displays new entries created by the model. Each row represents a new entry.
9. For example, if the beginning balance for Account ID "1000" was $12,000 in January 2023, the Activity in 2023 was $2,000 and the Activity in January 2024 was $2,000, the entry will be:
   Account ID = 1000, Reporting Month = 31/02/2024, Amount= $16,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 ensure your calculation model is functioning correctly, follow these steps to create a validation table or widget:

1. Go to the Excel add-in section on the left side panel.
2. Click on the plus icon to create a new table.
3. Select the table according to the calculation model you created. A list of fields will be displayed.
4. Drag and drop the following fields into the 'Display fields' section:
   'Reporting Month'
   'Data Type'
   'Account Group L1'
   'Amount'
   'Calc_Model_Name' 
5. Turn on the 'Dimension table' toggle.
6. Choose 'Reporting Month' as the dimension field and 'Amount' as the value field.
7. Drag and drop the following fields into the 'Filters' section:
   'Account Group L0' = "Balance Sheet" (for 'Cumulative to M' model validation, filter to "P&L"). 
   'Scenario' = "Actuals"
8. The table you created will be shown in the 'Preview table' section on the right.
9. Validate the numbers: check that the Calculation model's Beginning Balance amount reflects the sum of the Beginning Balance and Activity from the previous month.
   
   Example Validation:
   in the attached screenshot, the Onetime Beginning Balance was set for January 2024. Using the Calculation model, monthly Beginning Balances were generated.
   In the validation table:
   The February 2024 Asset Beginning Balance is $1,000, which is the sum of January 2024 Beginning Balance ($500) and Activity ($500).

   The March 2024 Asset Beginning Balance is $1,500, which is the sum of February 2024 Beginning Balance ($1,000) and Activity ($500).
   

Updated July 11, 2024 09:24