BRIGHT SARFO

• Email
• LinkedIn
• GitHub
• Tableau
• YouTube

© 2025. All rights reserved.

---

Economic Modeling And Business Analyst
| SQL | Python | Power BI | Tableau | GAMS

• Home
• Projects
• About Me
• Contacts

• (View Resume)

• Arrow Down

Hello, welcome to my project portfolio

I’m a data analyst passionate about solving real-world problems through analytics, automation, and evidence-based insights. From building predictive models to simplifying messy datasets, my work focuses on delivering clarity and value.My experience spans academic research and real-world business scenarios, using tools like SQL, Python, Power BI, and Tableau. I’m driven by curiosity, structure, and the desire to help teams make better decisions.Passionate about leveraging my experience to serve a greater purpose, I am driven to collaborate with individuals who share my dedication.My ultimate goal is to deliver insights and solutions that drive my employer’s financial and commercial objectives while ensuring the needs of customers and stakeholders remain central to every decision.

• Arrow Down

Skills

Excel | Power BI | Tableau | Python | SQL | GAMS Modeling

Welcome! Click Arrow to View My Projects

• Arrow Down

• Home
• Projects
• About Me
• Contacts

Featured Projects

BANK LOAN PERFOEMANCE ANALYSIS
An Excel-Based Analytical Dashboard for Monitoring Loan Quality, Risk Exposure, and Portfolio Health

Microsoft Excel (Advanced) | Pivot Tables | Financial KPIs | Dashboard Design

• This Project evaluates loan portfolio health by distinguishing good and bad loans, tracking repayments and identifying credit risk patterns.

• Expect executive-level KPIs, loan quality segmentation, geographic risk analysis, and month-on-month performance insights.

• View Project

INVESTIGATING THE EFFECT OF THE UK EMISSIONS TRADING SCHEME (UK ETS) ON INVESTMENT DECISIONS AND TIMING OF DECOMMISSIONING OF ENERGY PROJECTS IN THE UKcs

Summary of Project

• This is my MSc Energy Economics and Finance project.

• This project was done in collaboration with the Offshore Energies UK (OEUK).

• Given that drive to net zero should be a global fight, this project was to assess the impact of the UK ETS on decision to invest in the UKCS, including its potential influence on when existing companies decide to cease operations.

• The results underscore the critical role that oil prices play in the decarbonisation drive.

Excel | SQL | Power BI

• Loading...

Summary of Methodology Used

• To achieve this objective, I conducted a capital budgeting appraisal of three (3) representative fields of different sizes in the UKCS using Advanced Excel (Financial) Modeling.

• I used the Net Present Value (NPV) and Cessation of Production (CoP) to represent profitability and timing of decommissioning respectively.

• A Monte Carlo Analysis was used to assess the risks and uncertainties resulting from variations in carbon price and oil price on NPV and CoP.

Some Key Findings

• The deterministic analysis showed that Carbon Prices resulting from the UK ETS has a negative effect on profitability of investments in these fields and tend to shorten their lifespan.

• The impact is very insignificant for large fields but has pronounced effect on small fields.

• It was established that Oil price volatility exerts a significantly stronger influence on field profitability and decommissioning timelines than carbon pricing; even substantial increases in carbon prices have minimal impact unless accompanied by a sharp decline in oil prices.

INTERACTIVE COMPANY SALES DASHBOARD - HOSTED ON POWER BI SERVICE

Summary of Project

• Interactive sales dashboard for a pizza company to analyze sales trends, customer behavior, and performance KPIs.

• The dashboard helps understand what drives sales; from busiest days to popular pizza types.

• Designed for deep-dive exploration.

Why is this project necessary?

This project demonstrates how data-driven insights can help businesses make informed decisions. Though focused on the food sector, the skills are highly transferable across industries.

• Sneak peak

pOWERED BY Power BI, SQL & DAX
📅 JAN/15 – DEC/15
⚙️ Work in Progress

✅ Hosted on BI service: Clickable drill-down available for daily orders to view pizza category breakdown!

Key Metrics (KPIs)

• Average Pizzas per Order: 2.32

• Average Order Value: $38.31

• Total Pizzas Sold: 49,574

• Total Orders: 21,350

• Total Revenue: $817.86K

Value to company/investors?

• Help investors to assess the main risks to their returns.

• Help assess the effect of the recent increases in electricity prices and inflation on profits whilst creating value to customers.

• What options might the developer/investor consider to increase returns in an uncertain world?

Capital Budgeting Appraisal Of a Wind Farm Prospect Using Advanced Excel (Financial) Modelling

• Built a spreadsheet model to obtain the NPV and other useful output values (metrics) of a country's first offshore wind farm prospect solely financed by joint venture (100% equity financing).

• Calculated the Levelised Cost of Electricity (LCOE) and compared it to the government's Contract for Difference (CfD) to analyse the implications for the project.

• Calculated the break-even electricity price and constructed a Tornado chart for key input variables.

• Examined how the model and results will change if the project is financed by both equity and debt (Change in capital structure).

• Used Storytelling to Create Written Report Highlighting Findings with Visual Presentation from My Analysis Results.

Excel | Capital Budgetting Appraisal

• Loading...

Renewable Energy Adoption in the UK - A regional Analysis Using SQL, Excel and Power BI.

Watch this space for my SQL project. (Project done. Loading soon...)
This project analyses data from gov.uk on energy consumption in the UK to make recommendations on renewable energy adoption, both national and regional level.

Excel | SQL | Power BI

• Loading...

• Arrow Down

Professional Certifications

My growing list of proprietary, exam- and case study-based certifications

Excel Modeling specialist | 2023

Joint Venture Contracts | 2024

Oil and Gas Transport & Sales Contracts | 2024

• Arrow Up
• Arrow Down

• Back

FEASIBILITY REPORT OF OIL AND GAS PROSPECT FOR NEWFOUNDLAND

PROBLEM: THE BILLION DOLLAR GAMBLEHow many times have you heard the saying: "The best way to predict the future is to create it"? Yes, these words became reality for "Transition Energy Limited", an ambitious oil and gas company.Climate Change has been declared a world emergency by the United Nations, calling for an urgent need to halt the production of oil and gas by all nations to achieve net zero by 2050. The demand for oil and gas has been projected to fall in the coming years.Imagine buying a BlackBerry phone in the mid-2000s when it was really in vogue with the hope that you could sell it for a higher price later. Fast forward to 2025, and you realise that even being able to sell your BlackBerry at a loss is practically impossible. This is how the picture of the oil and gas sector's outlook appears to investors.The stakes are sky-high in the energy industry because it is characterised by uncertainty caused by volatility in oil and gas prices. These are mostly caused by external factors that are out of the control of the investor company. As a result, every investment decision made can have a huge impact on the future of the company and even nations.Here is Transition Energy company Limited, faced with a decision that could define its legacy in a developing country that has discovered its first oil: should they invest in this new oil and gas prospect in the turbulent waters of the NewFoundLand Continental Shelf (NCF)?The answer wasn't straigtforward. With the world demanding cleaner energy, the sector reeling from unpredictable price swings, and operational costs going through the roof, a single wrong move could cost them billions. They needed more than a consultant-they needed a partner who could help them navigate this storm of uncertainties.That's where I stepped in.

