Problem: Too many spreadsheets
Project forecasts are often time consuming and complicated. Multiple spreadsheets and other sources of data complicate the process of accurately forecasting a project’s costs and schedule. Projects often get overwhelmed with multiple spreadsheets, making managing and controlling the project very difficult and time consuming.
Project forecasting with spreadsheets is often viewed as being easy and that “everyone can do it”.
However, a spreadsheet doesn’t link in with all your other data. It is not a database.
Modern “shared” spreadsheets do allow multiple people to edit them, but in many cases the spreadsheets are stored such that it is only practical for one person to edit them at a time.
People then often send the file to others to view. Those people edit and send back or to others, often meaning there are numerous versions of the spreadsheet file being sent around. This creates a problem in that you don’t know which is the most recent master version and whether it has all the relevant information.
I also find that project controllers or project managers often have to spend a lot of time checking spreadsheets to ensure the formulas have not been accidentally changed or deleted. Even then I frequently see “final” dashboard reports presented to senior management that have errors in the formulas such that the forecast estimate to complete or other totals were significantly wrong.
I frequently have to do multiple status or dashboard reports to multiple different project stakeholders. In fact, I find that the higher up I get in a company, the more time waste filling in spreadsheets or reports. Some weeks up to half my time is spent responding to requests for bits of information about the status, costs, forecast etc of particular or groups of projects.
Few or any of these are automated by pulling the required data out of a database, or by generating a master dashboard report of the project or set of projects.
Normal spreadsheets are very powerful tools for manipulating and displaying data, but I believe they should not be used or relied upon for working with and displaying information that frequently changes, has multiple users, or which have multiple inputs or outputs.
I think they are better used for “one off” purposes, such as for unique calculations or working with data that is not done the same way every time.
Solution: Change to a project management software
I believe the best way to manage projects (especially for forecasting) is to use a good project management software, rather than spreadsheets.
To me this means a database based system instead of a spreadsheet.
There are plenty of high quality competing project management systems available in the market. Most offer software as a service (SAAS) type subscriptions at a per user per month price. This makes assessing the cost of those systems very simple. Most modern SAAS project management systems display their prices clearly on their website, however I do find that some of the older systems or more enterprise focused systems don’t, they require you to seek a quote instead.
Project management software (rather than spreadsheets) has many advantages (some of which I list further below). To me, the biggest advantages are that they allow a consistent method of managing all projects in your company, provide dashboard reporting so that senior managers can see the status of all projects or parts of a company at a glance, and they provide a “single source of truth” for your project.
All of this amounts significant cost and time savings. By having all your data in one place you don’t have to constantly search for the most recent spreadsheet to get the project forecast or status. This makes project controls much easier too.
It is very difficult to do the above with spreadsheets.
Some database based project management software systems have been developed to look and feel a bit like spreadsheets for ease of use. For example Smartsheet and SmartTask are systems that you could use for project management and which overcome many of the disadvantages of normal spreadsheets.
- Most people are familiar with how to use them
- The basic functions are very easy to use and learn
- There are lots of good tutorials on the internet showing you how to use them (for simple and very complex tasks)
- You can send the file to people easily
- They are accessible to everyone (most formats can be read by most spreadsheet software)
- They are usually not easily linked to other data systems.
- Projects (and whole companies) often have too many spreadsheets covering too many areas. This can lead to duplication of information and work, mismatched information, and lost information. Sometimes called “death by spreadsheet”
- Spreadsheets can get very complicated if you use them to link multiple information types and sources. You can end up with one or only a few people knowing how to source or display certain information about a project.
- The formulas and condition settings with spreadsheets are easily changed or deleted by users, often resulting in errors in the calculations.
Project management software advantages
- They allow a consistent method of managing all projects in your company
- You can usually store and link most or all of your project management information in that one system.
- They can usually link with other data sources.
- A project can centralise all their data to one “source of truth”.
- A program, department, or company can usually display summary dashboards of chosen information of all the projects below them. E.g. Total spend rate of the department, total project tasks allocated to a specific person, outstanding or overdue tasks for a specific person, department, group etc.
- Dashboard reporting is easier and can be automated.
- You can usually output into your required format (spreadsheet, pdf, etc) for dashboard reports (if you don’t want to use the in-built dashboard reports).
- A “dashboard report” or other template can usually be set up to calculate and present project data consistently across all projects.
- Most good systems have tutorials and instructions built into the software, so it is easy to learn.
Project management software disadvantages
- There is generally a steeper learning curve compared to simple use of a spreadsheet, as most people are already able to use spreadsheets to at least a basic level.
- There are many competing project management softwares, many with very different styles and methods of use. This means switching between them requires users to learn the new system again. Whereas most different spreadsheet software are very similar and most people can switch between them with little learning required.
- There is generally a monthly or yearly cost per user for this project management software (usually online software as a service). Most spreadsheet software has or is moving towards this model as well, but generally the cost is lower as it is part of a larger package of software (e.g. Microsoft Office package).
How to get people to use project management software
I believe there are three things a company needs to do to make their chosen project software be accepted and properly used.
- Demonstrations (sell it):
- Provide demonstrations of the benefits and features of the software.
- Make plenty of screencast examples of using the software for your companies specific needs (preferably by actual project managers in the company).
- Show examples of
- Provide in person training sessions of the main uses
- Make screen recordings of use cases (specific to your company)
- Choose a software with good tutorials, guides and instructions built in or clearly linked with it. (They must be easy to use and easy to access)
- Get senior managers to require all project information to be presented in the chosen project management system.
- They should require dashboard reports be done from only data within the system (or automated from it)
I often find that good training and easy to use and easy to access instructions on aspects of the software can overcome most of the problems of getting people to actually use the software. Plus senior managers requiring all project information to be stored
What you want in a project management system:
Not all the systems available cover all everything you might need, but most cover the things you need daily and then some link to accounting systems for the financial areas like purchase orders and variations.
Some of the things you may want project management software to do are:
- WBS planning and associated gantt charts
- Task tracking and allocation
- Cost tracking
- Cost forecasts, including:
- Costs to date
- Estimate to complete
- Estimate at completion,
- Spend rate
- Variation (areas over or under budget)
- Earned value
- Time tracking
- Change management
- Risk tracking
- Dashboard project status summary
- Dashboard summary of multiple projects filtered by whatever is needed (PM, department, size etc)
- Preferably of all the above areas if needed
- Contractual items:
- Purchased order value
- Variations and change orders
- Potential variations
- Being able to split projects into areas or stages, e.g:
- Concept design
- Detailed design
- And being able to track by design package
- Construction planning
- Client or stakeholder access (guest access) – only in the areas you want
- Good tutorials or instructions linked from within the software, where you need them.
- Integrations/links to commonly used systems that you use (e.g. timesheets, accounting). These must not just be a hyperlink, but actually integrating the data, so that automation is as complete as possible.
Use project management software rather than spreadsheets for managing projects and especially for managing your project forecasting.
A good project management tool will save you enormous amounts of time and stress. It should make your job easier and make your project run more smoothly, cost less money, and meet schedules more reliably.
There are hundreds of online and offline systems to choose from.
Some of the project management systems I like are below. None are perfect or have all the features possible, but all help you reduce your workload and be a more effective project manager: