Payment priority invoice list

[MrTall]

Hi there,

We are trying to improve the way we are prioritising payment of invoices and giving department managers the ability to "sort" which invoices should get paid first based on needs to the organisation. With COVID things are tight at the moment with cash, therefore we want to ensure there is no impact on significant services if we are not able to pay all invoices on time. Since there is no built in function to do this in our ERP we have resorted to Excel to assist.

Basically the financial controller at the start of the week will advise how much can be paid this week as a total figure. I have tried then using a "Running total" formula M3=SUM($H$2:H3)), which is then dragged down and leads to N3 =IF(M3>'Stripped Example'!$M$1,"Pay limit exceeded. Invoice will not be paid this payrun","Invoice will be paid in this payrun"), also dragged down, to make it clear to the user what invoices will be paid based on the sorting.

The issue comes up on how I can make it as easy to sort as possible for the user and for the formulas to remain consistent. I have tried using "Cut/Paste" rows which leads to the SUM formula in column M to be out of order. Do you have any suggested alternatives? I have tried searching online for a template to do this also with no luck, but by all means if you have a cleaner version you recommend we should use please suggest away

Cheers,
MrT

 

[CA_Punit]

So you can add a helper column with priority of payments so for eg
consider

New Microsoft Excel Worksheet.xlsx
	C	D	E	F	G	H	I
2			Budget	10000
3
4		Company Name	Amount	Helper	Running Total
5		v	1000	1	1000
6		s	2000	1	3000
7		s	3000		FALSE
8		s	2000	1	5000
9		s	1000	1	6000
10		g	1999		FALSE
11		g	2000		FALSE
12		f	1000	1	7000
13		f	2999		FALSE
14		ea	1	1	7001
15		d	400		FALSE
16		d	4000		FALSE
17		c	100	1	7101
18		a	2000	1	9101
19		a	1999		FALSE
20
21
22
23
24
Sheet1

Cell Formulas
Range	Formula
G5:G19	G5	=IF(F5=1,IF(SUMIF($F$5:F5,1,$E$5:E5)>$F$2,"Payment will not be made",SUMIF($F$5:F5,1,$E$5:E5)))

I> have initiated Helper Column. You have to enter 1 in case you want to make the payment and in running total you will find 3 values 1/- False which means it is not scheduled for payment
2. Value i.e it is scheduled for payment
3. Payment will not be made" for showing budget value increased.

Even if you short the data it will not affest the payment status

 

[Fluff]

Hi & welcome to MrExcel.
Firstly remove the sheet name from your formula in col M.
=IF(M3>$M$1,"Pay limit exceeded. Invoice will not be paid this payrun","Invoice will be paid in this payrun")
You should never us the name of the sheet that contains the formula.

You can then sort the data without damaging the formula.
The users could also add a priority number in col L & sort on that.

 

[MrTall]

Hey @CA_Punit & @Fluff,

Thanks for your responses. I have removed the sheet name from col M (thanks Fluff).

I appreciate the suggestion to use a helper column, though the reason I was going to "move rows around" was in case the amount of readily available funds was reduced further after the sheet was already submitted. That was if the assumption was the list was already sorted in priority order than it would simply be invoices at the bottom of the list that would not be paid be default rather than having to go back to the submitter.

With that additional detail, do you have any further suggestions?

Cheers

 

[Fluff]

I'm afraid I don't understand what you are asking.
If the data is sorted on the priority column, then surely the most urgent invoices would be paid first.

 

[MrTall]

I'm afraid I don't understand what you are asking.
If the data is sorted on the priority column, then surely the most urgent invoices would be paid first.

Sorry - I think I confused yours and @CA_Punit's responses. In the first response I can see it is either 1 or blank in the helper column F, that is more what I was replying to. I will give your solution a go

 

[MrTall]

In summary - is there no way I can move/cut and paste the rows that wont creating issues with column M's coding? For example, currently cell M9 =SUM($H$2:H9). If I was to cut and paste it to the bottom of the list (row 27) it now shows as =SUM($H$2:H27) which is perfect. If I undo this and move it to row 4 (2nd from the top) it shows =SUM($H$2:H9) in error. It's like Excel is allowing me to move it lower in the list but not move it up the list.

 

[Fluff]

If you are going to cut/paste rows with formulae, the best thing is to reset all the formulae afterwards.

 

[MrTall]

If you are going to cut/paste rows with formulae, the best thing is to reset all the formulae afterwards.

OK - thanks for your help.

 

[Fluff]

You're welcome.