Using the dataset, Concession Data complete the following tasks in excel. Here

Using the dataset, Concession Data complete the following tasks in excel.
Here is a brief overview of the two tabs in the excel file.
Sales Transactions – each line on this tab represents a concession sale, a very simple sale, of one item and one single quantity, but a sale nonetheless. It contains the date, the item sold, the category and what the item was sold for.
Item Look up Table – this tab contains your look up values that you will need for your calculations. It includes your cost for each of the items, meaning what did it really cost to make the popcorn or the hot dog (not what you sold it for) and how many calories the popcorn is for example.
For your assignment, you will need to follow the below transformation steps and then complete a simple report in Word/PDF summarizing what you did.
Start with the Sales Transactions Tab, it contains some messy data that needs cleaning.Group the items and categories and identify if there are misspellings or errors. This would most easily be done by creating a pivot table. (see Pivot Tables for help.)
Hint: Since these are concession sales, I would expect sale prices to be the same, so you can make some generalizations if you see anomalies or blanks there.
Check for missing values or blanks
Are there duplicates of the groups and are they valid?
Identify data with the wrong data type.
Are any of the dates incorrect? All transactions should have taken place in January 2022
Now that you have cleaned the transactional data, you are ready to use the look up table to do a couple calculations. (See Vlookup: When and How to use it for help.) Profit – calculate the actual profit based on the cost of goods sold number. To calculate profit, you will want to take the amount you sold the item for minus the cost of goods sold. (Ex: Sold item for $5, cost of goods sold, $2.50, Profit = $2.50 or 100%)Calculate the profit per line in dollars
Calculate the profit per day in dollars
Calculate the profit in total (all sales) in dollars
Identify which items did were most profitable, which were least profitable (where you didn’t make any money!), and which (if any) broke even in dollars
Total Calories – using the look up table, do another vlookup to identify the calorie amount per transaction lineCalculate the total calories per line (Hint: should just match the calories from the look up table, this isn’t a trick)
Calculate the total calories per day
Calculate the total calories in total (all sales)
Identify which days had the most caloric content sold and which had the least.
Submission InstructionsSubmit a Word/PDF file along with your excel file that shows your work. The Word/PDF file should contain the following items and a brief explanation of the steps taken to cleanse the transactional data. If you have any open questions, please include those as well.Profit – calculate the actual profit based on the cost of goods sold number. To calculate profit, you will want to take the amount you sold the item for minus the cost of goods sold. Calculate the profit per line in dollars
Calculate the profit per day in dollars
Calculate the profit in total (all sales) in dollars
Identify which items did were most profitable, which were least profitable (where you didn’t make any money!), and which (if any) broke even in dollars
Total Calories – using the look up table, do another vlookup to identify the calorie amount per transaction lineCalculate the total calories per line (Hint: should just match the calories from the look up table, this isn’t a trick)
Calculate the total calories per day
Calculate the total calories in total (all sales)
Identify which days had the most caloric content sold and which had the least.