Monthly Approval Report

PRODUCTION OF MONTHLY APPROVAL STATISTICS REPORTS.

Step 1: Preparations for monthly statistics reports. Assembling data.

Last day of the month: Confirm with all the Library assistants (Jim P., Cindy K., Scott S., and Debbie Thompson) that vendor spreadsheets and manual count spreadsheet have been updated from the paper log.

Assemble other data required for monthly statistics:

While most of the data for the monthly statistics will be compiled on the monthly report spreadsheet from the vendor spreadsheets by using macros, some data must be entered manually. These statistics should be gathered and on hand before beginning Step 6 Manually add the data not supplied by the vendor spreadsheets to the monthly statistics spreadsheet. These data are:

• Year-to-date BALANCE for each vendor for the same month in the previous fiscal year. Copy and paste from the previous fiscal year’s report of the same month in DSF (Y:\Acquisitions\Approval Section\Approval Stats Archive).

• Sierra encumbrance data for A/BO vendors: This is obtained by selecting the Funds option in Sierra and then the Approval/BO plans folder under Hierarchies. Note that only Texas A&M University Press should have encumbrance data

• Statistics reported in the spreadsheet ‘LA II Stats’.

For convenience in entering the data for the monthly report, print out the spreadsheet LA II Stats found in the folder Stats (current fiscal year) in the Approval Section folder in the Acquisitions folder in the DSF (shared) folders.

A brief description is given below for each group of statistics kept in this spreadsheet. These are all manual counts kept by the Library Assistants.

• Number of titles/volumes for the monographs from Serials Acquisitions sent to the A/BO section for the month. Usually there are fifty to sixty titles received per month from Serials and an additional thirty to forty from the Public Affairs Library.

• Center for American History receipts manual count supplied by person in section responsible for this duty.

• Purchase Orders sent to vendors A copy of this part of the spreadsheet is given to the Department head along with the Monthly Report. This part of the spreadsheet will print out on a separate sheet of paper.

Once all this data has been gathered the production of the monthly statistics reports can begin.

Use of vendor spreadsheets, macros, and monthly spreadsheet to produce monthly statistics and reports.

All spreadsheets and macros are found in the folder Stats (current fiscal year) in the Approval Section folder in the Acquisitions folder in the DSF folder. The instructions below assume that the Stats folder is open and all the vendor spreadsheets and macros are displayed.

NOTE: No one else should be allowed to access the vendor spreadsheets until the entire report procedure is finished. If a vendor spreadsheet is opened while the 'Monthly Report' spreadsheet is open, then the report spreadsheet will reflect any changes made in that vendor spreadsheet.

Step 2 - Update the date parameters in the VENDOR MACRO

The VENDOR MACRO extracts relevant information on number and amount of invoices to bookkeeping, "in process" invoices, and the "received" and "in process" title and volume count for each vendor as entered in the MAC log. It is used by the UPDATE ALL SPREADSHEETS MACRO when all the vendor spreadsheets are updated in preparation for the monthly report.

Open the document VENDOR MACRO. It is likely that a message box will appear asking you to verify that you do want to open this file:

Click yes

There are two lines of code to change each month:

Line 3 - Select the cell in line 3. Change the date within the parentheses to the appropriate month (e.g. change 4/1/2013 to 5/1/2013) Make sure to leave the => unchanged. Change year when appropriate. Press [ENTER] or [RETURN] to enter the change.

Line 5 - Select the cell in line 5. Change the month and day of the date within the parentheses to the appropriate month/day. The day will be the last day of the month. (e.g. change 4/30/2013 to 5/31/2013) Change year when appropriate. Press [ENTER] or [RETURN] to enter the change.

Save the changes in the macro and leave the macro document open.

Step 3 - Use the UPDATE ALL SPREADSHEETS MACRO to update all vendor spreadsheets.

Open VENDOR SPREADSHEET.xlsx.

Run the UPDATE ALL SPREADSHEETS macro against all the spreadsheets.

Click on the VIEW menu in Excel and then click on the drop-down menu under Macros.

A box will appear:

