Everyhting its EXCEL Formulas: Data Modeling Management Tools

You are a manager at a large manufacturing firm. You have been approached by your board of directors to make a decision regarding the selection of products that need to be produced by your firm. You are expected to brainstorm and collaborate with your department colleagues to identify the product. Data about the products can be sought from different departments.

Product Selection with Costing Data The costing department provides you with the data given in the table 1. Use the contribution margin approach, make your selection of products to be manufactured.

Table 1: Product Selection with Costing Data

Product	Super Variable Cost SVC ($)	Price P ($)
A	5	30
B	10	30
C	20	43
D	10	34
E	35	44
F	40	67
G	40	39

Rank the product(s) recommended by you (in the order of preference)? If you do not recommend the product, leave blank.

Product	A	B	C	D	E	F	G
Rank

Product Selection with Manufacturing Data

Next, the Manufacturing department provides the time taken by the machine to product each product (in minutes) and also states that the machine hours are limited to 2500 hours. Based on this additional information, what products will you manufacture?

Table 2: Product Selection with Manufacturing Data

Product	Time taken (In minutes)
A	15
B	20
C	12
D	15
E	60
F	30
G	20

How many units of each product will you manufacture?

Product	A	B	C	D	E	F	G
Units

Product Selection with Marketing Data

The marketing department gives you the demand data for the products (Table 3). It is your organizations policy to produce just enough of each product to meet 95 % of the expected demand. Based on this additional information, what products will you manufacture?

Table 3: Product Selection with Marketing Data

Product	Product Demand	Demand deviation
A	7,355	1000.000
B	485	100.313
C	395	124.632
D	260	85.114
E	396	99.705
F	393	65.052
G	13	4.256

Based on this additional information, how many units of each product will you manufacture? (Choose the closest integer value)

Product	A	B	C	D	E	F	G
Units

Product Selection with Limited Inventory

The inventory department reveals that due to unforeseen circumstances raw material: RM1236, that is used in the manufacturing of the products is limited to 20000.

Table 4: List of inventory RM1236 required for each product.

Product	Raw Material: RM1236 needed
A	2
B	4
C	8
D	4
E	14
F	16
G	16

Based on this additional information, how many units of each product will you manufacture?

Product	A	B	C	D	E	F	G
Units

Product Selection with Risk Data

You decide a new way of looking at your data. You decide to ignore all data so far and consider only new data. The risk assessment team recommends that the original investment should be recovered in 5 years. They are wary of projects that have long payback time. Future cash flow for various product options are given in the Table 5. Use the non-discounted payback method.

Table 5: Product Selection with Risk Data and Finance Data.

Product	Initial investment $	Yearly Cash Flow
A	-98000	10000
B	-120000	22000
C	-777777	150000
D	-88888	10000
E	-900000	200000
F	-100000	10000
G	-1000	-20

Which product(s) will you recommend?

Product	A	B	C	D	E	F	G
Yes/No

Product Selection with Finance Data

The finance department looks at a slightly larger picture and takes into consideration initial investment, and the rate of return of the product. Your company has a required rate of return is 5%. The finance department uses Net Present Value (NPV) with time horizon of 7 years, to figure good projects/products to invest. Use the data given in the table 5 to calculate the NPV to arrive at your recommendation.

Which product(s) will you recommend?

Product	A	B	C	D	E	F	G
Yes/No

Product Selection with Budget Data

The Budgeting department provides you with the capital budget available for the year. They tell you that only $250,000 is available to be spent on product related investments. Hence available budget will have implications on what products will be produced. Moreover, success rate as well as revenues if the product is successfully produced is taken into consideration. Based on the required initial investment, success rate, and revenue estimate provided in the Table 6, what products will you manufacture? MS Excel solver can be used for this analysis.

Table 6: Product Selection with Budget Data

Product	Initial Investment	Success Rate	Revenue if Successful	Expected Revenue =Revenue if successful *Success Rate	Decision
A	98,000	0.5	1,400
B	120,000	0.35	1,200
C	777,777	0.35	2,200
D	88,888	0.2	3,000
E	900,000	0.45	600
F	100,000	0.45	700
G	1000	0.5	0
		Total investment	(X)
		Total investment available	250000
		Total Expected Profit	(Y)
Use the simplex method in solver. The Decision will have a binary value (1 or 0). Total investment is (X) SumProduct of initial investment and Decision. Total expected Profit (Y) is SumProduct of Expected Profit and Decision.

Which product(s) will you recommend?

Product	A	B	C	D	E	F	G
Yes/No

Product Selection with Management

So far, you used department level data. The decision choices made by each department appear contradictory. Apart from the above data, the strategy of the organization also needs to considered. Hence, the organization has to make a decision over and above the diverging preferences expressed by different departments. A holistic view of the decision objective needs to be made and for this purpose this module introduces product priority system that focuses on ranking products based on their contribution to the organizations objectives and strategic goals.

As a next step, rate each product on a 1-100 scale for the specified objectives. For this, you can use the results from the prior analysis to award these scores. Undesirable products, such as products that are below the required rate of return, can be given a negative score. For the purpose of simplicity, the ratings for each product is prefilled (Table 7). Finally, for total score of each product, multiply the weights with the rates given for each of the objective and total them. Sum of these totals will give you the score for each of the products. Now these scores are used to assign ranks for each of the product. As you may note some of the considerations used by different departments also prominently figure in these objectives in order to make the decision. The final module takes into account the collective good of the organization over and above the product individual preferences of the departments.

Table 7: Product Priority Evaluation Form

	Contribution per machine hour	Budget	Financial impact	Building skills in new area	Strong Sponsor	Fill Market Gap	Competition	Add to companys prestige / reputation
Weightage	0.15	0.05	0.13	0.15	0.15	0.05	0.15	0.07
A	2	5	6	83	90	14	14	69
B	100	40	69	99	55	39	24	65
C	49	55	98	11	100	51	4	65
D	66	46	26	48	72	75	59	96
E	89	92	63	6	34	78	30	25
F	52	63	30	68	16	60	80	86
G	52	63	-10	100	100	100	90	100
Transfer the data into MS Excel and use function sumproduct to populate the Total column.

Note: Scores and total weighted scores are calculated using MS Excel formulas. Objectives and their weights are entered by students based on their discussion and prior analysis. Similarly they will also rate each of the products based on their discussion.

Rank the product(s) recommended by you (in the order of preference)? If you do not recommend the product, leave blank.

Product	A	B	C	D	E	F	G
Rank