Forecast in excel

[arshad1976]

Dear Mr. Excel

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

Regards


[TABLE="width: 2984"]
<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>[TR]
[TD]Shop[/TD]
[TD]Space[/TD]
[TD]Rent per sqm[/TD]
[TD]Total [/TD]
[TD]Grace Period in months[/TD]
[TD]Apr-17[/TD]
[TD]May-17[/TD]
[TD]Jun-17[/TD]
[TD]Jul-17[/TD]
[TD]Aug-17[/TD]
[TD]Sep-17[/TD]
[TD]Oct-17[/TD]
[TD]Nov-17[/TD]
[TD]Dec-17[/TD]
[TD]Total[/TD]
[TD]Jan-18[/TD]
[TD]Feb-18[/TD]
[TD]Mar-18[/TD]
[TD]Apr-18[/TD]
[TD]May-18[/TD]
[TD]Jun-18[/TD]
[TD]Jul-18[/TD]
[TD]Aug-18[/TD]
[TD]Sep-18[/TD]
[TD]Oct-18[/TD]
[TD]Nov-18[/TD]
[TD]Dec-18[/TD]
[TD]Total[/TD]
[TD]Jan-19[/TD]
[TD]Feb-19[/TD]
[TD]Mar-19[/TD]
[TD]Apr-19[/TD]
[TD]May-19[/TD]
[TD]Jun-19[/TD]
[TD]Jul-19[/TD]
[TD]Aug-19[/TD]
[TD]Sep-19[/TD]
[TD]Oct-19[/TD]
[TD]Nov-19[/TD]
[TD]Dec-19[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD] 1,000[/TD]
[TD]200[/TD]
[TD] 200,000[/TD]
[TD]3[/TD]
[TD="align: left"] 200,000[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: left"] 200,000[/TD]
[TD="align: left"] 200,000[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: left"] 200,000[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: left"] 400,000[/TD]
[TD="align: left"] 200,000[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: left"] 200,000[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD] 2,000[/TD]
[TD]300[/TD]
[TD] 600,000[/TD]
[TD]2[/TD]
[TD] [/TD]
[TD="align: left"] 600,000[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: left"] 600,000[/TD]
[TD="align: left"] 600,000[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: left"] 600,000[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: left"] 1,200,000[/TD]
[TD="align: left"] 600,000[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: left"] 600,000[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD] 1,500[/TD]
[TD]400[/TD]
[TD] 600,000[/TD]
[TD]3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: left"] 600,000[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: left"] 600,000[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: left"] 600,000[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: left"] 600,000[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: left"] 1,200,000[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: left"] 600,000[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: left"] 600,000[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD] 1,200[/TD]
[TD]150[/TD]
[TD] 180,000[/TD]
[TD]2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: left"] - [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: left"] - [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: left"] - [/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD] 1,300[/TD]
[TD]500[/TD]
[TD] 650,000[/TD]
[TD]6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: left"] - [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: left"] - [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: left"] - [/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD] 900[/TD]
[TD]350[/TD]
[TD] 315,000[/TD]
[TD]3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: left"] - [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: left"] - [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: left"] - [/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD] 100[/TD]
[TD]250[/TD]
[TD] 25,000[/TD]
[TD]4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: left"] - [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: left"] - [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: left"] - [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: left"]Total[/TD]
[TD="align: left"] 200,000[/TD]
[TD="align: left"] 600,000[/TD]
[TD="align: left"] - [/TD]
[TD="align: left"] 600,000[/TD]
[TD="align: left"] - [/TD]
[TD="align: left"] - [/TD]
[TD="align: left"] - [/TD]
[TD="align: left"] - [/TD]
[TD="align: left"] - [/TD]
[TD="align: left"] 1,400,000[/TD]
[TD="align: left"] 800,000[/TD]
[TD="align: left"] - [/TD]
[TD="align: left"] - [/TD]
[TD="align: left"] 600,000[/TD]
[TD="align: left"] - [/TD]
[TD="align: left"] - [/TD]
[TD="align: left"] 800,000[/TD]
[TD="align: left"] - [/TD]
[TD="align: left"] - [/TD]
[TD="align: left"] 600,000[/TD]
[TD="align: left"] - [/TD]
[TD="align: left"] - [/TD]
[TD="align: left"] 2,800,000[/TD]
[TD="align: left"] 800,000[/TD]
[TD="align: left"] - [/TD]
[TD="align: left"] - [/TD]
[TD="align: left"] 600,000[/TD]
[TD="align: left"] - [/TD]
[TD="align: left"] - [/TD]
[TD="align: left"] - [/TD]
[TD="align: left"] - [/TD]
[TD="align: left"] - [/TD]
[TD="align: left"] - [/TD]
[TD="align: left"] - [/TD]
[TD="align: left"] - [/TD]
[TD="align: left"] 1,400,000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: left"]Note:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="colspan: 9, align: left"]There are almost more then 1000 rows in this sheet and around five years plan but this is only sample[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="colspan: 16, align: left"]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[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="colspan: 10, align: left"]Now I am making it manually, like when the shop is occupied in April 2017 then I have to shift all year accordingly.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="colspan: 18, align: left"]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..[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD="colspan: 15, align: left"]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[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD="colspan: 15, align: left"]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.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



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.