For each of the Central Structure Items that you have defined in the Report Order in the previous lesson, you will need to enter at least the Functions, Expenditures, Incoming and Allocations in order for your Cost Plan to accurately calculate. In this lesson, we will go over entering data into each of the tables found when you expand a structure item in the navigation menu.
Creating Functions
A Function within a Structure Item is most often aligned with the major responsibilities and services provided by the Structure Item.
A function is defined as a significant task that a Structure Item performs throughout the year. There is no limit to the number of functions you designate. It is at the discretion of the agency to determine the total number of functions to create. This is specific to an agency and is usually determined by the Structure Item itself. Only one function is required to be created for each Central, however, you can create more depending on how granular you’d like to be in your Cost Allocation Plan.
Open the Function table in the workspace by expanding the Structure Item in the left hand Navigation Menu and clicking the View icon to the right of the Functions menu item.
In order to create a new function, first, click the “+” button and then fill out the fields in the table.
Func Name - This is the name of the function and will appear on the report as it is entered here.
Allocated - Choose one of the following:
Common Allocation - Choose one of the Common Allocations that have already been entered into the system.
Unique Allocation - Enter an allocation unit which is unique to this function.
Not Allocated - The expenditures associated with this function will not be allocated to other departments.
Common Allocation Name - If you have chosen the Common Allocation option, in this field, you will now choose the Common Allocation you would like to use for this function from the drop-down menu.
Basis - If you have chosen a Unique Allocation, enter the Allocation Basis that you would like to use for this function. An Allocation Basis is a statistical unit which correlates to the instances of service provided by the function. For example, an acceptable Allocation Basis for a Payroll function would be “# of paychecks per department” or “# of FTEs per department.” These are good examples of Allocation Basis because the instances of Payroll run for a Structure Item increase as the number of paychecks increase, or the number of FTEs increase. If you have chosen Common Allocation this field will be automatically populated by the Basis field defined in the Common Allocation Details table.
Description - This is a narrative description of the RI’s function. In our payroll example, this may read: “Allocates the cost of Payroll to Departments served based on the number of Full-Time Equivalents per Department.”
Exclude From CFR - Check this check box if this function is unallowable under the 2 CFR part 200 Uniform Guidance. If this checkbox is checked when you run a 2CFR report, costs associated with this function will not be allocated.
Once you have completed entering your functions you can now move on to entering your expenditures.
Importing Your Expenditures
In the Expenditures Table, you are entering the costs for the Structure Item that you are going to allocate to the Receiving Structure Items.
Expenditures represent the money that an Agency spends to pay for the services or functions within its department. Expenditures generally include employee salaries and benefits, supplies and services, equipment maintenance and any rent or lease costs from facilities that are not owned by the Agency.
When you navigate to the Expenditures table there will already be two lines in the table with the names “Salaries” and “Benefits.” These rows can be used to combined rows of multiple salaries or benefit expenditures into one row in your final report. This feature is optional -- you can provide whatever granularity of data you would like.
To Import Expenditures first, expand the Structure Item in the left hand Navigation Menu and clicking the Up Arrow Icon to the right of the Expenditures menu item.
In the Import menu, click the Choose File To Upload button and select the Allocate Upload Data Template from the directory. Or you can simply drag the Allocate Upload Template into the drag and drop area of the Importer.
In the Select Sheets to Import sections, select the Expenditures sheet for the Central you are importing into and then click the NEXT button in the lower right corner of the importer window. This will advance you to the Match Columns tab of the Importer.
On the Match Columns tab of the importer, you are matching the columns in the excel to the columns of the table in Allocate. Once on the Match Columns tab make the following selections:
In the Expenditure Name drop-down, select the Name field.
In the Total drop-down, select the Total field.
In the Type drop-down, select the Type field.
In the Roll Up drop-down, select the Salaries and Benefits Roll Up field.
In the following function columns, select the related function in Allocate from the drop-down menu.
Click the NEXT button in the lower right corner of the Match Column tab. This will advance you to the Overview tab of the Importer.
On the Overview tab, you have the ability to review and edit any data before it is Imported into the system. Make any changes you may deem necessary simply by clicking into the field you wish to edit and making the necessary changes. Once the data looks the way you would like it to be once imported into the system, simply click the Import Sheets button in the lower right corner of the Import window.
WARNING: This will is the final step in the import process and clicking the Import Sheets button will initiate the import process.
Once the Import is complete a green message will appear in the lower left corner saying “Import into Expenditures complete”. You can now open the view the imported data by clicking the View Edit Workspace button (Eye Icon) to the right of the Expenditure menu item. This will open the Expenditure table into the workspace.
You can now edit any data in the Expenditure table as necessary. For further information on the Expenditures table, you can watch our support video on the expenditures here.
Entering the Incoming Distribution
Incoming costs relate to the systems double step down methodology. The incoming distribution within each Central Report Item governs how incoming costs from other Central Report Items are treated. These costs come in as a single figure and need to be distributed to each of the functions within the Report Item.
For example, the Human Resources department relies on the Finance department to manage its payroll. Therefore there needs to be a method in which Finance’s cost of doing HR’s payroll is spread through HR’s functions. The incoming table is where you dictate how those costs are distributed.
Open the Incoming table in the workspace by expanding the Structure Item in the left hand Navigation Menu and clicking the View icon to the right of the Incoming menu item.
When you open the Incoming table there will already be a Default row. Generally, you will not need more than this one row. To complete the Default selection, fill out the following fields in the row:
Dist Type - In the default line you will need to designate in which way you would like to distribute your costs across the functions.
SAL - If Dist Type is set to SAL, it will spread the amount across the functions based on the distribution of the Salaries line in the Expenditures table.
PROP -The PROP distribution is used to proportionally distribute the incoming costs across the functions by entering the desired proportion into each Function Column.
EXP - If Dist Type is set to EXP, costs will be spread by the percentage of total Report Item expenditures associated with each function as determined by the distribution in your Expenditures table.
Function Columns - You only need to enter data into the Function columns if you have selected the PROP distribution type. There is one Function column for each Function of the Structure Item. They are intended to enter the proportional amount that you would like to attribute to that particular Function for the incoming costs. For example, if there are 2 functions in a given department then you need to show the incoming costs to the 2 functions. In our example, if the incoming costs should be distributed 50% to recruitment and 50% to labor relations then you would enter a “50” in the recruiting column and “50” in the labor relations column. The system will then spread that particular expenditure on the respective ratio of values in each function.
If you would like to spread a particular Central Report Item’s function in a different way than you are spreading the default line, you can add another row by clicking on the “+” Icon.
Report Item - For each row after the default row you will need to specify the Report Item for which you want to change the incoming distribution. If you have a Cost Plan Filter active then you will only see the Central Report Items in that Cost Plan. Select a Central Report Item.
Special Function - Select the Function for which you would like to change the incoming distribution. Only Functions for the Central Report Item selected in the Report Item field will appear in the drop-down.
For any additional rows after the Default row, you must use the Distribution Type “PROP”, and therefore must specify the proportions you would like to use in the Function Columns.
Importing Allocations
The Allocations tab is where a Structure Item allocates costs to other Structure Items by function using an “Allocation Basis.” A few examples of an Allocation Basis are Full-Time Employees per department, Expenditures per department and, Transactions per department. These Allocations correlate to the services that have been provided.
For example, the Payroll Function could use the “# of payroll checks per department/fund supported” because the cost of service from the payroll function increases as the number of paychecks increase.
Open the Allocations table in the workspace by expanding the Structure Item in the left hand Navigation Menu and clicking the View icon to the right of the Allocations menu item.
You cannot add rows to this table. To edit the data simply click into the field that you would like to edit and start entering data:
Org - The number associated with the parent Organization for the Structure Item in that row.
Fund - The number associated with the Fund or parent Fund for the Structure Itemin that row.
Dept - The number associated with the Department or parent Department for the Structure Item in that row.
Div - The number associated with the Division for the Structure Item in that row.
Structure Item - The name of the Structure Item in that row.
Function Columns - There is one Function column for each Function that has a Unique Allocation set for it in the Allocated column of the Functions table, those functions that have Common Allocations are pulling the allocation units from the Common Allocation Units table. In the Function Column header, it will show you the name of the function, as well as the Allocation Basis used. In the Column, you will enter the Allocation basis for each Structure Item in that row. For example, if the Allocation Basis for the Payroll function is FTEs and Finance has 4 FTEs, you would enter a 4 into the field in the Finance row of the Payroll Column.
If you would like to quickly find the Structure Item that you are looking for you can use the Name and Number filters at the top of the column to filter the table. Simply enter the value you are looking for in the Name or Number columns and the filter will limit the rows to only the values that match the terms you have typed into the filter.
You can also Import data into the Allocations table.
Locate the Allocations menu item in the Navigation Menu and click the Up Arrow Icon to open the Import menu.
Click the Choose File To Upload button and select the Allocate Upload Data Template from the directory. Or you can simply drag the Allocate Upload Data Template into the drag and drop area of the Importer.
In the Select Sheets to Import sections, select the Allocations sheet related to that Central and then click the NEXT button in the lower right corner of the importer window. This will advance you to the Match Columns tab of the Importer.
On the Match Columns tab of the importer, you are matching the columns in the excel to the columns of the table in Allocate. Once on the Match Columns tab make the following selections:
In the Structure Item Name drop-down, select the Structure Item option. With the Allocations upload, we are matching rows in the excel to existing structure items in the software. This can either be done with the Department Name, or the Department Number, or both. To make sure the most matches are found, map both the name and number to the Structure Item field.
In the Structure Item Number drop-down, select the Structure Item option.
In the Allocation 1 drop-down, select the related Allocation in Allocate.
If you have additional Allocations, you can map those to their related Allocations.
Once you have mapped the fields, click the Next button to advance to the Overview step.
On the Overview tab, you can edit and review the data before it is imported into the system. If the system could not find a match to a particular department in the spreadsheet, it will highlight that row with a red outline. To correct the matching, click into the Structure Item field and select the department, division or fund you intended to import that row into.
Once the data looks the way you would like it to be once imported into the system, simply click the Import Sheets button in the lower right corner of the Import window.
WARNING: This will is the final step in the import process and clicking the Import Sheets button will initiate the import process.
You can now open the Allocation stable and make any edits you need, or to simply view the data.
Once you have completed entering your Functions, Expenditures, Incoming and Allocations you will need to repeat this process for all of your Central Structure Items in order to complete the report. Once you have followed this process for all Centrals you are ready to move on to Lesson 4. Click here to continue to the Lesson 4 video.