Explore

Portfolio and data analysis / Briefing

TLDR:

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.

Tasks:

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.

Jobs

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

⁠