Lesson Prep:
Before starting this lesson please watch the Allocate Overview Video and the Working in the Workspace Video. These videos will give you a brief introduction into navigating around allocate and working in the workspace. Once you have watched these videos then you will be ready to start entering data.
Also, you will want to make sure that you have collected your Structure data (first three tabs in the Allocate Data Upload Template) as this is the data that will be Imported in this lesson.
Allocate Data Overview
In order to create a system which is easy to use yet has everything you need for your Agency, we have created a different way of storing data. This allows you to have Structure data, Fiscal Period data and Cost Plan data separated and editable in their own sections.
These three types of data are all treated differently in the Cost Allocation process and therefore the different ways you interact with the data should be reflected in the software.
Structure
The structure refers to the Funds, Departments, Divisions, and Programs that exist in your agency. These may change slightly from year to year but the majority of that data will remain stagnant over time. Therefore, you don’t want to have to reenter the same data year after year. In Allocate, the Structure Item Tables allow you to enter your structure data once and then incorporate that data into Cost Plans as you need them. If you do need to adjust the Structure of your Agency, simply edit the data in the Structure Item Tables and that data will populate into the Cost Plans where relevant.
Fiscal Period
In Cost Allocation, no matter if you are doing a 2 CFR, Full Cost, Citywide or an Interdepartmental plan, you need to specify some period of time for which you are going to collect the expenditures incurred by your Central Structure Items. Whatever that period of time you are collecting expenditures for is what you will define as your Fiscal Period. For example, many cities will use a standard fiscal year. In this case they will use a Fiscal Period of something like "2018-2019". Some Interdepartmental plans are run quarterly. In this case the Fiscal Period could be "Q3 2019". Allocate allows you to define any period of time that you would like when creating your Fiscal Period.
In Allocate, the Fiscal Period contains the Function, Expenditure, Labor Distribution, Incoming, Allocations, Direct Billed and Revenue Reconciliation data. Each Fiscal Period has its own unique table independent of each other. This means that you can create as many Fiscal Periods as you would like in order to run scenarios and create 'what if?' cost plans.
Cost Plan
Because you can enter your entire Agency structure, and you can have different data for different Fiscal Periods, the Cost Plan is where you are going to organize your data for the final report. In the Cost Plan, you can specify which fiscal periods and which Structure Items you want to include in the report.
For example, in the Structure Item Table you may have all of your Departments and Divisions, but you may only want to run an interdepartmental plan for the IT Department. You are not going to include all of the Departments in that Cost Plan. Using the Report Order tool in Allocate you can specify exactly which Structure Items you want to include and in what order they should appear on the final report. Next, you choose a Fiscal Period so that the report contains the correct expenditures and allocations.
The Cost Plan data also contains those items that appear on the report such as Report Title, Chief Financial Officer and Report Type (Full Cost or 2 CFR part 200).
Allocate was created with you in mind, and is made to be as flexible as you need it to be. By storing the data in this way, Allocate lets you create the exact Cost Allocation Plan that you want.
Navigating Allocate
In Allocate, we have made it as easy as possible to get to your data when you need it.
The Navigation Menu
On the left-hand sidebar of Allocate is the Navigation Menu. It will always be present as you work within the system. The Navigation Menu hosts multiple tools at your disposal.
Accessing Tables
In Allocate, a Table is a combination of columns and rows, in which data can be entered by importing, or by manual input. There are two types of Tables in Allocate:
Structure Tables - Structure Tables are tables in which you enter the entities that will be allocated to and from. To be more explicit, these are the Organizations, Funds, Departments, and Divisions Tables. These tables are not dependent on a Fiscal Period and are entered into the system once and then automatically brought into the other parts of the software where relevant.
Distribution Tables - Distribution Tables are the tables in which you enter the data which impacts the calculations of the cost plans. These are the Functions, Expenditures, Labor, Incoming, Allocations, Direct Billed, and Reconciliation Tables. Distribution Tables are dependent on the Fiscal Period that has been chosen. For example, you will have one set of data on your Finance < Expenditures Table for 2016, and a different set of data for Finance < Expenditures Table for 2017.
The Tables are displayed in the Navigation Menu and are expandable and collapsible using the arrow icon to the left of the Table name. When you expand the menu you will see all of the menu items related to the expanded item. Each Table in the Navigation Menu also has Icons to the right of the label which appears when you hover over the menu item with your cursor.
Import Icon - Clicking on this icon will open the Importer Dialog in which you can import data from Excel into the related table. (For more information see our related article “Importing From Excel.”)
Eye Icon - Clicking on this icon will open the table in the Workspace where you can start to edit the data housed in that table.
For example, if I needed to edit the expenditure data for Finance, I would first expand the Departments Table, then I would expand the Finance Department. Next, I would click on the eye icon next to the Expenditure Table, which will open the Expenditure Table in the Workspace.
The Search Bar
The Search Bar at the top of the Navigation Menu allows you to quickly find Report Items by filtering the Report Item Name. For example, if you are looking for the Finance Department, you can type “Finance” into the Search Bar to narrow down the list of Report Items to just the Finance Department. The Search Bar will start to filter results as soon as you have entered at least three characters.
Cost Plan Filter
The Cost Plan Filter allows you to limit the Report Items in the Navigation Menu to just the Central Report Items in that Cost Plan. While you may have entered your entire Agency structure into the Structure Item Tables, you may only need to enter data into a couple of Report Items in order to complete your Cost Plan. The Cost Plan filter helps you quickly identify which Report Items need data.
Before you can apply the filter effectively, you must create a Report Order for your Cost Plan. Once you have a Report Order you can apply the filter to display only those Report Items for which you need to enter data in order to finish your Cost Plan.
Allocate Logo
If you ever need to quickly return to your Dashboard from the Workspace, you can click the Allocate Logo to bring you back to the starting Dashboard.
Importing your Structure
Once you have collected your Structure data (Funds, Departments and Divisions) in the Allocate Data Upload Template then you are ready to import your agency structure. This is the first step in setting up your Allocate account. Once you have entered your agency’s structure, then we can start to shape your cost plan. You will want to upload all of your Funds, Departments, and Divisions whether or not these Structure Items will end up in your final cost plan. In a later step, you will choose exactly which structure items you will want to include in your cost plan.
Importing Funds
When importing your Structure you want to start with the highest hierarchy and work your way to down to the most specific. This will allow you to import the relationships between the Funds, Departments, and Divisions if you have collected that information in your Allocate Data Upload Template.
In the Navigation menu, click the Import Icon (up arrow) to the right of the Funds menu item.
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 Funds sheet 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 Fund Name drop-down select the Name option.
In the Fund Number drop-down, select the Number option.
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 Structure complete”. You can now open the view the imported data by clicking the View Edit Workspace button (Eye Icon) to the right of the Fund menu item. This will open the Funds table into the workspace.
You can now edit any data in the Funds table as necessary within the workspace.
Structure Item Tables in the Workspace
Now that you have opened the Funds table into the workspace you will notice there are more columns in the table then we uploaded. At this time you do not need to worry about these other data points. We will fill these in where appropriate in other steps. For further explanation of these fields, you can visit our Creating Funds help article here.
Importing Departments
In the Navigation menu, click the Import Icon (up arrow) to the right of the Departments menu item.
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 Departments sheet 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.
Once on the Match Columns tab make the following selections:
In the Department Name drop-down select the Name option.
In the Department Number drop-down, select the Number option.
If you have entered the Related Funds in the Allocate Template Data Upload, then map the
If you have entered the Description field for your Central Departments, you can select the Description field in the related drop down.
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. If you have mapped the related Fund and the system did not find a match to an existing Fund, the Fund field for that row will be highlighted in red. Click into the field and select the intended related Fund from the drop-down menu.
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 Structure complete”. You can now open the view the imported data by clicking the View Edit Workspace button (Eye Icon) to the right of the Departments menu item. This will open the Departments table into the workspace.
You can now edit any data in the Departments table as necessary within the workspace.
For further information on the Departments table, you can visit our Creating Departments help article here.
Importing Divisions
Not all agencies will want to enter the Structure down to the Division level. That is fine if you want to allocate your costs out at the Department level, most agencies do. Entering Divisions, if you wish, will later allow you do run interdepartmental plans, or to simply allocate the Agency-wide indirect cost at a more specific level.
In the Navigation menu, click the Import Icon (up arrow) to the right of the Divisions menu item.
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 Divisions sheet 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.
Once on the Match Columns tab make the following selections:
In the Division Name drop-down select the Name option.
In the Division Number drop-down, select the Number option.
If you have entered the Related Departments in the Allocate Template Data Upload, then map the
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. If you have mapped the related Department and the system did not find a match to an existing Department, the Department field for that row will be highlighted in red. Click into the field and select the intended related Department from the drop-down menu.
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 Structure complete”. You can now open the view the imported data by clicking the View Edit Workspace button (Eye Icon) to the right of the Divisions menu item. This will open the Divisions table into the workspace.
You can now edit any data in the Divisions table as necessary within the workspace.
For further information on the Divisions table, you can visit our Creating Divisions help article here.
Now that you have completed entering your Funds, Departments and Divisions you have completed Lesson 1. If you wish to continue to Lesson 2, you can simply click here.