Excel Development

This particular client, a small company, does project work for different non-regular customers. Typically a project will take one to two months and several projects are worked on concurrently. The company wanted to know the profitablity of any particular project, and despite using Sage Accounting to manage their accounts they couldn't easily work this out.

One of the reasons why Sage could not do this, was not all the data was in the Sage database. To calculate each projects profitability would take perhaps two hours effort and so was seldom calculated.

The solution was to create a macro enabled workbook that could import all the costs and revenues from both exported Sage reports in Excel format and the additional spreadsheets. It would then for each project summarise the revenues and costs and calculate the profit. Additional reports included a one line summary for each project based on month and manager. As a side benefit it was also possible to create a trial balance.

Now the solution takes less than 1 hour for all the projects, which is mostly preparation time, with the actually macro run time being less than 1 minute.

As a consequence, the report can be easily generated weekly or monthly, giving the managers a valuable insight into how their business is doing. The managers can quickly see how the projects have performed and drill down into the lower profit projects to figure out what the issues are.

There have been several similar cases where a list of items is spread over two data areas, with some items being in one data storage areas, some in the other data storage area and some in both. What is needed is one definitive list of all items, plus a list of what is missing in each individual data area. The most recent case, involved capital expenditure items, with a few thousand items in a company database and a few thousand in a spreadsheet. The twist - and this is often the case - to match the items required comparing an id field which was in slightly different format in each data store.

The solution was a macro enabled workbook that would read both data sources, match items against each other to produce a definitive list, and produce a list of exceptions that are not in both data stores. The macro would take seconds to run.

Then it was down to the data owners to look through the exceptions and try to tidy up the original data sources, so that on the next run, the exception list is a lot smaller. It could be argued that the situation should never arise, however in practice they do arise and often for legitimate reasons, for example when two companies merge.

In organisations where projects come and go with people resourcing being managed by project managers, getting realistic headcount plans can be quite tricky, particularly if the company is in a recruitment phase. The project manager starting a new project can work out his resourcing requirements to meet a certain deadline, if he is understaffed he will have a recruitment plan, for example he estimates he can recruit 2 people per month over the next six months. This might a reasonable rate if the project is recruiting in isolation. But problems occur if, for example, a specific site has several projects all trying to recruit the same type of resource, and in this situation recruitment rate may not be realistic. On top of that often budgets are based on headcount, then the company may be reserving money that it will not spend, and perhaps forgo some other opportunity.

This can be resolved if someone higher up the corporate ladder checks the reality of all the headcount plans by consolidating, challenging, prioritising and getting them adjusted, so that they are realistic and achievable. Ultimately in extreme cases taking drastic action such as cancelling projects.

Excel can't solve this problem, but can help. An Excel enabled workbook was used to create an Excel template for each project manager which they would then fill in their headcount plan for the upcoming months. Each month they returned the filled in template, and the management team then used the macro workbook to aggregate all the plans into one huge plan which could be reviewed and follow up discussions with project managers made. Finally the macro workbook regenerated each project managers plan so that it was ready for the next month, and returned to the project manager. The project manager wouldn't have to re-enter all his plan, just tweak key elements.