Instructio Start Excel. Open exploring ecap grader 2 Transactions.xisr and save the workbook as 0.000 exploring ecap grader c2 Tra LastFirst. On the luneTotals worksheet, sort the data in the range A3:E16 in ascending order by Category. At each change in Category, use the Sum function to add subtotals to the data in the Amount column. Accept all other defaults. Collapse the outline to show the grand total and Cat 2 4.000 als Create a PivotTable in cell F1 on the AnnualExp worksheet using the data in the range A1:D17. Add the Expense field to the PivotTable as a row label; add the Amount field as the value; add the Year field as the column label. Change the format of the values in the PivotTable to accounting with no decimal places and set columns E:] to AutoFit Column Width. 3 4.000 Add the Category field to the Report Filter area of the PivotTable. Filter the data so that only grand total. Insert a Year slicer in the worksheet and use the slicer to filter the data so that only data from left corner aligns with the top left corner of cell 12. 4 expenses in the Variable category are displayed. Display the values as percentages of the 4.000 5 2015 is displayed. Change the height of the slicer to 2" and then reposition it so that the top 4.000 art based on the data in the PivotTable using the default Pie chart type. Create a Change the chart title text to Variable Expenses and remove the legend. Add data labels to the outside end position displaying only the category names and leader lines. Reposition the 6 chart so that the top left corner aligns with the top left corner of cell F13 4.000 Note, Mac users, select the range F5:G7, and on the Insert tab, cdlick Pie, and then click Pie Follow the remaining instructions On the Hometoan worksheet, in cell A10, enter a reference to the monthly payment from umn B. Create a one-variable data table for the range A9:H10 using the interest rate from 4.000 Add-ins Font Paragraph column B as the Row input cell. On the Homeloan wor column B. Create a two-variable data table in the range A12:H16, using the interest rate from column B as the Row input cell and the term in months from column B as the Column input 4.000 om a payment in column B needs to be if you want the monthly payment in column B to be 2000. Accept the solution. 4.000 docx Evcel Proj Step On the Homeloan worksheet, create a scenario named Maximum using cells 82, 83, B5, and 360, respectively, Show the resuits and close the Scenario Manager. Undo the last change. On the June2017 worksheet, in cell 17, sum the values in 57:824 if the purchase in column C in cell 19, calculate the number of times groceries were purchased during the month On the June2017 worksheet, in cell 111, calculate the total amount spent on groceries using a 10 86 as the changing cells. Enter these values for the scenario: 280000, 24000, 075, and 11 s groceries, in cell 18, average the values in E7:E24 if the purchase in column Cis groceries CR Font Paragraph ksheet, separate the text in the first column into two columns using the 20 asterisk () as the delimiter. Insert a function in cell A3 that will display the text in cell A9 with 4.000 initial capitalization only. Open the XML file in Notepad, and then find and replace all instances of the text General Electric with General E lectric Co (no period). Save the XML document and dose Notepad. 21 Refresh the XML data on the Stocks worksheet. 4.000 eV2 highclose.csvfile into cell A11. Open the Note, Mac users, import the c CSV file in Excel, and then find and replace all instances of the text General Electric with Updated: 09/18/2017 docx r Instructions Points Step General Electric Co (no period). Save and close the CSV document. Refresh the data on the General Electric Co (no period). Save and close the GSV document. Refresh the data on the Stocks worksheet. and D3 Apply the Retrospect theme to the on 2.000 eet. Add-ins aph Apply the Retrospect theme to the workbook. Apply the cell style Accenti to cells A3 and D3 Set the Author property of the workbook to Exploring Excel Student and then set the Title 23 submit the file as directed. Instructio Start Excel. Open exploring ecap grader 2 Transactions.xisr and save the workbook as 0.000 exploring ecap grader c2 Tra LastFirst. On the luneTotals worksheet, sort the data in the range A3:E16 in ascending order by Category. At each change in Category, use the Sum function to add subtotals to the data in the Amount column. Accept all other defaults. Collapse the outline to show the grand total and Cat 2 4.000 als Create a PivotTable in cell F1 on the AnnualExp worksheet using the data in the range A1:D17. Add the Expense field to the PivotTable as a row label; add the Amount field as the value; add the Year field as the column label. Change the format of the values in the PivotTable to accounting with no decimal places and set columns E:] to AutoFit Column Width. 3 4.000 Add the Category field to the Report Filter area of the PivotTable. Filter the data so that only grand total. Insert a Year slicer in the worksheet and use the slicer to filter the data so that only data from left corner aligns with the top left corner of cell 12. 4 expenses in the Variable category are displayed. Display the values as percentages of the 4.000 5 2015 is displayed. Change the height of the slicer to 2" and then reposition it so that the top 4.000 art based on the data in the PivotTable using the default Pie chart type. Create a Change the chart title text to Variable Expenses and remove the legend. Add data labels to the outside end position displaying only the category names and leader lines. Reposition the 6 chart so that the top left corner aligns with the top left corner of cell F13 4.000 Note, Mac users, select the range F5:G7, and on the Insert tab, cdlick Pie, and then click Pie Follow the remaining instructions On the Hometoan worksheet, in cell A10, enter a reference to the monthly payment from umn B. Create a one-variable data table for the range A9:H10 using the interest rate from 4.000 Add-ins Font Paragraph column B as the Row input cell. On the Homeloan wor column B. Create a two-variable data table in the range A12:H16, using the interest rate from column B as the Row input cell and the term in months from column B as the Column input 4.000 om a payment in column B needs to be if you want the monthly payment in column B to be 2000. Accept the solution. 4.000 docx Evcel Proj Step On the Homeloan worksheet, create a scenario named Maximum using cells 82, 83, B5, and 360, respectively, Show the resuits and close the Scenario Manager. Undo the last change. On the June2017 worksheet, in cell 17, sum the values in 57:824 if the purchase in column C in cell 19, calculate the number of times groceries were purchased during the month On the June2017 worksheet, in cell 111, calculate the total amount spent on groceries using a 10 86 as the changing cells. Enter these values for the scenario: 280000, 24000, 075, and 11 s groceries, in cell 18, average the values in E7:E24 if the purchase in column Cis groceries CR Font Paragraph ksheet, separate the text in the first column into two columns using the 20 asterisk () as the delimiter. Insert a function in cell A3 that will display the text in cell A9 with 4.000 initial capitalization only. Open the XML file in Notepad, and then find and replace all instances of the text General Electric with General E lectric Co (no period). Save the XML document and dose Notepad. 21 Refresh the XML data on the Stocks worksheet. 4.000 eV2 highclose.csvfile into cell A11. Open the Note, Mac users, import the c CSV file in Excel, and then find and replace all instances of the text General Electric with Updated: 09/18/2017 docx r Instructions Points Step General Electric Co (no period). Save and close the CSV document. Refresh the data on the General Electric Co (no period). Save and close the GSV document. Refresh the data on the Stocks worksheet. and D3 Apply the Retrospect theme to the on 2.000 eet. Add-ins aph Apply the Retrospect theme to the workbook. Apply the cell style Accenti to cells A3 and D3 Set the Author property of the workbook to Exploring Excel Student and then set the Title 23 submit the file as directed