Background/Setting The Scene
The NewFoundLand Continental Shelf is a treasure trove of resources, but it's also fraught with risks. Oil and gas prices, like the ocean tides, are volatile and unpredictable. This inherent volatility, which has characterised the sector since the dawn of the petroleum age, has been further amplified by the seemingly endless geopolitical tensions and global pursuit of net-zero emissions through the transition to cleaner energy sources. Moreover, the continuous increasing decommissioning costs loom large like icebergs at the end of every project. Transition Energy Ltd. therefore had one burning question: "Is this prospect worth the gamble?"They needed to know if the venture would generate positive cash flows despite these uncertainties. They wanted a comprehensive, data-driven answer—one that could stand the test of scrutiny from shareholders, board members, and stakeholders alike. They sought a strategy that would inspire confidence, grounded in solid science and financial rigour.

Data Collection And CleaningStakeholder Engagement
I started off with a broad stakeholder engagement by meeting the senior leadership and all key management members from cross-functional teams who are involved in the project, directly and indirectly, particularly the engineering and finance teams.This integrated team approach ensures that all critical elements—from market dynamics and cost variability to regulatory compliance and technical feasibility—are addressed right from the outset, setting a strong foundation for robust decision-making throughout the project lifescycle.After a series of meetings, we agreed on the information shown in Tables 1, 2, and 3 below with the utmost help of the engineering team.

Breaking Down The Data Collected
This is where I set the ball rolling. In this section, I will break down the figures in the tables above so we can be on the same page.The field is expected to have a lifespan of 20 years of production. The engineers gave me estimates of the production profile for the years in Table 1.As a practice in financial modelling for capital budgeting decisions, we assume that production starts in year 1. Therefore, for the computation, consider that capital expenditure is incurred in Year 0 (the current year or the year before production begins), while decommissioning expenditure is incurred in Year 21 only (the year after production ceases).The company is ready to start production immediately if the project proves to be economically viable. Thus, we assumed with certainty that a Capital Expenditure (CAPEX) of $1 billion would be expended to prepare the grounds for actual production to begin—the CAPEX is not expected to change based on our estimates.Investors measure the riskiness of their projects against alternatives by using the discount rate—simply the interest rate discounted over the project's life. This is because it is considered as a measure of the return on 'average' alternative project(s) that investors would have to give up to invest in the current project. To overcome uncertainties, it is prudent to use a relatively higher but realistic interest rate rather than a lower rate. This helps to account for the opportunity cost, risk, and all unforeseen events that could affect profitability.A project with a higher discount rate means higher risks for investors' capital and therefore becomes desirable if it returns a higher value. However, the interest rate chosen must not be too high because an unrealistically higher interest rate can make investors abandon a project that would otherwise be economically feasible because it would return a lower value (NPV).In line with Transition Energy's risk tolerance level for their capital, the real interest rate (adjusted for inflation) in this developing country is assumed to be 30% over the years.Due to the volatile nature of oil and gas, we assumed the prices would exhibit stochastic distribution (i.e., will change over time) with the most likely price, minimum, and maximum prices (as indicated in Table 2). I'd like you to look at the charts below to give you a fair idea of some of the factors that influence our price estimates.

Note that One thousand cubic feet (Mcf) of natural gas equals approximately 1.038 MMBtu. For more information on conversions, click here.Likewise, based on historical data in similar environments, we assumed that the operations and decommissioning costs would also exhibit stochastic distribution as described in the table above.The Approach: Bringing Certainty to Uncertainty

As a standard appraisal unit in this region, our investment metric of interest is the Net Present Value (NPV), since it considers the time value of money. A positive NPV is desirable for the project because it means that the present value of all future cash inflows exceeds the present value of its cash outflows. Investors, seeking the highest return on their investment, typically desire to have the largest possible positive NPV without it being undermined by variables that could cause it to fall.Due to the uncertainties that characterise the sector, I proposed to adopt a sophisticated model to account for possible risk factors: Monte Carlo Simulations (this time not with Crystal Ball but exclusively with Excel).The discount rate—interest rate discounted—captures the opportunity cost of investing in the project and hence inherently contains risk of investment. However, the Monte Carlo method makes the model more robust by incorporating the volatility of the stochastic variables.Model Description
As mentioned earlier, I relied on an approach as dynamic as the industry itself: Monte Carlo Simulation (MCS). But what is it and why MCS?Imagine standing on the deck of a ship, surrounded by fog, and you are trying to find a safe harbour. Monte Carlo modelling is like having a radar system—it shows every possible route, every lurking hazard, and the safest paths forward among the various options. It gives you the opportunity to weigh in (simulate) all possible options available to you in such a period of uncertainty before taking action.

Therefore, to assess the viability of this project, I conducted a Capital Budgeting Appraisal using a detailed Excel spreadsheet with Monte Carlo Simulations (MCS) to model risks and uncertainties that may be associated with the project. This is because if uncertainties are wrongly anticipated, they can throw even a well-developed plan off course. The MCS approach duplicates multiple scenarios, thereby helping decision makers to gain insight into a range of uncertain situations that could arise from changes in the key input drivers. So in simple terms, the MCS approach makes you the "host" in the famous Monty Hall problem.

Laying The Foundation for My Dynamic Model—Putting The Pieces Together

I began my work by mapping out the critical variables: oil and gas prices, operational costs, and decommissioning expenses. With the help of an influence diagram, I carefully outlined my investment decision into a simplified map to guide my analysis.The MC model is significant because it accounts for variations in these stochastic variables over several scenarios to assess how sensitive the project is to these changes. This will help me to incorporate all possible risks and uncertainties that are likely to affect profitability.I therefore designed a model with 10,000 iterations—a virtual simulation of 10,000 different future outcomes. Each iteration told its own story: the highs of soaring oil prices, the lows of rising costs, and the stable middle ground. With the MC model, every possible outcome was on the table.

The Challenges and how I overcame them
The assumptions behind the revenue and cost components weren’t just numbers—they were stories of market demand, geopolitical tensions, and engineering challenges. Each variable was unpredictable, but through probability distributions, I could model their behaviour. Oil prices, for example, followed a triangular distribution, reflecting their historical highs and lows. Decommissioning costs were modelled with a triangular spread, showing their potential to balloon unexpectedly.Moreover, Excel is limited in the number of MCS iterations that can be done. However, given the stochastic variables, 10,000 iterations give a large enough sample size to incorporate all possible volatilities that could result from prices and costs.Another major challenge was to create a dynamic table of the variables that would reflect changes in real-life situations. I used the '=rand()' function in Excel to create random variables that would cause the stochastic variables to change in line with their probabilistic assumptions.

Analysis of the Model Process and Components
I set off to create a comprehensive Excel workbook that would include details of my approach and results in a way that both technical and non-technical stakeholders would easily understand. I created 9 sheets in my Excel workbook and colour-coded them to improve data readability and easy navigation for my readers. This is shown in the image below.

