# Skill Assessments

[Page 756]

### Skill Assessments

#### Project 2D. Water Department

Objectives: 1. Use AutoFill to Fill a Pattern of Column and Row Titles; 2. Copy Text Using the Fill Handle; 3. Use AutoFormat; 4. View, Scroll, and Print Large Worksheets.

In the following Skill Assessment, you will complete a workbook for Janice Wilke, manager of the City of Desert Park Water Services Department, showing the schedule of staff members who take emergency water calls from residents. Your completed workbook will look similar to Figure 2.54.

##### Figure 2.54.

 For Project 2D, you will need the following file: New blank workbook

You will save your workbook as

2D_Water_Department_Firstname_Lastname

 End You have completed Project 2D

[Page 759]

#### Project 2E. Guard Report

Objectives: 5. Design a Worksheet; 6. Copy Formulas; 7. Format Percents, Move Formulas, and Wrap Text; 8. Make Comparisons Using a Pie Chart; 9. Print a Chart on a Separate Worksheet.

In the following Skill Assessment, you will create a Crossing Guard report for the month of January for Dennis Johnson, Chief of Police for the City of Desert Park. Your completed workbook will look similar to the one shown in Figure 2.55.

##### Figure 2.55.

 For Project 2E, you will need the following file: New blank workbook

You will save your workbook as

2E_Guard_Report_Firstname_Lastname

[Page 760]

1.

Start Excel and display a new blank workbook. From the File menu, display the Save As dialog box, click the Save in arrow , and then navigate to your Excel Chapter 2 folder. In the File name box, type 2E_Guard_Report_Firstname_Lastname and then click Save or press . Close the Getting Started task pane.

2.

In the range A3:E3 , type the following column titles:

 School Hours Rate Monthly Total Percent of Total Cost

3.

In cell A4 type Westmont Elementary and in cell B4 type 25 Select columns A:B . From the Format menu, point to Column , and then click AutoFit Selection . In the range A5 : B10 , continue the list of schools and crossing guard hours worked:

 Fairview Elementary 8 Gladstone Elementary 32 Cactus Middle School 30 Hope Middle School 10 Sonoran High School 25 Park View High School 15

4.

In cell C4 type 9.75 click the Enter button on the Formula Bar , and then use the fill handle to copy the hourly rate down through cell C10 . In cell D4 , construct a formula to multiply the hours in cell B4 times the rate of pay in cell C4 , and then on the Formula Bar click the Enter button. Your result is 243.75 . With cell D4 selected, fill the formula down through cell D10 . Recall that these numbers are still formatted with the General format in which trailing zeroes do not display.

5.

Click cell D11 , click the AutoSum button, and then press . The sum of the monthly totals is 1413.75. In cell A11 , type Total for January

6.

In cell E4 , construct a formula to calculate what percent (rate) of the Total for January (base) in cell D11 is represented by Westmont School's monthly total (portion). Recall that rates (or percents) are calculated as rate = percentage (portion) divided by the base . After you click or type the reference to cell D11, press to make the cell reference absolute. On the Formula Bar , click the Enter button. Your result is 0.172414 . With E4 selected click the Percent Style button. Fill the formatted formula down through cell E10 . The formula divides each school's Monthly Total by the Total for January to determine the percent of the total.

7.

Select the range D4:D11 and click the Currency Style button. Applying the Currency Style to each value will display the dollar sign in your resulting chart. Click cell D11, click the Border button arrow , and then apply the Top and Double Bottom Border .

8.

In cell A1 , type Crossing Guard Expense by School and then press . Select the range A1:E1 and then click the Merge and Center button. With the worksheet title still selected, change the font to Arial Black , change the font size to 14 , change the font color to Brown , and then apply a Light Yellow fill color.

9.

In cell A2 , type Hours Served in January and then press . Click cell A1 . On the Standard toolbar, click the Format Painter button, and then click cell A2. With cell A2 selected, change the font size to 12 .

10.

Select the range D3:E3 . Display the Format Cells dialog box, click the Alignment tab , and under Text control , select the Wrap text check box. In the displayed dialog box, click OK . With these cells still selected, on the Formatting toolbar, click the Center button. Click cell C3 and then click the Align Right button. Select cell A3 and click Center . Select the range A3:E3 and click Bold .

[Page 761]

11.

Save your workbook. In the Name Box , type a4:a10,d4:d10 and press to select the name of each school and the total monthly expense for each school. Click the Chart Wizard button. Under Chart type , click Pie , and then under Chart sub-type , in the first row click the second chart sub-type Pie with a 3-D visual effect . In the Chart Wizard dialog box, click Next .

12.

In Step 2 , click Next . Click the Titles tab . In the Chart title box, type Crossing Guard Expense for January and then click the Legend tab . On the Legend tab , clear the Show legend check box. Click the Data Labels tab . Under Label Contains , select the Category name and Value check boxes. Be sure the Show leader lines check box is selected. The school name and monthly total for each school is added to the chart.

13.

Click Next . Under Place chart , click As new sheet . Type Guard Expense Chart and then click Finish . Point to the Guard Expense Chart sheet tab , and then drag the sheet tab to the right of the Sheet1 tab . Point to the Sheet1 tab , double-click the tab to select it. Type Guard Expense Data and then press . Select Sheet2 and Sheet3 , and then from the Edit menu, click Delete Sheet .

14.

For any label that does not display a leader line, select the label and drag it slightly away from the pie to display its leader line. Click the title Crossing Guard Expense for January and change the font size to 20 .

15.

From the File menu, display the Page Setup dialog box. On the Header/Footer tab , create a Custom Footer in the Left section containing the File Name . In the displayed Footer dialog box, click OK . Click the Print Preview button and confirm that the file name displays in the lower left corner. Recall that if your printer is not a color printer, the print preview may display in shades of gray. Close the Print Preview.

16.

Display the Guard Expense Data worksheet. Display the Page Setup dialog box, and then create a Custom Footer in the Left section containing the File Name . On the Margins tab , center the worksheet Horizontally . Check the Print Preview and then redisplay the worksheet.

17.

Save your workbook. To submit electronically, go to Step 18, and then follow the instructions provided by your instructor. To print on paper, right-click the sheet tab, and then click Select All Sheets . Then, on the Standard toolbar, click the Print button. If you have been instructed to print the formulas from the Guard Expense Data worksheet, refer to Activity 1.17 in Chapter One to do so.

18.

If you printed your formulas, be sure to redisplay the worksheet by pressing . From the File menu, click Close . Do not save the changes to Page Setup that you used for printing formulas. Close the workbook and close Excel.

 End You have completed Project 2E

[Page 762]

#### Project 2F. Surplus

Objectives: 6. Copy Formulas; 7. Format Percents, Move Formulas, and Wrap Text; 10. Design a Worksheet for What-If Analysis; 11. Perform What-If Analysis.

In the following Skill Assessment, you will create a workbook for Mary Wilson, Library Director of the City of Desert Park Public Library, to estimate the revenue from selling some unused office furniture. The city Accounting Department estimates that the value of the furniture has depreciateddecreased in valueby 10% each year since its purchase. Ms. Wilson expects that she will have to discount the price even further to sell it. You will estimate the current value and then compare the total revenue if further discounts are necessary to sell the furniture. Your completed workbook will look similar to the one shown in Figure 2.56.

##### Figure 2.56.

 For Project 2F, you will need the following file: e02F_Surplus