BUS329 Single Index Model Sample: Bank Beta and Risk Split Singapore

For information and reference only – not to be submitted as your own work.

This BUS329 single index model sample shows how one group member might write the individual regression and risk section of a Murdoch University Investment Analysis assignment. Brief: BUS329 group assignment, TS 2026, single index model. Guide: single index model regression in Excel.

Everything below uses a hypothetical firm, “Bank A”, an Australian-listed bank measured against a broad Australian share-market proxy such as the S&P/ASX 200. Every number is illustrative, generated only to show the method, and none of it is a real company’s result. Your own section must use your assigned firm’s actual prices and your own events.

1. Data and return construction

The extract assumes 61 month-end adjusted closing prices for Bank A and 61 month-end values of the market index, which give 60 monthly returns (n = 60) covering five years. Adjusted prices matter because they fold dividends and splits into the series. Raw closing prices would show an artificial fall on every ex-dividend date, and banks usually pay sizeable dividends.

Simple returns are calculated as R_t = (P_t − P_t−1) / P_t−1. Log returns, ln(P_t / P_t−1), are close for small moves, but the single index model is usually set up in simple returns because portfolio returns aggregate linearly across stocks. Pick one convention and state it in the methodology. Mixing the two inside one regression is a common reason for a table that will not reconcile.

If your course provides a risk-free series, you can regress excess returns instead. This extract uses raw returns, as the brief’s equation does, and says so in a note under the table.

2. Excel steps

1. Put dates in column A, Bank A prices in B and index values in C. Sort oldest to newest and check both series share exactly the same dates.
2. In D2 enter =B3/B2-1 and fill down; do the same in E2 for the index (=C3/C2-1). Format as percentages and multiply by 100 if you prefer working in percentage points, as this extract does.
3. Run the regression with the Data Analysis ToolPak (Data, Data Analysis, Regression), with Y range = Bank A returns and X range = market returns. If the menu is missing, load the Analysis ToolPak add-in first.
4. Cross-check with worksheet functions: =INTERCEPT(Y,X) for alpha, =SLOPE(Y,X) for beta, =RSQ(Y,X) for R², =STEYX(Y,X) for the standard error of the regression, and =VAR.S() for each variance. LINEST with the stats argument set to TRUE returns the coefficients, standard errors, R² and degrees of freedom in one block.
5. Keep every step on a labelled sheet (prices, returns, regression, decomposition) because the brief asks for the Excel file.

3. BUS329 single index model sample: regression output

The model estimated is R_i,t = α_i + β_iR_m,t + e_i,t. Returns are in percentage points per month.

Illustrative data for hypothetical Bank A. Two-tailed p-values use the t distribution with 58 degrees of freedom; the 5% critical value is about 2.00.

4. Risk decomposition

The model splits total variance into σ²_i = β²_iσ²_m + σ²(e_i). Sample variances (VAR.S) of the two return series are 22.3948 for Bank A and 11.0592 for the market, so the standard deviations are 4.73% and 3.33% per month.

The verification the brief asks for is simple: systematic variance divided by total variance is 12.4660 / 22.3948 = 0.5566, which equals the regression R² to four decimal places. One detail trips many groups. The residual variance in the table is the sample variance of the residuals, the sum of squared residuals divided by n − 1 (585.80 / 59 = 9.9288). It is not STEYX squared, which divides by n − 2 and gives 10.1000. STEYX is the right number for standard errors and t-tests, but only the n − 1 version makes the identity close exactly. State which one you used.

5. Interpreting alpha, beta and R²

Beta. At 1.06 with a standard error of 0.12, Bank A’s beta is slightly above one. A 95% interval of roughly 0.81 to 1.31 includes 1, so the honest label is “close to neutral, marginally aggressive”, not “aggressive”. Banks are cyclical because credit losses and lending volumes follow the economy, which is consistent with a beta around or above one, but the sector argument supports the estimate and does not replace it.

Alpha. The estimated alpha is +0.47 percentage points a month, which sounds large until you see t = 1.10 and p = 0.275. The test cannot reject α = 0, so the intercept is statistically indistinguishable from zero over this window. Write that sentence plainly. Claiming that the stock “beat the market by 5.6% a year” from an insignificant intercept is the most frequent over-reading in this assignment.

R². Market movements explain 55.7% of the variation in Bank A’s returns, and the other 44.3% is firm-specific. A beta of 8.5 standard errors from zero confirms the relationship is real, while the R² tells you how much of the return is tied to it.

6. Residual spikes and the events to look for