The "Cover Page" sheet contains basic introductory information about my feasibility report, such as the title, my name (Author), my client's name (Transition Energy Limited), and the date of my report. This is shown below.
PS: The date shows the day of viewing the page because I used excel dynamic date formula '=Today()'

I used the 'Background Information' sheet to give a detailed description of Energy Transition Limited and its business operations. In this section, I grouped the information under these categories:1) Purpose: Brief information about Transition Energy Limited and the objective of this report.2) Model: Description of the Excel model, how the workbook is organised, and the metrics used to achieve the objective.3) Working Sheets: A detailed description of the content of all 9 sheets and their unique colours.4) Influence Diagram: Included the influence diagram (as shown above) showing the dependencies between variables in my model.4) Navigation: I used hyperlinks to match the various sheets to their assigned colours for ease of navigation between worksheets. See snapshot below:

In my 'Inputs" sheet, I carefully organised my input variables under their names and values. To avoid the confusion with computation that could arise from hard coding, I assigned Range names to these values.As shown in the snapshot below, I used dynamic formula to reference the original input values to their respective range names in a separate column. This practice was adopted to preserve the calculations from being wrongly impacted by manual changes in the input values. Stakeholders can therefore play around with different input values without affecting the model.I also used the 'Further Information' column to provide a brief description of each input to ensure that stakeholders understand what has been presented in the workbook.

I also employed the dynamic modelling formula, using the =rand() function, to compute the distributions, as shown below. The outputs from these computations were then referenced to the 'Input Value' under the 'Variable' column. This creates a unique random variable set of values to help us in our iterations.

With my input values ready, all is set to start my appraisal using the Net Present Value Formula.Mathematically;

In my 'Oil Production' and 'Gas Production' sheets (as shown in the snapshots below), I have the data on the estimated production levels that were provided in collaboration with the team of experts (see production profile in Table 1 above).

As part of the data provided by the team of experts, I used the transpose function in Excel to copy and paste the production profiles of oil and gas data into my 'calculation' sheet to give the Total Production (TP) (shown below).
PS: Due to the random variables identified in the input section, my dynamic computation done above allows the computed values to keep changing to reflect changes in real-life situations.

Building The Dynamic Model
Under the "calculation sheet" in my Excel workbook, I used the transpose function to transfer the production profiles for 'oil' and 'gas' into my calculation sheet to avoid transmission errors that could arise from manual input. This is useful due to the different orientations of these sheets. The production levels for 'oil' and 'gas' are reproduced in rows 6 and 7, respectively.In order to compute the NPV of our project, I need the two main components: cash inflows (revenues) and cash outflows (expenditures).
The Total Revenue (TR) is the product of Total Production(TP) and the Price (P) we get from selling these outputs (i.e., TR = TP * P). This formula is used to calculate the Oil Revenue and Gas Revenue on rows 9 and 10 and their sum give the Total Revenues on row 11.By taking the Total Expenditures from the Total Revenues, the result is the Profits of the firm for all the years of production. These yearly profits represent the net cash flows for the firm in each year. To account for these net cash flows in money-of-the-day terms, they are discounted to their Present Value using the discount rate (30%) specified earlier. The result is the Net Present Value of this investment. This is how much the investment would be worth in terms of today's money.

The Monte Carlo Simulation
To account for any deviations that could result in our NPV value, a Monte Carlo Simulation (as explained earlier) is conducted. The Monte Carlo method makes the model more robust by incorporating the volatility of the stochastic variables. The key drivers of the possible risks were identified as our stochastic variables under our variable descriptions, which are the result of various factors, including but not limited to inflation and geopolitical factors.The chart below is a sampled snapshot of the simulation of the key variables. This advanced scenario analysis conducts 10,000 trials using the random variables that have been calculated. The MC model is significant because it accounts for variations in these stochastic variables over several scenarios to assess how sensitive the project could be in response to their changes. This helps to inform strategic decisions because it incorporates all possible risks and uncertainties that could likely affect profitability.PS: Excel is limited in the number of iterations that can be done and cannot handle huge amounts of data. As a practice in the energy industry, Crystal Ball software is normally used to make this analysis seamless. However, in this project, I decided to use Excel for all tasks.

Key Insights from the Simulations

Descriptive statistics and charts resulting from the model would be used to inform decision-making. A sensitivity analysis will also be carried out to understand all possible changes.Results Overview
Note that the results presented are just static representations of dynamic results in the model.

Breaking Down Statistics ResultsUnder the base case scenario (i.e., assuming all our estimated variables remain the same as estimated), the summary statistics results give the most likely outcome of Transition Energy's profitability after considering 10,000 iterations of possible variations.The results show that using a 30% discount rate over the expected life of the project, the estimated average NPV is about $342 million. Using the extreme cases of possible changes in the stochastic variables as stated above, there is about a 93% chance that the project will return a positive profit of up to $1.2 billion. This means there is only a 7% chance of the project generating negative NPV in the worst-case scenario, which is about -$220 million (loss).These results are also graphically presented in the chart, where the probability distribution of negative NPV values (only the first 4 bars on the left) are relatively insignificant compared to the positive NPVs (to the right).Given the base price and cost distributions provided from the beginning, there is 95% confidence that the NPV will fall between positive values of $337 million and $348 million.Now considering all the variables in the model, the margin of error from my computations was just 0.5%, which makes the level of accuracy in these predictions very high. The closeness of the computed mean values to the estimated input values (produced from the 'stakeholder engagement' in the early stages) further confirms confidence in the results, with 95% certainty.Key Risk Drivers:
To rank the risk factors for this project, we can also infer from the standard deviation results in the output. The deductions made from the result indicate that the cost factors had higher chances of affecting the project's earnings than the revenue components.Put simply;

Sensitivity Analysis: Testing The Limits
To further evaluate the robustness of the project and avoid any unforeseen circumstances that would affect the model's predictions, I tested the possibility of extreme scenarios through a sensitivity analysis. To achieve this objective, I altered the key variables identified as highly volatile in my base case results.These alterations were based on empirical and historical trends and assumptions. Since lower revenues and/or higher costs adversely affect NPV, this analysis sought to assess how profitability could be affected if prices fell and/or the costs increased. The following scenarios were tested, and the results are presented in the charts below:

Under these conditions, the project's probability of success dropped from 97% (under base case) to 79%, with a revised average NPV of $265 million. This shows that, despite the increased risks, the project remained viable in most scenarios with a 95% probability that the NPV would fall between $258 million and $271 million.

Conclusion and recommendationImplications and Detailed AnalysisProfitability with Caveats
Base-Case Viability:
The simulation indicates an average NPV of approximately $342 million with a tight confidence interval (roughly $337M–$348M). This suggests that—under normal or base assumptions—the project is economically attractive.Downside Risk:
Despite this optimistic mean, the worst-case scenario shows an NPV as low as -$220 million. Although the probability of negative NPV is around 7% in the base case, sensitivity analyses reveal that adverse conditions (e.g., steep drops in oil prices or spikes in decommissioning costs) can increase the probability of a loss significantly (up to 21% under stressed conditions).Decommissioning cost has the potential to erode profits drastically, whilst a significant downturn in oil prices can tip the balance from profit to loss, as oil revenue is a major driver of the overall NPV. Although operational expenses are modelled with a uniform distribution between $120M and $180M (and up to $200M in stressed scenarios), their cumulative effect over the production life further intensifies the risk, especially when paired with adverse movements in other variables.

The Interplay of Variables
1) Compound Risk:
The sensitivity analysis illustrates that the interplay between market risk (oil prices) and cost uncertainties (especially decommissioning) can have a compounding effect. For instance, even if oil prices remain moderately high, an unexpected surge in decommissioning costs could still drive the NPV into negative territory.2) Discount Rate Significance:
A 30% discount rate, reflective of the high-risk energy environment, magnifies the sensitivity of the project to future cash flows. This emphasizes the need for very robust revenue and cost management strategies.

Broader Industry Implications
1) Market Volatility:
The energy sector today is characterized by rapid fluctuations in commodity prices due to geopolitical tensions, shifting demand patterns, and a global push for sustainable alternatives. This project exemplifies how even well-planned investments can be vulnerable to external shocks.2) Regulatory and Environmental Pressures:
Increasing regulatory scrutiny, particularly around decommissioning and environmental impacts, further adds uncertainty to cost estimates. Companies are now required to incorporate more stringent ESG (Environmental, Social, and Governance) criteria, which could lead to higher compliance costs.3) Technological and Operational Efficiency:
The simulation underscores the necessity for real-time data analytics and adaptive operational strategies. In an industry where technology is evolving rapidly, maintaining operational efficiency is not just cost-effective—it’s essential for risk mitigation.

What It All Means: Insights and RecommendationsKey Takeaways:
With an average NPV of $340M and a 93% chance of success, the project is economically feasible.
Transition Energy Ltd should hedge against decommissioning costs and oil price volatility using financial instruments or cost management strategies.
Even in extreme conditions, the project shows promise, though risks remain. Risk, they say, cannot be eliminated but can be diversified or transferred. Exploring various risk mitigation strategies is the key to achieving optimal success.The NewFoundLand project symbolizes the challenges and opportunities of today’s energy sector. By leveraging sophisticated tools like Monte Carlo simulations, Transition Energy Ltd can confidently chart its course, balancing risk and reward.To decision-makers, investors, and industry pioneers: this is our chance to innovate and lead. Together, we can make informed decisions that secure energy supplies for future generations. Let’s navigate uncertainty—one project at a time.

In-Depth Recommendations
Based on the above analysis, the following strategic recommendations are proposed:A) Risk Mitigation Strategies
1. Financial Hedging Instruments:
- Oil Price Hedging: Leverage futures, options, and swaps to protect against steep declines in oil prices. Establish fixed-price contracts where feasible to stabilise revenue forecasts.
- Decommissioning Insurance: Explore specialised insurance products or contractual arrangements that lock in decommissioning costs, thereby reducing uncertainty.2) Cost Management and Operational Efficiency:
- Enhanced Cost Controls: Implement advanced cost tracking systems to monitor operational expenses in real-time. Use predictive analytics to flag potential overruns before they materialise.
- Lean Operations: Invest in process improvement initiatives and lean management techniques to minimise waste and inefficiency across the project lifecycle.

B) Scenario Planning and Dynamic Modeling
1. Continuous Model Refinement:
- Establish a feedback loop to update the Monte Carlo simulation regularly with the latest market data and operational performance metrics.
- Integrate real-time market intelligence to adjust probability distributions dynamically. This emphasises the significance of using current and advanced industry software systems like Crystal Ball and ETRM systems.2. Stress Testing and Sensitivity Analysis:
- Regularly perform comprehensive sensitivity analyses to understand how variations in key inputs affect NPV.
- Develop “what-if” scenarios that include extreme events (e.g., sudden regulatory changes, geopolitical conflicts) to ensure preparedness for adverse conditions.

C) Strategic Diversification and Investment1. Revenue Diversification:
- Consider investing in complementary revenue streams, such as downstream processing or renewable energy projects, to hedge against oil price volatility.
- Explore joint ventures or strategic partnerships with other firms in the energy sector to share risks and capitalise on cross-sector synergies.2. Capital Allocation and Contingency Planning:
- Set aside contingency funds specifically earmarked for potential overruns in decommissioning and operational costs.
- Revisit capital expenditure plans to determine if phased investments or staged development might reduce upfront risks.

Conclusion
The detailed Monte Carlo simulation has not only affirmed the economic feasibility of the NewFoundLand prospect—with an average NPV of $340 million—but also highlighted the critical risks that must be managed. The primary takeaway for Transition Energy Ltd is that while the project is attractive under base-case conditions, its success is highly contingent on managing decommissioning cost volatility and oil price fluctuations.The recommendations provided here—ranging from financial hedging and cost control to advanced analytics integration and stakeholder transparency—are designed to mitigate these risks. They reflect the current trends in business analytics and risk management, ensuring that Transition Energy Ltd is well-equipped to navigate an increasingly uncertain economic landscape.By implementing these strategies, Transition Energy Ltd can not only safeguard their investment but also set a benchmark for smart, data-driven decision-making in the energy sector. This approach, combining rigorous quantitative analysis with strategic foresight, is essential for businesses aiming to thrive in today’s volatile market.

Final Thoughts & Call to Action
This project reinforced my ability to analyze real-world economic challenges using financial modelling in Excel. It combined critical thinking, technical expertise, and effective communication—skills that are essential for a data analyst, economist, or policy strategist.💡 Are you a recruiter, policymaker, or business leader looking for data-driven insights?
💬 Let’s connect on LinkedIn or fill the forms below to send me an email!🚀 If you’re hiring, I’d love to bring these analytical skills to your team. Let’s shape data into solutions together.

• Back

• Back

EXPLORATORY DATA ANALYSIS OF THE VOLATILITY OF OIL PRICES USING DATA FROM U.S. ENERGY INFORMATION ADMINISTRATION (EIA)

About this Project:
This project explores the long-standing assertion that oil prices are volatile. While this is a common phrase in discussions about energy markets, the goal here is to test that claim with data rather than assumption. By combining data collection, statistical computation, and visualisation in Python, the project provides a structured investigation into the fluctuations of crude oil prices over time.

Objective of this Project:
The main objective is to quantify and illustrate the volatility of oil prices. This involves moving beyond raw price levels to examine log returns, measure price fluctuations, and visualise sharp movements caused by economic crises, supply shocks, and geopolitical events. Ultimately, the project seeks to explain why oil prices are labelled volatile and to present this evidence in a transparent and reproducible way.

The Data Used:
The data used in this project is sourced from the U.S. Energy Information Administration (EIA), a widely recognised and authoritative provider of energy statistics. Price data was collected into a structured pandas DataFrame for analysis, ensuring both reliability and transparency in the investigation. The data was downloaded in csv format and named as brent.csv.

Breakdown of the Analysis

1. DATA PREPARATION

A.# Import relevant Python libraries

