APCO 1P01 Winter 2020 Assignment 2
APCO / IASC 1P01 – Winter 2020
Assignment 2 –

Lab Dates to Work Assignments On – Mar 1 - 5, Mar 8 - 12, Mar 15 - 19
Assignment Due Date – Mar 19, 6:00 pm – uploaded to Sakai
Assignment Late Due Date – Mar 22, 6:00 pm, subject to 25% penalty

Please view all the Week 5 & 6 Online Lecture videos before attempting to start this assignment.

For this assignment you are to use Microsoft Excel in order to modify ONE Excel Workbook
that uses many of the features in Excel. You need to modify the Excel Workbook that is found
on Sakai along with this written description and upload it back.

• Part A – using the loan function within Excel
• Part B – calculating final marks and statistics for a class
• Part C – analyze data of your choosing and produce statistics and charts pertaining to it

Part A: Loan Function


Fig A – 1 (Loan Function)

APCO 1P01 Winter 2020 Assignment 2

Fig A - 2 (City Index)

The PT A - City Index worksheet will display the average house price (Feb 2021) for 6 Canadian
cities. The cell marked in orange (represents the down payment percentage). When that
amount is entered, all of the down payment amounts should be calculated. From this, you can
calculate the amount of loan you would need to purchase an average house in each city.

For example – An average house in Niagara Falls sells for $531,916. If you were to put down 5%,
that would be $26,595.80. This would mean that you need to secure a loan for the other 95%
(ie $505,320.20)

Once the values are calculated on the PT A - City Index worksheet, you will want it to calculate
how much the monthly payment would be for a 10-year mortgage and a 25-year mortgage, at 4
different interest rates in each of the 6 Canadian Cities, based on how much the loan is and
how much down payment is paid.

Looking at the table, we can see that:

Borrowing $304,862.60 in Welland, with repayment over 25 years, and annual interest rate of
2.0% =

$1292.17 / month repayment & we would pay $82,789.41 in interest over the 25 years

If the interest rate dropped to 1.5%,

$1219.26/ month repayment & we would ‘only’ pay $60,914.28 in interest over the 25 years

For Part A, you are to modify these worksheets that will calculate how much total interest you
are to pay for certain interest rates and certain term periods.

You will use the PMT function in Excel (use help to learn about this function). Here are some
hints for the parameters:
• Rate – this is the monthly rate (need to divide the annual rate by 12)
• Nper – number of total payments made over lifetime of loan
• Pv – the amount of money borrowed
• Fv , Type – these are optional – please omit these

Please fill out the PT A worksheets to make them look similar to Fig A-1 and A-2. Format the
tables so that the tables look presentable (ie - table headings, proper row and column headings
with similar font sizes and alignment, not all the numbers are negative numbers, etc). Clue –
multiplying a negative number by -1 will result in a positive value.
APCO 1P01 Winter 2020 Assignment 2

• The first table in each city will display the monthly loan repayment amounts. The columns
will be the payback period and the rows will be the interest rates. (use the PMT function in
each cell in the table, using the row heading and column headings as parameters in the
function)

• The second table will be the total amount paid back over the length of the loan (in other
words - the loan amount plus the interest). Use the same headings as the first table and use
the data from the first table in your formulas (ie – monthly payment [loan entry] * number
of monthly payments).

• The third table will calculate the cost of the loan (ie – the total amount paid back [table B
entry] minus the original amount of the loan [Loan Amount entry])

• Save the worksheets with the values that correspond to a down payment of 15%, interest
rates from 1.5% to 3.0% (in 0.5% increments) and for 10 years of loan repayments and 25
years of loan repayments.

Part B: Class Statistics

For Part B you are to modify the worksheets in Excel that helps you keep track of the marks of
a fictitious class.

Part B will consist of three sheets:
1. a marks worksheet (similar to Figure B-1) – PT B - Marks
2. a marking scheme worksheet (similar to Figure B-2) – PT B - MS
3. class list worksheet (initial data) – PT B – Class List


Fig B-1



Fig B-2 Fig B-3

