DASHBOARD OF A COMPANY INCOME STATEMENT
INTRODUCTION
I made this dashboard using the INDEX & MATCH function; Both VBA and pivot table were not used. It is a simple Financial Dashboard for Professional Finance.
OBJECTIVES
This project aims to provide an interactive dashboard highlighting the year’s key performance indicators (KPIs) in 2020. I chose this project because I was interested in applying my understanding of financial analysis and data visualization.
DATA GATHERING
The data was obtained as a csv file from a secondary source. Link below:
DATA PROCESSING
Firstly, the data’s dashboard template was designed then the following steps were carried out:
Step 1: Calculate the sum of each metric, and repeat for quarters 1, 2, 3, and 4.
Step 2: Create a table with the same parameters from the data, and the next column will be used for the INDEX AND MATCH functions.
In the first row, input the formula and auto fill downwards. Then use custom formatting to converts to millions.
Furthermore, change the Net profit margin and revenue growth number formats to percentages.
This is how your table should appear.
Step 3: Insert a “combo box (form control)” from the developer ribbon, then right click to format control and input the date range (January-December). Cut and paste in the dashboard sheet.
Step 4: Create another table for Net profit trendline chart and reference the data respectively. Then Go to Insert, pick 2D — Line chart, cut and paste in your dashboard.
Step 5: Make a new table and reference the data for the waterfall chart. Go to Insert, select a waterfall chart, and then copy and paste it into your dashboard.
Step 6: Go to the dashboard sheet, insert a text box, then reference income, expenses, tax, total income, total profit, income achieved vs target, expenses reached vs target respectively from the table in step 1.
Furthermore, For % of net profit and revenue growth per month, create a pie chart from the table in step 1. Cut and paste in the dashboard respectively.
Step 7: Arrange the chart neatly to fit in the dashboard.
INSIGHTS GOTTEN FROM THE DASHBOARD
1. The lowest and highest revenue months are July and December, respectively.
2. Q2 had the highest earnings.
3. The projected income for April, June, and December was exceeded by more than 100%, making it a successful month.
4. September has the highest revenue growth, followed by December and April.
5. The projected income achieved in July fell below 70%, making it a dreadful month.
6. TAX is 15% of EBIT per month.
CONCLUSION
This report’s data thoroughly analyzes the company’s financial performance in the year 2020. Potential investors can use this information to better understand how the business is doing, how its financial assets and liabilities relate to it, and how much money the business has made over the last 12 months. This report is necessary so that the shareholders may understand how they feel about the business’ success and how it might affect their investment. The management can use this information to examine financial trends for potential future adjustments, marketing plans, and updates to product information.
