BYU Student Author: @MitchFrei
Reviewers: @Jack_Thomas
Estimated Time to Solve: 40 Minutes
We provide the solution to this challenge using:
- Tableau
Need a program? Click here.
Overview
As the CFO of an East Coast law firm, Lexington Legal, you are responsible for the firm’s financial health. One important driver of profitability is realization – the revenue that is realized when you charge clients for legal work. Here’s how realization works:
- The firm decides on a standard hourly rate to charge clients for legal work. This standard rate varies based on the level of the professional in the firm. Lexington Legal charges $100/hour for an associate up to $300/hour for a partner.
- The firm’s clients can negotiate a discount to receive a lower rate than the standard rate. For example, a client may negotiate to pay $85/hour for an associate and $275/hour for a partner. This is the actual rate.
- The hours that a professional works multiplied by their standard rate is the standard revenue – the revenue that the firm could have made had they charged the standard rate.
- The hours that a professional works multiplied by their actual rate is the actual revenue – the revenue that the firm actually made, or realized.
- The actual revenue divided by the standard revenue is the realization.
- Professionals also spend time on internal projects instead of client-related projects. Only the client-related hours (billable hours) bring in revenue and standard revenue.
Instructions
Create a Monthly Realization Dashboard in Tableau that tracks the firm’s Realization. Include 2 sheets in your dashboard:
- Monthly Realization
- Create a bar chart with months along the columns and the realization percentage as the bars.
- Filter for 2023.
- Label the bars with the Realization percentage rounded to 1 decimal place
- Yearly Realization
- Create a pie chart showing the realization percentage for 2023. For example, if realization is 90%, then 90% of the pie should colored and labeled with 90.0%, and 10.0% of the pie should be colored and labeled with 10%. For an extra challenge, make this pie chart a donut chart! Yum!
Title your dashboard Monthly Realization Dashboard. Place the bar chart in the center and the pie chart in the top right corner. Include a legend for the colors on the pie chart. One color should represent realization and the other color should represent discounts. Include multiple value dropdown filters for practice and position underneath the pie chart that adjusts both sheets.
Equipped with this Realization Dashboard, you will be able to make smart financial decisions about giving discounts to clients and drive firm profitability!
Data Files
Suggestions and Hints
Calculation Hints
Consider creating the following calculations:
- Billable Hours
- Revenue
- Standard Revenue
- Realization
- Discounts
Make sure to SUM Revenue and SUM Standard Revenue when calculating Realization.
Discounts is simply the inverse of Realization (1 – Realization)
Pie Chart Hints
Since you want to separate Realization and Discounts by color, place Measure Names on color. (Make sure to filter Measure Names to include only Realization and Discounts). Since you want the angle of the pie slice to be determined by the Realization and Discounts percentage, place Measure Values on Angle. Place Measure Values on Label to label the percentages.
Donut Chart Hints
To create a donut chart in Tableau, you need to place a smaller white circle on top of the pie chart so that it looks like the center is taken out. To do this, you will need to create two pie charts and create a dual axis. To create a dual axis, create a dummy variable with a value of 1, place it on the columns shelf twice, and change to MIN. Right click on the pill and create dual axis. You will now see 3 sections in the Marks card – All, MIN(1), MIN(1)(2). Keep the pie chart elements in MIN(1), but get rid of all the pills in MIN(1)(2). Adjust the size to the be smaller than the other pie chart and change the color to white. Now clean up the view by right clicking on the axes and deselect Show Header. Right click on the view and select Format. Get rid of borders and grid lines by selecting those sections at the top of the format options and make sure everything is set to None.
Filter Hints
To make the filter adjust both sheets, place Position and Practice in the filter card in one of the sheets, then right click on the pill and select Apply to Worksheets → Selected Worksheets… → All. Then when you click on either sheet in the dashboard, click on the dropdown in the top right → Filters… → Position and Practice. To change to a multiple values dropdown filter, click on the filter, select the dropdown in the top right, and select Multiple Values Dropdown.
Solution
Challenge62_Solution.tbwx
Solution Video: Challenge 62|TABLEAU – Lexington Donuts