Excel Solver for Expense Transfers

Thank you Pronger for your post about the Excel solver function.  Follow this link to view his explanation and instructions.

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.

QUMA (Questions You might Ask)

Why move expenses invoice by invoice?

• For audit purposes you need to be able to tie qualifying expenses back to specific purchases, and be able to track the approvals for those purchases.

Do I enter a General Journal to move these expenses and use the Excel spreadsheet as back up?

• Only if your system will not let you go into the sub-ledger (AP) and move them there.  You’ll be surprised at the number of times you discover you have to move that invoice again (e.g. it didn’t qualify after all).  When selecting a financial software package, the ability to adjust account coding in a sub-ledger (AP, AR, Payroll) should be on your list of requirements.  The sub-ledger adjustment will then automatically generate the required G/L entries.

Where is the Solver?

• It is on Excel’s data tab, at the far right.  If you don’t see it there, it will be because you need to add it manually.  Go to File, then Options, then Add-ins.  Find the Solver Add-in and add it.