Production Planning in Excel

By Vicki Holmes

For any business, creating a production plan can be challenging. Using a spreadsheet such as the popular Microsoft Excel 2007 can simplify the task by performing complicated mathematical functions for you, creating charts and organizing user data so that it can be easily interpreted. Additionally, Excel has advanced functions that can be helpful in production planning.

Enter the Data

Enter the information into an Excel spreadsheet that you have to work with. It is always best to use the first row as a header to list your topics. The first column on the left of the spreadsheet will be used for categories. For example, let's say you are planning the production of a new product release for the first quarter of the year. You might name your workbook "New Product Production Planning." Cells B2 through E2 might read January, February, March and Total. Cells A2 through A4 might be your categories, Shipping, Advertising and Staffing. The body of the sheet would be your projected costs.

By entering your data in this fashion, you have set your worksheet up to work with Excel's planning features.

Using "What-If Analysis"

After entering your data, click on the "Data" tab in the Ribbon. In the "Data Tools" section, you will see a drop-down menu for "What-If Analysis." What-If Analyses are features that you can use to perform analysis on your data based on queries.

The Scenario Manager, for example, allows you to ask Excel to show you what would happen if a certain scenario were in place. Scenario Manager would be used at times you wonder what would happen if a certain scenario occurred. It can give you a summary page based on what would happen if you made those changes without changing the data in your original sheet.

Goal Seek allows you to ask Excel to show you what you need to change in your production to reach a certain goal. For example, if you had a goal for your staffing budget to fall under a certain total amount for the quarter, Goal Seek could tell you how much you'd need to spend each month to reach that goal.

Using Charts

Creating an Excel chart is helpful in production planning because it gives a visual representation of the data. To easily create a chart in Excel, simply hit the "F11" key on your computer keyboard. Excel will automatically create a separate tab with a chart based on your data. You can edit and tweak the chart as desired.