B.# Load oil price data from IEA into pandas DataFrame# This loads my csv file into a DataFrame (df).
# I also parsed the "Date" column in the data to tell pandas to treat it as datetime. This is very necessary to avoid any doubts in situations where pandas reads the dates as different datatype.
# Running the 'df' gives the DataFrame as below with 9709 rows and 2 columns; "Date" and "Price_dollar_per_bbl":

C.# Clean and format the dataset:

This sorts my data in chronological order based on "Date".
The .reset_index() resets the index to match the new order and the drop=True deletes the default index values.
Running 'df' gives us the output below:

2. METHODOLOGY

Now the data has taken shape and ready for analysis. However, as done in finance and econometric analysis, I want to investigate more than just price movements; I want to calculate the price changes.
I would want to go the extra mile to estimate the difference between the price movements from one period to the next, using "log".

I created a new column called "log_return" using numpy, which will compute the log of each price.
I used the .diff() function to find the difference between the log price at time_t and the log price at time_t-1.The resulting relative changes in price is ideal for detecting trends, volatility and stationarity in this time series analysis. This makes the analysis more informative than using raw price or raw price differences.

We need to clean the data and remove any null values that may distort the analysis.

The .dropna() removes rows with missing values.This is necessary because the .diff() introduced a 'NaN' in the first row since it tries to find the difference between that price and price that doesn't exist, based on the data we have.

So the .dropna() gives this clean sample output:

3. PLOTTING PRICE AND RETURNS

Using matplot library, create a 'figure' with two (2) subplots stacked vertically. Set the dimensions of the figure to 10 inches wide and 6 inches high, which is big enough to see the trends clearly.Complete<code>:

The codes specify that the first chart should plot the "raw Brent oil Price" (in dollars per barrel) over time whilst the second chart is used to show the volatility and price movement intensity over time using "Log returns", and not just price level.They are also given titles as "Trend of Brent Spot Price" and "Log returns" respectively.The .tight_layout() adjusts spacing so that the titles and the axes do not overlap.The .show() helps to render the plot.

A. VISUALISATIONS:
-First chart: Oil prices over time to show long-term trends.
-Second Chart: log return to reveal underlying volatility.

-The Price plot reveals the long-term trends in brent oil prices which could be upward, downward or even stable.
-The log-return plot reveals short-term dynamics in the oil market, which could be in the form of spikes, crashes or random movements.

B. QUICK STATS ON LOG RETURNS:

A. 📉THE PLOTS

🛢️Top Plot: Brent Price Over Time
This chart is a visual timeline of global energy dynamics:

📊Bottom Plot: Log Returns (Volatility)This chart reveals the 'emotional intensity' of the market:- Spikes in log returns indicate days of extreme price movement; either panic selling or euphoric buying. The oil price has fluctuated considerably over the past 3 decades.

B. 📊 SUMMARY STATISTICS OF BRENT CRUDE LOG RETURNS

🧠 Analyst Insights

• Back

• Back

🚧 Project Status: Work in Progress

This dashboard is still evolving! I’ve shared this "work in progress" here to:

🚧 What this project is about:

This comprehensive project involves an end-to-end process of building a Power BI dashboard for pizza sales analysis, starting with data import and transformation using MS SQL Server and Power Query Editor.It details the creation of key performance indicators (KPIs) such as total revenue and average order value using DAX, along with various interactive visualisations. The project progresses through designing charts like daily and monthly sales trends, percentage of sales by pizza category and size, and funnel charts for pizza quantity sold, culminating in a detailed analysis of top and bottom-selling pizzas. Throughout the process, the importance of validating Power BI results against SQL queries is emphasized, along with instructions for formatting and enhancing the dashboard's visual appeal.

• Read more about the Project KPIs and filters used

📬 Let’s Connect!Thank you for visiting and if you're looking for a data-savvy problem solver, or want to collaborate, feel free to reach out!

💬 “Data doesn't speak for itself — but with the right dashboard, it tells a powerful story.”

• Back

• Take me back

The project defines five key performance indicators (KPIs) to provide an overall understanding of the business, and I used filters to highlight these KPIs for deeper insights into business performance.

Project KPIs

The client's KPI requirements are explicitly defined and I will include calculations for each:1. Total Revenue: This KPI represents the sum of the total price of all pizzas ordered. It gives an aggregate measure of all sales.
2. Average Order Value: This is calculated by dividing the total revenue by the total number of orders. It indicates the average amount spent per order.
3. Total Pizza Sold: This KPI is the sum of all quantities of pizzas sold. It provides the total number of individual pizzas purchased.
4. Total Orders: This represents the total number of distinct orders placed. It counts unique order IDs to avoid duplication if multiple pizzas are in one order.
5. Average Pizzas Per Order: This KPI is calculated by dividing the total number of pizzas sold by the total number of orders. It shows the average number of pizzas included in a single order.These KPIs are displayed prominently at the top of the Power BI dashboard using a new card visual in power BI, providing a high-level aggregation of values for the business.

• Take me back

• Back

insights from analysis of the Regional energy consumption trend in the United Kingdom

Problem Statement: Exploring the regional energy consumption trends in the UK to identify opportunities for increased efficiency and sustainability. Highlight regions with the highest potential for renewable energy adoption.

Introduction
The United Kingdom has been ambitious in the race to achieve net-zero emissions by 2050. With this bold vision of net zero targets came the establishment of the Great British Energy, headquartered in the energy capital of Europe—Aberdeen. These goals have stirred debates among academics and industry players about whether the UK's net-zero ambition has outpaced reality.Now, kindly buckle your seat belts and join me on a journey across the UK, where the energy landscape tells the story of tradition and transformation. Our goal is to reveal the untapped potential for renewable breakthroughs that remain hidden within the familiar consumption patterns of the regions.As the energy landscape continues to evolve, it remains very crucial for investors and policymakers to understand the consumption trends to make informed decisions about renewable energy adoption.The energy trilemma—affordability, sustainability and security—continues to impact the transition to fully green energy sources, especially when this fight is not given the same level of prioritisation on a global level. This project does not only highlight my passion for sustainability and the need to achieve net zero but also demonstrates my expertise in data cleaning, integration, and visualisation using tools like Excel, SQL, and Power BI.As we explore the data today, I encourage you to picture each region as a chapter in this unfolding narrative—a tale of challenges met with opportunity, where every kilowatt-hour holds clues to a more efficient and sustainable future. Let us uncover these patterns together and see whic areas are poised to lead the energy revolution in the UK.

Data Collection & Tools UsedData Sources
To construct a comprehensive view of the UK’s energy landscape, I sourced data from several reputable, publicly available datasets:1. Annual Energy Consumption Data:
This dataset, the Energy Consumption in the UK (ECUK), was obtained from the UK Government’s Energy Statistics repository. It provided detailed annual figures from 1970 to 2022, including both unadjusted and temperature-corrected energy consumption values, along with disaggregated data by fuel type (solid fuels, petroleum, gas, bioenergy, and electricity) and by sector (industry, transport, domestic, and services).2. Regional Consumption Data:
For subnational analysis, I collected regional consumption data provided by the Department for Energy Security and Net Zero (DESNZ) that detailed energy use by region. This dataset included fields for sectors like domestic, transport, and industrial consumption as well as fuel-specific breakdowns, such as coal, petroleum, gas, electricity, and bioenergy. This allowed for a granular look at how energy is used across various regions.3. Renewable Energy Planning Database (REPD) Data:
This dataset^{1, 2,} by the Department for Business, Energy and Industrial Strategy (DBEIS), which contained data on renewable energy projects, was used to understand the regional trends and their respective renewable energy capacity. This dataset provided key information about each project, such as the site name, technology type (e.g., biomass, energy from waste, wind offshore and wind onshore, etc.), installed capacity, operational status, and geographical location (region, county, and country).PS: Other relevant sources of data for confirmation can be found here and here.

