1. Adjust the column width of columns D and F to 40. 2. Fill out the cells in co
ID: 371406 • Letter: 1
Question
1. Adjust the column width of columns D and F to 40.
2. Fill out the cells in column D for both CY00 and CY01 in the "Net Income" worksheet with
the appropriate excel calculations (eg: Income - Expense = Net Income Before Tax). Make sure
any cells associated with dollars are formatted as such to 2 decimal places.
3. Fill out the cells in column F for both CY00 and CY01 in the "Net Income" worksheet with
the appropriate calculation by reducing the Tax Rate from column D. Requirement: Use
absolute reference and tax rate in cell 'Rate Table'!B$2 & 'Rate Table'!B$3 directly from the
rate table worksheet with IF Condition to get full credit. Do NOT copy the rates back to the
Net Income worksheet.
4. For CY01, there is a projected increase in both income and expense. The increase
percentages are noted on the Rate Table worksheet and should also reflect absolute
references for income of 'Rate Table'!B$4 and expense 'Rate Table'!B$5 in calculating the
projected increase.
5. Provide totals and averages (SUM and AVERAGE functions required) for columns B, C, D,
and F as provided. All calculated values and totals should match those provided in this
assignment.
6. Make sure the Bank of Brodnax Heading is provided and centered using the Merge &
Center function across cells A-F.
7. The "look and feel" of the spreadsheet should correspond exactly to what is provided in
this assignment including color orientation for headings, totals, and tab label.
8. Be sure the Net Income and Rate Table tabs are appropriately labeled and colored.
9. In order to get full credit, you need to submit an Excel file to the Assignment Dropbox and
fulfill all of the requirements noted above. Due Date, 11:59pm, 21 Nov. Be sure and check
that each requirement is met. Each requirement not provided is a 10-point penalty. Entering
constants in calculation cells are incorrect and will be penalized as such.
Explanation / Answer
Bank of Brodnax - CY 00 Month Income Expense Total Net Income Before Tax Total Net Income After Tax Jan $ 20.00 $ 10.00 $ 10.00 $ 1.00 Feb $ 21.00 $ 9.00 $ 12.00 $ 1.20 Mar $ 23.00 $ 21.00 $ 2.00 $ 0.20 Apr $ 24.00 $ 2.00 $ 22.00 $ 3.30 May $ 50.00 $ 34.00 $ 16.00 $ 2.40 Jun $ 32.00 $ 23.00 $ 9.00 $ 0.90 Jul $ 74.00 $ 50.00 $ 24.00 $ 3.60 Aug $ 84.00 $ 44.00 $ 40.00 $ 6.00 Sep $ 32.00 $ 21.00 $ 11.00 $ 1.10 Oct $ 10.00 $ 8.00 $ 2.00 $ 0.20 Nov $ 21.00 $ 1.00 $ 20.00 $ 3.00 Dec $ 5.00 $ 2.00 $ 3.00 $ 0.30 Total $ 396.00 $ 225.00 $ 171.00 $ 23.20 Average $ 33.00 $ 18.75 $ 14.25 $ 1.93 Bank of Brodnax - CY01 Month Income Expense Total Net Income Before Tax Total Net Income After Tax Jan $ 22.20 $ 10.50 $ 11.70 $ 1.17 Feb $ 22.05 $ 9.00 $ 13.05 $ 1.31 Mar $ 23.00 $ 21.00 $ 2.00 $ 0.20 Apr $ 24.00 $ 2.00 $ 22.00 $ 3.30 May $ 50.00 $ 34.00 $ 16.00 $ 2.40 Jun $ 32.00 $ 23.00 $ 9.00 $ 0.90 Jul $ 74.00 $ 50.00 $ 24.00 $ 3.60 Aug $ 84.00 $ 44.00 $ 40.00 $ 6.00 Sep $ 32.00 $ 21.00 $ 11.00 $ 1.10 Oct $ 10.00 $ 8.00 $ 2.00 $ 0.20 Nov $ 21.00 $ 1.00 $ 20.00 $ 3.00 Dec $ 5.00 $ 2.00 $ 3.00 $ 0.30 Total $ 399.25 $ 225.50 $ 173.75 $ 23.48 Average $ 33.27 $ 18.79 $ 14.48 $ 1.96 Tax Rate 1 10% Tax % if less than: 15 Tax Rate 2 15% Tax % if greater than or =: 15 Proj. Income Rate 11% Proj. Expense Rate 5%
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.