Our Recent Financial Management Projects

Here are some examples of the sorts of work we have done with our customers across a variety of financial management reports, highlighting the features we built and challenges we faced along the way.

Contents - Click to jump to an entry

• Using Excel for Financial Reporting
• Excel Case Study: Biffa
• Projects Blog - Index

---

Using Excel for Financial Reporting

For many people, managing finances is the main reason to be using Excel. The challenge is to make the endless columns of figures inherently easy to understand, interpret and update.

We did this for one client using Excel Tables to make reports that were easy to manage on various topics. These automatically update as new data is added to the master financial records. We also included some automatically-generated graphs to compare real and budgeted transactions over the user's choice of time period so that they could access the real message of their large data tables at a single glance.

Database Features:

• Financial transactions entered in standard tabular format and therefore readable as a ledger
• Uses Excel Tables which are easy to add data to and expand as needed
• Analysis and consolidation carried out automatically through formulas
• Uses charts to provide instant information from the data
• Reporting area includes date range inputs for flexibility

Benefits:

• Straightforward and intuitive data entry
• Reports available instantly
• Flexible reports
• Easy interpretation of performance

Excel Case Study: Biffa

Nearly everyone is familiar with Excel spreadsheets which are very widely used throughout industry and commerce. It is easy to set up a simple spreadsheet and obtain results very quickly. However, spreadsheets can easily grow and become unmanageable and checking them can be difficult. When using linked spreadsheets, errors in one sheet can permeate through to others. For very large or complex spreadsheets it often makes sense to contact an expert in spreadsheet design.

The trucks and wheelie bins of waste management company Biffa are well known throughout the country. They collect a range of different types of waste, recycling it or taking it to landfill as appropriate. When Biffa was looking for the right tool to assist them with their budget projections and forecasting, they contacted Software-Matters for advice.

Biffa explained that they had a huge amount of data that needed careful structuring. Complex rules apply to the way in which they account for plant and vehicle depreciation and capital expenditure. Biffa wanted to be able to apply local or global percentage increases to any or all of their costs and income streams to predict next year’s budget, and help with forecasting the resources required. Local managers need to be able to access and change the figures for their depots, and higher level managers need to be able to view the whole picture, with consolidations at various levels.

Biffa’s work is subject to seasonal variations. For example, they need more agency staff at certain times of the year, and the proportion of different types of waste collected will vary from winter to summer. The solution needed to allow this variation to be incorporated into the budget projections.

Software-Matters provided Biffa with a suite of linked Excel spreadsheets to handle their budget projections. The spreadsheets use complex formulae to calculate each depot’s monthly income and expenditure in a wide range of cost areas. Regional sheets cover all the depots in a region, and are consolidated upwards towards one final spreadsheet showing the overall picture. Users can easily view and print budgets for individual depots or regions, preformatted and correctly laid out and labelled.

In a multi-user situation, good spreadsheet design includes controls and protection to prevent users from being able to make inadvertent changes or mistakes, and to enforce consistency between users, so Software-Matters ensured that all sheets are appropriately protected, and that data entry is only possible in the correct areas.

Software-Matters used Excel macros - sections of Visual Basic (VBA) code - to provide buttons on the spreadsheets to help with routine tasks. For example, using the button provided to print a sheet will ensure that the page is correctly formatted, and that any areas of the spreadsheet where no figures have been entered will not be printed, an important consideration when individual sheets run to 20 or more pages, and each spreadsheet may contain 10 to 15 worksheets.

It is important to design spreadsheets with a view to the future, to allow flexibility and evolution when systems and methods change, and requirements and the type of reporting required may vary from year to year. Software-Matters designed the first set of spreadsheets in the mid 1990s, and have evolved them over the years to cope with changes in reporting structures and accounting practices.

Biffa have been using these spreadsheets for about 15 years now, and find that having all the information at their fingertips helps them to increase efficiency and stay in control as the business grows.