Tools and Technologies
To transform the raw data into actionable insights, I employed a multi-tool approach:Excel:
Used for initial data cleaning and transformation. Pivot tables, sorting & filtering, and Find & Replace functions were essential for handling the metadata, standardising column names, and identifying inconsistencies across the datasets.MySQL:
Enabled robust data integration and validation. I wrote SQL queries to merge the datasets, perform group aggregations, create mapping tables for standardising region names, and validate data integrity through calculated checks.Power BI:
Served as the visualisation and dashboarding tool. In Power BI, I built interactive visuals such as bar charts, maps, and line charts, along with advanced DAX measures to convey renewable adoption rates, energy deficits, and consumption trends over time.

• STILL LOADING...

Data Cleaning & Integration1. Initial Data Assessment
Excel Exploration:
I started by opening the raw datasets in Excel. Each dataset presented its own set of challenges: metadata rows, inconsistent column headers, and varying formats. For instance, the Annual Consumption Data had extra header rows and verbose labels that needed simplification, while the Regional Consumption Data required careful removal of metadata and standardisation of regional names.Identifying Key Fields:
I noted that for the REPD dataset, critical fields such as installed capacity, operational start date, and construction start date sometimes contained missing values. Likewise, the Regional Consumption Data needed consistency in how regions were named, since this would be vital when merging with the REPD dataset.

2. Cleaning Each Dataset in Excel
Annual Consumption Data:Step 1: Removed extraneous rows and metadata to isolate the actual data.
Step 2: Standardized column names (e.g., converting long names to “Year,” “Unadjusted Consumption,” etc.).
Step 3: Verified totals by ensuring that the sum of sectoral consumption matched the overall totals.
Pivot Table Use: Created pivot tables to aggregate consumption by year and fuel type, which helped spot outliers and missing values.
Regional Consumption Data:
Step 1: Deleted metadata rows and removed unnecessary columns, keeping essential fields like regionalcode, region, localauthority, domesticconsumption, transportconsumption, industryconsumption, and totalconsumption.
Step 2: Standardized column names (e.g., “All fuels: Domestic” was renamed to “domesticconsumption”).
Step 3: Used pivot tables to cross-check that the sum of domestic, transport, and industry consumption matched the reported total consumption.
Step 4: Reviewed fuel-specific columns to ensure consistency and flagged any missing values.
REPD Data (Renewable Projects):
Step 1: Removed non-critical columns such as detailed planning notes.
Step 2: Standardized text entries in technologytype by replacing variations (e.g., “Biomass (dedicated)” vs. “Biomass (co-firing)”) with a single term, “Biomass.”
Step 3: Identified missing values in key fields like installed_capacity and date fields. For missing capacities, I considered imputation (using the median value for similar technology types) or, if too critical, excluding those records.
Step 4: Standardized regional information by creating a mapping table (in Excel and later in MySQL) to align names between the REPD dataset and the Regional Consumption Data.3. Data Integration Using MySQL
A) Merging Datasets:
With the cleaned CSV files from Excel, I imported the data into MySQL. I created tables for each dataset and a separate mapping table to standardize region names.Region Mapping:
I created a mapping table to reconcile differences in region names (e.g., “South Glamorgan” vs. “Wales”).[insert sql code snapshot of mapping]Joining Data:
I then joined the REPD data with the Regional Consumption Data using the standardized region field. This allowed me to aggregate renewable capacity per region and compare it directly against regional consumption totals.
[isert sql code snapshot of inner joins]Validation:
I ran SQL queries to:- Check for missing or null values in key fields.
- Validate that calculated totals (e.g., sum of domestic, transport, and industry consumption) matched the reported totals.
- Remove duplicate entries and ensure data integrity.

Analysis & VisualisationWith the integrated dataset in hand, I imported the data into Power BI. Here’s how I transformed data into a story:- Interactive Bar and Map Visuals: I created bar charts to compare total regional consumption against renewable capacity and a map visual to highlight regional disparities. These visuals were enhanced with slicers (for year and region) to let users explore the data interactively.
- Calculated Measures: Advanced DAX measures were developed to calculate renewable contribution percentages and energy deficits. For example, the measure RenewableContribution = DIVIDE(SUM(TotalRenewableCapacity), SUM(TotalRegional_Consumption), 0) * 100 provided a clear picture of how much renewable energy contributes relative to overall consumption.
- Trend Analysis: A line chart depicted energy consumption trends over time, segmented by fuel type. This visualization helped capture the gradual shift away from traditional fossil fuels towards renewables.

Challenges & How I Overcome ThemNo data project is without its challenges. Here are some of the key obstacles and my solutions:- Inconsistent Data Formats: Different datasets had varying formats for dates and region names. Using Excel’s powerful cleaning tools and pivot tables, I standardized these values, ensuring consistency across the board.
- Missing Values: Critical fields like installedcapacity and operationalstart_date had missing values. I used a combination of imputation (using medians or averages for similar technology types) and careful filtering to address these gaps.
- Data Integration Complexity: Merging datasets with different granularities (national, regional, project-level) required careful planning. I used MySQL to create mapping tables and execute precise joins, ensuring the integrated dataset was both accurate and comprehensive.

Conclusions & ReflectionsThis project has been a transformative journey, teaching me the importance of meticulous data cleaning, the power of integration, and the art of effective storytelling through data visualization. By merging diverse datasets and revealing key insights on energy consumption and renewable capacity, I’ve not only developed technical skills in SQL, Excel, and Power BI but also learned how to communicate complex data insights in an accessible manner.Through this project, I transformed raw, fragmented datasets into a coherent, integrated view of the UK’s energy consumption and renewable energy capacity. By leveraging the strengths of Excel, MySQL, and Power BI, I not only uncovered key insights about regional energy trends but also honed my data cleaning, integration, and visualization skills.Key Takeaways for Readers:
-Data Integrity is Paramount: Even the best analysis can be skewed by poor-quality data. Rigorous cleaning and validation are essential. Learn how to source and prepare data from diverse sources.
- Integration Unlocks Insights: Combining different data sources can reveal patterns that isolated datasets cannot. This project helps to understand the process of merging datasets through standardised keys and mapping tables.
- Visualisation is Communication: A well-designed dashboard can bridge the gap between technical analysis and strategic decision-making. Discover how interactive dashboards can bring data stories to life.
- Problem-Solving: Gain insights into handling missing values and inconsistencies through practical, step-by-step methods.

