8.3 Exercise: Border Creation

Time to put your skills to the test. Using what you’ve learned in the tutorial, create a butterfly chart that is capable of comparing the costs associated with building the border.

Download the Northwest_boundary_Commission_Special_Paylist.xlxs from the Module 8 folder in the files section of the course.

This file describes the costs (excluding salaries) of building the Canada-US border from the Pacific Ocean to the Rocky Mountains. Three columns are of particular note:

Exp_Type: This tells us the type of expense being used including provisions (food), transportation, accommodation, and supplies (compasses, wood, candles, etc.)

Date: the year the expense occurred

H_Sup_T: the amount of money given to a merchant for the goods and services rendered to the boundary commission. This includes provisions, labour, accommodations, etc. although it excludes the salaries of individuals directly employed by the boundary survey team.

Our goal is to create a butterfly chart that compares the amount of money the boundary surveyors gave merchants to pay for transportation vs what they paid for accommodations.

Try creating a butterfly chart now that can visualize that information. Use the video on the previous page for help if you've forgotten the steps.

Hint

You want to create two pivot tables. The date goes in the rows and Sum of H_Sup_T in the values. Both tables with have Exp_Type as the filter, one set to accommodation, the other to transporter.

How’d you do? Can you see any problems with what we’ve created?