Relationship_29

MF_GLFact

MD_GLAcc_SurrogateKey

Relationship_34

MF_FYAccountInfo

MD_GLAcc_SurrogateKey

VMD_GLControlAccount

VMD_GLCONTROLACCOUNT

MD_GLAcc_Name

Text or "description" corresponding to a GL code in Colleague, i.e., to one of the 14-digit account codes that identify GL accounts. Normally this text is most useful in reports generated for people who don't understand or care about the codes. Should be defaulted to "Not Applicable," for cases where a GL code is optional, such as for a student-aid grant, which may or may not have an associated GL code, depending on whether it's a Carleton award or a non-Carleton award.

DW_CodeDescLong

varchar(64)

64

MD_GLAcc_Division

General division of the college under which a given GL account is subsumed, e.g., Treasurer, Dean of Admission, etc.

DW_CodeDesc

varchar(32)

32

MD_GLAcc_FunctionalArea

Functional area of college operations. This field is maintained by the Business Office, and suits their reporting/analysis purposes, primarily IPEDS reporting.

This field is defined at the GL department level, although we attach a value for every GL account (i.e., at the object level).

Together with the MD_GLAcc_Function and/or MD_GLAcc_SubFunction fields, this field takes us a significant part of the way towards automating generation of IPEDS figures.

DW_CodeDesc

varchar(32)

32

MD_GLAcc_DepartmentManager

Common name of somebody who manages a budget.

Next level down from SubdivisionManager. The department manager typically has oversight over a number of individual budgets.

We could have placed a Colleague ID here; and for various reasons that would have been a great thing. But the bottom line is that users and reports don't like numbers. They like names. Names look better in reports. Hence this field is a name.

This field is defined at the GL department level, although we attach a value for every GL account (i.e., at the object level).

See the documentation on MD_GLAcc_BudgetManager for more details.

DW_CommonName

nvarchar(64)

64

MD_GLAcc_OtherManager

Common name of somebody in Colleague who manages a budget. We could have placed a Colleague ID here; and for various reasons that would have been a great thing. But the bottom line is that users and reports don't like numbers. They like names. Names look better in reports. Hence this field is a name.

DW_CommonName

nvarchar(64)

64

MD_GLAcc_FundGroupCode

Digit 1 of general ledger account number/code. Used as a way to group funds. 1 = unrestricted; 2 = restricted, 4 = endowment, etc.

See documentation on the MD_GLAcc_Fund field for more details on funds.

As of 2010, these are the active fund group codes. The two-digit funds are listed as subcategories of the fund groups (1X, 2X, etc.):

1X-UNRESTRICTED      
   10   Operating Budget
   15   Operating Special Projects
      
2X-RESTRICTED      
   24   Endowment
   27   Donor Designated Gifts
   28   Government Grants
      
3X-LOAN      
   31   Carleton College
   32   Henry Strong
   33   Perkins
      
4X-ENDOWMENT      
   41   True Endowment
   42   Narrowly Restricted Endowment
   43   Quasi (Board Designated) Endow      
      
5X-PLANT      
   51   Donor Designated Gifts for Plant
   52   Small Capital Projects
   53   Special Maintenance
   54   Maintenance Reserves
   55   Debt
   56   Sustainablity Fund
   58   Deferred Maintenance
   59   Board Designated Gifts for Plant

6X-AGENCY      
   61   Carleton Student Association (CSA)
   62   Other Agency
      
7X-Deferred gifts      
   71   Gift Annuities
   72   Pooled Income Funds @ Kaspick
   73   Trusts @ Kaspick
   74   Held by Others
   75   CAT @ Carleton
   76   Pooled Income Fund @ Carleton
   77   Unitrusts @ Carleton
   78   Life Insurance
      
Fund = 2 digits      
Fund group = first digit      

<None>

char(1)

1

MD_GLAcc_FundCode

FIrst 2 digits of GL code. See the documentation on MD_GLAcc_Fund for more details.

As of fiscal year 2010, these are Carleton College's current fund codes. These codes are offered merely as examples, as they will change from year to year:

