Shelly Cashman Excel 2019 | Module 11: SAM Critical Thinking Project 1c eMD COLLABORATE AND DEVELOP A USER INTERFACE   GETTING STARTED Open the file SC_EX19_CT11c_FirstLastName_1

Subject:MS ExcelPrice: Bought3

Share With

Shelly Cashman Excel 2019 | Module 11: SAM Critical Thinking Project 1c

eMD

COLLABORATE AND DEVELOP A USER INTERFACE

 

• GETTING STARTED

• Open the file SC_EX19_CT11c_FirstLastName_1.xlsm, available for download from the SAM website.
• Save the file as SC_EX19_CT11c_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_CT11c_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 the 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. Insert the Fill: Gray, Accent color 1; Shadow WordArt using Software and Service Prices as the worksheet title. Change the font size to 36 point and move the WordArt to the right of the eMD logo so that it spans columns B:D.
2. Lydia asked Patrice Greene, the vice president of sales and marketing, to review the workbook. Read and respond to Patrice's notes and comments as follows:
   1. Read the note attached to the Subscription Pricing Information heading. Patrice intended for this note to remain in the workbook as a reminder to sales representatives. Edit the note to use combining to correct the spelling error.
   2. Read the note attached to the price per month for demos. 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. Insert a comment to the Electronic Rx description 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 the theme color and theme font. She also wants all new workbooks used by 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 named 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 and totals by applying the Currency number format with zero 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 between the Q4 column and the Annual Totals column to chart the Q1–Q4 values and totals for each type of product or service. (Hint: Select the Q1–Q4 values and totals first.)
6. Lydia created the Quarterly Sales chart 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. Hide the rows that contain the Tech Support, Training, and Total sales data. 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. Apply 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, display the client's first name from the First_Name cell.
10. Sales representatives contact a client for one of five reasons. Add a fifth reason to the Reason for Contact section as follows:
    1. To the right of the Training option button in the Reason for Contact section, insert another Option Button (Form Control) and display Demonstration as the button text.
    2. Link the new option button to cell K26 using an absolute reference, and then format the option button using 3-D shading.
    3. Align the top of the new option button with the Training option button to its left.
11. Lydia also asks you to add a check box to the Software Tools section.
    1. Above the Diagnosis check box in the Software Tools section, insert a Check Box (Form Control) and display Health tracking as the button text.
    2. Link the new check box to cell N25 using an absolute reference, and then format the check box using 3-D shading.
    3. Align the new check box 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. Insert a Button (Form Control) to the right of the Save Info button. Assign the ClearForm macro to the new button.
    2. Format the new button to be 0.4" tall and 1.1" wide, and then display Clear as the button text.
    3. Align the new button with the top of the Save Info button on its left.
13. Lydia asks you to change the format of the Save Info button.  Display Save as the caption of the command button and change the font to Calibri Light 11 pt.
14. Lydia also created a macro named "PrintClient" that specifies printing settings and prints the client information.
    
    Create another button to the right of the existing buttons. Display Print as the new button text and then resize the button with the same dimensions as the Clear button. Attach the PrintClient macro to the new button and top align it with the Clear and Save buttons on its left. Then change the font of the Clear and Print buttons to match the Save button.
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. Check the compatibility of the workbook and copy the report to a new worksheet. (Hint: If necessary, use Compatibility Report as the name of the new worksheet.)
16. Lydia wants to make sure the worksheets cannot be moved until the sales representatives need to use them. Protect the workbook structure using eMD! as the password. 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.