Need Help: Determine Equipment Capacity

  • Thread starter Thread starter Will
  • Start date Start date
W

Will

I am trying to create a formula that can determine equipment capacity.

A1 = Seconds/Per Shift Avail.
A2 = Number of Shifts Scheduled
A3 = Number of Days Scheduled
B1 = Part A Annual Volume
B2 = Part B Annual Volume
B3 = Part C Annual Volume
C1 = Part A Cycle Time
C2 = Part B Cycle Time
C3 = Part C Cycle Time

I am just beginning to learn formulas in Excel and I am able to
determine the capacity for one Part. The problem is combining
multiple cycle times to determine capacity. Thanks in advance for the
help.
 
Can you give an example of the actual values, and the results you would like
to see?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
You get total time available with (A1*A2*A3)
You get total time needed by each part with b1*C1 , b2*c2, b3*c3

sum the total time required and subtract the time available. If it is a
positive numbe, you have excess capacity. If it is negative, then you need
to determine which part or parts will not be produced and use the cycle time
to figure out the decrease in amount.
 
Can you give an example of the actual values, and the results you would like
to see?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)








- Show quoted text -
Click to expand...

A1 = 25800 seconds per shift
A2 = 3 Shifts
A3 = 240 Days Available
B1 = 1,000,000 Part A Volume
B2 = 300,000 Part B Volume
B3 = 200,000 Part C Volume
C1 = 10 Seconds to make Part A
C2 = 20 Seconds to make Part B
C3 = 15 Seconds to make Part C

If I take A1*A2*A3 the result would be 18,576,000 seconds available
per year. B1*C1 results in 10,000,000 seconds to build B1. B2*C2
results in 6,000,000 seconds to build B2. B3*C3 results in 3,000,000
seconds to build C3. With all the times combined I need 19,000,000
seconds to build the required volume of parts.

I only have one piece of equipment available to make Parts A, B, and
C. So basically I need to be able to say that I need 1.02 pieces of
equipment to produce the required volumes.
 
=ROUND(SUMPRODUCT(B1:B3,C1:C3)/PRODUCT(A1:A3),2)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Back
Top