Showing posts with label Microsoft Excel Assignments. Show all posts

NP_Excel2013_T3_P1b_FirstNameLastName_2 Final Exam Part 2

New Perspectives Excel 2013

Tutorial 3: SAM Project 1b

Gallokol Dance Complex

ENHANCE A TIMESHEET

PROJECT DESCRIPTION

Tim Gallagher is a volunteer with Gallokol Dance Complex, a nonprofit organization thatrents and maintains facilities for community dance classes in Springfield, Missouri. He has created a workbook for logging the hours that the complex's rooms are booked. He would like your help finishing the log.

GETTING STARTED

· Download the following file from the SAM website:

o NP_Excel2013_T3_P1b_FirstLastName_1.xlsx

· Open the file you just downloaded and save it with the name:

o NP_Excel2013_T3_P1b_FirstLastName_2.xlsx

o If you do not see the .xlsx file extension in the Save file dialog box, do not type it. Excel will add the file extension for you automatically.

· With the file NP_Excel2013_T3_P1b_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet. If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

PROJECT STEPS

1. On the RoomListworksheet, select cells A2:A10 and then use AutoFill to apply the format only to the range B2:B10.

2. In cell B3 enter ah, in cell B4 enter bm, in cell B5 type lt, and then use Flash Fill to automatically fill in the values for the range B6:B10.

3. On the Room Rental Log worksheet, merge and center the range A1:C1, and then format the range using the Heading 1Cell Style (1^st style in the Titles and Headings section of the Cell Styles palette).

4. Merge and center the range A2:C2, and then change the font to Verdana and the font size to 12pt.

5. Center andthenformat the range A10:C10 using the Heading 2Cell Style (2^ndstyle from the left in the Titles and Headings section of the Cell Styles panel).

6. In cell B5, create a formula using the VLOOKUP function to look up the value of cell B4 (in the current worksheet) in the range A3:B10 on the Room List worksheet and then return the value in the second column of that range. Use FALSE as value of the Range_Lookup argument to specify an exact match lookup.

7. In cell B7 of the Room Rental Log worksheet, use the TODAY function to display the current date.

8. Use AutoFill to fill the range A12:A25 based on cell A11.

9. In cell C11, create a formula that uses the IF function to check if the value in cell B11 is equal to 0.

a. If this condition is true, the current cell should be made empty (equal to "").

b. If this condition is false, the current cell should display the result of multiplying cell B11 (hours booked) by cell B8 (hourly fee). Use a relative reference to cell B11 and an absolute reference to cell B8 so that the formula can be copied to other cells.

10. Copy the formula from cell C11 to all cells in the range C12:C25.

11. In cell B26, create a formula that uses the SUM function to total cells B11:B25.

12. In cell C26, enter a formula that uses the SUM function to total cells C11:C25.

13. Format B11:B26 with the Numberstyle, showing two decimal places.

14. Format cell B8 with the Currency Number style, showing two decimal places, and then apply the same formatting to the range C11:C26.

15. Format the range A26:C26 as bold and then add an Outside border around this range.

16. In cell B28, create a formula that uses the MAX function to display the largest value from the range B11:B25.

17. In cell B29, create a formula that uses the MIN function to display the smallest value from the range B11:B25.

18. In cell B30, create a formula that uses the AVERAGE function to display the average of the values in the range B11:B25.

19. In cell B31, create a formula that multiplies the value in cell B30 by 7 (or the number of days in a week).

Your workbookshould look like the Final Figure on the following pages. Save your changes, close the workbook, and exit Excel. Follow the directions on the SAM website to submit your completed project.

Final Figure 1: Room Rental Log Worksheet

Final Figure2: Room List Worksheet

NP_Excel2013_T11_P1a_FirstnameLastname_2

New PerspectivesExcel 2013

Tutorial11: SAM Project 1a

SafeClean Products

USING data from external files

New PerspectivesExcel 2013

Tutorial11: SAM Project 1a

SafeClean Products

USING data from external files

Project Goal

M Project Name

Project Goal

PROJECT DESCRIPTION

