The Idiot Guide
to
SAS as XL spreadsheet
within
DIRECT LENDING
Sean McNally
October 2015
Table of Contents
Contents are interactive. Scroll down to find what you want. Hold down the Ctrl key and click on the contents line. It will automatically take you straight there.
A.1 Reconcile the SAS 2
A.1.1 Setting up COD for the right version of SAS 3
A.1.1.1 Install EdConnect – if not already installed 5
A.1.2 Getting the SAS from EdConnect 6
A.1.3 Import the SAS in XL 11
This guide an extract from Sean’s Idiot Guide which is available at
www.sean-usloans.co.uk
A.1 Reconcile the SAS
The SAS is the School Account Statement.
¨ Lists individual totals disbursed per loan, per student, from COD
¨ shows each loan as gross, fee deducted, net disbursed
¨ Summarizes all cash movements
It needs to be reconciled to the school loan records which is probably your finance (fees) system to ensure that what the USA has lent to each student is what the school has actually given the student.
USA thinks you should do this monthly, by reality is that it should at least at year-end and after each round of disbursements.
The SAS is sent monthly to Ed Connect
¨ You have to set up COD for the correct version
¨ you have to download it from Ed Connect
¨ Your have to import the several sections into DL Tools
¨ print the SAS from DL Tools to be able to read it.
¨ reconcile SAS to your finance system
There are two systems to be used, both are published by USDE and need to be installed on your PC
¨ If you don’t have ‘administrator’ rights on your PC, and you probably don’t, then you need your IT support to do this installation for you.
¨ For Ed Connect, go to https://www.fsadownload.ed.gov/softedconnect.htm
¨ For DL Tools go to https://www.fsadownload.ed.gov/software.htm
¨ Download programme and install manual for both Ed Connect and DL Tools
§ Don’t do the set-up at the same time
¨ Have your IT support people do the install, then you can do the set-up. Only Ed Connect needs set-up.
§ You need a pad of paper and pen
§ Preplan 2 passwords, one for SYS and one for yourself, each needs to be 8 characters long, mixed letters/numbers and mixed upper/lower case
§ If you feel you can handle the manual – fine, but if not, don’t think twice, simply phone the support helpline – they are VERY helpful and will both talk you through and do some of the work for you at their end and they check that what you’re doing is coming through at their end. And they are VERY patient.
§ Helpline is 001 800-330-5947
Once you are set-up, here’s what they do
¨ Ed Connect receives transmissions from USDE to the school mailbox including the monthly SAS from COD and the Audit Report from NSLDS
¨ Ed Tools, into which the SAS received from Ed Connect needs to be imported in order to be able to read it properly
A.1.1 Setting up COD for the right version of SAS
The SAS is generated in COD monthly. Foreign schools really need the full version every time and not the summary version. Follow these instructions.
And here is the rest of the screen
If your SAS is not a full listing [but only summary cash listing] and you know that you got these settings right, then you need to get it changed to the full listing format. Phone the helpline and have them take you through it.
DECISIONS …
¨ If you set the file to comma delimited (CSV) you can only use XL to open/use it
¨ If you set the file to fixed length you can only use DL Tools to open/use it
You cannot mix and match between the two systems – here’s how it should finally look
A.1.1.1 Install EdConnect – if not already installed
you will need help
- get a computer support person with "administrator" rights to systems
2. collect your fsa login, password and red token
3. go to https://www.fsadownload.ed.gov/softedconnect.htm
4. and get the support person to instal it
- then support person to phone 001-800-330-5947 and between you all, get you properly set up
- apart from the login, everything else works the same as before
FIRST you need the following
¨ TG number if you don’t know what this is, then you don’t have one – you need to get a SAIG mailbox and ID from the US Ed Dept.
¨ SAIG login and password [the login will be your name followed by .fsa
¨ Little red token which you have already been using
SECOND Do not try to do this by yourself.
You need someone from IT who has administrator rights.
When this person is sitting beside you, you can start.
NOW YOU CAN START
Go to https://www.fsadownload.ed.gov/softedconnect.htm
Download and print the user guide, print in Black & White; then the programme
If you didn’t print the user guide first, then you won’t be able to look up a problem during the installation process.
Once you click on the programme you get this dialogue. Click on the “Save File” button
.
Look for this icon on your desktop and double click on it
Click on run
When this warning appears, ignore it.
Click on OK
When install complete, then support person to phone 001-800-330-5947 and between you all, get you properly set up.
A.1.2 Getting the SAS from EdConnect
Open Ed Connect.
Click on OK
Then normal log-in.
The login below will change as Ed Connect comes to accept the ‘token’ as authentication.
This will take you into the Transmission Queue.
You need to ‘transmit’ – that is synchronise your inbox on your PC with the inbox held at USDE.
Click on the transmission button it looks like arrows going up and down
The transmission will then start and you can watch its progress.
It’s quite quick.
Then click into the mailbox query. This shows everything which is now in your mailbox and available to you
You are looking for SAS things. There will be more than one.
Tick them in the boxes on the left (the left boxes do not show in this screenshot)
Then re-transmit (use the up-down arrows), this collects what you have ticked.
When finished, go to the Activity Log. This show what you requested and beside each request is what you received.
Here’s the SAS things. Yes, there are several of them, but they are different.
VERY IMPORTANT - Make a note of their names and their folder,
If you import into DL-Tools, then the file will disappear from the folder it is in now
GOOD ADVICE - copy the files to somewhere else, and back them up, then you can use them safely without touching the backup versions which will still be safe should anything else go wrong.
YOU’RE DOOMED - if use the originals without making backups, you will have to wait a month before getting the next versions
Leave Ed Connect and go to either XL or open DL Tools and follow the appropriate instructions in the following sections
A.1.3 Import the SAS in XL
Using XL either “open” or “import” the file from the safe folder in which you copied it.
XL will ask if, or assume that, the file is comma delimited (CSV file) and when opened, it will come in sections.
The top section is of little use but looks like this
The next section is more useful as it shows all cash movements
which list the cash transactions visible in an unhelpful format is COD.
Also shows each student loan totals
The final section lists all the students with the total value disbursed to date (net and gross) for each loan type
You can see that the students are not in any useful order. But the
But you can use the award identifier field and split it
¨ First nine characters are the SSN
¨ Tenth character is the loan type (S, U, P)
¨ Gross and net values also show
So a simple tidy up should produce a useful list like this
To ensure the list is complete, sum the gross and net fields which should agree to the summary values in the middle section of the report.
You could use then subtotal the student section which should give you something which is easy to reconcile.
Your aim is only to ensure that the combined total of all loans received by the student is the same as the combined total of all loans disbursed to the student in COD.
11