Demand vs Capacity analysis - How to solve this problem

[Veyron]

Hi all,

I have to develope an Excel spreadsheet to answer these two questions:

1. Can I deliver everything on time?
2. Which should be the machine configuration to meet the demand?

Now let me explain you briefly the problem:

There is a factory with 6 machines that make more than 30 different products. Customers place their order saying what they want (product and qty) and when they want it.

Restrictions:

- Work Calendar
- Not every product can be made in every machine
- Some products production vary depending on which machine they are.
- Minimize machine changes

-------------------------------- THIS IS HOW I STARTED --------------------------------​

Preparing and sorting data

With the customers demand I have created a pivot table were columns are total products (A,B,C,...) demand and rows are the due date when it has to be delivered:

[TABLE="class: grid, width: 250, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]01/01/2016[/TD]
[TD]5[/TD]
[TD][/TD]
[TD]10[/TD]
[TD][/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]02/01/2016[/TD]
[TD][/TD]
[TD]20[/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]03/01/2016[/TD]
[TD]5[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]04/01/2016[/TD]
[TD][/TD]
[TD]10[/TD]
[TD][/TD]
[TD]12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]05/01/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

I have also created a tab with the work calendar where I say if it is a labour day or not and if it is, how many cycles per day each machine runs (we are in an overloaded scenario so if it is a labour day each machine runs at its full capacity which means 3 shifts x 10 cycles per shift = 30 cycles per day)

I have created also another tab where I say the relationship among product - machine - qty per cycle

[TABLE="class: grid, width: 250, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]M1[/TD]
[TD]M2[/TD]
[TD]M3[/TD]
[TD]M4[/TD]
[TD]M5[/TD]
[TD]M6[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]12[/TD]
[TD]12[/TD]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD][/TD]
[TD]12[/TD]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD][/TD]
[TD]12[/TD]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD][/TD]
[TD][/TD]
[TD]16[/TD]
[TD]16[/TD]
[TD]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]

For example: Product A can be made in machines 1,2,3 and we get 12 products A in one cycle.


At this point I think I have all information I need but I do not know how to continue.


Capacity depends on machine configuration but configuration should be changed as less as possible but as necessary it takes to meet the demand.

Should I use solver tool for this? How should I use it? Because usually the demand we have is for the following 30 days but there is not a unique machine configuration that can last from day 1 to day 30 unchanged, it has to be changed maybe 3 times each per week... Can I get a solution like "use this configuration from day 1 to day 7, use this cofiguration from day 8 to day 14, ..." and so on?

Are there more options for this? or is there a easiest approach to this problem?

Any help would be really appreciated

Thanks

 

[mistahk]

This is actually kind of a cool puzzle but one you need to mostly solve on paper before trying to do in excel. It seems like you haven't really tried to analyze the solution, and are just asking for someone on the board to do your job for you, which I guess is the norm here, haha.

Maybe try doing some work on it so whoever helps you doesn't have to start from scratch. Post different scenarios & data, see what logic would apply, etc.