EnjoyingExcel
New Member
- Joined
- Jul 27, 2013
- Messages
- 1
Hi all,
I would really appreciate your help with the following task: I need to create a model that prioritizes cities for a business to enter based on a number of different criteria and would like to hear everyone's thoughts on how to set this up.
I plan (unless someone suggests otherwise) on having two scores and plot them in a 2X2 matrix: one will refer to the attractiveness of the city. While my list of variables is not final it will include things like GDP, population, as well as other more specific to the business, i.e., consumption of specific product of the business, number of outlets where product is sold, etc. The other one will be related to the capabilities of the business and will aim to assess how prepared it is to succeed in that city.
Once I decide on the variables and get the data cleaned up in different tabs, I assume that I need to assign scores to each variable and then combine them and compare them. I think this much I understand, however, I am not sure how to go about the detailed set-up of this model. Some key questions are:
--What would be the best layout for the calculations?
--How to assign scores to variables, both in terms of methodology and specific formulas to use. For example, how do I assign a score to the GDPs of lets say 30 cities? Would 10 be the largest and 1 the smallest? What formula would be of help?
--How do I combine scores of individual variables by assigning them different relative weights? Let's say 5 variables have a 1-10 score, but I want to weigh each one differently.Do I simply multiply the score by a relative weight? How do I do that in a way that makes the model flexible? I need to be able to change the relative importance of a variable and have the whole model update automatically.
Any comments or past models that aim to to a similar thing would be greatly welcomed.
I would really appreciate your help with the following task: I need to create a model that prioritizes cities for a business to enter based on a number of different criteria and would like to hear everyone's thoughts on how to set this up.
I plan (unless someone suggests otherwise) on having two scores and plot them in a 2X2 matrix: one will refer to the attractiveness of the city. While my list of variables is not final it will include things like GDP, population, as well as other more specific to the business, i.e., consumption of specific product of the business, number of outlets where product is sold, etc. The other one will be related to the capabilities of the business and will aim to assess how prepared it is to succeed in that city.
Once I decide on the variables and get the data cleaned up in different tabs, I assume that I need to assign scores to each variable and then combine them and compare them. I think this much I understand, however, I am not sure how to go about the detailed set-up of this model. Some key questions are:
--What would be the best layout for the calculations?
--How to assign scores to variables, both in terms of methodology and specific formulas to use. For example, how do I assign a score to the GDPs of lets say 30 cities? Would 10 be the largest and 1 the smallest? What formula would be of help?
--How do I combine scores of individual variables by assigning them different relative weights? Let's say 5 variables have a 1-10 score, but I want to weigh each one differently.Do I simply multiply the score by a relative weight? How do I do that in a way that makes the model flexible? I need to be able to change the relative importance of a variable and have the whole model update automatically.
Any comments or past models that aim to to a similar thing would be greatly welcomed.
