Organizations must interact with the environment to procure goods and services to facilitate their operations.

INSTRUCTIONS TO CANDIDATES

ANSWER ALL QUESTIONS

Requirement 01: Importing and Consolidating Data [10 marks]

Background:

Organizations must interact with the environment to procure goods and services to facilitate their operations. Larger organizations will have a dedicated procurement function and defined procurement processes to ensure the right goods and services are obtained at competitive pricing. One such organization is the municipal government of Ottawa, aptly named: City of Ottawa.

 

You are a data analyst for the City of Ottawa and your supervisor has come to you asking for information on the city’s procurement activities. In particular, they have asked you to produce an executive report on the 2020 Q1 and Q2 contracts awarded to external vendors, having a value greater than or equal to

$25,000.

 

Your first step is to retrieve the data and prepare it for analysis.

 

Instructions:

1. Go to https://open.ottawa.ca/documents/2020-contracts-awarded-greater-than-25000-/about and download the .xlsx dataset

2. Combine the 2 worksheets in a way that prepares the dataset for analysis:

a. Create a new blank worksheet.

b. Combine the 2 existing worksheets into the new worksheet, removing existing formatting when pasting the data into a new worksheet.

c. Since you extracted the data from 2 different worksheets and combined them into one, add a column that identifies which worksheet each row of data originally came from.

3. Identify and delete any records that fall outside of the 2020 Q1 and Q2 time period (i.e. anything not within January 2020 to June 2020).

 

Additional Notes:

1. Ensure you review the source data for any columns that might not be immediately visible.

 

 

Requirement 02: Formatting and Cleanup [10 marks]

Background:

Now that your raw source data is consolidated, you will apply formatting and structure to the dataset so that it is easy to understand and use in subsequent operations. You will also need to review and transform the data to either address poor data quality or enhance the data for reporting.

 

Instructions:

1. Convert your consolidated dataset into an Excel table and provide it an appropriate name.

2. Format the table to better display the data and provide context to other users:

a. Apply the AutoFilter to the column headers (if not done automatically).

b. Using Split Panes, freeze the top row of data and freeze the columns up to and including

“PO”.

c. Ensure any date fields are formatted as short date.

d. Ensure any currency (i.e. dollar) fields are formatted with a dollar sign and no decimal places.

3. Clean the vendor list to eliminate duplicated entries.

a. This will involve identifying vendors that are obviously the same but have minor spelling differences, for example:

i. 9135545 CANADA INC (MALWOOD) vs 9135545 CANADA INC

ii. COCO PAVING vs COCO PAVING INC.

iii. CORNWALL GRAVEL CO LTD vs CORNWALL GRAVEL COMPANY LTD.

b. In a new worksheet, create a table that can map the current vendors names to adjusted vendors names based on your identification of duplicated vendors.

c. In the original data table, create a new column that will retrieve the adjusted vendor name and provide this column an appropriate heading label.

4. Rename any new worksheets with short and descriptive titles.

 

Additional Notes:

1. For an entire column to be specially recognized by Excel as all date fields, there must not be any non-date values in the column. For any rows missing dates, assign them the first date of the reporting period.

2. For the mapping table, there are various ways of identifying partial duplicates, however, the LEFT and SORT functions are recommended; as well, conditional formatting can also be used with these functions to spot duplicates more easily.

 

 

Requirement 03: Analysis [40 marks]

Background:

Your supervisor wants to know the following high-level information items:

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. Who are top 20 vendors by total dollar value?

4. What are the contract totals per departments?

5. The number of Purchase Orders created by the top 4 departments per month based on total contract value.

Your supervisor also wants to know more in-depth information on specific items:

6. The top 3 vendors for each department based on total contract value, and the amounts per month.

7. A listing of the vendors, departments, and items that have been designated as “Various”

 

Instructions:

1. For item 1, create a new worksheet and table that contains a unique list of vendors and uses conditional summary functions to calculate the required statistics.

2. For items 2-5, create a series of Pivot Tables, each in their own worksheet. You should format these tables so that they are easy and readily understood.

3. For items 4-5, create a corresponding Pivot Chart that visualizes the differences among departments. Item 4 should be visualized using a bar chart that shows departments with the highest total contract value at the top. Item 5 should be visualized with a line chart.

 

Additional Notes:

1. When formatting tables and charts:

a. Consider what the amounts represent (e.g dollars, counts);

b. The scale of what is being represented (i.e. 10s, 1000s, 1,000,000s)

c. Labels and Titles (e.g. Who, What, Where, When)

d. Visual Noise (e.g. Do gridlines improve or detract from the visual?)

2. For requirement 6, you will need to consider the Pivot Table’s Report Layout in order to effectively filter the table.

 

 

Requirement 04 – Executive Report [20 marks]

Instructions:

1. Using the work performed to address the previous requirements, write a report describing your findings.

2. The body of the report must be within 3 to 9 pages, excluding a cover page, executive summary, and declaration of academic integrity. It must also include any tables or visuals that are used to support your findings, with exception to the list of vendors table used in Requirement 3.1.

3. You must structure report using based on the following outline:

o Cover Page

o Executive Summary

o High Level Summary

o In Depth Analysis

o Conclusion

o Declaration of Academic Integrity

4. Your report must be professional in appearance and adhere to the following requirements:

o Normal 1” (2.54cm) margins on all sides.

o Single-spaced with double-spacing between paragraphs.

o Size 11-12 font with your choice between: Time News Roman, Calibri, Trebuchet MS, Segoe UI, Arial, Verdana.

o Page numbers in the Footer

 

 

Additional Notes:

1. If a chart or table is to wide to present in the portrait orientation, switch the specific page to the landscape orientation. Your report is expected to be in portrait orientation otherwise

 

 

Attachments:

Related Questions

. Introgramming & Unix Fall 2018, CRN 44882, Oakland University Homework Assignment 6 - Using Arrays and Functions in C

DescriptionIn this final assignment, the students will demonstrate their ability to apply two ma

. The standard path finding involves finding the (shortest) path from an origin to a destination, typically on a map. This is an

Path finding involves finding a path from A to B. Typically we want the path to have certain properties,such as being the shortest or to avoid going t

. Develop a program to emulate a purchase transaction at a retail store. This program will have two classes, a LineItem class and a Transaction class. The LineItem class will represent an individual

Develop a program to emulate a purchase transaction at a retail store. Thisprogram will have two classes, a LineItem class and a Transaction class. Th

. SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 1 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of

. Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of