This Assignment requires you to apply what you have learned in Topics 6 (valuation of shares), 7 (regression analysis) and 8 (simulation) to build a single-workbook spreadsheet model that can simulate and forecast the share prices. The model should provide a recommendation to share traders whether to buy or sell a given stock on a given day based on the simulated and forecasted share prices.
Before you begin your Assignment, you are required to download the following data series from either the Bloomberg Professional in the SMART Lab (JO2.102) or the Yahoo!7 Finance website, URL: https://au.finance.yahoo.com and the DatAnalysis on ECU library databases.
i) Daily closing share prices of any 15 companies included in the S&P/ASX 200 index over a period of 15 months from 1 June 2016 to 31 August 2017. These price series can be downloaded from Bloomberg Professional, DatAnalysis or Yahoo!7 Finance.
ii) Daily closing values of All Ordinaries index over the same period of 15 months can be downloaded from Bloomberg Professional or Yahoo!7 Finance.
iii) From your selected 15 companies in (i), choose five companies for your Share Trading Model. For each of the five companies, download the most recent
a. Annual dividends (i.e. franked interim and final dividends) paid in financial year 2016/17.
b. Dividend payout ratio, debt-equity ratio (or gross gearing), return on asset, return on equity and costs of long-term debt from their 2016/17 Annual Reports (i.e. Income Statement and Balance Sheet). If a company has multiple sources of debt, you should calculate its weight average cost of debt.
These information can be retrieved from Bloomberg Professional or DatAnalysis.
Below are information about the four different methods used to simulate and forecast the next day share prices for each of the five companies in your Share Trading Model. Bear in mind that the predicted share prices are for next day of current trading date provided by the user. For example, if the current date of trading is 16 October 2017, then the predicted share prices will be for 17 October 2017.
1. Geometric Brownian motion (GBM) method
i) Simulate the next day share price 10 times using the GBM method.
ii) Assume there are 252 trading days in a year, daily returns on the stock are continuously compounded, and the initial value for simulation is the user input of current share price.
iii) The predicted share price for next day is the arithmetic average of the 10 simulated share prices.
2. Autoregressive (AR) model
i) Estimate four AR models of order one to four, i.e. AR(1), AR(2) AR(3) and AR(4) models using the downloaded price series.
ii) For each AR model, test the statistical significance of the estimated coefficients and determine the best AR model for predicting the share price.
iii) Predict the company's share price for the next day and its 95% forecast interval using the best AR model. [Note that the predicted share price is dependent on the user input of company's four share prices, i.e. current, lag 1, lag 2 and lag 3 prices.]
3. Capital asset pricing model (CAPM)
i) Estimate the beta factors of individual 15 companies using return on the All Ordinaries index as the proxy for market return.
ii) Regress average returns of the 15 companies on their beta factors to derive the security market line (SML).
iii) With the user input of the company's beta factor, predict the company's expected daily return using the SML equation estimated in 3(ii).
iv) Estimate the company's expected share price for the next day using the user input of current share price and the expected daily return estimated in 3(iii).
4. Constant growth dividend discount model (DDM)
i) Calculate the expected annual growth rate of dividend for each of the five companies using the economic fundamentals of the company. Assume the last annual dividend paid will grow at this estimated growth rate indefinitely.
ii) Estimate each company's weighted average cost of capital using the company's debt-equity ratio, return on equity and weighted average cost of debt retrieved from the Annual Reports. If the company's return on equity is negative, you should use the annualised cost of ordinary share equity estimated from the SML equation in 3(iii) as a proxy.
iii) The predicted share price is the value of each company's share derived using the constant growth DDM.
There are five categories of recommendation for your Share Trading Model which are determined by the four share prices derived above:
i) "Buy" - All four predicted share prices using the four different methods (i.e. GBM method, AR model, CAPM and constant growth DDM) are higher than current share price.
ii) "Sell" - All four predicted share prices are lower than current share price.
iii) "Accumulate" - Three of the four predicted share prices are higher than current share price.
iv) "Reduce" - Three of the four predicted share prices are lower than current share price.
v) "Hold" - The remaining combinations that do not satisfy any of the four categories listed above.
The Share Trading Model
Structure of the Model
The Share Trading Model should be structured in a three-tier fashion.
The first tier is the User Interface (UI) tier. There should not be any Excel formula except cell links contained in this tier; this provision excludes Excel formulas embedded in conditional formatting and data validation. For this Assignment, the UI tier should have only one worksheet for entering inputs and showing outputs.
The second tier is the Application tier where all the calculations are performed.
The third tier is the Data Access and Storage (DAS) tier where all the parameters, the price series, range names used, comments, etc. are stored. These should include documentation of the functionality of the model, the procedure in the Application tier, sheet protection password, and some basic information including author's name, version number, completion date, etc.
Mandatory inputs from the user
The inputs from the user are:
- Name of the company.
- Date of share trading.
- Current share price and lagged prices for three trading days.
- Company's beta factor.
Mandatory outputs from the Model
The outputs should contain the following mandatory information:
- Name and ASX code of the company.
- Date of share trading.
- Simulated average share price using the GBM method.
- Forecasted share price and its 95% forecast interval using the best AR model.
- Expected share price using the CAPM and the company's beta provided by the user.
- Share valuation using the constant growth DDM and its estimated dividend growth rate.
- Recommendation: Buy, Accumulate, Reduce, Sell, or Hold.
Technical Issue
I want to cover several technical issues that you need to pay attention to.
Preciseness of your coding
When coding your formulas, pay attention to your spelling, punctuation (such as parentheses and commas), and syntax. Yes, mistakes are a fact of life, but unfortunately, Excel is very unforgiving when it comes to mistakes.
User interface and user error
It is useful to colour-code your cells so that users and other modellers can understand what is going on and where to put their inputs. One way to preserve the integrity of an Excel model is to minimise user errors. This can be accomplished by several techniques. For examples: (a) Use a drop-down list, (b) provide instant feedback to users when they make a mistake in their entry. The latter can be achieved by using either conditional formatting or feedback messages.
Input and Output Control
Your application must be able to prevent the Model to display any output under the following situation:
- When there is missing mandatory input.
- When there is error in input(s).
This is an important part of modelling. You do not want the model to show error values when only some of the mandatory inputs are entered. And you do not want the model to recalculate at inappropriate time. Moreover, input-output control can also help to eliminate all error values.
Input and output control is quite easy to do. Suppose you have three inputs in Cells F12, F13, and F15. You want all three inputs to be present before proceeding to do your calculation. What you can do is to set up a control cell (range name = control), and write into it the following code:
When "YES" appears in the control cell, it means all the mandatory inputs are in. Hyperlinks Use hyperlinks to navigate between the worksheets. Look for the "Hyperlink2013.pdf" file in the unit Blackboard site that provides the procedure to establish a hyperlink between two worksheets.
VBA
If you wish to make the spreadsheet model operates efficiently, such as deleting all the entries by pressing one single button, you can install a VBA button. Look for the "BtnDelete2013.pdf" file in the unit Blackboard site that provides the procedure to add a Cancel button using VBA in Excel 2013.
Testing your model
Before you submit your Assignment, you have to test your model thoroughly. A lousy model not only frustrates users, it does nothing good for your reputation. Make sure you test your model thoroughly before you submit it.
Documentation and Protection of Your Model
You should provide a brief description on the functionality of the Model and the procedure in deriving the outputs in the Application tier. Some basic information about the Model to be included are Title, Version number, Date created, Author, Contact address, phone number and email, and Client's organization. All information and documentation should be included in the DAS tier.
In the real world, users cannot access the APP and DAS tiers of the model. They are safely hidden away and password protected. For this Assignment, do not hide or protect the APP and DAS worksheets with password to facilitate marking. Demonstrate your ability to protect the worksheet in the UI worksheet. Make sure you password protect all cells except for the input cells and provide the password in the DAS worksheet.
Check List for your Model
General
- Have you adopted a three-tier structure?
- Have you debugged all error values, conceptual and technical errors in your workbook?
- Have you protected your UI tier except input cells? Do not protect worksheets of the APP and DAS tiers.
- Have you cleared all mandatory inputs before submitting your Model?
User Interface (UI) Tier
- Have you given users clear and precise instructions for using the Model?
- Have you taken measures to prevent user errors?
- Have you eliminated all your calculations in this tier? [Note: Links, hyperlinks, and equations embedded in validation and conditional formatting are not calculations.]
- Have you used conditional formatting to improve presentation of your Model and provided instant feedback (say, messages) to user?
- Have you protected this worksheet and provided the password in the DAS tier?
Application (APP) Tier
- Have you done checks on the validity of the data entered?
- Have you done all your calculations in this tier and only this tier?
- Have you got rid of all hard coding in your Excel functions?
- Have you documented the procedure in deriving your outputs and provided the report in the DAS tier?
Data Access and Storage (DAS) Tier
- Have you eliminated all your calculations in this tier?
- Have you provided a descriptive summary on the functionality of your Model?
- Have you documented the author, version number, completion date, etc.?
- If you use range names, have you documented those range names?
Attachment:- Information for assignment.rar