1 Reply Latest reply on Mar 4, 2012 8:50 PM by eibcga

    Identifying/Calculating Fiscal Year(s) based on Grant beginning and End Dates

    sarahb_1

      Title

      Identifying/Calculating Fiscal Year(s) based on Grant beginning and End Dates

      Post

      Hi - I'm trying to track grants and FTE expenditures across and within fiscal years (10/01 -9/30).  Some grants are multiple years, some are just months, most don't start and end on the FY dates.  I want to be able to do reports by FY that will come up with all the grants that were active during that FY (even if they go on for multiple FYs), and calculate a total FTE by grant and then overall.  So far, I've got three tables:  Grants (which includes start and end date fields), Employees, and Grants_Employees which includes an FTE field (as a person may be on multiple grants). 

      The difficulty I have is that I don't know how best to identify Fiscal Year(s) for the grants so I can be able to create a report.  Is that a repeating field within the Grants table?  Is it a separate table?   Any suggestions would be greatly appreciated.

       

      sarah

        • 1. Re: Identifying/Calculating Fiscal Year(s) based on Grant beginning and End Dates
          eibcga

          One of many ways to deal with this one... but my answer goes something like this:

           

          Create the following tables:

          Contacts (to track all Employees and Vendors to whom you pay for expenditures, and Grantors from whom grants were received)

          Transactions (to track money paid to or received from the employees, vendors, and grantors)

          Details (to track financial transaction details using double-entry bookkeeping)

          Accounts (to track financial transaction details by separate categories of revenue and expense financial ledger accounts)

          Projects (to track all the various types of projects which receive various grants and other sources of funding, with fields for the project's title, term (start and end date), purpose of project, etc.)

           

          Create the following relationships between the tables by adding the appropriate match fields (primary keys (PK) and foreign keys (FK))

          Contacts::ContactID_PK = Transactions::ContactID_FK (a contact can have many transactions, but each transaction can only have one contact)

          Projects::ProjectID_PK = Details::ProjectID_FK (a project can have many transaction line items, but each transaction line item can only have one project)

          Transactions::TransID_PK = Details::TransID_FK (a transaction can have many transaction line items, but each line item can only have one transaction)

          Accounts::AccountID_PK = Details::AccountID_FK (an account can have many transaction line items, but each line item can only have one account) 

           

          Create other appropriate fields for each table.  For example, you may have these fields:

          Accounts Table (AccountID_PK, Acct_Name, etc.)

          Projects Table (ProjectID_PK, ProjectTitle, DateTermStart, DateTermEnd, Purpose, etc.)

          Contacts Table (ContactID_PK, Name, Addr1, Addr2, City, State, Zip, Phone, Fax, Email, etc.)

          Transactions Table (TransID_PK, ContactID_FK, Date, MemoTrans, RefNumber, etc.)

          Details Table (TransID_FK, TransItemID_PK, MemoLineItem, AccountID_FK, ProjectID_FK, Amount, etc.)

           

          Create appropriate calculation fields in the Details table to provide for various subsummary reports of the general ledger (based on records from the Details table, sorted by Account).  Summary field, Details::cBalance = Total of Amount (running).  You can do a Find on the Details table using a date range that you specify, and you will get totals for that range by account).

          On the Transactions layout showing records from the Transactions table occurrence, add all the appropriate fields and related fields, and add a portal to the Details table occurrence (make sure to turn on 'Allow creation of records in this table via this relationship', i.e., between the Transactions table and the Details table).  The Transactions layout could have the following fields:

          Transactions::TransID_PK,

          Contacts::ContactID_FK,

          Contacts::Name

          Transactions::Date

          Transactions::RefNumber

          Transactions::MemoTrans

           

          The portal on the Transactions layout, based on the Details table, could have these fields:

          Accounts::AccountID_FK

          Accounts::Acct_Name

          Projects::ProjectID_FK

          Projects::ProjectTitle

          Details::MemoLineItem

          Details::Amount

          Details::cBalance

           

          For example, lets say Project A receives funding from Grantor A with a grant for $100,000, which is spent over time by paying $80,000 to various employees, and $20,000 to various vendors for other expenses on the project, such as equipment, supplies, etc.  To record the funding received, in the Transactions layout, add a new record and fill in the fields for contact name (i.e., the name of the Grantor), date, ref #, memo.  Then in the portal, add a new record in the portal and fill in the fields for account, project, memo, amount.  For instance, account to increase could be Cash or Accounts Receivable, and amount would be $100,000.  Then add a 2nd record in the portal to record the corresponding increase to the Grants Revenue account for $100,000 as a negative amount.  Thus, the sum of all amounts in the Details records in the portal would be zero (i.e., $100,000 - $100,000 = 0), which means debits = credits.  Over the term of the project(s), add a new record in the Transactions layout for each instance a grant is received, an employee is paid, or a vendor is paid, again, adding the appropriate portal line items for each.

          After all transactions are recorded as they occur, you can do a Find on the Details table by entering in a date range in the Date field to run a subsummary report to get a report on all transactions during the period specified.  A subsummary report would need to be created by creating a new layout based on records from the Details table, adding any pertinent fields from the Details and other tables, to essentailly make a General Ledger report (a subsummary report when sorted by Accounts::AccountID_FK).

          I could go on but I'll stop for now.  Other people here may have other suggestions, or better ones.  It will depend on your circumstances and business rules.