ASSIGNMENT:

Starting from a traditional line-item budget, create a program budget for the Adams County Library system using this excel sheet file:

https://docs.google.com/spreadsheets/d/1pnsyUOFDWVqgFMoyJDRF6JMwO9fY1J4TdDcwEe9pG2o/edit?usp=sharing

STEP BY STEP INSTRUCTIONS:

1. The library system has 5 programs:acquisitions, cataloging, circulation, reference, and special programs. General administration of the library, maintenance, and other general functions are not "programs." These expenses must be divided among the programs.

1. Following the instructions below, create a program budget for your FIVE programs. This means you will rearrange the budget using the spreadsheet that you have been given. This is your FY1 PROGRAM BUDGET.

1. Total staff levels will remain the same between FY 1 and FY 2. No changes to staff.
2. Allocate salaries to programs based on the time study data below.
   1. Branch librarians spend 35 percent of their time on general administration, 25 percent on reference, 20 percent on special programs, 15 percent on acquisitions, and 5 percent on circulation.
   2. Assistant librarians devote 30 percent of their time to cataloguing, 25 percent to reference, 25 percent to circulation, and 20 percent to special programs.
   3. Director of county libraries spends all of her time on general administration, as do the director's secretary and the administrative assistants.
   4. Circulation clerks spend 100 percent of their time on circulation.
   5. Pages spend 100 percent of their time on cataloguing.
   6. Reference librarians spend 100 percent of their time on reference.

1. Add up the General Administration salaries (100 percent of Director, 100 percent of Admin Assistants, 100 percent of Secretary, and 35 percent of Branch Librarians' salaries). Add the total, then divide by 5 and add 1/5 to each program's salaries.
2. Allocate maintenance, computer supply, tech, and utility costs to each program in proportion to their share of staff salary costs. After adding the General Administration salaries to each of the five program salaries (#5 above), determine the proportion of the total salaries that corresponds to each program. For example if Reference salaries are 1/3 of the total, 1/3 of the Maintenance, etc. costs in #6 will be allocated to the Reference program.
3. Add other line items to each program as needed. For example, children's programs would go in the Special Programs budget.
4. Now you have created the program budget for FY1. Next, make another column for the FY2 PROGRAM BUDGET based on creating metrics using the LIBRARY DATA below.You will use the data to estimate the growth for the following year for each program and develop a program budget for FY2 for each of the 5 programs. For example, in FY1, 421,000 patrons used the library system and it is expected that 423,000 will visit in FY2. There are two ways to estimate the budget for FY2.
5. Let's say the performance metric that you created based on FY1 was program cost / library visits = $5/visit. Then FY2 cost would be $5 x 423,000 = $2,115,000. OR, Percent change = (Year 2 - Year 1) / Year 1 x 100.(423,000 - 421,000)/421,000 x 100 = 0.475% or 0.00475. Then Year 2 cost could be estimated as:

Year 2 = Year 1 x 1.00475.

1. The library data below can be used to estimate the growth of each program in FY2.

1. Now you learn that a generous donor has made a donation allowing the library system to purchase a bookmobile. Bookmobiles have become popular since the pandemic. The expenses for a bookmobile would include the truck itself plus maintenance, staff, and materials (below). Add the bookmobile to your program budget for FY2. Which program(s) does it fit into? Circulation? Special programs? Both?
   1. Bookmobile truck = $75,000
   2. Driver-librarian salary + benefits = $50,000
   3. Books and materials for bookmobile = $10,000
   4. Annual maintenance expenses = $10,000

LIBRARY DATA

1. Acquisitions program. Total holdings at the time of the merger = 1,652,000 volumes. In FY 1, 18,000 additional volumes were acquired. In FY2, the library system plans to acquire 21,500 new volumes.
2. Cataloging program. The library system catalogues all new volumes in the Library of Congress system. In addition, each year it will re-catalogue 10 percent of all of its volumes each year.
3. Circulation program. The total circulation is FY1 = 752,000; FY2 = 776,000 (expected).
4. Reference Program. In FY 1, the reference department answered 67,800 questions. It is expected that there will be 71,500 questions in FY2.
5. Special Programs. In FY1, 24 after school programs were conducted, which were attended by 1,200 elementary school students. In FY 2, these programs are expected to grow to 1,400 students. The Great Books Discussion groups for adults met over a 10-week period in FY1, with a participation of 127 people, and this program will be discontinued in FY2 due to poor attendance. A foreign film series attracted 4,500 attendees in FY 1 and is expected to expand to 6,000 in FY 2.
6. In FY1, there were 421,000 visits to the library. It is expected that there will be 423,400 visits in FY 2. Visits to the library are related to the total library budget.