Attachment B - FinancialStatementandTBSourceDataExample.xlsx

XLSX spreadsheet 67 KB Posted Jan 24, 2020

Attached to: FINANCIAL REPORTING AND BUSINESS INTELLIGENCE TOOL WITH INTEGRATED GOVERNANCE, RISK MANAGEMENT, AND COMPLIANCE CAPABILITIES
Solicitation number: FCSIC2020-01
Issued by: Farm Credit Administration

View the file

Other files

File	Type	Posted
Attachment A - InvestmentDetailSourceDataExample.xlsx	XLSX spreadsheet	Jan 24, 2020
Amended SOW GRC and BI Solicitation Jan 2020 with Track Changes .pdf	PDF	Jan 24, 2020
Attachment C - BankDataSourceDataExample.pdf	PDF	Jan 24, 2020
Requirements Response Sheet.xlsx	XLSX spreadsheet	Jan 24, 2020
Q and A from vendors - 1-24-2020.pdf	PDF	Jan 24, 2020
FCSIC SOW GRC and BI Solicitation - Jan 2020.pdf	PDF	Jan 6, 2020
FCSIC SOW - Appendix A - Day 1 Reports - Jan 2020.pdf	PDF	Jan 6, 2020

On GovTribe

Work with this file

Download the original file
Contacts named in this file
Similar government files
Ask GovTribe AI about this file

Try for free Login

Text version

The file's text, extracted by GovTribe without its formatting.

Trial Balance

