S. P. Jain School of Global ManagementSydney Campus Investment Analysis

Investment Analysis 

Learning Outcomes Assessed

B. Compare high-risk investment options and solve financial challenges in a dynamic market.
C. Explore market trends and formulate strategies to optimise portfolio performance.
D. Utilise investment management models to accurately value various asset classes and portfolios.

This assessment requires the use and application of advanced Microsoft Excel features to support and demonstrate your research findings. Although the assessment is individual, group activity is allowed for data collection.

Due Date: Week 8, Sunday, 26 October, 11:59 PM Sydney (AEST)
Marking Guide: Included in the assessment document.

Student Declaration

I certify that this is my own work, and I have read and understood the Misconduct or Breach of Assessment Rules of the school.

Student ID Student Name Student Signature Date

Assessment Details

Assessment Theme:
Financial performance analysis, security analysis, and portfolio management of ASX-listed companies.

Project Tasks

(a) Portfolio Formation and Analysis

Step 1 – Stock Selection and Data Collection

• Select a minimum of 10 listed stocks from different industries on the ASX.

• Download daily historical closing prices for the sample period:
  January 2022 – September 2025

• Download the daily historical S&P ASX 200 Index for the same period.

Step 2 – Return Calculation

• Prepare an Excel database of daily historical returns using the formula:
  LN(P1/P0)

• Use Excel’s LN function.

Step 3 – Statistical Analysis

• Compute:

  • mean returns

  • standard deviations

  • beta values

• Compare and interpret the results.

Step 4 – CAPM Calculation

• Apply the Capital Asset Pricing Model (CAPM) to calculate intrinsic values.

• Compare intrinsic values with current market prices to determine whether each stock is:

  • undervalued

  • overvalued

  • fairly valued

• Use the Australian risk-free rate (10-year bond yield):
  4.36% as of September 2025

Step 5 – Portfolio Construction

• Select two stocks from your sample to form a portfolio.

• Compute:

  • portfolio expected returns

  • portfolio standard deviation

• Interpret results.

• (Optional): Form a portfolio of all sampled stocks using MMULT for matrix variance-covariance calculations.

Application of One Valuation Model

Select one company from your ASX sample and apply one of the following models:

• Dividend Discount Model (DDM)

• Cash Flow Model

• Residual Income Model

• Market-Based Models:

  • Price-to-Earnings (P/E) Ratio

  • EV/EBITDA Ratio

Interpret the fair value results and compare them with ASX trading price.

Professional Report Writing

Prepare a professional report (maximum 2500 words) outlining all findings and recommendations.

General Instructions

• Use Times New Roman, Font Size 12, 1.5 line spacing, and adequate margins.

• Word limit: 2500 words (exceeding limits incurs a 10% penalty).

• Submit on time through Blackboard (late submissions incur 10% per working day).

• In case of medical emergencies, notify the lecturer and include a valid medical certificate.

• Regular class attendance and tracking assignment deadlines are the student’s responsibility.

• Use APA referencing throughout.

• Check plagiarism via Turnitin; high similarity may result in penalties or rejection.

• Marks will reflect:

  • content relevance

  • appropriate use of theory

  • research depth and evidence

  • reasoning and analysis

• Follow instructions carefully to avoid penalties or non-marking.

Blackboard Submission Requirements

You must submit:
The written report (Word file)
The Excel spreadsheet containing all calculations

Report Structure 

1. Cover Page

• Title of the Report

• Student Name and ID

2. Table of Contents

3. Introduction / Background 

4. Body of the Essay

4.1 Portfolio Results and Discussion 

Analysis of mean returns, standard deviation, beta, CAPM valuation, and portfolio performance.

4.2 Valuation Results and Discussion 

Fair value estimation using one selected valuation model.

4.3 Recommendations 

Investment insights for the overall portfolio and individual stock assessment.

Brief summary of assessment requirements

• Core tasks:

  1. Select ≥10 ASX stocks across industries + S&P/ASX 200 index; download daily closing prices (Jan 2022 – Sept 2025).

  2. Compute daily log returns using in Excel.

  3. Calculate mean returns, standard deviations, betas; interpret results.

  4. Apply CAPM to compute intrinsic values and compare with market prices (use Australian 10-year bond yield — 4.36% as of Sept 2025).

  5. Form a two-stock portfolio (or full sample with MMULT optionally), compute portfolio expected return and standard deviation; interpret.

  6. Valuation (one model) for one company — choose from DDM, Cash-flow, Residual Income, or market multiples (P/E, EV/EBITDA).

  7. Produce a professional report (≤2500 words) and submit the Word report + Excel workbook on Blackboard.

• Formatting & rules: Times New Roman 12, 1.5 spacing, APA referencing, Turnitin check; penalties for lateness/word-limit/plagiarism.

How the Academic Mentor guided the student

1. Clarify objectives & plan work (scoping)

• Mentor reviewed learning outcomes (B, C, D) with the student to align project goals: compare high-risk options, explore trends/strategies, and apply valuation models.

