ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
Five Year Financial Forecast Spreadsheet
2
Instructions
3
4
This spreadsheet was developed by the SSDT to assist school district's in preparing the Five Year Financial Forecast required by HB412 and EMIS.

The spreadsheet may be used alone (by hand entering the values) or in conjunction with the USASFF program provided by the SSDT with the USAS software. The USASFF program provides a CSV file which can be imported into this spreadsheet to provide the three prior year actual values as well as the expendable/receivable values for the current year estimate.

Note: These instructions assume you are using Microsoft Excel 97 or Excel 2000. The spreadsheet is believed to be compatible with earlier versions of Excel. However, the precise steps may vary slightly if you are using a different spreadsheet application.		Tip

If you have questions or need assistance using this spreadsheet, visit the USAS forum on the OECN Public Discussion forums at http://ssdt.oecn.k12.oh.us/forums

The Forums are a web-based 'discussion' areas (similar to DEC Notes). Someone from the SSDT will be there to help you.
5
6
Getting Started
7
This spreadsheet is actually a 'workbook" which contains multiple "worksheets". You will need to switch between the various sheets while you prepare the forecast. You are currently viewing the "Instructions" worksheet. Near the bottom of this window are tabs which allow you to switch between the various sheets. You should see the following worksheets:

-'Instructions' is the sheet you are currently viewing
-'Forecast' contains the Five Year Forecast
- 'Parameters' contains global parameters (district's name, FY)
- 'Data' may contain the information imported from USASFF.

Now switch to the 'Parameters' sheet and enter your district's name, county and the fiscal year. After you enter these values, switch to the 'Forecast' sheet to see that your district name and fiscal year appear in the headings
8
9
Importing Data From USASFF
10
If you choose to import data using USASFF. You should follow these basic steps:

1) Run USASFF and review the report. The text report includes the detail budget/revenue accounts which are included in each line.

2) Download the CSV file (USASFF.CSV) produced by USASFF to your PC or Mac. Your OECN DAS personnel may need to help you with this step.

3) You should now have both this forecast spreadsheet and the CSV file open in Excel. Now do the following: 		Tip

Be sure to download the CSV file in the correct mode.

For example, if using FTP or Kermit, download the CSV file in "ASCII" mode.
11
a. Switch to the CSV file and select the entire worksheet. The easiest way to do this is to click on the upper left corner of the sheet (in the corner adjacent to the column A and row 1 labels.). Alternatively, you may click-drag from cell A1 through F56 to select the appropriate cells.

b. On the menu bar, choose Edit->Copy. A dashed box should appear around the copied cells.

c. Switch back to the 'Data' sheet in the Forecast workbook and click on cell A1.

d. From the menu bar, choose Edit->Paste. The data from the CSV file should now appear in the 'Data' sheet.
12
4) If you completed the above steps successfully the data from the CSV file should be in the 'Data' sheet of this workbook. Now switch to the 'Forecast' sheet. You should see the values from the 'Data' sheet have been automatically placed in the correct locations of the Forecast.

If you see any error values (e.g. #VALUE, #N/A, etc) then something went wrong during the import. If this occurs, it is recommended that you start over with a fresh copy of the spreadsheet. (If you have already done a significant amount work in the forecast and don't want to start over, see "Correcting Common Problems" below.

After the data is loaded, you can proceed with completing the forecast (see below).
13
Entering or Correcting Data Manually
14
If you choose not use the data from USASFF, or you are not a state software user, then you must enter the actual values manually. You may do this by simply switching to the 'Forecast' sheet and enter the values in the appropriate row and column. There will be formulas in the cells. You may simply type the numeric values over the formulas. (Note: Once this is done, you may not load the USASFF CSV file later. If you type over the formulas then the link between the 'Forecast' and 'Data' sheets will be broken.)

Even if you loaded the data from USASFF, you will probably need to make manual changes to correct errors or reclassify revenues or expenditures. In this case, you have two choices. You may correct the values in the 'Data' sheet or you can replace the formulas on the 'Forecast' sheet as described above. If you make the changes in the 'Data' sheet, then the values will be automatically updated in the 'Forecast' sheet.

When entering manual data, you should not enter any values in lines which contain sub-totals (i.e., Cells that contain a SUM formula). The subtotals will be adjusted automatically when you change the detailed data.
15
16
Completing the Forecast
17
After importing or entering the actual values, you must complete the forecast. USASFF cannot supply all of the information necessary so you must enter the appropriate values in the 'Forecast' sheet. For example, you must enter values for 'New Levies', and 'Reserve' balances. It is left to the Treasurer to determine which values are needed to produce a valid and complete forecast.

The spreadsheet does not attempt to calculate values for the forecasted columns of the forecast. You may either calculate the numbers manually and enter the values in the appropriate column and row, or you devise your own formulas for the forecasted amounts.
18
Correcting Common Problems
19
Running out of memory

If Excel complains that it is 'out of memory', it's likely that this "Instructions" sheet is too large. Print the "Instructions" sheet and then delete the instructions (right click on the 'Instructions' tab and choose Delete).


20
Damaged Formula

If you accidentally delete or type over a formula, don't panic. The formulas in this spreadsheet were constructed to be easy to fix. A given formula on the spreadsheet can be recreated very easily. Simply select a formula from a similar cell that is still working and choose Edit->Copy. Then move to the cell with the damaged formula and choose Edit->Paste Special. In the dialog box, choose the 'Formulas' radio button and click OK.

Excel will automatically adjust the formula you copied to have the correct references.
21
Other Problems

If you manage to damage your copy of the spreadsheet in other way, you don't necessarily have to start over. Your local DA Site may be able to help you recover the spreadsheet. Alternatively, you can contact the SSDT for assistance. The best way to do this is via the OECN Forums web site (see link in first "Tip"). We may ask you to upload your spreadsheet to the forum so that we can look at it or repair it for you.
22
Exporting for use with EMIS (FFIMPORT)
23
After you have completed the forecast, you must provide the information to the EMIS-R system. You may either manually re-enter the data from this spreadsheet, or you may use the OECN FFIMPORT program to import the results of this spreadsheet.

To prepare this spreadsheet for importing into EMIS-R, switch to the 'Forecast' sheet and choose File->Save As... Give the file a different name and in the 'Save as Type' field choose 'Text (tab-delimited)'.

The resulting file must then be uploaded to the ITC computer system for loading into EMIS-R using FFIMPORT. Your ITC personnel may need to help you with the uploading process. The FFIMPORT program will convert this file into a file suitable for use in the EMIS-R flat file process.

Important: Be certain that you extract the 'Forecast' sheet and not the 'Percentage View' or 'Summary View'. Only the 'Forecast' sheet has all the information required by EMIS-R.		Note:

The spreadsheet is compatible with the default options of FFIMPORT. You do not need to enter any special values in FFIMPORT unless you have added or removed columns from the spreadsheet.
24
Other Suggestions
25
This spreadsheet was formatted to print acceptably on a laser or inkjet printer. If you find that the forecast does not print well on a single page with your printer, then you should use File->Page Setup and change the "Fit to" parameters appropriately. Most likely, you will want to print one page wide and two pages tall.

If the shading does not print well, or just annoys you, you can remove it easily by selecting the entire forecast sheet and choosing Format->Cells. Select the Patterns tab and then 'No color". This will remove the fill colors from the entire spreadsheet.
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100