Attempting to make a formula/algorithm based on weighted averages to find how much equipment we need to maintain.

I want to preface this by saying I have no idea if that was the correct tag to use.

I have an excel spreadsheet I have put together holding a bunch of data. I'm hoping to come up with a best guess ratio to achieve/maintain of 3 different pieces of equipment that each run a specific process.

Process/Equipment 1 runs 18 hours a day, 5 days a week.
Process/Equipment 2 and 3 run 24 hours a day, 6 days a week.
Process 3 will take 24 hours regardless of the part.

Part 1 runs 49 batches a year.
Process 1 takes 30 hours to complete a batch.
Process 2 takes 8 hours to complete a batch.

Part 2 runs 13 batches a year.
Process 1 takes 30 hours to complete a batch of parts.
Process 2 takes 8 hours to complete a batch.

Part 3 runs 20 batches a year.
Process 1 takes 15 hours.
Process 2 takes 14.5 hours.

Part 4 runs 23 batches a year.
Process 1 takes 15 hours.
Process 2 takes 14.5 hours.

Part 5 runs 33 batches a year.
Process 1 takes 34 hours.
Process 2 takes 7.5 hours.

I'm trying to get a weighted average for Process 1 and 2 time based on how many batches of each part we run a year. It is complicated by how long each process runs a week. This is what I am trying so far using the data above.

Process 1 Average (weighted) = ((30×49)+(30×13)+(15×20)+(15×23)+(34+33)) ÷ (49+13+20+23+33) = 26.2826087

Process 2 Average (weighted) = ((8×49)+(8×13)+(14.5×20)+(14.5×23)+(7.5×33))÷(49+13+20+23+33) = 9.905797101

I'm not sure how to account for time though. I thought about taking the time the processes run (respectively 80, 144, and 144 hours per week) and dividing by how many hours there are in a week to get a factor to multiply the weighted averages by.

80÷168 = 0.5357142857
144÷168 = 0.8571428571

Process Weighted Average × Time Factor Adjustment
Process 1 = 26.2826087 × 0.5357142857 = 14.0799689461
Process 2 = 9.905797101 × 0.8571428571 = 8.490683229
Process 3 = 24 × 0.8571428571 = 28

So in this scenario would call for a ratio of around 1:0.6:2 (approximately) for processes 1, 2, and 3 respectively correct? Am I doing this right? I'm not a mathmagician, I would just rather have a computer do this for me and usually I can figure it out how to make a formula for this type of thing in excel or python or javascript or something, but I don't really have another way to check this specific one. Hopefully I have described what I'm trying to do well enough to be understood, if not please let me know if I can do anything to make it more clear. Thank you all in advance for any help you can provide!
Algebra
Andrewm Andrewm
10
Report
  • Erdos Erdos
    0

    You need to make your question a bit more clear. Can you clarify what you mean by parts and processes here? What is a batch? You should state your problem assuming that other know absolutely nothing about your problem and try to make it clear what you mean by all those words.

  • Andrewm Andrewm
    0

    I work in a factory that produces parts, many different kinds, but that doesn't really matter. A batch is the amount of parts made, the final process they go through determine the size of the batch. That is why there are multiple durations of time listed for varying parts. The first process makes the parts, the second process prepares that batch of parts for the 3rd process, the final process has the same time duration and I don't believe that will change.

  • Erdos Erdos
    0

    What does "Part 1 runs 49 batches a year" mean? Does it mean that the factory makes 49 batches of part 1 in a year?

  • Andrewm Andrewm
    0

    Yes. I am using that to weight the time to process a batch of parts through process 1 and 2. So making 49 batches of part 1 a year will have its 30 hour time to process count more towards the average of all than part 3's average of 15 hours, because we would only make 20 batches per year.

  • Savionf Savionf
    0

    This problem is very vaguely formulated, and because of that may not be answered properly.

Answer

Answers can be viewed only if
  1. The questioner was satisfied and accepted the answer, or
  2. The answer was disputed, but the judge evaluated it as 100% correct.
View the answer
Erdos Erdos
4.6K
  • Andrewm Andrewm
    0

    I ended up adjusting it a bit for my purposes, but I believe the ratios I came up with are fairly close to accurate. Thank you!

  • Erdos Erdos
    0

    Cool! I hope my solution was helpful.

The answer is accepted.
Join Matchmaticians Affiliate Marketing Program to earn up to 50% commission on every question your affiliated users ask or answer.