Forecast in excel

[arshad1976]

Dear Mr. Excel

Can you please help me to prepare the data on excel sheet as per attachment.

Regards

Shop	Space	Rent per sqm	Total	Grace Period in months	Apr-17	May-17	Jun-17	Jul-17	Aug-17	Sep-17	Oct-17	Nov-17	Dec-17	Total	Jan-18	Feb-18	Mar-18	Apr-18	May-18	Jun-18	Jul-18	Aug-18	Sep-18	Oct-18	Nov-18	Dec-18	Total	Jan-19	Feb-19	Mar-19	Apr-19	May-19	Jun-19	Jul-19	Aug-19	Sep-19	Oct-19	Nov-19	Dec-19	Total
1	1,000	200	200,000	3	200,000									200,000	200,000						200,000						400,000	200,000												200,000
2	2,000	300	600,000	2		600,000								600,000	600,000						600,000						1,200,000	600,000												600,000
3	1,500	400	600,000	3				600,000						600,000				600,000						600,000			1,200,000				600,000									600,000
4	1,200	150	180,000	2										-													-													-
5	1,300	500	650,000	6										-													-													-
6	900	350	315,000	3										-													-													-
7	100	250	25,000	4										-													-													-
				Total	200,000	600,000	-	600,000	-	-	-	-	-	1,400,000	800,000	-	-	600,000	-	-	800,000	-	-	600,000	-	-	2,800,000	800,000	-	-	600,000	-	-	-	-	-	-	-	-	1,400,000
	Note:
1	There are almost more then 1000 rows in this sheet and around five years plan but this is only sample
2	I am forecasting that the shop number 1 will be occupied on April 2017, and we will receive rent on the same month & then next rent will be after 6 months plus grace period as in example
3	Now I am making it manually, like when the shop is occupied in April 2017 then I have to shift all year accordingly.
4	Or I am forecasting for May 2017 to rent the shop but it is not occopied on the same months and I have to shift it manually to next month and all the months I have to do it manually with 6 month plus grace period..
5	I want to make it by formula, means if I enter any number in any month then all the data should automatically be calculated in specific cell with specific interval up to end of all years
6	If for example shop # 3 didn’t occupied on the same month in which I forecasted the I have to shift it to next month means August 2017 and then manualy for all the years.

<colgroup><col span="4"><col><col span="2"><col><col><col span="5"><col><col><col><col><col><col span="2"><col><col><col><col><col span="2"><col><col><col><col><col><col span="8"><col></colgroup><tbody>
</tbody>

please help

 

[vbanoob0807]

Hi, this is quite difficult for me, but I would like to try, however,

why i see in the data that the 3rd rent payment onwards is 6 months after the 2nd payment,
instead of what you said 6 months + grace period.

if it's like what i see in the data, assuming formula starts in cell P2,

=IF(COUNT($F2:O2)<3,IF(OFFSET(P2,,-$E2-6-1)<>0,OFFSET(P2,,-$E2-6-1),""),IF(MONTH(P$1)<7,IF(OFFSET(P2,,-6-1)<>0,OFFSET(P2,,-6-1),""),IF(OFFSET(P2,,-6)<>0,OFFSET(P2,,-6),"")))

try shifting around the payments in the 2017 year range to see if it works for the rest of the years.

 

[vbanoob0807]

The above doesn't work, try this

{=IFNA(IF(COUNT($F2:O2)<3,IF(EDATE(INDEX($E$1:$N$1,1,MATCH(TRUE,$E2:$N2>99,0)),$E2+6)=P$1,INDEX($E$1:$N$8,ROWS($E$1:$E2),MATCH(TRUE,$E2:$N2>99,0)),""),IF(EDATE(INDEX($E$1:$N$1,1,MATCH(TRUE,$E2:$N2>99,0)),$E2+(COUNT($F2:O2)-(YEAR(P$1)-2017))*6)=P$1,INDEX($E$1:$N$8,ROWS($E$1:$E2),MATCH(TRUE,$E2:$N2>99,0)),"")),"")}

complete with shift+ctrl+enter and drag across and down.

 

[arshad1976]

The above doesn't work, try this



complete with shift+ctrl+enter and drag across and down.

Thanks vbanoob0807,

But it is not working, I don't know where is the error. Can anybody help me. Actually I had to do manual work and it takes too much time to shift the months.. If you want then i could send excel sheet to you.

 

[vbanoob0807]

hi, see if you can retrieve the file

https://www.dropbox.com/s/vff5g1vy21b8m5e/Rent Forecast.xlsx?dl=0

 

[arshad1976]

hi, see if you can retrieve the file

https://www.dropbox.com/s/vff5g1vy21b8m5e/Rent Forecast.xlsx?dl=0

Thanks.. I downloaded the file but when i am making any change in April 2017 or in any month the whole the cells with formula are showing #NAME ? error.

What to do? kindly advise..

 

[vbanoob0807]

can you give me a specific example of what you change the month header into, that shows the #NAME ? error?

 

[vbanoob0807]

Hi, it was discussed that ifna is used in Excel 2013,

https://stackoverflow.com/questions/43854096/excel-name-error-with-ifna

so i changed ifna to iferror.

https://www.dropbox.com/s/eg62krlfn24dgu6/Rent Forecast - 2nd Copy.xlsx?dl=0

Note, if the grace period is 2 months and rent payment starts in April-17, you still have to manually key in the figure in Dec-17 because formula starts in 2018 range onwards.