for owens

profilekeyashden
country_attractiveness_spreadsheet.xlsx
Home>Business & Finance homework help>Marketing homework help>for owens

Instructions

The Country Attractiveness spreadsheet consists of 4 main parts, each of which has its own sheet / tab located at the bottom of this sheet:
1) Latin America Subjective Rating (for use with the Latin America Scenario only)
First consider some of the factors that make a market or country more attractive. We've grouped them into four general categories: Demand, Supply, Competition, and Other.
However, you may prefer a different way to organize them. Note that you don't have to have 5 factors for each category. All that matters is that the factors you choose are ones
that you believe are important differences in the attractiveness of the market / country. The spreadsheet will automatically calculate the overall attractiveness of each
country based on the weight that you've entered for the factor and the rating of the factor on a scale of 1-10. Weights should total 100% when done.
- In short, your job is to decide on the factors, the weights (importance), and the assessment for each country on each factor chosen.
2) Asia Subjective Rating (for use with the Asia Scenario only)
Same as above but with Asian countries.
3) M2 Raw Data Formula Conversion
This sheet provides a statistical methodology for converting data from the simulation into ratings. It also provides sample calculations for 3 factors for both Latin America
and Asia. This is only a one possible approach however. If you would like to use this methology to calculate ratings, you will need to expand the sheet to address other factors.
4) M3 Raw Data VLOOKUP Conversion
This sheet provides a second methodology for converting data from the simulation into ratings using straight - line interpolation. It also provides sample calculations for 3 factors for both
Latin America and Asia. This is only a one possible approach however. If you would like to use this methology to calculate ratings, you will need to expand the sheet to address other factors.

Latin America Subjective Rating

Country Attractiveness Analysis for use with the Latin America Scenario
Argentina Brazil Chile Colombia Mexico Peru Venezuela
Importance Weight Rating (1-10) Assessment (Calculated) Rating (1-10) Assessment (Calculated) Rating (1-10) Assessment (Calculated) Rating (1-10) Assessment (Calculated) Rating (1-10) Assessment (Calculated) Rating (1-10) Assessment (Calculated) Rating (1-10) Assessment (Calculated)
Demand Criteria
1) 0% 0 0 0 0 0 0 0
2) 0% 0 0 0 0 0 0 0
3) 0% 0 0 0 0 0 0 0
4) 0% 0 0 0 0 0 0 0
5) 0% 0 0 0 0 0 0 0
Supply Criteria
6) 0% 0 0 0 0 0 0 0
7) 0% 0 0 0 0 0 0 0
8) 0% 0 0 0 0 0 0 0
9) 0% 0 0 0 0 0 0 0
10) 0% 0 0 0 0 0 0 0
Competition Criteria
11) 0% 0 0 0 0 0 0 0
12) 0% 0 0 0 0 0 0 0
13) 0% 0 0 0 0 0 0 0
14) 0% 0 0 0 0 0 0 0
15) 0% 0 0 0 0 0 0 0
Other Criteria
16) 0% 0 0 0 0 0 0 0
17) 0% 0 0 0 0 0 0 0
18) 0% 0 0 0 0 0 0 0
19) 0% 0 0 0 0 0 0 0
20) 0% 0 0 0 0 0 0 0
TOTAL 0% 0 0 0 0 0 0 0

Asia Subjective Rating

Country Attractiveness Analysis for use with the Asia Scenario
China Japan India S. Korea Philippines Thailand
Importance Weight Rating (1-10) Assessment (Calculated) Rating (1-10) Assessment (Calculated) Rating (1-10) Assessment (Calculated) Rating (1-10) Assessment (Calculated) Rating (1-10) Assessment (Calculated) Rating (1-10) Assessment (Calculated)
Demand Criteria
1) 0% 0 0 0 0 0 0
2) 0% 0 0 0 0 0 0
3) 0% 0 0 0 0 0 0
4) 0% 0 0 0 0 0 0
5) 0% 0 0 0 0 0 0
Supply Criteria
6) 0% 0 0 0 0 0 0
7) 0% 0 0 0 0 0 0
8) 0% 0 0 0 0 0 0
9) 0% 0 0 0 0 0 0
10) 0% 0 0 0 0 0 0
Competition Criteria
11) 0% 0 0 0 0 0 0
12) 0% 0 0 0 0 0 0
13) 0% 0 0 0 0 0 0
14) 0% 0 0 0 0 0 0
15) 0% 0 0 0 0 0 0
Other Criteria
16) 0% 0 0 0 0 0 0
17) 0% 0 0 0 0 0 0
18) 0% 0 0 0 0 0 0
19) 0% 0 0 0 0 0 0
20) 0% 0 0 0 0 0 0
TOTAL 0% 0 0 0 0 0 0

