In this project, you will update a workbook to display bank transactions as a PivotTable. You will filter the PivotTable, format the values, display the values as calculations, and create a PivotChart using this data. Additionally, you will sort and subtotal data, create one- and two-variable data tables, and use Goal Seek and Scenario Manager to calculate possible mortgage payments. You will format grouped worksheets, set up validation rules, and create mathematical, logical, and lookup functions. You will also import a text file and XML data and manipulate the imported text. Finally, you will modify the document properties, insert a comment, and mark the workbook as final.
For the purpose of grading the project you are required to perform the following tasks:
Start Excel. Download, save, and open the Excel file namedExploring_eV2_Grader_CAP.
On the JuneTotals 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. Accept all other defaults. Collapse the outline to show the grand total and Category subtotals only.
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 the row label; add the Amount field as the value; and then add the Year field as the column label. Change the format of the values in the PivotTable to accounting with no decimal places.
Add the Category field to the Report Filter area of the PivotTable. Filter the data so that only expenses in the Variable category are displayed. Display the values as percentages of the grand total.
Insert a Year slicer in the worksheet and use the slicer to filter the data so that only data from 2011 is displayed. Change the height of the slicer to 2” and then reposition it so that the top left corner aligns with the top left corner of cell I2.
Create a PivotChart based on the data in the PivotTable using the pie chart type. Change the chart title text toVariable Expenses, and then remove the legend. Add data labels to the Outside End position displaying only the category names and leader lines. Reposition the chart so that the top left corner aligns with the top left corner of cell F13.
On the HomeLoan worksheet, in cell A10, enter a reference to the monthly payment from column B. Create a one-variable data table for the range A9:H10 using the interest rate from column B as the Row input cell.
On the HomeLoan worksheet, in cell A12, enter a reference to the monthly payment from 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 cell.