Excel macros to consolidate and perform year-on-year calculations

A Private Foundation that has several residences or care centres for people with physical disabilities, asked us to develop several macros that make the consolidation of accounting data, and the interannual calculation, between sheets and the Excel books with which they keep the operating accounts of the centres. That is to say, to collect the data of each centre, in a final sheet, comparing data with an Excel book corresponding to the previous year.

We show the sheet corresponding to one of the residences and the sheet of Total:

Macros Consolidacio Interanual Residència

Macros Consolidacio Interanual Total

To make it easier for the user to use the macros, we created a menu sheet, with the buttons to perform the Interannual calculation and the Data Consolidation. We showed the menu sheet:

Macros Consolidacio Interanual Menú

Finally, the data are shown in a Global sheet, which compares the data of the current year and those of the previous year, making percentages of deviation:

Macros Consolidacio Interanual Global

Application made with Excel. To use this application you need to have the MS Excel program. The application is made in Catalan language. Note: The data inserted in the images are fictitious.