SMAD 200L - Spreadsheet Exercise

You will first need to download the Spreadsheet file by clicking on the Spreadsheet word. You will need to save it on the desktop of the computer you are working on. Second, make a copy of this file to your floppy disk and remove the floppy disk. By using the find file command, you will be able to find Excel, the spreadsheet program. Launch Excel and open the SS exercise file. Once the file is open, conduct the following exercise to answer the questions at the end.

The information is based on a 1996 Department of Labor statistics. There are column headings for the various types of jobs, projected job growth, median hourly income, median income, lowest, highest and range difference. The Job Title column is the title that the Dept. of Labor assigns to various positions. The Projected Job Growth column is based upon a prediction from 1996 to 2006. Average means that the number of jobs will hold steady over the projected time period; faster than average means that the number of jobs will increase in that area at a faster than average rate, and less than average means that the number of jobs will not increase faster than average. The range of salaries is based upon market size, i.e., large city vs. small city, and that the range of salaries includes the beginner as well as someone who has been in the field for many years.

Remember to save your file often and keep track of which step you are on.

You should bold face the title (1996 Bureau of Labor Statistics) and the various column headings. The headings will run into other columns so you will need to adjust the columns and make them larger by clicking on the column and sliding the right side of the column until you can read the entire heading. You will notice that the salary in the Highest column should change to a number rather than 8E+05.

1. The file contains the following job descriptions in column a: Photographers/Camera Operators, TV Technicians, Public Relations Specialists, Writers and Editors, Reporters and Correspondents, Radio and TV Announcers, Visual Artist, and Multimedia computer technicians. It may be easier for you to eliminate the job descriptions and type in the list below. Or you can just add to the list that the file contains. Either way you should have 19 job description titles excluding the "Average" title. You will need to make sure you have the following job description titles in column a by typing in the title and selecting the return key. When you get to the last entry "Average" make sure you boldface it.

Photographers/Camera Operators, TV Technicians, Motion Picture Technicians, Music Directors/Singers/Composers, Musicians/Instrumental, Writers and Editors, Newspaper Reporters, Radio Reporters, Radio Announcers, Radio Operators, Radio Sports Announcers, Radio Technicians, TV Announcers, TV Reporters, Weather Announcers,

Sports TV Announcers, Visual Artist, Computer technicians (multimedia), Audio/Visual Specialists (education), Average.

2. In column b you should enter the following information: B4 Average, B5 Average, B6 Faster than Average, B7 Less than Average, B8 Less than Average, B9 Faster than Average, B 10 Less than Average, B 11 Less than Average, B 12 Less than Average, B 13 Less than Average, B 14 Less than Average, B 15 Average, B 16 Less than Average, B 17 Less than Average, B 18 Less than Average, B 19 Less than Average, B 20 Faster than Average, B21 Faster than Average, and B 22 Faster than Average.

3. You should go to the top of col. B, highlight it, go to the format menu, under Cells, click on Alignment, under Horizontal and hold down the menu and highlight center. This will center all the text in the Projected Job Growth column.

4. Highlight column C and add a new column. Type in the column headline in line with the other titles the following title: Median Hourly. Make sure it is boldface and centered.

5. Highlight column C and go to the format menu, under cells, under numbers and select the currency option with the $ indicator. Then click on Alignment and in the horizontal menu select center and then select OK.

6. Enter the following in Column C: C6 NA, C7 $9.15, C8 $14.81, C9 $15.23, C13 $9.98, C16 $31.50, C21 $16.61, and C22 $14.19.

7. In column D you will need to highlight the column and make the necessary selections to show currency and centered using the Format menu as completed in number 5.

8. Enter the following information in column D: D4 30600, D5 24260.00, D6 NA, D10 23296.00, D11 32356.00, D12 NA, D14 NA, D15 30251.00, D17 31235.00, D18 $52,562.00, D19 48704.00, D20 27100.00.

9. In D7 enter the following formula c7*40*52. Copy and paste the formula for the remaining empty cells in Column D.

10. In column E repeat step 5 by centering the information and displaying the figures as currency showing a dollar mark and two place figures.

11. Enter the following information in column E: E4 21000, E5 16422, E6 20000, E7 11960, E8 11960, E9 11960, E10 17784, E11 20217, E12 7100, E13 11960, E14 10608, E15 14500, E16 24935, E17 17435, E18 25638, E19 22400, E20 23000, E21 20780, E22 11960.

12. In columns F and G by holding down the shift key and repeat step 5 by centering the information and displaying the figures as currency showing a dollar mark and two place figures.

13. In column F enter the following information: F4 75100 F5 45158, F6 100000, F7 89959, F8 89000, F9 89959, F10 52000, F11 38541, F12 102676, F13 50439, F14 106200, F15 46602, F16 199741, F17 79637, F18 103919, F19 128877, F20 43000, F21 124000, F22 90000.

14. In G4 enter the following formula: F4 - E4.

15. Copy and paste that formula for the column.

16. In D 23 enter the following formula: Average(D4:D22)

17. Copy that formula to E 23, F 23, and G 23.

18. In C 27, you should type the answer to the following question: Based on the information, what is the Median Income and Range Difference of the Audio/Visual Specialists?

19. In C 28, you should type the answer to the following question: What job has the greatest Range Difference and what job has the least Range Difference?

20. In C 29, you should type the answer to the following question: What job looks like it is promising in terms of projected job growth (Faster than Average or Average is good), is average or above average in Median Income, and has a greater than Average Range Difference?

21. In A1 type your Test ID no.

22. In A2 type your name.

23. To print out the results, you will need to go to the File menu, under Page Setup, select Landscape, select adjust to 75% of normal size, select print preview - if everything looks like it is on one page turned sideways, then tell the computer to print it - HOWEVER, if everything doesn't look like it is on one page, then reduce the size to 65% instead of 75%.

24. Turn in the one page, showing your spreadsheet, the three answers, and your name and test id number in the upper left corner.