credit card conundrum

[nightster]

Hello everyone,

I’d really appreciate your help with this problem please…

We are managing our business spending across 4 credit cards.

Each credit card has a different limit. The total limit is £54,000

Every time one of the cards hits it’s credit limit, we move spending to the next card in the list.

Once the total spend gets to £53,000, we pay down the first card and start spending on that card again.

Card 1 has limit of £9,600. Card 2 has a limit of £14,400. Card 3 has a limit of £19,200. Card 4 has a limit of £10,800.

Please see this image to see how I would like this laid out: https://photos.app.goo.gl/g8aU7MRM2mH6Vvuq6

I would like to put the total spend for each week in row 2 and then for Excel to automatically fill in rows 5 to 8 and rows 11 to 14 (rows 17 to 20 and 23 to 26 are already taken care of as they are straight-forward).

Any ideas please community??? Thanks so much!

 

[AlanY]

shouldn't the 10k on week 4 spend on Card 3?

Formula;
C5 on its own
C6 copy down
D5 copy across
D6 copy down and across

Book1
	A	B	C	D	E	F	G	H
1			Week 1	Week 2	Week 3	Week 4	Week 5	Week 6
2			£8,000	£10,000	£12,000	£10,000	£12,000	£9,000
3
4	Spend
5	Card 1	£9,600	£8,000	£1,600	£0	£0	£0	£0
6	Card 2	£14,400	£0	£8,400	£6,000	£0	£0	£0
7	Card 3	£19,200	£0	£0	£6,000	£10,000	£3,200	£0
8	Card 4	£10,800	£0	£0	£0	£0	£8,800	£2,000
Sheet2

Cell Formulas
Range	Formula
C5	=MIN($B5,C$2)
C6	=MIN($B6,C$2-SUM(C$5:C5))
D5	=MIN($B5-SUM($C$5:C5),D$2)
D6	=MIN($B6-SUM($C6:C6),D$2-SUM(D$5:D5))

 

[nightster]

Wow, thanks!

Yes - thanks for spotting the error, you are correct.

So I have implemented this. Two issues:

- I don't think week 6 is working correctly?
- Do you have any thoughts on how I can handle the repayments, i.e. rows 11 to 14?

An updated image here (sorry I don;t know how to post the data directly in the thread):
https://photos.app.goo.gl/DLeJHmoTTASfNJ2K6

Thanks so much!

 

[AlanY]

depends, based on the numbers on post #2 , by Week 5 with 52k spent on the 54k limit, will you start to repay Card 1 on week 5 ready for the 9k spend on wk 6?

 

[nightster]

No, we would repay in the same we as we need to start spending again. As soon as we pay down a card, it is available for spending again. Make sense?

 

[AlanY]

check carefully, I've changed some weekly spending for testing

C5 on its own
D5 copy across
C6 copy down
D6 copy down and across

D11 copy across
D12 copy down and across

Book1
	A	B	C	D	E	F	G	H	I
1			Week 1	Week 2	Week 3	Week 4	Week 5	Week 6
2			£8,000	£10,000	£12,000	£10,000	£12,000	£15,000	£12,000
3
4	Spend
5	Card 1	£9,600	£8,000	£1,600	£0	£0	£0	£9,600
6	Card 2	£14,400	£0	£8,400	£6,000	£0	£0	£3,400
7	Card 3	£19,200	£0	£0	£6,000	£10,000	£3,200	£0
8	Card 4	£10,800	£0	£0	£0	£0	£8,800	£2,000
9
10	Repayments
11	Card 1	£9,600		£0	£0	£0	£9,600	£9,600
12	Card 2	£14,400		£0	£0	£0	£3,400	£2,400
13	Card 3	£19,200		£0	£0	£0	£0	£0
14	Card 4	£10,800		£0	£0	£0	£0	£0
Sheet2