Fiona Walsh works forSafeClean Products, a publically-traded cleaning products company in New York.The company maintains numerous files and databases to trackcustomer orders,sales commissions, employee data, and market data. Fiona has asked you to create a comprehensive workbook that combines all of the data into a single file. You will need to import andformat data from numerous types of data files, including Access databases, text files, and XML files.

GETTING STARTED

· Download the following file from the SAM website:

o NP_Excel2013_T11_P1a_FirstLastName_1.xlsx

· Open the file you just downloaded and save it with the name:

o NP_Excel2013_T11_P1a_FirstLastName_2.xlsx

o Hint: If you do not see the .xlsx file extension in the Save file dialog box, do not type it. Excel will add the file extension for you automatically.

· To complete this SAM Project, you will also need to download and save the following data files from the SAM website onto your computer:

o support_NP_E13_T11_P1a_10Year_Stock.txt

o support_NP_E13_T11_P1a_Employees.xml

o support_NP_E13_T11_P1a_Employees.xsd

o support_NP_E13_T11_P1a_SafeClean.accdb

o support_NP_E13_T11_P1a_Sales.txt

o support_NP_E13_T11_P1a_SCOrders.html

o support_NP_E13_T11_P1a_Stock.txt

· With the file NP_Excel2013_T11_P1a_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet. If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

· If a security warning appears at the top of the Excel window, click the Enable Content button in the message bar to enable the macros contained in the file, see Figure 1.

Figure 1: Security Warning Message Bar

· To complete this project you will need to display the Developer tab. To add this tab to the Excel ribbon, click on the File tab to open Backstage view and then click the Options button. In the Excel Options dialog box, click on the Customize Ribbon option and click the Developer check box, see Figure 2. Click the OK button to close the Excel Options dialog box and confirm the Developer tab appears in the Excel Ribbon.

Microsoft product screenshots used with permission from Microsoft Corporation. Copyright © 2014 Cengage Learning. All Rights Reserved.

Figure 2: Customize Ribbon Section of Excel Options Dialog Box

PROJECT STEPS

1. Go to the Overview worksheet. Import data from the text file support_NP_E13_T11_P1a_Sales.txt into the worksheet using the following parameters:

a. The data in the text file has headers and is delimited with a comma.

b. Apply the Text column data format to the first column and the General format to the remaining columns.

c. Do not import the second columnof data (with the column heading 2012).

d. Import the data into the Overview worksheet, starting in cell B4.

2. Import data from the text file support_NP_E13_T11_P1a_10Year_Stock.txtinto the Overview worksheet using the following parameters:

a. The data has fixed width columns with no headers.

b. Begin importing the data at the 6 row.

c. Place column breaks at the following marks on the ruler: 4, 9, and 14 (as shown in Figure 3).

Figure 3: Column Breaks for Support_NP_E13_T11_P1a_10Year_Stock.txt

d. Apply the Date column data format to the first column and the General format to the remaining columns.

e. Import the data into the existing worksheet (the Overview worksheet), starting in cell B12.

3. Format the Overview worksheet as follows:

a. Adjust the width of column B to 4.5 characters.

b. Change the width of columns C:G to 12 characters.

c. For range C4:G4, Center cell contents and apply Bold formatting.

d. Apply the Accounting number format with no decimal placesto range C5:G8.

4. Insert a High-Low-Close chartinto the Overview worksheet, based on range P3:S286. (Hint: In most cases, selecting cell P4prior to creating the chart will result in Excel automatically selecting the necessary data range for your chart.) Format the chart as described below:

a. Change the chart title to SafeClean Products - Stock Trends 2017.

b. Remove the chart legend.

c. For the vertical axis, modify the Minimum bounds to be 35 and the Major units to be 5.

d. Enter the text Stock Price as the Primary Vertical axis title.

e. Enter the text Dates as the Primary Horizontal axis title.

f. Resize and reposition the chart so the upper-left corner is in cell F10 and the lower-right corner is in cell O29.

5. Select cell P5 and edit the External Data Rangeproperties as follows (Hint: Remember to edit the External Data Range properties, not the Connection properties):

