Table of Contents

Financials

Export to CSV

The ability to export the data from your finance grid has been released. This feature is available through the Data menu.

This image shows the Data menu

This csv file:

  • Only shows the csv data in vertical mode, even if the user was viewing the Financials grid in horizontal mode
  • Shows the timeline zoom level the user was reviewing, so if the Financials grid was displaying Month view, the data in the csv will be grouped by month, Year view will show the data grouped by Year, etc
  • None of the grouped total rows are included in the csv file
  • The nested Financial Category field is broken down into its individual categories in the csv file, with the "Category" field displaying the lowest level setting for the financial item.

Editing Finance Data In Grid

By default, the Finance Grid will now allow editing of Financials data via the Grid itself. This is managed through the this configuration setting.

When displayed in the Finance Grid, data is aggregated as per the View > Period chosen by the user who is viewing the data. The aggregated value that is displayed in a Finance Grid cell can be made up of any number of individual transactions that fall within the range of the related period for the Financial Item and transaction type.

For example, say the following Forecast transactions exist for a particular Financial Item in June 2023:

Transaction Date Forecast Value
1 June 2023 $50
7 June 2023 $50
15 June 2023 $75
21 June 2023 $100
28 June 2023 $125

When represented on the Finance Grid in Month mode, the Forecast transactions for the month of June will be aggregated to display a value of $400 in the June Forecast cell.

This image shows an In Grid Editing aggregated value

If the user then chooses to edit the $400 amount shown in the June 2023 forecast cell for that Financial Item, then the underlying transactions will all be updated relative to the change in aggregated value.

The formula used to determine the new value of the underlying transactions is:

[New Transaction Value] = [Old Transaction Value] + (([New Aggregate Value] - [Old Aggregate Value])/[Number Of Transactions])

If the user was to modify the aggregate value from $400 down to $300, then the underlying transactions would be adjusted according to the above formula and would result in the following Transactions after saving.

Transaction Date Forecast Value
1 June 2023 $30
7 June 2023 $30
15 June 2023 $55
21 June 2023 $80
28 June 2023 $105

Transactions with a negative value (e.g. -$100) will be treated in the same way as positive value transactions, with any changes to the aggregate value being distributed evenly across all transactions that form the aggregate value. Because the distribution across underlying transactions is linear, this may result in some positive transactions going negative and/or some negative transactions going positive depending on the scenario. Users can choose to modify individual transaction values via the Edit Cost Item/Edit Budget Item form.

If the user chooses to enter a value into a Finance Grid cell that does not yet contain any underlying transactions, then a new transaction will be created and placed on the first date of the aggregated period. (So, if in Month view, a new transaction for August 2023 would be placed on 1 Aug 2023.)

If an aggregated value is adjusted to $0, then all underlying transactions will be adjusted to have a value of $0.

Note

If an aggregated value is removed/deleted from a cell, then once saved, all underlying transactions for that cell will be deleted.

If any of the following conditions are true, then a cell will be locked and will prevent changes from taking place:

  • Finance Grid In-Grid Editing feature is turned off
  • User has read-only access to the Project
  • Transaction Type is Locked at:
    • the Project level
    • the Financial Item level
    • the Fiscal Period level
    • the financialsGridSettings Configuration Setting Transaction Type Security Locks
  • Fiscal Period itself is Locked
  • Any transaction which is used to calculate the aggregated value is Locked

Grouping

You can display the financial grid by Category or not show any grouping.

When the grid is in ungrouped mode the values is sorted by Financial Type:

  • First Budgets, with the Name values sorted in alphabetical order within this Financial Type.
  • Then Costs (Forecasts/Actuals/etc), with the Name values sorted in alphabetical order within this Financial Type.

When the grid is in Category grouped mode, the values are grouped within an accordion view with their Financial Categories starting at the top-level values and showing all the sub values within the Financial Category. The values within each of the Financial Categories are sorted in the following manner:

  • First Budgets, with the Name values sorted in alphabetical order within this Financial Type.
  • Then Costs (Forecasts/Actuals/etc), with the Name values sorted in alphabetical order within this Financial Type.

<- Back to Training TOC

Edit this page