Embedded Files
This was a personal project I undertook during my time at AB InBev. My teammates and I were spending a considerable amount of time and effort manually analysing our store's transactions and preparing customised financial reports for senior managers daily. These were often real-time reports needed urgently and they occasionally contained errors because our data wasn't structured properly.
My goal was to ensure our reports were consistently accurate and instantly accessible on demand. I automated this daily inventory reporting task by building a user-friendly Excel workbook with data validation and conditional formatting rules that assure data quality, including advanced formulas that provide real-time analytics and eliminate the need for manual data filtering. I also made my work accessible to my supervisor, senior managers and other members of my team via Microsoft SharePoint, and provided the necessary engagement and support as we transitioned from a manual system to this automated one.
The workbook contains three sheets, each performing a specific function. Here's the workbook with only sample data:
Details
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.
Google Sites
Report abuse