Updating Budgets Balances

1.      Obtain the most recent materials and revenue budget summaries.

2.      Open the "Budgets" excel spreadsheet, located in I:\ACQ\AcqStatistics.

3.      Begin by updating the values in the "Revenues Free Balance" column.

•      The current values are on the revenues budget summary.
•      Find the fund on the summary and update the value in the spreadsheet to match the value under "Free Balance".
•      Do this for each of the revenues budgets.

4.      Update the values in the "Materials Free Balance" column.

•      The current values are on the materials budget summary.
•      Find the PARENT fund on the summary (usually beginning on the 3^rd page).
•      Update the value in the spreadsheet to match the value under "Free Balance", but be sure to omit the negative.
•      Do this for each of the revenues budgets.

5.      The values in the "Total Current Free Balance" for the revenues budgets will update based on the values entered above. Check that the formula is still working correctly.

6.      Update the values under "Total Current Free Balance".

•      The current values for the remaining budgets are on the materials budget summary.
•      Find the fund on the summary and update the value in the spreadsheet to match the value under "Free Balance".
•      Do this for each of the remaining budgets with the exception of MRSV, PURPER, and RESV, leave these blank.

7.      Obtain data and update foundation accounts

•      Open the Access File FoundationAccounts located at I:\ACQ\AcqStatistics
•      Open the Main Interface under Forms and click Fund Balance Reports
•      Update values in the Fund Summary Encumbrances using the materials budget summary.
•      Update the date and close.

e.     Obtain Free Balance information from report. Input these numbers  into Budget spreadsheet.

8.      Save and Close the spreadsheet

9.      Import the updated spreadsheet into Access, located at I:\Orders\bookRequest2new.mdb

•      Open bookRequest2new and select the "External Data" tab from the ribbon.
•      In the "Import" box, click on "Excel".
•      Click "Browse" and select the spreadsheet you just updated.
•      Make sure that "Import the source data..." is selected and click ok.
•      Make sure that "Show Worksheets" is selected and "sheet1" is highlighted and click next.
•      Check the box next to "First row contains...", click ok on the popup box, and click next.
•      Scroll to the right until the "Field 9" column is visible. Select the column and change the name to "Balance" by typing it into the "Field Name" box., click next.
•      Make sure that "Let Access add..." is selected, click next.
•      In the "Import to Table" box type exactly Balanceimport, click finish.
•      Click yes to overwrite the existing table and click close.

10.  Run the Budgets Query to update the budgets online.

•      Select "Queries" from the drop down menu on the left.
•      Open "Balances" in design view.
•      Update the date.
•      Close and Save.
•      Run the query.  It should be updating 67 fields, if not stop and ask for help.

11.  Choose "Tables" from the drop down menu.

12.  Open "Depts" and check to make sure the dates and the values were imported correctly.