The Hawley Lighting Company manufactures four types of lamps at its factory including table lamps, floor lamps, ceiling lamps, and pendant lamps. Table 1 presents the average material costs for each of the products. Each product is made in one of two production processes by purchasing components, assembling and testing the product, and finally packaging it for shipping. Table lamps and floor lamps go through the assembly and finishing process in Department 1, while ceiling fixtures and pendant lamps go through the process in Department 2. Variable production costs and capacities are shown in Table 2. The capacities are measured in units of product. Note that there are regular and overtime possibilities for each department.Average selling prices for the four products are known, and estimates have been made of the market demand for each product at these prices (see Table 3). Sales levels can also be affected by advertising expenditures. Starting with the demand levels in the table, an increase of up to $10,000 in advertising raises the demand by the percentage shown in the last row. An expenditure of less than $10,000 in advertising will lead to a proportional effect on demand. For example, an increase in advertising of $5,000 for table lamps would raise demand by 6 percent, or 3,600 units. However, there is a budget limit of $18,000 on the total amount to be spent on advertising among all four products.

• 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.

Transcribed Image Text:

Table 1 Product Material cost Table 2 Process Department 1 Department 2 Table 3 Selling price Potential sales (000) Advertising effect Table $66 Unit Cost $16 12 Table $120 60 12% Floor 85 Regular Time Ceiling 50 Capacity 100,000 90,000 Floor 150 20 10% Pendant 80 Overtime Unit Cost 18 15 Ceiling 100 100 8% Capacity 25,000 24,000 Pendant 160 35 15% A 1 Costs and prices: 3 4 5 Celling price 6 Material costs 7 Production costs 8 Unit profit 9 10 11 Decision Variables: 12 13 Units produced 14 15 Advertising 16 17 Objective function: 19 max (Profit) = ZU 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 32 33 Advertising constraint 34 35 B D r = regular time; o = overtime Table lamps (T) Tr To Tr T LHS 120 66 16 38 0 0 To 120 66 18 36 Department 1 F RHS 100000 25000 90000 24000 60000 20000 100000 35000 18000 Floor lamps (F) Fr Fo Fr C 150 85 E 16 49 Fo P 150 85 18 47 F Cr Ceiling lamps (C) Pendant lamps (P) Cr Co Pr Po 100 50 G 12 38 100 50 H Co 15 35 Department 2 160 80 12 68 Pr Po 160 80 15 65 Table 1 Product Material cost Table 2 Process Department 1 Department 2 Table 3 Selling price Potential sales (000) Advertising effect Table $66 Unit Cost $16 12 Table $120 60 12% Floor 85 Regular Time Ceiling 50 Capacity 100,000 90,000 Floor 150 20 10% Pendant 80 Overtime Unit Cost 18 15 Ceiling 100 100 8% Capacity 25,000 24,000 Pendant 160 35 15% A 1 Costs and prices: 3 4 5 Celling price 6 Material costs 7 Production costs 8 Unit profit 9 10 11 Decision Variables: 12 13 Units produced 14 15 Advertising 16 17 Objective function: 19 max (Profit) = ZU 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 32 33 Advertising constraint 34 35 B D r = regular time; o = overtime Table lamps (T) Tr To Tr T LHS 120 66 16 38 0 0 To 120 66 18 36 Department 1 F RHS 100000 25000 90000 24000 60000 20000 100000 35000 18000 Floor lamps (F) Fr Fo Fr C 150 85 E 16 49 Fo P 150 85 18 47 F Cr Ceiling lamps (C) Pendant lamps (P) Cr Co Pr Po 100 50 G 12 38 100 50 H Co 15 35 Department 2 160 80 12 68 Pr Po 160 80 15 65