Shelly Cashman Excel 365 | Module 11: SAM Project A Radius Electronics

GETTING STARTED

1. Save the file SC_EX365_11A_FirstLastName_1.xlsm as SC_EX365_11A_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_11A_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_11A_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 Power Pivot and Developer tabs. If these tabs are 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 boxes:

a. Developer

b. Layout Click the OK button to close the Excel Options dialog box and add the Developer tab, the Power Pivot tab, and the buttons for the Power tools to the ribbon.

PROJECT STEPS

1. Nari Han is a sales manager at Radius Electronics, a manufacturer of cell phone accessories and other electronic products. 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 New Products worksheet, which lists the unit prices for five new products. Nari asked Martin Cisneros, the vice president of sales and marketing, to review the workbook. Read and respond to his comments as follows:

a. Read the comment in cell D4. Martin Cisneros intended this comment to remain in the workbook as a reminder to sales representatives. Edit the comment to use repeat instead of "repet".

b. Read the next comment in cell B7. Martin inserted this comment for Nari only. Delete the comment.

c. Nari recalls a recent discussion about reducing the price of the Trice three-in-one charger. In cell D8, insert the following comment to Martin: Have we decided to reduce this price?

2. Nari 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 Radius that uses Indigo, Accent 5, Darker 25% as the Text/Background - Dark 2 color.

b. Create a custom font set named Radius that uses Century Gothic as the Heading font and Gill Sans MT as the Body font.

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

3. Go to the Sales worksheet, which contains the quarterly sales for the new products. Nari 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. Nari wants 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 or service. (Hint: Select the Q1–Q4 values and totals first.)

5. Nari created the Quarterly Sales chart in the range B11: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. Nari asks you to provide a way to examine sales for only the SlimSet and Trice products. Create a custom view of the Sales worksheet as follows to meet Nari's request:

a. Hide rows 4:6.

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

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

8. Go to the Contacts worksheet, which serves as a form for entering information about customer contacts. The worksheet is not finished, and Nari 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 Training option button to its left.

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

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

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

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

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

e. Position the AutoCharge check box so that its box aligns with the Radius Headset check box below it and with the AudioMax check box to its left.

11. Nari 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 1.1".

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. Nari 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. Nari 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 1.1".

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. Nari is planning to send the workbook to the Radius Electronics 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. Nari 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 radius! 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: New Products Worksheet

Final Figure 2: Sales Worksheet

Final Figure 3: Contacts Worksheet

Final Figure 4: Compatibility Report