CALENDAR YEAR TRIAL BALANCE		This spreadsheet shows the general trial balance and financials statements. Any amounts in this spreadsheet are examples and for illustrative purposes only; this amounts are not accurate.
2019-03
RECLASSIFIED AFTER PREMIUM ADJUSTMENT
Ussgl	Ussgl Description	Beginning Balance	CY Activity	CY Ending Balance	Manual Adjustments	Adjusted Ending Balance
10100001	FUND BALANCE WITH TREASURY	102,312.81	- 0	102,312.81		102,312.81
10100021	SF 224 DISBURSEMENTS
10100022	SF224 COLLECTIONS
10100024	NON SF 224 TRANSACTIONS
10100025	PAYROLL-NON 224
10100027	NON SF224 IPAC TRANSACTIONS
10100028	NON SF224 COLLECTIONS
10100029	NON SF224 DISBURSEMENTS
13100001	ACCOUNTS RECEIVABLE
1310	OTHER ACCOUNTS RECEIVABLE
13100020	ACCOUNTS RECEIVABLE - EMPLOYEE INDEBTEDNESS
13400001	INTEREST RECEIVABLE
13420001	INTEREST RECEIVABLE - INVESTMENTS
14100050	TRAVEL ADVANCES - TIER SUBACCOUNT
16100001	INVESTMENTS IN U.S. TREASURY SECURITIES ISSUED BY THE BUREAU OF THE PUBLIC DEBT
16110001	DISCOUNT ON U.S. TREASURY SECURITIES ISSUED BY THE BUREAU OF THE PUBLIC DEBT
16120001	PREMIUM ON U.S. TREASURY SECURITIES ISSUED BY THE BUREAU OF THE PUBLIC DEBT
16130001	AMORTIZATION OF DISCOUNT AND PREMIUM ON U.S. TREASURY SECURITIES ISSUED BY THE BUREAU OF THE PUBLIC DEBT
17500001	GENERAL PROPERTY, EQUIPMENT, AND SOFTWARE, NET
17590001	ACCUMULATED DEPRECIATION ON EQUIPMENT
21100001	ACCOUNTS PAYABLE
21100002	ACCOUNTS PAYABLE - RECEIPT ACCRUALS
21900001	OTHER LIABILITIES WITH RELATED BUDGETARY OBLIGATIONS
22100001	ACCRUED FUNDED PAYROLL
22100002	ACCRUED FUNDED LEAVE
22130001	EMPLOYER CONTRIBUTIONS ON PAYROLL TAXES PAYABLE
31000001	UNEXPENDED APPROPRIATIONS-CUMULATIVE
33100001	CUMULATIVE RESULTS OF OPERATIONS
40600001	ANTICIPATED COLLECTIONS FROM NON-FEDERAL SOURCES
40700001	ANTICIPATED COLLECTIONS FROM FEDERAL SOURCES
42010001	TOTAL ACTUAL RESOURCES COLLECTED
42010099	PRIOR YEAR DELIVERED ORDERS - OBLIGATIONS-PAID
42510001	REIMBURSEMENTS AND OTHER INCOME EARNED-RECEIVABLE
42520001	REIMBURSEMENTS AND OTHER INCOME EARNED-COLLECTED
42660001	OTHER ACTUAL BUSINESS-TYPE COLLECTIONS-NON FEDERAL SOURCES
42730001	INTEREST COLLECTED FROM TREASURY
42830001	INTEREST RECEIVABLE FROM TREASURY
43820001	TEMPORARY REDUCTION - NEW BUDGET AUTHORITY
43840001	TEMPORARY REDUCTION/CANCELLATION RETURNED BY APPROPRIATION
44500001	UNAPPORTIONED AUTHORITY
46200001	UNOBLIGATED FUNDS EXEMPT FROM APPORTIONMENT
46200004	UNOBLIGATED FUNDS - SPENDING
46200005	UNOBLIGATED FUNDS - PAID EXPENDITURES
48010001	UNDELIVERED ORDERS - OBLIGATIONS-UNPAID
48010099	PRIOR YEAR UNDELIVERED ORDERS - OBLIGATIONS-UNPAID
48020001	UNDELIVERED ORDERS - OBLIGATIONS, PREPAID/ADVANCED
48710001	DOWNWARD ADJUSTMENTS OF PRIOR-YEAR UNPAID UNDELIVERED ORDERS - OBLIGATIONS,RECOVERIES
48810001	UPWARD ADJUSTMENTS OF PRIOR-YEAR UNDELIVERED ORDERS - OBLIGATIONS,UNPAID
49010001	DELIVERED ORDERS - OBLIGATIONS-UNPAID
49010099	PRIOR YEAR DELIVERED ORDERS - OBLIGATIONS-UNPAID
49020001	DELIVERED ORDERS - OBLIGATIONS-PAID
49710001	DOWNWARD ADJUSTMENTS OF PRIOR-YEAR UNPAID DELIVERED ORDERS - OBLIGATIONS, RECOVERIES
49720001	DOWNWARD ADJUSTMENTS OF PRIOR-YEAR PAID DELIVERED ORDERS - OBLIGATIONS, REFUNDS COLLECTED
49810001	UPWARD ADJUSTMENTS OF PRIOR-YEAR DELIVERED ORDERS - OBLIGATIONS, UNPAID
49820001	UPWARD ADJUSTMENTS OF PRIOR-YEAR DELIVERED ORDERS - OBLIGATIONS, PAID
52000001	REVENUE FROM SERVICES PROVIDED
53110001	INTEREST REVENUE - INVESTMENTS - EXCHANGE
55000001	INSURANCE AND GUARANTEE PREMIUM REVENUE
61000001	OPERATING EXPENSES/PROGRAM COSTS
61000008	FUNDED LEAVE EXPENSE
63300001	OTHER INTEREST EXPENSES
64000001	BENEFIT EXPENSE
67100001	DEPRECIATION, AMORTIZATION, AND DEPLETION
67900001	OTHER EXPENSES NOT REQUIRING BUDGETARY RESOURCES
71110001	GAINS ON DISPOSITION OF INVESTMENTS - EXCHANGE
72110001	LOSSES ON DISPOSITION OF ASSETS - OTHER - EXCHANGE
75000001	DISTRIBUTION OF INCOME-DIVIDEND - EXCHANGE
88010001	OFFSET FOR PURCHASES OF ASSETS
88020001	PURCHASES OF PROPERTY, PLANT, AND EQUIPMENT
88040001	PURCHASES OF ASSETS - OTHER
99000001	INTERNAL MANAGEMENT PLAN-DEBIT
99000002	INTERNAL MANAGEMENT PLAN-CREDIT
		102,312.81	- 0	102,312.81		102,312.81
Investments Bought on Final Business Day of Current Month			100.00
Investments Bought on Final Business Day of Prior Month			100.00

Securities Matured During the Month	100.00
Securities Sold During the Month	0.00

	Par	Initial Discount	Initial Premium	Inflation Compensation	Total
Securities Purchased (Reinvested) During the Month	0.00	0.00	0.00	125.00	125.00

LIABILITIES BY VENDOR TYPE

