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:
- Tracking his revenues and costs by project showing to total profit
- Showing the past 10 years revenues and costs split by year
- 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.
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]
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
This field contains the
name of the person who enter the transaction.
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.
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.
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:
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.
This column makes a
member of staff Active or Inactive. Only active members are used for the
income/cost calculations.
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.
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.
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.
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.
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]
