Final report : April 2, 2007
Section 2 - Development of "Baseline" Source Datase
2.1 Process Used to Develop Baseline Dataset for FY2005
The dataset used by the LCC model for the RC-based, or Baseline system, updated with FY2005 source data, is attached as the Excel file "FY2005 Calculated Values April 2007.xls." It consists of an Excel workbook composed of 16 worksheets, or tables, in which source data are contained and compiled as necessary to provide inputs for the 101 data "Elements" used by the LCC model. An associated notebook (3-ring binder), provided by NLS, has been updated with additional hardcopy backup added as necessary and is being submitted to NLS along with this final report.
The development of this dataset began with the use of the file "FY2005 Calculated Values.xls," dated May 23, 2006, provided by NLS. The first step in the process was to verify data for FY2004 that were contained in the FY2005 file against FY2004 data in several other files, also provided by NLS, the purpose being to establish a basis for which the derived FY2005 values could be compared. It was determined that all the FY2004 data were consistent among the several files.
It was then determined that the FY2005 file as provided was only partially completed and documented. Furthermore, there was no "checklist" which identified the extent to which the FY2005 update had progressed (the staff responsible for performing the update had left NLS prior to completion of the update). ManTech, therefore, had to effectively perform a full audit of the FY2005 dataset, collect and compile additional source data as necessary, and calculate the values of the 101 data Elements as required for use by the LCC model.
Any logic errors found in formulas which compile the source data in the various worksheets were corrected. ManTech also identified and brought to the attention of NLS in the draft report several issues regarding estimates and assumptions for the source data used by the LCC model, and NLS feedback regarding these issues has been incorporated into the final report and LCC model FY2005 update.
2.2 Baseline System Source Data Tables
Site The tables from the 16 worksheets in the FY2005 Baseline source dataset are contained in Appendices A of the report, which document the sources of all data used to generate the values of the data Elements used by the LCC model and how they are compiled. The Numbers, names, descriptions, FY2005 values, units of measure, and sources of the 101 data Elements used by the LCC model are detailed in the "Data Dictionary" table, which is shown first. Below are summarized noteworthy points regarding the data tables and data Elements.
Data Dictionary List
Source data for the 101 Elements are not actually input directly into this table, but are brought in by formulas. The only data input directly by a user is the fiscal year (as FYyyyy and yyyy, where yyyy is the fiscal year number). So the table effectively serves as the compilation of the source data Elements, and becomes the input dataset to the LCC.
Therefore, no logic changes of any types were made. Minor corrections to definitions were made, e.g., the source shown for Element 77, Labor Inflation Rate, was Annual Library Resources Directory, but actually is the Employment Cost Index (ECI). LCC development work by Northrup was first reviewed before making such changes.
Inflation Rate
Source data backup was lacking for this table (which was later found misfiled in the notebook provided), so the BLS website was accessed and data were obtained for both the Consumer Price Index (CPI) and ECI for FY2005. The misfiled backup was filed in the notebook for the correct table.
The footnote for the CPI was modified to cite the specific CPI data needed (All Urban Consumers (CPI-U)) for the update among several types of CPI data that are available from BLS.
The footnote for the ECI was modified to inform the user that the base year for the index has recently changed from June, 1989 to December, 2005. Backup data for the 2005 ECI for both bases is included in the notebook.
In future years, the ECI must either be converted by the user from the December, 2005 base to the June, 1989 base (which are the values used in the model), or the values for prior years in the model must be converted to values using the December, 2005 base.
Errors were found in the formulas calculating the "Constant Factor" variable (in the CPI part of the table) for the years 2001–2005, which were corrected. Errors in these cells would have caused errors in the values for this variable for all years (since the value for year "t" relies on the value for year "t+1") in any prior calculations. Correction of these formulas fixed the current calculations for a FY2005 base year.
Readership and Circulation
There were no noteworthy points regarding the update of this table, which includes recorded readership and circulation source data, from which growth rates are calculated for each.
Machines, General
The formulas in Cells C40 and G10 were corrected to reference activity for the correct years.
It was noted that the Machine Cost Inflation formula in the table used a 1-year average to determine the rate of change, which is different from almost all other rate-of-change variables calculated in the various worksheets in the LCC model which typically use multiple years to determine the rate. The value for this data element for FY2005 now uses a 3-year rather than 1-year average.
Machines, Assigned
All inputs from the FY2005 MMRs were verified, and the formula in Cell D147 was corrected.
The LSU and DBR rates were changed to 3-year from 5-year averages. This was done to remove the anomalous behaviour of FY2001 and FY2002 when C1’s were both written-off and disposed in an atypical fashion.
In the smaller table in the bottom of the Machines Assigned worksheet (which calculates values that are apparently not used in subsequent calculations), the formulas were changed from 7-year to 5-year averages.
Machine Repairs
Additional information was requested from NLS, misfiled invoices were consolidated with others already in the notebook, and reported totals and invoice totals were compared and reconciled wherever possible. It is noted that the calculation of the rate of change for "Non-Contract Repair Attrition Rate" has used in the past and still uses data which include contract repairs, i.e., total repairs by all repairers.
Machine Disposals
No backup documentation was provided in the notebook, but quantities (different from those of FY2004) and unit cost (the same as for FY2004, i.e., $2 each) were already entered in the file in this table. The unit price is all that is actually required by the model.
Machine Usage, MTBF
This factor of 248.1 machine hours per assigned machine was determined by Northrup and later investigated and verified by NLS. It is dependent upon the use of a value of 1,000 hours for the MTBF of the CBM, based upon a review of LCC model documentation. The value was left unchanged.
Machine Batteries, Spare Parts
Total expenses for both CBM spare parts and batteries were lacking and therefore requested from NLS, obtained, and input into the table. A unit cost of $6.47 was assumed.
An error in Cell C79 was corrected (activity in FY2002 rather than in FY2005 was referenced).
The error previously noted in the "Constant Factor" for the CPI would have impacted previous calculations for batteries and spare parts expenses by not correctly inflating historical dollars to current dollars used to calculate multiyear averages.
Book Production
The table was using data for book production that was not the most current. The most current data were obtained from the NLS Production Control Department and used, including both FY2005 and multiyear histories from PICS.
Logic for container reconditioning activity and costs were added to the calculations. Data for container reconditioning activity for the past 3 years were requested from NLS, provided, compiled, and used to calculate average annual activity and costs. A weighed-average container unit cost is now calculated taking into account both reconditioned and new containers.
Magazines and TBT
The existing table had many errors since a file had been pasted-in and a hidden column was throwing-off many of the calculations. Therefore, the source data were re-input and the calculations were reconstructed with all hidden columns removed.
The file was also using data for magazine production that were not the most current. The most current were obtained from the NLS Production Control Department and used, including both FY2005 and multiyear histories from PICS.
Several formulas were changed for which the LCC documentation called for weighted rather than simple averages, e.g., Magazine Frequency, Magazine Media Units, Magazine Length, Magazine Copies, Magazine Master Cost and Magazine Media Unit Cost. These changes typically resulted in small differences, but were nevertheless changed in order to conform with the model’s documentation.
Production Inflation Rates
No backup or calculations had been performed for this data Element. Source data were obtained from NLS, added to the table, and used to generate Element values. The backup was then added to the notebook.
The formulas for both narration and duplication costs were corrected (the range of years used to calculate the averages were incorrect).
Network & MSC Figures
Errors were determined in the formulas in Column K after a review of LCC documentation. Correct inflation indexes (3 are used to inflate different cost components from 1989 values) were used, but a factor of 1.01 raised to the 15 power was found in all of the cells. Upon review of the LCC model documentation, these Elements are supposed to be adjusted by the appropriate inflator and the Average Readership Growth Rate (Element 3). The (10-year) Average Readership Growth Rate, which changes year-to-year, should be raised to the power of the number of years between 1989 and the current year, not a fixed factor of 1.01 raised to the same power. This mistake was causing the Element estimates to be inflated by approximately 16% above the correct values (the Average Readership Growth Rate is very close to zero), and likely caused high estimates in earlier years.
This table also contains a calculation for the Operations Inflation Rate. No update had been made for FY2005. Therefore, the required data (CPI - Other Goods and Services dataset) were obtained from the BLS website, printed, added to the notebook, and entered in the table.
Facilities Inflation Rates
No backup for source data was in the notebook, but an Adobe Acrobat version of the CoStar report with 2005 data was provided by NLS and used for the update; FY2004 data were in the table as obtained. The CoStar report was printed and added to the notebook and the data were added to the file.
There is a flaw in the manner in which the Facilities Inflation Rate is calculated which significantly understates facility space costs. The problem lies not in the inherent logic of calculating a multiyear inflator factor for multiple geographic areas, determining an average national inflation factor, and then calculating an annual average rate of change. The problem is that the CoStar current rate is used in conjunction with a 1989 GSA rate, each ostensibly representing 75% of "office space" costs (as a proxy for library space), but the estimates are apparently incompatible for many areas. This calculation methodology results in some locations, like Boston, MA, showing a decline in price of 20% over the 16-year period from 1989 to 2005, which is clearly incorrect. The logic was therefore changed so that the inflator factor for any geographic location is equal to the greater of the calculated value or 1.0.
Data for Jacksonville and Miami-Dade were added to the calculations, since 1989 data and current data exist for both.
USPS
Backup for source data as hardcopy was available in the notebook, and was input to the table.
A correction to the formula which calculates a multiyear average total cost was corrected (it included 1998–2003 activity rather than activity through 2005).
Some costs incurred by the USPS for which it is reimbursed by Congress are actually attributable to the delivery of mail to and from overseas voters and not for Free Matter. Therefore, USPS costs for the free national library program are overstated to some extent, but by how much is uncertain. It may be impossible to separate the costs for these two uses, but it may also be worth asking the USPS if they have any estimates for the same (based upon sampling and/or audits).
Compare 2005 to Previous
This table simply takes the final Element values from the Data Dictionary table and compares them to Element values calculated for prior years. No data inputs are actually made directly to this table.