Financial Forecast Bulk Edit Functionality

Modified on Sat, 20 Apr 2024 at 09:05 AM

Fluid's Financial Forecast upload functionality allows financial administrators to seamlessly import and bulk-edit non resource financial forecasts for multiple projects. This feature is not only a time-saver but also helps ensure that financial planning is both accurate and adaptable to changing project needs.

Efficiency in Financial Planning: Manually entering forecast data for each project is time-consuming and prone to errors. The bulk upload functionality allows for the efficient handling of large volumes of financial data, reducing the risk of manual errors and freeing up time for strategic analysis and decision-making.

Seamless Third-party Integration: Many organisations use separate systems for finance and accounting. The Financial Forecast upload feature allows for the seamless transfer of financial forecast data from third-party systems into the Fluid. This integration ensures consistency across platforms and enhances data integrity.

The upload functionality is available from the Financial Administration page and only users with the Financial Administration role on their account can use the functionality.

Understanding the Components of a Financial Forecast Record

A financial forecast record is essentially a dataset that represents expected financial forecast activities over a period of time, which could encompass several months. It includes a set of static and dynamic components.

The static components — those that typically remain constant across the forecast period — are as follows:

Financial Reference: An identifier for the forecast entry, allowing for precise tracking and reference. It can be unique to each record or shared among multiple entries representing related forecasts. For example, a purchase order (PO) number, a purchase order (PR) number or a budget item code.
Expense Category / Expense Type: These classify the nature of the forecast. The category is a broad classification such as 'Hardware' or 'Operational', while the type is more specific, detailing the exact nature of the expense under its category, for instance, 'Servers' or 'Utilities'.
Currency: Indicates the currency code (GBP, USD, EUR, etc.) in which the financial forecast values are denoted.
Description: The description can be used to provide additional details or justification for the forecast record.

The dynamic components—those that change with each entry and represent different points in time—are as follows:

Date: The date the forecast is expected to occur.
Value: For each date, there is an associated value, detailing the expected amount of money related to the forecast.
Comment: Provides additional notes specific to that particular forecasted value and date.

In practice, this structure means you can have multiple 'Date', 'Value', and 'Comment' entries under the umbrella of a single 'Financial Reference', 'Expense Category/Type', 'Currency', and 'Description'. For example, a financial forecast record for the 'Hardware' category might have separate entries for January, February, and subsequent months, each with its own date-specific value and any comments if needed.

Financial Forecast File Format Description

The financial forecast file is composed of the following columns, each containing specific data types and values:

Column Title	Description	Data Type	Notes
A. Financial Ref	Identifier for the forecast entry. Please refer to the upload rules below for more information about how this field is used during the upload process.	Text	This field is not mandatory.
B. Project Ref	The external reference of the project to which the financial forecast applies to.	Text	This field is mandatory and the project must exist for the record to be processed. The row will be skipped if the project reference is invalid. The upload process will stop if the value is left blank as it indicates the end of the file.
C. ExpenseCategory	The expense category the forecast applies to.	Text	This field is mandatory and the value must be a valid expense category. If not, the row will be skipped.
D. ExpenseType	The expense type forecast applies to.	Text	This field is mandatory and the Expense Category / Expense Type combination must be a valid. If not, the row will be skipped.
E. Local currency code	The currency code for the forecasted amount.	Text	If left blank, the currency will be set to the default currency. If the currency is invalid, the row will be skipped.
F. Description	Additional details or justification for the forecast record.	Text	If left blank, no description will be set.
G. Date	The date when the expense is expected to occur.	Date	The date must be greater than the capitalisation lock date. Otherwise, the row will be skipped.
H. Value	The forecasted amount value.	Number	This field is mandatory. If left blank or if the value is not a number, the row will be skipped.
I. Comment	Additional information or rationale that provides further insight into the forecast amount.	Text	If left blank, no comment will be set.
J onwards.	Financial Custom Properties

Financial Forecast Upload Guidelines

You can use the upload feature to add new or update existing forecast records for projects. Ensure the following information is provided in the upload file for successful processing:

Project External Reference: Unique identifier for the project the forecast applies to.
Expense Category and Type: Specifies the expense classification. As this upload can only be used to update non resource financial forecast, the class of the expense type must be 'Cash' and not 'Resource'.
Date: Input as day/month/year; however, only the month and year are used for processing.
Amount Value: Forecasted financial amount for the record.

Key points to note:

Fluid records forecasts at the month level. The day entered is not considered, with the system only referencing the month and year.
Records are identified and managed based on a unique combination of 'financial reference', 'project reference', 'expense category', 'expense type', 'currency', and 'month/year'.
If a forecast record matching this unique combination already exists, it will be updated with the new amount and comment provided.
When multiple entries for the same unique combination of identifiers within the same month/year are present in the file, the system consolidates these into a single record by summing the values and combining the comments.

Let's look at various scenarios to better understand the processing rules.

Scenario 1 - Each line entry represents a different record

Creating new forecast records

In the above example, each record represents a different forecast record as the combination of 'financial reference', 'project reference', 'expense category', 'expense type', 'currency' and 'month/year' for each line is unique. This will result in three new forecast records being created if no such records already exist for the project.

Note: If no Financial Reference is provided for a forecast record, it will be assigned an empty value. Matching and processing will proceed with the Financial Reference considered as empty within the unique identifier combination.

Updating forecast records

You can also use the bulk edit functionality to update existing records.

Let's continue to use our example. If we want to update the April forecast for servers, we just need to upload a new file with the new forecast value and comment.

As there is already a record matching the 'financial reference', 'project reference', 'expense category', 'expense type', 'currency' and 'month/year' combination values, the existing record is updated with the new value and comment.

The above example will result in the April amount value for hardware / servers to be changed to $3,000 and the comment to be changed too.

Note: The same result would have been obtained if the date value in the updated file example had been changed to another day in April. Forecast are recorded at the month level so the upload process does not use the day value when looking at matching records.

Scenario 2 - Multiple line entries for the same record

Let's look at the below example. There are three distinct forecast records, each having multiple entries.

Forecasts with Financial Reference 'PO 8861':
- As all entries fall on the same day in April, the forecast values will be combined, resulting in a single April forecast value.
- Comments from these entries will be compiled into one.

Forecasts with Financial Reference 'PO 1245':
- The two entries that have a date in May will have their values summed up and saved as the May forecast.
- The June entry will be treated separately and saved as the June forecast value.

Forecasts without a Financial Reference:
- As each entry falls in a different month, they will be individually recorded as separate monthly forecast values.

Note: When multiple entries have the same 'financial reference', 'project reference', 'expense category', 'expense type', 'currency' and 'month/year' but different 'description' and 'custom property' values, the description and custom property values of the last entry will be saved.