Cell Formulas
Range	Formula
C5	=MIN($B5,C$2)
C6	=MIN($B6,C$2-SUM(C$5:C5))
D5	=MIN($B5-SUM($C$5:C5),D$2)
D6	=MIN($B6-SUM($C6:C6)+SUM($C$12:C12),D$2-SUM(D$5:D5))
D11	=MAX(0,MIN($B11,SUM($C$2:E$2)-SUM($B$5:$B$8)-SUM($C11:C11)))
D12	=MAX(0,MIN($B12,SUM($C$2:E$2)-SUM($B$5:$B$8)-SUM($C$11:C12)-SUM(D$11:D11)))

 

[nightster]

Hi,

Thanks so much for your response. Sorry I have left it so long - I have been away.

I have followed your instructions but the repayment section isn't working correctly unfortunately. I've tried to investigate and understand the formulae but it's beyond me!

You can see an image of my spreadsheet here: https://photos.app.goo.gl/99HFXJSexNEoRU4T6

An further advice please? Thanks so much!

 

[AlanY]

The repayment part is more complicated than I originally anticipated.
I've simplified the formula that make use of the empty cells in Rows 4 & 10 and the extra cell B9 (Card 1 credit limit)

copy C5 down to C8
copy D5 down and across
copy D11 down and across

you can find the working file here
https://drive.google.com/file/d/1C_R9AU32oXh6XvT_XO5VT44t-YaM9N9i/view?usp=sharing

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1			Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Week 7	Week 8	Week 9	Week 10	Week 11	Week 12
2			£8,000	£10,000	£12,000	£10,000	£12,000	£18,000	£12,000	£20,000	£45,000	£10,000	£10,000	£10,000
3
4	Spend
5	Card 1	£9,600	£8,000	£1,600	£0	£0	£0	£9,600	£0	£0	£9,600	£0	£9,600	£0
6	Card 2	£14,400	£0	£8,400	£6,000	£0	£0	£8,400	£6,000	£0	£14,400	£0	£0	£10,000
7	Card 3	£19,200	£0	£0	£6,000	£10,000	£3,200	£0	£6,000	£13,200	£19,200	£0	£0	£0
8	Card 4	£10,800	£0	£0	£0	£0	£8,800	£0	£0	£6,800	£1,800	£10,000	£400	£0
9		£9,600
10	Repayments
11	Card 1			£0	£0	£0	£9,600	£0	£0	£9,600	£0	£9,600	£0
12	Card 2			£0	£0	£0	£14,400	£0	£0	£14,400	£0	£0	£14,400
13	Card 3			£0	£0	£0	£0	£19,200	£0	£19,200	£0	£0	£0
14	Card 4			£0	£0	£0	£0	£0	£10,800	£0	£10,800	£0	£0
Sheet1

Cell Formulas
Range	Formula
C5	=MIN($B5,C$2-SUM(C$4:C4))
D5	=MIN($B5-SUM($C5:C5)+SUM($C11:C11),D$2-SUM(D$4:D4))
D11	=IF(SUM($B$5:$B$8)+SUM($C$11:C$14)+SUM(D$10:D10)-SUM($C$5:D$8)D10>0,$B5,IF(AND(SUM($C$11:C$14)=0,$A11="Card 1"),$B5,IF($B5=IF(SUM(C$11:C$14)=0,0,LOOKUP(2,1/(C$11:C$14>0),$B$6:$B$9)),$B5,0))),0)

 

[nightster]

Thanks so much!

Ok we're almost there. The last thing is that currently the each card is paid down the week before it is then spent on again.

In fact, the cards should be paid down the SAME week hat they are then spent on again. (what happens in reality is that we pay the card down and can start spending on it the same day)

I hope that makes sense?

I really do appreciate your help - thank you!

 

[AlanY]

that's easy, just modify D11 to

=IF(SUM($B$5:$B$8)+SUM($C$11:C$14)+SUM(D$10:D10)-SUM($C$5:C$8)<D$2,IF(D10>0,$B5,IF(AND(SUM($C$11:C$14)=0,$A11="Card 1"),$B5,IF($B5=IF(SUM(C$11:C$14)=0,0,LOOKUP(2,1/(C$11:C$14>0),$B$6:$B$9)),$B5,0))),0)