# Excel Solver for Expense Transfers

The solver function is especially useful if you deal with a multitude of funding sources such as state and local grants, federal awards and bond funds.  Each restricted funding source has very particular spending rules and most have time limits.  It is a common practice to move qualifying expenses to expiring grants.

This usually occurs when school principals “leave money on the table” by not spending their most restricted funds first. During the year-end closing process the business office now must move expenses so no money is returned.

Or, as recently occurred, a large bond program was ending.  There was a fund balance of roughly \$300,000.  Several other funding sources had been tapped to complete all of the construction projects.  Instead of adding projects to spend the final bond fund balance, we found qualifying expenses that matched the available funds to the penny, to just “end this thing”.

Here’s the process:

• Download a list of eligible invoices into Excel (include, such things as invoice number, PO number and current account coding).
• Use the solver function and type in the exact amount that you are trying to transfer
• Do something else while the solver tries to find invoices that exactly add up to your desired amount (it could take a while)

What happens, though, if there is no solution? What if no combination of qualifying invoices matches your required total?

I have a solution for that.

• In the column next to your invoice amounts, round them to the nearest dollar.
• In the solver, type in the rounded down amount you are solving for.
• If there is still no solution type in the next lower whole number into the solver and try again.  It should only take one or two tries until you have a hit.
• Multiply the solver factor by the true invoice amount and calculate the difference from the true amount being solved for.
• Select an unused invoice and move the amount required (percentage) of that invoice.  It will be one or two dollars at most.