Select Page

Olivia’s Art Supply Salary Report

by | Apr 6, 2022 | Computer science | 0 comments

For This or a Similar Paper Click To Order Now

Figure 2-1
Worksheet Title Olivia’s Art Supply Salary Report
Needs An easy-to-read worksheet that summarizes the company’s salary report (Figure 2–3). For each employee, the worksheet is to include the employee’s name, email address, number of dependents, hours worked, hourly pay rate, gross pay, federal tax, state tax, total tax percent, net pay, and hire date. The worksheet also should include the total pay for all employees, as well as the highest value, lowest value, and average for each category of data.
Figure 2–3
Source of Data Supplied data includes employee names, number of dependents, hours worked, hourly pay rate, and hire dates.
Calculations The following calculations must be made for each of the employees:
1
2 * (Gross Pay – Number of Dependents * 24.32)
3 * Gross Pay
4
5
6 Compute the totals for hours worked, gross pay, federal tax, state tax, and net pay
7 Compute the total tax percent
8 Use the MAX and MIN functions to determine the highest and lowest values for number of dependents, hours worked, hourly pay rate, gross pay, federal tax, state tax, total tax percent, and net pay
9 Use the AVERAGE function to determine the average for hours worked, number of dependents, hourly pay rate, gross pay, federal tax, state tax, and net pay
In addition, using a sketch of the worksheet can help you visualize its design. The sketch for the Olivia’s Art Supply Salary Report worksheet includes a title, a subtitle, column and row headings, and the location of data values (Figure 2–3). It also uses specific characters to define the desired formatting for the worksheet, as follows:
For an introduction to Windows and instructions about how to perform basic Windows tasks, read the Office and Windows module at the beginning of this book, where you can learn how to resize windows, change screen resolution, create folders, move and rename files, use Windows Help, and much more.
1. The row of Xs below the leftmost column heading defines the cell entries as text, such as employee names.
2. The rows of Zs and 9s with slashes, dollar signs, decimal points, commas, and percent signs in the remaining columns define the cell entries as numbers. The Zs indicate that the selected format should instruct Excel to suppress leading 0s. The 9s indicate that the selected format should instruct Excel to display any digits, including 0s.
3. The decimal point means that a decimal point should appear in the cell entry and indicates the number of decimal places to use.
4. The slashes in the last column identify the cell entry as a date.
5. The dollar signs that are adjacent to the Zs below the totals row signify a floating dollar sign, or one that appears next to the first significant digit.
6. The commas indicate that the selected format should instruct Excel to display a comma separator only if the number has sufficient digits (values in the thousandths) to the left of the decimal point.
7. The percent sign (%) in the Tax % column indicates a percent sign should appear after the number.
In this module, you will learn how to use functions and create formulas. The following roadmap identifies general activities you will perform as you progress through this module:
For an introduction to Office and instructions about how to perform basic tasks in Office apps, read the Office and Windows module at the beginning of this book, where you can learn how to run an application, use the ribbon, save a file, open a file, print a file, exit an application, use Help, and much more.
1. enter formulas in the worksheet.
2. enter functions in the worksheet.
3. verify formulas in the worksheet.
4. format the worksheet .
5. check spelling .
6.
Step 1
* 1 
Run Excel and create a blank workbook in the Excel window.
* 2 
If necessary, select cell A1. Type Olivia’s Art Supply in the selected cell and then press the down arrow key to enter the worksheet title.
* 3 
Type Salary Report in cell A2 and then press the down arrow key to enter the worksheet subtitle.
Next step
* 1 
With cell A3 selected, type Employee and then press the right arrow key to enter the column heading.
* 2 
Type Email Address in cell B3 and then press the right arrow key.
* 3 
In cell C3, type Dependents and then press the right arrow key.
* 4 
In cell D3, type Hours and then press the alt+enter keys to enter the first line of the column heading. Type Worked and then press the right arrow key to enter the column heading.
* 5 
Type Hourly in cell E3, press the alt+enter keys, type Pay Rate, and then press the right arrow key.
* 6 
Type Gross Pay in cell F3 and then press the right arrow key.
* 7 
Type Federal Tax in cell G3 and then press the right arrow key.
* 8 
Type State Tax in cell H3 and then press the right arrow key.
* 9 
Type Tax % in cell I3 and then press the right arrow key.
* 10 
Type Net Pay in cell J3 and then press the right arrow key.
* 11 
Type Hire Date in cell K3 and then press the right arrow key.
Next Step
* 1 
Select cell A4. Type Bennett, Joanne and then press the right arrow key two times to enter the employee name and make cell C4 the active cell.
* 2 
Type 2 in cell C4 and then press the right arrow key.
* 3 
Type 64.25 in cell D4 and then press the right arrow key.
* 4 
Type 19.75 in cell E4.
* 5 
Click cell K4 and then type 4/5/12.
* 6 
Enter the payroll data in Table 2–1 for the eight remaining employees in rows 5 through 12.
Next step
* 1
* Click cell B4 to select it.
* Type jbennett@email.com and then press the down arrow key to select cell B5.
* Type mfred@email.com and then click the Enter button to enter Michael Fred’s email address in cell B5 (Figure 2–4).
* 2
* Click Data on the ribbon to select the Data tab.
* Click Flash Fill (Data tab | Data Tools group) to enter similarly formatted email addresses in the range B6:B12.
* Remove the entries from cells B1 and B2 (Figure 2–5). 


