Projects where I proposed and executed business analyses to identify and articulate the need for change in specific organizations areas, and to facilitate that change, maximizing value for my department and giving our company sources of revenue, ultimately improving the way we do business.
Proposal and development of an Excel analysis for buyers and planners by category, brand, and competitor to improve inventory allocation and to provide insights to leadership on decision making in advertising, marketing, business strategy and operations areas.
Done by the use of macros with data extracted from Microstrategy and historical competition flyer data.
Situation
Sears was in need to optimize inventory allocation to save costs, specifically in the Refrigeration Category in Major Appliances, in order to have less warehouse space being used for on hand inventory.
Task
The task was to determine the top selling categories (such as top mount, French door) by brand, store and region. Also, to analyze customer demographics within a radius of 20 miles from stores in order to give a more comprehensive view of specific consumer behaviour.
Action
A price-point analysis in Excel was executed for competitiveness using historic sales, promotional history from flyer databases, and demographics. The analysis would point out pricing competitiveness, turnover and best sellers by category, brand and region, suggesting better inventory allocation and price adjustment for better decision making by the buyers.
Results
As a result, inventory was allocated more efficiently, thus using less warehouse space, more competitive pricing and better turnover.
Overall, the matrix helped buyers to make better buying decisions for better inventory handling and more competitive pricing.
Example: Quebec customers prefer French Door refrigerators across the entire province, while most of eastern Ontario close to Quebec, prefer Top Mount.
Project to show the efficiency of space use, providing insights for improving store layout, merchandising, staff performance and more, to improve the dollar value of sales per square foot.
Done by the use of macros; Excel custom formulas and integration of Excel with Autodesk Revit.
Autodesk Revit is a building information modelling software tool for architects that show areas with square footage and structural, mechanical, electrical, and plumbing data.
Situation
The square footage performance analysis that was being executed at Holt Renfrew used printouts from the designers and store architects and the data had to be manually input into Excel. Afterwards, when the square footage in the analysis was agreed upon, the design team would provide the floor plans to be included in the report, which took time since it was a team outside of the BI group.
The data gathering was also a complex process; since the stores fixtures were constantly being changed, when the data was being collected, an overseen fixture change in the middle of the season might appear and impact several files, store plans included, thus cascading into issues with data accuracy, such as store plans with incorrect square footage and misleading KPIs.
The BI Manager needed a faster and better approach to execute the analysis and to rely less on the design team in order to have the floor plans available as soon as possible. Furthermore, the data processing needed to be less manual to shorten the time to react when overseen updates appeared.
Task
Take ownership of the floor plans shown in the existing analysis by creating and editing Revit files for use within the BI team and rely less on the architects and designers, thus being able to update the printouts delivered to leadership and streamline the data process, reducing processing times and improving accuracy.
Action
Revit and Autocad files were provided by the store designers as the first step of the project.
After a brief training with online tutorials, I started to create new Revit files for each store to update the areas according to historical data and the BI manager’s own recollections. Revit automatically calculated the square footage of a room and we reconciliated the data with previous analyses to ensure the calculations were correct. Rooms and areas were updated using separation lines to set apart sectors with sales from circulation areas and building structures such as electrical rooms, warehouses and elevators.
I found that it was possible to extract the room schedule list with square footage into an Excel file from Revit, creating a whole new, simplified and automated process to execute the analysis, with as minimal manual intervention as possible.
After updating and exporting the room schedule from Revit, a series of macros would transform the analysis file tables and formulas, re-calculating and providing an accurate output immediately.
Results
The consolidation of data processing improved data updates, requiring less time and less intervention on updating all of the files and making it easier to detect any issues down the process. The store maps were more consistent and visually appealing to the stakeholders who received the report.
Processing time was improved from several months to a few weeks.
Technical Data / 
Autodesk Revit website: https://www.autodesk.ca/en/products/revit/
Add-in to export schedule data from Revit to Excel
