For this project, you will (individually) be creating a dashboard from the provided template of data.

A dashboard provides a quick summary of a supplied data source. Typically, the summarizations are separate from the original data so that the viewer can quickly see what is important without being bogged down by the original data set. In this project, youll be adding some additional calculations to the data provided, and creating a dashboard of information on another sheet.

In the workbook template for this assignment, you will find two worksheets - Dashboard and Data. The only thing you should do in the Data worksheet is Step #4 below. All other calculations, charts, etc. should appear in the Dashboard worksheet.

In this project, the transactions for the month of December 2016 are listed for 12 companies. These companies are: BLUPLANET, CANDECOR, DAIDO, DIGIGEN, FIBRODYNE, FLUMBO, GADTRON, JIMBIES, OZEAN, REMOTION, TALAE, and WEBIOTIC. There are three different statuses for an order. They can be Ordered, Shipped, or Delivered.

Complete the following:

1. (2 pts) Format the Cost column in the Data worksheet as a monetary value.
2. (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.
3. (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%.
4. (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.
5. (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.
6. (4 pts) Filter out the orders that have been Delivered.
7. (4 pts) Make the text for any status that is Ordered (as opposed to Shipped or Delivered) in Red using Conditional Formatting.
8. (8 pts) Show the following data about the transactions (including processing fees and sales tax):
   1. What was the smallest transaction? Use the MIN() function.
   2. What was the largest transaction? Use the MAX() function.
   3. What was the average transaction? Use the AVERAGE() function.
   4. What is the total of all of the transactions? Use the SUM() function.
9. (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.
10. (6 pts) Create a column chart that shows the data from the previous step.
11. (8 pts) Create a list that shows the three order statuses and how many transactions are at each state. Again, use the COUNTIF() function.
12. (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.
13. (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.
14. (10 pts) Protect the sheets so the only cells that can be changed are the sales tax and processing fee on the first sheet. Do not include a password.

Be sure to follow the guidelines listed in the syllabus. All calculations must be completed in Excel as formulas. Input cells should be clearly styled to differentiate from other cells.

Name your project midterm_lastname.xlsx.