M2 Raw Data Formula Conversion

Country Attractiveness Analysis Using Raw Data, Standardized
For use with the Latin America Scenario
Criteria Population GDP/Capita ($) - Higher is More Attractive GDP/Capita ($) - Lower is More Attractive Total Assessment
Weight 15% 10% 10%
Raw Data Standardized Rating Assessment Raw Data Standardized Rating Assessment Raw Data Standardized Rating Assessment
Argentina 43.0 -0.40 2.2
IMBA: This formula converts the standardized score into a 1-10 rating by adding the standardized value to the absolute value of the lowest score, dividing by the range, then multiplying by 9 (10-point scale - 1). Because this yields values ranging from 0-9, the final step is adding 1, making all values fall between 1-10. 		0.3 17900.0 1.10 8.4 0.8 17900.0 1.1 2.6
IMBA: This formula converts the standardized score into a 1-10 rating by adding the negative standardized value to the absolute value of the highest score, dividing by the range, then multiplying by 9 (10-point scale - 1). Because this yields values ranging from 0-9, the final step is adding 1, making all values fall between 1-10. 		0.3 1.4
Brazil 202.7 1.96 10.0 1.5 11900.0 -0.76 1.6 0.2 11900.0 -0.8 9.4 0.9 2.6
Chile 17.3 -0.78 1.0 0.2 19300.0 1.53 10.0 1.0 19300.0 1.5 1.0 0.1 1.3
Colombia 46.2 -0.35 2.4 0.4 11400.0 -0.91 1.0 0.1 11400.0 -0.9 10.0 1.0 1.5
Mexico 120.3 0.75 6.0 0.9 15300.0 0.29 5.4 0.5 15300.0 0.3 5.6 0.6 2.0
Peru 30.1 -0.59 1.6 0.2 11400.0 -0.91 1.0 0.1 11400.0 -0.9 10.0 1.0 1.3
Venezuela 28.9 -0.60 1.6 0.2 13246.3 -0.34 3.1 0.3 13246.3 -0.3 7.9 0.8 1.3
Statistics
Mean 69.8 14349.5 14349.5
SD 67.7 3230.9 3230.9
Max 2.0 1.5 1.5
Min -0.8 -0.9 -0.9
Range 2.7 2.4 2.4
For use with the Asia Scenario
Criteria Population GDP/Capita ($) - Higher is More Attractive GDP/Capita ($) - Lower is More Attractive Total Assessment
Weight 15% 10% 10%
Raw Data Standardized Rating Assessment Raw Data Standardized Rating Assessment Raw Data Standardized Rating Assessment
China 1337.0 1.41 10.0
IMBA: This formula converts the standardized score into a 1-10 rating by adding the standardized value to the absolute value of the lowest score, dividing by the range, then multiplying by 9 (10-point scale - 1). Because this yields values ranging from 0-9, the final step is adding 1, making all values fall between 1-10. 		1.5 7400.0 -0.52 2.2 0.2 7400.0 -0.5 8.8
IMBA: This formula converts the standardized score into a 1-10 rating by adding the negative standardized value to the absolute value of the highest score, dividing by the range, then multiplying by 9 (10-point scale - 1). Because this yields values ranging from 0-9, the final step is adding 1, making all values fall between 1-10.
IMBA: This formula converts the standardized score into a 1-10 rating by adding the standardized value to the absolute value of the lowest score, dividing by the range, then multiplying by 9 (10-point scale - 1). Because this yields values ranging from 0-9, the final step is adding 1, making all values fall between 1-10.
IMBA: This formula converts the standardized score into a 1-10 rating by adding the standardized value to the absolute value of the lowest score, dividing by the range, then multiplying by 9 (10-point scale - 1). Because this yields values ranging from 0-9, the final step is adding 1, making all values fall between 1-10. 		0.9 2.6
Japan 126.4 -0.58 1.5 0.2 34300.0 1.43 10.0 1.0 34300.0 1.4 1.0 0.1 1.3
India 1189.0 1.16 9.0 1.3 3400.0 -0.80 1.0 0.1 3400.0 -0.8 10.0 1.0 2.4
S. Korea 48.8 -0.70 1.0 0.2 29900.0 1.11 8.7 0.9 29900.0 1.1 2.3 0.2 1.3
Philippines 101.8 -0.62 1.4 0.2 3400.0 -0.80 1.0 0.1 3400.0 -0.8 10.0 1.0 1.3
Thailand 66.7 -0.67 1.1 0.2 8800.0 -0.41 2.6 0.3 8800.0 -0.4 8.4 0.8 1.3
Statistics
Mean 478.3 14533.3 14533.3
SD 610.2 13845.7 13845.7
Max 1.4 1.4 1.4
Min -0.7 -0.8 -0.8
Range 2.1 2.2 2.2

