Procedures for Final Exam – Part 2, Actg. Spreadsheet Applications
1. Download the Personal Finances.xlsx file, being sure to save it as Your_Last name_Personal Finances.xlsx.
2. Open the Your_Last name_Personal Finances.xlsx file.
3. Take a look at the file and complete the steps listed below in the grading rubric.
| Step | Instructions for Personal Finances file | Points Possible |
| 1 | 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. | 2 |
| 2 | 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. | 3 |
| 3 | 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. | 1 |
| 4 | 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. | 2 |
| 5 | Create a PivotChart based on the data in the PivotTable using the pie chart type. Change the chart title text to Variable 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. | 2 |
| 6 | 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. | 2 |
| 7 | 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. | 2 |
| 8 | 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 $2,000. Accept the solution. | 1 |
| 9 | 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. Show the results, and then close the Scenario Manager. Undo the last change. | 2 |
| 10 | On the June2012 worksheet, in cell I7, sum the values in E7:E24 if the purchase in column C is groceries; in cell I8, average the values in E7:E24 if the purchase in column C is groceries; and in cell I9, calculate the number of times groceries were purchased during the month. | 2 |
| 11 | On the June2012 worksheet, in cell I11, calculate the total amount spent on groceries using a credit card; in cell I12, calculate the average spent on groceries using a credit card; and in cell I13, calculate the number of times groceries were purchased using a credit card during the month. | 2 |
| 12 | On the June2012 worksheet, in cell F7, nest an AND function within an IF function to determine if the transaction was paid using a credit card and the amount of the transaction is less than -100. If both conditions are met in the AND function, the function should return the text Flag. For all others, the function should return the text OK. Copy the function down through cell F24. | 2 |
| 13 | Group the June2012 and JuneTotals worksheets together. Fill the contents and formatting from the range A1:F1 on the JuneTotals worksheet across the grouped worksheets. (Hint: You will use the fill drop-down in the Editing section on the Home ribbon.) Ungroup the worksheets. In cell I19 on the June2012 worksheet, insert a reference to cell E26 on the JuneTotals worksheet. | 1 |
| 14 | On the June2012 worksheet, create a validation rule for the range D7:D24 to only allow values in the list from the range I21:I24. Create an error alert for the selected range that will display after invalid data is entered. Using the Stop style, enter Invalid Entry as the title, and then type Please select a valid method. (include the period) as the error message. | 2 |
| 15 | Apply the Civic theme to the workbook. (Hint: this means all the sheets in the workbook) Apply the cell style Accent1 to cells A3 and D3 on the CarLoan worksheet. | 1 |
| 16 | Set the Author property of the workbook to Exploring Excel Student, and then set the Title property to Personal Finances. | 1 |
| 17 | Change your user name to Exploring Excel Student. On the CarLoan worksheet, insert the comment Updated on 7/17/2012 in cell A1. Mark the workbook as final. | 2 |
| 18 | Ensure that the worksheets are correctly named and placed in the following order in the workbook: JuneTotals; June2012; AnnualExp; HomeLoan; CarLoan. Save the workbook. Close the workbook and then exit Excel. | 0 |
| Total Points | 30 |
4. Download the Accounting Services.xlsx file, being sure to save it as Your_Last name_Accounting Services.xlsx.
5. Open the Your_Last name_Accounting Services.xlsx file.
6. Take a look at the file and complete the steps listed below in the grading rubric.