• Built a project timeline covering data collection, Excel modelling, valuation, writing, and review to meet the Week-8 deadline.

2. Stock selection & data collection (Step 1)

• Mentor advised diversification: choose ≥10 ASX stocks across sectors (e.g., financials, materials, healthcare, energy, tech).

• Demonstrated reliable data sources (e.g., ASX, Yahoo Finance, Bloomberg) and showed how to download daily closing prices for Jan-2022 to Sept-2025.

• Student stored raw prices in a clean Excel sheet with consistent date indexing and a separate sheet for ASX 200 index.

3. Return calculation & Excel setup (Step 2)

• Mentor taught the correct formula for log returns: in Excel , and how to fill down and handle missing dates.

• Set up named ranges, date alignment across instruments, and taught version control (master workbook + backup).

4. Statistical analysis: means, sd, betas (Step 3)

• Mentor explained statistical concepts: arithmetic vs log returns, sample mean, sample standard deviation, and interpretation in context.

• Showed Excel functions: (for beta via regressing stock returns on ASX200 returns), and for regression diagnostics.

• Interpreting results: assessed volatility (sd), risk (beta >1 implies higher systematic risk), and return trade-offs.

5. CAPM and intrinsic value calculation (Step 4)

• Mentor walked through CAPM: , using Rf = 4.36% (10-yr bond yield Sept 2025).

• Converted expected return into a discount rate to derive intrinsic values (e.g., via dividend/cashflow discounting or one-period/ Gordon approximations depending on data availability).

• Compared intrinsic values vs current ASX prices to classify each stock as undervalued / overvalued / fairly valued and recorded rationale.

6. Portfolio construction & risk aggregation (Step 5)

• For the two-stock portfolio: mentor coached on choosing complementary stocks (low correlation) and computing portfolio return .

• Showed portfolio variance formula and Excel implementation: variances, covariance , and portfolio σ = sqrt(…); explained interpretation (diversification benefits).

• Optional: introduced, matrix operations and covariance matrix for full-sample portfolios if the student wanted to extend analysis.

7. Valuation model application (Part b)

• Mentor helped select the most data-appropriate valuation model (e.g., DCF if free cash flows available; P/E multiple if limited cash flow disclosure).

• Demonstrated model mechanics in Excel — forecast assumptions, discount factors, terminal value, sensitivity analysis (best/central/worst cases).

• Interpreted fair value vs market price and presented a clear investment recommendation backed by quantitative evidence.

8. Report drafting & academic compliance (Part c)

• Mentor provided the report structure (Intro 150 words; Portfolio 1000; Valuation 1000; Recommendation 200; Conclusion 150).

• Emphasised clarity: charts (returns histogram, cumulative returns, risk/return scatter), tables (summary statistics, CAPM outputs), and appendix with Excel snapshots.

• Reviewed referencing (APA), Turnitin similarity checks, and proofreading to ensure academic English and compliance with submission rules.

9. Review, feedback & finalisation

• Mentor reviewed draft, suggested tightening language and strengthening interpretations (linking statistics to practical investment implications).

• Encouraged inclusion of limitations (data period, model assumptions) and recommended risk disclosures.

• Final deliverables: polished Word report (≤2500 words) + fully functional Excel workbook with formulae, named ranges, and a README tab documenting steps and assumptions.

Outcome achieved & learning objectives covered

• Outcome: A professional report + Excel workbook delivering: computed log returns, summary statistics (mean, sd, beta), CAPM intrinsic values, two-stock portfolio analysis, a full company valuation (chosen model), clear investment recommendations and sensitivity checks. Visuals and appendices documented all steps for reproducibility.

• Learning outcomes satisfied:

  • B: Student compared high-risk investments (volatility, beta) and addressed market challenges through diversification and valuation evidence.

  • C: Student analysed market trends (Jan-2022 to Sept-2025), used results to form portfolio strategies and recommended optimisation actions.

  • D: Student applied investment models (CAPM and one valuation model) to value assets and assess relative pricing accuracy.

Get High-Quality Academic Guidance Use Samples Wisely & Score Better

Looking to understand how to structure and present your assignment effectively? You can download the sample solution provided on this page to review the correct formatting, research depth, and analytical approach expected in university assessments. However, please remember that this sample is strictly for reference purposes only. Submitting it as your own may lead to plagiarism penalties, academic misconduct issues, or assignment rejection.

If you want a fully original, high-quality submission, you can also order a fresh, plagiarism-free assignment crafted exclusively for you by our team of expert academic writers. Every custom solution is written from scratch, tailored to your guidelines, and checked through trusted plagiarism tools before delivery.

Why Order a Fresh, Custom-Written Assignment?

• 100% original and plagiarism-free content

• Written by qualified subject experts

• Structured as per your university’s marking rubric

• Delivered on time with guaranteed quality

• Covers research, analysis, calculations, citations, formatting, and editing

• Complete confidentiality and student-friendly pricing

Whether you're looking for guidance or a personalized solution, we are here to support your academic success with reliable, trustworthy services.

Download Sample Solution Order Fresh Assignment