top of page

Weekly 
Financial Dashboard

OBJECTIVE

The objective of this project was to gather, clean, and create visualizations of TPB's financial data for upper management to explore and monitor data/numbers on a weekly basis, as well as facilitate decision making. The project involved gathering the raw data directly from the accounting department, cleaning the data using Excel formulas and macros, and then creating visualizations using Power BI.

Tools

  • SQL

  • Microsoft Excel

  • Microsoft Power BI

​

Data Source

  • Everyday financial data from Accounting and Finance teams.

Data Prep

  • The first step of this project involved gathering and exporting the data from multiple sources/teams into a single excel document. This file normally contains between 10,000-20,000 rows of data. I then cleaned the data by removing NA’s and duplicates, fixing incorrectly formatted fields and filling up blanks. Below is the visual of one of the final sheets from the data.

  • Once the data is clean and ready in Excel, it was imported into Power BI, where I created relationships between the different tables based on common fields (date and GL name)

Design

  • The design consisted of looking and testing very simple yet efficient styles of graphs. The key fields graphed were:​

    • interest income

    • Personnel cost

    • Operating expenses

    • Total non-interest income

    • Total occupancy cost
       

  • ​Each of these was broken down into subcategories to show the values changing over time as well as the comparison between actual and forecast value. For that reason I chose to use: 

    • A line graph to show Budget, Forecast and Value on X-axis and Dollar amount on the Y-axis for the key categories

    • A horizontal line chart to show a comparison of the breakdown between actual and budget dollar value for each sub category of assets/income.
       

  • Below is an image of the sketches created as a rough draft before creating the real graphs in power BI. This step helped figure out the best type of graph to be utilized for comparison, relationship or distribution purposes. The ones being used for this project were both comparison graphs (compare categories or elements among the own items or over time.

Visualizations

  • My favorite part of the entire data analytics process is creating the visualizations. Now, with a clear idea of what I want to show, I am ready to jump into the report. I started by creating an overview page just showing the 2023 net income and total asset dollar amounts as text. Then, I added two line graphs comparing the actual values vs the budget and forecast for the first three months of the year of Net Income and Total Assets key metrics. Below those I created the breakdown of the different components of each.
     

  • I decided to make these graphs very simple, 2D and light shading to really focus on the data points for each week. I also added some functionalities to the dashboard including a text box of information when you hover over the dots of the line graphs, as well as a drill-through of each of the components. The arrows next to the graphs take you to these breakdowns once clicked.
     

  • The breakdown pages follow a similar format to the overview page, with a line graph to show the change over time of the three fields (actual, budget and forecast) as well as a horizontal bar graph to compare actual and budget value of the subcategories of that breakdown branch. It also includes a “weekly updates” text area for Finance teams to add any comments or updates per week. The orange color leads you to a graph for that specific topic when clicked.

Reflection

  • One of the most important takeaways of this project, and one that I keep being reassured of is that simplicity is a data visualization best friend. Some tips that helped me and would love to share with any other analyst/data visualization specialist:

    • Remove meaningless color coding, it can be confusing and unnecessary!

    • Symplify numbers (removing extra decimals that could clutter)

    • Adding extra data to the charts can sometimes be helpful to provide clarity(data labels or axis titles)

    • Make it user friendly: very important to consider who the final and main dashboard user is. 

bottom of page