SI-GL-RT19.NFP - Grant Allocation

Applies to

• Product version: Velixo NX
• ERP: Sage Intacct
• Functional area: Financials, Budgeting, Other
• Industry: NFP
• Plan: All
• Template type: Production Report template

TABLE OF CONTENTS

• Applies to
• Description
• Coverage
• Typical audience
• Features
• Preview
• Download this template
• Documentation
  • Grant Management
    • Filter
    • Add Grants Data Table
    • Existing Grants Data table
  • Grant Income
    • Filter
  • Grant Expense Allocation
    • Filter
    • Allocation table
  • Employee Expense Allocation
    • Filter
    • GL code and Grant Allocation table
    • Allocation table
  • Dashboard
• User Guide
  • Using the Template
  • Using the Grant Management
  • Using the Grant Income
  • Using the Grant Expense Allocation
  • Using the Employee Expense Allocation
  • Using the Dashboard
• Version History

Description

The SI-GL-RTN.NFP Grant Allocation Template is a  tool for nonprofit organizations to efficiently track and allocate grant funds across projects and programs while maintaining compliance with funding requirements. It simplifies grant management by aligning expenses with budgets, automating allocation calculations, and streamlining the creation of new grants. With built-in functionality to upload directly to Sage Intacct, this template enhances accuracy, transparency, and ease of use in managing grant-related financial activities.

Coverage

This workbook includes the following sheets:

Typical audience

The typical users of this type of report are Finance professionals: Accountants, CFOs, Controllers.

Features

The following features are used by this report. To use this report, please ensure that your Velixo license includes all of them, (or contact Support or Sales):

• Financial functions
• List functions
• Query functions
• Budget Writeback

Preview

Download this template

Download

💡Having trouble downloading the file?

Simply right-click on the link and select Save link as

Documentation

Using the SI-GL-RTN.NFP Grant Allocation template is simple and efficient. This template helps non-profits allocate grant funds by allowing seamless updates to grant allocation records, making it easier to manage finances accurately in Sage Intacct.

Grant Management

This tab is designed for creating and uploading new grants directly to Sage Intacct. It also displays all existing grants in the system, providing a more comprehensive view.

Filter

To assist users with Grant creation, two filters are available in the upper-left corner of the sheet:

• Entity: specifies the entity for which the Grant will be created. If left blank, the Grant will be created at the top-level entity.
• Grant to Copy: displays a list of all existing Grants in the system. Selecting a Grant from the list populates the table, which can then serve as a template for creating the new Grant.

Add Grants Data Table

This table is used for uploading new Grants to Sage Intacct. It includes only editable data columns that correspond to the attributes of the Grant. These columns align with the available fields in Sage Intacct, making them easy for users to identify and work with.

Existing Grants Data table

To provide a clearer overview of all the grants in the system, the sheet also includes a list of all available grants. Similar to Add Grants data table, the details of existing grants can be updated through the template.

Grant Income

This sheet is primarily used to track the income generated by each Grant for each period over a span of three years. It provides users with a comprehensive view of which grants are generating the most income and which may require further attention.

Filter

Located at the upper left of the sheet, the filters include:

• For year: the year from which to extract the data.
• Location: a list of all available locations in Sage Intacct.
• Books: the books from which to extract the data.

Grant Expense Allocation

This sheet is used for tracking and uploading or updating budgets directly into Sage Intacct. Additionally, it features a comparison tool that allows users to compare the current budget with an existing budget from a different year. This helps users determine which adjustments may be needed for the current budget year.

Filter

Located at the upper left of the sheet, the filters are divided into two categories:

Grant and budget details:

• Grant: the grant to which the budget will be uploaded.
• Start date: a read-only field displaying the start date of the selected grant.
• End date: a read-only field displaying the end date of the selected grant.
• Grant Income: a read-only field showing the total income budgeted for the selected grant.
• Percentage allocated: a read-only field indicating the percentage allocated for the grant. This is the sum of the budget allocated for the current year and the selected budget for the comparative year.
• Budget ID: the ID of the budget to upload or update in the system.
• Budget year: the year of the budget.
• Allocation type: choose either manual or equal allocation.
• % of budget to allocate for the budget year: the percentage of the grant income to allocate to the current year’s budget.
• Grant budget to allocate: a read-only field showing the amount of the budget to allocate based on the specified percentage.
• Total budget allocated: a read-only field showing the total amount of the budget allocated for the current year.
• Remaining budget: a read-only field showing the remaining budget after deducting the current and comparative year’s budgets from the grant income.

Comparison details

These filters drive the details for the comparative year, which is used to compare the current year's budget. In addition to the budget, they also pull the actual balances, showing the actual expense amounts incurred for the comparative year. The filters include:

