Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

INSTRUCTIONS: 1. Add a sheet to the worksheet and rename sheet Pivot table 1. On sheet which is now called Pivot table 1, go to

INSTRUCTIONS: 1. Add a sheet to the worksheet and rename sheet Pivot table 1. On sheet which is now called Pivot table 1, go to cell A1 then "insert" "pivot table" using the data range provided on the data sheet (including the heading lines). Drag "account #" to the row labels and "amount" to the Values. You should now see the dollar amounts for sales revenue, direct materials, direct labor, variable overhead, and Grand Total (contribution margin). If you don't see the dollar amounts click the arrow next to amounts in the Values box and choose "Sum of amount". Next, answer the following: How much are total sales revenues? How much are total direct material costs? How much are total direct labor costs? How much are total overhead costs? How much is total contribution margin?

2. Add a sheet and rename "Pivot table 2". On the sheet which is now called Pivot table 2 , insert a pivot table in cell A1 using the data range provided on the data sheet (including the heading lines). Drag "sales person" to the Row Labels box, drag "account #" to the Column Labels box, and drag "amount" to the Values box. Answer the following: Which sales person generated the highest sales revenue? Which sales person generated the highest contribution margin? Which sales person generated the lowest sales revenue? Which sales person generated the lowest contribution margin?

3. Add a sheet and rename it "Pivot table 3". On the sheet which is now called Pivot table 3 insert a pivot table at cell A1 using the data range provided on the data sheet (including the heading lines). Drag "Job #" to the Row Labels box and "amount" to the Values box. On the table select the data in the sum of amounts column and sort from highest to lowest. Answer the following: Which job number generated the highest contribution margin? Which job number generated the lowest contribution margin?

4. Add a sheet and rename "Pivot table 4". On the sheet which is now called Pivot table 4 insert a pivot table at cell A1 using the data range provided on the data sheet (including the heading lines). Drag "customer" to the Row Labels box and "amount" to the Values box. On the table select the data in the sum of amounts column and sort from highest to lowest. Which customer generated the highest contribution margin? Which customer generated the lowest contribution margin? 5. Rename sheet 4 "Pivot table 5". On the sheet which is now called Pivot table

5 insert a pivot table at cell A1 using the data range provided on the data sheet (including the heading lines). Drag "location" to the Row Labels box and "amount" to the Values box. Which location generated the highest contribution margin? Which location generated the lowest contribution margin? Submit your Excel worksheet and answers to the above.

Pls anyone who's good at Excel pls

the due is coming soon right now

since I cannot upload the excel file here,

can you pls provide me an email here?

Ill thumb up for this pls !!

I am not unable to upload an excel file on here image text in transcribed

L A 1 Job # 2 1008 3 1008 4 1008 5 1008 6 1011 7 1011 8 1011 9 1011 10 1020 11 1020 B CUSTOMER Marut Marut Marut Marut Mihal Mihal Mihal Mihal Lago Iann D E F G H J K SALES PERSON ACCOUNT # LOCATION AMOUNT K.Leeds 4000 Sales Revenue AZ 800 K.Leeds 5020 Direct Materials AZ -2 K.Leeds 5040 Direct Labor AZ -64 K.Leeds 5050 Variable Overhea AZ -400 K.Leeds 4000 Sales Revenue NJ 700 K.Leeds 5020 Direct Materials NJ -2 K.Leeds 5040 Direct Labor NJ -56 K.Leeds 5050 Variable Overhea NJ -350 C. Mayer 4000 Sales Revenue CO 1000 5020 Direct Materials co Ir Maver -70 L A 1 Job # 2 1008 3 1008 4 1008 5 1008 6 1011 7 1011 8 1011 9 1011 10 1020 11 1020 B CUSTOMER Marut Marut Marut Marut Mihal Mihal Mihal Mihal Lago Iann D E F G H J K SALES PERSON ACCOUNT # LOCATION AMOUNT K.Leeds 4000 Sales Revenue AZ 800 K.Leeds 5020 Direct Materials AZ -2 K.Leeds 5040 Direct Labor AZ -64 K.Leeds 5050 Variable Overhea AZ -400 K.Leeds 4000 Sales Revenue NJ 700 K.Leeds 5020 Direct Materials NJ -2 K.Leeds 5040 Direct Labor NJ -56 K.Leeds 5050 Variable Overhea NJ -350 C. Mayer 4000 Sales Revenue CO 1000 5020 Direct Materials co Ir Maver -70

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

Fundamentals Of Entrepreneurial Finance

Authors: Marco Da Rin, Thomas Hellmann

8th Edition

0199744750, 9780199744756

More Books

Students also viewed these Accounting questions

Question

List the major prohibitions of the Canadian Human Rights Act .

Answered: 1 week ago