Using MS Excel Filters & Subtotal Feature in Maintenance Planning & Budgeting.

Excel 2007’s Filters can be used to plan your maintenance activity and hence maintenance budget. Filters provide us with option to filter in between two instances of time thus yield relevant data that you can use further.

The Filter Option in Excel 2007 can be accessed by going to Data Tab > Filter or Data Tab > Advanced Filters; however for this post we will keep ourselves restricted to Filter, that is more appropriate for our use.

We first need to setup a database in the shape of a list since filter work best with data in this shape. The Header row contains titles like S.No. , Machine, Item/Description, Worked Date, Life, Next Due, Rate, Quantity, and Total.

S.No will be used to keep data in chronological order as you enter it. It can be used for sorting a getting to the original shape of the list after it has been sorted for some other criteria. Machines, Item/Description, Worked Date are self-descriptive. Life should be in Months. Next Due is calculated by using EDATE() function that adds months to a certain date giving us next due date, Rate & Quantity are quoted as it is giving us Total in the last column.

Setting Up the List:

The list can be easily setup and is available in the Sample File.

Using EDATE() Function:

The function has following syntax: EDATE (start_date, months)

The Start Date is the Worked Date, a month is Life in months, and the formula is entered in column for Next Due.

Getting Data in between Two Dates:

Let’s assume that Today is 1^st of July and we want to find the maintenance activities that are due this month (July, 2012). This will help us manage the inventory required and plan our activities more effectively.

Go the Data > Filter > Date Filters > This Month

In fact the last step of this can be changes for Weeks, Years, Quarters, Day-Before, Today & Day-After, and In Between Any Two Dates. So you can plan for the maintenance activities in next quarter way ahead of it, you can have  detailed schedule using Months, Weeks even Days.

Example:

Let’s consider an example. Download the attached sheet. This sheet enlists few machines with data against each of them and details, along with quantity and price. We will use filter the data to see:

> What are the activities that are due this month, next month or this quarter?

> What has been the Maintenance Budget for this month, Next Month, This quarter etc?

Scrolling and following the procedure reveals that there is no maintenance activity / replacement activity is schdudeld this month, neither for the following month. However we needed some replacements in the last days of the first quarter of the year. See the following picture:

When we select the Totals Column we can see the Total as Auto-Sum or alternatively we could use subtotals for this purpose.

Go the Data > Subtotals

… To see the dialogue box shown in the picture below and add subtotals for each machine under column Totals.

Conclusion:

There are variations to this method, you can add more detail to the workbook adding more levels of detail to make thing more practical like Department, Lines, Machines, Assemblies, Individual Parts etc to pin point things.

Download the Workbook