Setting up a complicated model... and dealing with iterative summing?

[inno]

Dear all,


So I have to set up a somewhat complicated model. Maybe someone can help me think through how to do it best? I am afraid I am about to set this up all incorrectly and then regret it later... Here is an overview:


-- Sheet 1 has a list of unique buildings, 10000 of them. Each building has 10 estimates of its value at different points in the future.

-- Sheet 2 has estimates of % damage that each of those unique buildings can suffer from five different types of natural disasters, each with four scenarios

-- Sheet 3 is the computation engine - for each building it computes the 200 possible damage values (10 value estimates times 20 different kinds of damage)


Now what I want to do is to be able to say, for a subset of buildings (filtered dynamically, e.g. only those in a certain zipcode), what is the total value of damage under different scenarios?


I guess one way I could do it is to:

-- Use VBA to filter Sheet 1 by chosen conditions and come up with list of buildings

-- Create a computation sheet with 200 columns for each possible outcome

-- Use VBA to copy the list of assets into the computation sheet and then do the summing


But seems like that 200 column thing is a bad idea... What if I need to add a scenario?


Do you think there is a way that I can use iteration or data tables to calculate the damage estimate in Sheet 3 for each building and keep summing these estimates as I calculate them? Happy to use a little VBA...


If you have any ideas, please tell! Oh, and you would be helping protect a big city from climate change.

 

[NARAYANK991]

Hi ,


Can I put down some specifics which will help to clarify the whole concept ?


1. On Sheet1 , you have let us say column A , listing the 10,000 buildings. You also have 10 estimates for each building ; I assume you will have these 10 estimates in 10 columns . column B through column K. Since you say each building is unique , it means you will have the building IDs in column A


2. On Sheet2 , you say you want to provide each building with 5 different disasters , each disaster having 4 different scenarios ; does this mean you will have column A of Sheet2 also carrying the list of 10,000 buildings ? If so , how are you going to specify the 5 disasters and 4 scenarios for each disasters ? Across 20 columns , 5 sets of 4 columns each or 4 sets of 5 columns each ?


3. On Sheet3 , you wish to compute the 200 values for each building ; or is it that the computation will be done on demand , either for just one building , or for a subset of the 10,000 buildings ?


4. You say that the filter will be on Zip Code ; this means the Zip Code will have to be entered in one column in Sheet1 ; it may have to be copied on Sheet3 also.


5. Will the computation engine be all , or do you want a separate sheet to display the final results in dashboard form ?


I think this entire exercise will stretch Excel quite a bit. Access or any other database software might be better suited for your application.


Narayan

 

[Hui]

Inno


Firstly Welcome to the Chandoo.org Forums


Setup your model on Page 3 using place marker values for the buildings and damages


Then use a Data Table to iterate through the model replacing the Inputs and collating the Inputs and Outputs in a Table.


Then drop this all into a Pivot table


Have a read of:

http://chandoo.org/wp/2010/05/06/data-tables-monte-carlo-simulations-in-excel-a-comprehensive-guide/

and

http://chandoo.org/wp/2011/06/20/analyse-data-like-a-super-hero/

 

[inno]

@Narayan:


1. Indeed - and building ID in column A is the primary key


2. Yes, the same 10,000 buildings, and 5 sets of 4 columns each - I just wanted to keep the valuations separate from damage estimates


3. On Sheet 3 as it is right now, the computation would be done for a standalone building. The idea would be to cycle through all the buildings in a filtered subset, use Sheet 3 to compute values for each of them, and keep adding up values as the computation is done.


4. Sheet 1 will have a whole bunch of data about each building (in addition to value) that you can use to get the list of buildings that you want to run the model for. I was thinking you'd just generate the list of buildings (let's say 500 of them) and then just use that list throughout the model...


5. It'd be great to be able to display the results separately and play around with them... But I suppose doing that would require more VBA than I know what to do with?


Anyway, thanks a ton guys for your suggestions! I'd have loved to use something fancier than Excel, but I'm afraid I have to keep it somewhat manageable for all the other people who will be working with this.