Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

This is supposed to be on a template in which there are two sheets : Dashboard and Data. On the Dashboard sheet, create labels and

This is supposed to be on a template in which there are two sheets : Dashboard and Data.

  1. 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%.
  2. 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.
  3. 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. Filter out the orders that have been Delivered.
  5. Make the text for any status that is Ordered (as opposed to Shipped or Delivered) in Red using Conditional Formatting.
  6. 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.
  7. 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.
  8. Create a column chart that shows the data from the previous step.
  9. Create a list that shows the three order statuses and how many transactions are at each state. Again, use the COUNTIF() function.
  10. Create a pie chart that shows the data from the previous step. Remove the legend and instead label each piece on the chart itself.
  11. 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.
  12. 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.

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Students also viewed these Accounting questions