Question

Homework #3 Instructions: Use MS Excel to produce the computations to support your answers to Problems,, IV and V Problem l: In a wine-making factory, a bottling machine that has an efficiency rating of 87 percent can fill 50 bottles in 24 minutes. The daily production rate is 21,000 bottles. How many bottling machines are needed? (Assume an 8-hour working day). Problem Il: A company produces two products P1, and P2 at the rate of 7200, and 8900 units per month. Three components C1, C2, and C3 are used in making a unit of each of the products. The component requirements for each unit of the product are given in Table 1 Table 1: Unit Requirements C1 C2 C3 P1 4 P2 The time required (in minutes) to make a unit of each of these components on the two machines is given in Table 2. Table Time in Minutes per Unit M1 M2 C1 6.9 C2 6.4 4.8 C3 6.2 The machines are available for 22 days, 8 hours a day, in a month to do production work. Currently, there are 50 machines of type M1, and 13 machines of type M2 available in the workshop. Machines of type M2 are used to produce only C2 items. Machines of type M1 can be used to produce any of the three components. Do we have enough capacity to produce all the components in a month? (Hint: First, calculate the required rate of production for each of the components. Determine the maximum

Please help me I need this completed by midnight. Please compute in Microsoft Excel as well. Thank you.

Answer 1

Problem I

Effective capacity of machine = (50/24)*8*60 = 1000 bottles per day

Actual output per machine per day = Effective capacity * Efficiency rating = 1000*87% = 870 bottles per day

Daily production rate = 21000 bottles per day

Number of machines required = Daily production rate / Actual output per machine per day = 21000 / 870 = 24.14 ~ 25 machines

Formula: J15 =J5*$M5 copy to J15:L16

Effective capacity of machine = (50/24)*8*60 = 1000 bottles per day Actual output per machine per day = Effective capacity * Efficiency rating = 1000*87% = 870 bottles per day Daily production rate = 21000 bottles per day Number of machines required = Daily production rate/Actual output per machine per day = 21000/870 = 24.14 ~ 25�machines Problem I Effective capacity of machine = 1000 bottles per day Effective rating of machine = 87% Actual output per machine = 870 bottles per day = Effective capacity * Efficiency rating Daily production rate = 21,000 bottles per day Number of machines needed = 24.14 machines = Daily production rate/Actual output per machine (rounded - up) 25 machines Problem II Table 1: Unit Requirements Table 2: Time in Minutes per unit Required rate of production of each component Maximum number of C2 items to be produced on M2 =

N18 =M11*22*8*60/K11

N19 =SUM(K15:K16)-N18

N20 =J15*J10+N19*K10+L15*L10

N21 =M10*22*8*60