Lab 6– Excel 2007 – Formulas and Functions

GradingSheet

Name: / Date Turned In:
Section: / Class Day(s) and Time:
eGCC Login: / Score:
Practice 6A: Tour Guide Payroll Calculator.xlsx / Completed
“Peter” spelled correctly in cell A5, and “Maez, Javier” entered in cell A9
Cell B6 contains the value 38
Cell G5 contains the formula =E5+F5, result is 750
Cell F5 contains the formula = C5*(2.5*D5), result is 150
Cell C12 contains the value 4, and the cell C13 contains the value 6
“Totals” entered in cell A15
Cells B15:G15 sum range above. Results match Figure A-8
Cell C18 contains “Average Gross Pay”
Student name entered in cell A21
Text deleted from cells A1:G2. Left header includes “Quest Specialty Travel," center header includes “Tour Guide Payroll Calculator,” and right header includes “Week 32.”
Total Points:

Attachment: Tour Guide Payroll Calculator.xlsx, 1 page

Method: print menu [in Excel]

Replace Assignment 6A with the “Real Estate Listing” project in SAM

Assignment 6A: DO NOT COMPLETE Weather Statistics.xlsx
Do Project “Real Estate Listing” instead! / Points
Santa Fe spelled correctly in cell G3
Cell B8 = 52 and Cell C7 = 35.4
Cells B10:G10 average cell values above
Cell H7 contains the formula =SUM(B7:G7), result is 162.8
Cell H8 contains the formula =SUM(B8:G8), result is 186
Text deleted from cells A1 and A12. Center header includes “Average Annual Weather Statistics” and right header includes student’s name.
Page layout set to portrait, and gridlines turned off
Total Points:

Attachment: Weather Statistics.xlsx, 1 page

Method: print menu [in Excel]

Practice 6B: Tour Revenue Analysis.xlsx / Completed
Cells H4:H11 calculates 20% increase for each country’s revenues, using absolute cell reference to cell I1 (value 1.2). Example: H4=F4*$I$1
Cells in Total column sum 4 quarters revenue by country. Example: F4=SUM(B4:E4)
20% rise calculated for Quarters 1-4 in cells B14:E14. Cells are rounded, no decimal places. Example: B14=ROUND(B12+B12*.2,0)
Average calculated for Quarters 1-4 in cells B15:E15. Example: B15=AVERAGE(B4:B11)
Maximum value calculated for Quarters 1-4 in cells B16:E16. Example: B16=MAX(B4:B11)
Minimum value calculated for Quarters 1-4 in cells B17:E17. Example: B17=MIN(B4:B11)
30% rise calculated for cells B21:E21. Example: B21=B12*1.3
Cells B3:E3 copied to cells B20:E20
Total Points:

Attachment: Tour Revenue Analysis.xlsx, 1 page

Method: print menu [in Excel]

Replace Assignment 6B with the “Estimated Diner Expenses” project in SAM

Assignment 6B:DO NOT COMPLETE Candy Supply Company Inventory.xlsx Do Project “Estimated Diner Expenses” instead! / Points
“Change” entered in Cell G1
Cells H4:H8 multiply the row totals by Cell H1, using an absolute cell reference (value 2.3). Values are rounded to show one digit. Example: H4=ROUND(F4*$H$1,1)
Cells F4:F9 sum the inventory in each storage area. Example: F4=SUM(B4:E4)
Cells B11:F11 calculate a 30% drop in inventory. Example: B11=B9-B9*.3
Cells B13:E13 calculate the average number of cases in inventory. Example: B13=Average(B4:B8
Maximum values calculated for cases in storage in cells B14:E14. Example: B14=MAX(B4:B8)
Minimum values calculated for cases in storage in cells B15:E15. Example: B15=MIN(B4:B8)
Cells B3:F3 copied to cells B17:F17 and cells A4:A9 copied to cells A18:A23
Total Points:

Attachment: Candy Supply Company Inventory.xlsx, 1 page

Method: print menu [in Excel]

Lab 6Grading SheetPage 1 of 2