a. Rename the support_NP_E13_T11_P1a_Stock external data range to be SafeClean 2017 Stock Performance.

b. Modify the Refresh control property to Prompt for File name on refresh.

c. Modify the Data Formatting and Layout properties to Adjust column width.

6. Go to the FY18 Earningsworksheet. Import data from the support_NP_E13_T11_P1a_SafeClean.accdb database, using the Microsoft Query Wizard as follows(Hint: When importing this data into the workbook, use the From Other Sources button to access the Query Wizard):

a. In the Microsoft Query wizard, choose the MS Access Database* option as the data source, and then navigate to and select the support_NP_E13_T11_P1a_SafeClean.accdb database.

b. When adding columns to your query, select the CustomerName and Contact columns (in that order) from the Customer table.

c. Then select the EmployeeLastName column from the Employees table.

d. Then select the Product and Amount fields from the Orders table (Hint: The columns in your query should be in the following order: CustomerName, Contact, EmployeeLastName, Product, and Amount).

e. Do not filter the data.

f. Sort the data by the Product field in Ascendingorder and then by the Amount field in Ascending order.

g. Return the data to Microsoft Excel as a Table in the existing worksheet (the FY18 Earnings worksheet), starting in cell B4.

h. Rename the table FY18_Q1Earnings.

i. Change the number format of the range F5:F13 to the Accounting number format with 0 decimal places.

j. Confirm the table matches Figure 4.

Figure 4: FY18_Q1Earnings

7. Modify the properties of the connection (created in step 6) in the FY18 Earningsworksheet as follows:

a. In the Workbook Connections dialog box, select the connection created in step 6. The connection should be named Query from MS Access Database (Hint: If necessary, use the Click here to see where the selected connections are used link to confirm the selected connection has a sheet value FY18 Earnings).

b. Change the name of the connection to SafeCleanFY18 Q1 Earnings.

c. Change the connection description to SafeClean Products FY18 Q1 Earnings imported from the SafeClean database. (include the period).

d. For the Refresh Control options in the Usage tab, uncheck all checkboxes associated with the Last Refreshed property.

8. Make a copy of the FY18 Earningsworksheet and update the worksheet as follows:

a. If necessary, move the new worksheet so that it appears between the FY18 Earningsworksheet and the FY18 Ordersworksheet.

b. Update the worksheet tab to read FY18 Commissions.

c. Update cell A1 to read SafeClean Products - Fiscal Year 2018 - Commissions.

d. Change the name of the table in the worksheet to FY18_Q1Commissions.

9. Modify the connection associated with the FY18 Commissions worksheet (which is a copy of the connection from the FY18 Earningsworksheet) as follows:

a. Open the Workbook Connections Dialog box and select the connection associated with the FY18 Commissions worksheet created in step 8. The connection should be named SafeCleanFY18 Q1 Earnings1 (Hint: If necessary, use the Click here to see where the selected connections are used link to confirm the selected connection has a sheet value FY18 Commissions).

b. Change the name of the connection to SafeCleanFY18 Q1 Commissions.

c. Change the connection description to SafeClean Products FY18 Q1 Commissions imported from the SafeClean database. (include the period).

d. Do not close the Connection Properties Dialog box.

10. With the SafeClean FY18 Q1 Commissions connection still open in the Connection Properties dialog box, select the Definition tab and edit the connection query as follows:

a. Remove the Contact column from the query.

b. From the Employeestable, add the EmployeeFirstName column to the query. Move the EmployeeFirstNamecolumn up in the query, so that it appears between the CustomerName and EmployeeLastName columns.

c. From the Orderstable, add the Commission columnto the query (Hint: The columns in your query should be in the following order: CustomerName, EmployeeFirstName, EmployeeLastName, Product, Amount, andCommission).

d. Add a filter to the query, so that only data with a Commission column value greater than 1250.0000 are displayed in the table.

e. Change the sort order of the query to sort only by the Commission column values in Ascending order.

f. Return the data to Microsoft Excel (Hint: You will need to close the Connections Properties Dialog box and the Workbook Connections Dialog box before returning to the FY18 Commissions worksheet).

