代做AP/ADMS4540 Assignment #1 Summer 2024帮做R程序

AP/ADMS4540

Assignment #1

Summer 2024

Due Wednesday, June 05, 4:00PM (ATK 289)

Question No.	Topic	Full Marks	Marks Obtained
1	ODA	20
2	Bond Refunding	15
3	Risk & Return and DGM	20
4	Inflation, Bond and Stocks Returns (Excel)	25
5	Financial Planning	20
Total		100

Instructions:

(1) This assignment is to be submitted individually in your own handwriting except for the submission of Excel graphs or printouts as required in Question 4. Discussion with other students is allowed. Any external sources used, including data sources, should be referenced. MAKE THIS PAGE AS THE TOP PAGE OF YOUR SUBMITTED ANSWER AND WRITE YOUR NAME, STUDENT # AND SECTION. USE REGULAR PAGES.

(2) This assignment is due by Wednesday, June 05, at 4:00pm in ATK 289.

(3) This assignment carries a total of 100 marks.

(4) This assignment (except for question 5) covers material to be tested on the midterm exam. So, it is important that you learn as much as possible from working on this assignment.

(5) This assignment must be handwritten. Any work that is not original handwritten (e.g., printed or photocopied) will receive zero credit. Work that is too difficult to read due to messiness and poor handwriting will also receive zero credit. You must show all the necessary working steps to receive full credit. You are to submit printouts of the Excel calculations or graphs for parts of the inflation and stock returns question 4 as instructed in the question and handwrite your answer to the other parts of question 4.

(6) Decimal places: please keep at least 4 in your calculations and round to the nearest penny or basis point in your final answers.

Question 1 ODA (20 Marks)

To qualify as official development assistance (ODA), development loans which are concessional in nature must have a grant element of at least 25 percent, calculated using a stated annual interest rate of 10 percent. Suppose Canada Infrastructure Bank has decided to consider a development loan to Haiti for its infrastructure development. The loan under consideration is as follows: $25,000,000 to be amortized over 7 years by 14 semi-annual payments, after a grace period of 4 years during which only interest would be paid semi-annually. This loan would charge interest at a stated annual rate of 5.5 percent.

I)              Determine whether this loan would qualify as ODA.

II)            What is the maximum interest rate that could be charged for this loan to qualify as ODA? Note: Show all working steps clearly.

(Important Note: Use a rate of 5% or 6% if you want to decrease or increase the subsidized rate for the calculation of maximum rate. Justify the change of your rate.)

Question 2 Bond Refunding (15 Marks)

Virginia Foods Group (VFG) is considering whether to refund an old issue of $90,000,000, 7.5 percent coupon (paid annually) twenty-year bonds that were sold eight years ago. A new issue of $100,000,000 twelve-year bonds can be sold with a coupon rate of 5.5 percent (paid annually). A call premium of 6.2 percent will be required to retire the old bonds and flotation costs of $8,000,000 will apply to the new issue. The tax rate applicable is 40 percent and VFG expects that there will be a one-month overlap during which any funds can be invested in Treasury bills yielding 1 percent. The additional $10,000,000 from the new bond issue could be invested in a twelve-year project with an expected net present value of $2,200,000. Should VFG refund the old issue of $90,000,000 bonds?   Note: Show all four working steps clearly.

Question 3

Part a) Risk and Return (10 Marks)

Watching YouTube Videos

a)    Watch the following YouTube videos on your PC or laptop or mobile device and then write up a two-page summary (max 500 words and max 2 pages) of what you learnt from these YouTube videos. There will not be an answer key provided for this question. Instead, the markers will also have watched these videos, and if what you write of these videos is consistent with their understanding and different from other students, you will receive full credit. Your essay answer must also be in your own handwriting.

Bear Stearns is fine?

https://www.youtube.com/watch?v=1QEKdsEfLY0

Lessons from the father of modern portfolio theory

http://www.youtube.com/watch?v=5Y1MBc_Vj3w

There are no shortcuts to investing: Nobel laureate William Sharpe

http://www.youtube.com/watch?v=pGIzygsvqck

Part b) Stock Pricing – DGM (10 Marks)

AT the end of May 2020, the price of Walmart stock was approximately $123. The company was expected to earn about $5.55 a share in the coming year and was projected to pay 40% of earnings as dividends. Dividends forecast from 2021 – 2025 are provided in the table below.

Year                                               1                2               3                4              5

Earnings                                       5.55           5.86           6.19           6.54           6.91

Dividends (40% of earnings)           2.22           2.34           2.48            2.62          2.76

i)  What is the dividend growth rate? You should find the rate using both arithmetic and geometric averages.

ii)  Suppose that on further analysis you decide that after year 5 Walmart’s earnings and dividends will grow by a constant 2.5% a year  indefinitely.  Find your estimate of the value of Walmart stock at year 0 assuming the required rate of return 4.8%?

Question 4 Inflation, Bond Yields and Stock Returns (25 Marks)

Go to the Statistics Canada website and download the following two tables:

Table 18-10-0004-01 Consumer Price Index, monthly, not seasonally adjusted

https://www150.statcan.gc.ca/t1/tbl1/en/tv.action?pid=1810000401

Geography: Canada