Sort the residuals (actual return minus fitted return) and flag the largest. In this illustrative run the three biggest fall in months 32 (+6.56), 35 (−6.55) and 38 (−7.41) percentage points, equal to roughly 2.1, 2.1 and 2.3 times the regression’s standard error. For your real firm, search the announcements and press for each flagged month. The table lists the kinds of events worth checking, not dated news.

Use the company’s own ASX announcements and recognised financial press, and cite each source. If no company-specific news explains a spike, say so rather than forcing a story: it may simply reflect an index composition effect or sector-wide news.

7. Model discussion (about 300 words)

Bank A’s market model fits moderately well. With an R² of 0.557, just over half of the stock’s monthly return variance is explained by the broad market, so movements in the index are the main but not the only driver of its risk. The estimated beta of 1.06 (standard error 0.12) is statistically different from zero but not from one at the 5% level, which places the bank in the neutral-to-slightly-aggressive range rather than clearly defensive or aggressive. That is plausible for a large bank: earnings depend on credit conditions, interest margins and housing activity, all of which move with the economic cycle, so a beta near the market’s is unsurprising. The sector’s cyclicality supports the estimate, but the regression is the evidence.

The intercept is positive at 0.47% per month, yet its t-statistic of 1.10 and p-value of 0.275 mean we cannot conclude that Bank A generated returns beyond what its market exposure implies. Any apparent outperformance over the sample is consistent with chance. Alpha estimates from 60 observations are also noisy, so even a larger t-statistic would call for caution.

The residual variance of 9.93 accounts for 44.3% of total variance, and this is the part of risk that is specific to the bank. The largest residuals coincide with the types of events that move a single bank’s share price but not the whole market, namely earnings surprises, regulatory action and management change. Limitations include the use of a single factor, a five-year window in which a single episode may dominate, and the assumption that beta is stable through time. A multi-factor model or rolling-window beta could test those assumptions.

8. Diversification note

Because the residual terms of different firms are assumed uncorrelated in the single index model, the firm-specific part of portfolio variance shrinks as more stocks are added, while the β²σ²_m part does not. Markowitz (1952) set out the logic of reducing risk through combinations of imperfectly correlated assets, and Sharpe (1963) simplified the estimation by relating every security to a common index. For Bank A, an investor holding a spread of banks, resources, retailers and health companies would see most of the 44.3% idiosyncratic share averaged away, but would still carry market risk. Holding several banks removes little, because they share sector exposure that the single index does not capture.

9. Drawing the scatter plot and characteristic line

Select the market-return column and the Bank A column, insert a scatter chart with market return on the horizontal axis (x) and the stock return on the vertical axis (y), then add a linear trendline and tick “Display equation” and “Display R-squared”. The line is the characteristic line: its slope is β and its intercept is α. Label both axes with units (% per month) and add a caption stating the sample period and the number of observations. The vertical gap between any point and the line is that month’s residual, which is a handy way to point out the spikes from section 6.

If your own firm’s numbers will not reconcile, or you want the workbook structure checked before submission, our investing and portfolio management support and finance assignment help pages explain how a finance specialist can review your work. For a similar Murdoch finance extract, see the BUS286 corporate finance group assignment sample.

About the expert

This extract was prepared with guidance from Mei Lin, who holds a Master’s in Finance from Singapore Management University. She specialises in financial analysis, investment management, risk management, financial accounting and corporate finance, and she guides students through complex financial calculations. She has over 7 years in the finance industry as a financial analyst and consultant and works as a part-time academic writer at Singapore Assignment Help.

References

Bodie, Z., Kane, A., & Marcus, A. J. (2024). Investments (13th ed.). McGraw Hill.

Markowitz, H. (1952). Portfolio selection. The Journal of Finance, 7(1), 77–91. https://doi.org/10.1111/j.1540-6261.1952.tb01525.x

Microsoft. (n.d.). LINEST function. Microsoft Support. https://support.microsoft.com/office/linest-function-84d7d0d9-6e50-4101-977a-fa7abf772b6d

Microsoft. (n.d.). Load the Analysis ToolPak in Excel. Microsoft Support. https://support.microsoft.com/en-us/office/load-the-analysis-toolpak-in-excel-6a63e598-cd6d-42e3-9317-6b40ba1a66b4

Microsoft. (n.d.). RSQ function. Microsoft Support. https://support.microsoft.com/office/rsq-function-d7161715-250d-4a01-b80d-a8364f2be08f

Sharpe, W. F. (1963). A simplified model for portfolio analysis. Management Science, 9(2), 277–293. https://doi.org/10.1287/mnsc.9.2.277