Choose any Australian or US listed firm that has 4 years of past financial reports available, is not a bank and has no preferred stock or multiple classes of stock. Do not choose GameStop or Woolworths since these will be the examplars shown in the recorded classes. 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 Choose any Aust.

Finance assignment
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 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 Australian or US listed firm that has 4 years of past financial reports available, is not a bank and has no preferred stock or multiple classes of stock.
Do not choose GameStop or Woolworths since these will be the examplars shown in the recorded classes.
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
Choose any Australian or US listed firm that has 4 years of past financial reports available, is not a bank and has no preferred stock or multiple classes of stock.
Try to avoid choosing the same firm as your friends. Doing so and sharing files will result in serious plagiarism problems.
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 cap 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).
You can download financial reports from the Yahoo finance website which is free and online.
Copy these financial statements into your questions spreadsheet tab under row 115.
Re-arrange this data so that its in ascending date order from left to right. Try to line the dates up with the dates given at the top since this will make calculations easier to follow. But do not add, move or remove columns or rows above line 115.
You should construct pro-forma income statements and balance sheets in the questions spreadsheet tab under line 115. This section will not be marked, but you will use this data to answer the questions.
Note that depreciation and amortisation may not be shown in the Yahoo finance data, which is a headache. You may have to look up the last years depreciation 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.
Each week during Keiths Zoom class well walk through certain technically challenging sections of how to solve the assignment, particularly those requiring advanced spreadsheet skills.
The class will be recorded and posted to online for everyone to see.
Not all questions will be discussed and answered in these classes or in 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 in 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 de Apellániz, Eduardo and F. Acín, Javier, Survey: Market Risk Premium and Risk-Free Rate used for 81 countries in 2020 (March 25, 2020). Available at SSRN: https://ssrn.com/abstract=3560869
Past Past Past Past Forecast Forecast Forecast Forecast Forecast This column N is for marking formulas only. Please dont enter calculations into this column or move it since it may risk scoring zero.
Question Marks Year index -3 -2 -1 0 1 2 3 4 5
Year (replace with your firms year end dates) 30-Jun-17 30-Jun-18 30-Jun-19 30-Jun-20 30-Jun-21 30-Jun-22 30-Jun-23 30-Jun-24 30-Jun-25
Q1a 5 Revenue growth rate (For past: calculate based on past revenues. For forecast: provide reasonable estimate. Note that the year 5 growth rate will be used in the OFCF perpetuity formula. Ensure its a reasonable choice)
Q1b 2 Revenue (For past: retrieve from P&L. For forecast: calculate based on growth rates)
Q1c 1 Net Income (For past: retrieve from P&L. For forecast: estimate based on forecast P&L done somewhere below line 115)
Q1d 1 Depreciation & amortisation expense (estimate based on forecast P&L done somewhere below line 115)
Q1e 1 Tangible and intangible capital assets, carrying amount, net of depreciation and amortisation (estimate based on forecast balance sheet done somewhere below line 115)
Q1f 4 CapEx (calculate based on above net capital assets and other items)
Q1g 1 Operating net working capital (estimate based on forecast balance sheet done somewhere below line 115)
Q1h 2 DeltaNOWC (calculate based on above operating working capital and other items)
Q1i 1 Interest expense (estimate based on forecast P&L done somewhere below line 115)
tc, corporate tax rate (feel free to change this if you wish) 0.3 0.3 0.3 0.3 0.3 0.3
Q1j 4 OFCF (calculate based on above items)
Q2a 2 rf (retrieve from online source of 10 year government bond yields)
Q2b 2 betaE (retrieve from reputable online source such as Reuters)
Q2c 2 MRP (retrieve from reputable online source such as Fernandez, de Apellániz and Acín (2020) https://ssrn.com/abstract=3560869
Q2d 2 rE (calculate based on CAPM)
Q3a 1 Debt (book value. Retrieve from balance sheet)
Q3b 1 rD draft estimate based on calculation of InterestExpenseOverPriorYear / BookDebtAtStartOfPriorYear
Q3c 1 rD draft estimate using alternative method such as comparable firms IntExp/BookDebt, credit ratings or traded bond yields
Q3d 4 rD final estimate, used as an input into WACC after tax
Q3e 1 Equity (traded market value, retrieve from stock exchange)
Q3f 1 Equity (book value, retrieve from balance sheet)
Q3g 4 D / V final estimate. Calculate using the above data you think most appropriate. This debt-to-assets ratio will be used as input into WACC after tax.
Q3h 1 WACC after tax. Calculate using corporate tax rate, rD final estimate, and other data stated above using a formula. Do not use this WACC after tax in any calculations.
Q3i 1 Please copy, paste special, value the WACC after tax in the above cell to this questions yellow cell, and base all calculations below on this hard-coded WACC after tax. This cell must not contain a formula because otherwise the Goal Seek process needed in Q6 and the Data Table process needed to do a sensitivity analysis in Q7 will not work. Use this WACC after tax in all below calculations.
Q4a 2 PV of OFCF for each year 1 to 4. Calculate using above data, ensure WACC after tax from Q3i is used.
Q4b 2 Terminal value as at year 4 based on perpetuity of year 5 OFCF growing at year 5 revenue growth rate forever. Calculate using above data.
Q4c 2 PV of the Terminal Value based on perpetuity. Calculate using above data
Q4d 2 Assets (model estimated value. Calculate using above data)
Q4e 2 Equity (model estimated value. Calculate using above data)
Q4f 1 Units of all above cash flows. Retrieve from financial statements. For example, if in millions, then type 1,000,000.
Q4g 1 Number of shares. Retrieve from online source or financial statements. Ensure consistent units with items above. For example, if the number of shares is 700 million, but your cash flows above are all in millions, then your number of shares here should be 700.
Q4h 2 Share price in dollars per one share (model estimated value based on perpetuity TV. Calculate based on above data)
Q4i 2 Share price in dollars per one share (traded market value. Retrieve from online source at the same recent data that the number of shares and market capitalisation of equity were found above)
Q4j 2 NPV in dollars of buying one share assuming model is correct and market price is not correct. Calculate based on above data
Undertake a multiples valuation using this table: Three Similar Listed Firms Price-to-Sales Ratios
Firm name and ticker code Annual sales (last reported, in same units as other items listed above) Equity market capitalisation (in same units as other items listed above) Price-to-sales ratio Data source or link
Q5a 1
Q5b 1
Q5c 1
Q5d 1 Arithmetic average price-to-sales ratio:
Q5e 3 Estimate the Terminal Value (TV) at year 5 was based on the arithmetic average price-to-sales ratio found above rather than the perpetuity formula.
Q5f 3 Estimate the share price of your firm based on this multiples-based TV.
Remember to include the 5th year OFCF in the valuation as well, since the multiples valuation would normally be assumed to be the price just a moment after the cash flow (OFCF) at that time is paid.
Q6 5 Find the WACC after tax that makes the market and model-estimated share prices equal.
In other words, find the IRR. The model-estimated share price to be made equal to the market share price should be the one using the perpetuity formula Terminal Value (TV) from Q4h, not the price-to-sales ratio TV share price from Q5f.
Note that you will have to use Goal Seek or Solver to complete this. Using the IRR formula wont work properly since the WACC in the terminal value will not be adjusted properly.
If using Goal Seek, the by changing cell should be your hard-coded WACC after tax from Q3i, not the formula from Q3h.
Once youve found your answer to this question using Goal Seek, copy this hard-coded WACC after tax from Q3i into the yellow cell provided in this question, then
overwrite Q3is yellow cell back to its original hard-coded value that matches your answer in Q3h, using copy and paste by value.
Q7 7 Conduct a 2-dimensional sensitity analysis of your share price (model estimated based on perpetuity TV) by varying the WACC after tax (in the tables left column) and the year 5 revenue growth rate (in the tables top row). Make the WACC numbers increase from smaller to bigger as theyre written from top to bottom, and make the growth rate numbers increase from smaller to bigger as theyre listed from left to right.
Ensure that the table shows your base case share price bolded in the middle somewhere.
Year 5 revenue growth rate
WACC after tax
Q8 7 On the same graph, show 3 lines for revenue and net income from time -4 to 5, and OFCF from time 1 to 5.
Ensure that net income and OFCF are on the primary (left hand side) axis, while revenue is on the secondary (right hand side) axis.
Place your graph into the yellow area below.
View this Tesla valuation report by Tasha Keeney from ARK Invest, dated May 22nd 2019.
The spreadsheet model named Tesla Valuation for Github_5.27.19_v3.5.xlsx is copied in this workbook in the sheet Tesla Model for you to see. The below questions are based on this model.

Tesla Valuation Model: Are Investors Stuck in Reverse?

Q9a 1 Does ARK value Tesla using DCF or multiples? Write DCF or multiples.
Q9b 1 Do they value Tesla as a single uniform entity or by summing valuations of multiple divisions? Write single or sum.
Q9c 1 What assumptions are made about Teslas interest-bearing leverage in 2023? Write levered or unlevered.
Q9d 1 Does the valuation model include the benefit of interest tax shields? Write includes or excludes.
Q9e 2 Based on the current Tesla share price of $620, is this better than the bull scenario, worse than the bear scenario or in between? Write better, worse or between.
With regards to the above question, keep in mind that there was a stock split in 2020 that you should adjust for. Ignore timing differences between now and 2023 and simply compare stock prices, adjusted for the stock split.
Q9f 2 Are the stock prices on line 68 of the Tesla vauation model present values as at 2018 or future values as at 2023? Write 2018 or 2023.
100
This cell should be C115. If its not, youve inserted rows or columns and the auto-marking formulas will not work which will result in a mark of zero. Start again by re-downloading this spreadsheet and refill your answers carefully. Note for students: This cell should be N115. If its not, youve inserted rows or columns and the auto-marking formulas will not work which will result in a mark of zero. Start again by re-downloading this spreadsheet and refill your answers carefully.