Excel Production Planning

Written by david ingram| 13/05/2017

Production Planning in Excel (eyeglasses and chart image by timur1970 from Fotolia.com)

Microsoft Excel is a powerful and flexible spreadsheet program that can be utilised for a wide range of business applications. Production planning methods are designed to ensure that production processes operate in the most efficient and profitable manner while reducing waste. Production planning often involves subjecting large sets of data to complex mathematical models, making Excel a highly effective tool for the task.

Benefits

The ability to employ several production planning techniques in a single software environment carries distinct cost benefits. Not only can one save money on software purchase costs, but training costs for alternative programs (to accomplish the features available in Excel) would far exceed the costs of using software that is already likely to be familiar to employees.

Linear Programming

The Excel Solver is a powerful tool that can be used to solve complex linear programs. The Solver can determine the optimum production quantity of a mixed set of products to maximise profit or minimise cost, subject to a set of defined constraints. Additional uses of linear programming in Excel include determining the most economically beneficial delivery routes and the optimum layout of manual production lines.

Critical Path Method (CPM)

Excel can be used to construct and visualise the critical path of any complex production process. After listing each activity, its necessary predecessors and its time constraints in a chart, one can conveniently view critical activities and slack times while identifying non-value-added activities that can be trimmed from the production process.

MRP software packages can be costly and include steep learning curves, but Excel may be used to perform a number of MRP functions, including the determination of the EOQ. EOQ calculations can be quite complex and can shift frequently based on a variety of factors, including changing demand patterns and cost structures. Excel can be used to create spreadsheets specifically designed to determine when to repurchase materials and in what quantities, so as to minimise holding costs and ensure that all required production inputs arrive on time.

Considerations

There are several ways to proceed with establishing a production planning system in Excel. For the most custom-tailored experience, you might attempt to create your own spreadsheets, entering the formulas and layout required to meet your planning needs. Larger businesses may consider hiring in-house developers to create robust production planning spreadsheets, or they may contract with a third-party service provider to craft a planning system that meets their specific needs. Small business owners may find it more beneficial to take advantage of the wide array of Excel production planning templates available free of charge on the Web.