CASE STUDY – PROJECT MANAGEMENT SYSTEM
Case Study – Our Own Internal Project Management System
Yes, you read correctly. We have decided to use our very own internal management system as a case study. People often contact us and ask what project management system we can create for them, however they usually underestimate what we can do. We hope that this case study shows you what we are capable of, but please don’t let this limit your imagination, we created this system for our requirements. Take a look at what we have done, and think about what we could do to help you manage your projects more effectively. Just to let you know, the spreadsheet involved in this case study is macro-free and consists of 4 sheets. Take a look.
Firstly, we have the contact sheet. This is where we enter in most of the information. Job number, client details, project confirmation date, number of sheets and the start and completion date. The spreadsheet then shows us how many days we have left, which then shows how far we are into the project, both in time and number of pages completed. This enables us to make sure that we stay ahead of schedule. All of the information in the white cells below are manually entered, but it all gets fed to other parts of the spreadsheet. The grey cells are automated.
We then complete the rest of the information sheet, which is the job heading and job description. We just enter a description of the job, however you could have this more advanced. You could have some selection options which could add in certain chapters. For example, if you have personal and business clients, you could have a different paragraph to be included for the various types. Just select the job type, and the respective paragraph would appear, with the relevant information! What we have here is a very simple version, don’t limit your options to what you see here.
The next sheet is the quote. Do you find that you spend ages typing up a quote, or using old ones and then changing details as per the new job? This can be done in a fraction of the time. The quote template is already laid out and populated by the information already entered, this means that all I need to enter is the date, and the price breakdown. The rest is already populated. Most of the quote is standard terms and conditions, and the client’s details already entered. You could even have certain terms and conditions showing, depending on selections already made, if you wish.
We select the type of entry (deposit), and input the percentage and the amount, the rest is automatically generated. The beauty is that the information entered here is not only for the quotes, but goes to create the invoices and payment schedule too. This can then be saved as a PDF and sent to the client.
The next sheet is our time schedule and report. We need to know how much time we are spending on each job, and we also need to track our progress. We need to know how far along we are with allocated time, project progress and money earned. On this sheet, we allocate the number of hours we expect the job to take, and then as we go along we capture the time that we spend. The spreadsheet shows us how much we have earned (based on the invoices paid which we will get to next), it shows us how much time we have spent (based on the time inputted) and it shows us how far the project is (based on the number of pages done). This is data gathered from all of the pages of the spreadsheet, not just what is entered here. This is one of the major advantages of having you entire job card on one spreadsheet, and not on various document types, you can enter data once and use it for various calculations.
The next sheet is possibly one of the most useful and important, it is where we track the payments on the job. Now we have another spreadsheet where we capture each invoice number and the respective details, so all we need to do here is enter the invoice date and number. The spreadsheet calls up the information entered on the quote, and then uses the invoice number entered to check the invoice master list, in order to establish the status of the payment. This will check that we have invoiced the correct amount, and that it has been paid, and will alert me if there are any mistakes. The information gathered here will also populate other sheets, so that we can tell the progress of the project based on payments.
The last sheet is the invoice itself. The same problem often arises with invoices as with the quotes, people tend to use old documents and ‘update’ them as per the current job, often resulting in mistakes. We have the invoice template already, so all we need to do is select the invoice number from a drop down list. The list contains all of the invoice numbers relating to that particular job. Select the number that you wish to display, and there is the invoice. Populated as required. It even shows ‘Paid in Full’ if the invoice has been paid.
This way, you don’t have to have an invoice template for each invoice, just one, which populates according to the selected number. Once the number has been selected, we then ‘save as’ a PDF, which we can keep as our records and send to the client. As you can see, the invoice states which invoice it is in relation to the job, and what the total value of the job is. You could have the invoice laid out how you wish. As per all of the documents, the layout and company logos are as per your design, we don’t have any ‘standard’ templates.
So there you have it. We also have certain information linked from this spreadsheet, to a management spreadsheet, where we can see a review of all of the jobs at once. That list shows us vital data from each project, on one sheet, all colour coded. That means that we can open it up and see any ‘problem areas’ at a glance. I’m sure that you will admit that knowing where you are on all of your projects is useful to know at the very least.
We hope that this case study has inspired you to get us to create your project management software. This was all created using Excel, and is a macro-free spreadsheet, imagine what we can do for you. Please use the links below to visit our website and contact us. We have videos, free downloads, brochures, and a wealth of information about what we do on our website. You can also sign up to receive our monthly newsletter. If you think that your business would benefit from a spreadsheet like the one shown here, please contact us using the links below.
Richard Sumner