• Select year: the year to be used for comparing the current year’s budget.
• Budget ID: the budget ID to compare the current year’s budget with.
• Books: the books to be used for extracting the actual balances.
• Total budget allocated: a read-only field showing the total budget allocated for the selected year and budget ID.

Allocation table

To facilitate a smooth budget allocation, this template lists all the available expenses based on Sage Intacct QuickStart categories (NFP) and categorized into:

• Operational Expenses
• Employee-related Expenses
• Financial and Regulatory Expenses
• Program and Miscellaneous Expenses

Key metrics are included to show the allocation of the balance for the selected year and budget ID, covering both the budget and actuals. This provides users with an overview of the performance against the budget from the comparative year, helping them use that information as a basis for allocating the current year’s budget.

User-input fields, such as Employee and Memo, are included to provide additional details on the budget upload. These fields allow users to assign the budget line to a specific employee and add a memo to provide further context or details about the budget.

There are two allocation types users can choose from to allocate the budget for the current year:

Equal Allocation

This option distributes the grant budget equally across periods and accounts.

Manual Allocation

This option allows users to manually enter the budget for each period and account.

Employee Expense Allocation

This sheet is designed to facilitate the allocation of expenses for specific employees across multiple grants. It streamlines the employee allocation process, ensuring a more efficient and accurate distribution of grant funds. 

Filter

Located at the upper left of the sheet, the filters included are:

• Employee: lists all the employees on the Sage instance.
• Budget ID: the ID of the budget to upload or update in the system.
• Budget year: the year of the budget
• Budget description: a brief description of the budget

GL code and Grant Allocation table

These two tables determine how amounts are allocated across the periods of the selected budget year. In the GL table, amounts must be assigned to their corresponding GL codes. The allocated amounts are then distributed based on the percentages defined in the Grants table, ensuring accurate and proportional allocation.

Allocation table

The allocation table is generated based on inputs from the GL and Grants tables. It spans 12 periods and maps GL codes to their respective grants, ensuring a precise and structured budget upload.

Dashboard

This sheet features charts and visualizations derived from the data in the Grant Income and Grant Expense Allocation sheets. These visual tools provide users with a clear illustration of the budget’s performance compared to actual data, as well as how the pre-allocated budget will fare in relation to the comparative year's performance.

User Guide

Using the Template

Now that we've covered the template overview, let's dive into a step-by-step guide on effectively utilizing the template.

• Set the Connection Name on the Options sheet: crucial for all processes, set the Connection Name on the Options sheet by updating cell A2. Ensure it matches the name used during Velixo application login.

• Update the Options Sheet: as the foundation for the filters on the main template, keep the Options sheet up to date. It should always and automatically capture the latest dimensions and structures from your Sage Intacct instance.

Using the Grant Management

• Navigate to the Grant Management tab and select the value for the filters, if needed.

• Populate the editable column fields with the details of the new Grant.

• Once details for the new grant are complete, initial the Velixo Writeback process.

• After a successful upload, the status in Column B (Writeback Status) will change to 'Line uploaded'. 

Tips: If multiple Grants need to be created, simply expand the Excel table. The corresponding formulas will automatically extend, providing additional input fields for the extra Grants.

Using the Grant Income

• Go to the Grant Income tab and choose the desired filters.

• Changing or selecting any filters will automatically refresh the data table

Using the Grant Expense Allocation

• Navigate to the Grant Expense Allocation tab and select the Grant to upload the budget data to.

• Once the Grant is selected, specify the values for the following filters, as they will serve as the basis for the allocation details.
  • Budget ID
  • Budget year
  • Budget description
  • Allocation type
  • % of grant budget to allocate for the budget year

• Populate the Comparison Details filter. This step is optional and only necessary if the current year's budget needs to be compared to an existing budget ID.

• If necessary, specify the Employee and add a Memo to provide additional context for the budget line.

• If the selected allocation type is Manual Allocation, input the amount manually for each line.

• Once details are finalized, initiate the Velixo Writeback process.

• After a successful upload, the status in Column AJ (Writeback Status) will change to 'Line uploaded'.

Using the Employee Expense Allocation

• Navigate to the Employee Expense Allocation tab and select the Employee to upload the budget data to.

• Enter the Amount to Allocate and Allocated % values in the GL and Grants table

• The values entered will serve as the basis for distribution in the Allocation table. If applicable, include a memo for reference.

• Once details are finalized, initiate the Velixo Writeback process.

• After a successful upload, the status in Column L (Writeback Status) will change to 'Line uploaded'.

Using the Dashboard

• Simply navigate to the Dashboard tab after completing the Grant Income and Grant Expense Allocation tabs. The charts refresh automatically whenever changes are made to either of these tabs.

Version History