Using Microsoft Excel Solver for Library Staff Optimization

Balance your budgetary constraints and staffing needs with this easy tool

Posted on December 15, 2025 in Blog Posts

It is the age-old challenge in the library world: the balancing act of the need for exceptional patron service with the reality of fixed or shrinking budgets. How do you ensure your circulation desk is fully covered during the chaotic midday peak without wasting money during the quiet morning hours? This is a classic optimization problem that keeps managers up at night.

You may recall my previous post where we walked through the basic mechanics of Microsoft Excel’s Solver and applied it to budget allocation for a grant. For more background on Solver, please refer to the earlier post. This post transitions directly from theory to practical application. Specifically, we will create a spreadsheet model designed to help you minimize staffing costs while guaranteeing that every service hour is covered. We will break down the data structure, the formulas, and the constraints you need to deliver the optimal schedule for your patrons.

The Staffing Model Framework: A Quick Review

Before translating our staffing problem into Excel, let’s quickly translate our library’s objective into Solver language.

1. Decision Variables (The Changing Cells): These are the staff counts for each distinct shift option. Solver will experiment with these numbers until it finds the ideal combination.

2. Objective Function (Minimize Cost): Our goal is purely based on financial efficiency. We will create a formula to calculate the Total Daily Staffing Cost and then tell Solver to drive this value as low as possible.

3. Constraints (Service Requirements): These are the rules we can’t break. The primary constraint is our service mandate, which means the staff scheduled must be greater than or equal to the minimum required, based on our patron traffic data.

---

🔥 Stay up-to-date with LibTech Insights by signing up for our free newsletter. Just one weekly email with our new blog posts, top tech news stories, and other bonus content. Check out some posts from our archive:

• How to Find and Fix Hidden Accessibility Issues
• Balancing User Needs and Technology after System Migration
• GenAI in Information Literacy Sessions: Strategies for One-Shot Instruction

---

Scenario: Scheduling Circulation Staff

A library manager is trying to create the most cost-efficient schedule for three student workers—Steve, Randy, and Amy—for a six-hour shift (9 AM to 3 PM). The goal is to minimize the total wage cost while ensuring adequate staffing throughout the day.

Goal: Minimize the total wage cost of employees while maintaining the minimum level of staff available during the day.

1. Setting up the Solver Model

Student	Max/Hours/Day (B)	Hourly Wage (C)		9 AM – 11 AM (E)	11 AM-1 PM (F)	1- 3 PM (G)	Total Hours (H)	Total Cost (I)
Steve	8	$15.00					=SUM(E3:G3)*2	=C3*H3
Randy	6	$14.00					=SUM(E4:G4)*2	=C4*H4
Amy	8	$13.00					=SUM(E5:G5)*2	=C5*H5
Min Students Needed				2	3	2
Actual Students Scheduled				=SUM(E3:E5)	=SUM(F3:F5)	=SUM(G3:G5)
Total Hours for Student	=SUM(H3:H5)
Total Cost	=SUM(I3:I5)

In Excel:

• Decision Variables (E3:G5): These are the quantity cells Solver will change (the positions to  Staff)
• Total Hours for Student Cell (B10): This calculates the total hours per student worked.
• Total Cost (B11): This is our Objective Cell. It is the total costs of the students working across all shifts. This is what we want to minimize.

Setting up the Solver Model:

1. Go to the Data tab and click Solver.
2. Set Objective: Set the cell reference to the Total Cost Cell (B11).
3. To: Select Min (We want the lowest possible cost)
4. By changing variable cells: Select the Units to Buy cells (E3:G5)
5. Subject to the Constraints: Click Add to enter the rules:
   1. Binary Constraint: E3:G5 = binary (The objective is to determine whether the student is working that shift. Solver will output a binary variable (1 or 0), where 1 indicates the student is working and 0 indicates they are not.)
   1. Minimum Staff Constraint: E8:G8 >= E7:G7 (The staff working each shift has to be greater than or equal to minimum staff needed)
   1. Maximum Hours Constraint: H3:H5 <= B3:B5 (Each student can only work so many hours per day)
   1. Ensure the “Make Unconstrained Variables Non-Negative” box is checked. (The number of shifts a student works cannot be a negative value)
6. Select a Solving Method: Choose Simplex LP (Linear Programming)
7. Click Solve.

The Optimal Solution

The objective of minimizing cost was successfully met while satisfying the required staffing levels. However, the current solution presents an equity issue: Steve is assigned only one shift, whereas Randy and Amy each have three shifts. This highlights a common dilemma in resource allocation: do we prioritize cost savings or equitable workload distribution? To address this, let’s implement a new constraint requiring each student to work a minimum of three shifts. New constraint: $E$3:$G$5 = 1

The Equitable Solution

By implementing the equitable constraint, we achieved a balanced workload where every student is assigned a fair share of shifts. This fairness, unfortunately, resulted in a significant budget impact. It increases costs by around $60. This optimization exercise highlights a critical conflict within the library’s mission: the practical application of equity principles versus fiscal responsibility. Determining the best path forward requires a decision on what is ethically sound and most valuable to the library.

Conclusion

The optimization activity demonstrates the inherent trade-off between cost efficiency and workload equity. While the initial solution successfully minimized staffing expenditures, it resulted in an unbalanced distribution of shifts among students. Enforcing the equity constraint increased the weekly budget by approximately $60, confirming that achieving fairness comes at a measurable cost. Ultimately, this modeling exercise shifts the focus from simple data calculation to a strategic decision for the library: the final staffing schedule depends entirely on whether fiscal responsibility or the organization’s equitable mission is the highest priority. The library must now define its acceptable balance point between these two crucial values.

Related Posts