g. If necessary, change the number format of the rangesE5:E9and G5:G9 to the Accounting number format with 0 decimal places. Be certain to confirm that the number format uses 0 decimal places, rather than the default2 decimal places. (Hint: Depending on how you complete this substep, the number format may appear as Custom instead of Accounting.)

h. Confirm the table matches Figure 5.

Figure 5: FY18_Q1Commissions

11. Go to the FY18Orders worksheet. Create a PivotTable report based on the tables in the support_NP_E13_T11_P1a_SafeClean.accdb database as described below:

a. From the support_NP_E13_T11_P1a_SafeClean.accdb database, import data from the Customers, Employees, and Orders table for the PivotTablereport. (Hint: Use the From Access option in the Get External Data section of the Data tab when importing the tables from the support file.)

b. Add the PivotTable report to cell B4 in the existing (FY18 Orders)worksheet.

c. From the Employees table, add the EmployeeLastName field to the Columns area of the PivotTable Fields Pane.

d. From the Orders table, drag the Product fieldto the Rows area of the PivotTable Fields Pane.

e. From the Customers table, drag the CustomerName field to the Rows area below the Product field.

f. From the Orders table, drag the Quantity field to the Values areaof the PivotTable Fields Pane (Hint: The field name will be updated to Sum of Quantity).

g. Change the width of column B to 33 characters.

h. Change the label in cell B4 to Products Sold by Sales Team.

i. Change the label in cell B5 to Product and Customer Name.

j. Change the label in cell C4 to Employee.

k. Confirm the PivotTable matches Figure 6.

Figure 6: FY18 Orders PivotTable

12. Go to the Order Detailsworksheet. Import data from the support_NP_E13_T11_P1a_SCOrders.htmlsupport file as follows:

a. Get the external data from the support_NP_E13_T11_P1a_SCOrders.htmlsupport file using the From Web option.

b. In the new Web Query dialog box, enter the path to the support_NP_E13_T11_P1a_SCOrders.htmlsupport file into the address box (Hint: The path is the location on your computer that the support file is stored).

c. In the New Query Dialog box, select the second arrow (as shown in Figure 7) so that only the SafeClean table is highlighted.

Figure 7: Portion of New Web Query Dialog Box

d. Import the data into cell B4 of the Order Details worksheet.

e. Open the Workbook Connections Dialog box and select the connection associated with the Order Detailsworksheet. The connection should be named Connection. (Hint: If necessary, use the Click here to see where the selected connections are used link to confirm the selected connection has a sheet value Order Details.)

f. Change the name of the connection to SafeCleanOrder Status.

g. Confirm your worksheet matches Figure 8.

Figure 8: Portion of New Web Query Dialog Box

13. Go to the Payroll worksheet and complete the following actions to import data from the support_NP_E13_T11_P1a_Employees.xml support file. (Hint: The support_NP_E13_T11_P1a_Employees.xsd file needs to be in the same directory as the support_NP_E13_T11_P1a_Employees.xmlfile when you add the .xml map to the workbook.)

a. In the XML Source pane, add the Employees data map from thesupport_NP_E13_T11_P1a_Employees.xmlsupport file to the workbook.

b. Rename the XML Map from dataroot_Map to Payroll Information.

c. Bind the XML element (under the Employees data map) to the Payroll worksheet as shown in Table 1.

Table 1: XML Mapping for the Payroll Worksheet

Element	Cell
ID	B4
Last Name	C4
First Name	D4
Title	E4
Start Date	F4
Status	G4
Compensation Type	H4
Total Compensation	I4

