Infrastructure Order Analysis

Import the dataset into Google Sheet

The downloaded file is a "xlsx" file. We can import it into Google Sheets. Open a new sheet on Google sheet. Under File on the top menu bar, select import, then select the upload tab and upload the file downloaded. Click select replace spreadsheet and then click on import data.

You may notice the naming of the columns downloaded from Keepthereceipt looks a bit different from the dashboard, but we could guess the meaning most of the time. If you have any questions on the meaning of these column names, you could refer to this page.

Make a Pivot Table to see top Buyers

If you haven't heard of Pivot table before, we recommend you to water this tutorial first before getting into the example with procurement data

Insert a pivot table

We are interested in which department spent most under infrastructure. We could use the pivot table feature in Google sheets to do this. The columns we are interested in then would be the "buyer_name", "order_amount_zar" and "payment_amount_zar".

To make a pivot table, we need to select the relevant columns on Google Sheet. Follow the below steps to make the pivot table

1. Click on insert in the menu and select pivot table

2. Select B to F column.

3. Click “create”

We can see a new sheet called pivot table is opened. On the right side is a list of columns. We can then drag the relevant columns under the rows, values, and columns menu under the pivot table editor. Because we want to see the sum of orders or payments by buyer name, we can

1. Drag the buyer name into rows.

2. Drag the order_amount_zar and payment_amount_zar under values.

3. Select sum under the summarize by dropbox to indicate we want to sum up all the order amounts by one buyer.

However, by this time, we found all cells in the sum of payment_amount_zar column has 0 as the sum. There is some problem happening here.

Assign types of data to columns

The issue is that Google didn’t understand the data under the "payment_amount_zar" column is numbers, so it’s treating these cells as plain text. It doesn’t make sense to sum up these text. Thus, we need to indicate to Google these values are numbers manually.

Let’s go to the previous sheet and inspect column "order_amount_zar" and "payment_amount_zar" . The "123" button on the menu bar indicates the data type. To change the data type of a column, we could follow below steps.

1. Select the column of interest and click on the 123 button. We can see the data type currently is automatic.

2. Change "automatic" to "number".

Switch to the pivot table sheet, we can the the sum of these amounts are showing in the pivot table now.

Sort the pivot table to see top buyers

We would like to organize the pivot table to see the top spending department. Under buyer name in the pivot table editor, we could see Order by and sort by drop down box. This is used to sort the pivot table.

1. Under the "Sort by" dropbox, select the "sum of payment_amount_zar" option.

2. Under "Order by" dropbox, select "descending".

Now we could clearly see Gauteng Health and Gauteng Infrastructure development is the top spending department. Gauteng Health spent over 886 million Rand on infrastructure. While Gauteng Infrastructure development ordered over 2 billion Rand infrastructure, even though only 400 million Rand is paid.

Filter the sheet by buyer name