Call to Action
If you’re passionate about the future of energy or seeking a data professional who can turn raw data into actionable insights, I invite you to explore this project further. Connect with me on LinkedIn or via my website to discuss how data-driven strategies can pave the way for a more sustainable future. Together, let’s harness the power of data to drive positive change.I invite you to explore this project further and see how I can turn raw data into actionable insights. Whether you are looking to drive sustainable energy strategies or seeking a data professional adept at cleaning, integrating, and visualizing complex datasets, this project showcases my ability to deliver clear, impactful results. Connect with me on LinkedIn or visit my portfolio for a deeper dive into my work.

• Home
• Projects
• About Me
• Contacts

EFFECT OF UK's ENERGY POLICIES ON INVESTMENT AND DECOMMISSIONING IN THE UKCS

Picture this: It's the year 2050. The morning sun rises over a bustling coastal city. You raise your head or open your window to see the huge platforms on the sea, and the story of how that machine helps to bring out the oil and gas you need for your car starts to come back to you. People everywhere are waking up to start their day. Families gather for breakfast; children laugh as they prepare for school. Offshore, energy platforms hum quietly, supplying the lifeblood of modern life. At first glance, it's a scene of balance, progress, and continuity.

But as the day unfolds, the cracks in this picture begin to show, and floodwaters from the previous storm still linger on the streets, disrupting commutes. Farmers in nearby regions are struggling with soil that's either too dry or too salty. Fishermen whose livelihoods once thrived now contend with dwindling stocks as the ocean's ecosystems falter. The hum of industry is louder than ever, but so too are the voices of discontent and the visible scars on the landscape.A just Transition: The Neutral Ground
"Now, imagine if we had foreseen this. Imagine if the choices made decades earlier had been different—not about choosing sides, but about coming together to chart a shared path.What if we had embraced innovation, cooperation, and sustainability before the storm clouds gathered? What if governments, industry leaders, and activists had sat at the same table to align on a vision, not for what divided us but for what connected us—a shared future on this fragile planet? What if the UK was not the only nation setting ambitious targets with the aim to achieve a global goal but all nations were aligned? What if the UK was ambitious and the world was able to achieve this global goal, but the UK now had to depend on other countries that played no role in this achievement? Yes, the questions go on and on and on.....Most developing countries remain poor because investors are afraid to invest their resources there. This is not because they are "developing countries" but due to instability in most cases. Instabilities, whether political or policy wise can hinder the flow of Foreign Direct Investments (FDI) into any country. Check out my work on the "Effect of Sovereign Credit Rating on Foreign Direct Investments in selected African countries".So the begging question is, what effect would a stricter UK policy on their oil and gas sector have on investment in the sector, compared to their global competitors? If existing firms are disincentivised to cease operations before the planned time, how would this affect the supply chain as well as Decommissioning in the UK Continental Shelf (UKCS)?The purpose of this project helps us explore these possibilities together. This work is useful for governments in policy analysis as well as private businesses in the general energy sector.Watch this space as we answer these thought provoking questions together...

• Return to Home
• Back to Top
• View Other Projects

• Home
• Projects
• About Me
• Contacts

More About Me

I’m a Business Data Analyst with hands-on experience in Excel, SQL, Python, Power BI, GAMS, STATA, R and Business Intelligence.I started my career in telecom sales where I analysed market data for the company. I also analysed competitor performance to ensure that our products enjoy high Intention to Recommend (ITR). I became a supervisor for a pilot project, the mobile money merchant acquirers, which is now one of the flagship products driving high revenues for the company. Afterwards, I made the leap into business operations at the Ghana Maritime Authority, handling the analysis of operational and financial data of the entire organisation with presence in 6 regions and 10+ departments, informing strategic planning and implementations. It was at this point that I realised how important data was at the heart of every organisation and my love for analysis of data grew tremendously.Even though Excel was sufficient for the work at the Ghana Maritime Authority, I took charge of my development by learning SQL, Power BI, and Python to become a leader in the field. I championed the automation of the company’s business processes, designing structured data collection forms, using Python to automate transmission, and implementing an Extract, Transform, Load (ETL) workflow to consolidate data into Excel and SQL, which was then visualised in Power BI for accurate reporting and board presentations. This approach eliminated delayed reports and minimised errors in data transmission. In my role as the data analyst, I leveraged data to identify trends in maritime security, helping the organisation reduce piracy, armed robbery, and bunkering incidents. By sharing actionable insights with leadership and external stakeholders, I strengthened decision-making and improved safety across the maritime sector.While working at the Ghana Maritime Authority, I went on to pursue a Masters degree in Business Administration (Finance) to ensure I understood the underlying factors behind the numbers I worked with daily. Just before I completed my MBA, I won a scholarship to pursue MSc in petroleum, Economics and Finance at the University of Aberdeen, UK, where I was exposed to the world of energy and how data shapes this massive driving force behind economic growth.Over the past few months, I have tackled personal projects head-on, adapting quickly to a new cultural environment and navigating challenging circumstances to build solutions from scratch, demonstrating how I harness analytics tools to solve complex business problems and deliver tangible results within deadlines. My goal in every organisation is to drive commercial and financial success, delivering measurable impact, so much so that previous employers have been reluctant to let me go.Explore my portfolio to see how I transform data into results that steer organisations toward their objectives.My Interests:
I am passionate about research and the analysis of data. I am naturally curious, and I always strive to stretch my abilities to learn new things, and so I love to work with different people especially among smart people. I am a highly motivated and result-oriented energy economist with a strong passion for sustainability.What I bring to the team:
My background combines a solid foundation in economics and finance with a strong interest in Analysis of data that create real business value and drive revenue. Throughout my career, I have honed my expertise in economic analysis, financial modelling, and risk assessment, applying these skills to evaluate the feasibility and viability of projects.I excel at tasks that involve research, analytical problem-solving, and strategic thinking, particularly when they are data-driven. These challenges are like mental puzzles for me, and tackling them with both logic and outside-the-box thinking gives me a real surge of energy.I have experience working in a team, completing time-sensitive projects within deadlines, and taking pride in delivering exceptional customer service. In every organisation I have worked with, I work hard to act as a positive role model for the company, take ownership of my professional development, and embrace change to ensure the business becomes a market leader. I am hungry to learn, want to be the best I can be and always strive to support my co-workers to achieve a common goal.Why work with me:
I'm a creative problem solver who analyses information and facts to achieve the best outcome for the team. I quickly fit into a team, act selflessly, and work hard to help my employer achieve their commercial and financial goals.I like to work on projects that have real world impact and make a difference in society, by solving problems that businesses face.Feel free to navigate through my projects, which combine the application of multiple analytical tools to showcase my versatility in data analysis.View My Resume | LinkedIn | YouTube | Email

• Return to Home
• Back to Top
• View Other Projects

• Home
• Projects
• About Me
• Contacts

HOSPITAL EMERGENCY VISITS ANALYSIS

• Home
• Projects
• About Me
• Contacts

BANK LOAN PORTFOLIO PERFORMANCE ANALYSIS

