Building a Pro Forma Financial Model and Valuing NCK Shares

Building Pro Forma evaluation for Australian shares

In the Assignment file, you will find information for Nick Scali Limited (NCK). Use this information and the templates from Topic 3’s seminar (including the seminar questions) to value NCK using five years of financial data.

For this Assignment, you only need to provide your answers in one Excel file that contains all spreadsheets. Any necessary explanation and arguments should also be included in this Excel file (in a cell or note).

For NCK, please calculate the following values [4 marks each for each of the following]:

1. The economic balance sheet (or operational balance sheet).
2. The enterprise value using the efficient markets assumption.
3. The weighted average cost of capital (WACC). Explicitly write down your assumptions.
4. Use the WACC to calculate the value per share using the simplified valuation approach.
5. Use the WACC to calculate the value per share using the “full pro forma analysis” approach.

Note:

Throughout the Assignment, please list your assumptions for the relevant parts, such as choosing the risk-free interest rate or a certain method to get the average number.

Assessment Requirements Summary

The assignment focuses on applying financial valuation techniques to Nick Scali Limited (NCK) using five years of historical financial data. The key requirements include:

1. Economic Balance Sheet (Operational Balance Sheet)

   • Construct an economic balance sheet to understand the company’s real asset and liability structure.

   • Adjust for off-balance-sheet items or operational factors.

2. Enterprise Value Calculation

   • Calculate NCK’s enterprise value using the efficient markets assumption.

   • Consider market capitalization, debt, and cash adjustments.

3. Weighted Average Cost of Capital (WACC)

   • Determine the WACC for NCK.

   • Explicitly list assumptions, including the risk-free rate, equity beta, market risk premium, and cost of debt.

4. Simplified Valuation Approach

   • Use WACC to estimate the value per share through a simplified approach.

5. Full Pro Forma Analysis Approach

   • Use WACC and detailed pro forma financial statements to calculate the value per share using a comprehensive valuation method.

Important Notes:

• All assumptions should be clearly documented.

• The entire solution must be submitted as one Excel file with spreadsheets, in-cell explanations, and notes where necessary.

• Each component carries equal marks (4 marks each).

Approach Taken by Academic Mentor

The Academic Mentor guided the student through the assignment in a step-by-step manner:

1. Understanding the Assignment Scope

   • Reviewed the assignment brief and clarified the deliverables.

   • Explained the difference between simplified and full pro forma valuation approaches.

2. Data Organization

   • Compiled five years of financial statements for NCK into the Excel file.

   • Ensured data integrity and proper formatting for calculation and analysis.

3. Economic Balance Sheet Construction

   • Guided the student to adjust the book values to reflect operational reality.

   • Included items like off-balance-sheet assets, operational liabilities, and non-operating adjustments.

4. Enterprise Value Calculation

   • Explained the components: Market Value of Equity + Debt – Cash.

   • Illustrated how to apply the efficient market assumption using market prices.

5. WACC Calculation

   • Demonstrated the WACC formula:

     WACC=EV⋅Re+DV⋅Rd⋅(1−T)WACC = \frac{E}{V} \cdot Re + \frac{D}{V} \cdot Rd \cdot (1-T)WACC=VE⋅Re+VD⋅Rd⋅(1−T)

     • Defined E as market value of equity, D as debt, V as total capital, Re as cost of equity, Rd as cost of debt, T as corporate tax rate.

   • Supported the student to select assumptions: risk-free rate, beta, market risk premium, and debt costs.

6. Simplified Valuation Approach

   • Applied WACC to calculate a high-level value per share using free cash flow projections and discounting.

   • Emphasized clarity in documenting assumptions.

7. Full Pro Forma Analysis

   • Built detailed pro forma financial statements: income statement, balance sheet, and cash flow projections.

   • Discounted projected free cash flows using WACC to arrive at a refined value per share.

8. Documentation and Notes

   • Taught the student to provide in-cell notes and explanations for each assumption and calculation step.

   • Ensured all references to data sources and assumptions were clearly stated.

Final Outcome

• A single Excel file containing all spreadsheets and in-cell explanations.

• Completed calculations for economic balance sheet, enterprise value, WACC, simplified valuation, and full pro forma valuation.

• Clear documentation of assumptions such as risk-free rate, beta, market premium, and averaging methods.

• Learning objectives covered:

  1. Understanding operational and economic adjustments in balance sheets.

  2. Applying WACC and discounted cash flow analysis to real-world companies.

  3. Valuing a company using both simplified and detailed pro forma approaches.

  4. Developing analytical and Excel-based financial modeling skills.

  5. Effectively documenting assumptions, methodology, and rationale for decisions.

Unlock Your Academic Success with Expert Guidance

Accessing high-quality assignment samples can help you understand concepts, structure your work, and enhance your learning. Download our sample solution to see a step-by-step approach to completing your assignment. Important: This sample is for reference only submitting it as your own work is considered plagiarism and may have serious academic consequences.

If you want a fully plagiarism-free, custom-written assignment, our team of professional academic writers can create a fresh solution tailored to your specific requirements. Ordering a custom assignment ensures:

• Original content written uniquely for you

• High-quality research with credible sources

• Clear formatting and professional presentation

• On-time delivery, ready for submission

Take control of your grades today and learn effectively while staying safe from plagiarism.

[Download Sample Solution]  [Order Fresh Assignment]