Shelly Cashman Excel 365 | Module 11: SAM Project B Light Smart

GETTING STARTED

1. Save the file SC_EX365_11B_FirstLastName_1.xlsm as SC_EX365_11B_FirstLastName_2.xlsm

a. Edit the file name by changing “1” to “2”.

b. If you do not see the .xlsm file extension, do not type it. The file extension will be added for you automatically.

2. SC_EX365_11B_FirstLastName_1.xlsm, available for download from the website. Files downloaded from the website are safe and do not contain viruses, but due to a recent Microsoft policy update, macros in downloaded files are disabled by default. To complete this project, you will need to enable macros in the file. To enable macros on this file:

a. Open Windows File Explorer and go to the folder where you saved the file. Right-click the file and choose Properties from the context menu. At the bottom of the General tab, select the Unblock checkbox and select Apply, and then click OK.

3. With the file SC_EX365_11B_FirstLastName_2.xlsm open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet.

a. If cell B6 does not display your name, delete the file and download a new copy.

4. To complete this project, you need to display the Developer tab. If this tab is not displayed, 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 box: Developer Click the OK button to close the Excel Options dialog box and add the Developer tab and the buttons for the Power tools to the ribbon.

PROJECT STEPS

1. Gloria Veracruz is the sales manager at Light Smart, a manufacturer of smart home lighting products in Des Moines, Iowa. She is developing an Excel workbook for sales representatives to use when contacting current and prospective customers. 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 easier to use. Go to the Product Pricing worksheet, which lists the unit prices for five new products. Gloria asked Heather Foy, the vice president of sales and marketing, to review the workbook. Read and respond to her comments as follows:

a. Read the comment in cell D4. Heather Foy intended this comment to remain in the workbook as a reminder to sales representatives. Edit the comment to use customers instead of "custumers".

b. Read the next comment in cell B6. Heather inserted this comment for Gloria only. Delete the comment.

c. Gloria recalls a recent discussion about reducing the price of the TuneSync lighting system product. In cell D8, insert the following comment to Heather: Should I reduce this price?

2. Gloria wants you 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:

a. Create a custom color scheme named Smart that uses Dark Gray, Background 2, Darker 25% as the Text/Background - Dark 2 color.

b. Create a custom font set named Smart that uses Georgia as the Heading font and Gill Sans MT as the Body font.

c. Save the new settings as a custom theme using Smart as the name of the new theme.

3. Go to the Quarterly Sales worksheet, which contains the quarterly sales for the new products. Gloria asks you to make the sales numbers easier to interpret. Apply the Currency number format with 0 decimal places and the $ symbol to the sales numbers in the ranges C4:F9 and H4:H9.

4. Gloria wants you to show how the sales of each product changed from one quarter to another. Use the Quick Analysis tool to add Line sparklines to the range G4:G9 to chart the Q1–Q4 values and totals (range C4:F9) for each type of product. (Hint: Select the Q1–Q4 values and totals first.)

5. Gloria created the Quarterly Sales chart in the range B10:H25 to illustrate the sales per quarter for the company's new products. Identify each product by adding a legend to the bottom of the chart.

6. Gloria asks you to provide a way to examine sales for only the SmartBulb and TuneSync products. Create a custom view of the Quarterly Sales worksheet as follows to meet Gloria's request:

a. Hide rows 4:6.

b. Save the settings as a custom view, using Top Two as the name of the view.

7. Gloria created another custom view that shows the Quarterly Sales worksheet in Page Layout view in preparation for printing. Gloria asks you to display those settings now. Show the Page Layout custom view in the Quarterly Sales worksheet.

8. Go to the Sales Contacts worksheet, which serves as a form for entering information about sales contacts. The worksheet is not finished, and Gloria asks you to complete it. In cell A25, enter a formula without using a function that displays the customer's first name from the cell named First_Name (cell B4).

9. Sales representatives contact a customer for one of five reasons. Add a fifth reason to the Reason for Contact section as follows:

a. In cell D10, insert an Option Button (Form Control).

b. Edit the option button text to use the Demo to replace the placeholder text.

c. Format the new option button to link it to cell $K$26, if it is not automatically linked.

d. Format the option button to use 3-D shading.

e. Position the new option button so that it is aligned on the top with the Installation option button to its left.

10. Gloria asks you to add a check box to the Products section.

a. In cell C16, insert a Check Box (Form Control).

b. Edit the check box text to use Welly to replace the placeholder text.

c. Format the new check box to link it to cell $S$25.

d. Format the check box to use 3-D shading.

e. Position the Welly check box so that its box aligns with the Lumexa check box above it and with the TuneSync check box to its left.

11. Gloria created a macro in Visual Basic named ClearForm that clears the form for a new customer contact. Add a button to run the macro as follows:

a. In cell E12, insert a Button (Form Control) to the right of the Save Data button.

b. Assign the ClearForm macro to the new button.

c. Format the new button to change the height to 0.4" and the width to 0.7".

d. Edit the button to use Clear to replace the placeholder text.

e. Position the Clear button to align with the top and bottom of the Save Data button on its left.

12. Gloria asks you to change the format of the Save Data button so that it matches the Clear button. Change the format as follows:

a. Use the text Save as the caption of the command button.

b. Change the font to 11-point Gill Sans MT.

13. Gloria also created a macro named PrintCustomer that specifies printing settings and prints the customer information. Add a button to run the macro as follows:

a. In cell F12, insert a Button (Form Control).

b. Attach the PrintCustomer macro to the new button.

c. Change the height of the new button to 0.4" and the width to 0.7".

d. Edit the button text to use Print to replace the placeholder text.

e. Position the Print command button to align with the top and bottom of the Clear and Save buttons on its left.

14. Gloria is planning to send the workbook to the Light Smart sales representatives. Because not all of them have the latest version of Excel, she asks you to check the compatibility of the workbook.

a. Check the compatibility of the workbook.

b. Copy the Compatibility Report to a new worksheet. Change the worksheet name to Compatibility Report, if necessary.

15. Gloria wants to make sure the worksheets cannot be moved until the sales representatives need to use it. Protect the workbook as follows:

a. Protect the workbook structure using smart! as the password.

b. 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 website to submit your completed project.

Final Figure 1: Product Pricing Worksheet

Final Figure 2: Quarterly Sales Worksheet

Final Figure 3: Sales Contacts Worksheet

Final Figure 4: Compatibility Report Worksheet