Ghaf Logistics equity research

The assignment

You are an analyst on a research team writing an initiation note on Ghaf Logistics Co., a fictional listed freight and warehousing company. Its share prices, the market index (the fictional market index, the same index used for Bustan Foods Co. in the lessons), its segments, forecasts and peers are all invented for teaching. This is an educational exercise: the rating rule below is a teaching device, not investment advice.

The team wants four things in one linked model: how the shares have behaved (return, volatility, drawdown, beta), what the business is expected to earn (a segment-driven EPS forecast), what the shares are worth against peers (forward P/E and PEG), and whether the return implied by today's price clears the return the market requires for the shares' risk (CAPM).

The Data tab holds 37 month-end observations (month 0 … 36) of Ghaf's share price, its dividends (paid in the month the share goes ex-dividend) and the market index. The Inputs tab holds FY2025 actuals, FY2026E–FY2028E segment assumptions and the market and rating inputs. The five peers on Valuation are fictional.

Deliverables

Complete every white cell with a formula linked to the given data, Inputs or other tabs — no typed numbers. The model will be re-tested with different data and assumptions.

Conventions (follow exactly)

• Monthly total return (Data column E) = (price + dividend paid that month) ÷ previous month's price − 1; growth factor = 1 + return. Market return = index ÷ previous index − 1 (a price index, no dividends). Returns start in month 1.
• Arithmetic mean = AVERAGE of the 36 monthly returns. Geometric mean monthly return: for Ghaf, PRODUCT of the growth factors ^ (1 ÷ number of returns) − 1; for the market, (last index ÷ first index) ^ (1 ÷ number of returns) − 1.
• Annualised return compounds the geometric mean: (1 + geometric mean)^12 − 1. Never multiply a monthly return by 12.
• Volatility uses the sample standard deviation (STDEV.S) of monthly returns; annualise × √12.
• Drawdown is measured on price (column B, dividends excluded): running peak = highest price so far (month 0's peak is its price); drawdown = price ÷ running peak − 1; the maximum drawdown is the most negative value.
• Beta is built from deviation columns: deviation = return − its arithmetic mean (Returns & risk row 2). Covariance = sum of (Ghaf deviation × market deviation) ÷ (n − 1), using SUMPRODUCT or helper cells; market variance = VAR.S; beta = covariance ÷ market variance. Correlation = covariance ÷ (Ghaf monthly volatility × market monthly volatility). There is no COVARIANCE.S, SLOPE or CORREL function.
• CAPM required return = risk-free rate + beta × equity risk premium (annual rates on Inputs). Sharpe ratio = (annualised return − risk-free rate) ÷ annualised volatility, for Ghaf and for the market.
• Earnings: FY2025A drivers come from Inputs; each forecast volume, price and central-cost line = prior year × (1 + growth). Revenue = volume × price (SAR m); segment EBIT = revenue × that year's margin; EBIT = freight EBIT + warehousing EBIT − central costs; profit before tax = EBIT − net interest; tax = PBT × tax rate; EPS = net income ÷ diluted shares. EPS CAGR = (FY2028E EPS ÷ FY2025A EPS)^(1/3) − 1.
• Valuation: forward P/E = price ÷ forward (FY2026E) EPS. PEG = forward P/E ÷ (EPS growth × 100), using each peer's 3-year growth and Ghaf's EPS CAGR. Use the peer median (MEDIAN). Value per share from P/E = median forward P/E × Ghaf's forward EPS; from PEG = median PEG × Ghaf's growth × 100 × forward EPS. Target price = weight × P/E value + (1 − weight) × PEG value, with the weight on Inputs.
• Expected 12-month return = (target price + expected dividend) ÷ share price − 1, using the month-36 price.
• Rating rule (educational): =IF(expected >= required, "Above required return", "Below required return") — type the two labels exactly.
• No rounding. Results are graded to within 0.05%.

What the team will discuss

Be ready to explain why the compound and arithmetic returns differ, why Ghaf's drawdown is deeper than its worst month, why Ghaf's beta is far below the ratio of its volatility to the market's (and what that says about diversifiable risk), how much of the target price rests on the growth forecast (the PEG value), and why a strong past return says little about the expected return from today's price.