Help With Capacity Planner Tool Excel ??

[JL682]

Hi,

I am looking for some help with an Excel Model that I want to create -

The basic concept is to calculate the -> order volumes - Month & Daily Take an Average and define what resource is required based on the times for the product manufacture.

It would then calculate the potential output possible based on the staff level, and the times entered.

Does anyone know if this is possible?

Any help would be greatly appreciated,
Thanks.
JL682

[kasan]

Hi, I think that your problem is not clear not only for me.
Could you fill some values in your sample file and give comments how did you calculate it.

[JL682]

Hi
I can see that my explanation of what I want to achieve could be more clear - To Simplify..

- Worksheet 1 ( Input Volumes - Monthly(Manually Enter), generate the weekly and daily volumes below this (which I am able to do)
- Worksheet 2 ( Standard Times For Product Build, Breakdown into Task Number & Mins (manually entered)
- Worksheet 3 ( Staff Hours - This should contain the available hours for each member of staff)
- Worksheet 4 ( Output Possibility Monthly , Weekly Daily - This would need to be generated by the above worksheets in someway ????

Input -> Time Required -> Time Available -> Possible Output.

Maybe I am overcomplicating this, so any alternative ideas would be welcomed.

Thanks, JL682

[LordLoki]

Your Data does still not make any sense.

- What do you Enter in Sheet 1 is what exactly? The Expected Volume for Each Product?
- Why do you need to break down the Work in Tasks when you only want to know how much product you can make in one day.
- With the Information in Sheet 3 i assume all workers are working every day? If not you need to specify each workers working hours per weekday.

So lets assume you want to generate a general Forecast with the Data you provided.
That would then be (90*60+60*0,6)/57,5 =94,53 that means you can at best make 94 Piece of product every day if all employees are available

[JL682]

Thank you LordLoki,

-Yes worksheet 1 is the expected volume.
- I would like to break it down into tasks so eventually I could split the tasks into different sections and allocate staff hours to that number of Tasks.
- I agree the staff hours could be done per weekday..

The general forecast is great and works I am just trying to get a template that looks a little more professional instead of just and excel sheet with data (hence the split of worksheets)

Thanks Again, will try and rethink this as I am not really able to explain the concept very well,

BR
JL

[Logit]

Are you asking for an answer from the following ?

Sheet Name..........Cell Data

Input Data.............B5 = 100
Times...................B7 = 7
Resource...............B4 = 151, C4 = 37.75, D4 = 7.55

From the above data, are you seeking a formula something like: 100 x 7 x 151 = ? .... or .... 100 x 7 x 37.75 = ? .... or .... 100 x 7 x 7.55 = ?

Then whatever the formula construct is, place the answer in Capacity Planner / B6 ?