ECON311 - Assignment 3
Sencer Karademir
Summer 2020
Instructions
ˆ This assignment is due on August 09, 2020 at 23:55.
Late submissions will not be accepted for any reason.
ˆ Answer all the questions in an Excel workbook file. The
workbook must be submitted in the Dropbox in D2L.
Assignments sent by e-mail will not be accepted. Dropbox is set in such
a way that only your latest submission is kept. When you upload a second file,
the first one will be erased.
ˆ You are encouraged to work with your friends. However, you cannot submit
the same file with your friends. Your submission should reflect your own work
and effort.
ˆ Name the workbook that contains your answers with your family name and
first initial, followed by A3. For example, "KarademirSA3.xlsx".
ˆ Put your name in the cell A1 and student number un cell A2, in bold type
for all of the worksheets.
ˆ Clearly number all your answers and work.
ˆ Put your answers to the questions in a textbox beside the question number
and part. Answers need to be done in a sentence with good grammar, correct
punctuation, and spelling.
ˆ There will be point deductions if any of your answers are presented badly and/or
not appropriately formatted.
1
1. Mortgage
Do the following questions on a worksheet titled "Mortgage".
Your answers must contain Excel functions and include comments whenever possible.
Walter and Jesse want to start a business. They need an initial investment of
$335,000 for the equipment and facilities, and decide to apply for a mortgage to cover
the full amount.
(a) WhenWalter and Jesse apply for the mortgage, the bank informs them what the
current nominal interest rate with quarterly compounding is. Explain Walter
and Jesse what this interest rate means.
(b) If Walter and Jesse want to make monthly payments, explain which interest
rate they should use for each payment they make. Explain how you obtaining
this interest rate. Do not explain the mechanical process only, explain the logic
behind each action you are taking.
(c) Walter and Jesse decided to check another bank's offer and compare it with the
initial bank's. The second bank offered the exact same nominal interest rate,
but with semi-monthly compounding. Explain which offer is preferable over
the other one and why. Do not explain the mechanical process of comparing
these rates. Explain the logic of why one is better than the other one.
(d) Explain Walter and Jesse how you obtain monthly and semi-annual payments
of a mortgage (or any kind of loan) with constant payments. Address a common
mistake they can make if they did not take ECON311.
(e) Suppose that there is another bank with an offer too. The offers Walter and
Jesse received are; 5% NIR with quarterly compounding from Bank A, 5%
NIR with semi-monthly compounding from Bank B, and 4.98% with weekly
compounding from Bank Q. Assume that the interest rate won't change until
the mortgage is paid in full. Set up an appropriate table to compare both
options and advice Walter and Jesse to take the best option. Explain why
which one is better over the other ones and address any possible mistakes
Walter and Jesse can make if they did not take ECON311.
(f) Walter and Jesse decided to listen to your advice and applied for a mortgage
from the bank of your choosing. Suppose that the mortgage has an amortiza-
tion period of 25 years, 5-year terms, semi-annual payments. Assume that the
2
interest rate will remain the same in each term. Set up a semi-annual mortgage
repayment scheme with constant payments. Then, set up a semi-annual mort-
gage scheme with constant repayments. Compare both options, and decide on
which one is the better option. Use the interest rate you use for each period as
the DIR. Explain why one option is better than the other one.
(g) For the remaining part of the question, assume that payments are calculated
as if the interest rate will remain the same for all the terms (e.g., in term 2, the
payments will be calculated as if the interest rates of terms 3, 4, and 5 will be
the same with that of term 2's). Construct mortgage repayment schemes for
each of the following options, and decide on which one is the best option:
ˆ Constant payments, the interest rate you used in (e).
ˆ Constant payments, starts with 1 percentage point less than the interest
rate you used in (e) for each period and increases by 0.5 percentage points
in each term.
ˆ Constant payments, starts with 1 percentage point more than the interest
rate you used in (e) for each period and decreases by 0.5 percentage points
in each term.
Use the interest rate you use for each period in option 1. Explain your reasoning.
Note: If 7% increases by 1 percentage point, then you get 8%. If 7% increases
by 1%, then you get 7.07%.
3
2. Donuts
Do the following questions on a worksheet titled "Donuts".
Your answers must contain Excel functions and comments whenever possible.
Homer owns 200 donut stores in Calgary (CA), Edmonton (ED), and Lethbridge
(LB). The average price of a donut is $3.15 in every store Homer owns. The data for
these stores is in the "Donuts" worksheet of "Assignment_3_Data.xlsx". This data
contains the number of daily customers, the fixed cost of each store (rent, wages,
etc.), and the variable cost of each donut sold. Insert this data in your worksheet
and appropriately format it.
(a) Determine the annual profit for each store (1 year = 365 days).
(b) How many stores are there in each city?
(c) Using database command (wherever possible) and a Data, Table command
calculate the total profit, the average daily sales, the average fixed cost, and
the average variable cost assuming all stores are operating. Then, find the
number of stores with a negative profit in each city.
(d) Create another column that categorizes the stores in terms of their sales per
day. Stores with the number of sales per day of at least 1500 are in category
TOP, more than 1250 and less than 1500 in category MEDIUM, and at most
1250 are in category BOTTOM.
(e) Using prices of $2.80, $2.90, $3.00, $3.10, $3.20, $3.30, and $3.40, derive the
annual supply schedule of Homer's donut store chain (the annual number of
sales at each price). Assume the stores with negative profits you found in (c)
will not produce.
(f) Draw an appropriate graph of the annual supply curve for the donut store chain
using your findings in (e). Keep in mind that this is the supply curve while
constructing your graph.
(g) Using Data, Table command calculate the total profit for each city for the prices
in (e). Assume stores with negative profits will not produce.
(h) Create two pivot tables to present the same information as (c). One of these
pivot tables should be done by the cities, the other should be done by the
4
categories you created in (d). Your pivot tables should contain ALL DATA,
but your presentation should not include stores with negative profits.
5
3. SLID
Do the following questions on a worksheet titled "SLID".
Your answers must contain Excel functions and comments whenever possible.
The data consisting of some select variables from Survey of
Labour and Income Dynamics (SLID) in 2011 is in the "SLID"
worksheet of "Assignment_3_Data.xlsx". See the document Assign-
ment_3_SLID_variables.txt for the description of some of the variables.
Post your answers next to this table after inserting it on your worksheet, not under it.
(a) Only keep the records from individuals in Alberta and post the data on your
worksheet.
(b) How records are there in the database?
(c) Include a new column, "Record #" to the left-most of the table. Start from 1
and fill all the cells.
(d) How many married females and males are there in the database? Compare.
(e) How many married females and males earn at least $50,000? Compare.
(f) How many single (never married) females with 15+ years of education earn
at least $150,000? What is this number for single males with 15+ years of
education? Compare.
(g) Insert a new column: Education Class. Anyone with the education of at least
19 years is in P class, with education of at least 17 years but less than 19 years
is in G class, with education of at least 15 years, but less than 17 years is in U
class, with education of at least 13 years but less than 15 years is in D class,
with education of at least 10 years but less than 13 years is in B class, and
finally anyone with education less than 10 years is in NF class. Use a lookup
table for this question.
(h) Using the bins from the previous part and COUNTIF function, do a frequency
distribution for education class. Comment on your findings.
(i) Use the "years in education" and histogram function to find the frequency
distribution for education class. Do your results match with the findings in
(h)?
6
(j) Use FREQUENCY function to address the previous part. Comment on your
findings.
(k) Use an appropriate graph to compare the frequencies of each education group.
(l) What are the average, the median, and the deciles for age? What are the
minimum and maximum ages? Interpret these numbers.
(m) Use an appropriate function to find the distribution of age. Your bins should
start from the minimum value and go up by 5 years increments to maximum.
Comment on your findings. Which age group is the most populated?
(n) What is the average income of the most populated age group? What is the
average income of the least populated age group?
(o) Use pivot tables to compare the average earnings, the average number of years
of education, and the average age between (i) males and females, (ii) between
married and single, and (iii) between G education class and P class. Comment
on your findings.
7