In this workshop, we're going to create a supermarket sales report with graphs and charts. However, before we create it, we need to clean the data set and calculate totals, since it only comes with unit values. Let's have a quick look at the data set. In here we have the grocery name being sold, which aisle or section you can find it in the quantity sold sales price, purchase price and other Cost of Goods Sold which may consist of depreciation shelving and freight to the REITs. We have four columns that are now that we need to calculate. The first one is total revenue which will be unit sold multiplied by sales price, total cost of goods sold which will be the purchase price plus other Cost of Goods multiplied by unit sold.
Profit Margin which will be our total revenue minus cost of goods sold divided Cost of Goods Sold again. And finally, we're going to determine if the item we're selling is actually profitable by checking if our profit margin is more than zero percent. It might not be immediately apparent, but there are also duplicate rows and items. We can see it here for mushrooms and ketchup. And there are other items we need to remove to. Under Section we also have a lot of null values which we'll need to find and replace from the supermarket items table, and then we're going to round up the unit sold.
As there appears to be a rounding error. You can't really sell a fraction of asparagus or broccoli. And finally, we're going to do some calculations and formulas for the totals. Let's take a quick look at the report we'll be creating. Our report is going to consist of three charts which compare the revenue to cost of goods sold by Each section, so the blue bars show our revenue, and orange show our total cost of goods sold. So we can see that particularly for cheese, we seem to have a pretty healthy margin here.
The second one will be the profit margins per section in a pie graph. And the third will show how many items per section are actually profitable. So under fresh fruits, we are selling 15 fresh fruits, but only 13 of them seem to be profitable. To understand how to get here, let's have a look at our workflow. Okay, I know that this looks pretty intimidating. I know it looks like a pretty big step up from our first workshop.
But I promise you at the end of it, you're going to be like, Huh, that's actually not that bad. To make this digestible and manageable. We're going to break this workshop into three parts. The first is cleaning the data set as I mentioned before, by Removing duplicates and nulls. And rounding up the unit sold will also be calculating totals. The second is building the charts out for our report.
And the third is creating a pivot table with a grand total. Again, I know this looks like a lot to absorb. But remember, we're putting everything we've learned in the previous chapters into practice. So you can get a good understanding of how to string all these tools together to perform process automation. So let's get started.