ࡱ> TVS_ bjbjzXzX *N2@\2@\4 4 8 U <$!G$ "µ^\% 0U t$z$0$4"U $4 > r: Exercise 1: Creating a Spreadsheet Read the instructions below to create the following payroll spreadsheet. Save your spreadsheet in your student workspace. Create a folder called Excel and save your file as payroll. MILLENIUM COMPUTER CONSULTANTSPayroll: Week Ending January 14, 2000Employee #Last NameFirst NameHours Hourly PayGross PayDeductionsNet Pay1EdwardsRocco40$20.002Smith Sarah40$25.003RossDavid40$18.004DavisFred44$29.005FosterSusan35$35.006TangiDona38$21.007LynchBob25$18.00TOTALS In cell A1, type (in upper case) the title MILLENIUM COMPUTER CONSULTANTS In cell A3, type Payroll: Week Ending January 14, 2000 Type the following headings: Cell Heading A5 Employee # B5 Last Name C5 First Name D5 Hours E5 Hourly Pay F5 Gross Pay G5 Deductions H5 Net Pay List the employee numbers in range A6:A12 List employee last names in range B6:B12 List employee first names in range C6:C12 List the number of hours worked for each employee in range D6:D12 List each employees hourly wage in range E6:E12. Enter the hourly wage WITHOUT the dollar sign with zero decimal places. For example, enter 20 in cell E6. To format the hourly wages for all employees, highlight the desired range (E6:E12). Choose Cells from the format menu, select the number tab. In the category list box, choose the desired format for the cells, in this case select Currency. In the decimal places box, type 2 or use the arrows to select 2. Click OK. In cell A14, type TOTALS (in upper case). Highlight the range A5:H14 and center align the data. CALCULATING GROSS PAY, DEDUCTIONS AND NET PAY In our spreadsheet, gross pay refers to the amount of money that our employees have earned (before any deductions are taken away). Gross pay is equal to the number of hours worked multiplied by each employees hourly wage. Therefore, we need to multiply cell D6 with cell E6 to find Rocco Edwards gross pay. By entering a formula in cell F6, Excel will automatically do this calculation for us. Remember all formulas start with an equal sign (=) and that Excel uses the asterisk (*) for multiplying, plus sign (+) for addition, minus sign (-) for subtraction and a slash (/) for division. CALCULATING GROSS PAY In cell F6, type =D6*E6 This formula will multiply the number of hours worked with the hourly pay rate for Rocco Edwards. Complete the above step for all employees in the range F7:F12. Note: the cell reference for each formula will be different. Format the cells in range F6:F12 as currency to 2 decimal places. CALCULATING DEDUCTIONS In our spreadsheet, we will assume that deductions are going to be 25% of our gross pay. Therefore, we need to enter a formula in cell G6 to calculate the deductions for employee #1, Rocco Edwards. In cell G6, type =F6*.25 Calculate the deductions for the remainder employees in the cell range G7:G12 Format the range G6:G12 as currency to 2 decimal places. CALCULATING NET PAY Net pay in our spreadsheet is the difference between an employees gross pay and their deductions. A formula is required in cell H6 that will automatically calculate an employees net pay (gross pay deductions). In cell H6, type =F6-G6 Calculate the net pay for the remainder employees in range H7:H12 Format the range H7:H12 to currency 2 decimal places. CALCULATING TOTALS FOR ALL EMPLOYEE HOURS, GROSS PAY, DEDUCTIONS AND NET PAY In our spreadsheet, we want to know the total hours all employees worked in cell D14. To calculate the total hours worked for all employees, you could create a formula that adds each cell one at a time. For example, =D6+D7+D8+D9 etc. etc. In a large spreadsheet this isnt practical or efficient. You can use a function in Excel called SUM to give you the same results. Make cell D14 the active cell. From the menu bar, select Insert, Function and select sum (or click the sum button ( from the standard tool bar and press enter). Make sure the range in the formula is D6:D13. Your function should be SUM(D6:D13) Calculate the total Gross Pay, Deductions and Net Pay for all employees in cells F14, G14 and H14. ENTERING NEW DATA One employee was not entered in our payroll spreadsheet. Enter in the following data (what happens to the values in cells D14, F14, G14 and H14?) Employee #: 8 Last Name: Russel First Name: Ana Hours Worked: 30 Hourly Pay: $22 Save your work and exit Excel. Make sure youve saved your workbook (named your file) payroll.     BTT 101 #$  4 5 r      < @ F h r "[}텁hZjhZU jh#5CJOJQJh#56CJOJQJh#>*CJOJQJh#5>*CJOJQJ"hhB*OJQJhnH phtH "h#B*OJQJhnH phtH  h#>*h#h#CJOJQJh#5>*CJ2#$   fkd$$IfrR b%4 ayth $$Ifa$$If        $$Ifa$ 0 1 2 3 4 E?6666 $$Ifa$$Ifkdb$$IfִB R b%    4 a4 5 6 7 8 9 : ; < = {rrrrrrrr $$Ifa$kd$$IfrR b%4 ayth = > I S ^ e p E<<<<< $$Ifa$kd$$IfִB R b%    4 ap z <6$Ifkd`$$IfִB R b%    4 a $$Ifa$ $$Ifa$ $$Ifa$$If E<66<<$If $$Ifa$kd$$IfִB R b%    4 a <3 $$Ifa$kd$$IfִB R b%    4 a $$Ifa$ $$Ifa$ $$Ifa$$If E<66<<$If $$Ifa$kdJ$$IfִB R b%    4 a  <3 $$Ifa$kd$$IfִB R b%    4 a $$Ifa$        $$Ifa$ $$Ifa$$If   $ ) , 3 E<66<<$If $$Ifa$kd$$IfִB R b%    4 a3 4 5 6 7 9 <3 $$Ifa$kd4$$IfִB R b%    4 a $$Ifa$9 ? C F M N O P $$Ifa$ $$Ifa$$IfP Q R S T U V E<<<<< $$Ifa$kd$$IfִB R b%    4 aV W X Y Z a b <6$Ifkd$$IfִB R b%    4 a $$Ifa$b c d e f g h $$Ifa$h i j k l m n E?6666 $$Ifa$$Ifkd$$IfִB R b%    4 an o p q r s <:5 & Fkdl$$IfִB R b%    4 a $$Ifa$  / < J S a o }  G #O$a$$a$ h & F h^ & F6XYp:S$Z[#$C & F  & F & F & F & F h h{|}#Uwxy$a$$a$ h & F h#CJOJQJh#h#5CJOJQJhZjhZU(/ =!"#$% `$$If!vh#v#v:V 5544 ythL$$If!vh#v:V 544 `$$If!vh#v#v:V 5544 ythL$$If!vh#v:V 544 L$$If!vh#v:V 544 L$$If!vh#v:V 544 L$$If!vh#v:V 544 L$$If!vh#v:V 544 L$$If!vh#v:V 544 L$$If!vh#v:V 544 L$$If!vh#v:V 544 L$$If!vh#v:V 544 L$$If!vh#v:V 544 L$$If!vh#v:V 544 L$$If!vh#v:V 544 s2&6FVfv2(&6FVfv&6FVfv&6FVfv&6FVfv&6FVfv&6FVfv8XV~ 0@ 0@ 0@ 0@ 0@ 0@ 0@ 0@ 0@ 0@ 0@ 0@ 0@ 0@_HmH nH sH tH <`< Normal_HmH nHsH tHP@P  Heading 1$@&^` CJOJQJL@L  Heading 2$@& h5CJOJQJN@N  Heading 3$$ h@&a$ CJOJQJDA D Default Paragraph FontVi@V  Table Normal :V 44 la (k (No List 8>@8 Title$a$ CJOJQJ8J@8 Subtitle CJOJQJ4@4 Header  !4 "4 Footer  !LB@2L Body Text$ ha$5CJOJQJPK![Content_Types].xmlN0EH-J@%ǎǢ|ș$زULTB l,3;rØJB+$G]7O٭VvnB`2ǃ,!"E3p#9GQd; H xuv 0F[,F᚜K sO'3w #vfSVbsؠyX p5veuw 1z@ l,i!b I jZ2|9L$Z15xl.(zm${d:\@'23œln$^-@^i?D&|#td!6lġB"&63yy@t!HjpU*yeXry3~{s:FXI O5Y[Y!}S˪.7bd|n]671. tn/w/+[t6}PsںsL. J;̊iN $AI)t2 Lmx:(}\-i*xQCJuWl'QyI@ھ m2DBAR4 w¢naQ`ԲɁ W=0#xBdT/.3-F>bYL%׭˓KK 6HhfPQ=h)GBms]_Ԡ'CZѨys v@c])h7Jهic?FS.NP$ e&\Ӏ+I "'%QÕ@c![paAV.9Hd<ӮHVX*%A{Yr Aբ pxSL9":3U5U NC(p%u@;[d`4)]t#9M4W=P5*f̰lk<_X-C wT%Ժ}B% Y,] A̠&oʰŨ; \lc`|,bUvPK! ѐ'theme/theme/_rels/themeManager.xml.relsM 0wooӺ&݈Э5 6?$Q ,.aic21h:qm@RN;d`o7gK(M&$R(.1r'JЊT8V"AȻHu}|$b{P8g/]QAsم(#L[PK-![Content_Types].xmlPK-!֧6 0_rels/.relsPK-!kytheme/theme/themeManager.xmlPK-!R%theme/theme/theme1.xmlPK-! ѐ' theme/theme/_rels/themeManager.xml.relsPK] N  & 4 = p   3 9 P V b h n  !"#$%8@0(  B S  ?NONO8*urn:schemas-microsoft-com:office:smarttagsCity9*urn:schemas-microsoft-com:office:smarttagsplace 0045#QZ}330045so#  0045 m 00 01= 1= D mh Man o -#r hh^h`.hh^h`.hh^h`.hh^h`.hh^h`.hh^h`.hh^h`.hh^h`.hh^h`. 00oDMan1=mhm-#r01= 2Z#P (4h@@UnknownG*Ax Times New Roman5Symbol3. *Cx ArialCNComic Sans MSACambria Math"1h[;g[;gLA0  0 !43HP ?42!xx "Exercise 1: Creating a SpreadsheetT.A.B.Salvo, Marianne0         Oh+'0   @ L X dpx$Exercise 1: Creating a SpreadsheetT.A.B.NormalSalvo, Marianne2Microsoft Office Word@F#@,wZ@@0՜.+,0 hp  T.A.B.   #Exercise 1: Creating a Spreadsheet Title  !"#$%&')*+,-./123456789:;<=>?@ABDEFGHIJLMNOPQRURoot Entry F@µWData (1Table0%WordDocument*NSummaryInformation(CDocumentSummaryInformation8KCompObjr  F Microsoft Word 97-2003 Document MSWordDocWord.Document.89q