M3 Raw Data VLOOKUP Conversion

Country Attractiveness Analysis Using Raw Data, Standardized, Scaled Using VLOOKUP
For use with the Latin America Scenario
Criteria Population GDP/Capita ($) - Higher More Attractive GDP/Capita ($) - Lower More Attractive Total Assessment
Weight 15% 10% 10%
Raw Data Standardized Rating Assessment Raw Data Standardized Rating Assessment Raw Data Standardized Rating Assessment
Argentina 43.0 -0.40 2.0
IMBA: VLOOKUP function looks at the standardized score in D6 (-0.41) and searches the array D22-E31 to determine the value to place in cell E6. It finds the closest value in D22-D31 (rounding down), which is -0.48 in cell D23. It then finds the value in the same row, next column, which is 2.0 in cell E23. The rating placed in cell E6 is thus 2.0.		 0.3 17900.0 1.10 8.0 0.8 17900.0 1.10 3.0 0.3 1.4
Brazil 202.7 1.96 9.0 1.4 11900.0 -0.76 1.0 0.1 11900.0 -0.76 10.0 1.0 2.5
Chile 17.3 -0.78 1.0 0.2 19300.0 1.53 10.0 1.0 19300.0 1.53 1.0 0.1 1.3
Colombia 46.2 -0.35 2.0 0.3 11400.0 -0.91 1.0 0.1 11400.0 -0.91 10.0 1.0 1.4
Mexico 120.3 0.75 6.0 0.9 15300.0 0.29 5.0 0.5 15300.0 0.29 6.0 0.6 2.0
Peru 30.1 -0.59 1.0 0.2 11400.0 -0.91 1.0 0.1 11400.0 -0.91 10.0 1.0 1.3
Venezuela 28.9 -0.60 1.0 0.2 13246.3 -0.34 3.0 0.3 13246.3 -0.34 8.0 0.8 1.3
Statistics
Mean 69.8 14349.5 14349.5
SD 67.7 3230.9 3230.9
Max 1.96 1.53 1.53
Min -0.78 -0.91 -0.91
Range 2.74 2.45 2.45
Interval (1-10) 0.30 0.27 0.27
Ranked Values
1 (Min) -0.78
IMBA: The minimum standardized value = -0.76 (cell C17). Add the interval value (0.28, cell C19) to get the next rank-order value (-0.76 + 0.28 = -0.48). Follow same procedure until you have 10 values; the 10th value will equal the maximum standardized value. 		1.0 -0.91 1.0 (Max) -0.91 10.0
IMBA: The rating order has been reversed in M22-M31 so that lower scores are rated higher, and higher scores are rated lower.
2 -0.47 2.0 -0.64 2.0 -0.64 9.0
3 -0.17 3.0 -0.37 3.0 -0.37 8.0
4 0.14 4.0 -0.10 4.0 -0.10 7.0
5 0.44 5.0 0.17 5.0 0.17 6.0
6 0.75 6.0 0.45 6.0 0.45 5.0
7 1.05 7.0 0.72 7.0 0.72 4.0
8 1.35 8.0 0.99 8.0 0.99 3.0
9 1.66 9.0 1.26 9.0 1.26 2.0
10 (Max) 1.96 10.0 1.53 10.0 (Min) 1.53 1.0
For use with the Asia Scenario
Criteria Population GDP/Capita ($) - Higher More Attractive GDP/Capita ($) - Lower More Attractive Total Assessment
Weight 15% 10% 10%
Raw Data Standardized Rating Assessment Raw Data Standardized Rating Assessment Raw Data Standardized Rating Assessment
China 1337.0 1.41 10.0
IMBA: VLOOKUP function looks at the standardized score in D6 (-0.41) and searches the array D22-E31 to determine the value to place in cell E6. It finds the closest value in D22-D31 (rounding down), which is -0.48 in cell D23. It then finds the value in the same row, next column, which is 2.0 in cell E23. The rating placed in cell E6 is thus 2.0.		 1.5 7400.0 -0.52 2.0 0.2 7400.0 -0.52 9.0 0.9 2.6
Japan 126.4 -0.58 1.0 0.2 34300.0 1.43 10.0 1.0 34300.0 1.43 1.0 0.1 1.3
India 1189.0 1.16 8.0 1.2 3400.0 -0.80 1.0 0.1 3400.0 -0.80 10.0 1.0 2.3
S. Korea 48.8 -0.70 1.0 0.2 29900.0 1.11 8.0 0.8 29900.0 1.11 3.0 0.3 1.3
Philippines 101.8 -0.62 1.0 0.2 3400.0 -0.80 1.0 0.1 3400.0 -0.80 10.0 1.0 1.3
Thailand 66.7 -0.67 1.0 0.2 8800.0 -0.41 2.0 0.2 8800.0 -0.41 9.0 0.9 1.3
Statistics
Mean 478.3 14533.3 14533.3
SD 610.2 13845.7 13845.7
Max 1.41 1.43 1.43
Min -0.70 -0.80 -0.80
Range 2.11 2.23 2.23
Interval (1-10) 0.23 0.25 0.25
Ranked Values
1 (Min) -0.70
IMBA: The minimum standardized value = -0.76 (cell C17). Add the interval value (0.28, cell C19) to get the next rank-order value (-0.76 + 0.28 = -0.48). Follow same procedure until you have 10 values; the 10th value will equal the maximum standardized value. 		1.0 -0.80 1.0 (Max) -0.80 10.0
IMBA: The rating order has been reversed in M22-M31 so that lower scores are rated higher, and higher scores are rated lower.

