VBA Beginer -Real Estate Industry

[Big Boss]

Hi,
I am beginner in VBA,
I am currently working in the assets management and investment department for a mega project to develop public assets (Museums, Parks, Venues…etc.), and several real estate assets classes (Residential, Retail, Offices, and Hospitality, healthcare, and education).
Currently, we are working on developing a consolidated financial model for all the assets within the park and one of the outcomes of this model is to provide us with the potential lands value per plot (parcel), based on a discounted project cashflow.
One of the key outcomes of this model is to provide us with a prefeasibility and a potential land value (sqm price) that can be used as a threshold before going into a partnership with investors (Jv, SPV, …) so that we can have an estimate of our share (land-in-kind) on any JV.
Since the model needs to be updated frequently, what are the best-VBA-practices that can facilitate and help us in the process of updating the key assumptions (E.g., CAPEX costs per sqm for construction, design, land scaping, infra structure, ...etc.).?
I know this is seems very general, however, I am expecting at this stage to receive ideas and thoughts from those working in the industry and who might had faced such a challenge (updating the model).
This will help me a lot in approaching this challenge and establishing necessary requirements and data bases to activate relevant MACROS moving forward.
What I define as success here is when we reach a point where whenever we open the model and run one macro, all model outcomes are based on most recent assumptions.

Thank you for your time.

[DocAElstein]

Hi Big Boss
Welcome to ExcelFox
You are very welcome to post these general questions here, but you may not get a large audience just now, as just me and a few others pass here by from time to time. ( My interest in Excel is purely as an Excel hobbyist, answering questions, mostly writing VBA, for fun, so I have no computer or software related work experience and therefore have little experience of actual applications).

Feel free to post in parallel to other forums where you may reach a larger number of professionals with the experience to help you.
Some other forums may have an extra sub forum for off topic or general questions or general discussions.

Here are some example forums that I know about: These general discussion sub forums are at two of the main Excel forums:
https://www.mrexcel.com/board/forums...plications.22/
https://www.excelforum.com/the-water-cooler/

Note that most forums have a “cross posting rule”. Basically it means they are happy for you to post your questions in other forums as long as you tell them , preferably with a URL link, of the other forums where you posted the same question.

Alan

---

P.s.
You may experience problems posting a link at some forums as a new member because anti spam measures often block links from new members. At some forums you can fool that by disguising the link somehow.
One example I know about: For example the link you would need to advise people of your post here at excel fox would be

https://excelfox.com/forum/showthread.php/2763-VBA-Beginer-Real-Estate-Industry

If that gives you problems when you try to post it, and if the forum supports BB code tags, which many do, then you could try to post this instead

h[color=Black]t[/color]tp[color=Black]s[/color]:/[color=Black]/[/color]excelfox[color=Black].c[/color]om/forum/showthread.php/2763-VBA-Beginer-Real-Estate-Industry

What you are doing there is using the BB code tags to color some characters black. By default all characters are usually black anyway, so it wont make any difference to what is finally posted. You should just get this posted, as before:

https://excelfox.com/forum/showthread.php/2763-VBA-Beginer-Real-Estate-Industry

But you will have fooled the software that looks for URL links, as you have messed up the character combination bits that are checked for: It appears to do the checks on what you try to post and it does not usually check what actually appears in the final post

[Naveedraj]

Hi,
It's great to hear that you're diving into VBA for your work in asset management and investment, especially with such a significant focus on developing public assets and real estate projects. Given your goal of creating a consolidated financial model that can be easily updated, here are some best practices that can facilitate the process of managing key assumptions, such as CAPEX costs per square meter for construction and other related expenses.

1. Use Named Ranges for Key Assumptions
Implementing named ranges in your Excel model can significantly simplify the process of updating key assumptions. By assigning names to critical cells that contain variables like CAPEX costs, you can easily reference these names in your VBA code. When you need to update a value, you can do so in one location, and all formulas and macros that reference that named range will automatically reflect the change.

2. Create a Dedicated Assumptions Sheet
Designate a specific worksheet for all your key assumptions. This sheet should contain all relevant data, including CAPEX costs, operational expenses, and other financial metrics. By centralizing this information, you can streamline updates and ensure that your model pulls the most current data. Your VBA macros can be programmed to read from this assumptions sheet, making it easier to refresh the model with the latest figures.

3. Automate Updates with Macros
Develop VBA macros that automate the process of updating your model. For instance, you can create a macro that runs whenever you open the workbook, which pulls the latest data from your assumptions sheet and recalculates all dependent formulas. This way, you only need to run one macro to ensure that all model outcomes are based on the most recent assumptions.

4. Implement Error Handling
When working with VBA, it's crucial to implement error handling to manage any potential issues that may arise during the execution of your macros. This includes checking for missing data or incorrect formats in your assumptions. By anticipating and managing errors, you can ensure that your model runs smoothly and provides accurate outputs.

5. Document Your Code
As you develop your VBA macros, make sure to document your code thoroughly. Include comments that explain the purpose of each section of code, especially if it involves complex calculations or logic. This practice will not only help you understand your own work when revisiting it later but will also assist any colleagues who may need to work with your model in the future.

6. Regularly Review and Optimize
Finally, regularly review your financial model and VBA code for opportunities to optimize performance. As your model grows in complexity, it may become slower or more cumbersome to update. Look for ways to streamline calculations, reduce the number of unnecessary calculations, and ensure that your macros are efficient.
By following these practices, you can create a robust and flexible financial model that allows for easy updates and accurate forecasting. Projects like Capital Smart City exemplify the type of developments where such models can provide valuable insights into potential land values and investment opportunities.

Good luck with your project, and I hope these tips help you streamline your financial modelling process!

Thank you for your time.

[kirin999]

Hi,

For keeping your model updated, you might want to:

Centralize Assumptions: Put all key numbers in one sheet for easy updates.
Use Named Ranges: Makes your VBA code cleaner.
Automate Imports: Set up macros to pull in the latest data automatically.
Create a Master Macro: Have one macro to update everything in your model.
Add Error Handling: Include checks to manage any issues that come up.

These steps should make updating your model a lot easier.