EssayproSolutions

Project Individually Creating Dashboard Provided Template Data Dashboard Provides Quick Su Q43786271

For this project, you will (individually) be creating adashboard 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 dataso that the viewer can quickly see what is important without beingbogged down by the original data set. In this project, you’ll beadding some additional calculations to the data provided, andcreating a dashboard of information on another sheet.

In the workbook template for this assignment, you will find twoworksheets – Dashboard and Data. The only thingyou should do in the Data worksheet is Step #4 below. Allother calculations, charts, etc. should appear in theDashboard worksheet.

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

Complete the following:

1. (2 pts) Format the Cost column in the Dataworksheet as a monetary value.
2. (6 pts) Format the Date column in the Dataworksheet as a date in the format DD-MON-YYYY. For example, August14th, 2016 would be 14-Aug-2016.
3. (4 pts) On the Dashboard sheet, create labelsand input cells for the processing fee for each transaction and thepercentage of sales tax. Assume the processing fee is $24 and salestax is 6%.
4. (14 pts) Add a column to the Data worksheetthat adds in the processing fee and sales tax. Processing fees aretaxable. To apply sales tax, multiply the total by 1 + % sales tax.For example, if sales tax is 6%, multiply by 106% (1 + 6%). Youmust perform the calculations by referencing the processing fee andsales tax on the first sheet, and more specifically, absolute cellreferences.
5. (4 pts) Sort the data by company, and then bydate ordered. If there are multiple orders by the same company onthe same date, the order does not matter.
6. (4 pts) Filter out the orders that have beenDelivered.
7. (4 pts) Make the text for any status that isOrdered (as opposed to Shipped or Delivered) in Red usingConditional Formatting.
8. (8 pts) Show the following data about thetransactions (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 andhow many transactions each one made. Use the COUNTIF() function.The COUNTIF function takes two arguments. The first is the rangebeing checked, while the second is the value you are comparingagainst. You should be able to create this list with the names ofthe 12 companies in one column, and a second column that containsthe formula with this function that is replicated throughout eachof the 12 rows.
10. (6 pts) Create a column chart that shows thedata from the previous step.
11. (8 pts) Create a list that shows the threeorder statuses and how many transactions are at each state. Again,use the COUNTIF() function.
12. (6 pts) Create a pie chart that shows the datafrom the previous step. Remove the legend and instead label eachpiece on the chart itself.
13. (10 pts) Create data validation rules torequire that the sales tax be a percentage between 0% and 8% andthe processing fee be any amount of money.
14. (10 pts) Protect the sheets so the only cellsthat can be changed are the sales tax and processing fee on thefirst sheet. Do not include a password.

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

Name your project midterm_lastname.xlsx.

Expert Answer

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