USSGL	VENDOR TYPE CODE	BALANCE	ADJUSTMENTS	ADJUSTED BALANCE
21100001	N	100.00	(100.00)	- 0
22100001	N	100.00	59,233.01	59,333.01
22100002	N	100.00	(700.00)	(600.00)
22130001	F	100.00	1,400.00	1,500.00
22130001	N	100.00	46.00	146.00
21100002	N	100.00	(63,000.00)	(62,900.00)
22100001	F	- 0		- 0
21100001	F	- 0		- 0
21100002	F	100.00	30,150.33	30,250.33
21900001	N	- 0		- 0
				- 0	CHECK FIGURE
				27,729.34	- 0
					27,729.34

USSGL 53110001 BY CAM1

53110001	FCS110010000	100.00
53110001	FCS110020000	100.00
53110001	FCS190010000	100.00
53110001	FCS190020000	100.00	CHECK FIGURE
		400.00	- 0
			(400.00)

USSGL 55000001 BY CAM1

55000001	XXXXXXXXXXXX	100.00
			CHECK FIGURE
		100.00	- 0
			(100.00)

EXPENSES BY VENDOR TYPE

61000001	F	100.00
61000001	N	10.00
61000008	N	100.00
63300001	N	100.00
64000001	F	100.00
67900001	N	- 0
67100001	N	100.00
64000001	N	0.00
61000008	F	0.00	CHECK FIGURE
		510.00	- 0
			(510.00)

Financial Condition

Company Name

Comparative Statement of Financial Condition

December 31, 2018

CROSSWALK

RECLASSIFIED

		F=Fed
	G/L	N=Non-Fed
	Acct Number	I=Govt	Amount	Total
Assets

Cash & Cash Equivalents	1010	F	102,312.81
	1021	F	0.00
	1022	F	0.00
	1024	F	0.00
	1025	F	0.00
	1027	F	0.00
	1028	F	0.00
	1029	F	0.00
	1610

mjackson: mjackson:

One-day investments F 100.00 102,412.81

Investments in U.S. Treasury 1610 mjackson: mjackson:

Investment holdings other than one-day	F	(100.00)
		1611	F	0.00
		1612	F	0.00
		1613	F	0.00	(100.00)

Receivables
Accrued Interest Receivable	1342	F	0.00	0.00

Premiums Receivable 1310 N 0.00 0.00

Other Receivables	1310	N	0.00
	1310	F	0.00	0.00

Prepaid Expenses 1410 N 0.00 0.00

General Property, Equipment, and Software, Net 1750/1759 F 0.00 0.00

Total Assets	calc	102,312.81
		===========

Liabilities and Insurance Fund

Accounts Payable	2110	F	30,250.33
and Accrued Expenses	2110	N	(62,900.00)
	2190	F	0.00
	2190	N	0.00
	2210	F	0.00
	2210	N	58,733.01
	2213	F	1,500.00
	2213	N	146.00
	2220	N	0.00	27,729.34

Liability for Estimated	2990	N	0.00	0.00
Insurance Obligation

Farm Credit Insurance Fund

3100	0.00
3310	0.00
5200	0.00
5311	0.00
5500	0.00
6100	0.00
6330	0.00
6400	0.00
6730	0.00
6710	0.00
6790	0.00
6800	0.00
7111	0.00
7211	0.00
7500	0.00	0.00

Total Liabilities and Insurance Fund	calc	27,729.34
		===========
IN BALANCE CHECK		130,042.15

Monthly Revenue and Expense

Company Name - Example Only

Monthly Statement of Revenue and Expense

December 31, 2018

CROSSWALK

RECLASSIFIED

		F=Fed
	G/L	N=Non-Fed	Month Ended	Calendar Year	NOV/19-18
	Acct Number	I=Govt	December 31, 2018	to Date
Income

Premiums 5500 N (100.00) 400.00 500.00

Insurance Premium - AIRAS Distribution 5500 N 0.00 0.00 0.00

Interest Income, Investments 5311 F (100.00) 400.00 500.00

TIPS Daily Inflation Compensation, Investments 5311 F (100.00) 400.00 500.00

Amortized (Premiums) / Discounts 5311 F (200.00) 200.00 400.00

Other Income	5200	F	0.00	0.00	0.00
	5200	N	0.00	0.00	0.00

