53|ALTERYX&TABLEAU – Making an Income Statement Visual

[Hyrum]

BYU Student Author: @Hyrum
Reviewers: @Andrew, @Donovon
Estimated Time to Solve: 60 Minutes

We provide the solution to this challenge using:

• Alteryx
• Tableau

Need a program? Click here.

Overview
You have been given the wonderful opportunity to work as an intern for a determined and hardworking lady who runs a jewelry shop called “Shine Bright Like a Diamond”. She has entrusted you with the task of creating a visually appealing income statement that she can use to track her business’s progress. You have eagerly taken on this challenge and are excited to utilize your skills to help her achieve her goals.

Yesterday, she handed you a pile of receipts from all of her transactions this month and asked you to put them all into Excel by hand. Although it was a tedious process, you worked diligently to organize the data and ensure its accuracy. To your delight, she was impressed with your work and even challenged you to take it a step further. She asked you to use Alteryx to clean the data and then Tableau to “make it pretty”. She informs you that anything marked inventory is cost of goods sold, and “Repairs & Maintenance” is actually just “Maintenance”. You are thrilled at the challenge and get to work!

Instructions
You are provided with an excel sheet that has all the transactions from the business. Use Alteryx to organize each expense into categories, making the expenses negative while keeping the revenue positive. You’ll want it to look something like this:

Then output an income statement that looks like the one below:

Then put it into tableau, and make a waterfall chart to make a visual income statement. Have it look something like this:

Data Files

• Challenge53_Data.xlsx
• Challenge53_Data_Dictionary.docx

Suggestions and Hints

• Use RegEx in Alteryx to get the number and category out. Here is a helpful site to help!
• Export two different datasets from Alteryx, one for tableau and one for an income statement.
• When making the income statement, use a running total to calculate the totals for the total row and then remove the extra rows in between.
• Here is a helpful site to learn how to make waterfall charts.

Solution

Challenge53_Solution.yxmd
Challenge53_Solution.twbx
Solution Video: Challenge 53|ALTERYX&TABLEAU – Making an Income Statement Visual

[Marco]

I learned a few new things thanks to you. I’m also proud of myself for figuring this one out and I was able to build mine a bit differently than yours (Except the chart).

Monthly Challenge M7 Solution.yxmd (18.7 KB)

[MitchFrei]

My RegEx code was a little simpler than yours:

To get the category:
((.*))

To get the dollar amount:
$(.+?)\s

It was fun refreshing my RegEx skills and learning how to do a waterfall chart in Tableau!

[Spencer]

I had never used the “running total” tool until today. Thanks for showing me that! Here is my solution:

M7_Solution.yxmd (23.0 KB)

[zullo13]

nice work