The first sheet is for data entry. I set conditional formatting and data validation rules on key columns to assure data quality. For example, if a user enters an invalid stock code on column M or an unrecognised name for a department on column C, they will get an error alert and not be allowed to continue with the entry. I also developed a formula on Column Q that causes it to ignore its default multiplication rule and return "0.00" each time it finds the word "returned" or "zero" on column I. These words also cause data in the entire row to automatically appear in red font. This feature comes in handy when a customer returns an item or when an issued item’s quantity on inventory is zero. It neatly maintains cost accuracy and provides a clear visual reminder of the peculiar situation.

The second sheet displays real-time analytics. It summarises the entire data from the first sheet into an intuitive dynamic table and performs analysis to generate detailed insights, such as the cost breakdown of arrears (unposted items) to date and cost breakdown of spending (issued items) in the last 24 hours. These parameters update automatically with each passing day and month, returning "N/A" for previous periods since they're no longer applicable.

The third sheet displays real-time results of predefined filter combinations, e.g., unposted normal-running spares not issued this month, project spares posted this month but issued previously, etc. Once the custom name of the filter combination is selected from a drop-down menu, the sheet automatically displays the entire filtered dataset itself in real time and generates the total cost. This eliminates the need to switch to the first sheet and manually filter the dataset multiple times on several columns to achieve the desired filter combination before finding the total cost. This sheet contains Excel functions, which work only in Microsoft 365 and the latest versions of Excel.