Financial Performance Tracker

Excel model for tracking multi site financial performance

Opera North are a national operatic company based in Leeds and each year they put on a wide variety of performances around England. Revenue from these performances is recorded at several different venues which is subsequently sent to Opera North in different formats and has historically been manually combined to create a management report. This report took several days to compile, so Opera North came to Xlteq in order to reduce the workload by automating the data input and report creation. Additionally Opera North required several new reports to be built from the data.

After an in-depth conversation with Opera North about their requirements, it was discovered that the venues which provided the data had very different output formats which would require specific loading methods for each one. It was also realised that the data would require cleaning and correcting depending on the venue and performance, ticket type, etc

In order to ensure the data would be stored in a standardised format, several small routines were coded which would load the data into a checking tab and at this point ensure the data was transformed into the correct format for reporting on. Examples of the corrections made include changing the ticket type or removing specific types altogether.

After loading the data and transforming it into a standard format, we were then able to check and enhance the supplied data. For example enhancements included:

  • Changing performance names into a standard name, as different venues recorded performance names differently
  • Changing the ticket name into a standard name as ticket types are recorded differently in different venues
  • Populating a “Complementary Ticket” field if the ticket type is complementary.
  • Updating the venue name to a different venue (as some venues included sales from other venues but stored the data under their venue name and not the other)

Example of table containing the alternate names of Operas provided by venues and the correct name to use.

The types of checks which were performed included:

  • Checking all ticket types were recognised
  • Checking venues and performances were correct.
  • Checking if the data had already been loaded for this reporting period


After loading the data the user is able to review the loaded data to double check it has been loaded correctly and at this point act on any error messages they have due to incorrect data.

Once happy with the data, the user can confirm the load, and the data is added to the data loaded from previous weeks.

Reports are driven from pivot tables, an in-built Excel functionality, meaning there is less reliance on code or complicated formulas which can go wrong (although several additional formulas were required to create the reports).

Reports were either hand-built tables or used Excels in-built charts to display the appropriate data. Some reports also included additional data supplied by Opera North (for example Budgets, Seat Numbers, etc). This was achieved by providing a simple table which could be populated by Opera North and fed into the appropriate reports.

Example of table allowing additional data to be used

Contact us to find out more about our Excel and Access Database development and support.


Contact