This project consists of a Microsoft Excel workbook that can be used by Amazon Fulfillment Ship Dock department leadership to plan and visualize labor allocation.
This project consists of an Excel (.xlsx) workbook with 5 worksheets.
MainBoard: This worksheet is the digital version of a real-life whiteboard-on-wheels that PAs use when assigning associates to their work area and role for the day. The physical whiteboard is used along with laminated, magnetic badges that each correspond to a specific associate. These badges are placed on a specific and clearly marked section of the whiteboard, depending on the area and role assigned. In this worksheet, the user simply inputs associates' logins instead.
ManualAddBoard: This worksheet provides department labor planners the space for an additional 244 customized roles and logins. Included in these 244 extra slots are 64 VTO slots.
TotalsBoard: This worksheet calculates and displays the number of associates staffed per department work area, as well as the department's total headcount.
CleanTextSheet: Like the name implies, this worksheet outputs the information on the two worksheets above in simple black-and-white text that's easy to read, save, and share.
TextOutputFormulas: This hidden worksheet is where a lot of the magic happens. It contains formulas that sanitize user input and that are used by CleanTextSheet to filter-out irrelevant text.
- Help me git gud at Excel.
I decided to start learning Excel in May 2023, after I bombed a pre-interview Excel/VBA assessment test that hit me by surprise. From May-September, I logged about 50 hours total, studying a little bit of everything. To this project, I have dedicated about 75-100 hours, which means that in total, I have about 150 hours of Excel experience. Nonetheless, I was able to complete this unique and challenging project.
- Help me learn the visual/graphic design/UX side of Excel.
I know core Java really well, and a lot of the concepts, key words, and method names have analogues in Excel. I know that learning arrays, how to write complex functions, and how to automate tasks with VBA will come relatively easy given my programming background, which is why this project appealed to me; it focuses more on the visual/graphic design/UX side of Excel, which has no equivalent in Java. Merging cells, customizing their borders, aligning cell text, and giving columns/rows specific widths/heights are all skills that this project helped me learned well.
- Create a digital staffing tool for SAN3's Ship Dock leadership to use in conjunction with, or as a replacement for, the physical whiteboard.
Initially, this project consisted of only 1 worksheet: MainBoard, the digital counterpart of the physical whiteboard-on-wheels. However, after seeing the PAs calculate totals by hand, I decided to create and add a 2nd worksheet to the project. I named this worksheet TotalsBoard, and it automatically calculates and updates staffing numbers as PAs enter or delete associate logins. I also noticed that the associates that help the PAs with staffing were typing roles and logins one-by-one, and then sending this information to other parties via messaging app. For example, they type: [Lanes 18-25: PG: brandon, WS: joseag, Supplies: estefania] [Spirals: PG: jose]. This is time-consuming and error-prone, which is why I decided to create and add a 3rd worksheet to the project. I named this worksheet CleanTextSheet, and it automatically gathers, cleans, and displays staffing information in a simple text format.
Amazon Ship Dock staffing is an anomaly, a challenge.
The department has over 30 roles in which an associate can be staffed in. Other departments have only a fraction of this amount. Thus, assigning work areas and roles, and modifying these assignments throughout the course of a 10-hour shift is the Amazon equivalent of conducting an orchestra.
This workbook has the potential to revolutionize the Ship Dock staffing workflow, the potential to help labor planners achieve a Bach-like performance. This workbook has the tools to assist PAs, the department's conductors, in producing a beautiful symphony of associate movement across dispersed work areas. This workbook helps PAs hit every single staffing note, for with this workbook they can easily reference, save and share important staffing information even when they are physically distant from the whiteboard.
This workbook makes it possible to update staffing roles based on business needs. When the physical board becomes outdated (due to roles being added and/or removed) or damaged to the point where it's no longer usable, I can use the skills that I gained while working on this project to create an updated template, and then suggest that the template be printed on a new, physical whiteboard.
This workbook can replace the physical whiteboard altogether. Users can simply open this workbook on a laptop, and connect it to a large flat-screen television/display.
MainBoard: Type in associate logins into select cells. My login is "joseag", so a PA would type "joseag" into, say, cell W26, which corresponds to the PG role for EAST AR BELOW MEZZ (aka "Flats" area) LANES 18-25.
In the GO-CART / NON-INVENTORY section:
Enter a number into the following cells: AO42:AO45 (DOCK DOOR NUMBER).
Enter a login into the following cells: AQ42:AQ45 and AS42:AS45 (GO-CART role).
Enter a login into the following cells: AO46, AQ46, and AS46 (EYT role).
Below is an example of what the section should look like.
CleanTextSheet: Once you have completed worksheets MainBoard and TotalsBoard to your liking, navigate to the CleanTextSheet worksheet and:
- first, click on the filter button located in cell A27
- second, check the box for (Select All)
- third, scroll all the way down and uncheck the box for (Blanks)
- fourth, click the OK button
You should now be able to see all the logins and their corresponding roles.
The image below shows how CleanTextSheet should output the data for the GO-CART / NON-INVENTORY group, assuming we input the same data as the image in one of our previous examples.
- I made stylistic improvements to the main board's layout. For example, I improved the WEST AR BELOW MEZZ and EAST AR BELOW MEZZ sections of the board by clearly separating the aisles into 4 groups. See Exhibit 1 below.
- The physical board has a dead area, a column of blank cells, on its right edge. I turned this dead zone into a VTO zone (everybody secretely wishes they were staffed here all day, every day). See Exhibit 2 below.
- Added the current time (using =NOW( )) to the left side of the digital board. Also on top of the Notes section.
- In the worksheet TotalsBoard, users can see the total number of associates per work area and per area subgroup. For example, an user can see that EAST AR BELOW MEZZ LANES 18-25 has a total of, say, 3 associates staffed and that they are staffed as follows: 1 in the PG role, 1 in the Waterspider role, and 1 in the Supplies role.
- The worksheet ManualAddBoard features the great MANUAL-ADD BOARD, into which planners can input roles that are not accounted for in the MainBoard worksheet.
- The worksheet TotalsBoard features an area that adds-up the total headcount accross all work areas and displays it in a "you-can't-miss-it-if-you-wanted-to" highlighter-yellow colored cell.
- This workbook makes use of the almighty =TRIM() function to sanitize user input, preventing false-positives by eliminating useless whitespace.
- I spent many hours merging and locking cells, which should help maintain worksheet integrity and data accuracy.
Exhibit 1: I improved the WEST AR BELOW MEZZ and EAST AR BELOW MEZZ SECTIONS.
Exhibit 2: Converted what appears to be a dead zone into a VTO (Party) zone.
I am working on a macro-enabled version of this workbook. Below are some of the features that I am working on.
- Macros that clear all logins from both MainBoard and ManualAddBoard.
- A macro that emails the text in CleanTextSheet to select personnel.
- Adding an additional worksheet to store associate information, such as id, name, login, and permissions.
- After adding the worksheet I mention above, I can include macros that do things such as check for incorrect or duplicate logins.
- Macros that record the day's staffing data. The macros will compare staffing information quarter by quarter and produce a document that highlights these changes.
Below is a photograph of the real-life Amazon Ship Dock staffing board.
Below is a photograph of my magnet.
I authorize Amazon and any of its employees to use this workbook, or a modified version of, to assist with their day-to-day operations.