Answer the excel file, instructions are in the word document.

Requirements:

• Reference data from multiple sources

  • Slam %

  • Active Driver Associates (DAs)

• Collate to 1 excel file

• Index all sheets to a summary page

  • Pivot Table

  • Nested Formulas

  • Absolutes

  • VLOOKUP

  • IFERROR

  • Conditional Formatting

  • SUMIFS

• Analyze data

MTP Compare Setup/Questions:

Use the attached file (Assessment”) and fill out the following steps.

After filling out the Excel “Station Summary” tab, answer the corresponding questions in the “Quiz” tab:

1. Using the VLOOKUP function, on the “Station Summary” tab, fill out the “Region” column using the “WK 52+Attributes” tab.

   1. Use the IFERROR function to return the word “ERROR” for non-existing stations in the list once verified.

2. Using the VLOOKUP function, on the “Station Summary” tab, fill out the “Super” column using the “WK 52+Attributes” tab.

   1. Use the IFERROR function to return the word “ERROR” for non-existing stations in the list once verified.

3. Using the VLOOKUP function, on the “Station Summary” tab, complete the “Slam %” data in Station Summary tab using the “Slam %” tab.

   1. Use the IFERROR function to return the word “ERROR” for non-existing stations in the list once verified.

4. Using the VLOOKUP function, on the “Station Summary” tab, complete the “Active DAs” data in the Station Summary tab using the “Active DAs” tab.

   1. Use the IFERROR function to return the word “ERROR” for non-existing stations in the list once verified.

5. Using the SUMIFS function, on the “Station Summary” tab, fill out the Publish 1 (“Publish 1” tab) and Publish 2 (“Publish 2” tab) volumes for all stations from weeks 10-18.

6. On the “Station Summary” tab, fill in the ‘% Change’ cells to display the percentage lost from “Publish 1” to “Publish 2” for each site every week.

   1. Hint: For a standard % Change you are comparing the older volume to the newer to show the volume delta increased or decreased

7. Using conditional formatting on the “Station Summary” tab, highlight every cell in red where the % Change row shows a change less than 0%.

8. Create a pivot table of the entire “Wk 52+Attributes” tab displaying WK9 volume by Region, Delivery Station (DS), and Cycle.

(C# [i.e., C1, C2]) - Cycle - An identifier employed to specify a dispatch window within a delivery station

(DAs) - Delivery Associate - An individual responsible for transporting and delivering goods or packages to customers

(DS) - Delivery Station - A facility or location where packages are received, sorted, and dispatched for final delivery to customers

(MTP) - Mid-Term Plan - A 13 week plan that serves as a guide for decision-making and resource allocation to achieve the desired outcomes within the specified timeframe.

(SLAM) - Shipment Label and Manifest - When the volume has been Shipment Label and Manifested (SLAM'd), it confirms that the volume has been processed, including scanning, sorting, and loading, and is ready for transportation to its next destination

(Super) - Super Regional Leader - A managerial or executive role within an organization, typically overseeing a larger geographic region than a standard regional leader

2