We provide the solution to this challenge using:
Need a program? Click here.
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!
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:
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.