Ready.gov: Services

Price Sheets

Redesigned Corporate Price Sheets and Created Job Aids

Prior to my internship with Real Floors, corporate pricing was managed in Google spreadsheets. Data was manually entered, added and formatted by hand. Each management company has its own price sheet, set up in a grid pattern with products and labor lines listed down the side, and markets across the top (as seen below). In order to improve the process, I implemented two key improvements.

First, I standardized the formatting between all 80 of the corporate price sheets. As seen above, there were numerous different styles, designs and formats throughout the different management companies. Because these sheets are also intended to be given to our customers in pdf format, I designed a version of the price sheets that were a cleaner, more readable design, while better showcasing the company branding by removing extraneous colors, especially those which did not align with the company style guide I made previously in the internship (See the Branding and Communications Style Guide).

I started by making a prototype that could be reviewed by the rest of the team (seen below) before the design was implemented into all 80 corporate price sheets.

The second step was to add formulas in order to both decrease errors and improve the usability of the sheet. Each sheet has two parts: The final sheets (seen above), and the worksheets (seen below).

The worksheets are meant for internal use. Each market or branch is comprised of three columns: the old price, the change in price, and the new price. Prior to this project, each column and the final sheet were all manually maintained. The requirements for this stage were two-fold: decrease the number of columns that needed to be edited to reduce the risk of error, and it had to be something that could be teachable, so others could learn how to manage the formulas.

I created several possible series of formulas; however, I ultimately decided to utilize a vlookup. I chose vlookup because it could consistently be used in the most places compared to other potential formulas (such as an index-match-match or query sort), meaning that if someone did need to learn how to manage these formulas with a lower basis of excel/sheets experience, they would only need to learn one formula, rather than multiple.

As such I used the vlookup in three ways:
To pull the pricing into the “old” column on the worksheet from the previous final sheet
To pull the increase amount for each product from a separate master increase amount sheet
To pull the pricing from the “new” column into the new final sheet

These formulas allow the sheets to be edited much more quickly, as changes only need to be made in one place, and the formulas would automatically populate the pricing into the correct sheets. The final formulas can be seen in the job aids below.

Along with the price sheet redesign, I also created two sets of job aids to help my coworkers be able to utilize and recreate the redesigned price sheets.

The first set of job aids was designed to help with formatting the new price sheets and worksheets. Those job aids can be seen below.

The second set of job aids I created were for the formulas. These were intended to also act as a guide for someone if I was unable to be there to train them, potentially with minimal excel experience. As such, I made sure that not only were the formulas included in these job aids, but also explanations for what information needed to be entered into each part of the formula.