The challenge

While managing the project management office, one of Louise’s tasks was to receive on a monthly basis all project finances from the finance team in the form of an automatic Excel spreadsheet. This would then to be sent to the project managers for review, with a summary of where each project was to date.

After having several meetings with the finance team, it was established that both departments used and needed slightly different formats. Whilst the information being sent out was correct for the finance team, it didn’t support a requirement for Louise. Something that was later agreed couldn’t be accomplished easily. This meant she would have to manipulate the information being extracted before sending it on to all the projects managers. This process would take up to a day a month to accomplish.

The problem was not big enough to warrant a change, as outputs were still being created, but it was getting worse with more projects being created.

The solution

After an initial discussion, a solution was suggested which took under two hours to complete.

This solution involved utilising Excel’s more advanced capabilities. A spreadsheet was created in the form of a view. This view would look at a source (in this instance the original spreadsheet), extract the information required and summarise it in the requested format. Then additional calculations were added which created the missing requirements. This could then be copied and sent to the relevant individuals.

Now, Louise would simply have to save the newly received file from finance into a named folder, open the view spreadsheet which automatically updated, copied, pasted and sent the information on. This new process takes under five minutes to be completed end to end, saving a day a month.