Downloading Cashflow Report #1 for Excel



Using the Cash Flow Excel Template

Reports needed for preparation of Cash Flow:

1. SACS Cash Flow Report (Comma Delimited) for current year months that have actuals. (see attachment #1 for creating this file)

2. SACS Cash Flow Report (Regular) for current year months that have actuals. (see attachment #1)

3. SACS Cash Flow Report (Comma Delimited) for prior year information for projection. (see attachment #2 for creating this file)

4. SACS Cash Flow Report (Regular) for prior year information. (see attachment #1)

5. Copy of prior year unaudited actuals, page 3, Fund 01, SACS format.

6. Copy of your budget. If you are preparing a cash flow for 1st or 2nd interim, you will need column D budget figures. If you are preparing this for any other time you will need your current budget figures.

7. Budget report writer for current year ending 10/31/CY or 1/31/CY (depending on which interim report you are preparing) for objects 8010 – 8099.

After you have the above reports, you are ready to open the Cash Flow workbook. The workbook contains seven worksheets:

1. Projected Cash Flow (protected)

2. Current Year Download

3. Current Year Detail (protected)

4. Prior Year Download

5. Prior Year Detail (protected)

6. Projected Totals (entry areas unprotected)

7. Ratio Calculation (protected)

This workbook is designed for user input into the spreadsheets “Current Year Download”, “Prior Year Download” and entry areas of “Projected Totals”. The workbook is a modified version of the cash flow spreadsheet presented at last year’s CASBO conference. A simple protection is used so you may easily remove it should you need to tailor an area to your special needs. It will use “actual” information for the months that are known and will project based upon last year’s data for months in the future. The file has been saved in the version Microsoft Excel 5.0/95 Workbook in the hope that it will accommodate most of our districts.

Instructions

1. Open Excel.

2. Open the Cash flow workbook.

3. Click on the worksheet “Projected Totals”.

4. Input your 7/1/CY beginning cash balance from the printed current year cash flow report into the Total Projected Budget Column.

5. Using your budget input the various receipts and disbursement category amounts as indicated. Use the budget report writer for objects 8010 – 8099 to breakdown the individual revenue limit sources.

6. If you are a district with a TRANS, obtain the amount of money deposited into the county treasury for your TRANS. Input that number in BOTH the receipts AND disbursements TRANS lines. Your projected ending cash for the year will be reflected on line E of the projected total spreadsheet. IF YOU ARE PART OF THE PIPER JAFFRAY TRANS AND YOU DO NOT BOOK ENTRIES IN OBJECTS 9135 AND 9640 FOR THE TRANSACTION, SKIP THIS INSTRUCTION.

7. Use the receivable amount plus any “due from” amount from your prior year Unaudited Actuals Fund 01 for the amount to be placed in the receivables cell in the receipts section. Add the liability amounts and deferred revenue together and place in the disbursement section.

8. At the top of your screen click on File and choose Open. Find the “Current Year Download” file and double click to open it. (You will need to click on the dialog box “files of Type” and change the setting to “all files.”

9. The Text Import Wizard will appear. There will be three screens to complete. On screen one click on “Delimited” then “Next” at the bottom of the box. On screen two make sure “Comma” is the only selection made and click “Next”. On screen three click on “Finish”.

10. A new spreadsheet will open containing the downloaded data. Place your cursor in cell A5. Hold the left mouse button down and highlight across until you run out of data to highlight, then pull your cursor down until all of the information is highlighted. Release.

11. Click “Edit” at the top of your screen and select “Copy”.

12. Click on “Window” at the top of your screen and move back to the Cash Flow template.

13. Go to the “Current Year Download” worksheet.

14. Place your cursor in cell A2.

15. Click “Edit” at the top of the screen and select “Paste”. The data should flow into the sheet. Open the worksheet “Current Year Detail. You will see that the known actual data has flowed into the appropriate categories. Verify ending balance totals from your cash flow report to the ending balances displayed on “Current Year Detail”. They can be off due to rounding. We will now transfer the prior year data so the worksheet can create percentages to project future months.

16. At the top of your screen click on “File” and choose “Open”. Find the “Prior Year Download” file and click to open it. You may have to change the dialog box to reflect all files.

17. The Text Import Wizard will appear. There will be three screens to complete. On screen one click on “Delimited” then “Next” at the bottom of the box. On screen two make sure “Comma” is the only selection made and click “Next”. On screen three click on “Finish”.

18. A new spreadsheet will open containing the downloaded data. IF YOU HAD OUT OF RANGE REVENUE OR DISBURSEMENTS, DELETE THOSE LINES FROM THIS SPREADSHEET NOW. Place your cursor in cell A5. Hold the left mouse button down and highlight across until you run out of data to highlight, then pull your cursor down until all of the information is highlighted. Release.

19. Click “Edit” at the top of your screen and select “Copy”.

20. Click on “Window” at the top of your screen and move back to the Cash Flow template.

21. Go to the “Prior Year Download”.

22. Place your cursor in cell A2.

23. Click “Edit” at the top of the screen and select “Paste”. The data should flow into the sheet.

24. This step is very important. The worksheet, “Prior Year Download” must contain data ONLY for the months that you wish to project. If you are preparing the first interim report, which ends on 10/31/CY, you need to project from 11/01/PY through 06/30/PY. If you are preparing the second interim report, which ends, on 01/31/CY you need to project 02/01/PY through 06/30/PY. You must erase the data for the months that do NOT need to be projected. If you do not, the spreadsheet will not calculate correctly. For first interim this would be July through October. For second interim this would be July through January. Highlight the unneeded data. Click “Edit”, “Clear”, and then “Contents”.

25. Open the worksheet “Prior Year Detail”. Data should have flowed into the sheet. You will see that the transferred data has flowed into the appropriate categories. This data will be used to calculate percentages for future month. Place your cursor in cell C9. Type in the beginning balance of the first month that shows data. That will enable you to check ending balances from this sheet against your cash flow report. If you had out of range revenue or disbursements, these balances will not match and of course rounding is a consideration.

26. Click on the worksheet “Projected Cash Flow”. Choose first or second interim by putting an X in the appropriate box. Type in your district name by the district designation.

27. Print the worksheet “Projected Cash Flow”. The following should be verified.

a. Beginning Balance

b. Totals of each budget category

c. TRANS receipts and disbursements balancing to zero.

d. Accuracy of projected ending balance. Does it match E on the worksheet “Projected Totals”?

e. Monthly ending balances for months that have actuals (for first interim months July through October balances should match. For second interim, months July through January should match.

Monthly ending amounts could be off due to rounding by two to nine dollars. I would not invest my time in looking for an amount less that 25 dollars. This workbook can be used for first and second Interim or at anytime you need to project cash flow.

Attachment #1

Downloading Current Year Cash flow Report #1 for Excel

1. Logon to Reflections

2. Choose #2, Budget

3. Choose #5, SACS Financial Reporting System

4. Choose #6 SACS Cash Flow Analysis Report

5. Define date from 07/01/CY to 10/31/CY (or 07/01/CY to 01/31/CY for 2nd Interim). (See Example #1). Be sure that this office has reconciled cash so you are working with numbers that will not change.

6. Choose Report Template #1.

7. Indicate Y on the line that asks about comma delimited files. Press enter and continue.

8. On the next screen indicate Fund 01 and launch the report. Press enter and F7 to launch.

9. When you go into your print screen, you will see that it has created two reports. One is a regular cash flow report that you can print. The second is the comma-delimited file to be downloaded to the Excel spreadsheet.

10. Print the regular cash flow report and be sure you do not have anything “Out of Range.” If so, stop and make corrections and start back at step one.

11. If your regular cash flow does not reflect any items “Out of Range”, at the print screen place you cursor on the comma delimited file and type L and press enter.

12. Tab down to the question, “Delimiter?” Indicate C for comma. Press enter. Then press F3 to Build the DL File. Then F7 to download the DL File.

13. The next screen is asking where you would like to place the file. I would suggest creating a file folder called cash flow on your C Drive. You can create several cash flows during the year and have them available to you in one spot. Give the file a name that reflects that it contains “Current” data.

14. Make your selection and press Enter to download. You will see it download the file and display a screen telling you that it has been successfully downloaded.

Attachment #2

Downloading Prior Year Cash flow Report #2 for Excel

1. Change the year in Reflections to PY.

2. Choose #2, Budget

3. Choose #5, SACS Financial Reporting System

4. Choose #6 SACS Cash Flow Analysis Report

5. Define date from 11/01/PY to 06/30/PY (or 02/01/PY to 06/30/PY for 2nd Interim). See example #2. The spreadsheet will be using this report to create percentages for months that are in the future.

6. Choose Report Template #1.

7. Indicate Y on the line that asks about comma delimited files. Press enter and Continue.

8. On the next screen indicate Fund 01. Press enter and F7 to launch.

9. When you go into your print screen, you will see that it has created two reports. One is a regular cash flow report that you can print. The second is the comma delimited file to be downloaded to the Excel spreadsheet.

10. Print the regular cash flow report and be sure you do not have anything “Out of Range.” If you do have items out of range, you can not correct them because the books are closed. They will have to be corrected in another manner.

11. Place your cursor on the comma delimited file and type L and press enter.

12. Tab down to the question, “Delimiter?” Indicate C for comma. Press enter. Then press F3 to Build the DL File. Then F7 to download the DL File.

13. The next screen is asking where you would like to place the file. Give the file a name that reflects that it contains “Prior Year” data.

14. Make your selection and press Enter to download. You will see it download the file and display a screen telling you that it has been successfully downloaded.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download