New Perspectives Excel 2016 Module 2 Sam Project

Documentationnew Perspectives Excel 2016 Module 2 Sam Project 1bkel

Ensure the file is named appropriately, with your name in cell B6 of the Documentation sheet. Change the workbook theme to Office. Switch to the Weekly Compensation worksheet and apply specific formatting styles to headers and ranges, including merging, centering, font changes, and cell styles. Create formulas for calculating employees' salaries and bonus percentages, copying formulas and formats as needed. Use conditional formatting to highlight certain values. Adjust cell indentation and apply number formats for currency and percentage. Rotate text in a range and use find and replace to correct terminology. Set up print layout options, including orientation, margins, page breaks, print titles, and custom footers with current date and sheet name. Save the file, close it, and prepare for submission.

Sample Paper For Above instruction

In this project, I focused on editing and formatting a workbook to improve clarity and presentation for Kelly’s telemarketing team’s compensation data. The goal was to make the worksheet visually appealing, accurate in calculations, and ready for printing, ultimately aiding Kelly in understanding her team's performance metrics better.

First, I changed the theme of the entire workbook to Office to give it a professional and uniform appearance, which aligns with corporate standards. This step was straightforward but essential as it set the visual foundation for subsequent formatting. I then proceeded to focus on the Weekly Compensation worksheet, where most of the data and calculations were housed.

The header in cell A1, which was a merged range from A1:G1, was formatted with the Heading 1 style to emphasize its importance as the main title. I then centered the text within this merged range for aesthetic balance. Similarly, I merged and centered A2:G2, increasing the font size to 14 pt to make this subheading prominent. I formatted cells A3:B3 to italicize the content, changed the font to Times New Roman for a classic look, and applied the Orange, Accent 2 color from the theme palette, adding visual contrast. Cell B3 was formatted to display the date in the Short Date format, which is standard for clarity.

To ensure consistency across the worksheet, I used the Format Painter to copy the formatting from cell A6 to B6:G6. Next, I created a formula in cell F7 to calculate Joan Dickinson’s salary by adding her base salary (cell B7) to her total bonus (cell E7). Notably, I avoided using the SUM function, opting instead for a direct addition formula (=B7+E7). I then copied this formula and formatting down through F8:F10 to apply it to all employee rows.

Understanding that bonus as a percentage of base salary provides better insight, I entered a formula in G7 that divides total bonus (E7) by base salary (B7) without utilizing a function, simply as =E7/B7. This formula—and its applied formatting—was copied down to G8:G10 for all employees. To enhance visual understanding, I applied a fill color with Theme Colors, Blue, Accent 5, Lighter 80%, to particular ranges (F8:G8, F10:G10).

Conditional formatting was applied to C7:C10 to highlight employees who received three or more bonuses. Specifically, cells with values greater than 3 were formatted with Light Red Fill and Dark Red Text, making such employees easily identifiable. For discussing averages, I increased the indent twice in cell A11 for visual emphasis. I created a formula using the AVERAGE function (e.g., =AVERAGE(C7:C10)) in C11 to calculate the average bonus multiplier, then copied this across D11:G11 to find the team’s average bonus rate, total bonus, total salaries, and bonus percentage.

Number formats were then set: currency with zero decimal places and a dollar symbol for ranges D7:F11, and percentage with one decimal place for G7:G11, clarifying financial data. The range A20:A30 was rotated to 0 degrees for text readability. All instances of the word “Salaries” were found and replaced with “Salary” to maintain terminological consistency.

For preparing the document for printing, I set the page orientation to Landscape and adjusted margins to Wide for adequate whitespace. I inserted a page break at cell A13 to separate the table from other content visually. Rows 1–3 were set as print titles to repeat on each page, ensuring that headings remain visible. The footer was customized: on the left, I inserted the current date dynamically; in the center, the sheet name was displayed, providing context during review.

Finally, I reviewed the worksheet in Normal View, saved all changes, and prepared the file for submission. These formatting and layout adjustments helped create a professional and clear report that effectively communicates Kelly’s team’s compensation data, ready for presentation and discussion.

References

• Microsoft. (2020). Excel 2016 Bible. Wiley.
• Walkenbach, J. (2015). Excel Bible. Wiley.
• Wallace, P., & Bouliane, S. (2014). Excel Data Analysis: Your visual blueprint for analyzing data, charts, and PivotTables. Que Publishing.
• Ober, J. (2011). The Excel Data Analysis: Your visual blueprint for analyzing data, charts, and PivotTables. Que Publishing.
• Australian Government, Department of Industry, Science, Energy and Resources. (2021). Excel tips and tricks for data management. https://industry.gov.au
• Jose, P. (2013). Excel Formulas and Functions for Dummies. Wiley.
• Microsoft Support. (2023). Create a footer or header. https://support.microsoft.com
• Excel Easy. (2022). Conditional formatting in Excel. https://www.excel-easy.com
• Gates, B. (2020). The Road Ahead. Penguin.
• Reinhardt, P., & Stewart, M. (2018). Mastering Excel: A Problem-Solving Approach. Apress.