Question

• Calculate Unit Profit for each product (in cells B8:I8) and Total Profit (in cell B19)
• Calculate total capacity required in Department 1 (overtime) in cell B24 and Department 2 (regular and overtime) in cells B25:B26
• Calculate total units produced of each product family in cells B28:B31.

Table 1 Product Table Floor Ceiling Pendant Material cost $66 85 50 80 Table 2 Regular Time Process Unit Cost $16 12 Capacity 100,000 90,000 Unit Cost 18 15 Capacity 25,000 24,000 Table 3 Selling price Potential sales (000) Advertising effect Table $120 60 12% Floor 150 20 10% Ceiling 100 100 8% 160 35 15% File Insert Page Layout Formulas Data Review View Help Tell me what you want to do Σ AutoSum A Calibr Copy Format Painter Fill ▼ 트트囹Merge & Certer. $. % , 留. Conditional Format as Cell FormattingTable Styles . .2 . A. . 一ー insert Delete Format B 1 u, Sort & Find & Clear FilterSelect Clipboard Font Cells Editing 13 Costs and prices r regular time; oovertime Table lanm Floor lamps (F) Ceiling lamps (C) Pendant lamp To Fo Cr CO Po Celling price Material costs Production costs Unit 120 150 160 160 16 16 12 15 Department 1 Department 2 Decision Variables Units produced Advertising To Fo Co Po 7 I Objective function: max (Profit) - Constraints Capacity constraints: Department 1 regular time Department 1 overtime RHS 0 100000 Insert Page Layout Formulas Data Review View Help Tell me what you want to do Cut Copy AutoSum Calibri 11 A'A.-보 ウ· 曹wrap Text Fill Paste 00 Conditional Format as Cell Insert Delete Format Sort & Find & Filter Select в 1 u. Eu, 의. Д· 菇 Merge & Certer . $. % , 넒 ..Clear Cells Format Painter Formatting Table Styles Styles Clipboard Alignment Editing 013 11 Decision Variables: 12 13 Units produced 14 15 Advertising 16 17 Objective function: 19 max (Profit) To Fr Fo Cr Co Po 21 Constraints: 22 Capacity constraints: 23 Department 1 regular time 24 Department 1 overtime 25 Department 2 regular time 26 Department 2 overtime 27 Demand constraints 28 Table lamps 29 Floor lamps 30 Ceiling lamps 31 Pendant lamps LHS RHS 0 100000 25000 60000 33 Advertising constraint 34 0 18000

Answer 1

A) Calculation of Unit Profit and Overall Profit of the products:

Particulars	Table Lamps		Floor Lamps		Ceiling Lamps		Pendant Lamps		Total Profit
	Tr	To	Fr	Fo	Cr	Co	Pr	Po
Selling Price	120	120	150	150	100	100	160	160
Material Costs	66	66	85	85	50	50	80	80
Production Costs	16	18	16	18	12	15	12	15
Unit Profit	38	36	49	47	38	35	68	65
Sales (Without Advertising)	-	-	-	-	-	79000	-	35000
Sales (With Advertising) in Units	67200	-	22000	-	-	-	-	-
Total Profit	2,553,600.00	-	792,000.00	-	-	2,765,000.00	-	2,275,000.00	8,385,600.00

Note:  In case of Table Lamps & Floor Lamps, No. of Sales units taken considering Advertisement effect of 12% and 10% respectively but in case of Ceiling Lamps and Pendant Lamps, Advertisement effect because of capacity limitation.

B) Capacity requirement in department 1 :

i) Table Lamps Maximum sales including advertisement effect = 60000+12% = 67200 units

ii) Floor Lamps Maximum sales including advertisement effect = 20000+10% = 22000 units

iii) Normal Capacity of Department 1 = 100000 Units

iv) Overtime Required (iii)-(i)-(ii) = Nil

Capacity requirement in department 2 :

i) Ceiling Lamps Maximum sales including advertisement effect = 100000+8% = 108000 units

ii) Floor Lamps Maximum sales including advertisement effect = 35000+15% = 40250 units

iii) Total Capacity Requirement of Department 2 (Including overtime)    = 148250 Units

iv) Normal Capacity of Department 2 = 90000 Units

v) Overtime Required (iv)-(v) = 58250 Units

C) Total units produced of each product :

Table Lamps = 67200

Floor Lamps = 22000

Ceiling Lamps = 73750 (114000-40250)

Pendant Lamps = 40250 (35000+15%) *

* Note = Since production capacity is limiting factor (key factor) , Pendant lamps are produced to the maximum because profit per unit is more as compared to Ceiling Lamps and therefore balanced capacity of department 2 has been used for Ceiling lamps .