Hello ,can you help me with this 2 assignments. They are similar to the ones you did in the past .

Office 2013 - myitlab:grader - Instructions Exploring - Excel Chapter 4: Assessment Project 1 Credit Card Expenses Project Description: You started recording every credit card transaction so that you can analyze your monthly expenses. You used an Excel worksheet to track dates, places, categories, and amounts. Because you are a consultant who travels periodically, you also have business expenses. You included a column to indicate the business-related transactions. Now you need to convert the data to a table, sort and filter the data to analyze it, and apply conditional formatting to apply a visual effect for further analysis. Instructions: For the purpose of grading the project you are required to perform the following tasks: Step Points Possible Instructions 1 Start Excel. Open the downloaded Excel file named exploring_e04_grader_a1.xlsx. 2 In the Dining Out worksheet, convert the data to a table. Apply Table Style Light 14. 3 Remove duplicate rows in the Dining Out worksheet. 4 Sort the table by Description in alphabetical order, then by Store in alphabetical order, and then by Amount from the smallest to the largest. 10 5 Filter the records using the check box filters to show personal (i.e., non-business) lunch and dinner expenses. 10 6 Display the Total Row. In cell D48, select the function to total the Amount values. In cell E48, do not show a total. 10 7 In the June Expenses worksheet, apply the conditional formatting style that displays a Green Data Bar Gradient Fill in the range D2:D47. 8 Create a conditional formatting rule with these specifications to the range A2:C47: Formula to determine which cells to format Formula: =(AND(argument1,argument2)) where argument1 compares $D2 to see if it is greater than or equal to 100 and argument 2 compares $E2 to see if the text is "Yes". Light Green (Standard) fill color Green border color Outline preset border style Updated: 04/04/2013 1 0 10 5 5 10 E_CH04_EXPV1_A1_Instructions.docx Office 2013 - myitlab:grader - Instructions Exploring - Excel Chapter 4: Assessment Project 1 Step Instructions Points Possible 9 Create a conditional formatting rule with these specifications to the range A2:C47: Formula to determine which cells to format Formula: =(AND(argument1,argument2)) where argument1 compares $D2 to see if it is less than 100 and argument 2 compares $E2 to see if the text is "Yes". Orange (Standard) fill color Green border color Outline preset border style 10 10 Create a custom color sort for the Description column with these specifications: Sort on cell color to display the Light Green (Standard) fill color on Top as the primary sort. Sort on cell color to display the Orange (Standard) fill color on Top as the secondary sort. 10 11 Freeze the top row. 5 12 Set the Scale to Fit percentage to 115%. Display the worksheet in Page Break Preview and insert a page break so that row 36 starts on a new page. 5 13 Repeat the titles on row 1 on all pages. 5 14 Insert a custom footer with the text Exploring Series on the left side, the sheet name code in the center, and the file name code on the right side of the June Expenses worksheet. 5 15 Ensure that the worksheets are correctly named and placed in the following order in the workbook: Dining Out, June Expenses. Save the workbook. Close the workbook, and then exit Excel. Submit the workbook as directed. 0 Total Points Updated: 04/04/2013 2 100 E_CH04_EXPV1_A1_Instructions.docx Office 2013 - myitlab:grader - Instructions Exploring Excel 05 A1 Salary Data Project Description: As the Human Resources Manager, you maintain employee salary data. You exported data from the corporate database into an Excel workbook. You want to display subtotals for salary by city. For further analysis, you want to create a PivotTable to determine what the total salaries would be if employees who earn an Excellent rating get a 5% increase in salary. Finally, you want to create a PivotChart that depicts what percentage of employees earn each performance rating. Instructions: For the purpose of grading the project you are required to perform the following tasks: Step Points Possible Instructions 1 Start Excel. Open the downloaded Excel file named exploring_e05_grader_a1_start.xlsx. 0 2 In the Subtotal worksheet, sort the data by Location in alphabetical order and then by Title in Alphabetical order. 5 3 In the Subtotal worksheet, use the Subtotal feature to calculate the average Salary by Location. 10 4 In the Subtotal worksheet, display only the Location averages and grand average. Expand the details for Chicago. 5 5 Use the Data worksheet to create a recommended PivotTable. Select the Sum of Salary by Title recommended PivotTable. Rename the new worksheet PivotTable. 10 6 Add the Location field to the COLUMNS area and remove the Last Name field from the ROWS area in the PivotTable. 5 7 Modify the VALUES field by changing the custom name to Salary Totals and formatting the values with Currency number type with zero decimal places. 8 Set a Performance filter to display Excellent ratings only. 5 9 Insert a calculated field named Field1 that calculates a 5% increase in salaries. The new value should give the total new salaries, not just the increase. 5 Updated: 07/17/2013 1 10 E_CH05_EXPV2_A1_Instructions.docx Office 2013 - myitlab:grader - Instructions Step Exploring Excel 05 A1 Points Possible Instructions 10 Modify the calculated field by changing the custom name to New Salary Totals and formatting the values with Currency number type with zero decimal places. 5 11 Type Job Titles in cell A5 and Cities in cell B3. 5 12 Apply Pivot Style Light 15, and display banded columns. 5 13 Use the Data worksheet to create a PivotChart using the Performance field for both the AXIS (CATEGORY) and VALUES areas on a new sheet named Performance. Change the chart type to Pie. 5 14 Hide the field buttons in the PivotChart and change the chart title to Performance Ratings. 5 15 Sort the data in the Performance PivotTable to display the largest number to the smallest number. 5 16 Add data labels to the Inside End position and change them from values to percentages. Group the data labels together and apply bold and 10-pt size to them. 17 Adjust the size of the PivotChart for the range A8:D20. 5 18 Ensure that the worksheets are correctly named and placed in the following order in the workbook: Subtotal, PivotTable, Performance, Data. Save the workbook. Close the workbook, and then exit Excel. Submit the workbook as directed. 0 Total Points Updated: 07/17/2013 2 10 100 E_CH05_EXPV2_A1_Instructions.docx Date Store 6/1/2016 Barnes & Noble 6/1/2016 Wendy's 6/2/2016 United 6/2/2016 Applebee's 6/3/2016 Marriot 6/3/2016 Subway 6/4/2016 Costco 6/4/2016 Costco 6/4/2016 Costco 6/5/2016 Theater 6/6/2016 Amazon.com 6/6/2016 PC Connection 6/6/2016 Hallmark 6/8/2016 Wendy's 6/8/2016 Wendy's 6/9/2016 Dillard's 6/9/2016 Subway 6/12/2016 Chili's 6/12/2016 Theater 6/12/2016 Theater 6/12/2016 Sam's Club 6/13/2016 Costco 6/14/2016 Delta 6/14/2016 Delta 6/14/2016 Texas Roadhouse 6/15/2016 Applebee's 6/15/2016 Hampton Inn 6/15/2016 McGrath's 6/16/2016 Delta 6/18/2016 Sam's Club 6/18/2016 Sam's Club 6/18/2016 Costco 6/19/2016 Costco 6/20/2016 McDonald's 6/20/2016 McDonald's 6/22/2016 Dillard's 6/22/2016 Hallmark 6/22/2016 Subway 6/22/2016 Wendy's 6/22/2016 Wendy's 6/24/2016 Costco 6/25/2016 American 6/25/2016 American 6/25/2016 Texas Roadhouse 6/26/2016 Hampton Inn 6/26/2016 Applebee's 6/27/2016 Tucano's 6/27/2016 Sam's Club 6/27/2016 Red Robin 6/28/2016 Amazon.com 6/30/2016 Theater 6/30/2016 Subway Description Books Lunch Airline Ticket Dinner Hotel Lunch Gas Gas Groceries Entertainment Books Computer Equipment Gifts Lunch Lunch Clothes Lunch Dinner Entertainment Entertainment Gas Groceries Airline Ticket Baggage Dinner Dinner Hotel Lunch Baggage Books Gas Groceries Computer Equipment Lunch Lunch Gifts Gifts Lunch Lunch Lunch Gas Airline Ticket Airline Ticket Dinner Hotel Lunch Dinner Groceries Lunch Books Entertainment Lunch $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ Amount 24.95 10.76 385.16 30.15 214.80 7.50 23.45 23.45 100.33 20.00 26.55 250.85 25.38 7.45 7.45 155.65 7.50 18.61 20.00 20.00 22.85 114.44 385.00 25.00 34.18 14.12 95.85 16.44 15.00 18.44 24.11 120.44 885.12 5.65 5.65 54.42 12.18 6.40 7.65 7.65 26.18 400.15 400.15 36.14 112.44 15.81 30.14 85.67 14.42 22.15 20.00 6.40 Business Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Date Store 6/1/2016 Barnes & Noble 6/1/2016 Wendy's 6/2/2016 United 6/2/2016 Applebee's 6/3/2016 Marriot 6/3/2016 Subway 6/4/2016 Costco 6/4/2016 Costco 6/5/2016 Theater 6/6/2016 PC Connection 6/6/2016 Amazon.com 6/6/2016 Hallmark 6/8/2016 Wendy's 6/9/2016 Dillard's 6/9/2016 Subway 6/12/2016 Chili's 6/12/2016 Theater 6/12/2016 Sam's Club 6/13/2016 Costco 6/14/2016 Delta 6/14/2016 Delta 6/14/2016 Texas Roadhouse 6/15/2016 Applebee's 6/15/2016 Hampton Inn 6/15/2016 McGrath's 6/16/2016 Delta 6/18/2016 Sam's Club 6/18/2016 Sam's Club 6/18/2016 Costco 6/19/2016 Costco 6/20/2016 McDonald's 6/22/2016 Dillard's 6/22/2016 Hallmark 6/22/2016 Subway 6/22/2016 Wendy's 6/24/2016 Costco 6/25/2016 American 6/25/2016 Texas Roadhouse 6/26/2016 Hampton Inn 6/26/2016 Applebee's 6/27/2016 Tucano's 6/27/2016 Sam's Club 6/27/2016 Red Robin 6/28/2016 Amazon.com 6/30/2016 Theater 6/30/2016 Subway Total Description Books Lunch Airline Ticket Dinner Hotel Lunch Gas Groceries Entertainment Computer Equipment Books Gifts Lunch Clothes Lunch Dinner Entertainment Gas Groceries Airline Ticket Baggage Dinner Dinner Hotel Lunch Baggage Books Gas Groceries Computer Equipment Lunch Gifts Gifts Lunch Lunch Gas Airline Ticket Dinner Hotel Lunch Dinner Groceries Lunch Books Entertainment Lunch $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ Amount 24.95 10.76 385.16 30.15 214.80 7.50 23.45 100.33 20.00 250.85 26.55 25.38 7.45 155.65 7.50 18.61 20.00 22.85 114.44 385.00 25.00 34.18 14.12 95.85 16.44 15.00 18.44 24.11 120.44 885.12 5.65 54.42 12.18 6.40 7.65 26.18 400.15 36.14 112.44 15.81 30.14 85.67 14.42 22.15 20.00 6.40 Err:508 Business Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Last Name First Name Title Salary Aikman Felser Granger Noble Beamer Powell Mills Watson Pennekamp Fegin Erpf Jones Jackson Bocholis Vergaldi Stack Kirsman O'Keefe Rodriguez Bret Brickey Dill Rapee Gander Felsher Trice Wat Edelstein Goldstein Carlo Hoffman Mason Rodriguez Denver Krissel Strafaci Winnick Smith McClendon Gary Cynthia Kevin Linda Larry Kathy Wendy Fran Lynn Nancy Meredith Kim Heidi Wendy Deborah Andrew Christy Ben Lisa Phil Wiliam Jeanne Paula David Joellen Burt George Martin George Criste Marjorie Elise Susan Jan Linda Jodi Jack Denise Bob Account Rep Account Rep Trainee Account Rep Trainee Account Rep Manager Account Rep Account Rep Account Rep Account Rep Account Rep Account Rep Senior Account Rep Trainee Trainee Senior Account Rep Manager Senior Account Rep Account Rep Account Rep Account Rep Account Rep Account Rep Senior Account Rep Trainee Trainee Trainee Account Rep Account Rep Account Rep Account Rep Account Rep Account Rep Senior Account Rep Account Rep Account Rep Senior Account Rep Manager $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ Location 83,600 81,800 41,000 93,800 43,400 89,800 141,800 65,600 67,400 50,400 73,800 91,600 89,400 112,400 36,600 43,600 99,600 168,800 78,600 96,400 55,600 64,000 61,000 66,200 89,200 37,400 44,000 38,400 65,000 63,800 54,400 90,000 88,400 87,800 88,600 92,200 71,800 82,800 154,200 Detroit Boston Detroit Boston Boston Chicago Detroit Chicago New York Boston Boston New York Boston Boston Chicago Boston Chicago Chicago Detroit New York Chicago Boston Detroit Detroit Detroit Boston Detroit New York Chicago Chicago Boston Chicago Detroit Boston Boston Detroit Detroit Boston Boston Last Name First Name Title Salary Aikman Felser Granger Noble Beamer Powell Mills Watson Fegin Erpf Jackson Bocholis Vergaldi Stack Kirsman O'Keefe Rodriguez Brickey Dill Rapee Gander Felsher Trice Wat Goldstein Carlo Hoffman Mason Rodriguez Denver Krissel Strafaci Winnick Smith McClendon Gary Cynthia Kevin Linda Larry Kathy Wendy Fran Nancy Meredith Heidi Wendy Deborah Andrew Christy Ben Lisa Wiliam Jeanne Paula David Joellen Burt George George Criste Marjorie Elise Susan Jan Linda Jodi Jack Denise Bob Account Rep Account Rep Trainee Account Rep Trainee Account Rep Manager Account Rep Account Rep Account Rep Account Rep Senior Account Rep Trainee Trainee Senior Account Rep Manager Senior Account Rep Account Rep Account Rep Account Rep Account Rep Senior Account Rep Trainee Trainee Account Rep Account Rep Account Rep Account Rep Account Rep Account Rep Senior Account Rep Account Rep Account Rep Senior Account Rep Manager $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ Location 83,600 81,800 41,000 93,800 43,400 89,800 141,800 65,600 50,400 73,800 89,400 112,400 36,600 43,600 99,600 168,800 78,600 55,600 64,000 61,000 66,200 89,200 37,400 44,000 65,000 63,800 54,400 90,000 88,400 87,800 88,600 92,200 71,800 82,800 154,200 Performance Detroit Boston Detroit Boston Boston Chicago Detroit Chicago Boston Boston Boston Boston Chicago Boston Chicago Chicago Detroit Chicago Boston Detroit Detroit Detroit Boston Detroit Chicago Chicago Boston Chicago Detroit Boston Boston Detroit Detroit Boston Boston Good Average Good Good Average Average Good Good Excellent Excellent Good Good Average Average Average Good Excellent Average Poor Good Good Good Excellent Average Average Average Average Excellent Average Good Excellent Excellent Excellent Average Good