Project OverviewThis project evaluates a bank loan portfolio comprising 38,576 loans with $435.8 million in capital deployed, with the objective of assessing portfolio performance, credit risk exposure, and profitability.Using KPI-driven Excel dashboards, the analysis tracks funded amounts, repayments, interest income, and borrower risk metrics, enabling clear separation of good vs bad loans and visibility into portfolio-level returns.The portfolio generated $473.1 million in total repayments, representing a net profit of approximately $37.3 million, with an average interest rate of 12.05% and borrower DTI of 13.33%.The objective was to support risk management, lending strategy, and profitability decisions using structured, transparent analysis.

---

Tools & Technology Used:

• Microsoft Excel (Advanced) – pivot tables, calculated fields, dashboard design

• Power Query & Data Modeling - KPI Structuring, Data quality and categorical derivations

• Financial Analysis – loan performance metrics, credit risk segmentation

---

Dataset OverviewThe dataset covers 38,576 unique loan applications issued from January 2021 to january 2022 with 25 columns/ fields. The dataset represents a bank loan application portfolio, where each row corresponds to a unique loan application and the fields include:- Loan Identifiers: Unique ID per application- Financial Metrics: Loan Amount, Total Payments Received, Interest Rate, Debt-to-Income (DTI)- Loan Status: Fully Paid, Current, Charged off (Bad debt)- Temporal Data: Loan Issue Dates- Borrower Attributes: Employment Length, Home Ownership- Geography: Borrower StateThe dataset supports both performance analysis and credit risk segmentation..

---

Problem Statement“How can a bank quickly assess loan portfolio health, identify risky segments, and track performance trends over time using a single, decision-ready dashboard?”The analysis focuses on separating profitable (good) loans from high-risk (bad) loans, while tracking funding, repayments, and borrower characteristics.

---

Approach & MethodologyA. Data Preparation- Used Power Query editor in Excel to import raw CSV filed with data to preserve original dataset format.- Converted raw CSV file into XLSX format and connected to PostgreSQL database to leverage the robust data management capabilities of SQL with flexible analysis and visualisation features of Excel to handle large datasets in the furure.- Structured the dataset as an Excel Table to enable dynamic pivot tables.- Validated record counts and ensured each row represented a unique loan application.B. Core KPI DevelopmentKey portfolio KPIs were defined and calculated using pivot tables:- Total Loan Applications → Count of unique loan IDs
- Total Funded Amount → Sum of loan amounts issued
- Total Amount Received → Sum of total payments received (principal + interest)
- Average Interest Rate → Average interest charged across all loans
- Average Debt-to-Income (DTI) → Mean borrower leverage indicatorAll values were professionally formatted into thousands (K) and millions (M) for executive readability.C. Time-Based Performance AnalysisTo evaluate performance trends:- Created Month-to-Date (MTD) KPIs using loan issue dates
- Filtered latest reporting month (December) for current performance
- Derived Previous MTD (PMTD) metrics using November data
- Calculated Month-on-Month (MoM) Growth using:MoM = (MTD -PMTD) / PMTDThis highlights acceleration or deterioration in lending and repayment activity.D. Loan Quality Classification (Good vs Bad Loans)
Since no direct “good/bad” flag existed, loan quality was derived from loan status:- Good Loans = Fully Paid + Current
- Bad Loans: = Charged OffA calculated column was created to classify loans accordingly, enabling:
- Loan quality distribution analysis
- Percentage contribution of good vs bad loans
- Visual comparison using doughnut chartsE. Segmentation & Risk AnalysisThe portfolio was further analyzed by:
- Geography: Loans by state (map visualisation)- Employment Length: Risk distribution by job stability- Home Ownership: Mortgage, Rent, Own (tree map)Long-Term Trends: Loan quality evolution over timeDuplicate pivot tables were used where necessary to enable map visualisations.

F. Dashboard Design
Two dashboards were delivered:1. Summary Dashboard
- High-level KPIs
- Good vs Bad loan breakdown
- Portfolio health snapshot

2. Overview Dashboard
- Geographic distribution
- Borrower segmentation
- Trend and performance analysis

Slicers were added for intuitive filtering and interactive exploration.

---

Key Insights & Business ValueKey Financial & Risk Insights
- The bank issued 38,576 loans totaling $435.8M, generating $473.1M in total repayments, resulting in a net portfolio profit of ~$37.3M.
- Good loans accounted for 86.18% of total loan volume (≈33.2k customers), representing $370.2M in issued capital and $435.8M in repayments, indicating strong portfolio performance and pricing effectiveness.
- Bad loans represented 13.82% of total loans (≈5.3k customers), with $65.5M issued but only $37.3M recovered, highlighting a material credit risk exposure.
- The average interest rate of 12.05% and DTI of ~13.33% suggest relatively conservative borrower leverage, supporting overall portfolio sustainability.
- Loan quality segmentation enables targeted risk mitigation by identifying borrower segments and regions contributing disproportionately to defaults.

Business Value1. Loan Portfolio Health
- A clear distinction between performing and non-performing loans enables rapid risk assessment
- The majority of loans fall within the good loan category, supporting portfolio stability2. Credit Risk Exposure
- Charged-off loans cluster within specific borrower segments
- Employment length and home ownership show meaningful correlation with loan performance3.Financial Performance
- Total amount received provides visibility into actual returns, not just issued capital
- Interest rate and DTI analysis highlight pricing and affordability dynamics4. Geographic Risk Concentration
- Certain states show higher loan concentrations, helping identify regional exposure risk5. Executive Decision Support
The dashboard enables leadership to:
- Monitor portfolio health at a glance
- Adjust lending criteria proactively
- Strengthen risk controls
- Improve capital allocation strategies

---

What I LearnedThis project strengthened my ability to:- Translate financial datasets into risk-focused insights
- Build bank-ready KPIs using Excel pivots
- Derive meaningful classifications when data lacks explicit labels
- Design dashboards aligned with credit, risk, and finance leadership needs
- Communicate complex loan performance metrics clearly and concisely

• Return to Home
• Back to Top
• View Other Projects

Healthcare project using made up dataLet me tell you a story about my very good friend, (suggest a name that has underpinning meaning). (Name) completed first degree with first class honours, second degree in the second most prestigious university in the US and went ahead to have several certificates. He had work experience in different fields where his contribution was invaluable to the companies. In every work he had been in, it always ended up with emotions to the point where colleagues cried because he had to leave. And the management were willing to increase his salary just for him to stay. But you know one of those moments where you wish but can't? Yes, he always had to leave one way or the other because he was relocating or had to further his education on scholarship- often hard-to-choose-between opportunities.Fast forward, (name) has good certificates to his name acquired through hardwork and determination, and he has vast experience in companies where he added a lot of value. Now he is at a point in his life where he doesn't just want to work but wants a place where he can serve humanity in a more direct way due to his love for sustainability. He finds himself in a new country where he came as a scholarship student. After school he excelled in the program and is now looking for a job. The market has been very tight and his chances of getting a job in a foreign land remains elusive. He therefore decides to create his own hospital in the local community he found himself due to the school. This, he believed, would help him to directly serve people in a life changing way. He is not a doctor but his expertise in data analytics would ensure he helps the company create the best service that is of great value the community.