Homework answers / question archive / Shelly Cashman Excel 2019 | Module 11: SAM Project 1a eMD COLLABORATE AND DEVELOP A USER INTERFACE GETTING STARTED Open the file SC_EX19_11a_FirstLastName_1

Shelly Cashman Excel 2019 | Module 11: SAM Project 1a eMD COLLABORATE AND DEVELOP A USER INTERFACE GETTING STARTED Open the file SC_EX19_11a_FirstLastName_1

MS Excel

Share With

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

eMD

COLLABORATE AND DEVELOP A USER INTERFACE

• GETTING STARTED

• Open the file SC_EX19_11a_FirstLastName_1.xlsm, available for download from the SAM website.
• Save the file as SC_EX19_11a_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_11a_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 display 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 following check boxes:
   
  • Developer
  • Layout
    
    Click the OK button to close the Excel Options dialog box and add the Developer tab to the ribbon.

• PROJECT STEPS

1. Lydia Rosales is a sales manager at eMD, a software company providing software by subscription to healthcare organizations, such as hospitals and clinics. She is developing an Excel workbook for sales representatives to use when contacting clients and prospective clients. She is also using the workbook to track quarterly sales the representatives have made. She asks for your help in completing the workbook and making it more attractive and easier to use.
   
   Go to the Prices worksheet, which lists the monthly subscription prices for eMD's software packages and services. The worksheet is missing a title, and Lydia asks you to add one as follows:
   1. Insert the Fill: Gray, Accent color 1; Shadow WordArt.
   2. Type Software and Service Prices as the worksheet title.
   3. Apply a 36-point font size to the WordArt text.
   4. Move the WordArt to the right of the eMD logo in row 1 so that the WordArt spans the range B1:D1.
2. Lydia asked Patrice Greene, the vice president of sales and marketing, to review the workbook. Read and respond to her notes and comments as follows:
   1. Read the note in cell B2. Patrice Greene intended this note to remain in the workbook as a reminder to sales representatives. Edit the note to use combining instead of "combing".
   2. Read the next note in cell D4. Patrice inserted this note for Lydia only. Delete the note.
   3. Lydia recalls a recent decision to add the medical billing software to the ePharmacy package. In cell C8, insert the following comment asking Patrice to verify the decision:
      Should I add medical billing to this package?
3. Lydia wants to customize the appearance of this workbook by changing a theme color and theme font. She also wants all new workbooks in the Sales and Marketing Department to reflect these changes.
   
   Create a custom theme as follows:
   1. Create a custom color scheme named eMD that uses Blue-Gray, Accent 2, Darker 50% as the Text/Background—Dark 2 color.
   2. Create a custom font set name eMD that uses Century Schoolbook as the Heading font and Calibri Light as the Body font.
   3. Save the new settings as a custom theme using eMD as the name of the new theme.
4. Go to the Sales worksheet, which contains the quarterly sales for eMD's software and services. Lydia 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 C4:F8 and H4:H8 by applying the Currency number format with 0 decimal places and the $ symbol.
5. Lydia wants to show how the sales of each product and service type changed from one quarter to another.
   
   Use the Quick Analysis tool to add Line sparklines to the range G4:G8 to chart the Q1–Q4 values and totals (range C4:F8) for each type of product or service. (Hint: Select the Q1–Q4 values and totals first.)
6. Lydia created the Quarterly Sales chart in the range B10:H28 to illustrate the sales per quarter for the company's products and services.
   
   Identify each product and service by adding a legend to the bottom of the chart.
7. Lydia asks for a way to examine sales for only the software packages and tools. Create a custom view of the Sales worksheet as follows to meet Lydia's request:
   1. Hide rows 6:8.
   2. Save the settings as a custom view, using Software as the name of the view.
8. Lydia created another custom view that shows the Sales worksheet in Page Layout view in preparation for printing. Lydia asks to review those settings now.
   
   Show the Page Layout custom view in the Sales worksheet.
9. Go to the Client Information worksheet, which serves as a form for entering information about client contacts. The worksheet is not finished, and Lydia asks you to complete it.
   
   In cell A25, enter a formula without using a function that displays the client's first name from the cell named First_Name (cell B4).
10. Sales representatives contact a client for one of five reasons. Add a fifth reason to the Reason for Contact section as follows:
    1. In cell D10, insert an Option Button (Form Control).
    2. Edit the option button text to use the Demonstration to replace the placeholder text.
    3. Format the new option button to link it to cell $K$26.
    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 Training option button to its left.
11. Lydia asks you to add a check box also to the Software Tools section.
     
    1. In cell C14, insert a Check Box (Form Control).
    2. Edit the check box text to use Health tracking to replace the placeholder text.
    3. Format the new check box to link it to cell $N$25.
    4. Format the check box to use 3-D shading.
    5. Position the Health tracking check box so that its box aligns with the Diagnosis check box below it and with the Medical billing check box to its left.
12. Lydia created a macro in Visual Basic named ClearForm that clears the form for a new client contact. Add a button to run the macro as follows:
    1. In cell E12, insert a Button (Form Control) to the right of the Save Info button.
    2. Assign the ClearForm macro to the new button.
    3. Format the new button to change the height to 0.4" and the width to 1.1".
    4. Edit the button to use Clear to replace the placeholder text.
    5. Position the Clear button to align with the top and bottom of the Save Info button on its left.
13. Lydia asks you to change the format of the Save Info 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 Calibri Light.
14. Lydia also created a macro named PrintClient that specifies printing settings and prints the client information. Add a button to run the macro as follows:
    1. In cell F12, insert a Button (Form Control).
    2. Attach the PrintClient macro to the new button.
    3. Change the height of the new button to 0.4" and the width to 1.1".
    4. Edit the button text to use Print to replace the placeholder text.
    5. Position the Print command button to align with the top and bottom of the Clear and Save buttons on its left.
15. Lydia is planning to send the workbook to the eMD sales representatives. Because not all of them have the latest version of Excel, she 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. Lydia wants to make sure the worksheets cannot be moved until the sales representatives need to use it.
    
    Protect the workbook as follows:
    1. Protect the workbook structure using eMD! 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.