Data Cleansing and Formatting for Analysis

Now that your raw source data is consolidated, cleaned, and normalized, apply basic formatting for ease of readability, navigation, analysis, and future usage.

1. Apply bold text formatting to the column headers for ease of visualization.
2. Apply the AutoFilter to the column headers for ease of filtering & sorting.
3. Split Panes, freeze the top row of data, freeze the columns up to and including PO.
4. Ensure date fields are formatted as dates.
5. Ensure dollar value fields are formatted with a dollar sign. If most items are over ten thousand dollars, hide the 2 decimal places (i.e. do not show cents).
6. Never leave worksheets with default names like Sheet1, Sheet2, Sheet3. Rename worksheets to something short yet descriptive.
7. You are encouraged to use Power Query to automate Requirements 1 and 2. This enables the source data to be untouched and if the data is updated in the future, all of these steps would automatically apply to the new data.

Student or professor materials created for this course (including presentations and posted notes, labs, case studies, assignments, and exams) remain the intellectual property of the author(s). They may not be reproduced or redistributed without prior written consent of the author(s).

1. How many different vendors were awarded contracts in 2020 Q1 and Q2?
2. Which vendors were the top 20 highest value contracts awarded to?
3. Which are the top 20 vendors by total dollar value?
4. Present a pie chart suitable for a high level overview – show the breakdown of the top 50% of contracts.
5. Visually show the total dollar value of contracts awarded over time using a Sparkline after the last column of the PivotTable.

Cleanse the vendor list to eliminate duplicated entries. In other words, combine vendors that are obviously the same but have very minor spelling differences.

There are multiple ways to do this, some are more time efficient than others. Recommended concepts to use: New Worksheet, Sort, Remove Duplicates, VLOOKUP. Show your work! In a new worksheet, create a mapping table showing the old name and the new name.

2. On your main worksheet, using VLOOKUP and formulas as needed, create a new column to store the corrected vendor names.
3. Use your knowledge of PivotTables to answer items 1-3 above. Create your PivotTable into a new worksheet, utilize Number Format, Sort, Filter, and formatting best practices. Write your answers professionally into your executive report and support with a copy of the table.
4. Use your knowledge of filtering and charting to create a pie chart for the top 50% of contracts. Add and format data labels callouts, show vendor name, percentage, value, and arrange using Best Fit. Copy & paste your final chart into your executive report, professionally write a short summary of your findings.
5. Create a Sparkline showing dollar values over time. Make note of any exceptions/outliers in your report.
6. For ease of visualization, apply the concept of Conditional Formatting to the Amount column, in order to easily high value contracts.

Student or professor materials created for this course (including presentations and posted notes, labs, case studies, assignments, and exams) remain the intellectual property of the author(s). They may not be reproduced or redistributed without prior written consent of the author(s).

1. Using a column chart, show the top 10 departments by total contract value.
2. Using a table or PivotTable, show the top 3 vendors by each department and by month, showing the grand totals.
3. What are the vendors, departments, and items that comprise “Various”?
4. For the procurement of discrete homogenous items, identify the cost per unit and summarize in a table.

1. Filter your table/PivotTable and add a column chart to answer Item 1. Apply formatting standards, copy to report.
2. Filter your table/PivotTable, apply suitable professional formatting, copy the resulting table to your report to answer Item 2.
3. Filter your table accordingly, read the information across the entire table, summarize your answers in the report.
4. Look through the Description column to identify items that are quantifiable. Add new columns

to your combined data set to identify quantity (i.e. units) and calculate unit cost. Show your work: ensure your formulas are visible