Gain/Loss on the Disposition of Investments 7111/7211 F 0.00 0.00 0.00

Total Income	calc	(500.00)	1,400.00	26,500.00
		================	================	================

Expenses:

Administrative Operating Expenses	6100	F	100.00	600.00	500.00
	6100	N	110.00	610.00	500.00
	6330	N	100.00	100.00	0.00
	6400	F	100.00	500.00	400.00
	6400	N	0.00	0.00	0.00
	6800	N	0.00	0.00	0.00
Depreciation	6710	N	0.00	16,805.14	16805.14
Provision for Estimated Obligation	6790	N	0.00	0.00	0.00

Total Expenses	calc	410.00	18,615.14	400.00
		================	================	================

NET INCOME	(910.00)	(17,215.14)	400.00
	================	================	================

Comparative Revenue and Expense

Company Name - Example Only

Comparative Statement of Revenue and Expense

For the Period Ended December 31, 2018 and December 31, 2017

CROSSWALK

RECLASSIFIED

		F=Fed
	G/L	N=Non-Fed	Month Ended	Calendar Year to Date	Calendar Year to Date	NOV/19-18
	Acct Number	I=Govt	For the Period Ended December 31, 2018 and December 31, 2017
December 31, 2018	December 31, 2017
Income

Premiums 5500 N (100.00) 0.00 340,762,000.00 100.00

Insurance Premium - AIRAS Distribution 5500 N 0.00 0.00 0.00 0.00

Interest Income, Investments 5311 F (100.00) 0.00 66,524,312.59 100.00

TIPS Daily Inflation Compensation, Investments 5311 F (100.00) 0.00 3,044,548.75 100.00

Amortized (Premiums) / Discounts 5311 F (200.00) 3,900.00 (11,848,650.00) 4,100.00

Other Income	5200	F	0.00	0.00	0.00	0.00
	5200	N	0.00	0.00	0.00	0.00

Gain/Loss on the Disposition of Investments 7111/7211 F 0.00 0.00 0.00 0.00

Total Income	calc	(500.00)	3,900.00	398,482,211.34	500.00
		================	================	================	================

Expenses:

Administrative Operating Expenses	6100	F	100.00	700.00	680,308.25	600.00
	6100	N	110.00	710.00	2,493,241.66	600.00
	6330	N	100.00	100.00	0.00	0.00
	6400	F	100.00	600.00	453,812.13	500.00
	6400	N	0.00	0.00	26,591.54	0.00
	6800	N	0.00	0.00	0.00	0.00

Provision for Estimated Obligation 6790 N 0.00 0.00 0.00 0.00

Depreciation	6710	N	0.00	50.00	13,749.66	50.00
Total Expenses	calc		410.00	2,160.00	3,667,703.24	4,500.00
			================	================	================	================

NET INCOME	(910.00)	1,740.00	394,814,508.10	35,500.00
	================	================	================	================

Insurance Fund

Company Name - Example Only

Statement of Changes in Insurance Fund

For the Month Ended December 31, 2018

CROSSWALK

RECLASSIFIED

	Month Ended	Accumulative YTD
	December 31, 2018	December 31, 2018
Balance Beginning of Period	4,929,154,414.56	4,847,718,161.00	4,847,718,161.00	Only change in January to December 31, 2016 ending balance

Payment to
AIRAs Accountholders	0.00	(175,831,635.00)	(175,831,635.00)

Net Income (910.00) 257,266,978.56 257,267,888.56

Balance End of Period 4,929,153,504.56 4,929,153,504.56 4,929,154,414.56 Prior month ending balance

IN BALANCE CHECK	4,929,153,504.56
			Month	YTD
		January	22,776,458.70	22,776,458.70
		February	22,714,929.80	45,491,388.50
		March	23,875,761.94	69,367,150.44
		April	22,472,868.81	91,840,019.25
		May	23,589,062.48	115,429,081.73
		June	23,568,020.61	138,997,102.34
		July	23,223,259.48	162,220,361.82
		August	23,961,793.71	186,182,155.53
		September	22,450,022.33	208,632,177.86
		October	24,248,701.34	232,880,879.20
		November	24,387,009.36	257,267,888.56
		December		257,267,888.56
			257,267,888.56

Note: Data gathered from previous month's statements.

File details come from the government source that posted it. Updated Jan 25, 2020.