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.