Production planning using excel

[Aalap]

As we are a pharma company, the demand forecasting is never actual. Currently we manually consider each product sales, last 3 months sales, correspoding month sales of last year+ 25 % and put down the orders.

This process is done on a weekly basis. Almost a day is spent in making the sheet and putting purchase orders. Still there are many times the product gets short. The lead time is normally 2 months from the date of order placed.

Is there a process to help me with the current system.
Have attached a copy of production planning we use.

 

[Faseeh]

Hi Aalap,

How can we help you? I think we can help you in two ways..

1. By helping you make (a sheet) that reduces the time it consumes to make this sheet.
2. By improving formulas in the current sheet (if you need some..)

What is your opinion and what you are looking for?

 

[Aalap]

Thank you for the reply.

My main concern was to define a set process, so that in future orders can be placed easily. I came across Gantt chart for planning, but couldnt get make much out of it. May be use of formulas for better forecasting would help.

 

[khelgadi]

Dear Alaap,
There are tools for inventory management like Reorder Level , Reorder Quantity etc. The System which you are using looks similar to that, but it seems to consider simple avg. You can use "trend" for forcasting the future demand. Also the quantity produced in pharma generally depends on Batch size, which is mostly fixed, i.e. ROQ is fixed.
The other way to speed up the issue is by getting the required data by using formula from the reports taken from your inventory mgmt software