Set Reference Period: January 1971 to December 2023

Use the CPI values for “All Items” .

Table 10-10-0125-01 Toronto Stock Exchange (TSE) statistics

https://www150.statcan.gc.ca/t1/tbl1/en/tv.action?pid=1010012501

Set Reference Period: January 1971 to September 2023

Use the “close” values for the composite index. This table does not include three months data of October-December 2023. For that reason, find the TSX monthly close (the “Close (S)” value on the last trading day of the month) for October 2023, November 2023 and December 2023 from TMX Money:

https://money.tmx.com/en/quote/%5ETSX/price-history

Note that the CPI value and TSE close are at the end of the month and the TSE close is a nominal value.

4a. Inflation in Canada (1971- 2023): In a worksheet get the months in a column using “transpose” in special paste. In the next column get the CPI values. Graph the CPI values of all items over the reference period (January 1971 to December 2023) in Excel. From this graph, determine the TWO periods where the graph turns particularly steep and remains steep. What is meant by this steeper parts of the graph? (3 marks)

To calculate annual inflation for month t, the change in CPI should be calculated as the value in month t minus the value in month (t-12) and then divided by the value in month (t-12). For example, if month t is January 1972, then month (t-12) is January 1971. Using this method, calculate the year-over-year inflation for all these years in the next column to CPI. Submit a printout of the graph of year-over-year inflation for these 53 years. What happened to year- over-year inflation in Canada in December 2023? Why? (3 marks)

Also calculate the average and standard deviation of the annual inflation over this period (2 marks).

Hints:

https://www150.statcan.gc.ca/n1/daily-quotidien/240116/dq240116a-eng.htm https://www.cbc.ca/news/business/inflation-dec-2023-1.7084981

https://www.reuters.com/markets/canadas-inflation-rises-34-dec-meeting-expectations-2024-01-16/

4b. Stock Returns (1971 - 2023): In a different worksheet of the same workbook, calculate the monthly returns for S&P/TSX Composite using the closing values. (The change should be calculated as the value in month t minus the value in month (t-1) and then divided by the value in month (t-1) to find the monthly returns). Calculate the mean and standard deviation for these monthly returns. Annualize the monthly average and standard deviation of returns.

(5 marks)

Submit a printout and a graph of monthly annual inflation (part a) and nominal TSE returns for the last 50 years (1973 – 2023) and  report their correlation.  How  does  high  inflation affect  nominal  (not  inflation-adjusted) TSE returns in the real world? (4 marks)

4c. Next, in a third worksheet, copy the data of CPI and TSE close. Calculate the real TSE close by dividing the nominal TSE close by the corresponding CPI. Then calculate the monthly TSE real return from January 1973 to December 1982 by looking at the percentage change of the real TSE close for these 10 years. Next, graph the TSE real return for these 10 years and report whether monthly real TSE returns are mostly positive or mostly negative. What does this tell you about the real return of investing in Canadian stocks in a period of high inflation? Submit the graph with your handwritten answer. (3 marks)

4d. You should work in a single excel file (workbook) having different worksheets as specified earlier for inflation and S&P/TSX composite data and real return calculation. Save your file as [LastName, FirstName Assignment1.xlsx]. Submit your Excel file to the required folder on eClass.

If you are confused with small things, say error in date/year, apply your own judgements. (5 marks)

Question 5 Financial Planning (20 marks)

You have been employed as a Financial Analyst with the food services provider Blue Mountain Foods Corporation.

Now it is near the end of 2023, the company’s fiscal year. It is also the time to perform financial planning for the future years. The following are Blue Mountain Foods Corporation’s financial statements for the year ended December 31, 2023.

BLUE MOUNTAIN FOODS CORPORATION

For the period from January 1 to December 31, 2023

Income Statement

Sales $95,400

Costs $61,300

EBIT $34,100

Interest expense $4,000

Taxable income $30,100

Taxes (34%) $10,234

Net income $19,866

Dividends $10,926

Addition to retained earnings $8,940

BLUE MOUNTAIN FOODS CORPORATION

Balance Sheet

As at December 31, 2023

Assets ($) Liabilities and Owners' Equity ($)

Current assets Current liabilities

Cash $3,215 Accounts payable $5,315

Accounts receivable $6,314 Notes payable $7,420

Inventory $8,750 Total current liabilities $12,735

Total current assets $18,279 Long term debt $21,400

Net Plant and equipment $45,231 Owners' equity

Common stock $25,000

Total Assets $63,510 Retained earnings $4,375

Total liabilities and owners' equity    $63,510

You have been asked to perform financial planning for the 12-month period ending December 31, 2024. You were given the following assumptions to work with:

1)    A 15% increase in sales.

2)    Costs vary with sales and the dividend payout ratio is constant.

3)    All assets and accounts payable vary in the same extent (growth rate) with sales, whereas notes payable,

interest expense and LT debt do not change and remain constant. You are required to answer the following:

a)     What is the projected addition to retained earnings? Prepare a proforma income statement to support your answer. (10 marks)

b)    Prepare a pro forma balance sheet and calculate EFN. When there is a surplus or shortage of EFN, what does the company needs to do the fill in the gap if EFN shortage or surplus?  (10 marks)