Formula for Production Plan with Date Parameters

[Kevin442]

I am looking for a formula that calculates a Production Plan (supply) based on a Shipment Plan (demand) for a given Product in a given Fiscal Year. My goal is to calculate how much of each product (#s 102-107) should be produced in future years to meet consumer demand and not leave any inventory gap prior to the next product release date. I have attached a file to provide an example of the data and table I am working with. Below is a list of requirements for the formula:

Formula Criteria
- Products must be sold in numerical order. For example, Product #100 must sell all 1,000 units in inventory before transitioning to Product #101 (column A).
- Products cannot be sold prior to their given Production Release date (column B).
- Shipments in a given Fiscal Year must equal the "Total Shipments" (row 3).

File Notes
- Shipment Monthly Run Rate (row 4) represents Total Shipments (row 3) divided by 12.
- Products 100-101 have already been produced.
- Products 102-107 will be produced in future periods based on the calculation in this model.

I have been able to set up formulas to calculate the product plan, however I am having trouble layering in the time periods. I appreciate any help!

 

[NARAYANK991]

Hi ,

Can you give more information , by specifying which cells need to have formulae in them ?

You say :

I have been able to set up formulas to calculate the product plan

Where are these formulae ?

What does the following mean ?

however I am having trouble layering in the time periods.

Narayan

 

[Hui]

Kevin,

Firstly, Welcome to the Chandoo.org Forums

I can solve the problem like this:


But I'm sure that isn't the solution you want,
but it meets your supplied criteria!

What are the limits on each Product # in terms of total qty able to be produced?

 

[Hui]

I suspect this is the solution?


Note I have added a Max Qty column for each product
see attached file: