The assigment at hand is a school work that i need help with. I got an dataset of information, it concernes multiple projects in a goverment company. The task is to
take the informatin and turn it in to a general dashboard and one for the project managers. It has to be a "one-pager".
So the use of slicer should be implemented.
Now i tried to do it on my own and understood that i laked the necessary excel skills so im turning to you for help.
The KPIs i would like to have are: Planned value (PV), Earned value analysis (EVA) Cost Variance (CV), Overdu projects/crossed deadlines, Schedule Variance (SV) and precentage of projects completed on time.
Feel free to ask anything. The KPI:s are pretty simple.
This is the assigment :
The assigment ” One-pager”
This tab represents a "one-pager" whose purpose is to give a quick overview of the ongoing investments.
This file is the controllers main tools. It contains all investments and is directly conected to the accounting system, which means that data is always fresh.
The role of an controllers includes determining the annual budget (business plan / VP) for all investment projects. Throughout the year every project is to be followed upp according to this budget. Partly to ensure that projects adheres to the budget but also to ensure that the projects planned in the budget really gets started.
Throughout the year, the investment economist also updates the project managers forecasts on their projects and the status of submission forecast shown in the "investment list".
(See the tab named forecasts)
There are several departments in the company that are interested in the data contained in this file, so the "one-pager" should not only aims to meet the controllers own monitoring needs.
It may be that individual project managers or department managers want to view the data based on their own perspective. By meeting also their need for follow-up and analysis you create a great added value to the company.
The companys investment portfolio is divided in three different budgetframes; "Within the framework", "Extended scope", and "PU". These must be monitored separately
The entire investment framework, in addition to being broken down by the different budget, is also divided into two categories. On the one hand, the companys own estates ( named FSK) and on the other hand, the sporting estates (named IDF). The two categories must not be mixed (ie, projects can not prioritized between these two), but they must be followed up separately. A project is thus combined with both a framework and a category, which gives a total of six possible combinations.
Explanation of ”the investment list"
The first columns that has a green main menu refers to the basic data for projects such as budgetfram, unit and department, division between the IDF and FSK etc.
Status of whether the forecast is filed or not shown in the column labeled "status of the forecast submission" (note that the data is initially only available in a separate tab)
The columns that are of the most interest in addition to the columns with green main menu is the outcome columns, forecast columns, VP columns (ie budget).
Get Started Tips
Be sure to do the one-pager foreseeable and mix graphs and tables. Useful formulas can for example be VLOOKUP, Index / fit, COUNTIFS, SUMIFS, year, today, displacement and much more.
Note that the column for the forecast submission is empty and that the data is in a separate file.
Be sure to do the one-pager "dynamic" so as to provide accurate data, not just for today but for every day of a year. The file's data refers to end of the month of November / December.
Also think about how you can meet several needs at once.
It is OK to remove or add lines and tabs, make your own assumptions etc. It is not corecctnes of the numbers that is important but the way they make possible the analysis and presentation that is in focus!
10 freelancers are bidding on average $22/hour for this job
Hi, I am good at Excel vba. I have seen the details. I would like to discuss the KPI and presentation part in the allocated 4 boxes and a slide bar. Looking forward to discuss in the chat.