Grader You are the financial manager for the School

/in /by

Grader

You are the financial manager for the School of Information Sciences. Your school has three divisions: Information Management, Information Systems, and Information Technology. You need to complete a workbook to track faculty professional development expenses for each division. You will then link the individual division workbooks to a master workbook for the School of Information Sciences.

Data Validation

You are concerned that faculty are requesting professional memberships that cost more than the allocated budget. You want to create a validation rule to prompt the user with information if invalid data is entered. Because you might provide an exception to the rule, you want to warn, not stop, the user from entering data.

  1. Open e09c1IM and save it as e09c1IM_LastFirst.
  2. Display the Bonnet sheet and create a validation rule in cell B3 with these specifications:
  1. Group the four faculty worksheets, fill the contents of cell B3 from the Bonnet worksheet to the other three faculty worksheets. Use the option that fills all. Ungroup the worksheets and change the values in cell B3 in these sheets:

When the error message displays, click No, type 350, and press Enter.

  1. Activate the feature that circles invalid data on the Bonnet worksheet. Save the workbook.

Group Worksheets, Fill Data, and Enter Formulas

The Bonnet worksheet contains formatted labels in column A and the range B1:C1. You want to copy the formatted data to the other faculty worksheets. In addition, you will insert a function to calculate the total expenses for the faculty while the worksheets are grouped. Finally, you will format the values.

  1. Group the four faculty worksheets.
  2. Select the range A1:A6 in the Bonnet worksheet and fill across the other grouped worksheets.
  3. Change the width of column A to 26.
  4. Enter the SUM function in cell B6 of the Bonnet worksheet to total her expenses.
  5. Format cells B3 and B6 with Accounting Number Format with zero decimal places. Format the range B4:B5 with Comma Style with zero decimal places.
  6. Apply Underline to cell B5 and Double Accounting Underline to cell B6.
  7. Ungroup the worksheets and save the workbook.

Create 3-D References

You want to enter formulas that reference cells on other worksheets. That way, if the values for the individual faculty change, those changes will be reflected in the IM Division worksheet.

  1. Click the IM Division sheet tab.
  2. Enter each formula with a 3-D reference:
  1. Select the range B4:E4 and copy the formulas to the range B5:E7.
  2. Create a function in cell F4 with 3-D references to the other worksheets. Use the SUM function to calculate the total professional membership dues for all four faculty. Copy the function to the range F5:F7. The copied functions should calculate the total reference books, total travel expenses, and the grand total for all faculty.
  3. Format the ranges B4:F4 and B7:F7 with Accounting Number Format with zero decimal places.
  4. Format the range B6:F6 with Comma Style with zero decimal places.
  5. Apply Underline to the range B6:F6 and Double Accounting Underline to the range B7:F7. Save the workbook.

Create Hyperlinks

The Documentation sheet is the first sheet. You want to create hyperlinks from this sheet to the total cells for the respective faculty and for the IM Division.

  1. Select the Documentation sheet tab, enter your name and the current date in the respective cells.
  2. Create the following hyperlinks in the Documentation worksheet to the other worksheets:
  1. Edit the hyperlink in cell B11 to display a ScreenTip Click to see division totals.
  2. Test all hyperlinks and make any necessary corrections. Save the workbook.

Audit a Workbook

You want to audit formulas in the Information Systems workbook. You will correct errors while using the Error Checking feature.

  1. Open e09c1IS and save the workbook as e09c1IS_LastFirst.
  2. Show dependents for cell B4 to see that the formulas in cells B7 and F7 rely on the value in cell B4.
  3. Activate the Error Checking dialog box to find the first potential error. Click Help on this Error to display a Help window. Correct the function to total the range B4:E4.
  4. Use Error Checking to identify a circular reference. Display the precedents arrow and fix the error. Save the workbook.

Link Workbooks

You want to consolidate division totals into the professional development workbook using links. To make it easier to create the links, you will tile windows.

  1. Open e09c1IT and save it as e09c1IT_LastFirst.
  2. Open e09c1ProDevelop and save it as e09c1ProDevelop_LastFirst.
  3. Tile the four windows, making sure the division totals worksheets are active.
  4. Create a link in cell B4 in the e09c1ProDevelop_LastFirst workbook to the cell containing the total Professional Membership dues for the IM Division. Edit the formula to change the cell reference from absolute to relative. Then copy the formula to the range B5:B7.
  5. Create a link in cell C4 in the e09c1ProDevelop_LastFirst workbook to the cell containing the total professional dues for the IS Division. Remove the arrows and edit the formula to change the cell reference from absolute to mixed. Then copy the formula to the range C5:C7.
  6. Create a link in cell D4 in the e09c1ProDevelop_LastFirst workbook to the cell containing the total professional dues for the IT Division. Edit the formula to change the cell reference from absolute to mixed. Then copy the formula to the range D5:D7.
  7. Create a function in cell E4 for the total professional dues. Copy the function to the range E5:E7.
  8. Set up a Watch Window to watch the formulas in the e09c1ProDevelop_LastFirst workbook.

Finalize the Workbooks

You are ready to finalize the workbooks by adding a footer to the worksheets. In addition, you will compress the files into one zip folder.

  1. Create a footer on all worksheets with your name on the left side, the sheet name code in the center, and the file name code on the right side. Save and close the workbooks.
  2. Open File Explorer, select the four e09c1 files with your name and compress them into a folder named e09c1BudgetFiles_LastFirst.
  3. Based on your instructor’s directions, submit e09c1BudgetFiles_LastFirst.zip.

This page intentionally left blank