Financial Report (Yearly) Dashboard in Excel 2010

 

This Dashboard gives us a detailed description of all the KPIs involved in the process or business. Customization is possible in this dashboard. This dashboard is a very useful tool to analyze the 7 year trends depending on the KPIs of any process

Let us understand the dashboard in detail

There are a total of 4 sheets in the excel file. (Report, Raw Data, Settings and Formulas)

Let us understand all the tabs in detail.

Untitled_01_img

 

Report Tab:

Under the report tab all the KPIs are displayed

You can insert your company name in cell B3

img1
 
You can select the report year from cell H2 dropdown

img2
 
Once you select the report year from the dropdown menu, automatically all the KPIs would be refreshed accordingly

Below are the Key Performance Indicators

img3
 
We have Revenue, Net Profit, Interest, Loss and Profit as indicators in Key Performance indicators
Here the data would be displayed in numbers and in traffic lights symbols which would be automatically indicated. Also the trend would be shown in a column graph
If KPI gets increased compared to previous year than traffic light would be displayed in Green

img4
 
If KPI gets equal compared to previous year than traffic light would be displayed in Yellow

img5
 
If KPI gets decreased compared to previous year than traffic light would be displayed in Red

img6
 
Also this dashboard has other Indicators like KPIs, Selected Year, Previous Year, % Change and Yearly Trend

img7
 
KPIs - Reflect all the process indications

Selected Year –Displays the year that is selected by the user in cell H2.

Previous Year –Displays the previous year of the selected year
% Change –Displays the change of KPI data in comparison of selected year to previous year. This column also shows % change using traffic lights

5 – Yearly Trend – Displays the 5 years yearly trend in graphical view

Raw Data Tab:

In this tab we can input 25 KPIs for 7 years

img8
 
Under the Raw Data Tab we have 8 columns (Metrics name and 7 Years Data)
Metrics –Displays all the metrics that are selected

img9
 
7 Years –We need to insert data for 7 years

img10
 
Settings Tab:

Under this tab we can select 5 KPIs that would be displayed in the report tab in Key Performance Indicators section

img11
 
We need to select from the dropdown menu as shown below

img12
 
The selection would be displayed as shown below

img13
 
Settings Tab:

In this tab there are 2 sections (KPI calculations and All KPIs calculations)

img14
 
In KPIs section 5 KPIs are displayed which wewe had selected in settings tab

img15
 
Calculations are displayed here according to the selection which we have made in settings tab

img16

 

image 48

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com

 



6 thoughts on “Financial Report (Yearly) Dashboard in Excel 2010

  1. Looks nice. A comment I see repeatedly for these templates is: where is the file so that it can be used as a resource or starting point by others? Or are articles like this only to show off what someone did once?

    • You can find this template in Excel 2013. Open up excel, click on new, select template and scroll down the list, or you can search by typing in the search field “financial analysis”. The template is awesome and very useful. good luck.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>