Question

A manufacturing company is planning to build a new facility in one of four states within the United States. Review the table below and develop a weighted project selection model with a minimum of 6 criteria (two criteria have been provided to get you started) to aid in the selection of the correct project. The company can only invest in one facility. Assign fictional scores to each criterion for each of the 4 states, provide a legend, and total the score. Make a recommendation based upon the score. Complete the assignment using Microsoft Excel. Please attach Excel file if possible. Thanks!

State Cost to Operate Skilled Worker Availability Medium Michigan Ohio Texas Idaho Mediumm Low Low High Medium Low Low

Answer 1

SOLUTION:

1. DECIDING THE CRITERIA:

Let me start with deciding on the 6 criteria (minimum) that can be taken into consideration. Some of the criteria that plays a huge role in zeroing a location for setting up a manufacturing plant can be as follows:

1. Cost to operate
2. Skilled worker availability
3. Availability of resources
4. Proximity to the market
5. Infrastructural facilities
6. Favorable Government policies

2. WEIGHTS FOR CRITERIA:

In order to define the level of impact or the level of importance that each factor will play a role in selection of a location, we need to give weights for each criteria totaling 100%. I have assigned fictional weights as 30%, 25%, 20%, 10%, 10% and 5% respectively for the above mentioned criteria.

3. RATING THE CRITERIA FOR EACH LOCATION:

Each location will have its own Pros and Cons, against each criteria, with respect to setting up of a manufacturing plant. Accordingly the location is given a rating as HIGH, MEDIUM and LOW against each criteria.

Weights	30%	25%	20%	10%	10%	5%	100%
State	1	2	3	4	5	6
	Cost to Operate	Skilled Worker availability	Availability of resources	Proximity to the market	Infrastructural facilities	Favorable Government Policies
Michigan	Medium	High	Low	Medium	High	Low
Ohio	Medium	Medium	High	Low	Medium	Low
Texas	Low	Low	High	High	Medium	Medium
Idaho	Low	Low	Medium	Low	High	High

The above table (TABLE 1) indicates the weights for each criteria and the rating given for each location with respect to the criteria.

4. CHANGING QUALITATIVE TO QUANTITATIVE RATING:

Since this is a qualitative judgement we need to change it into a quantitative factor in order to arrive at a composite weighted score. Hence, I gave scores of 5, 3, and 1 for High, Medium and Low respectively as shown below (TABLE 2).

Rating	Scores
High	5
Medium	3
Low	1

Accordingly the table (TABLE 3) will be changed as the following image:

Weights	30%	25%	20%	10%	10%	5%
State	1	2	3	4	5	6
	Cost to Operate	Skilled Worker availability	Availability of resources	Proximity to the market	Infrastructural facilities	Favorable Government Policies
Michigan	3	5	1	3	5	1
Ohio	3	3	5	1	3	1
Texas	1	1	5	5	3	3
Idaho	1	1	3	1	5	5

(Note: EXCEL TIP: The corresponding scores can be automatically generated with the help of =vlookup function in MS Excel from TABLE 1 and 2)

5. WEIGHTED SCORE CALCULATION:

A weighted score is calculated for each location by multiplying the weight % and corresponding rating for that particular criteria. (Note: EXCEL TIP: Use =sumproduct

Weights	30%	25%	20%	10%	10%	5%
State	1	2	3	4	5	6
	Cost to Operate	Skilled Worker availability	Availability of resources	Proximity to the market	Infrastructural facilities	Favorable Government Policies	WEIGHTED SCORE
Michigan	3	5	1	3	5	1	3.2
Ohio	3	3	5	1	3	1	3.1
Texas	1	1	5	5	3	3	2.5
Idaho	1	1	3	1	5	5	2

Let us consider Michigan for analysis:

Weighted score = (30% X 3) + (25% X 5) + (20% X 1) + (10% X 3) + (10% X 5) + (5% X 1) = 3.2

a	30%	25%	20%	10%	10%	5%	Total
b	3	5	1	3	5	1
c= a * b	0.9	1.25	0.2	0.3	0.5	0.05	3.2

Since Michigan has the highest score of 3.2, we can recommend that as the final location.

  --------END OF SOLUTION-------

POINTS TO NOTE:

1. Weights should add up to 100%
2. Rating score be given any number such that High should have the maximum value and Low should have the minimum value.

MS EXCEL TIPS:

1. Use Vlookup to call the corresponding score for the rating.
2. Use sumproduct to multiply weights and ratings to simplify the work. You can multiply one by one as well.
3. Use Conditional formatting to highlight the maximum weighted score.
4. Use Max function to return the maximum value of weighted score
5. Use Index function to return the city name with highest weighted score.

Once you link the tables in a way I explained, the only thing which you need to input will be the weights, rating (high, medium or low) and the corresponding score for each rating. Excel will do the work for you and give the recommended location.

(I built the excel model which will automatically return the result, but I am not able to attach it)

	Weights	30%	25%	20%	10%	10%	5%	100%
	State	1	2	3	4	5	6
		Cost to Operate	Skilled Worker availability	Availability of resources	Proximity to the market	Infrastructural facilities	Favorable Government Policies			Rating	Scores
	Michigan	Medium	High	Low	Medium	High	Low			High	5
	Ohio	Medium	Medium	High	Low	Medium	Low			Medium	3
	Texas	Low	Low	High	High	Medium	Medium			Low	1
	Idaho	Low	Low	Medium	Low	High	High
	Weights	30%	25%	20%	10%	10%	5%
	State	1	2	3	4	5	6
		Cost to Operate	Skilled Worker availability	Availability of resources	Proximity to the market	Infrastructural facilities	Favorable Government Policies	WEIGHTED SCORE
	Michigan	3	5	1	3	5	1	3.2		MAX SCORE:
	Ohio	3	3	5	1	3	1	3.1		3.2
	Texas	1	1	5	5	3	3	2.5		SELECTED LOCATION:
	Idaho	1	1	3	1	5	5	2		Michigan

Hope, this is satisfactory.