Finals

Step

Instructions

Points Possible

1

Start Excel. Open “New_Final2020_21_FIT1040.xlsx” and save the workbook.

0

2

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 in the Amount column. Accept all other defaults. Collapse the outline to show the grand total and Category subtotals only.

12

3

Create a PivotTable in cell F3 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 F:J to AutoFit Column Width.

12

4

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.

12

5

Insert a Slicer and select Year in the worksheet and use the slicer to filter the data so that only data from 2019 is displayed. Change the height of the slicer to 1.4" and then reposition it so that the top left corner aligns with the top left corner of cell I2.

8

6

Create a PivotChart based on the data in the PivotTable using the 3D Pie chart type. 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 chart so that the top left corner aligns with the top left corner of cell F9. Hide all Field Buttons on chart. Note, Mac users, you do not have field buttons

12

7

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.

8

8

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.

8

9

On the HomeLoan worksheet, perform a goal seek analysis to determine what the down payment in column B needs to be if you want the monthly payment in column B to be 2000. Accept the solution

6

10

On the HomeLoan worksheet, create a scenario named Maximum using cells B2, B3, B5, and B6 as the changing cells. Enter these values for the scenario: 280000, 24000, .075, and 360, respectively.

5

11

Run Summary. Change the results cells to show B2, B3, B5, B6. Tidy up the scenario summary sheet (delete and empty rows and columns) and add NAMES to column A6:A9 and A11:A14. Then delete Column B. Delete note then Autofit columns

7

12

Group ALL worksheets together. Add a Footer - Your name to the left hand section, Sheet tab code in the middle and File name to the right hand section.

4

13

Change the Theme to Parcel for all sheets. Ungroup sheets and select the AnnualExp tab, save and submit

6

14

Save the workbook. Close the workbook, and then submit the file to MyITLab.

0

Total Points

100