10   Current Operations
15   Special Allocations
24   Restricted Endowed Income
27   Restricted Gifts
28   Government Grants
31   College Loans
32   Henry Strong
33   Perkins
34   Coll share-Perkins
40   Endowment assets
41   True Endowment
43   Quasi Endowment
44   Endowment Gains
45   Pledges and unrealized gain
50   Plant assets
51   Capital Projects
52   Small Capital Projects
53   Special maintenance
54   Maintenance Reserves
55   Debt Funds
56   General
57   Essential maint
58   Capital Renewal
59   Gifts collegedesignated plant
61   CSA
62   Agency -- Other
70   Trust assets
71   Gift Annuities
72   Pooled Life Income Fund II
73   Unitrusts
74   Trusts held by others
75   Annuities-sep
76   Pooled Life Income Fund I
77   Unitr.-sep. invested
78   Life Insurance
79   Life Estates

<None>

char(2)

2

MD_GLAcc_SourceCode

Digits 3-6 of GL code. Atomic.

The source field is also known as a "project code." It is typically utilized to specify a particular restriction placed on monies within a fund group. For example, each endowment within the endowment fund is assigned a unique project code. A project number must be used with all funds other than Fund 10 (where typically a default source of 0000 or "Gen" is utilized).

Here are some sample source codes, taken from the Business Office Chart of Accounts Inquiry spreadsheet (2010). These are offered merely as examples. Most operational-budget reporting (unrestricted funds) will use the first code below, 0000 ("Gen"):

0000   Gen
0001   January
0002   February
0003   March
0004   April
0005   May
0006   June
0007   July
0008   August
0009   September
0010   October
0011   November
0012   December
0013   prev mo adj
0015   DIS deposits/pmts
0060   Miscellaneous Institutions - C
0100   Pre-advance
0101   Retirement fund
0102   NCTLA STUDY
0103   Japanese subgrant
0104   Presser Foundation Grant to Mu
0106   MATH DEPT GENERAL FUND
0107   Sherman Fairchild Sci Equip
0108   Luther Rogers mem
0110   Cur restr schol AAF
0114   MPCF - Cargill Diversity Schol
0120   EDUCATIONAL OPPORTUNITY GRANTS
0122   SUSAN N. GEIGER SCHOLARSHIP
0125   DAVIES SCHOLARSHIP FOR CHILDRE
0141   Spencer Grant -- Galotti
0143   HHS -- Galotti
0148   Monsanto Student Summer Rsrch
0150   3M Scholarship in Sciences
0151   Quentin Voight mem
0153   Carey Memorial -- Music
0154   MCAN Prospects travel fund
0158   Bush Foundation LTC
0162   Wright Arb Fund
0165   Powell/ACM Wilderness Fields S
0167   NSF -- Ferrett
0169   Scoreboard
0171   Bradley Fdn -- C Zuckert
0172   Dreyfus fdn

<None>

char(4)

4

MD_GLAcc_FunctionCode

Note that this is the code corresponding to the text field, MD_GLAcc_Function.

The function and subfunction fields in GL accounts are key for IPEDS reports. Compare the class and subclass, which follow more the natural classification of an expense.

The following is a summary of information on the GL function, provided in the Chart of Accounts Informational Spreadsheet (put together by the Business Office). The single-digit numbers in the left-hand margin are the functions (1, 2, 3, etc.). The indented numbers, together with the function, form the subfunction (e.g., 22, 27, 28). The subfunction is further documented elsewhere:

1-gen admin.      

2-stu services      
   2-offices   
   7-phy ed/athletics   
   8-student activities   

3-ext relations      

4-gen inst      

5-academic      
   1-arts and literature   
   2-humanities   
   3-social sciences   
   4-science and math   
   5-area studies   
   6-   
   7-phy ed/athletics   
   8- Off-campus programs   

6-acad support      
   1-curriculum planning   
   2-   
   3-   
   4-   
   5-faculty development   
   6-library   
   7-support offices   
   8-discretionary   
   9-other

7-professional development      
      
8-acad bldgs,campus, aux ent      
   1-acad/admin bldgs   
   2-campus,grounds,arb   
   3-food service   
   4-dorms   
   6-services (bookstore,CS)   
   7-shop, utilities   
   8-fac/staff houses

9- other      
   6-summer programs   
   7-benefit allocations   
   9-fin aid
      
Dept = 4 digits      
Sub.func = first two digits      
Function = first digit      

<None>

char(1)

1

MD_GLAcc_SubFunctionCode

Code corresponding to MD_GLAcc_SubFunction.

The function and subfunction fields in GL accounts are key for IPEDS reports. Compare the class and subclass, which follow more the natural classification of an expense.

