Your 'Go To' for Custom Excel Solutions

Case Study – Excel Database

Keeping track of stock inventory in the hospitality industry is critical to managing cash flow and ensuring the rooms are properly maintained with the expected inclusions. This combined with different staff cleaning and maintaining rooms keeping track of stock and maintenance activities can become a full time job on it's own.


The Problem

This Hospitality business was manually tracking the stock and maintenance activities across 300 rooms. This included the regular items for a room like soaps, coffee and tea. Also being tracked were items required to fix issues within the rooms like a leaking tap or a broken light.


The existing process was managed on paper with job sheets and stock lists being updated as each room was refreshed with stock each day. Then at the end of each month the administration team would reconcile the stock to purchase orders. Finally for the rooms that were owned by investors an invoice was drafted to cover the expenses to stock those rooms for the month.


The Solution

The solution was to automate the tracking of stock items for each room that were used in the resort. A centralised Excel workbook was created to store all stock used for room. Then each day as the resort staff used items in a particular room either for replacement or as part of a job to fix something it was recorded in the spreadsheet for that room.


The stock details, including the price and supplier were recorded in the spreadsheet and when required reports could be run to generate a purchase order for replacement of any items that were running low on stock.



The Benefit

With the introduction of this workbook the administration team saved hours of work each month. Manual paper based reconciliation was no longer needed.


Invoices could be generated for the investors to detail all items stocked into their rooms for the month and if required reports could be generated for previous months.


A key benefit now for the resort management was the ability to see the current stock levels for all consumables used in room maintenance.