Instructions:
Do not insert or add rows or columns since the assignment will be auto-marked using formulas which will expect to see your answers in pre-determined cells. Adding rows and columns will upset this process and result in a zero mark.
Only the answers entered into the yellow cells of the -questions- tab will be marked.
Do not link this spreadsheet to other spreadsheets saved in different files since you will only be able to submit a single file online, so any links will not work and risk scoring zero.
Choose any one Australian or US listed firm that has the past 5 years of financial statements available, is not a bank, and has no preferred stock or multiple classes of stock.
If you really want to do a listed firm in Europe, Asia, your home country or somewhere else, please go ahead, just make sure that you can find and download the firms financial statements of the past 4 years.
Note that only banks tend to have preferred stock. Australian companies have only one class of common stock, but some US firms such as News Corp have multiple classes of stock and you should avoid choosing them, since it will complicate the process of finding the number of shares which is used to calculate the share price in Question 4 and Question 5.
Side note: If you really want to, you can pick a US firm with multiple classes of stock, just be sure to calculate the share price of the most liquid class of shares and make an adjustment to the number of shares so that your
share price is equal to the equity market capitalization divided by this adjusted number of shares.
Say the 1 million class A shares have 10 times the voting power and dividends as the 100 million class B most liquid shares, then each class A share should count for 10 class B shares, so the total number of shares should be 110m (=1m*10 + 100m).
Try to avoid choosing the same firm as your friends. Doing so and sharing files will result in serious plagiarism problems.
You can download financial statements from the Yahoo finance website which is free and online. Or using S&P Capital IQ Pro to find out the financial statements.
Just Google -FirmName Yahoo Finance-, e.g. -Woolworths Yahoo Finance-. Or using S&P Captial IQ Pro database, which will be discussed.
On your firms Yahoo Finance website, click the-Financials- blue tab, you can see the Annual -Income statement-, -Balance sheet-, and -Cash flow- of the past 4 years.
Copy these financial statements into your assignment questions spreadsheet tab under line 115. Normally, Income statement comes first, then Balance sheet, and finally Cash flow.
For example, select all income statement data from Yahoo Finance - ctrl c, then enter C118 of -questions- spreadsheet tab and right click to do -paste special -text-.
Delete the -TTM- column data in -questions- tab, because we only focus on the financial years annual data, but TTM (Trailing Twelve Month) is the past twelve months data which is not necessarily the standard financial years data.
Similarly, you can copy Balance sheet and Cash flow from Yahoo Finance into -questions- tab.
Re-arrange these financial statements data, so that they are in ascending date order from left to right, consistent with the dates given at the top lines 1 and 2, since this will make calculations easier to follow.
Note that: in addition to Yahoo Finance, please feel free to use any data sources (S&P database) and whatever methods to copy your firms past 4 years financial data into -questions- tab under line 115. Also, make sure these past annual data are ordered from the old to the new.
You can try to forecast the future 5 years each item of income statements and balance sheets in questions tab under line 115, but this forecast is not mandatory and this forecast section will not be marked in this assignment, because the accurate forecast requires advanced and excellent skills in marketing, accounting, finance, and macroeconomic disciplines.
In this assignment, we just want to show you a whole picture about the firm valuation and thus have tried to simplify the detailed valuation process.
To simplify the forecast, we assume that most items in the annual income statement and balance sheet will change by this year’s revenue growth rate, such as COGS, SG&A, depreciation (and amortization) expense, interest expense, net income, capital assets, current assets and current liabilities. This assumption makes sense because if your firm is making more revenue, in general your firm will spend more costs and expenses, and also need to buy more long-term capital assets and short-term working net assets to support the certain revenue growth.
Thus, in Question 1, for the forecast data, you can either calculate them based on the revenue growth rate or estimate them based on your forecasted financial statements done somewhere below line 115.
You can also see that a lot of parts in Question 1 only have one mark, which basically means just putting a reasonable number in there. We are probably not going to check it much.
Note that depreciation (and amortisation) expense may not be directly shown in Income statement of Yahoo finance. You may have to look up -Accumulated depreciation- of Balance sheet, and use this years accumulated depreciation minus the before years accumulated depreciation to get this years deprecation expense. Or you can check depreciation (and amortisation) expense manually by downloading the firms annual report.
After this, you might try extrapolating the depreciation (and amortisation) expense into the future using, say, the percent of sales method or percent of assets method.
Please carefully read this instructions and follow the step-by-step guidance document available from iLearn, and then try your best to first do the assignment.
Not all questions will be discussed and answered in these classes or on the forums since we wish for you to discover (and remember!) the process yourself or with friends.
We encourage you to discuss the assignment with other students and teachers in class and on the forums. Completing the assignment is meant to be a learning process and will hopefuly promote discussion and thought.
But be very careful not to send your assignment file to any other students.
Plagiarism is looked upon as a very serious offense by the university and can result in failure of the course and a permanent record on your academic transcript.
Do not re-use assignments submitted by students in past semesters, this is plagiarism. This assignment must be your own work.
Please submit the assignment online
Reference for Q2c:
Fernandez, Pablo and bañuls, sofia and Fernandez Acin, Pablo, Survey: Market Risk Premium and Risk-Free Rate used for 88 countries in 2021 (June 6, 2021). Available at SSRN: https://ssrn.com/abstract=3861152