bank-record-assignment

In this assignment, you will add a logical test, conditional formatting, and tables to an Excel spreadsheet. You can look at a sample to get an idea of what you’ll be creating.

1. Download the Bank_Records file. On this spreadsheet, debits and credits to a bank account over three months are shown.
2. In cell A1, type your name and Checkbook Register. Merge and center the title across columns A:G.
3. Insert 4 rows below the title.
   • In cell C3, type Starting Balance.
   • In cell C4, type January Balance.
   • In cell C5, type February Balance.
   • In cell C6, type March Balance.
4. In cell G8, type Balance.
5. In cell D3, enter $1500.
6. In cell G9, enter a formula that uses the Starting Balance to calculate the balance after this transaction. Debits should be subtracted and credits should be added.
7. In cell G10, enter a formula which uses the previous balance to calculate the balance after this transaction.
   • Copy and paste this formula to the rest of the cells in the Balance column.
8. Enter cell references to show ending balances.
   • In cell D4, enter a cell reference that shows the ending balance for January.
   • In cell D5, enter a cell reference that shows the ending balance for February.
   • In cell D6, enter a cell reference that shows the ending balance for March.
9. Use What-If Analysis to:
   • Create a scenario showing how a higher or lower credit for your paycheck would affect each monthly balance.
   • Create a scenario summary that displays how the monthly balances would be affected by the scenario you created. This summary will automatically be displayed on a separate worksheet.
10. Insert a PivotTable on a separate sheet, setting Category as Row Labels and Credit and Debit as Values.
    • For Value Field settings, select Sum for Debit and Credit. Your finished PivotTable will show how much you paid and deposited within each category.
11. Format your Bank Records spreadsheet. Use formatting that makes your information easy to read and understand. Consider font size and style, background colors, cell format (i.e. currency), table borders, etc.

Save your file as an Excel Workbook (.xlsx)
Include the term Bank and your First Name when saving your file. Example: Bank_David.xlsx
Upload your completed document using the Browse button, and then click the Submit button.