“If you fail to plan, you are planning to fail!” —Benjamin Franklin
Among the many titles in Ben Franklin’s vast career, I’m fairly certain you will not find “Project Manager.” However, you can be sure that he understood the value of planning to ensure the success of any activity requiring multiple tasks.
Benjamin Franklin didn’t have the benefit of computers and couldn’t leverage Microsoft Excel or any tools to aid in organizing his projects… But I bet he would if he was able. So, in celebration of the fact that we DO have access to these tools, in this article, I’ll provide some information about Project Management and more specifically about Microsoft Excel as a tool to help you manage your projects more effectively.
What is ‘Project Management’?
Project management is the discipline of planning, organizing, motivating, and controlling resources to achieve specific goals. A project is a temporary endeavor with a defined beginning and end (usually time-constrained, and often constrained by funding or deliverables), undertaken to meet unique goals and objectives, typically to bring about beneficial change or added value. The temporary nature of projects stands in contrast with business as usual (or operations), which are repetitive, permanent, or semi-permanent functional activities to produce products or services. In practice, the management of these two systems is often quite different, and as such requires the development of distinct technical skills and management strategies.
The primary challenge of project management is to achieve all of the project goals and objectives while honoring the preconceived constraints. The primary constraints are scope, time, quality and budget. The secondary —and more ambitious— challenge is to optimize the allocation of necessary inputs and integrate them to meet pre-defined objectives.
What is Microsoft Excel?
Microsoft Excel is a commercial spreadsheet application written and distributed by Microsoft for Microsoft Windows and Mac OS X. It features calculation, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications. It has been a very widely applied spreadsheet for these platforms, especially since version 5 in 1993, and it has almost completely replaced Lotus 1-2-3 as the industry standard for spreadsheets. Excel forms part of Microsoft Office. The current versions are 2010 for Microsoft Windows and 2011 for Mac OS X.
Cells, Formulas and other Excel Content
Excel is an intuitive and easy to use tool for creating anything from simple todo lists to math intensive spreadsheets. It’s power is in its extensibility. Excel documents are called spreadsheets. Spreadsheets are made up of cells. Cells are arranged in a tabular format of rows and columns. Each cell in a spreadsheet can contain formulas or plain text. There are approximately 350 individual formulas that exist in current versions of Microsoft Excel and these can help you perform calculations and data manipulation. A simple formula can be seen in the example below:
Click on the next page to continue reading and for more information about our example formula… or skip to page 3 to check out the free example download.
Stack Ranking as the name implies is the process of stacking things or people up and applying a 1 to n rating to each. Unlike a standard performance rating, a ranking implies that no two employees should have the same assigned rank and that when you’re finished, someone will be number one and someone will be on the bottom of the pile.
Stack Ranking the members of your team can be a loathsome task. Especially when you consider the fact that many companies use this tool as a way to identify and carry out targeted reductions in force. The issue that many managers face when it comes to stack ranking is that even if you have a solid team of super hero performers, someone will be on the bottom of the list.
Some have argued that there are many problems with the stack ranking system. And I must admit the arguments against are valid.
*** Update September 20, 2012 *** I have released an updated version which you will find detailed here:
The Excel Project Planning spreadsheet has been a handy tool for many of the projects I’ve worked on and I’ve continued to tweak and add features it. Therefore, I thought I’d add this post to talk about some of the enhancements and provide a link to the updated version.
Enhancements from Version 1 to Version 2
Task color coding based on progress against current day
As you can see from the attached image, the Gantt cells are now color coded.
A – Green cells indicate work that was scheduled and has been completed (100%) as indicated in column F.
B – Yellow cells indicate that tasks were scheduled and more than 50% of the task has been completed but the start date is now in the past.
C – Red. Cells are filled with Red when tasks are scheduled to have had work completed but have not yet started and the start date has come and gone.
D – Dark Blue. As in version 1 of the Excel Project Planning Spreadsheet, cells filled with dark blue indicate work is scheduled to be started and completed on these dates.
Today’s Date – What/If Analysis
I’ve added a field and some conditional formatting noted in the above image by label E – which enable you to analyze the project as if today’s date were progressing beyond the actual date/time when you’re viewing the project planning spreadsheet.
Clicking the spinner in cell C29 – either up or down will increment the value of Today’s Date. This will cause the conditional formatting in the Gantt area to show you what the project color coding will look like based on current progress on the date provided in cell B29.
This will also highlight another enhancement that I made to make progress against the current date a bit more obvious. You’ll notice the dates that appear at the top of the Gantt area that are filled with the project dates (eg: 5/19). These will now appear in a Grey filled font as they drift into the past.
All of these new enhancements have been accomplished using conditional formatting combined with functions. To view these, select the cells where the formatting appears and from the home tab, click conditional formatting, then manage rules.
Here’s a link to the spreadsheet download. Excel Project Plan v3 (15127)
I hope you enjoy the new features. Please Tweet, Like or G+ if so! Also, if you have a feature request, feel free to comment or use the contact page to reach me.
I’ve used OpenProj for several years. It’s a nice, free alternative to MS Project. The only problem is sharing the project plans with folks – rarely do people I work with have OpenProj installed. Fortunately, OpenProj gives you the option of saving as a MS Project XML file. This makes it easier but I was still looking for the ability to save the projects in PDF format. This is possible, but only with the paid version of OpenProj. This is when I found the Gantter Project.
Gantter is written to work with Google Apps. It enables you to import MS Project project plans, save them as PDF and even save them directly to Google Docs.
For me, Google Docs and the Gantter Project work best with my custom domain – mlynn.org – but you can use it even if you don’t have a custom domain. Visit Gantter.com to find out more information about this great, free tool to help you manage your projects.
As long as there’s money to be had by gaining exposure on the web, we’ll have to suffer with the less than reputable out there trying to game the system by filling up your blog with lame comments and links to their crappy porn, drug or adult dating web sites. You can minimize the impact of these n’er do wells, however by implementing several plugins and taking certain actions in response to their attempts.
Here’s a very simple Excel based decision tool which I’ve used in many different forms for both personal and business use over the years. In this basic format, this tool will enable you to list work through a decision process between two major choices.
The tool lets you provide two basic choices (cell C2 and cell E2), features of the overall decision (these are typically outcomes or impacts of your decision between the two choices), weights for each feature and a grade for each decision by feature.
The tool also provides a calculated score based on your grades and weights and a nice little chart showing which decision came out on top.
To use the tool follow these basic instructions:
1. Provide your two choices in cell C2 and cell E2. eg: Join Army, and Go to College
2. List the features of the decision as a whole in column A rows 3 through 16. You don’t need to complete a rows. You’ll want to focus on outcomes or impacts of your decision when listing these features. eg: Long term impact on life goals.
3. Weight each feature on a scale of 1-5 based on how much you value this feature in column B, rows 3 through 16.
4. Provide a grade for each decision as it pertains to the feature. For many decisions, this will be highly subjective… don’t give too much thought initially to your scores. Try to record your immediate reaction.
Note: Don’t put anything in the “Score” columns… these are computed fields.
That’s it – very simple as I said. This tool can be built upon in many ways. I wanted to keep it simple initially and provide a Simple Decision tool. If you have other tools you use to help you make decisions, please use the comments field and let me know. Simple Excel Decision Tool (3882)
If you like this, you may enjoy my recent article on Stack Ranking.