Project Excel template for Sales and Operation plan

Using excel template for sales and operations plan

Located on the second level of production planning, the MPS, Master Production Schedule: Decomposes aggregate data (product families) of  Sales & Operations (S and OP) plan with reference individual products. It plans over a shorter sub-periods with the finest real demand and therefore production of each finished product. It reveals the state of stocks into finished products and their evolution over the planning horizon.

Dice are not nice for those who don't believe in god. For those, it is the reason.

Dice are not nice for those who don’t believe in god. For those, it is the reason.

Net requirements calculation

The process of calculating net requirements in references finals is used by MRP to calculate  the components and required materials.  The MPS data are generally more realistic than the S and OP because they are no longer solely based on forecasts and includes firm orders. There rules to consumme forecast based on received orders. At the time of its preparation, many orders were already registered. Which reduces the uncertainties in the planning.

Master production schedule

Stocks are used as regulatory element of the whole production process. Production effect in the context of a MPS is stretched by flow. The net requirement of the MPS has thus two main functions: Meet all firm orders either by the available stock, either by initiating a work order if stock is insufficient and closing stocks of the way back to the level planned for early next period (stocks level management role).  The product view shows current production plan and what should be done to meet customer requirements and stock objectives.

Product forecast and net requirment

Although the production of one unit is triggered by a prior request (pull flow production), we keep a minimum inventory and fixed production quantity for the work in progress to regulate and balance the use of production resources , hence the notion of frozen period given the various production constraints of real production (limited capacity, insufficient resources, fixed working hours, random events …). The management of a Master Production Schedule is a compromise between several objectives in each period:

Will be the actual demand be satisfied?

How much stock should there be available at end of the period?

How much production do we will make during this period?

How much ressource are required? (should I use 2 or 3 shifts)

What will be the workload ? Can I plan maintenance ?

Available to promise

Available to promise KPI can also be calculated in excel to give the part of the stock is already consumed with orders and where there is a sales effort to concentrate. Some tools also modelise a gray part for regular orders but not confirmed.

In period 10, there is a shortage of 8 units based on the real production. In fact, the term real production is not correct. It is rather an production objective based on the negociation beween sales department and purchase/production departments.

The ressource table permits to control the loading of business lines or ressources. A production system with finite capacity (limited production resources), inventory management components and materials plays an important role in steering the MPS. Delivery times required by customers are usually short, we organize the process so as to have always a certain amount of stock at the beginning of each period if production does not permit to fulfill all requirement in the given period.