- July 31, 2020

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