Allocation of cost to value of savings.

[fayo2009]

Hello
Please help

I am trying to allocate the investment cost of £46k across these months. Each months allocation can not go over the savings value for that month. I cant get Nov to only allocate the balance of £4k and the remaining months are nil.

Investment cost	£46,000.00
Investment cost allocated in contract year	£64,000.00
Carried forward investment cost	-£18,000.00
Months		Apr-25	May-25	Jun-25	Jul-25	Aug-25	Sep-25	Oct-25	Nov-25	Dec-25	Jan-26	Feb-26	Mar-26	Total
Savings		4,000.00	3,000.00	5,000.00	0.00	10,000.00	10,000.00	10,000.00	10,000.00	3,000.00	3,000.00	3,000.00	3,000.00	64,000.00
Investment cost allocation		4,000.00	3,000.00	5,000.00	0.00	10,000.00	10,000.00	10,000.00	10,000.00	3,000.00	3,000.00	3,000.00	3,000.00	64,000.00
Savings		0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00

 

[awoohaw]

Hi try this. It requires nothing in cell C6.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1		Investment cost	£46,000.00		46000
2	Address	Investment cost allocated in contract year	£64,000.00		64000
3		Carried forward investment cost	-£18,000.00
4
5		Months		25-Apr	25-May	25-Jun	25-Jul	25-Aug	25-Sep	25-Oct	25-Nov	25-Dec	26-Jan	26-Feb	26-Mar	Total
6		Savings		4,000.00	3,000.00	5,000.00	0	10,000.00	10,000.00	10,000.00	10,000.00	3,000.00	3,000.00	3,000.00	3,000.00	64,000.00
7		Investment cost allocation		4,000.00	3,000.00	5,000.00	0	10,000.00	10,000.00	10,000.00	10,000.00	3,000.00	3,000.00	3,000.00	3,000.00	64,000.00
8		Savings		0	0	0	0	0	0	0	0	0	0	0	0	0
9
10				4,000.00	3,000.00	5,000.00	0.00	10,000.00	10,000.00	10,000.00	4,000.00	0.00	0.00	0.00	0.00	46,000.00
Sheet2

Cell Formulas
Range	Formula
C3	C3	=C1-C2
D10:O10	D10	=IF(SUM($D$6:D6)<$C$1,MIN(D6,$C$1-D6),MAX($C$1-SUM($C$6:C6),0))
P10	P10	=SUM(D10:O10)

 

[fayo2009]

it worked, many thanks

 

[awoohaw]

My pleasure. Happy to help. I am sure there are probably other more elegant solutions, but I'm happy this works for you.

Best wishes!

 

[fayo2009]

Hello
Please help

I am trying to allocate the investment cost of £3,545k to months. Each months allocation can not go over the savings value for that month. Please see expected results. Many thanks.