The following is a summary of information on the GL functions and subfunctions, provided in the Chart of Accounts Informational Spreadsheet (put together by the Business Office). The single-digit numbers in the left-hand margin are the functions (1, 2, 3, etc.). The indented numbers, together with the function, form the subfunction (e.g., 22, 27, 28):

1-gen admin.      

2-stu services      
   2-offices   
   7-phy ed/athletics   
   8-student activities   

3-ext relations      

4-gen inst      

5-academic      
   1-arts and literature   
   2-humanities   
   3-social sciences   
   4-science and math   
   5-area studies   
   6-   
   7-phy ed/athletics   
   8- Off-campus programs   

6-acad support      
   1-curriculum planning   
   2-   
   3-   
   4-   
   5-faculty development   
   6-library   
   7-support offices   
   8-discretionary   
   9-other

7-professional development      
      
8-acad bldgs,campus, aux ent      
   1-acad/admin bldgs   
   2-campus,grounds,arb   
   3-food service   
   4-dorms   
   6-services (bookstore,CS)   
   7-shop, utilities   
   8-fac/staff houses

9- other      
   6-summer programs   
   7-benefit allocations   
   9-fin aid
      
Dept = 4 digits      
Sub.func = first two digits      
Function = first digit      

<None>

char(2)

2

MD_GLAcc_DeptCode

Digits 7-10 of GL code; compare Function and SubFunction, which are prefixes of the Department. Not the same as an organizational department. Many organizational departments have multiple GL Departments.

Here are a few sample budget codes, taken from the Business Office Chart of Accounts Inquiry spreadsheet (2010):

0000   General
0001   adj
0013   Year end adj.
0200   Faculty salaries
0300   Acad support sal
0400   Technicians
0500   Secretaries
0600   Athletic coaches
0900   St. Olaf share
1000   General
1100   Gen admin
1110   Trustees
1120   President
1123   Pres Award- Sports Nationals
1124   Pres entertain/ Nutting Hs
1125   Vice President and Treasurer
1126   Endowment Management
1130   Dean-Academic
1142   Business office
1143   Human Resources
1144   Dr-Au Serv
1146   Benefit Admin-external
1149   Treasurer's discretionary
1150   Past President's office
1199   Gen Administrative Benefits

<None>

char(4)

4

MD_GLAcc_ClassCode

Digit 11 of GL code; first digit of Object. Used to distinguish assets, liabilities, income, expenses, etc.

It is one level more general than the subclass.

As opposed to, e.g., the function and subfunction fields (which are used mainly in IPEDS reporting), the class and subclass follow more the natural classification of an expense.

Here is a summary of class codes. The class codes are in the left-hand margin (1-asset, 2-liability, etc.). The indented digits, together with the class digits on the left, form the subclass (e.g., 11-cash, 12-investments, 13-receivables), and are documented elsewhere:

1-asset      
   1-cash   
   2-investments   
   3-receivables   
   4-inventories   
   9-prepaid expense
   
2-liability      
   1-payables   
   2-accrued expense   
   4-deferred(prepaid) income   

3-compensation      

4-income      
   1-tuition and fees   
   2-endow earnings   
   3-gifts   
   4-misc gen inst   
   5-unassigned   
   6-Aux ent-external   
      0-Dorms
      1- Food service
      4,5- Bookstore
      7- Real estate rents
   7-unassigned   
   8-Aux ent-interdept charges   

5-expenses       
   1-student work   
   2-professional services   
   3-supplies   
   4-equipment   
   5-bldg maint   
   6-utilities   
   7-stu organizations   
   8-travel   
   9-other
   
6-fund balance

<None>

char(1)

1

MD_GLAcc_SubClassCode

Digits 11-12 of GL code; first two characters of the Object. Under expenses, used to indicate whether travel, student work, supplies, etc. Also useful in grouping things like salaries together (e.g., subclass 31 = salaries, subclass 32 = benefits).

