FINA503
PROJECT - COMPANY VALUATION
Due Date: 11.59 pm, Sunday 13th October 2024
Weighting: 50%
Type: Individual
Submission: Electronic Submission via Canvas
Instructions for the Project
Each student is assigned a New Zealand based company for valuation (please see the Canvas for your assigned company). Students are required to develop a company valuation model using the discounted cashflow valuation and relative valuation method for the assigned company.
The model should contain:
Financial statements that are forecasted 5 years into the future based on the 5 years’ worth of financial statement (Balance Sheet, Income Statement, Cashflow Statement). Students are required to build the financial statements for the forecasting purpose from the annual report .
o Assumptions made in forecasting the future should be identified and justified with reference to past financial information, other annual report information and any other information sources that the student deems relevant.
o All assumptions made by the student should be reasonable and justified.
o The model should also allow the user to change key inputs to assess the impact on the firm’s value.
o Students should feel free to add in additional information from the annual reports where it will add value to their model i.e., segmented revenue information or more detailed expense data.
The model should accurately compute the weighted average cost of capital and beta with the option of allowing users to alter the frequency of the beta used in the calculation of the WACC (i.e. one year daily data versus five years monthly). The student should identify the most appropriate period and frequency for their company.
Accurately employ the Free Cashflows formula to compute the cashflows for the next 5 years based on the forecasted financial statements’ information, and then use the Discounted Cashflow and/or relative valuation method to compute the value of the company and hence stock price.
The valuation model should conform. to the rules of good financial model design.
The model will only be marked if it is built within the spreadsheet file “ Valuation Project (S2 2024).xlsm” available from Canvas. Students are expected to be able to explain or demonstrate any features or techniques in their assignments if requested.
Marking Criteria
To receive a C Grade, students must ensure their model meets the following requirements:
|
Model
|
Model contains simple financial statements lacking in detail or that are exact copies of those provided by the company
Model contains suitable forecasting methods for most values
Weighted Average Cost of Capital is correctly calculated
Model correctly calculates the beta of the company
Model correctly calculates the financial cashflows
Company Valuation is accurate given the inputs without excessive changes in values
|
|
Layout
|
Calculator conforms to the following rules of good design
o Clear separation of inputs and outputs
o Formulas are correctly linked without any hardcoded values
Model has a summary page with a few inputs and the key output
Model outlines key findings
|
|
Features
|
Model contains limited sensitivity analysis
|
To receive a B Grade, students must ensure their model meets the following requirements:
|
Model
|
Model contains financial statements that incorporate some additional information from the notes to the financial statements or consolidation of the information provided by the company
Model contains suitable forecasting methods for most values
Assumptions are listed with justification for some items
Company Valuation is accurate given the inputs without excessive changes in values or plugs
Weighted Average Cost of Capital is correctly calculated
Model correctly calculates the beta of the company with the option of at least two frequencies (i.e. daily and monthly)
Model correctly calculates the financial cashflows
|
|
Layout
|
Calculator conforms to the following rules of good design
o Clear separation of inputs and outputs
o Formulas are correct and correctly linked
o Employs menus to make the model easy to use
o Report attempts to justify the assumptions made clearly
o Model outlines key findings clearly
|
|
Features
|
Model contains a summary page with most key inputs and the key findings
Model contains limited sensitivity analysis
Some Excel controls are used
|
To receive an A Grade, students must ensure their model meets the following requirements:
|
Model
|
Model contains financial statements that incorporate considerable information from the notes to the financial statements and external sources and contain only items that are suitable for forecasting with all others consolidated appropriately
Model contains suitable forecasting methods (DDM and other methods) for all values
Assumptions are listed with excellent justification for most items
Company valuation is accurate given the inputs without excessive changes in values or plugs
Weighted Average Cost of Capital is correctly calculated
Model correctly calculates the beta of the company with the option of using a variety of different periods and frequencies
Model correctly calculates the financial cashflows
|
|
Layout
|
Calculator conforms to the following rules of good design
o Clear separation of inputs and outputs
o Formulas are correct and correctly linked
o Employs menus to make the model easy to use
o Changes to assumptions used to forecast financial statements are easy to do
o Report attempts to justify the assumptions made very clearly
o Model outlines key findings exceptionally clearly
|
|
Features
|
• Data validation is employed in the summary sheet to prevent inappropriate inputs for all inputs
• Excel controls are used extensively to make changes to the model easy and intuitive
• Model contains a well set out and easy to understand summary page containing all key inputs and highlighting the key findings and other key information
• Model employs sensitivity analysis to examine the effect of changes in key drivers of value
|