Macro to generate Production plan in excel.

[Vignesh Veerasamy]

Dear Gurus,

User had placed the requirement for a macro to generate Production plan automatically with available data considering the criteria.
User will receive the marketing plan in logic of M(current month) , M+1(next month) , M+2 (two months from current month)
Currently user is spending hours to balance the vertical and horizontal values in the excel manually.

POST SCRIPT : Data in excel is manually edited.I have filled up the columns not be left blank.

CRITERIA
• OPENING STOCK SHOULD BE MINIMUM 15%
• VERTICAL TOTAL SHOULD TALLY WITH APR'14(similar for other months too) TOTAL GIVEN IN MARKET PLAN ALSO WITH CAPACITY
• HORIZONTAL TOTAL SHOULD NOT FALL BELOW THE MARKETING REQUIREMENT.
• HIGHLIGHTED YELLOW COLUMN WILL BE UPDATED BY USER BASED ON OPEN STOCK IN HAND MANUALLY
• AVAILABLE CAPACITY WILL BE GIVEN BY USER MANUALLY IN SHEET - MARKETING PLAN.

Finally sheet named as Production plan should be generated afte the macro is being over.

Will be thankful for solution.

Thanks in Advance.



Vignesh.V

 

[NARAYANK991]

Hi Vignesh ,

I think some more explanation is required.

1. Can I assume that the WORKING tab is purely your own internal calculations , except for the data in column C ?

2. The MARKETING PLAN tab is the input data , and the Production Plan tab is the output.

3. What is the Billing Requirement that is on the WORKING tab ?

If you wish to automate the process , I think you should take one product , and explain the entire process of how the production plan for that product for the entire year is arrived at considering the given inputs.

Narayan

 

[Vignesh Veerasamy]

Dear Narayan Sir,

Greetings to you.

1. Marketing plan is the input data that user will be working and arriving at the Working sheet manually.Yes column C will be given manually.
2.Marketing plan is the input data and Production plan sheet is nothing but the summary of data from working sheet.
3.Billing requirement is nothing but the marketing requirement had been mentioned as billing requirement.

For example let us consider Part no: A.

Marketing had given the requirement month-wise in sheet of Marketing plan like

Apr'14 - 90
May'14 -75
Jun'14 - 50
etc
etc

Same data is being made in horizontal wise data in working sheet row A6 100 75 50 etc etc under each month.
for which Production plan is being given as 110 33 49 which is in-turn taken care that Opening stock percent should not fall less than 15%.

Finally the consolidated Production plan is being manually copied from working sheet and Production plan sheet is arrived to send to plants.

Final output is like the quantity should have satisfied both Marketing requirement (column AZ in marketing sheet - over all total of Marketing requirement - manual work) for the month also it should be not more than the Available capacity (column BA in marketing sheet - manual input)of the plant.

Post script : As I made the data manually as A B C D E F etc etc...across the sheets the data vary....for ease of understanding I have changed the data manually sorry for inconvenience.

In attachment i have shown Part A in brown.

Regards,

Vignesh.V

 

[Vignesh Veerasamy]

@NARAYANK991 Sir,

Is the above requirement feasible with macro or need manual workings?

@Vignesh Veerasamy

 

[NARAYANK991]

Hi Vignesh ,

I am sorry but I think you have not addressed the comment I made in my earlier post :

If you wish to automate the process , I think you should take one product , and explain the entire process of how the production plan for that product for the entire year is arrived at considering the given inputs.

Given a figure of 90 , how have you arrived at 110 ? The same applies to the remaining figures for product A.

In case someone else has understood this , they may be able to respond.

Narayan

 

[Vignesh Veerasamy]

@NARAYANK991

Sir,
Sorry for delayed response also for misunderstanding the earlier query.
In case of fig 90 user is arriving at 110 such that opening stock not falls below 15% of opening balance for the next month.
In this case user had manipulated the data such that each time his yearly today and monthly total are getting tallied.

Below is the user feedback for logic calculation.

Dear Vignesh,


We have to arrive a production volume for current month, so as to maintain sufficient opening stock for managing next month volume.

This is the logic behind this. Opening stock percentage vary from model to model. Will be decided based on billing projection.

Also we have to consider the capacity constraint for a model or a group of variants.


Vignesh.V

 

[NARAYANK991]

Hi Vignesh ,

I am still not able to understand ; can we put down the data for product A :

The Billing Requirement for April is 90.

The Opening Stock is 1.

The Billing Requirement for May is 75.

15 % of 75 would be 11.25

Can you please explain ?

Unless we understand the logic behind the calculations , how can we think of automating it ?

Narayan