Often, when looking at the budget in aggregate, we are interested in the class, rather than the subclass, which takes us up to a more general level (grouping all 3xxx budget object codes together, all 4xxxx's, etc.).

For more information on the chart of accounts—what all these codes mean and how they're used, check with the Business Office and see if they'll mail you the latest version of their chart of account spreadsheet.

For budgeting purposes, the key subclass codes are:

52 (object code 5250) – Carryover Funds
Carryover budget is etermined later in June. Carryover was instituted back in 1998 and currently still exists (as of 2012).
53 – Supplies Controls
Printing, office supplies, lab supplies, films, postage, subscriptions, etc.
54 - Equipment
Equipment repair, equipment purchases, equipment rental, equipment service contracts, capital purchases
58 - Travel
Airfares, field trips, travel for professional development, travel expenses for speakers, travel for training
59 – Other
Meetings and entertainment, fees, honoraria, insurance, long distance tolls, services, public relations, room and board, other.

The subclass level is where control accounts are defined, in the sense that a control account number consists of the first twelve digits of the GL account number, with '00' appended. The last two digits (the subclass) classify the nature of the account/expenses. A given '00' control account subsumes a series of sub-accounts with the same first twelve digits, but that end in something other than '00,' like 01, 02, 03, etc. Debits are typically assigned to the sub-accounts, and credits to the control account.

Put differently, control accounts are like other accounts, except that they typically end in '00' (although not all accounts ending in '00' are control accounts, and not all control accounts end in '00'). And, as noted above, they are credited, not debited.

The subclass level is often called the 'control' level.

<None>

char(2)

2

MD_GLAcc_ObjectCode

Digits 11-14 of GL code. For budget lines, like office supplies, salaries, etc. These codes tell us how we're spending money where the rubber hits the road.

See MD_GLAcc_Object for information on the actual names ("descriptions") of the object codes, which are typically useful in reports to non-business/finance people.

The object code (the last four digits of the account number in the GL) is used for classifying GL transactions at a detailed level. It is the lowest level of detail that can be obtained within the class -- subclass -- object hierarchy. A typical example of such a hierarchy would be class 3 -- subclasses 31, 32, 35 (benefits, salary) -- objects 3115 (stipends), 3121 (salaries), 3125 (salaries non-payroll), 3201 (FICA), 3202 (Retirement), etc.

Here is a sample list of just a few object codes, taken from the Business Office Chart of Accounts Inquiry spreadsheet (2010):

3115   Stipends
3121   Salary
3122   Exempt salary
3125   Salaries non-payroll
3200   Benefits Control
3201   FICA taxes
3202   Retirement
3203   Medical
3204   Life ins
3205   Other benefits
3206   vacation pay
3210   Worker's Compensation
3220   Disability Insurance
3230   Medical Insurance
3240   Life Insurance
3250   Retirement Planning
3255   Emeriti Health Plan
3265   Union Pension
3280   Unemployment
3295   Tuition Benefit
4110   Tuition
4111   Tuition Off-Campus

Just to offer another example set, here are codes typically used as expense codes for faculty managing their professional development accounts:

5281 Professional Services (Honorariums, Officials, Trainers, Event Workers, Independent Contractors)
5301 External Printing
5302 Office Supplies
5303 Lab Supplies
5305 Postage
5309 Subscriptions and films (magazines,newspapers, etc.)
5401 Equipment Repair (Specific repair & Maintenance contracts that cover repair)
5402 Equipment Purchases (Contact Facilities prior to purchases over $10,000.00)
5404 Equipment Rental
5405 Equipment Service Contracts
5451 Capital Purchases over $10,000.00
5801 Employee Travel (travel expenses, registration fees, air or ground travel, hotel accommodations, and meals)
5802 Student Travel/Field Trips (CSA, academic programs)
5811 Travel for Professional Development (incl travel expenses, registration fees, air or ground travel, hotel accommodations, and meals)
5814 Travel Expenses for Alumni or Speakers (Includes all expenses related to speaker's travel)
5907 Meetings & Entertainment (department meetings, visitors, donors and related refreshments)
5911 Fees (Membership fees to organization, etc.)
5954 Early Returnees
5997 Faculty Entertaining Student (outside the classroom)
5999 Other/Miscellaneous

<None>

char(4)

4

MD_GLAcc_GLClassification

One of: Revenue, Asset, Liability, Fund Balance, Expense, or Unknown. Defaults to 'Unknown' (which reflects NULL in the underlying Colleague data file).

DW_CodeDescShort

varchar(16)

16

MD_GLAcc_Tag

Currently not populated (although available in Colleague). Every account comes up as 'Not Applicable.'

DW_CodeDescShort

varchar(16)

16

MD_GLAcc_Timestamp

<None>

timestamp

MD_GLACCOUNT_AK

TRUE

FALSE

FALSE

FALSE

TRUE

MD_GLAccount

MD_GLFIELD_SOURCECODE

FALSE

FALSE

FALSE

FALSE

FALSE

MD_GLAccount

AltKey