Answered step by step
Verified Expert Solution
Link Copied!

Question

...
1 Approved Answer

I really need help on this excel homework and I have been tossed around from tutor to tutor, no one has been able to help

I really need help on this excel homework and I have been tossed around from tutor to tutor, no one has been able to help me with my homework and I would like to find a tutor who can help me with excel, please. I put the excel instructions below and I have a google docs link to the excel file, I am really struggling and would like some help understanding how to do my homework.

Google excel link - https://docs.google.com/spreadsheets/d/1ehUZYDf3YcF3ylxDVHzcxJHDmD1tXVbYXhqv-GYzTeg/edit?usp=sharing

(Copy and Paste this link on a new tab to take a look at the file)

Step Instructions Point Value
1 Start Excel. Download and open the file named Excel_Ch06_PS1_Automobiles.xlsx. Grader has automatically added your last name to the beginning of the filename. Save the file to the location where you are storing your files. 0
2 Converting plain data sets to Excel Tables can provide easy ways to explore the data. The data on the AutoSample worksheet includes details about vehicles that the company may be interested in buying for resale. On the AutoSample worksheet, insert a table with headers using the data. 8
3 With the table selected, including the headers, create a CarDatabase named range to be used in database functions later. 2
4 Insert six rows above the table to make room to create an advanced filter. 5
5 In order to create an Advanced Filter you must include the column headings to which you will be specifying criteria. Copy the headers in A7:J7 and paste the headings starting in cell A1. 2
6 Using the Advanced Filter, display only the records with an Automatic transmission type, that are a Midsize vehicle size, with an MSRP of <30000. 12
7 Copy the filtered data, including the headings, and paste a copy on the SelectedVehicles worksheet, starting in cell A1. If necessary, adjust the column widths so that all data are visible. 10
8 Clear the filters applied to the AutoSample data. Copy the headings and paste them onto the DatabaseTotals worksheet, starting in cell A1 to begin to setup and create database functions to further analyze the data. If necessary, adjust the column widths so that all data are visible. 6
9 Complete the DatabaseTotals worksheet using the appropriate database functions using the CarDatabase named range, use the MSRP as the field in your calculations, and only calculate vehicles that meet all the following criteria: Vehicle year is newer than 2015 Transmission type is Automatic Highway MPG is greater than 25 20
10 Using the data on the AutoSample worksheet, create a PivotTable on a new worksheet and rename the worksheet, PivotAnalysis. The PivotTable should use the Make field as the Rows and MSRP as the Values. Change the MSRP calculation so that it calculates the Average of MSRP and format the field as Accounting with 2 decimal places. Change Row Labels in cell A3 to Vehicle Make and adjust the column width as necessary so that all data are visible. 17
11 Create a drill-down of the Volkswagen onto a new worksheet and rename the worksheet Volkswagens. Further modify then PivotTable on the PivotAnalysis worksheet. Insert a Year slicer and position it so that its top-left corner in the cell D2. Insert a Vehicle Style slicer and position it to that its top-left corner in cell G2. Use the Year slicer to only show vehicles that are 2010 and newer. 10
12 Create a Clustered Bar PivotChart from the PivotTable. Apply the Style 10 chart style. Apply the Monochromatic, Palette 5 colors to the chart. Delete the Total legend from the right-side of the chart. Edit the chart title to read Average MSRP. Move the chart to its own worksheet with the name AverageMSRPPivotChart. 8
13 Save and close Excel_Ch06_PS1_Automobiles.xlsx. Exit Excel. Submit the file as directed.

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access with AI-Powered 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

Students also viewed these Accounting questions