Excel Project 3 (Multiple Spreadsheets)

The purpose of this hands-on project is to give students an opportunity to apply the consolidation process to multiple electronic spreadsheets (CLO 5, MLO 5.1), and create, sort, and query data within an electronic spreadsheet (CLO 5, MLO 5.2). To do this, use the instructional materials, along with the lecture video(s), and attached data files to complete the Excel Project 3 (Multiple Spreadsheets).

1. Complete Excel Module 5: Working with Multiple Worksheets and Workbooks, pages EX 5-1 – EX 5-57*. Turn in 9 screen shots of the final completed Consolidated Expenses worksheet (Consolidated Expenses worksheet, Consolidated Expenses formulas, Consolidated Expenses chart, Lady Lobster worksheet, Lady Lobster formulas view, Henry Haddock worksheet,  Henry Haddock formulas, Sharon Shrimp worksheet, Sharon Shrimp formulas) each on a single page. Please be sure to extend cells with formulas displayed to ensure that the entire formula is shown. 

2. Complete Excel Module 5: Consolidating Payroll Worksheets (Annual Payroll Totals), pages EX 5-58 – EX 5-60*. Be sure to follow the instructions for steps 1 – 9 as directed and turn in 10 screen shots of the final completed Payroll consolidated spreadsheet (Annual Totals worksheet, Annual Totals formulas, Qtr 1 worksheet, Qtr 1 formulas, Qtr 2 worksheet, Qtr 2 formulas, Qtr 3 worksheet, Qtr 3 formulas, Qtr 4 worksheet, and Qtr 4 formulas) each on a single page. Please be sure to extend cells with formulas displayed to ensure that the entire formula is shown.

3. Complete Excel Module 6: Creating, Sorting, and Querying a Table, pages EX 6-1 – EX 6-55*. Turn in 4 screen shots (Bank Account Managers worksheet, Bank Account Managers formulas, Bank Account Managers with Subtotals worksheet, Bank Account Managers with Subtotals formulas, and Bank Account Managers with Treemap chart, each on a single page). Please be sure to extend cells with formulas displayed to ensure that the entire formula is shown.

The “@” symbol is primarily used for Excel Tables. It specifically means in a formula that you are referring to a specific cell in another column or even the header of the column itself. Since the textbook does not provide enough detail on the use of the @ sign in pages 6-18 and 6-19, please reference the formula below for cell H9. Similar syntax using the @ sign will be used for column I as well.

=IF([@Specialty]=”Loans”, [@[Account Values]]*0.0025,0)

4. Complete Excel Module 6: Apply Your Knowledge, Creating a Table with Conditional Formatting (Physical Therapy Codes), pages EX 6-56 – EX 6-58*. Be sure to follow the instructions for steps 1 – 16 as directed and turn in two screen shots in worksheet view and formulas of your completed workbook. Please be sure to extend cells with formulas displayed to ensure that the entire formula is shown.

5. Attach and submit your completed project (which should be a single Word document with word processing and/or screen shots of all assignment components, labeled and in order) by the assigned due date.

6. In addition to attaching the single Word document, please also attach ALL of the original Excel workbooks used in this assignment by the assigned due date.

* = Instructional materials are provided as PDF documents.