Reference no: EM132387042
Assignment
You work for Rockville Auto Sales and have been asked to aid in the development of a spreadsheet to manage sales and inven¬tory information.
You will start the task with a prior worksheet that contains vehicle information and sales data for 2018. You need to convert the data to a table.
You will manage the large worksheet, prepare the worksheet for printing. sort and filter the table. include calculations, and then format the table.
Prepare the Large Worksheet as a Table
You will freeze the panes so that labels remain onscreen. You also want to convert the data to a table so that you can apply table options.
a. Open the e04c1AutoSales workbook and save it as e04clAutoSales_LastFirst.
b. Freeze the first row on the Fleet Information worksheet.
c. Convert the data to a table. name the table Inventory, and apply the Table Style Medium 19.
d. Remove duplicate records.
Sort and Print the Table
To help the sales agents manage vehicle inventory, you will sort the data. Then you will prepare the large table to print.
a. Sort the table by Make in alphabetical order, add a second level to sort by Year. and a third level to sort by Sticker Price smallest to largest.
b. Repeat the field names on all pages.
C. Change page breaks so each vehicle make is printed im a sip:male page.
d. Remove duplicate records.
Sort and Print the Table
To help the sales agents manage vehicle inventory; you will sort the data. Then you will prepare the large table to print.
a. Sort the table by Make in alphabetical order, add a second level to sort by Year, and a third level to sort by Sticker Price smallest to largest.
b. Repeat the field names on all pages.
c. Change page breaks so each vehicle make is printed on a separate page.
d. Add a footer with your name on the left side, the sheet name code in the center, and the file name code on the right side.
Add Calculated Fields and a Total Row
For tax purposes. the accounting department needs you to calculate the number of vehicles sold. the total value of sticker prices, and actual sales price for vehicles sold in the first quarter.
a. Click the Sales Information worksheet and convert the data to a table. name the table Sales, and apply the Table Style Dark 11.
b. Create a formula with structured references to calculate the percentage of the Sticker Price in column E.
c. Format the range E2:E30 with Percentage Style Number Format.
d. Add a total row to display the Average of % of Sticker Price and Sum of Sticker Price and Sale Price.
e. Adjust the width of columns B:E to show the total values.
Apply Conditional Formatting
You want to help the office manager visualize the differences among the sales. To highlight sales trends, you will apply data bar conditional formatting to the % of Value column.
a. Apply Data Bars conditional formatting to the % of Sticker Price data.
b. Create a new conditional fin-mat that applies yellow fill and hold font to values that sold for less than 60% of the list price.
c. Edit the conditional format you created so that it formats values 70% or less.
Copy and Filter the Data
In order to isolate first quarter sales, you will filter the data. To keep the original data intact for the sales agents. you will copy the table data to a new sheet and use that sheet to display the filtered data.
a. Copy the Sales Information sheet and place the duplicate sheet to the right of the original sheet tab.
b. Rename the duplicate worksheet First Quarter Sales.
c. Rename the table FirstQuarter.
d. hsplay the filter am flys lot the data.
Copy and Filter the Data
In order to isolate first quarter sales, you will filter the data. To keep the original data intact for the sales agents. you will copy the table data to a new sheet and use that sheet to display the filtered data.
a. Copy the Sales Information sheet and place the duplicate sheet to the right of the original sheet tab.
b. Rename the duplicate worksheet First Quarter Sales.
c. Rename the table FirstQuarter.
d. Display the filter arrows for the data.
e. Filter the data to display January. February and March sales.
Finalize the Workbook
You are ready to finalize the workbook by adding a looter to the new worksheet and saving the Anal workbook.
a. Add a looter with your name on the left side, the sheet name code in the center. and the Ilk name code on the right side.
b. Select Landscape orientation for all sheets and set appropriate margins so that the data will print on one page.
c. Save and close the Ilk. Based on your instructor's directions. submit e04c1 AutoSales_Lastrirst.
Attachment:- e04c1AutoSales.rar