Spreading revenue over months

[sps21]

Hi there

New user here! I am building a financial model and want a quicker way to spread the revenue. I have a monthly P&L going for 5 years and I have to spread the monthly revenue across 12 months in a separate tab.


Source file:

	Jan 17	Feb 17	Mar 17	till	Dec 21
Monthly Revenue	X	Y	Z

<tbody>
</tbody>

which needs to expressed in following manner in a different tab

Jan17

Feb 17

Mar 17

Apr 17

May

Jun

Jul

Aug

so on

Dec17

Jan18

Feb18

Mar18

till Dec 21

Jan

X/12

X/12

X/12

X/12

Feb

Y/12

Y/12

Y/12

Y/12

Mar

Z/12

Z/12

Z/12

Z/12

till Dec

Total

<tbody>
</tbody>

I would really appreciate if any one can suggest a quicker way to do this..something like a formula which can be dragged across to pull in the value from the source.


Thanks in advance

 

[sheetspread]

If X,Y,Z are monthly why divide them by 12 again?

 

[sps21]

If X,Y,Z are monthly why divide them by 12 again?

Thanks for the reply! The thing is the previous monthly figure is form of a revenue contract of which the cash flow is realized over 12 month period.

Thanks

SPS

 

[sheetspread]

If the spaces in between are supposed to be filled, something like:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		1/31/2017	2/28/2017	3/31/2017	4/30/2017	5/31/2017	6/30/2017	7/31/2017	8/31/2017	9/30/2017	10/31/2017	11/30/2017	12/31/2017
2	Monthly Revenue	7545	4154	1202	7522	5320	2658	7502	2333	3842	7147	9241	8209
3
4
5		1/31/2017	2/28/2017	3/31/2017	4/30/2017	5/31/2017	6/30/2017	7/31/2017	8/31/2017	9/30/2017	10/31/2017	11/30/2017	12/31/2017
6	Jan	628.75	628.75	628.75	628.75	628.75	628.75	628.75	628.75	628.75	628.75	628.75	628.75
7	Feb		346.16667	346.16667	346.16667	346.16667	346.16667	346.16667	346.16667	346.16667	346.166667	346.166667	346.166667
8	Mar			100.16667	100.16667	100.16667	100.16667	100.16667	100.16667	100.16667	100.166667	100.166667	100.166667
9	Apr				626.83333	626.83333	626.83333	626.83333	626.83333	626.83333	626.833333	626.833333	626.833333
10	May					443.33333	443.33333	443.33333	443.33333	443.33333	443.333333	443.333333	443.333333
11	Jun						221.5	221.5	221.5	221.5	221.5	221.5	221.5
12	Jul							625.16667	625.16667	625.16667	625.166667	625.166667	625.166667
13	Aug								194.41667	194.41667	194.416667	194.416667	194.416667
14	Sep									320.16667	320.166667	320.166667	320.166667
15	Oct										595.583333	595.583333	595.583333
16	Nov											770.083333	770.083333
17	Dec												684.083333
Sheet11

Cell Formulas
Range	Formula
B6	=IF(AND(COLUMN()-ROW(A1)>=1,COLUMN()-ROW(A1)<=12),INDEX($B$2:$BI$2,ROW(A1))/12,"")

 

[sps21]

If the spaces in between are supposed to be filled, something like:

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L	M
1		1/31/2017	2/28/2017	3/31/2017	4/30/2017	5/31/2017	6/30/2017	7/31/2017	8/31/2017	9/30/2017	10/31/2017	11/30/2017	12/31/2017
2	Monthly Revenue	7545	4154	1202	7522	5320	2658	7502	2333	3842	7147	9241	8209
3
4
5		1/31/2017	2/28/2017	3/31/2017	4/30/2017	5/31/2017	6/30/2017	7/31/2017	8/31/2017	9/30/2017	10/31/2017	11/30/2017	12/31/2017
6	Jan	628.75	628.75	628.75	628.75	628.75	628.75	628.75	628.75	628.75	628.75	628.75	628.75
7	Feb		346.16667	346.16667	346.16667	346.16667	346.16667	346.16667	346.16667	346.16667	346.166667	346.166667	346.166667
8	Mar			100.16667	100.16667	100.16667	100.16667	100.16667	100.16667	100.16667	100.166667	100.166667	100.166667
9	Apr				626.83333	626.83333	626.83333	626.83333	626.83333	626.83333	626.833333	626.833333	626.833333
10	May					443.33333	443.33333	443.33333	443.33333	443.33333	443.333333	443.333333	443.333333
11	Jun						221.5	221.5	221.5	221.5	221.5	221.5	221.5
12	Jul							625.16667	625.16667	625.16667	625.166667	625.166667	625.166667
13	Aug								194.41667	194.41667	194.416667	194.416667	194.416667
14	Sep									320.16667	320.166667	320.166667	320.166667
15	Oct										595.583333	595.583333	595.583333
16	Nov											770.083333	770.083333
17	Dec												684.083333

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet11​

Worksheet Formulas Cell Formula B6 =IF(AND(COLUMN()-ROW(A1)>=1,COLUMN()-ROW(A1)<=12),INDEX($B$2:$BI$2,ROW(A1))/12,"") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Hi Spreadsheet

Thanks for your reply! It works! However could you explain your thought process behind AND(COLUMN()-ROW(A1)>=1,COLUMN()-ROW(A1)<=12). This would help me devise my own formula for situations like these in future. If you don't feel like typing a big reply to question then ignore! I am grateful for your help

Thanks again and have a good day!

SPS

 

[sheetspread]

You're only trying to return 12 cells per row, starting at the first column for the first row, second column for the second row, etc. Row(A1) will change as you copy down (from 1 to 2 to 3.......) Likewise Column() in B6 (the same as typing Column(B6)) will start at 2 and change to 3, 4... as you copy across. So test whether the difference falls between 1 and 12 inclusive, if it does, you return the revenue/12, otherwise a "" (fake blank). One good way to understand multi-function formulas is by examining them in pieces, i.e. seeing the results of each component.

 

[sps21]

You're only trying to return 12 cells per row, starting at the first column for the first row, second column for the second row, etc. Row(A1) will change as you copy down (from 1 to 2 to 3.......) Likewise Column() in B6 (the same as typing Column(B6)) will start at 2 and change to 3, 4... as you copy across. So test whether the difference falls between 1 and 12 inclusive, if it does, you return the revenue/12, otherwise a "" (fake blank). One good way to understand multi-function formulas is by examining them in pieces, i.e. seeing the results of each component.

Got it! Thanks a lot

SPS