## excel The task is :- Produce a capacity and demand model, (with sensitivity analysis) for NI’s packing operation?

excel The task is :- Produce a capacity and demand model, (with sensitivity analysis) for NI’s packing operation?

Order Description

Produce a capacity and demand model, (with sensitivity analysis) for NI’s packing operation?

Capacity Model
? Always save your work each time with a new version number in case you make an error
? Always back it up

Use the blank ‘Units for Packing’ worksheet in the ‘Forecast Sales by Product’
workbook to do all your calculations:

1. Calculate the forecast number of units per product for each week using the data from the Sales Units tab in the ‘Forecast Sales by Product’ workbook – don’t forget

that this can be done quite easily using cell referencing and formulae and by dragging formulae down columns and across rows.

2. Add all the Units together for each week and this will give you your total forecast demand for each week
You now have the demand – we now need to look at capacity side Using the ‘Packing Productivity’ data in the NI Labour Data workbook:
Calculate the average units packed per minute per person
Convert this average to the number of units packed per week per person – to do
this you will have to make some assumptions about the number of hours worked in a week.

3. Going back to your ‘Units for Packing’ worksheet you can now calculate the number of packers required each week using the total forecast demand for each week and

the av. weekly units packed per person per week

4. Can you display your findings graphically? (have a look at the graphs we produced for the call centre)
What are the implications of your findings?
Can you produce some costings for your capacity plan?
Write these up in the excel workbook , use a new tab to create new worksheet.

Help :
Here is a video explaining the process. ( Capacity )