Create a list that shows the three order statuses and how many transactions are at each state
- (2 pts) Format the Cost column in the Data worksheet as a monetary value.
- (6 pts) Format the Date column in the Data worksheet as a date in the format DD-MON-YYYY. For example, August 14th, 2016 would be 14-Aug-2016.
- (4 pts) On the Dashboard sheet, create labels and input cells for the processing fee for each transaction and the percentage of sales tax. Assume the processing fee is $24 and sales tax is 6%.
- (14 pts) Add a column to the Data worksheet that adds in the processing fee and sales tax. Processing fees are taxable. To apply sales tax, multiply the total by 1 + % sales tax. For example, if sales tax is 6%, multiply by 106% (1 + 6%). You must perform the calculations by referencing the processing fee and sales tax on the first sheet, and more specifically, absolute cell references.
- (4 pts) Sort the data by company, and then by date ordered. If there are multiple orders by the same company on the same date, the order does not matter.
- (4 pts) Filter out the orders that have been Delivered.
- (4 pts) Make the text for any status that is Ordered (as opposed to Shipped or Delivered) in Red using Conditional Formatting.
- (8 pts) Show the following data about the transactions (including processing fees and sales tax):
a. What was the smallest transaction? Use the MIN() function.
b. What was the largest transaction? Use the MAX() function.
c. What was the average transaction? Use the AVERAGE() function.
d. What is the total of all of the transactions? Use the SUM() function.
- (14 pts) Create a list of the 12 companies and how many transactions each one made. Use the COUNTIF() function. The COUNTIF function takes two arguments. The first is the range being checked, while the second is the value you are comparing against. You should be able to create this list with the names of the 12 companies in one column, and a second column that contains the formula with this function that is replicated throughout each of the 12 rows.
- (6 pts) Create a column chart that shows the data from the previous step.
- (8 pts) Create a list that shows the three order statuses and how many transactions are at each state. Again, use the COUNTIF() function.
- (6 pts) Create a pie chart that shows the data from the previous step. Remove the legend and instead label each piece on the chart itself.
- (10 pts) Create data validation rules to require that the sales tax be a percentage between 0% and 8% and the processing fee be any amount of money.
(10 pts) Protect the sheets so the only cells that can be changed are the sales tax and processing fee on the first sheet.