• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Advice on Improvement for Automated Rent Concession workbook

Royushken

New Member
Hi All,

First, I'd like to thank you for taking the time to read this and your help regarding this matter. I've attached an excel workbook that is meant to be a nearly fully automated rent concession template able to be used to quickly amortize rent up to a 20 year period of time under various conditions. I was hoping for suggestions on ways I could improve it, or any issues anybody may be able to find, regarding the layout, formula calculations, or any other limitation or improvement you can think of or suggest.

This is my first post on this website, although I have used Chandoo.org extensively to enhance my excel skills.

For some background on me: I am an accountant at a large corporation. I love to problem solve using excel and try to optimize/automate as much of my job or anyone else's that I can. I've been developing my excel skills for about 2 years now.

Thank you very much in advance for your assistance.

Best,

Ian
 

Attachments

vletm

Excel Ninja
Royushken
Okay, I try to figure something ... but I would like to get some answers?
1) Rows above 48, Could those be in other sheet?
2) Could You please explain in one short sentence below ...? ( = What are You looking for?)
Screenshot 2019-06-28 at 18.08.35.png
3) Why there are many times same kind of sections (eg 54.. 100 row in 'Rent Concession'-sheet)?
4) 'Summary'-sheet: Have You ever used Pivot-tables?
 

Royushken

New Member
Vletm,

Thank you for checking it out and for your response. To answer your questions:

1) Rows above 48, Could those be in other sheet? They could be, but one of my goals with this workbook was to retain the same format as it was originally designed, before I automated it. Everything in this was entirely manual before. I wanted to keep it the same as close as possible but as close to fully automatic as possible.


2) Could You please explain in one short sentence below ...? ( = What are You looking for?)
View attachment 61040


Unfortunately I cannot explain this in one short sentence, but I will attempt to clarify the desired result of this formula as briefly as possible. I am sure there is a better way to do this, but I did not know how. The orange input cells dictate a range of possible scenarios of lease amortizations: lease length, specific month of the year the rent increases, annual percentage increase to the lease, and how frequently the rent increases (I.e every other year or every year or every 3rd year, up to 5).

  1. This formula has to subtract any value inserted in column H, same row.
  2. Has to calculate rent properly based on the parameters I stated above. The ones that are input into the orange cells. This is what the data set in T1:AH47 is for.
  3. Has to appear blank if the lease has not begun or has ended in that month and any previous or subsequent months.
3) Why there are many times same kind of sections (eg 54.. 100 row in 'Rent Concession'-sheet)? The section in 'Rent Concession'-sheet repeats itself because this entire workbook is basically an amortization of a lease. Each year has a section, which you referred to, and I wanted this workbook to be capable of producing up to a 20 year lease automatically, hence the repeated sections (note the year is different in each section).


4) 'Summary'-sheet: Have You ever used Pivot-tables? I have used Pivot-tables extensively, but I'm not sure I could have achieved the format and function of the summary tab, as it appears right now, using a pivot table. As stated in my answer to quesiton 1, I did not design the base format of this workbook and my goal was to retain that format.
 

vletm

Excel Ninja
Royushken
Your thread starts with words Advice on Improvement...
... and You wrote many times like I wanted to keep it the same as close as possible but as close to fully automatic as possible.
Which one would You like to follow?

1) ... as above and 'how to use it?' ... from hidden rows?
2) Your reply was longer than that which has many copies ... hmm?
Have You manually verified that it works?
How to modify it if needs to add something? ... or could some one do it (with or without Your explain)?
... for me, if something like that ... it should do other ways. - sorry.
3) Can someone see all those 20 years in one view?
If 'yes', even then ... You need to modify every of those every time...
If 'no' then ... select year ... show it.
4) as #1, #2, #3 ... do You want to keep everything same or change something?
Have You thought to use eg VBA? 'do once' and 'loop it ... 20 ... or as many times as needed' ... if really needs.

Hmm? ... maybe would need to change something?
 

Royushken

New Member
Vletm,

Thank you for you very much for your input. I did ask for advice on improvement, and in my answer to you I clarified that I was looking to retain the format. So to be crystal clear, I cannot change the base format of the output and how it is viewed on the two sheets, with the exception of what you noted about viewing the 20 years. That is a good point, and I'll address that below.

1) ... as above and 'how to use it?' ... from hidden rows? I'm not sure what you are asking here.

2) Your reply was longer than that which has many copies ... hmm?
Have You manually verified that it works?
How to modify it if needs to add something? ... or could some one do it (with or without Your explain)? I have manually verified the formula functions under most possible lease scenarios. There are exceptional scenarios, one of which I encountered recently, where it didn't work. All I had to do though was input the numbers in column F manually and everything else populated correctly in both sheets.


3) Can someone see all those 20 years in one view?
If 'yes', even then ... You need to modify every of those every time...
If 'no' then ... select year ... show it.

I'm not sure exactly what you mean here, but I think you bring up a good point. I don't think it is necessary to view all 20 years at once and like you said just select the year and have that data populate would definitely have been easier to build, and maybe also more user friendly. Thanks for the suggestion.

4) as #1, #2, #3 ... do You want to keep everything same or change something?
Have You thought to use eg VBA? 'do once' and 'loop it ... 20 ... or as many times as needed' ... if really needs. I don't have much experience with VBA. I am not really able to write code for VBA like I am able to write formulas and problem solve using other excel functionality. I am open to suggestions if you have any.

Thanks again, Vletm.
 

vletm

Excel Ninja
1) You have hidden rows above row 48. There are some manually filled values.

2) You have ... but can someone else? How to modify?
Would be useful if split in logical parts. Possible to track unwanted feature.
... and eg row 59-'formulas' needs only once, if would use VBA.

3) There are 20 years values. What kind of monitor or printout needs that can use all of those in one time?
You maybe get some kind of idea ... or how?
What for are those values? There are numbers ... numbers. Could any chart show something?
Do someone would like to see ... what has happened? or what would happen? ... compare?

4) Some samples of VBA:
Cell F59 ... You have one l..g formula - okay?
You can write 'formula' to code-page and get to F59 only value (not formula)
same 'formula' from code can use with next cell F60 and get value there ... and so on.
If 'formula' needs to modify, then change 'formula' only to the code ... and that's it. ( No need to copy paste ... copy paste ... )
As well as .. if someone would clear by 'mistake' eg cell i79-formula.
How many days would it take that someone would notice that? That value would keep same same.
> Many things are possible,
but before eg any VBA, the layout should be crystal clear!
as well as what really needs?
 
Top