2020: Week 34 - Solution
This solution is by Tom Prowse and you can download our full solution here.
This week's challenge built upon the work that we did in Week 33 and used that solution as our starting point. For this week's task we will be looking into profitability analysis and suggestions on how we can improve this for the Chin & Beard Suds Co execs.
Step 1 - Calculate Avg Units Sold
The input for this week's challenge is our solution to last week, therefore we can start by opening up that workflow.
From here, our first task is to calculate the Avg Units Sold for each Scent. To do this we can use an aggregation tool, to group by Scent and then Avg the Units Sold field:
We can then tidy this field up a bit by rounding up to the nearest whole number, rounding to the nearest 10, and multiplying by 7. We do this by using this calculation:
Units Ordered
ROUND(CEILING([Avg Units Sold]),-1)*7
Step 2 - Calculate Waste
Now we have created the units ordered field, we need to join this back to our workflow from last week, and use some of the calculations in order to calculate how much waste there has been.
First, we need to include the Price field from last week's flow. You may have removed this field last week, so take a look at the 'Units Sold' clean step, and using the Change Pane make sure you haven't removed Price.
This may be located somewhere else, depending on how you completed the challenge last week!
Next, we want to include the price when aggregating and calculating the Weekly Totals.
We don't want to aggregate the price in any way, so we can just drop the Price field into the Grouping.
Now, we've include Price we can join back onto our Units Ordered workflow. We are going to be joining from the Weekly Totals aggregation and joining on Scent:
We can now start to calculate the Waste and tidying a couple of the fields. We can do this with the following calculations:
Waste
[Units Ordered]-[Units Sold]
Under Stocked
IF [Waste]<0 THEN ABS([Waste]) ELSE 0 END
Weekly Sales
ROUND([Weekly Sales]-([Under Stocked]*[Price]),2)
Units Sold
[Units Sold]-[Under Stocked]
Waste
IF [Waste] <0
THEN 0
ELSE [Waste]
END
After these calculations we have calculated to waste and then adjusted it where necessary. Our table should now look like this:
Step 3 - Calculate Profits
The final part of this week's challenge is to calculate the profits and the difference between them in the two different scenarios.
First we need to calculate the cost of the waste, which we can do now we've calculated the Waste.
Waste Cost
ROUND([Cost]*[Waste],2)
Then from here we can calculate how much profit we have by subtracting the waste from the sales:
Profit
ROUND([Weekly Sales]-[Waste Cost],2)
Once we have done this we can remove some fields so our table now looks like this:
Currently, our table is split out by Scent and Week, therefore to find the total profit we need to aggregate this up. We use an aggregation tool, by grouping by Scent and finding the Sum of Profit:
Now we have the 'new' profit from the scenario, it's time to compare these against our profits from last week. To do this, we can join the two tables together from our current workflow and after the Profit aggregation from last week:
We us Scent as the join condition:
Now we have both of our profits for each scent within the same table, we can use the following calculations to calculate the difference (we've split them out for clarity):
Total Profit
ROUND([Total Profit],2)
New Profit
ROUND([New Profit],2)
Difference
ROUND([New Profit]-[Total Profit],2)
After these calculations we are left with our desired table which is ready for the output:
