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 1st 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.
