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

{% if main.adsdop %}{% include 'adsenceinline.tpl' %}{% endif %}

You will save your workbook as

2D_Water_Department_Firstname_Lastname

	[Page 757]
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 2D_Water_Department_Firstname_Lastname and then click Save or press . Close the Getting Started task pane.
2.	In cell A2 type Start Time In cell B2 , type 8:00 AM and then on the Formula Bar , click the Enter button. This is the starting time of the period from 8 AM to 9 AM. With cell B2 still selected, drag the fill handle to the right through cell Q2 to fill the hours from 8 AM to 11 PM.
3.	Press to return to the upper left portion of the worksheet. In cell A3 , type End Time In cell B3 type 9:00 AM and then on the Formula Bar , click the Enter button. With B3 still selected, drag the fill handle to the right through cell Q3 to fill the hours from 9 AM to 12 AM.
4.	Press to return to the upper left portion of the worksheet. In cell A4 , type May 1 and then on the Formula Bar , click the Enter button. With A4 still selected, drag the fill handle down through cell A34 to fill the days in May through May 31.
5.	In cell A1 , type Water Services Hotline Shift Assignments and then on the Formula Bar , click the Enter button. In the Name Box , type a1:q1 press , and then click the Merge and Center button. For the title, change the font to Arial Rounded MT Bold , the font size to 18 , the font color to Plum , and then add a fill color of Light Green .
6.	In cell B4 type Jane and then on the Formula Bar , click Enter . With cell B4 selected, drag the fill handle to the right through cell J4 . With B4:J4 selected, drag the fill handle down through cell J8 . Jane works from 8:00 AM to 5:00 PM the first five days of the month.
7.	Jane takes two days off and then repeats the pattern of working five days and taking two days off. With the range B4:J8 selected, on the Standard toolbar, click the Copy button. Click cell B11 , and then on the Standard toolbar, click the Paste button. Jane's schedule from May 1 through May 5 is copied to May 8 through May 12.
8.	Click cell B18 and click the Paste button. Jane's name is pasted into the range B18:J22 . Repeat this pattern to paste Jane's name into the shifts for May 22 to May 26. Use the skills you just practiced to copy three rows of Jane's name and paste them into the shifts for May 29 to May 31.
9.	Fred works the same 8 AM to 5 PM shift as Jane when Jane is off. Use the skills you practiced to fill his name into the range B9:J10 . Copy Fred's name and paste it into the other three gaps between Jane's name, ranges B16:J17 , B23:J24 , and B30:J31 . Save your workbook.
10.	Click cell B4 , and then from the Window menu, click Freeze Panes . This will keep the row and column headings on the screen while you complete the schedule for the evening shift. Samantha works the evening shift until closing. She works three days and takes one day off. In cell K4 type Samantha and, on the Formula Bar , click the Enter button. Use the skills you just practiced to fill her name into K4:Q6 . Copy cells K4:Q6 that contain Samantha's name and paste into ranges K8:Q10 , K12:Q14 , K16:Q18 , K20:Q22 , K24:Q26 , K28:Q30 , and K32:Q34 .
11.	Doug works the closing shifts that Samantha does not work. In cell K7 , type Doug and then fill his name into the remaining cells in the schedule.
	[Page 758]
12.	In the Name Box type a2:q34 and press . From the Format menu, click AutoFormat . In the displayed dialog box, scroll down and click List 3 , and then click OK . Select cell B2 , and then on the Standard toolbar, click the Format Painter button. Select the range A3:Q3 to apply the format from row 2 to row 3 .
13.	Click the Sheet2 sheet tab . Hold down and click the Sheet3 sheet tab . From the Edit menu, click Delete Sheet .
14.	From the File menu, display the Page Setup dialog box. On the Page tab , under Orientation , click Landscape . Under Scaling , click Fit to and confirm that 1 is displayed in the adjacent 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. Close the Print Preview window.
15.	Save your workbook. To submit electronically , follow the instructions provided by your instructor. To print on paper, on the Standard toolbar, click the Print button. Close the workbook and close Excel.

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

{% if main.adsdop %}{% include 'adsenceinline.tpl' %}{% endif %}

You will save your workbook as

2E_Guard_Report_Firstname_Lastname

[Page 760]

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.

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

School

Hours

Rate

Monthly Total

Percent of Total Cost

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

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.

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

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.

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 .

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.

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

{% if main.adsdop %}{% include 'adsenceinline.tpl' %}{% endif %}

You will save your workbook as

2F_Surplus_Firstname_Lastname

	[Page 763]
1.	Start Excel, and then from the File menu, click Open . Navigate to the student files that accompany this textbook ; locate and then open e02F_Surplus . 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 2F_Surplus_Firstname_Lastname and click Save .
2.	Take a moment to examine the information in the worksheet. In cell B3 type 10% which is the rate of depreciation per year. Recall that by typing % , the cell is formatted with the percentage format. In cell B4 type 20% and then press .
3.	In cell D6 type Depreciation Percent and press . In cell E6 type Depreciated Value and in cell F6 type Sale Price Select the range D6:E6 , display the Format Cells dialog box, click the Alignment tab , and then select the Wrap text check box. Select columns D:E and set the width to 80 pixels .
4.	In cell D7, construct a formula to calculate the Depreciation Percent of the File Cabinet, which is equal to the Age in Years times the Depreciation per Year . As you construct the formula, use * to indicate the multiplication operation and press to make the reference to the value in cell B3 absolute. Click the Enter button on the Formula Bar . Fill the formula down through cell D11 . Then, with the range D7:D11 selected, click the Percent Style button.
5.	Click cell E7 and construct a formula to calculate the value after decrease , which is equal to base (original cost) x percent for new value . The base is the value in cell B7, so begin by typing =b7* Then, within parentheses, establish the percent for new value , which equals base percent - percent of decrease ; in this instance 100%-70%. Because the percent of decrease is the value in cell D7, you can reference that cell. So, complete the formula by typing (100%-d7) The file cabinet is worth 30% of its original value. Your completed formula is =b7*(100%-d7) On the Formula Bar click the Enter buttonyour result is 67.5 . The formula used to calculate the depreciated value is similar to the formula used to calculate the value after increase , except that you subtract the percentage from 100% instead of adding the percentage to 100%. With cell E7 selected, click the Currency Style button, and then fill the formatted formula down through cell E11 . You can see that after 7 years, the file cabinet is worth just $67.50.
6.	In cell E12 , double-click the AutoSum button. If all of the items are sold at their depreciated value, with no further reduction, the total revenue from the sale will be $349.90 . With cell E12 selected, click the Copy button, click cell B15 , display the Edit menu, and then click Paste Special . Under Paste , click the Values and number formats option button and click OK . This will paste the cell's value $349.90 rather than the cell's content, which is a formula. If you want to do so, press to cancel the moving border.
7.	In cell F7 , construct a formula to calculate the sale price of each item assuming it is sold at its depreciated value with an Additional Reduction of 20%. That is, type =e7(100%-$b$4) and then on the Formula Bar click the Enter button. Alternatively, type = e7(1-$b$4) This formula calculates the value after an additional decrease. It multiplies the Depreciated Value by 100% minus the Additional Reduction of 20% in cell B4 . With cell F7 selected, click the Currency Style button, and then fill the formula in cell F7 down through cell F11 .
	[Page 764]
8.	In cell F12 , use AutoSum to calculate the total Sale Price of the items with an additional 20% discount. The result is $279.92 . Copy cell F12 and use the Paste Special command to copy the values and number formatsrather than the formulato cell B16 . If you want to do so, press to cancel the moving border.
9.	To sell the items, it may be necessary to reduce the items by an additional 40%, rather than 20%. In cell B4 , replace 20% by typing 40 Press and notice that all the formulas in the range F7:F12 are recalculated. The new total is $209.94 . Copy cell F12 , and then paste its value and number format into cell B17 .
10.	In cell D12 type Totals Select the range E12:F12 , click the Borders button arrow , and then apply the Top and Double Bottom Border . Select the range A6:F6 , click Center , and then apply a Light Green fill color. Click cell A1 , merge and center the title across the range A1:F1 , change the font to Arial Rounded MT Bold , change the font size to 16 , change the font color to Olive Green , and apply a Light Green fill color.
11.	Select Sheet2 and Sheet3 , and then from the Edit menu, click Delete Sheet . From the File menu, display the Page Setup dialog box, and then on the Margins tab , center the worksheet Horizontally . On the Header/Footer tab , create a Custom Footer in the Left section and add the File Name . In the displayed Footer dialog box, click OK .
12.	In the displayed Page Setup dialog box, click the Print Preview button. Confirm that the file name displays in the left section of the footer. On the Print Preview toolbar, click the Close button.
13.	Save your workbook. To submit electronically, go to Step 14, and then follow the instructions provided by your instructor. To print on paper, click the Print button. If you have been instructed to print the formulas, if necessary refer to Activity 1.17 in Chapter One to do so.
14.	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.