Select ‘VENDOR MACRO.xlm!Update_all’ and then click on ‘run’.

Each vendor spreadsheet will be updated and the changes saved.

After the macros has finished running, close the VENDOR MACRO spreadsheet.

Step 4 - MONTHLY REPORT spreadsheet: Adjustments to allocations. If there are no adjustments to allocations proceed to the next step.

Open the MONTHLY REPORT spreadsheet.

NOTE: Do not click on the Enable Content button. This will keep the monthly report in its “blank” configuration.

There are columns for four different adjustments. Enter the adjustment (either a positive or a negative number) into to a cell in columns F through M and the appropriate row for the vendor. Add the date of the change to the appropriate cell.

Save this change to the MONTHLY REPORT spreadsheet.

Close the spreadsheet.

This adjustment will remain for the rest of the fiscal year.

Step 5 - Create the monthly report for the appropriate time period.

Open the MONTHLY REPORT spreadsheet.

Click on the to the Enable Content button. The spreadsheet will then access the vendor spreadsheets and extract the appropriate data from each.

Perform a “save as” to the Monthly Report folder and name the new document as the current statistics period

(e.g. May FY 2012-2013).

Step 6 - Manually add the data not supplied by the vendor spreadsheets to the monthly statistics spreadsheet.

Previous year-to-date financial statistics:

Update monthly stats in spreadsheet column AA (FY XX BALANCE YTD) using the printed statistics report from the same month of the previous fiscal year. Alternatively, copy and paste from the previous fiscal year’s report of the same month. For each vendor key in the BALANCE information.

Sierra Encumbrance data

For each vendor with encumbrances:

Key in the Sierra encumbrance value in column V.

monographs from serials

Input title count in column AR, row 9 and volume count in column AS, row 8. Input year-to-date title/volume count in columns AT and AU.

Center for American History receipts

Input title count in column AR, row 30 and volume count in column AS, row 31. Input year-to-date title/volume count in columns AT and AU.

Step 7 – Saving monthly reports from monthly statistics spreadsheet

Four separate reports are extracted from the spreadsheet. These are

• Financial Report

• Title/Volume Report

• Encumbrance Report

• Summary Title/Volume, Invoice count Report

The reports are each saved as a PDF then merged into one PDF.

Use of ‘Custom Views’ to print reports

The Excel 'Report Manager' is used to print all four reports at once. All page set-ups and configurations are specified by the report manager.

To print the complete monthly report:

From the Excel menu bar choose View, then select Custom Views.

When the Custom Views dialog box appears select the desired View and click the Show button

Once the View changes to the selected view, save that view. Do a Save As and save each report as a PDF (Encumbrance Sept FY 2014-2015.pdf, for example)
Repeat for the other Views.

Step 8 - Freeze data on monthly statistics spreadsheet

Examine the spreadsheet and printouts to make sure that there are no cells that have bad data values (e.g. bad links). Once this has been ascertained, the spreadsheet data will be 'frozen' by replacing all the formulas and links with the values that currently are in the cells. This prevents any changes in the spreadsheet if it is opened while some of the vendor spreadsheets are open.

Select the entire spreadsheet by using [CTRL][A]
Click Copy
On the Edit menu, click Paste Special
Under Paste Special, click Values, then click OK

Save changes

Step 9 – Merge the PDFs and distribute

The report is assembled in this order:

Financial report,
Title-Volume repor
Encumbrance report
Summary page

Use a site like PDF Merge to put each PDF in order and then merge into one file, once the file is downloaded to your Downloads folder go change the name to match the Excel version (Sept_Oct FY 2014-2015.pdf, for example) and move it into the Acquisitions>Approval Section>Stats FY 2015-2015>Monthly Reports FY 2014-2015 folder)

Email the pdf version of the report to the following people (as of 8-15-2013)

Robin Fradenburgh
Jim Peoples
Cindy Kelly
Merry Burlingham
Jose Montelongo
Laura Schwartz
Katie Pierce-Meyer
T-Kay Sangwand
Janelle Hedstrom
David Hunter
AJ Johnson
Susan Macicak
Hugo Chapa-Guzmán
Uri Kolodney