This portfolio is a structured investment analysis model developed in Microsoft Excel, designed to evaluate and monitor a diversified portfolio consisting of ETFs, Unit Trusts and Private Equity (DCF – Creamy Delight) under a simulated firm, Summit Capital.
The model is built using financial modelling best practices from a Cover, through to inputs, calculations and lastly outputs. The model incorporates key portfolio construction principles, including asset allocation, diversification, and performance tracking. It further integrates South African tax considerations, specifically corporate income tax and capital gains tax (CGT), allowing for a more realistic assessment of after-tax returns.
Financial analysis techniques such as scenario analysis are applied to evaluate how changes in returns, allocation weights, and tax impacts affect overall portfolio performance. The outputs sheet, includes a range of visualizations, such as pie charts, column charts and line charts.
Notes have been added to help guide the reader.
From a technical perspective, the model leverages a combination of Excel functions and tools, including:
- =CHOOSE
- =SUMIF, =SUMPRODUCT
- Conditional formatting
- Combo Box
- =AVERAGE, =MEDIAN, =MAX, =MIN
- =XNPV
- =YEARFRAC
- What If Analysis – Data Table