Microsoft Office Tutorials and References
In Depth Information
Excel Chapter 3 What-If Analysis, Charting, and Working with Large Worksheets
In the Lab continued
18. An employee pays Social Security tax only if his or her YTD Soc. Sec. in column D is less than the
Maximum Social Security value in cell B3. Use the following logic to determine the Social Security
tax for Raul Aquire in cell G8 and then copy it to the range G9:G14.
Soc. Sec. (cell G8): If Social Security Tax * Gross Pay + YTD Soc. Sec. > Maximum Social
Security, then Maximum Social Security
YTD Soc. Sec., otherwise Social Security Tax * Gross
D8, $B$1 * F8)
19. In cell H8, enter the following formula and then copy it to the range H9:H14:
Medicare (cell H8) = Medicare Tax * Gross Pay or =$B$2 * F8
20. In cell K8, enter the following formula and copy it to the range K9:K14:
Pay or =IF($B$1 * F8 + D8 >= $B$3, $B$3
Net Pay (K8) = Gross Pay
(Soc. Sec. + Medicare + Federal Tax + State Tax) or =F8
(G8 + H8 +
I8 + J8)
21. In cell L8, enter the following formula and copy it to the range L9:L14:
% Taxes (cell L8) = (Soc. Sec. + Medicare + Federal Tax + State Tax) / Gross Pay or = (G8 + H8 +
I8 + J8) / F8
22. Use the Range Finder (double-click cell) to verify the new totals as shown in row 15 in Figure 3-87b.
Unfreeze the worksheet by clicking the Freeze Panes button on the View tab on the Ribbon, and
then clicking Unfreeze Panes on the Freeze Panes menu.
23. Preview the worksheet. Use the Page Setup button to change the orientation to landscape and ﬁ t
the report on one page.
24. Change the document properties, as speciﬁ ed by your instructor. Change the worksheet header
with your name, course number, and other information requested by your instructor. Save
the workbook using the ﬁ le name Lab 3-2 Britney’s Music Emporium Weekly Payroll Report
25. Use the Zoom button on the View tab on the Ribbon to change the view of the worksheet. One by
one, select all the percents on the Zoom dialog box. When you are done, return the worksheet to
100% magniﬁ cation.
26. Preview the formulas version ( CTRL +` ) in landscape orientation. Close the worksheet without
saving the latest changes.
27. Submit the workbook as requested by your instructor.
Instructions Part 2: Start Excel. Open Lab 3-2 Britney’s Music Emporium Weekly Payroll Report
Complete. Using the numbers in Table 3–13, analyze the effect of changing the Medicare tax in cell B2.
The ﬁ rst case should result in a total Medicare tax in cell H15 of $106.78. The second case should
result in a total Medicare tax of
$166.73. Close the workbook without
saving changes. Submit the results of
the what-if analysis as requested by
Table 3–13 The Britney Music Emporium’s Medicare Tax Cases
Instructions Part 3: Submit results for
this part as requested by your instructor.
1. Start Excel. Open Lab 3-2 Britney’s Music Emporium Weekly Payroll Report Complete. Select
cell F8. Write down the formula that Excel displays in the formula bar. Select the range C8:C14.
Point to the border surrounding the range and drag the selection to the range D17:D23. Click
cell F8, and write down the formula that Excel displays in the formula bar below the one you wrote
down earlier. Compare the two formulas. What can you conclude about how Excel responds when
you move cells involved in a formula? Click the Undo button on the Quick Access Toolbar.