BYU Student Author: @James_Gerstner
Reviewers: @Sterling_Lane, @Andrew_Wilson
Estimated Time to Solve: 20 Minutes
We provide the solution to this challenge using:
- Alteryx
Need a program? Click here.
Overview
Hello, Dean! You have recently been named dean of the Big Moves School of Accountancy at Up The Ladder University (UTL). As part of your new appointment, you’ve decided to help students file their taxes this year. The Lifetime Learning Credit (LLC) allows students to yearly deduct 20% of up to $10,000 of qualified educational expenses (including qualifying book and tuition costs). You’ve gathered financial data for each student, grouped by class, and now want to calculate the tax credit for each Big Moves student using the LLC.
Using Alteryx, complete the following:
- Combine the classes’ data together into one dataset.
- Clean the data.
- Create columns for total cost and LLC.
- Output the updated data to a .csv file.
- Additionally, prepare data to show the president of UTL the average tax credit for each class.
Instructions
- Download the data file.
- Create a new Alteryx workflow and import each sheet from the data file to the flow.
- Combine the data from the sheets together and clean the data.
- Calculate total expense costs and the tax credit for each student.
- Export the student data to a .csv file.
- Using the updated data, calculate the average credit for each class (the last two digits of a Student_ID designate graduating class).
- Sort the average credit information by year (ascending) and export to a .csv file.
a. Consider rounding the averages to two decimal places to make them more readable (see “Suggestions and Hints” below).
Data Files
Suggestions and Hints
- You can use both Select and Data Cleansing tools to clean your data by converting the appropriate “string” data fields to the “double” type and replacing null values with zeros.
- Be careful not to convert the Student_ID field away from a string if you plan on using information in that column to separate students by class later (for the UTL president).
- To make the LLC values more intuitive, use the ROUND function in a Formula tool to round the calculations to two decimal places. This looks like: Round([LLC], .01).
Solution