d. Select cell B5 and only refresh the data associated with the XML data source (Hint: The Refreshoption is available in a drop-down menu accessed by clicking on the Refresh Alltext. If you click on the Refresh All icon in the Connections section of the Data tab, Excel will not provide you with the option to only update the data associated with this XML data source.

Your workbook should look like the Final Figure on the following page. Save your changes, close the workbook, and exit Excel. Follow the directions on the SAM website to submit your completed project.

Final Figure 1: Overview Worksheet

Final Figure 2: FY18 Earnings Worksheet

Final Figure 3: FY18 Commissions

Final Figure 4: FY18 Orders Worksheet

Final Figure 5:Order Details Worksheet

Final Figure 6: Payroll Worksheet

NP_Excel2013_CS_T5-8_P1a _FirstNameLastName

PROJECT DESCRIPTION
Theresa Pratt works in the business office at Mills College, a technical college in Council Bluffs, Iowa. She has created a workbook to store and analyze registration and employee data. The workbook is composed of several individual worksheets. Theresa has asked you to help her work with table data, create advanced formulas, and build macros.

PROJECT STEPS
1. Switch to the Summary worksheet. Add a comment to the merged range B5:C5 that reads Use these figures in annual performance review. (including the period). Erase any other text that appears in the comment, including your name.
2. In cell B7, enter a formula using the SUM function that totals the value of cell B7 from each of the following workbooks: Qtr1, Qtr2, Qtr3, Qtr4. Copy the formula from cell B7 into the range B7:C9.
3. Add defined names to the Summary worksheet as described below:
a. Select the range A7:C9 and create names for the selection using the left column option.
b. Add the defined name Iowa_Total to cell B10.
c. Add the defined name Nebraska_Total to cell C10.
4. Group the Summary, Qtr1, Qtr2, Qtr3, and Qtr4 worksheets. Format the grouped worksheets as described below:
5. Make a copy of the Qtr4 worksheet and name it Qtr4-Revised. Move the Qtr4-Revised worksheet between the Qtr4 worksheet and the Calls worksheet. Switch to the Qtr4-Revised worksheet. Edit the content of the merged range B5:C5 to read Quarter 4 - Revised. (Hint: Do not use a period in the name of the new worksheet or in the range B5:C5.)
6. View the Qtr4 worksheet in Normal view and add external references to the worksheet as described below:
External reference to support_NP_E13_CS_T5-8_P1a_Iowa.xlsx
7. Switch to the Calls worksheet. Create a table based on the range A1:E48 that contains headers, and then update it as described below:
a. Change the name of the table to Qtr1Calls.
b. Apply the Table Style Medium 2 table style to the table.
8. Edit the record shown in row 43 of the Qtr1Calls table so that it has a State field value of Iowa.
9. Switch to the EmployeeSubtotal worksheet. Freeze the top row (row 1) of the worksheet.
10. Insert subtotals into the EmployeeSubtotal worksheet as described below:
11. Switch to the EmployeeList worksheet and complete the following actions:
. Apply a conditional formatting rule to the range A3:A102 that will highlight any Duplicate values using a conditional formatting color option of your choice. (Hint: Two values in the table will be highlighted.)
a. Change the Emp ID field value for the employee with the last name Ralston to 1011, so that the conditional formatting rule no longer highlights any value in column A. Do not clear the conditional formatting rule from the worksheet.
12. Sort the table by the Job Status field value in ascending order, then by the Pay Grade field values in ascending order, and finally by the Current Salary field value from Largest to Smallest.
13. Add a hyperlink to cell H2 in a section of the workbook as described below:
14. In cell K3, enter a formula using the COUNTIF function that will count the number of employees with a Job Status field value of FT. Use a structured reference to the Job Status column as the range parameter in your formula.
15. In cell K4, enter a formula using the AVERAGEIF function that will determine the average salary (based on the Current Salary field values) for the employees with a Job Status field value of FT. Use structured references to the Job Status column and Current Salary column in your formula.
16. Create a new PivotTable (not a recommended PivotTable) based on the EmployeeList table. The PivotTable should be placed in a new worksheet titled EmployeeListPivot between the EmployeeSubtotal and EmployeeList worksheets. Update the PivotTable as described in the following steps so that it matches Figure 3 below:
17. Switch to the EmployeeBenefits worksheet. Add a Total Row to the EmployeeBenefits table. Using the total row, add the following calculations to the table:
.
18. In cell N2, enter a formula using the IF and OR functions to determine whether an employee will earn a bonus vacation week. A bonus vacation week is awarded to employees based on their job status (shown in Column G) or years of service (shown in column M). Use structured references and the following parameters when creating this formula:
19. In cell O2, enter a formula using nested IF functions to calculate the maximum amount of 401(k) contributions that the company will match for an employee. The company's 401(k) matching plan is based on an employee's job status (shown in column G) and years of service (shown in column M). Use the following parameters and noted reference types when creating this formula:
a. If an employee's Job Status field value is equal to FT, the employee is eligible for the 401(k) matching program. Use a structured reference to the Job Status field in the formula.
b. If the FT employee also has a Years Service field value greater than or equal to 15, the Max 401(k) Company Match amount is calculated by multiplying the employee's Current Salary by the value in cell T3. Use structured references to the Years Service and Current Salary fields and an absolute reference to cell T3 in the formula.
c. If the FT employee does not have a Years Service value greater than or equal to 15, the Max 401(k) Company Match amount is calculated by multiplying the employee's Current Salary by the value in cell T4. Use structured references to the Years Service and Current Salary fields and an absolute reference to cell T4 in the formula.
d. If the employee's Job Status field value is not equal to FT, the formula should return a value of 0.
The formula should automatically fill into the range O2:O101. (Hint: If the formula does not fill into that range, copy the formula from cell O2 to the range O3:O101.)
20. In cell P2, enter a formula using the VLOOKUP function to determine the cost of each employee's medical plan as described below:
21. In cell Q2, nest the existing formula in the cell into an IFERROR function. The IFERROR formula should display the message Invalid Plan Code if the HLOOKUP function returns an error value.
The updated formula should automatically fill into the range Q2:Q101. (Hint: If the formula does not fill into that range, copy the formula from cell Q2 to the range Q3:Q101.)
22. Switch to the WorkforcePivot worksheet. Apply the Pivot Style Medium 2 PivotTable style to the PivotTable.
23. In the WorkforcePivot table, modify the Years Service field (displaying as Sum of Years Service) in the VALUES area as described below:
24. Add the Pay Grade field to the FILTERS area of the PivotTable. Filter the table to only display records where the Pay Grade field value equals B.
25. Add a slicer to the PivotTable based on the Gender field as described below:
. Resize and reposition the Gender slicer so that the top-left corner of the slicer appears in cell E3 and the bottom-right corner appears in cell F9.
a. Using the slicer, filter the table to only display records with a Gender field value equal to F.
26. Switch to the RegistrationEntry worksheet and add a Data Validation rule to cell B9 as described below:
27. Switch to the Visual Basic Editor and edit the RegistrationEntry macro so that the Range("B3:B10").Select command will select the range B3:B11. Switch to the RegistrationRecord worksheet and run the macro to confirm that the macro copied a record with a value in the Registration Date field value into the table. (Hint: The shortcut to run this macro is ctrl+k.)
28. Switch back to viewing the RegistrationEntry worksheet. Add a macro button to the worksheet in the range A12:B13 that is linked to the RegistrationEntry macro in the worksheet. Change the name of the button to Record Registration.
29. Protect the RegistrationEntry worksheet without a password. Do not change any of the default protection options for the worksheet.
Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and exit Excel. Follow the directions on the SAM website to submit your completed project.

NOTE: Step-25 not done
Enter name in Documentation Worksheet.

SAM Capstone Project 1a NP_Excel2013_CS_P1a

New Perspectives Excel 2013
Tutorials 1-4: SAM Capstone Project 1a
Eclipse Solar Solutions
PROJECT DESCRIPTION
Andres Gonzales runs Eclipse Solar Solutions, a New Mexico-based company that manufactures solar panels and other solar products. Andres has created a workbook that tracks employee data and examines various growth scenarios. He has asked you to format the workbook so the worksheets have an appealing and consistent look. He then wants you to complete the scenario planning worksheet using formulas and charts so he can better decide on a future business strategy.

GETTING STARTED
• Download the following file from the SAM website:
o NP_Excel2013_CS_P1a_FirstLastName_1.xlsx
• Open the file you just downloaded and save it with the name:
o NP_Excel2013_CS_P1a_FirstLastName_2.xlsx
o Hint: If you do not see the .xlsx file extension in the Save file dialog box, do not type it. Excel will add the file extension for you automatically.
• With the file NP_Excel2013_CS_P1a_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet. If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

PROJECT STEPS
1. Change the theme of the workbook to Office.
2. On the Employees worksheet, change the width of columns B through D to be 17.00 characters.
3. Change the height of row 2 to 30 pts.
4. Merge and center the contents of the range B2:J2.
5. Format the merged ranges B2:J2 and L2:M2 as described below:
a. Apply the Title cell style.

b. Change to font color to Orange, Accent 2, Darker 50% (6th column, 6th row in the Theme Colors palette).

c. Apply bold formatting.

d. Change the cell fill color to Gold, Accent 4, Lighter 40% (8th column, 4th row in the Theme Colors palette).

6. Format the ranges B3:J3 and L3:M3 as described below:

a. Center cell contents

b. Change the font to Copperplate Gothic Bold.

c. Change the font size to 10 pt.

d. Change to font color to Orange, Accent 2, Darker 50% (6th column, 6th row in the Theme Colors palette).

e. Apply a Top and Bottom cell border.

f. Apply text wrapping to cell H3.

7. Select range B4:C13 and right-indent the cell contents by two levels.

8. Italicize the range B14:B17 and the range B19:B20.

9. Enter a formula in cell J4 using the IF function to calculate the bonus for Sonya Martinez. Sonya receives a bonus if her Performance rating (in cell H4) is greater than or equal to 4.

a. If this condition is true, the employee receives a bonus of $10,000. (Hint: The if_true value should be 10000)

b. If this condition is false, the employee does not qualify for a bonus, and the bonus amount is 0. (Hint: The if_false value should be "0")

c. Copy the formula created in cell J4 to the range J5:J13.

10. Format range I4:J17 with the Accounting Number format with no decimal places.

11. Enter a formula in cell F4 to calculate employee tenure. Tenure is calculated by subtracting the start date in E4 from the Data Updated date in cell C19. Because you want to show tenure in years, you will then divide that result by 365. You will need to use an absolute reference to the Data Updated date in cell C19. Copy the formula from cell F4 to cells F5:F13.

12. For range F4:F17, update the number format by decreasing the number of decimal places displayed to 1.

13. Enter a formula in cell I14 using the SUM function to total the salaries in the range I4:I13.

14. Enter a formula in cell I15 using the AVERAGE function to calculate the average salary at Eclipse based on the range I4:I13.

15. Enter a formula in cell I16 using the MAX function to calculate the highest, or maximum, salary at Eclipse based on the range I4:I13.

16. Enter a formula in cell I17 using the MIN function to calculate the lowest, or minimum, salary at Eclipse based on the range I4:I13.

17. Add Gradient Fill Red Data Bars to range I4:I13.

18. Enter a formula in cell M6 using the VLOOKUP function to find an exact match for the bonus amount for employee Joyce Sommers. (Hint: Use "Sommers" as the lookup_value, the range B3:J13 as the table_array, 9 as the col_index_num argument, and False as the range_lookup argument.)

19. In cell C20, enter a formula using the TODAY function that displays the current date.

20. Hide column G.

21. On the Planning worksheet, modify the width of column B to best fit cell contents.

22. Use the Format Painter to copy the format only from range B3:F3 to the range B11:F11. Then use the Format Painter to copy the format only from the range B9:F9 to the range B16:F16.

23. Enter a formula in cell C9 using the PMT function to calculate the monthly payment on a loan using the assumptions listed in the Status Quo scenario. In the PMT formula, use C6 as the monthly interest rate, C8 as the total number of payments, and C4 as the loan amount. Enter this formula in cell C9, then copy the formula to the range D9:E9.

24. Select cell F9 and use Goal Seek Analysis to determine the loan amount in the Aggressive Expansion scenario based on a monthly payment of $10,000. In the Goal Seek calculations, set the value of cell F9 to -10,000 and select cell F4 (the Total Loan Amount) as the changing cell. Keep the outcome of the Goal Seek Analysis as the value of cell F4.

25. Enter a formula in cell C16 using the SUM function to calculate total revenues for the current year (or the range C12:C15). Copy the formula from cell C16 to the range D16:F16.

26. Insert Line Sparklines in range G12:G15 based on data in range C12:F15. Change the line color to Orange, Accent 2, Darker 50% (6th column and 6th row of the Theme Colors palette) and line weight to 1.5 pt.

27. Select range B12:C15. Use the Quick Analysis tool to insert a Pie chart segmenting revenues by product. Reposition the chart so the upper-left corner is in cell H2.

28. Make the following changes to the chart:

a. Change the chart style to Style 5.

b. Change the chart title to Current Year Revenues and change the font color to Dark Red (1st column, 1st row of the Standard Colors palette).

c. Add Inside End data labels to the chart.

d. Move the chart legend to the Right position.

29. Select the Actual and Projected Revenues chart located in the range H17:M30. Make the following changes to the chart:

a. Change the major units of the vertical axis to 500000.

b. Type Scenario as the horizontal axis title and Revenues as the vertical axis title.

c. Remove the data labels from the graph.

d. Move the chart legend to the Bottom position.

30. Set the print area as the range B1:G16. Change the worksheet orientation to Landscape.

31. Delete the Customers worksheet.

NOTE:
I am using Office 2007. So, following steps could not be completed:

5d, 17, 26, 29c and 29d.

Homework, Assignments, Quizzes and Papers that will help you get A+ Score

Pages

NP_Excel2013_T3_P1b_FirstNameLastName_2 Final Exam Part 2

PROJECT DESCRIPTION

GETTING STARTED

PROJECT STEPS

Final Figure 1: Room Rental Log Worksheet

Final Figure2: Room List Worksheet

NP_Excel2013_T11_P1a_FirstnameLastname_2

PROJECT DESCRIPTION

GETTING STARTED

Figure 1: Security Warning Message Bar

Microsoft product screenshots used with permission from Microsoft Corporation. Copyright © 2014 Cengage Learning. All Rights Reserved.

Figure 2: Customize Ribbon Section of Excel Options Dialog Box

PROJECT STEPS

Final Figure 1: Overview Worksheet

Microsoft product screenshots used with permission from Microsoft Corporation. Copyright © 2014 Cengage Learning. All Rights Reserved.

Final Figure 2: FY18 Earnings Worksheet

Microsoft product screenshots used with permission from Microsoft Corporation. Copyright © 2014 Cengage Learning. All Rights Reserved.

NP_Excel2013_CS_T5-8_P1a _FirstNameLastName

SAM Capstone Project 1a NP_Excel2013_CS_P1a

Pages

NP_Excel2013_T3_P1b_FirstNameLastName_2 Final Exam Part 2

PROJECT DESCRIPTION

GETTING STARTED

PROJECT STEPS

Final Figure 1: Room Rental Log Worksheet

Final Figure2: Room List Worksheet

NP_Excel2013_T11_P1a_FirstnameLastname_2

PROJECT DESCRIPTION

GETTING STARTED

Figure 1: Security Warning Message Bar

Microsoft product screenshots used with permission from Microsoft Corporation. Copyright © 2014 Cengage Learning. All Rights Reserved. Figure 2: Customize Ribbon Section of Excel Options Dialog Box

PROJECT STEPS

Final Figure 1: Overview Worksheet

Microsoft product screenshots used with permission from Microsoft Corporation. Copyright © 2014 Cengage Learning. All Rights Reserved.

Final Figure 2: FY18 Earnings Worksheet

Microsoft product screenshots used with permission from Microsoft Corporation. Copyright © 2014 Cengage Learning. All Rights Reserved.

NP_Excel2013_CS_T5-8_P1a _FirstNameLastName

SAM Capstone Project 1a NP_Excel2013_CS_P1a

Microsoft product screenshots used with permission from Microsoft Corporation. Copyright © 2014 Cengage Learning. All Rights Reserved.

Figure 2: Customize Ribbon Section of Excel Options Dialog Box