78|ALTERYX – Tax Tool

BYU Student Author: @Alex_Garrett
Reviewers: @TylerBooth, @DylanKing
Estimated Time to Solve: 20 Minutes

We provide the solution to this challenge using:

• Alteryx

Overview
Who’s paying taxes and how much are they paying? You’re exploring the possibility of starting your own CPA firm and in this quest, you figure it’s a good idea to investigate the effective tax rate on average for a CPA firm in the states you are most interested in: Washington, Oregon, California, Arizona, and Utah. You wrote a script to collect tax information on a sample of nearly 1000 companies in these states, and it worked! All the data follows the same format, but your script collected data from many sources, so there may be some redundancy.

Instructions
In practice, effective tax rates are calculated to determine how much tax a company is effectively paying. It is calculated by dividing a company’s tax burden by its taxable income. Calculate the average effective tax rate (using the total tax burden) for each state and order your results from greatest to least by the average rate.

NOTE: Companies may have multiple rows of data associated with the same year. Before performing your calculations, filter out unnecessary rows so that only the most complete row for each company remains. In other words, each company should only have one row of data before you calculate the average effective tax rate for each state.

Data Files

Suggestions and Hints
1. Use the “ABSOLUTE” function to identify the rows with the most complete tax information.
2. Sort the data by company, then by the sum of the absolute values greatest to least.
3. Use a unique tool to filter out excess information.
Check Figures

Effective Tax Rates by State

1. CA = 34.34%
2. OR = 29.76%
3. WA = 22.74%
4. AZ 19.41%
5. UT 17.71%

Solution

Challenge 78 Alteryx.yxmd (12.2 KB)

Thank you for the challenge! It was cool to see how you could use the different companies fiscal year taxes!

3 Likes

Nice challenging one! I got the same results without including fiscal year in unique function.

Challenge78_Workflow.yxmd (12.6 KB)

8 Likes

techhub1.yxmd (11.6 KB)

So fun!

3 Likes

I had a bit of trouble figuring out the data cleansing at the beginning, but the video was super helpful and I learned how to get rid of redundant data!

TaxRate.yxmd (12.1 KB)

2 Likes

That was good practice, especially when learning what “most complete row” would mean and learning how to use the unique tool.

TechHub Nov1.yxmd (36.8 KB)

1 Like

Challenge78Results.csv (141 Bytes)

1 Like

I never thought of using absolute values to get the most complete data, but that’s cool!

TechHubChallenge3.yxmd (11.5 KB)

1 Like

TaxAlteryxPractice.yxmd (12.1 KB)

This one was actually pretty hard for my beginner abilities, but I tried my best and I’m going to continue to work hard.

The video was very helpful! I’ve never used the absolute function before. Great way to learn something new.

2 Likes

It was challenging to find the most complete rows, but using the absolute value of the taxes was a cool way to do it. Other than that, fairly straight forward. Here’s what I did:

Here is my solution!

2 Likes

The beginning of this challenge taught me new ways to clean and organize data. It was fun to see and learn a new aspect of Alteryx. Here is a screenshot of my solution and the related file!

TechHubpractice1.yxmd (11.3 KB)

1 Like

TaxToll.yxmd (12.8 KB)

Here’s what I got. The unique tool is pretty nifty.
TechHubChal78.yxmd (12.5 KB)

Fun challenge! Good practice!

Took me a little bit to figure out how to get only the most complete row for each company but ended up being good practice.

1 Like

78.yxmd (13.0 KB)
My answers were a little different because I maintained negatives, but the final order was still right.

Great challenge!

1 Like