Spreadsheet Applications

The following spreadsheet shows an organizational chart for a small company that provides computer help desk services over the telephone. The company has a number of general and administrative personnel and two levels of telephone support. Customer service representatives (CSRs) are the first to receive initial telephone support calls. If the CSR is unable to solve the problem, the call is passed to a software analyst who is more skilled than the CSRs to handle more complex questions.

Select the following link to review the chart: Week 1 Individual Project. (see attachment)

For a human, it is apparent from the layout of the worksheet how the organization hierarchy is structured. The chief executive officer (CEO) is the highest management individual. His staff is composed of personnel who oversee finance, administrative, and office functions, as well as two managers who in turn direct the group of CSRs and software analysts, respectively.

Question: Would a computer be able to recognize these relationships as easily?

Answer: Probably not. The computer requires more order and structure. The first column has one job title, the last column has only names, and the two intermediate columns have both job titles and names. The rows are not any better. They also have a mixed set of values. Many rows have only names.

To make the data understandable to a computer, it needs to be given more structure than it currently has. If you choose rows and columns to hold related information and then fill in the blanks, the computer will be able to process the data with speed and efficiency.

Part I: Perform the following operations using the worksheet provided:

1.     Make a duplicate of the worksheet called "Report," and save it using the following naming scheme: FirstInitialLastName_week1IPa (e.g., MRogers_week1IPa.xls).

2.     Make the new worksheet labeled "FirstInitialLastName_week1IPa" the active sheet.

3.     Row 2 will be used to hold column names. Name the first column "Job Title." Name the second column "Full Name." Name the third column "Manager."

4.     Select and move the data in columns B and C to the left so that it aligns with the CEO data. Keep the data in the same rows, even if there are blanks separating them.

5.     Move the two job titles that are now in Column B left so that they are now in Column A.

6.     Move the group of software analyst names left to Column B. Do the same for the group of customer service representatives.

7.     Use the Fill Down ribbon tool to copy the job title to each blank cell in Column A that is opposite a name.

8.     Populate Column C with the name of the corresponding manager for each employee. For the CEO, leave the corresponding Column C entry blank.

9.     Set the column widths of Columns A, B, and C to best fit the data.

10.  Column D should be blank. Delete Column D.

Part II: One of the most common worksheet functions is SUM. Perform the following operations on the FirstInitialLastName_week1IPa worksheet:

1.     Unhide Columns D through P.

2.     Using the SUM function and Fill Right operation, create a row of sums of all columns that hold dollar amounts beneath the data rows. Boldface this row of sums.

3.     At the first blank column to the right of the data (this should be Column Q), name the column "Net Salary" in row 2.

4.     Using formulas and the SUM function, calculate the net salary, which would be the salary (Column E) minus all deductions (Columns F through P).

5.     Save the spreadsheet.

  Click Here To Get More on This Topic......