47|ALTERYX – Variance Analysis Paralysis

[MitchFrei]

BYU Student Author: @MitchFrei
Reviewers: @Mark, @Andrew
Estimated Time to Solve: 45 minutes

We provide the solution to this challenge using:

• Alteryx

Need a program? Click here.

Overview
You are a financial analyst for Pentathon Solutions, a national accounting advisory firm. The
firm has 15 locations across the U.S. grouped into four regions: North, South, East, and West.
Each region provides five practices to clients: Tax, Audit, M&A, Advisory, and Technology
Solutions. The CFO has tasked you with providing a variance report to compare actual revenue
against planned revenue for Fiscal Year 2023 (April 01, 2022 – March 31, 2023).

Before the start of each fiscal year, Pentathon Solutions creates a revenue plan, which is the
number of sales it expects or aims to bring in to the firm that year. At the end of the year, the
plan is compared against actual revenue to track firm performance relative to its plan, and also
analyze which regions and practices performed better than others. The revenue plan and
revenue actuals are stored in separate data files.

The CFO would like to know how the firm performed overall in Fiscal Year 2023, which
regions were responsible for the results, and which practices were responsible for the results in
the worst-performing region. Therefore, the CFO has asked for three separate reports:

Overall Actual v Plan

This report should have 1 row and 4 columns:

1. Actual
2. Plan
3. Actual v Plan (calculated as Actual minus Plan)
4. Actual v Plan % (calculated as Actual v Plan divided by Plan –
   format as a percent rounded to 2 decimal points. For example,
   .923587 = 92.36%)

Actual v Plan by Region

This report should have 4 rows (one for each region) and 5 columns. Sort by Actual v Plan % Ascending:

1. Region
2. Actual
3. Plan
4. Actual v Plan (calculated as Actual minus Plan)
5. Actual v Plan % (calculated as Actual v Plan divided by Plan –
   format as a percent rounded to 2 decimal points. For example,
   .923587 = 92.36%)

Actual v Plan by Practice within the worst-performing Region

This report should have 5 rows (one for each practice) and 6 columns. Sort by Actual v Plan % Ascending:

1. Region
2. Practice
3. Actual
4. Plan
5. Actual v Plan (calculated as Actual minus Plan)
6. Actual v Plan % (calculated as Actual v Plan divided by Plan –
   format as a percent rounded to 2 decimal points. For example,
   .923587 = 92.36%)

Instructions
Produce these reports in Alteryx using Pentathon Solution’s Revenue_Plan and
Revenue_Actual files. Consider the tips under ‘Suggestions and Hints’.

Data Files

• Challenge47_Revenue_Actual.xlsx
• Challenge47_Revenue_Plan.xlsx
• Challenge47_Revenue_Actual_Data_Dictionary.docx
• Challenge47_Revenue_Plan_Data_Dictionary.docx

Suggestions and Hints

• Revenue_Plan contains data for Fiscal Year 2023 only, whereas Revenue_Actual
  contains data since 2020. You will need to filter this data for Fiscal Year 2023 (April
  01 2022 – March 31 2023)
• The Revenue Plan is made on a monthly basis and Revenue Actual is made on a daily
  basis. Hence, you will see only month-end dates in Revenue Plan and any date in
  Revenue Actual
• For each of the 3 reports, end with a Table tool and a Browse tool
• It will be easiest to summarize before joining
• The Append Fields tool can be used to join datasets that have no relation (no linking field)

Solution

Challenge47_Solution.yxmd
Solution Video: Challenge 47|ALTERYX – Variance Analysis Paralysis

[Mark]

Here is an image of my workflow:

Attached is my solution file. Note that I did not include table tools as the Challenger directed.
Variance Analysis Challenge.yxmd (31.7 KB)

[Andrew]

This was a great challenge!!. I spent time trying to manipulate the data before joining, but couldn’t get it to work. Once I changed my strategy, I was able to get it to work.

Here is my workflow:
Monthly_Challenge_Solution.yxzp (490.4 KB)

[Jonathan_Weston]

Here’s my solution. I misunderstood the instructions for formatting and thought I needed to add a ‘%’ symbol. This made some of the sorting a little tricky, but it turned out great.

[TylerBooth]

This was a great challenge! I like the way that you grouped the different reports in a way that allows you to turn them on and off. I’ll have to learn how to do that. I made my initial connections wireless to avoid the crossing lines. I love a clean data flow!

[DylanKing]

I really enjoyed this challenge! It was great hands on experience to understand the importance of variance which is what we just finished learning in my managerial accounting class. I got tripped up on the last one simply because I kept forgetting to join on both region and practice. This was a really good challenge to see firsthand how Alteryx can allow you to go deeper into data, understand relationships, and answer questions. Thanks for putting this together! Here was the workflow that I created:

[Connor_Richards]

This was a fun activity to revisit Alteryx with. By the time I got to the end, I started realizing ways in which I could have simplified my workflow. It is great being able to practice and work on my workflow optimization, and it is for sure something I could improve. Thanks for the challenge!

[Brett_Lowe]

Great challenge! I’d never used the Append Fields tool before — just settled for joining by record position, but it appears to have worked just as well. Here’s my workflow:

[Brett_Lowe]

This solution was cool to look at. How did you manage to “wirelessly” link your data collection with the rest of your workflows?

[TylerBooth]

If you right click on the wired connection, you have the option to make the connection “wireless.” I find it helpful to keep the data flow a bit more clean at times. When you click on the tool, it will temporarily show the wire so you don’t forget which output the data is coming from!

[Fisher_Riggs]

I had never used the table or append fields functions before! Very straightforward outside of the new tools. What a cool challenge!

[Caleb_McKay]

I am still new to alteryx but I made my way through this problem! I had difficulty rounding and adding the % but eventually got there!

[Kaleb_Jensen]

TechHubChallenge47.yxmd (49.5 KB)
Here’s my workflow! I did it a little different and maybe a little more complicated but it stuff worked!

[sally_kang]

47.yxmd (27.9 KB)

[Summer_Hanamaikai]

techhub1.yxmd (32.4 KB)

[Jack_McFarland]

Challenge47.yxmd (13.5 KB)

[Yoomin_Jang]

Time to Complete: 40 mins
It couldn’t get any messier but I feel so proud of it!

[Emmanuel_Mills_Obboh]

Time to Complete: 47 Minutes
Rating: Difficult
Solution: