Instructions ECON 311 Spring 2023 Assignment 2 Due Date: June 4th, 2023 11:55pm • Submit your workbook to D2L Dropbox before the deadline. 5 points penalty for one day late submission. Solutions will be posted on June 5th, 11:55pm, so submissions after will not be graded. • Answer all the questions within the Excel workbook file. • You are encouraged to work with classmates so long as you submit your own workbook. • Name the workbook with your last name, then your first initial, then A2 eg. SharmaM A2.xlsx. Points will be deducted if file is named in any other format. • Clearly number your answers and show your work. • Put all your answers in a textbox beside the question number. • 10/100 points for presentation and organized work is awarded. Note that correct answers with bad presentation will get penalized. • New columns or rows added should be formatted differently. Either a different fill or font color. • Note the question number answered on every new column or row added. • At the end of the assignment, insert a new row at the top of every worksheet. Put your name in cell A1 and student number in B1 in BOLD on all worksheets. This is part of the presentation points. 1 1. Dwight starts working at age 22 and plans to retire at age 65 at the end of the year. He earns $50,000 at age 22, and deposits $4,000 of his earnings to a savings account for retirement at the beginning of the year. The savings account offers 3% nominal interest rate compounded monthly. At each year, he will get a raise of 5% on his salary and he will increase his savings by 20% of his salary increase each year (e.g., if his salary increase was $1,000, he will increase his savings by $1,000*20% = $200). All savings will be deposited at the beginning of the year. Dwight is planning to make quarterly withdraws from his savings account at equal amounts and he is planning to exhaust his resources by the end of the year at age 85. (a) How much money will Dwight have in his savings account when he retires? Set up a cashflow scheme illustrating Dwight’s transactions in his savings account from while he works (don’t forget to include Dwight’s salary). (b) Now, assume that Dwight deposits at the end of each year and repeat part a. How much is the difference between depositing at the end of the year and depositing at the beginning of the year? Where does this difference come from? (c) Return back to deposits at the beginning of the year. How much money will Dwight take out of his retirement fund each quarter after he retires? Set up a cashflow scheme illustrating Dwight’s transactions in his savings account from age 22 to 86. 2. You have just become the happy parent of a new child. You wish to provide a study fund of $72,000 by the time the child will be 18 years old by putting a fixed amount into this fund every year, beginning at birth and ending with a deposit on the eighteenth birthday. (a) How much should you put into this fund every year if the interest rate is 5% compounding monthly? (b) Use an annual table to find the interest earned and the corresponding total value in the education savings fund each year. Include a column that shows the future value of each annual contribution. (c) Assume instead that $18,000 is needed at the ages 18, 19, 20 and 21. What are the annual amounts that must be deposited every year under this new assumption? 3. A company plans the construction of a winter-themed family amusement park in Alberta. The project details are as follows: • $1,000,000 in year 1 and $1,500,000 in year 2 for construction. • $500,000 in year 3, increasing at a rate of 4.5% per year in years 4 to 10 as operating and maintenance costs. • $20,000 in each of years 1 and 2, and $40,000 in each of years 3 to 10 for advertising. • 25,000 tourists will visit the park in each of years 3 and 4, after which the number of tourists is expected to increase at a rate of 5% per year. • Initially the price of entrance will be $34 for year 3, increasing at a rate of 3.5% in years 4 to 10. • The opportunity cost is 10%. 2 (a) Construct a table containing the outflows, the inflows, the net cash flow, and the discounted cash flows for years 1 to 10 for the Winter Park project. (b) Calculate the NPV and the IRR in year 10 and decide if the project is profitable. (c) What is the maximum discount rate for which this project should be undertaken? (d) Calculate the cash balances and the interest-based cash balances1. For both columns, highlight the maximum amount tied in the project during the ten years. (e) What is the payback period? What is the interest-based payback period? What do they repre- sent? The investors want to know what changes could make the project profitable, other than financ- ing and want to use some sensitivity analysis to understand this. (f) Using a Data Table command with a change cell determine the NPV and the IRR for changes in initial ticket price of -15%, -10%, -5%, 0%, 5%, 10%, 15%, and 20%. Use a discount rate of 10%. Is the project profitable at any of these changes? (g) Using a Data Table command with actual values determine the NPV and the IRR for changes in initial number of tourists of -15%, -10%, -5%, 0%, 5%, 10%, 15%, and 20%. Use a discount rate of 10%. Is the project profitable at any of these changes? (h) An investor, who has studied economics, is concerned that if the price of a ticket increases then the number of tourists will decrease, so instead, maybe they should decrease the ticket price so the number of tourists will increase. Using a two-way Data Table command, determine the NPV for changes in both the initial ticket price and the number of tourists. Do this for the IRR as well (you need to do two, two-way tables). Use the changes of -15% to 20%, increasing by 5%, along with the discount rate of 10%. Use conditional formatting to represent the investor’s concerns/idea (outside of scenarios where both ticket price and number of tourists are decreasing). What would your analysis of this project tell you about the demand curve that would be needed, the elasticity, for this to be profitable? 4. 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) When Walter 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 and why? (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 com- pounding. Explain which offer is preferable over the other one and why. 1Cash Balances with interest component 3 (d) 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. (e) 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 amortization period of 25 years, 5-year terms, and semi-annual payments. Assume that the 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 mortgage 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. (f) Now assume that the interest rate is different for multiple years. That is, • Periodic interest rate for years 1-10 is 1 percentage point less than the periodic interest rate you found in part (e). • It then increases by 0.5 percentage points in each term, where each term is 10 years. Create another semi-annual mortgage repayment scheme with constant payments, and decide which option is better between this one and part (e). Use the interest rate you use for each period in option 1 as the DIR. Explain your reasoning.2 5. Snappy Petroleum is planning to drill a well. The expected costs and revenues in terms of money in “Time 0” are: • the drilling cost: $2,050,000; • the variable costs of production: $19 per barrel; • the fixed costs: $234,000. All these costs are subject to inflation for the next years. • Cost inflation is 3% per year. The well is expected to produce for 20 years of economic life, beginning in ”Time 1”, with 90 barrels per day. • The production is expected to decrease at the rate of 10% per year. • The initial oil price (in ”Time 0”) is $82 per barrel, and it is expected that this price increases at the rate of 2% per year. • The Crown Royalty is 25% and Overriding Royalty is 10%. 2Note: If 7% increases by 1 percentage point, then you get 8%. If 7% increases by 1%, then you get 7.07%. 4 • The federal income tax rate is 30% and the provincial income tax amounts to 45% of the federal income tax. • The working interest in the well is 85%. The discount interest rate is 8%. (a) When does the well reach the end of its economic life? (b) Calculate the NPV and the IRR for 8%, 10%, and 12% discount rates. Discuss if it is worth Snappy Petroleum undertaking this project. (c) Using a Data Table command, determine the NPV for changes in initial daily production of -15%, -10%, -5%, 0%, 5%, 10%, and 15%. Use a discount rate of 8%. (d) Using a Data Table command, determine the NPV for changes in initial oil price of 15%, -10%, -5%, 0%, 5%, 10%, and 15%. Use a discount rate of 8%. (e) To which factor is the NPV more sensitive, to changes in the initial oil price or to changes in the initial daily production? Support your answer by a graph. What does it imply for Snappy Petroleum? (f) Using the Data Table command determine the NPV for changes in both initial oil price and variable cost, for changes of -15%, -10%, -5%, 0%, 5%, 10%, and 15%. Use a discount rate of 8%