This assessment is meant to test your knowledge of the information taught this first quarter.
It is in two parts:
Part one - Question and Answer
Part Two - Project
Part One - Question and Answer
Part Two - Project
EXCEL
In this exercise, you will prepare and print a payroll for the Lasting Impressions Print Shop.
1. Download Quarterly_One or create the spreadsheet below in a workbook named Quarterly_One – put your name in cell A14.
2. Center the title and subtitle over the columns.
3. Enter a formula to calculate Gross pay for Davis using the Hourly Rate and Hours Worked, and copy for each employee.
4. Enter a formula to compute Federal Income Tax at 15% of gross Pay.
5. Copy the formula for each employee.
6. Enter a formula to compute Social Security Tax at 6.20% for each employee and copy the formula for all employees.
7. Enter a formula to compute Medicare Tax at 1.45% of Gross Pay and copy to all employees.
8. Enter a formula to calculate Net Pay, which is the amount received after taxes are deducted and copy to all employees.
9. Format columns E, F, G, H and I for two decimal places using the Number format option.
10. Right-Align all column heading labels for columns C through I.
11. Print Preview the file. Close Print preview.
12. Select Page Setup from the File menu and note all print settings-landscape, and center on page.
13. Print one copy of the worksheet.
14. Close the workbook.
Posted at 04:33 AM | Permalink | Comments (0)
1. Open 2.12payroll, which you created earlier or, Download D3.24payroll.
2. Save the file as 3.24payroll.
3. Insert new columns as shown in figure below.
4. Enter the new labels EMPL #, ID #, REGULAR PAY, OVERTIME PAY, UNION DUES, and AVERAGE HOURS WORKED, as shown.
5. Edit the worksheet heading to read PAYROLL FOR WEEK ENDING November 17th, and center it across the columns.
6. Change the column widths to fit the data.
7. Delete the date from Cell C2 and the DATE: label from Cell A2.
8. Sort the employee names alphabetically.
9. Enter the ID number for AARON as 112 and for BELL as 113
Use the Fill Series function to enter the ID numbers for the other employees.
10. Calculate REGULAR PAY for AARON.
(I'm not sure how to do that.") Show me a Hint
(I have no idea what I'm doing.") Show me the Answer
Copy the formula to the other employees
11. Calculate OVERTIME PAY for AARON.
a. Enter an IF statement formula in Cell G6 that computes overtime at the rate of time and a half if employees work more than 40 hours per week. If employees do not work more than 40 hours, they do not receive any overtime pay.
("WAIT!!! I can't fogure it out. I need a hint.") Show Hint One
("WAIT!!! I STILL can't figure it out. Just tell me. I give up.") Show Hint Two
b. Copy the formula to the other employees.
12. Edit the GROSS PAY formula for AARON by adding REGULAR and OVERTIME PAY.
b. Copy the updated GROSS PAY formula to the other employees.
13. Calculate UNION DUES for AARON. (UNION DUES are computed at the rate of 1% of GROSS PAY.) Copy the UNION DUES formula to the other employees.
14. Edit the NET PAY formula for AARON by subtracting the sum of taxes and UNION DUES from GROSS PAY. Copy the NET PAY formula to the other employees.
15. Copy the TOTALS formula to the REGULAR_PAY OVERTIME PAY, and UNION DUES columns.
16. Calculate the AVERAGE HOURS WORKED where shown.
17. Edit the column heading EMPL NAME to read EMPLOYEE, and S.S. to read F.I.C.A.
18. Insert a right-aligned footer that reads Bit-Byte Computer Store - November Payroll, and apply bold and italic.
19. Adjust column widths as necessary.
20. Save the file.
21. Copy the worksheet to a new sheet within the same workbook.
22. Edit the heading to read: PAYROLL FOR WEEK ENDING NOVEMBER 26.
23. Change the hours worked for each employee for the current week as follows:
AARON: 47. 5
BELL: 34. 5
DAVIS: 36
FORD: 37
MINO: 50
TURRO: 45
24. Name the first tab November 17 and the second tab November 26.
25. Set the print orientation for both worksheets to landscape, then print one copy of the November 26th worksheet with gridlines and row and column headings.
26. Save the changes; close the file.
Have Fun and learn lots
-Mr. Hennings
Posted at 02:51 AM | Permalink | Comments (0)
Directions:
Have fun, and good luck
-Mr. Hennings
Posted at 10:48 AM | Permalink | Comments (0)
1. Download D3.19exam, or open 1.7exam, which you may have created earlier.
NOTE: Your file may not look exactly like the illustration shown below. Modify this exercise to work with the layout you created earlier.
2. Save the file as 3.19exam.
3. Enter the new labels STUDENT AVERAGE, EXAM AVERAGE, HIGHEST GRADE, and LOWEST GRADE in the cell locations shown in Figure 19.1. If you downloaded my file, this step is done for you.
4. Adjust column widths to fit the data.
5. Calculate the STUDENT AVERAGE for each student. Enter the AVERAGE function to average EXAM 1, EXAM 2, and EXAM 3 grades. Place the insertion point in the cell where the answer should appear. Click the AutoSum list arrow and choose Average. Adjust the range, if necessary, and then press Enter.
Copy the formula from Cell E5 to the range E6:E10.
6. Insert a column after Column D.
NOTE: STUDENT AVERAGES will automatically update.
7. Calculate EXAM AVERAGE.
8. Calculate HIGHEST GRADE.
9. Calculate LOWEST GRADE.
10. Format all EXAM AVERAGE, HIGHEST AVERAGE, LOWEST GRADE, and STUDENT AVERAGE grades to whole numbers with two decimals. Apply bold to STUDENT AVERAGE grades
11. Format and align worksheet data as indicated in the ﬁgure.
12. Sort the student names in alphabetical order. Select the table data without the column headings. Click Sort&Filter, select Sort A to Z.
13. Set your page to print with gridlines and row and column headings.
14. Save the file.
15. Print-preview the file for your teacher.
Have fun, learn lots
-Mr. Hennings
Posted at 10:50 AM | Permalink | Comments (0)
Today you will use an "IF" statement to help calculate the bonus your employees will recieve.
Directions:
1. Open 3.20bonus, which you created earlier, or, Download D3.20bonus.
2. Save the file as 3.23bonus.
3. Enter the new label BONUS in Cell A16, as shown in figure below.
a. Enter an IF statement formula in Cell B16 to compute the BONUS for Ryan.
If his monthly sales are $9,000 or more, compute a 3% bonus; otherwise, compute a 1% bonus.
Click the insert Function button on the Formula bar. Select IF, and then press OK. Enter the arguments in the Function Arguments dialog box and stick OK.
b. Copy the formula to the other salespeople.
4. Apply bold to the bonus amounts.
5. Format the bonus amounts for currency with two decimal places.
6. Sort the employee names in Row 4 alphabetically.
NOTE: Be sure to select the range B4:F14 when sorting this data. When sorting by a row, choose "Custom Sort", then choose "Options..." in the dialog box, select “Sort left to right” and click OK.
Select "Sort by - Row 4", "Order A to Z", and click OK.
7. Copy the worksheet to a new sheet within the same workbook.
Right-Click on the tab name and choose "Move or Copy". Select the "Make Copy" check-box.
8. Delete (clear) December data.
NOTE: "Data" means, just the numbers and dates, not all the formulas, headings, and titles.
9. Use the AutoFill option to enter the dates for January, as shown in Figurebelow.
NOTE:There are only four weeks for January.
10. Enter sales for each salesperson for the first week in January, as shown.
11. Replace the subtitle DECEMBER with JANUARY.
12. Save the file.
13. Name the first tab DECEMBER and the second tab JANUARY. Color the tabs as you prefer.
Right-Click tab name and select "rename"
14. Preview both worksheets.
15. Print one copy of the December worksheet in landscape orientation with gridlines and row and column headings.
16. Save the changes; close the file.
Posted at 04:37 AM | Permalink | Comments (0)
Today we (as a class) are going to create a spreadsheet that utilizes the "IF( )" statement.
We are going to use it to quickly determine whether a student passes or fails based on their earned average.
We can then try to use the IF( ) statement to determine the letter grade each student earns based on their average.
Directions:
Posted at 10:49 AM | Permalink | Comments (0)
1. Download D3.20bonus shown below.
2. Save the ﬁle as 3.20bonus.
3. Use the AutoFill feature to ﬁll in the WEEKLY dates. Select the cells containing the ﬁrst date and second date. (NOTE: The second date should be one week apart from the first date.) Drag the fill handle to the remaining cells.
4. Center the titles across the columns.
5. Adjust column widths to fit the data.
6. Calculate the TOTALS (ROW 12) for each salesperson and for WEEKLY TOTALS.
a. Enter the SUM function in Cell B12 to add the sales for RYAN.
b. Copy the formula from Cell B12 to the range C12:G12.
NOTE: Zeros will appear at the bottom of the WEEKLY TOTALS column until you insert data into the cell.
7. Calculate HIGHEST sales for each salesperson and WEEKLY TOTALS. a. Enter the MAX function in Cell B13 to find the highest sales for RYAN. b. Copy the formula from Cell B13 to the range C13:G13.
8. Calculate LOWEST sales for each salesperson and WEEKLY TOTALS.
a. Enter the MIN function in Cell B14 to ﬁnd the lowest sales for RYAN.
b. Copy the formula from Cell B14 to the range C14:G14.
9. Calculate the WEEKLY TOTALS.
a. Enter the SUM function in Cell G6 to add sales for the week of 12/O3/2007
b. Copy the formula from Cell G6 to the range G7:G10.
10. Calculate HIGHEST WEEKLY sales for each week.
11. Calculate LOWEST WEEKLY sales for each week.
12. Apply an AutoFormat to the worksheet; format all money amounts with a comma and two decimal places, and all totals—including highest and lowest money amounts for currency.
13. Set your page to print with gridlines and row and column headings.
14. Change the font color for WEEK ENDING to green.
15. Change the scale setting to fit the worksheet on one page. Choose the "Page Layout" tab, next to "Width", choose "1 page"
16. Preview the file, and then print one copy.
17. Save the changes; close the file.
Have fun, learn lots
-Mr. Hennings
Posted at 10:48 AM | Permalink | Comments (0)
Budgets are funny in that if you don't use them, you loose them. For this reason people with budgets tend to try to stick very closely to them. You will take this concept to the extreme.
For this challenge you will need two things:
1. This Spreadsheet with item descriptions and per unit costs - Download 3000
2. A working knowledge of spreadsheet formulas
After downloading the spreadsheet document, create whatever formulas you need to help you spend as close to your $3,000 budget without going over.
Yes, it IS possible to find the perfect combination of products and quantities to eaqual EXACTLY $3,000.
Have fun, learn lots, Mr. Hennings
Posted at 08:23 AM | Permalink | Comments (0)
Visual organization is important. For this reason we separate page content into literal and visual compartments.
One way we can do this is bay using the HTML <div></div> tag-pair and adding the CSS attribute id to them.
Below are a few examples of some commonly used layouts. We are going to try several methods of creating these and discuss their pro's and con's.
I will walk the class through creating a few of these and then you will be creating the rest yourself.
Have fun, learn lots
Posted at 09:03 AM | Permalink | Comments (0)
Recent Comments