78|ALTERYX – Tax Tool

Alex_Garrett · May 26, 2023, 5:32pm

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

We provide the solution to this challenge using:

Alteryx

Need a program? Click here.

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

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

Check Figures

Effective Tax Rates by State

CA = 34.34%
OR = 29.76%
WA = 22.74%
AZ 19.41%
UT 17.71%

Solution

Solution Image

Challenge78_Solution.yxzp
Solution Video: Challenge 78|ALTERYX – Tax Tool

Alexander_Smith · October 31, 2023, 10:31pm

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!

Dakin_Rust · November 1, 2023, 7:57pm

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

Challenge78_Workflow.yxmd (12.6 KB)

Elizabeth_M_Wheatley · November 1, 2023, 11:10pm

techhub1.yxmd (11.6 KB)

So fun!

Spencer_Fleming · November 1, 2023, 11:40pm

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)

Emily_Johns · November 2, 2023, 12:26am

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)

Lauren_Garner · November 2, 2023, 2:04am

Challenge78Results.csv (141 Bytes)

Ethan_Hunt · November 2, 2023, 2:07am

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

TechHubChallenge3.yxmd (11.5 KB)

Zach_Moffat · November 2, 2023, 3:23am

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.

Ella_Wilson · November 2, 2023, 3:36am

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

Wyatt_Fronk · November 2, 2023, 4:25pm

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:

Warren_Goodin · November 2, 2023, 5:07pm

Here is my solution!

James_Clark · November 2, 2023, 5:23pm

Alyssa_Oldham · November 2, 2023, 5:29pm

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!