Maximizing Impact: Using Excel Solver for Optimal Library Budgeting

This overlooked add-in can have a big impact on budget decisions

Posted on November 3, 2025 in Blog Posts

In the world of libraries, every dollar matters. As budgets tighten and the demand for new services and resources increases, resource allocation increasingly becomes a complex and high-stakes puzzle. This is the question that keeps administrators up at night: Which projects will deliver the greatest return on investment for our patrons, and how do we fund them responsibly? 

Much too often, these critical decisions rely on intuition or historical spending. But what happens when you could eliminate the guesswork and use hard data to find the best combination of projects within a fixed budget?

This is where Excel Solver comes in. Solver is an often-overlooked tool that transforms spreadsheets into a decision-making engine. It is an Excel add-in program used for optimization, which basically means finding the best (maximum or minimum) value for a specific goal. In doing so, Solver systematically adjusts values in a set of designated cells while adhering to specific constraints. For library leadership, this means moving beyond simple budget tracking to evidence-based, optimal (yes, optimal) resource planning.

How Excel Solver Works

Think of Solver as a Goal Seek with superpowers. Excel’s Goal Seek function allows users to work backward from their desired solution to an equation to discover the necessary variables to reach that solution. But whereas Goal Seek can only change one input to find a target result, Solver can simultaneously adjust hundreds of variables to find the best result for your main goal, even if you have multiple, independent, or interdependent constraints.

The three main components of Solver are:

• Objective cell: The single cell containing the formula you want to maximize (like community impact score), minimize (total cost), or set to a specific value. This is your goal of the analysis.

• Variable cells: The cells whose values Solver can change to reach the objective. In our budgeting scenario, these will be the cells that determine whether a project is funded. These are your decision points.

• Constraints: These are the limitations or rules that must be followed. For example, this is where you set your total budget cap and minimum staffing levels, or ensure that a resource purchase is an “all-or-nothing decision.” Without constraints, the answer is always infinite profit (or impact) at infinite cost!

By defining these elements, you enable Solver to navigate thousands of possible outcomes and confidently present a single, mathematically optimal solution.

---

🔥 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:

• Filling in the Gaps: A Guide for Data Imputation for Libraries
• An Overlooked Library Tech: Apps and Extensions
• Field Notes for a One-Person UX Team

---

Enabling the Solver Add-In

Before you can use Solver, you need to activate it. Solver is often uninstalled with Excel, but not visible by default.

1. Go to File → Options
2. Click on the Add-Ins category.
3. In the Manage box at the bottom, select Add-Ins and click Go
4. In the Add-Ins dialog box, check the box next to Solver Add-in and click OK.

Scenario: Maximizing Priority Score for a Collection Grant

Let’s imagine your library has been awarded a $50,000 grant for collection development. You have identified several resource packages, each with a cost per unit and a measurable Priority Score (1–100) based on circulation history, strategic goals, etc. We need to maximize the total weighted Priority Score while staying within the $50,000 grant limit.

The problem: You need to find the optimal quantity of each resource package to purchase that maximizes the Weighted Score without exceeding the $50,000 grant budget. Three heavy-hitting, though slightly distinct resources, are competing for this grant.

1. Setting Up the Excel Model

Resource Package	Cost Per Unit (B)	Priority Score (C)	Units to Buy (D)	Total Cost (E)	Weighted Score (F)
The Highlander	$1,500	85	0	=B2*D2	=C2*D2
Danielle Steele	$15,000	95	0	=B3*D3	=C3*D3
Tom Clancy	$5,000	70	0	=B4*D4	=C4*D4
				Total Grant Used	Total Weighted Score
Totals				=SUM(E2:E4)	=SUM(F2:F4)
Max Grant Budget	$50,000

In Excel:

• Decision Variables (D2:D4): These are the quantity cells Solver will change (the Units to Buy)
• Total Grant Used Cell (E7): This calculates the sum of the total costs for all units purchased.
• Total Weighted Score Cell (F7): This is our Objective Cell. It is the sum of the Priority Score multiplied by the number of units purchased for each resource. This is the score we want to maximize.

2. Configuring Excel Solver

1. Go to the Data tab and click Solver.
2. Set Objective: Set the cell reference to the Total Weighted Score Cell (F7).
3. To: Select Max (we want the highest score possible)
4. By changing variable cells: Select the Units to Buy cells (D2:D4)
5. Subject to the Constraints: Click Add to enter the rules:
   1. Budget Constraint: E5<=B8 (Total cost must not exceed the max grant budget)
   1. Integer Constraint: D     2:D     4 = integer (You must buy whole resource packages; you can’t buy one-half of a book)
   1. Ensure the “Make Unconstrained Variables Non-Negative” box is checked (you can’t buy negative books!)
6. Select a Solving Method: Choose Simplex LP (Linear Programming)
7. Click Solve.

3. The Optimal Solution

As you can see, Solver determined the optimal solution based on our objective to maximize the Total Weighted Score within the budget. It arrived at 33 units of the Highlander because that package offers the best financial return: its Priority Score Per Dollar (85/$1,500 ≈ 0.0567) is significantly higher than the cost of Tom Clancy (70/$5,000 ≈ 0.0140) or Danielle Steele (95/$15,000 ≈ 0.0063) packages. By buying the maximum possible amount of the most cost-effective item, the model achieved the highest possible score of 2805. 

However, this solution is not equitable, as the Highlander resource package was disproportionately purchased compared to the others. This is a key factor to consider. To remedy this imbalance, let’s ensure each resource is purchased at least twice. We’ll add the following constraint $D$2:$D$4=>2

• The Equitable Solution

By adding this constraint, we’ve increased equity at the cost of reducing the total weighted score. There is not a single “right” answer. Solver simply identifies the most extreme (or optimal) scenario based on the constraints you provide. Given your professional mandate, which solution better aligns with your library’s priorities: the highest weighted score or the most equitable resource distribution? Food for thought.

Conclusion

Solver offers a wide range of analytic scenarios for library operations. It functions as a powerful starting point for decision-making and provides an optimized scenario of “what could be” under specific constraints. Please keep in mind, however, that it is not the final answer. The Solver solution is a model that requires your professional judgment and interpretation to account for real-world complexities. Specifically, you must weigh the calculated optimal solution against the need for equitable practice. It is ultimately you who makes that final determination. In my next post, we will explore how Solver can be applied to staffing and scheduling.

Related Posts