Overview
You are the new owner of a used car lot. The previous management was buying models from any and every manufacturer you can think of: Audi, BMW, Saturn, Volvo, etc. As the new owner, you hope to shift your focus to acquiring used cars from manufacturers whose models have the least amount of average depreciation on their car models. You imagine that doing so will prevent you from taking big losses by holding onto cars for long periods of time.
The data provided gives you access to information on manufacturers, models, sales in thousands of units, the resale price, the price in thousands of dollars, and more from 2008-2012. With the data provided, use Alteryx to find the top 10 manufacturers with the lowest average depreciation across their various models. Follow the instructions below.
Instructions
Calculate Depreciation for each specific model. Convert the number from thousands to regular units (i.e. 5.14 in thousands to 5140)
Remove any rows of data that have a null price or resale value.
Average the depreciation for each model across the manufacturers. Round to two decimal places.
Remove any rows of data for manufacturers who did not have at least 3 models to prevent outliers.
Sort by average depreciation in ascending order. The output should only include the manufacturers and their average depreciation. Limit the output to the ten manufacturers with the least average depreciation
That one threw me for a bit. I used the wrong field to start, the “Sales_in_thousands_of_units” and not the “Price_in_thousands” field. I should have read the data dictionary more closely!
Also, it was easier once I realized that each car model was only listed once, it wasn’t transaction data. After that, much easier. My solution is below.
Great Alteryx challenge! I forgot to change the depreciation that we calculated from a string to a number, so I spun my wheels for a while trying to get the average. I also didn’t know about the fixed decimal data type, but that comes in handy for this one. Overall, solid for brushing up on formulas, summarizing, filtering, and some of the other basic Alteryx tools. This was my solution:
Alteryx is so forgiving. I used Sales instead of Price for my initial depreciation formula, but once I fixed that, everything else down the chain flowed so smoothly. I also appreciate the ability to pull other data flows off of different points; I worried that my summarize functions would be off, so I split off the count from the average to double-check my work. Thanks for the challenge, Dylan!
Cool problem! I probably didn’t take the most efficient approach in regards to filtering out manufacturers without at least 3 models but it worked out anyways.
Same. I ended up going a bit overboard with what I did. Also, I need to figure out how to rewrite the descriptions below each tool like how you have it presented. I feel like that makes it much more understandable and aesthetically pleasing.
This was a cool challenge, I tried a couple of different ways to count the number of models for each manufacturer, but couldn’t figure it out. The solution showed me a cool way of doing it thinking outside the box. I didn’t even know the CountDistinct was an option for the summarize tool until now
This one was a little tricky with the double filter. This problem was good at reminding me to take a step back and think about what data I am trying to find. Alteryx has shown me how to break things into simple steps.
