Nested offset

[Nick_S]

I am trying to combine offset either with another offset or with sum/index or sum/take. I could easily do this on one row, but I'd like to keep everything to a single row. Basically I have a financial model with months across the top 1-72 (six years) and various expenditure lines going down the page - salaries, bonuses, payroll taxes, etc. I am using offset to create a separate cash flow pattern for the various expenses where an offset of 0 months means the expense is paid in the month, 1 means one month earlier, -1 means 1 month later etc (I have also incorporated some failsafe checks to make sure you can't specify paying anything before the model starts. So far so good and that all works nicely for a regular monthly expense like salaries. Whilst the bonuses are accrued every month I would like to add an additional formula that can specify that the bonus rolls up until the end of a quarter or year (or whatever period I specify). Again it is easy enough to create a new row with formulae in to place the accrued / cumulative totals for a period at the end of that relevant period. However, I'd like to be able to then offset that total bonus so that it is paid 1 month after the period end (or whatever - perhaps even in advance of the period end).

For example I have a formula like this: =SUM(TAKE($L10:N10,,-N$4)) where row 4 contains the chronological month for a period (lets say it's a quarter - so month 3 is the end of the quarter) and row 10 contains the bonuses accrued for the month. I plan on having an IF formula that can be used to determine the month end so I will only end up seeing the total cumulative bonuses to the end of the quarter. I Would ideally have then put an OFFSET formula around the SUM(TAKE.....) so that I could shift when the payment is made forward or backwards. Unfortunately OFFSET doesn't seem to like having another formulae included in the same cell. So I can't see a way to achieve what I want without having 2 rows (and doubling the size of my model and making it a lot less elegant).

Does anyone have any suggestions please? I've thought about MATCH/INDEX and INDIRECT, but so far not terribly inspired.

Thank you!

 

[StephenCrump]

It's hard to visualise what you're after based on just a description - a sample layout and required results would be helpful.

But I think you're simply trying to sum a row from some start value to some end value? I've used 5 and 7 to illustrate, but presumably these will be formula driven.

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Month	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20
2
3	Amount	15	45	5	5	10	15	5	40	20	30	20	35	30	5	15	50	10	10	40	50
4
5	Sum from	5
6	to	7
7
8	Results	10	15	5
9		30
Sheet1

Cell Formulas
Range	Formula
B1:U1	B1	=SEQUENCE(,20)
B8:D8	B8	=INDEX(B3:U3,B5):INDEX(B3:U3,B6)
B9	B9	=SUM(INDEX(B3:U3,B5):INDEX(B3:U3,B6))
Dynamic array formulas.

 

[Nick_S]

It's hard to visualise what you're after based on just a description - a sample layout and required results would be helpful.

But I think you're simply trying to sum a row from some start value to some end value? I've used 5 and 7 to illustrate, but presumably these will be formula driven.

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Month	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20
2
3	Amount	15	45	5	5	10	15	5	40	20	30	20	35	30	5	15	50	10	10	40	50
4
5	Sum from	5
6	to	7
7
8	Results	10	15	5
9		30
Sheet1

Cell Formulas
Range	Formula
B1:U1	B1	=SEQUENCE(,20)
B8:D8	B8	=INDEX(B3:U3,B5):INDEX(B3:U3,B6)
B9	B9	=SUM(INDEX(B3:U3,B5):INDEX(B3:U3,B6))
Dynamic array formulas.

Hi Stephen - thanks for your response and apologies (I wanted to include a screen grab but the tiny PNG image I had was apparently too big to upload). I only just discovered SEQUENCE after posting so thanks for using that. It's not quite what I needed as OFFSET was fairly critical to the whole thing - OFFSET would be applied to the whole sequence of months (I've got a 72 month maximum timeframe) to determine the cashflow of the rows expensed in a particular month - so it would be the whole of your row 3 either one month later or one month earlier (you can specify - it could be 12 months earlier). I wanted to nest a second set of equations that would essentially group months together to represent billing cycles that might be monthly, quarterly or even annually. So each line of expense would have a label to the left of it eg "M3P-1" and the first calculation would sum three month periods and the offset would then place the cash out figure in the correct column. In your example months 4, 5 and 6 add up to 30 and if the payment is made at minus one (month 3 / column D) I would have 30 in a row in column D, 60 in column F etc. I had an additional check to deal with the start of the model (as OFSET wouldn't work earlier than month 1) so that in month 1 you make any initial 'prepayments' (with payment in arrears it is all fine) hence there is a payment of 65 in month 1. In the end I have just added extra rows - one row calculates the periodicity and another row does the OFFSET. It just makes the spreadsheets a little bigger. I have another set of rows where I show the balance sheet position (prepayment / accrual or potentially zero if the payment is made in the month). If I can figure out posting a screen grab I'll put up an image.

Thanks for your help!

Nick

 

[StephenCrump]

The best way to post a screenshot is using the XL2BB add-in.

Then we can simply copy your layout for testing.