PBC Item # 2 Comparative Trial Balance of Campus Ledger/Legal Ledger/GAAP Ledger

PBC Schedules127, 133b and 138 – Transaction Details for Student Tuition & Fees,

Sales and Services of Auxiliary Enterprises and Scholarships and Fellowships

Details by Transaction

Purpose: This document provide instructions on how to generate a report showing transactions details for certain GAAP accounts (listed below). This will be used to satisfy PBC items 127, 133b, and 138.

PBC 127aTuition and Fees Details by Transaction (721001-Student Tuition and Fees, net of scholarship allowances)

PBC 133bSales and Services Details by Transaction (721006-Sales and services of auxiliary enterprises, net of scholarship allowance)

PBC 138Scholarship and Fellowship Details by Transaction (722003-Scholarshipsand Fellowships)

The screenshots below describe the KPMG approveddata downloaded from Data Warehouse for PBC schedules127, 133b and 138.

Note: Steps below are to be used to download data associated with GAAP account 721001, 721006 and 722003 one by one for preparing aforementioned PBC items respectively.

Step Description / Screenshot/Description of Dashboard Page

Create an Excel file and name the file using the naming convention below

PBC# Two-letter Abbreviation for Agency Name PBC Name Fiscal Year
Examples:
PBC127aXX Tuition and Fees Details by Transaction 15-16
PBC133b XX Sales and Services of Aux Ent Details by Transaction 15-16
PBC138 XX Scholarship and Fellowship Details by Transaction 15-16
In the Excel file, create three tabs and name them to indicate the following accordingly:
A1 / Summary / Sample PBC 127a

A2 / GAAP 998 Data
A3 / Legal Data
Run GAAP Summary / Dashboard: FIRMS GAAP
Dashboard Tab: GAAP Summary
B1 / Open Dashboard
Menu location: Upper right corner
Navigation: Dashboards > FIRMS GAAP
*If it was recently accessed, open the dashboard from the main Home page. /
B2 / Set Dashboard Filters
Report Filters

Select GAAP Business Unit
Select FIRMS Business Unit
Select Fiscal year
Select PeriodFrom 1 -Period To 998
Select GAAP Object Code
Click on “Apply Filters” button to apply the filters

B3 / Set GAAP Summary Report Filters
Note: The filter setting below is on the assumption that the report result returned is based on the default setting. Filter setting may vary depending on how report format is customized.

Column 1: Fiscal Year
Column 2: Acct Fdescr
Column 3: Acct Fdescr
Column 4 to 6: Hide (default setting)
Click on “OK” button to apply the filter.
Once the result returned, select Report ViewsBy Period

B4 / Format GAAP Summary Report Result

Once report by period returned, right click on the yellow columns heading to access the columns menu.
Choose Show Subtotal None to remove subtotal for the yellow columns. Apply the setting for the three yellow columns one by one.

To remove the duplicate Acct Fdescr column, set Column 3 as “Hide”.
Click on “OK” button to apply the filter.

B5 / ExportReport to Excel
Navigation: Export > ExcelExcel 2007+

Once the Excel file is downloaded, copy the report and paste it to the summary tab (reference to A1) of the Excel file created in Step A.

Run GAAP Actuals -998 Data / Dashboard: FIRMS GAAP
Dashboard Tab: GAAP Actuals
C1 / Set Dashboard Filters

Select GAAP Actuals tab

Report Filters

Select GAAP Business Unit
Select FIRMS Business Unit
Select Fiscal Year
Select Period 998
Select GAAP Object Code
Click on “Apply Filters” button to run the data

C2 / Download data to Excel
Menu location: page footer
Navigation: Export > Data > CSV Format

Once the .csv file is downloaded, copy the data and paste it to the GAAP Datatab (reference to A2) of the Excel file created in Step A.

/
Run Legal Actuals / Dashboard: Transaction Inquiry
Dashboard Tab: Actuals Reports
D1 / Open Dashboard
Menu location: Upper right corner
Navigation: Dashboards > Transaction Inquiry
*If it was recently accessed, open the dashboard from the main Home page. /
D2 / Choose Actuals Report

Click on either “Actuals Report” Tab on the page header or the link (with the same name as the tab) in the menu list
ChooseReport Index > Actuals Transactions

/
D3 / Set Dashboard Filters
Report Filters

Select Business Unit (CMP)
Select Fiscal Year
Select PeriodBetween 1 to 12

Advanced Filters

Select GAAP Nat Class

Click on “Apply Filters” button to run the data

/
D4 / Download Data to Excel
Menu location: page footer
Navigation: Export > Data > CSV Format

Once the .csv file is downloaded, copy the data and paste it to the Legal Data tab (reference to A3) of the Excel file created in Step A.

/
D5 / Required Parameters
The transaction data downloaded from Data Warehouse contains 54 data fields.
For San Diego State University, the ones marked with red asterisk (*) are minimum data fields required for submitting the three PBC schedules.
Note: Doc or Document referred in some of the field names includes the following:

Billing Accounting Lines
Accounting Lines
Manual Journal Entry
Student Financial Journals
AP Voucher Accounting Lines

/ No. / Data Field Name / No. / Data Field Name / No. / Data Field Name
1 / Row Count / 19 / Purchase Order / 37 / SCO Subfund Fdescr
2 / Page Number / 20* / Supplier ID
(associated w/voucher) / 38 / Jrnl Ln #
3* / Business Unit(Legal) / 21* / Supplier Name(associated w/voucher) / 39 / Jrnl Class
4* / Fiscal Year / 22* / Invoice ID / 40 / Jrnl Class Descr
5* / Period / 23 / Jrnl ID / 41 / Jrnl Ln Ref
6 / Accounting Date / 24 / Jrnl Descr / 42 / Jrnl Rev Cd
7* / Doc ID / 25 / CSU Descr / 43 / Jrnl Template
8 / Doc Src Fdescr / 26 / CSU Ref 1 / 44 / User ID
9* / Doc Ln Descr / 27 / CSU Ref 2 / 45 / Doc Ln #
10* / Amount / 28 / Ledger Fdescr / 46 / Doc Dst Ln #
11* / Account Fdescr(PSoft Account) / 29* / Document Date / 47 / Acct Entry Type
12* / Fund Fdescr(PSoft Fund) / 30 / Jrnl Src / 48* / Customer ID
(associated w/billing)
13* / Dept Fdescr / 31 / Posted Date / 49* / Customer Name(associated w/billing)
14 / Prog Fdescr / 32 / Journal Date / 50 / Open Item Key
15 / Class Fdescr / 33 / SCO Fund Fdescr / 51 / Reversal Date
16 / Project Fdescr / 34* / CSU Fund Fdescr / 52 / PO Dst
17 / Stat Cd / 35* / FIRMS Obj Cd Fdescr / 53 / PO Ln
18 / Stat Amt / 36 / FIRMS Proj Cd Fdescr / 54 / PO Sch
Agree the Legal datato the total of Period 1 to 12 data and GAAP 998 data to Period 998 of the GAAP Summary Report

Upload Excel to PBC SharePoint site.

Page 1 of 10