IMBA: VLOOKUP function looks at the standardized score in D6 (-0.41) and searches the array D22-E31 to determine the value to place in cell E6. It finds the closest value in D22-D31 (rounding down), which is -0.48 in cell D23. It then finds the value in the same row, next column, which is 2.0 in cell E23. The rating placed in cell E6 is thus 2.0.
IMBA: The minimum standardized value = -0.76 (cell C17). Add the interval value (0.28, cell C19) to get the next rank-order value (-0.76 + 0.28 = -0.48). Follow same procedure until you have 10 values; the 10th value will equal the maximum standardized value.
IMBA: The minimum standardized value = -0.76 (cell C17). Add the interval value (0.28, cell C19) to get the next rank-order value (-0.76 + 0.28 = -0.48). Follow same procedure until you have 10 values; the 10th value will equal the maximum standardized value.
IMBA: VLOOKUP function looks at the standardized score in D6 (-0.41) and searches the array D22-E31 to determine the value to place in cell E6. It finds the closest value in D22-D31 (rounding down), which is -0.48 in cell D23. It then finds the value in the same row, next column, which is 2.0 in cell E23. The rating placed in cell E6 is thus 2.0.		 2 -0.47 2.0 -0.56 2.0 -0.56 9.0
3 -0.23 3.0 -0.31 3.0 -0.31 8.0
4 -0.00 4.0 -0.06 4.0 -0.06 7.0
5 0.23 5.0 0.19 5.0 0.19 6.0
6 0.47 6.0 0.44 6.0 0.44 5.0
7 0.70 7.0 0.68 7.0 0.68 4.0
8 0.94 8.0 0.93 8.0 0.93 3.0
9 1.17 9.0 1.18 9.0 1.18 2.0
10 (Max) 1.41 10.0 1.43 10.0 (Min) 1.43 1.0