Portfolio and data analysis / Briefing


3 data analysis jobs for supporting a portfolio analysis of a creative agency.
the following excel will be provided.


The briefings

01 Data Analyst Briefing: Identifying High-Performance Projects and Healthy Client Relationships

Objective: Evaluate and identify projects based on two key criteria: highest profit (high-performance projects) and highest billability (projects with healthy client relationships).
Data Source and Metrics:
Data: Utilize Excel data focusing on profit and billability metrics.
Profit Distribution: Assess projects based on their profitability. Use median as a benchmark where:
Above Median: Good Performance
Median: Satisfactory Performance
Below Median: Poor Performance
Billability Distribution: Evaluate client relationships based on billability. The agency's average/median billability acts as a baseline:
Above Baseline: Good Relationship
Baseline: Average Relationship
Below Baseline: Poor Relationship
Evaluation Scales:
Profit Scale: Categorize projects into seven levels ranging from 'terrible' to 'great', with 'terrible' representing the lowest profitability.
Billability Scale: Classify client relationships into seven levels ranging from 'acid' to 'superpower', with 'acid' indicating the lowest billability.
Statistical Analysis: Calculate the statistical distribution of projects and clients. Determine whether using the median or average is more effective for assessing billability.
Visualization: Create a matrix to visually represent the distribution of projects and clients according to the established profit and billability scales.
Goal: To effectively categorize and visualize projects based on profitability and client relationship health, aiding in strategic decision-making and resource allocation.

Desired result

02 Data Analyst Briefing: Project Impact Analysis

Objective: Your goal is to analyze and understand the business impact of various projects.
Data Source and Variables:
Data: Utilize Excel data, specifically focusing on income data and the sum of all incomes.
New Variable - Weight: Define a new variable 'weight' to measure business impact. Weight is calculated as the project income divided by the total agency income. A higher weight indicates significant resource use and potential for high value creation or destruction.
New Variable Power: Determine the 'power' of each project, a metric derived from the product of the profit index and billability index.
Analysis: Evaluate which projects empower the business, which obstruct it, and which are detrimental.
Assessment Assumptions:
Recognize that less profitable projects that lock resources, especially in large volumes, can be detrimental.
Identify projects that are unprofitable yet have high weight, as these are considered catastrophic.
Note that projects with little profit but high billability are undesirable.
Calculations and Visualization:
Create a spectrum visualization showing the relationship between weight and power for each project.
This visualization should aid in understanding the impact and potential of different projects or clients.
Desired Outcome: A clear, insightful visualization that illustrates the business impact of various projects, enabling informed decision-making regarding resource allocation and project prioritization.

Goal: understand if specific functions are responsible for performance
how this makes sense
a high weight project with low profit * high billability is a big challenge, because it needs a fundamental change.
whereas a
high weight with low profit * low billability is not great, but can be fixed


03 Project Details showing cost functions

Take the top 10% projects and show how the various functions perform.
take the bottom 10% projects and and show how the various functions perform.
apply the impact analysis lens
apply the health & performance lens

Want to print your doc?
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
) instead.