Alteryx Designer Discussions

The Expert Exam is now live online! Read about the specifics and what it took to bring it to life in the blog by our very own Elizabeth Bonnell!

Identifying Invoices Within a Total Amount Constraint - Similar to Excel Solver Use Case

Highlighted
5 - Atom

Hi there Alteryx community! First time poster, long time lurker, and after days of frustration I'm hoping you guys can help us out!

We have received a lump sum payment from a client for a balance owed (\$100,000). However, the payment does not cover the full amount due (\$150,000), and we need to allocate this \$100,000 payment to individual invoices in order to mark them as paid. While we could just simply sort the invoice amounts from low to high, or by date, and use the running total tool to identify the invoices that fall within the total payment threshold, we'd prefer to identify which invoice amounts sum up to the greatest number less than or equal to the payment amount (\$100,000).

Is there a way to identify the optimal selection of invoices to mark as paid? Would the optimization tool work for this? If so, I'm having an incredibly difficult time figuring out how to get it to work in this case. Further compounding this challenge is the fact that we actually have more than 40 clients for which we need to go through this process, so any ways to process this for all of them at once would be amazing!

Typically, I would use Excel's Solver for a problem like this, but because we have more than 200 invoices to decide on, it's not feasible.

Thank you so much for any and all help!

Highlighted
9 - Comet

@mpstagefront  - Can you share a sample file so that somebody can help create a workflow for you. It may be easier for you to post a sample data file vs us to create one and then the workflow. I do think you have explained the problem well.

Highlighted
5 - Atom

Thank you for that suggestion! Please see attached for an example start for the workflow, containing two inputs:

1. The invoice detail containing each invoice (transaction) and original payment amount, by client (this input contains each transaction that we need to consider for the payment application process)

2. Payments received by client, to be allocated across the individual transactions from the invoice detail

This is just example data. In reality, we have over 135 clients to do this for... As I mentioned in the original post, I'd love to be able to build a model that can intelligently account for each client, but if I need to build out an arm for each client individually, so be it.

Also, for some perspective, I originally built out a workflow to do this that, rather than identifying the optimal combination of transactions, simply sorted the transactions from low to high (in absolute value), then used the running total tool to identify the cutoff for each client at which we would stop allocating payment. This worked pretty well, but did result in some higher remainders of the total payment balances on some clients than we would hope for.

Thanks again for any and all help!

Highlighted
9 - Comet

@mpstagefront  - Thanks for sharing data and providing some more details. I had a quick look and yes, the easiest way would be to sort your data and apply payments. If a client owes \$40,000 and made a payment for \$30,000 - remainder will always be \$10,000 irrespective of which invoices you apply them to..... so not sure what you mean by "some higher remainders of the total payment balances". There are few questions:

-  Doesn't matter order of sort, your 'remaining balance' for the client will be the same. If you sort amount by DESC, smaller invoices will remain and vice-versa.

-  Is the goal to apply to 'exact' amount of the payment received (in which case you need a better model and not sure if that will always happen in real life); else the last remaining amount can easily be applied to the invoice and kept remaining amount to be applied next time on that 1 invoice.

-  You can create a macro to run the logic for all clients in one go...... definitely can help you create that if you like based on your sample data

Let me know if this helps or makes sense. Happy to chat more and help.

Highlighted
5 - Atom

@pankajk: Thank you again for your response. It's actually untrue that the remainder would always be \$10,000 in your scenario. Each invoice is for a different amount, and therefore some will leave different remainders than others. Here's a more simple example:

Total amount to be reconciled: \$50

Individual invoice amounts:

\$5

\$12

\$18

\$20

\$45

Sorted this way, from low to high, we can only include the first three amounts (5 + 12 + 18 = 35) to stay under the \$50 threshold, leaving a \$15 remainder.

Sorting from high to low, we can only fit the first number (45) under the \$50 threshold, leaving a remainder of \$5.

If we want to minimize this remainder (maximize the application amount), then the optimal combination would be 45 + 5 = 50, which is a perfect match.

So, as you can now see, there is certainly an optimal combination of numbers to be applied. We don't expect perfect matches with the larger data sets, but we can get closer by selecting the optimal combinations. The Excel Solver tool is a great way to do this, but unfortunately, you need to create a variable for each number and the maximum allowed is 200 variables.

I'm curious if Alteryx' Optimization tool can handle this, but none of the documentation I've found seems to indicate that it can.

Highlighted
9 - Comet

Thanks for the explanation - I understand what you need. Also, my reference to remainder was from the total amount irrespective of which invoices you paid out.

Let me build something for you by tomorrow morning..... it's a very simple workflow/macro.

Highlighted
5 - Atom

@pankajk Thank you so much! I really appreciate it and can't wait to see what you come up with!

Highlighted
9 - Comet

@mpstagefront - Apologize but have only have couple of hours to try couple of models, which didn't work. But seems like this is an interesting problem to solve. I may not have time to 'solve' for this issue over next few days but would recommend that you talk to the VSC (Virtual Solutions Centre) if you haven't already done so. They would have lots of ideas to help you. If you msg me in private with your email, we can connect offline and chat as well.

https://community.alteryx.com/t5/Virtual-Solution-Center/tkb-p/vsc

Labels