In the PT B – Class List worksheet, notice that the Assignment 3 column is blank. In Column K,
generate random numbers between 18 and 30. Copy just the values into Column E. This
simulates the students getting a mark on Assignment 3 out of 30.
APCO 1P01 Winter 2020 Assignment 2

For the PT B - MS worksheet, similar to Figure B-2, the section on the right will allow the
professor to type in a student number (where it is highlighted in blue) and it will then display
above inside the thick box - the student name, their marks for the 6 pieces of work as well as
the student’s final mark and letter grade. If it is an invalid student number, N/A will appear in all
the columns.

To do this you will need to use the VLOOKUP function multiple times:
=VLOOKUP(lookup value, table array, index_num, [range lookup]) hint – use a value of false for
range lookup. The mark and letter grade will be a VLOOKUP of two different columns and use
the CONCATENATE function.

The class list is sorted by Student Number. Your PT B - Marks worksheet, will sort the class by
Last Name and then First Name (since two of the students have the same last name).

Formulas:
Raw Final Mark
• calculated by using the student's marks on the first worksheet and the marking scheme on
the second worksheet
• for each piece of work, you need to calculate what it contributes to the final mark and add it
to the running total for all pieces of work to get a final mark.
o For example – if a student got 45 / 60 on the test, and the test was worth 20% of the
final mark, then the test would contribute 45/60*20 = 15 marks toward the final
mark.
• the calculation of the raw final mark needs to only include cell references (and not hard
coded numbers) *****

Final Mark
• round the Raw Final Mark to the closest whole number
• need to format the cells (or use a round function)

Grade
• assign a letter based on the final mark (>79 = A, >69 = B, >59 = C, >49 = D, else it is an F)
• use an IF statement (use the nested logic that we discussed in the online videos)

Averages –
• average of each piece of work, the raw final mark, the final mark and the grade

Class Summary –
• similar to Fig B-3, put below on your worksheet with the student’s marks
• before grades can be finalized, the chair of the department may wish to know how the class
did. To that end, the class’s lowest (min), highest (max), and average (both median and
mean) grades can be easily calculated.
• finally, you’ll want to know how many As, Bs, Cs, Ds, and Fs were earned by the class. eg -
count the As: =COUNTIF(K4:K18,"A")

APCO 1P01 Winter 2020 Assignment 2
Part C: Statistics of your choosing

Please review the Week 6 Part D video that presented statistics based on US States, the
population, number of electoral college votes, percentage of US’s total population, whether the
instructor had visited the state and whether or not there was an NHL hockey team based in the
state.

For this part of the assignment, you are to search the internet for data that you will do similar
statistical analysis on.

Part C will consist of 3 worksheets:
1. PT C - Data worksheet that will contain 20 rows of data, and 5 measurable attributes of
that entity
2. PT C - DataAnalysis worksheet that will answer 10 questions based on the first
worksheet It is to use the COUNTIFS, SUMIFS function, and other functions you feel are
appropriate. Please answer some questions that will be conditional on at least five
columns of the data
3. PT C - Dashboard worksheet that will contain a least 4 different formatted graphs or
charts that use the data in the first worksheet. Please include proper labels and chart titles
as you will be marked on level of effort.

For example, I might choose the 20 largest cities in Canada to study.

The 5 measurables I might have are:
• province that the city is in
• population of city
• number of inhabitants under 5 years of age in city
• number of registered daycares in city
• number of registered daycare workers in city

Here are some example questions I might ask in PT C - DataAnalysis:
• what is the average number of registered daycares in the largest 20 Canadian cities
• how many top registered daycares are there in the cities that are in Ontario
• how many of the top 20 cities have a population over a million and have less than
100,000 kids under the age of five
• how many of the largest cities found in Quebec, have kids under 5 make up more that
20% of that cities’ population
• what is the sum total population of the top cities in the list that are from Alberta

For PT C - DashBoard, I could choose the following – 3D Pie Chart, Statistical Histogram, a 2D
Line Chart, and a 2D Column chart to highlight information and patterns I find interesting.

Be creative and find some fun data. Do not use any of the examples that were used (ie US
States, states I have visited or anything related to hockey).

Please include the website references where you found the information at the top of PT C -
Data worksheet.