Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Excel Tables If you can provide directions, pics, formula I'd appreciate it. I have no idea where to begin. The Sales Orders worksheet shows orders.

Excel Tables If you can provide directions, pics, formula I'd appreciate it. I have no idea where to begin.

image text in transcribed

image text in transcribed

image text in transcribed

The Sales Orders worksheet shows orders. Some research needs to be done to locate a group of information.

A. Create a table starting at J2 to look up sales tax. Central sales tax is 7%, East is 8%, West is 9%, and South is 5%. J1 put in text Tax Table, display the tax rates in K column as %

B. Insert a column before column H and call it Sales Tax Table

C. In Sales Tax column do VLOOKUP for the sales tax rate using values from column B. Set VLOOKUP as exact match. Define the range for the vlookup as absolute Page 2 of 2 reference.

D. Update the calculations in column H to include sales tax as part of the calculation

E. Convert the Orders dataset into an Excel Table (remember you only need to select one cell to create excel table)

F. Then, locate the records of the dataset that comply with all the following characteristics: 1. Region is East or West. 2. Rep is Parent.

G. Format the Cost and Total to be currency ($)

H. Copy total sales values column H for rep Parent in the East and West region and paste values into column P. Add a sum at the bottom of the values in column P to show total sales for Parent in the East and West Regions

I. Select columns A thru H and create a named range called Sales

J. Add a Total row and use the properties of the Total Row to calculate:

1. the sum of Total (column H).

2. the sum of units (column E)

K. Apply the Olive Green, Table Style Medium 11 to the table

Pivot Table 1 :

A. Use the SalesOrders worksheet and create a Pivot Table on new worksheet with a filter on Region, row of Rep, column of Item and sum of Units, Sum of Total

B. Filter the information so that only East region is displayed.

C. Format total to display in currency format

D. Which Rep has the most revenue? Answer in cell M1 of this sheet.

E. Rename the PivotTable's worksheet: SalesPivot.

Thats everything from the assignment I appreciate whatever help you can provide. Thank you.

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

Recommended Textbook for

Auditing and Assurance Services A Systematic Approach

Authors: William Messier Jr, Steven Glover, Douglas Prawitt

10th edition

77732502, 978-0077732509

More Books

Students also viewed these Accounting questions