Homework answers / question archive / Shelly Cashman Excel 2019 | Module 11: SAM Project 1b Task Team COLLABORATE AND DEVELOP A USER INTERFACE   GETTING STARTED Open the file SC_EX19_11b_FirstLastName_1

Shelly Cashman Excel 2019 | Module 11: SAM Project 1b Task Team COLLABORATE AND DEVELOP A USER INTERFACE   GETTING STARTED Open the file SC_EX19_11b_FirstLastName_1

MS Excel

Share With

Shelly Cashman Excel 2019 | Module 11: SAM Project 1b

Task Team

COLLABORATE AND DEVELOP A USER INTERFACE

GETTING STARTED

• Open the file SC_EX19_11b_FirstLastName_1.xlsm, available for download from the SAM website.
• Save the file as SC_EX19_11b_FirstLastName_2.xlsm by changing the “1” to a “2”.
  • If you do not see the .xlsm file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
• With the file SC_EX19_11b_FirstLastName_2.xlsm 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.
• To complete this project, you need to add the Developer tab. If this tab does not display, right-click any tab on the ribbon, and then click Customize the Ribbon on the shortcut menu. In the Main Tabs area of the Excel Options dialog box, click the Developer check box, and click OK.

• PROJECT STEPS

1. Kenny Preston runs a small business called Task Team, which offers installation, maintenance, and repair services to homeowners. He is developing an Excel workbook for service representatives to use when contacting customers and prospective customers. He is also using the workbook to track annual sales. He asks for your help in completing the workbook and making it more attractive and easier to use.
   Go to the Services worksheet, which lists the hourly rates for Task Team services. The worksheet is missing a title, and Kenny asks you to add one as follows:
   1. Insert the Fill: Brown, Accent color 4; Soft Bevel WordArt.
   2. Type Service Prices as the worksheet title.
   3. Apply a 36-point font size to the WordArt text.
   4. Move the WordArt to the middle and center of the merged cells A1:C1.
2. Kenny asked his wife and business partner, Ruth Preston, to review the workbook. Read and respond to her notes as follows:
   1. Read the note in cell C3. Ruth Preston wants this note to remain in the workbook as a reminder to service representatives. Edit the note to insert the word from so that the comment reads "This price has been increased from $55."
   2. Read the next note in cell B7. Ruth inserted this note for Kenny only. Delete the note.
   3. Kenny and Ruth have been discussing whether to change the description for the "Other exterior" service area. In cell B9, insert and post the following comment asking Ruth to verify the change:
      Change to "Walls, foundation, and roof"?
3. Kenny wants to customize the appearance of this workbook by changing a theme color and theme font. He also wants all new Task Team workbooks to reflect these changes.
   Create a custom theme as follows:
   1. Create a custom color scheme named TaskTeam that uses Light Green in the Standard Colors palette as the Followed Hyperlink color.
   2. Create a custom font set name TaskTeam that uses Rockwell (not Rockwell Condensed) as the Heading font and Rockwell as the Body font.
   3. Save the new settings as a custom theme using TaskTeam as the name of the new theme.
4. Go to the Sales History worksheet, which contains the annual sales for the past four years. Kenny says that the sales numbers should appear with a comma thousands separator and a dollar sign.
   Create a number format for the sales numbers in the ranges B3:E7 and G3:G7 by applying the Currency number format with 0 decimal places and the $ symbol.
5. Kenny wants to show how the sales of each service category changed from one year to another.
   Use the Quick Analysis tool to add Line sparklines to the range F3:F7 to chart the Year 1 to Year 4 values and totals (range B3:E7) for each category of service. (Hint: Select the Year 1 to Year 4 values and totals first.)
6. Kenny created the Annual Sales chart in the range A9:G27 to illustrate the sales per year for the four categories of services.
   Identify each service category by adding a legend to the top of the chart.
7. Kenny asks for a way to examine sales for only the exterior and interior services. Create a custom view of the Sales History worksheet as follows to meet Kenny's request:
   1. Hide rows 5:7.
   2. Save the settings as a custom view, using Exterior Interior as the name of the view.
8. Kenny created another custom view that shows the Sales History worksheet in Page Layout view in preparation for printing. Kenny asks to display that view now.
   Show the Print Setup custom view in the Sales History worksheet.
9. Go to the Survey worksheet, which serves as a survey form for Task Team customers. Although Kenny plans to add more survey questions, he asks you to complete the ones he has already entered.
   In cell C25, enter a formula without using a function that displays the customer's address from the cell named Address (cell B5).
10. Add a third option to Question 1 as follows:
    1. In cell B12, insert an Option Button (Form Control) to the right of the Satisfactory option button.
    2. Edit the option button text to use the Needed improvement to replace the placeholder text.
    3. Format the new option button to link it to cell $K$26, if necessary.
    4. Format the option button to use 3-D shading.
    5. Position the new option button so that it is aligned on the top with the Satisfactory option button to its left.
11. Kenny asks you to also add a check box to the options for Question 2.
    1. In cell C14, insert a Check Box (Form Control).
    2. Edit the check box text to use Responsive to replace the placeholder text.
    3. Format the new check box to link it to cell $O$25.
    4. Format the check box to use 3-D shading.
    5. Position the Responsive check box so that its box is top-aligned with the Enthusiastic check box to its left.
12. Kenny created a macro in Visual Basic named ClearData that clears the form for new customer responses. Add a button to run the macro as follows:
    1. In cell E10, insert a Button (Form Control) to the right of the Save Data button.
    2. Format the new button to change the height to 0.4" and the width to 1".
    3. Edit the button to use Clear to replace the placeholder text.
    4. Position the Clear button to align with the top and bottom of the Save Data button on its left.
    5. Assign the ClearData macro to the new button.
13. Kenny asks you to change the format of the Save Data button so that it matches the Clear button. Change the format as follows:
    1. Use the text Save as the caption of the command button.
    2. Change the font to 11-point Rockwell (Body).
14. Kenny also created a macro named PrintData that specifies printing settings and prints the survey. Add a button to run the macro as follows:
    1. In cell F10, insert a Button (Form Control) to the right of the Clear button.
    2. Change the height of the new button to 0.4" and the width to 1".
    3. Edit the button text to use Print to replace the placeholder text.
    4. Position the Print command button to align with the top and bottom of the Clear and Save buttons on its left.
    5. Assign the PrintData macro to the new button.
15. Kenny is planning to send the workbook to Task Team service representatives. Because not all of them have the latest version of Excel, he asks you to check the compatibility of the workbook.
    1. Check the compatibility of the workbook.
    2. Copy the Compatibility Report to a new worksheet.
16. Kenny wants to make sure the worksheets cannot be moved until the service representatives need to use them.
    Protect the workbook as follows:
    1. Protect the workbook structure using TaskTeam as the password.
    2. Mark the workbook as final.

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