next step
* 1 
Select cell A13. Type Totals and then press the down arrow key to enter a row header.
* 2 
Type Highest in cell A14 and then press the down arrow key.
* 3 
Type Lowest in cell A15 and then press the down arrow key.
* 4 
Type Average in cell A16 and then press the down arrow key (Figure 2–6). 


next step
* 1 
Double-click the Sheet1 tab and then enter Salary Report as the sheet tab name and then press the enter key.
* 2 
Right-click the sheet tab to display the shortcut menu.
* 3 
Point to Tab Color on the shortcut menu to display the Tab Color gallery. Click Green (column 6, row 7) in the Standard Colors area to apply the color to the sheet tab.
* 4 
Save the workbook in your hard drive, OneDrive, or other storage location using Olivia’s Art Supply Salary Report as the file name.
next step
The formulas needed in the worksheet are noted in the requirements document as follows:
1. Gross Pay (column F) = Hours Worked x Hourly Pay Rate
2. Federal Tax (column G) = 0.22 x (Gross Pay – Dependents x 24.32)
3. State Tax (column H) = 0.04 x Gross Pay
4. Tax % (column I) = (Federal Tax + State Tax)/Gross Pay
5. Net Pay (column J) = Gross Pay – (Federal Tax + State Tax)
The gross pay for each employee, which appears in column F, is equal to hours worked in
column D times hourly pay rate in column E. Thus, the gross pay for Joanne Bennett in cell F4
is obtained by multiplying 64.25 (cell D4) by 19.75 (cell E4) or = C4 × D4. The following
steps enter the initial gross pay formula in cell F4 using the keyboard.
With cell F4 selected, type =d4*e4 in the cell to display the formula in the formula bar and the current cell and to display colored borders around the cells referenced in the formula
Press the right arrow key to complete the arithmetic operation indicated by the formula, display the result in the worksheet, and select the cell to the right (Figure 2–8). The number of decimal places on your screen may be different than shown in Figure 2–8, but these values will be adjusted later in this module.
The sketch of the worksheet in Figure 2-3 calls for the federal tax, state tax, tax percentage,
and net pay for each employee to appear in columns G, H, I, and J, respectively. All four of
these values are calculated using formulas in row 4:
A
Federal Tax(cell G4) = 0.22 x (Gross Pay
Dependents x 24.32 or = 0.22 * (F
State Tax(cell H4) = 0.04 × Gross Pay or = 0.04 *F4
Tax % (cell I4) = (Federal Tax + State Tax) /Gross Pay or = (G4 +H4) /F4
Net Pay(cell J4) = Gross Pay –
(Federal Tax + State Tax or = F4 – (G4 +H4)
An alternative to entering the formulas in cells G4, H4, I4, and I4 using the keyboard is to
enter the formulas using the pointer and Point mode. Point mode allows you to select cells for
use in a formula by using the pointer. The following steps enter formulas using Point mode.
With cell G4 selected, type =0 . 22* ( to begin the formula and then click cell F4 to add a
cell reference in the formula
• Type – (minus sign) and then click cell C4 to add a subtraction operator and a reference to
another cell to the formula.
• Type *24.32) to complete the formula
• Click the Enter button in the formula bar and then select cell H4 to prepare to enter the next
formula.
• Type =0.04* and then click cell F4 to add a cell reference to the formula (Figure 2-11)
• Click the Enter button in the formula bar to enter the formula in cell H4.
• Select cell I4. Type = ( (equal sign followed by an open parenthesis) and then click cell G4
to add a reference to the formula.
• Type + (plus sign) and then click cell H4 to add a cell reference to the formula.
• Type ) / (close parenthesis followed by a forward slash), and then click cell F4 to add a cell
reference to the formula.
• Click the Enter button in the formula bar to enter the formula in cell I4 (Figure 2-12).
• Click cell J4, type = (equal sign) and then click cell F4.
• Type – ( (minus sign followed by an open parenthesis) and then click cell G4.
• Type + (plus sign), click cell H4, and then type ) (close parenthesis) to complete the formula
(Figure 2-13).
Click enter button
Next step
Select the source range, F4:J4 in this case, activate the fill handle, drag the fill handle down
through cell J12, and then continue to hold the mouse button to select the destination range.
Release the mouse button to copy the formulas to the destination range (Figure 2-14).
1
Select the cell to contain the sum, cell D13 in this case. Click the Sum button (Home tab | Editing
group) to sum the contents of the range D4:D12 in cell D13 and then click the Enter button to
display a total in the selected cell.
2
Select the range to contain the sums, range F13:H13 in this case. Click the Sum button (Home tab
| Editing group) to display totals in the selected range.
3
Select the cell to contain the sum, cell J13 in this case. Click the Sum button (Home tab | Editing
group) to sum the contents of the range J4:J12 in cell J13 and then click the Enter button to
display a total in the selected cell (Figure 2-15).
Select the cell to be copied, I12 in this case, and then drag the fill handle down through cell 113 to
copy the formula (Figure 2-16).
Next step
• Select the cell to contain the maximum number, cell C14 in this case.
• Click the Insert Function button in the formula bar to display the Insert Function dialog box.
• Click MAX in the Select a function list (Insert Function dialog box; Figure 2-17). You may
need to scroll.
• Click the OK button (Insert Function dialog box) to display the Function Arguments dialog
box.
• Replace the text in the Number1 box with the text, c4 : c12 (Function Arguments dialog
box) to enter the first argument of the function (Figure 2-18).
• Click the OK button (Function Arguments dialog box) to display the highest value in the
chosen range in cell C14 (Figure 2-19).
• Select cell C15 and then click the Sum arrow (Home tab | Editing group) to display the Sum
menu (Figure 2-20)
• Click Min to display the MIN function in the formula bar and in the active cell
Click cell C4 and then drag through cell C12 to update the function with the new range
(Figure 2-22).
• Click the Enter button to determine the lowest value in the range C4:C12 and display the
result in cell C15 (Figure 2-23).
• Select the cell to contain the average, cell C16 in this case.
• Type =av in the cell to display the Formula AutoComplete list. Press the DOWN ARROW key to
highlight the AVERAGE function (Figure 2-24)
• Double-click AVERAGE in the Formula AutoComplete list to select the function.
• Select the range to be averaged, C4:C12 in this case, to insert the range as the argument to
the function (Figure 2-25).
• Click the Enter button to compute the average of the numbers in the selected range and
display the result in the selected cell (Figure 2-26).
Select the source range from which to copy the functions, in this case C14:C16.
2
Drag the fill handle in the lower-right corner of the selected range through cell J16 to copy the three
to the selected range.
3
Select cell I16 and then press the DELETE key to delete the average of the Tax % (Figure 2-27).
Double-click cell I4 to activate Range Finder (Figure 2-28).
• Press the ESC key to quit Range Finder and then click anywhere in the worksheet, such as cell A18, to
deselect the current cell.
• Click Page Layout to display the Page Layout tab.
• Click the Themes button (Page Layout tab | Themes group) to display the Themes gallery (Figure 2-30).
Click Ion in the Themes gallery to change the workbook theme (Figure 2-31).
2
Select the range to be merged, Al:K1 in this case, and then click the ‘Merge & Center’ button (Home tab
Alignment group) to merge and center the text in the selected range.
3
Select the range A2:K2 and then click the Merge & Center’ button (Home tab | Alignment group) to merge
and center the text.
4
Select the range to contain the Title cell style, in this case Al:A2, click the Cell Styles button (Home tab |
Styles group) to display the Cell Styles gallery, and then click the Title cell style in the Cell Styles gallery to
apply the Title cell style to the selected range.
15
Select cell A2 and then click the ‘Decrease Font Size’ button (Home tab | Font group) to decrease the font size
of the selected cell to the next lower font size (Figure 2-32).
• Select the range A1:A2 and then click the Fill Color arrow (Home tab | Font group) to display the Fill
Color gallery (Figure 2-33).
Click Blue-Gray, Accent 5, Lighter 60% (column 9, row 3) in the Theme Colors area to change the
background color of the range of cells (Figure 2-34).
Click the Borders arrow (Home tab | Font group) to display the Borders gallery (Figure 2-35).
• Click “Thick Outside Borders’ in the Borders gallery to create a thick outside border around the selected
range.
• Click anywhere in the worksheet, such as cell A18, to deselect the current range (Figure 2-36).
1
Select the range to be formatted, cells A3:K3 in this case.
12
Use the Cell Styles gallery to apply the Heading 3 cell style to the range A3:K3.
3
Click the Center button (Home tab | Alignment group) to center the column headings.
4
Apply the Total cell style to the range A13:K13.
5
Bold the range A14:A16 (Figure 2-37).
1
• Select the range to contain the new date format, cells K4:K12 in this case.
• Click the Format Cells: Number Format Dialog Box Launcher (Home tab | Number group) (shown in
Figure 2-37) to display the Format Cells dialog box.
• If necessary, click the Number tab (Format Cells dialog box), click Date in the Category list, and then
click 03/14/12 in the Type list to choose the format for the selected range (Figure 2-38).
1
• Select the range to contain the new date format, cells K4:K12 in this case.
• Click the Format Cells: Number Format Dialog Box Launcher (Home tab | Number group) (shown in
Figure 2-37) to display the Format Cells dialog box.
• If necessary, click the Number tab (Format Cells dialog box), click Date in the Category list, and then
click 03/14/12 in the Type list to choose the format for the selected range (Figure 2-38).
Click the OK button (Format Cells dialog box) to format the dates in the current column using the
selected date format style.
3
• Select the range C4:C16 and then click the Center button (Home tab | Alignment group) to center the
data in the selected range.
• Select cell E4 to deselect the selected range
Select the range to contain the accounting number format, cells E4:H4 in this case.
12
While holding down the CrRI key, select cell J4, the range F13:H13, and cell J13 to select the nonadjacent
ranges and cells.
13
Click the ‘Accounting Number Format’ button (Home tab | Number group) to apply the accounting number
format with fixed dollar signs to the selected nonadjacent ranges.
Select the ranges to contain the comma style format, cells ES:H12 and J5:J12 in this case.
15
Click the Comma Style button (Home tab | Number group) to assign the comma style format to the selected
ranges.
6
Select the range D4:D16 and then click the Comma Style button (Home tab | Number group) to assign the
comma style format to the selected range
• Select the ranges (E14:H16 and J14:J16) and then click the Number Format Dialog Box Launcher
(Home tab | Number group) to display the Format Cells dialog box.
If necessary, click the Number tab to display the Number sheet (Format Cells dialog box).
• Click Currency in the Category list to select the necessary number format category and then click the
third style ($1,234.10) in the Negative numbers list to select the desired currency format for negative
numbers (Figure 2-41).
Click the OK button (Format Cells dialog box) to assign the currency style format with a floating dollar sign to the selected ranges
• Select the range to format, cells I4:115 in this case.
• Click the Percent Style button (Home tab | Number group) to display the numbers in the selected range
as a rounded whole percent.
• Click the Increase Decimal button (Home tab | Number group) two times to display the numbers in the
selected range with two decimal places (Figure 2-43).
Select the range D4:D12.
• Click the Conditional Formatting button (Home tab | Styles group) to display the Conditional Formatting
menu (Figure 244)
2
• Click New Rule on the Conditional Formatting menu to display the New Formatting Rule dialog box.
• Click ‘Format only cells that contain’ in the Select a Rule Type area (New Formatting Rule dialog box)
to change the Edit the Rule Descriiption area.
• In the Edit the Rule Descriiption area, click the arrow in the relational operator box (second box) to
display a list of relational operators, and then select greater than to select the desired operator.
• Select the rightmost box, and then type 7 2 to enter the value of the rule descriiption (Figure 2-45).
• Click the Format button (New Formatting Rule dialog box) to display the Format Cells dialog box.
• If necessary, click the Font tab (Format Cells dialog box) to display the Font sheet. Click the Color arrow
to display the Color gallery and then click White, Background 1 (column 1, row 1) in the Color gallery
to select the font color (Figure 2-46).
• Click the Fill tab (Format Cells dialog box) to display the Fill sheet and then click the blue color in
column 9, row 1 to select the background color (Figure 2 47).
• Click the OK button (Format Cells dialog box) to close the Format Cells dialog box and display the New
Formatting Rule dialog box with the desired font and background colors displayed in the Preview area
(Figure 2 48).
Click the OK button (New Formatting Rule dialog box) to assign the conditional format to the selected
range.
• Click anywhere in the worksheet, such as cell A18, to deselect the current range (Figure 2 49).
• Drag through column headings A, B, and C above row 1 to select the columns.
• Point to the boundary on the right side of column heading C to cause the pointer to become a split double
arrow (Figure 2-50).
• Double-click the right boundary of column heading C to change the width of the selected columns to
best fit.
• Point to the right boundary of the column H heading above row 1.
• When the pointer changes to a split double arrow, drag until the ScreenTip indicates Width: 10.25 (87
pixels). Do not release the mouse button (Figure 2-51).
• Release the mouse button to change the column width.
• Click the column D heading above row 1 to select the column.
• While holding down the CTRI key, click the column E heading and then the column I heading above row
1 so that nonadjacent columns are selected.
• Point to the boundary on the right side of the column I heading above row 1.
• Drag until the ScreenTip indicates Width: 7.50 (65 pixels). Do not release the mouse button (Figure 2-
52).
• Release the mouse button to change the column widths.
• Click the column F heading and drag to select the column G heading.
While holding down the CTRI key, click the column J heading and drag to select the column K heading
above row 1 so that nonadjacent columns are selected.
• Drag the right boundary of column G until the Screen Tip indicates Width: 11.00 (93 pixels). Release the
mouse button to change the column widths.
• Click anywhere in the worksheet, such as cell A18, to deselect the columns (Figure 2-53).
• Point to the boundary below row heading 3 until the pointer becomes a split double arrow.
• Drag down until the Screen Tip indicates Height: 48.00 (64 pixels). Do not release the mouse button
(Figure 2-54).
• Release the mouse button to change the row height.
• Point to the boundary below row heading 14 until the pointer becomes a split double arrow and then drag
downward until the ScreenTip indicates Height: 27.00 (36 pixels). Do not release the mouse button
(Figure 2-55).
• Release the mouse button to change the row height.
• Click anywhere in the worksheet, such as cell A18, to deselect the current cell (Figure 2-56)

 

Place your order now for a similar assignment and have exceptional work written by our team of experts, At affordable rates

For This or a Similar Paper Click To Order Now

Our confidential essay writing service protects your privacy

We cherish your trust and do our best to protect you. Anything that you share with our private essay writing service is never misused as the access to your personal data is very limited. Your payment details and credit card number also fall under strict protection when you buy a custom essay online from us.