Mk0011 Solved Assignment In Excel


1Q2. Define market segmentation and explain the different bases for market segmentation,with an example of each.Ans.:

Market Segmentation:

Market segmentation is the process in marketing

, “of dividing a market into distinct subsets(segments) that behave in the same way or have similar needs” 

. Because each segment is fairlyhomogeneous in its needs and attitudes, it is likely to respond similarly to a given marketingstrategy. It is also likely to have similar feelings and ideas about a marketing mix, comprised of agiven product or service, sold at a given price, distributed in a certain way and promoted in acertain way. Before marketers started using the concept of market segmentation, the way thebusiness was done was through

mass marketing,

i.e. offering the same product and samemarketing mix to all consumers. The classic case of such a practice was that of Henry Ford’sphilosophy of offering Ford Model T car, with the choice to the consumers of selecting any co lour they wanted as long as it was black. If all consumers were alike, i.e. if all of them had the sameneeds, desires, personalities, backgrounds, attitudes, etc., mass marketing would have beenrelevant and highly successful. Then, only one standardized product, one advertising campaignand one marketing mix are needed for all consumers. The main advantage in this strategy is that,it costs substantially less. However, as we have seen in the vignette of HLL vs. Nirma, theconsumers are indeed diverse and such a strategy of mass marketing would be highly risky intoday’s market environment.

0Bases for Market Segmentation

The first step in developing a segmentation strategy is, to select the most appropriate basis onwhich the market can be segmented. There are many ways in which this can be done and somemost popular variables used for market segmentation are discussed in the following paragraphs:

Geographic Segmentation:

In this method, the market is divided on the basis of location. There can be different categories insuch segmentation also. Some of these are:



Region of the world or country:

East, West, South, North, Central, coastal, hilly, etc.



City Size:

Metropolitan cities, small cities, and towns.



Density of population:

Urban, semi-urban, and rural.




: Hot, cold, humid, rainy

Demographic variables

This is the second most popular variable used by marketers. Factors like age, education, income,etc. individually or in combination, are commonly used to segment the market. Some of thesevariables are discussed here:a)


The assumption here is that people in the same age group will behave in the samemanner. Based on this we can have different subgroups like infants (new born to 1 year), children(1 to 12 years), teenagers (13 to 19 years), adolescents (16-19 years), youth (20-35 years),middle aged (36-50 years), elders or seniors (50 years and above).




Male preferences are different from the female preferences. While some productslike garments and cosmetics are produced exclusively for each segment, there are someproducts, which are meant for both segments and these are called unisex products.



Education – School, College and University:

The level of a consumer’s education will alsoaffect the preferences and also the level of awareness. The higher the level of education, thehigher is the awareness about the market environment and about different products. Their awareness about their rights as consumers will also be better.



Marital status:

Family has been the focus of most marketing efforts and householdcontinues to be the target for many products and services. Marketers are interested indetermining the profiles of decision makers in households, to develop appropriate marketingstrategies. Many marketers have found it useful to target specific marital status groupings, likesingles, divorced individuals, single parents, dual income married couples, etc.




It is believed that, as the consumers’ income increases, their consumption behavior also changes. Research findings have proved that, the expenditure on food and basic necessitiesas a percentage of total expenditure declines as consumer income increases. The consumer thenstarts buying costlier branded products, and also so called luxuries like automobiles, washingmachines, microwave ovens, holiday packages, air travel, etc. On the basis of income, the

Formulate the Model | Trial and Error | Solve the Model

Use the solver in Excel to find the assignment of persons to tasks that minimizes the total cost.

Formulate the Model

The model we are going to solve looks as follows in Excel.

1. To formulate this assignment problem, answer the following three questions.

What are the decisions to be made? For this problem, we need Excel to find out which person to assign to which task (Yes=1, No=0). For example, if we assign Person 1 to Task 1, cell C10 equals 1. If not, cell C10 equals 0.

What are the constraints on these decisions? Each person can only do one task (Supply=1). Each task only needs one person (Demand=1).

What is the overall measure of performance for these decisions? The overall measure of performance is the total cost of the assignment, so the objective is to minimize this quantity.

2. To make the model easier to understand, name the following ranges.

Range NameCells

3. Insert the following functions.

Explanation: The SUM functions calculate the number of tasks assigned to a person and the number of persons assigned to a task. Total Cost equals the sumproduct of Cost and Assignment.

Trial and Error

With this formulation, it becomes easy to analyze any trial solution.

For example, if we assign Person 1 to Task 1, Person 2 to task 2 and Person 3 to Task 3, Tasks Assigned equals Supply and Persons Assigned equals Demand. This solution has a total cost of 147.

It is not necessary to use trial and error. We shall describe next how the Excel Solver can be used to quickly find the optimal solution.

Solve the Model

To find the optimal solution, execute the following steps.

1. On the Data tab, in the Analyze group, click Solver.

Note: can't find the Solver button? Click here to load the Solver add-in.

Enter the solver parameters (read on). The result should be consistent with the picture below.

You have the choice of typing the range names or clicking on the cells in the spreadsheet.

2. Enter TotalCost for the Objective.

3. Click Min.

4. Enter Assignment for the Changing Variable Cells.

5. Click Add to enter the following constraint.

Note: binary variables are either 0 or 1.

6. Click Add to enter the following constraint.

7. Click Add to enter the following constraint.

8. Check 'Make Unconstrained Variables Non-Negative' and select 'Simplex LP'.

9. Finally, click Solve.


The optimal solution:

Conclusion: it is optimal to assign Person 1 to task 2, Person 2 to Task 3 and Person 3 to Task 1. This solution gives the minimum cost of 129. All constraints are satisfied.


Leave a Reply

Your email address will not be published. Required fields are marked *