Project Revenues and Costs Tracking Dashboard

Dear All,

I am very happy to announce one of my latest projects I did for a customer.
My customer needed a dashboard to sort out the following issues:

1. Tracking his revenues and costs by project showing to total profit
2. Showing the past 10 years revenues and costs split by year
3. Splitting the revenues and costs between the active staff based on the percentage of contribution to costs by staff. Inactive staff should not be counted for revenues and costs

Please find below the solution I proposed to him. [The Dashboard is downloadable at the end of this page]

1 Dashboard:

The dashboard is the page were data are analysed and it consists of different sections

1.1 Select Project:

In this area you select the project you want to analyse the data for, in the example above the data are shown for a project called Pluto.

Projects are selectable from a drop down menu and it is possible to analyse only one project at time.

1.2 Menu:

On the left side of the page you can see a menu. Buttons will help you to navigate through the spread sheet.

Hovering the mouse on the button will show you were it is pointing.

1.3 Quick Summary:

In this section there are three main information about the project.
The number of year shows since how long the project is active, based on the first transaction [income or expenses] of the Database sheet. This will be discussed later. 2. Database
Open balance shows the profit or loss of the project when is created [i.e. for past projects that you want to import in the dashboard]. The balance is setup on the Database sheet.2. Database

Total profit show the profit for the project as at today.

1.4 Profit Trend:

This section shows a graph of the profit of the project for the past 10 year only.

The profit is split by year and the amount per year is shown on top of each bar of the histogram

1.5 Income, Expenses and Profit Summary:

This section has 3 subsections to show:

1. The income:
a. As total income for the full project life since the beginning of the project to today
b. As a comparison with the same period for the previous year [saying period means from January to today]

2. The expenses:
a. As total expenses for the full project life since the beginning of the project to today
b. As a comparison with the same period for the previous year [saying period means from January to today]

1. The profit:
a. As total profit for the full project life since the beginning of the project to today
b. As a comparison with the same period for the previous year [saying period means from January to today]

 

1.6 10 Years Breakdown:

This section shows a quick snapshot of the past 10 years Income, Expenses and Profit.
The total Income, Expenses or Profit is split by year and the icons show the trend compared to the highest income in a specific year.
Please note that this tables do not show the total income, expenses or profit for the all project life but for 10 years only.

1.7 Current Band Staff and Income/Costs Allocation Based on Contribution Percentage:

This is one of the most important section of the dashboard.

In this section is shown:

a. The staff names

b. The Income, Expenses and Profit contributions based on the percentages of risk agreed on the

Staging Tables section. Please note that if the percentages are all the same, Income, Expenses and Profit should show the same values. 3. Staging Tables

2 Database: 

The Database is the core of the financial model and here is where the magic happen.

As you can see from the picture, there are two main coloured area.
The areas shaded in yellow contain formulas and do not need to be updated, whole the striped area need to be manually filled.
The striped area contains the following fields:

2.1 Date:

This is the date of the transaction

2.2 P&L:

P&L represent the type of transaction you want to enter.
Two types are allowed:
I. Income
II. Expenses

We choose income if we receive money in, while we choose expenses if we pay money out.
Income or Expenses is selectable from a dropdown menu.

2.3 Project:

Project is the name of the project for what we want to allocate an income or expense.
The project is selectable from a dropdown menu and projects are setup on the Staging Tables section. 3. Staging Tables

2.4 Type:

Type is the kind of expense.

Names on this field should be kept as mu as possible consistent and is absolutely compulsory the name "Open Balance" to allocate an open balance to a project.

Open balance affects the amount that appear on the Quick Summary section, please check the link below if you need more information about this section. 1.3 Quick Summary 

2.5 Process by:

This field contains the name of the person who enter the transaction.

2.6 Client/Supplier:

This field contains the name of the client or supplier.

This field can be eventually used for further analysis like income/expenses by client/supplier and so forth.

2.7 Description:

The description shows what is a transaction for.

More details are added on the description and more it is easier to remember what was a transaction for.

2.8 Amount:

This field contains the amount of the transaction.

3 Staging Tables:

The Staging Tables section contains the setup for the dashboard.

Several tables are in this section. Some of the tables are for calculation only while others are for proper setup of other sections like Dashboard or Database.

3.1 Product Dynamic Unique List:

This table contains the name of the projects.

Adding a project here, will automatically add it on the dropdown menu of the Database section/Project field, and it will be selectable for income or expenses allocation.

3.2 Active Staff Table:

This table is vital for the Income, Expenses and Profit allocation.

In this section there is the setup of the percentages used to split the Income, Expenses and Profit between the staff.
This is also a very complex table because the setup will affect from when the Income, Expenses and Profit will be split.

The table has the following columns:

3.2.1 Name

3.2.2 Percentage Cost/Profit

3.2.3 Active

3.2.4 From

3.2.5 To

3.2.6 Active Only

3.2.1 Name:

This column contains the name of staff. Active staff will appear on the 10 Year Tables on the dashboard section. 1. Dashboard

3.2.2 Percentage Cost/Profit:

This column contains the percentages used to calculate the Income, Expenses and Profit for each member of staff.

Please note that the Total Active Staff Sharing should be 100% otherwise it means that costs are over or under split.

3.2.3 Active:

This column makes a member of staff Active or Inactive. Only active members are used for the income/cost calculations.

3.2.4 From:

This column is vital because allows to setup from when a member of staff will take part of the income, expenses and profit sharing.

i.e. in the table above none of the members will get sharing of Income or Costs for transactions that happened before the 01/07/2003.

3.2.5 To:

This column is vital because allows to setup up to when a member of staff will take part of the income, expenses and profit sharing.

i.e. in the table above non of the members will get sharing of Income or Costs for transactions that happened after the 31/12/2013.

The From and To columns can be used to setup the length of a contract for a specific member of staff.

3.2.6 Active Only:

This column is a calculate field and based on the column active, it will recreate a list of active members only.

Active members appear on the Current Band Staff and Income/Costs Allocation Based on Contribution Percentage section on the dashboard.

3.3 Project Table:

This table contains the project for what you want to track costs.

If you need to add a project, please do that on the first empty row below the table. The table will expand automatically and the whole dashboard will update accordingly.

3.4 Other Tables:

In this section there are 3 other tables that are used for calculations and further setup.

The first table called Dates calculation for last year income and expenses is used on the calculations on the Income Summary and Expenses Summary on the Dashboard section. 1. Dashboard

The second and the third table are used to calculate the 10 years period and to change automatically the title of the graph on the dashboard section in the next years

To download the dashboard please click here: Revenues and Costs Tracking Dashboard

Please save the Dashboard on your Computer before using it. Once you have saved the file, unzip it using 7z [http://www.7-zip.org/] and install it.

Following on from the above also note that LockXLS add-in will execute the file with Excel.

I hope you enjoy the spread-sheet.

Paolo

[Copyright © 2012, Paolo Succo]