Ying Zhang

Account Services Intern (Summer 2022)

1. Account Services Intern, The Estée Lauder Companies
2. Job Functions
   1. Managed ELC Online warehouse and B2C (Aveda, Bumble and Bumble, Origins) sales orders
   2. Run biweekly ELC Online Open Order Reports (OOR) from SAP to Excel, and use Exacta WMS (Warehouse Management System) to update order status, delivery blocks, and expected shipment dates
   3. Run daily Sales Proposal Manager (SPM) on SAP to review and post unposted proposals for B2C orders and ELC Online warehouse replenishment orders
   4. Created accurate orders based on requested location, partner, material, and material quantity
3. Examples of projects (e.g., list the projects you worked on and what you accomplished)
   1. ELC Online OOR Excel Automation
      1. Used Macro, VBA, and Power Query to automate the report-building process (adding columns, filtering, deleting unnecessary order rows, applying formulas), cutting report building and inspection time from 60 minutes to less than 20 minutes
      2. Manually editing and cleaning data and applying formulas –> Ctrl + Q, W, E, R
   2. Daily Gross Shipment Automation
      1. Used Power Query to import and update the 21 Estee Lauder brands’ daily and monthly brand shipment metrics (in dollars)
      2. Built the two main summary pages referencing six other sheets
      3. Copying and pasting the daily six excel sheets from email to excel –> clicking “Refresh All” or refreshing each query
4. What you learned and how it relates to your major (e.g., describe what you learned from this experience in the context of specific courses)
   1. Worked in ELC PA office, which was more supply chain focused: when creating orders or rejecting orders, I had to check the material inventory status. Different plant locations and brands ran delivery on different days of the week, so calculating shipment dates required more planning. The shipment date and material detail related a lot to MSOM3101.
   2. I identified that the ELC Online Open Order Report time was very time-consuming, so I automated the process to make report time more efficient. The automation does not really apply to any specific MIS course I’ve taken, but I used my background in queries and coding (from MIS2502 and MIS2402) to learn the VBA (Microsoft’s programming language), debug, and edit the recorded macros to meet specific parameters and criteria. When building and editing the macros, it was crucial to important the correct steps of building the reports and which route to take if different values were returned (MIS2901).
   3. When building the Daily Gross Shipment report, it was mostly Power Query from Exchange Online. The steps were very similar to Mongodb (MIS2502) where I had to find a specific source, filter, and rearrange data
   4. I got to use ERP (SAP) to manage sales orders, check material inventory, and check order status, and learn how each step and action would affect another. (MIS2901)
   5. I often had to run reports multiple times a week or day, so other teams can reference the updated and accurate data when needed because sometimes shipment dates or order status will change during the day and throughout the week. The reports I built used historical data(MIS2502), so it was important to update them often to make accurate calls.