Shelly Cashman Excel 365 | Module 11: SAM Critical Thinking Project C Radius Electronics

GETTING STARTED

1. Save the file SC_EX365_CT11C_FirstLastName_1.xlsm as SC_EX365_CT11C_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_CT11C_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_CT11C_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.

a. Read the comment for the AudioLink unit price. Martin Cisneros intended this comment to remain in the workbook as a reminder to sales representatives. Edit the comment to use repeat to correct the spelling error.

b. Read the next comment for the SlimSet product name. 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 the cell containing the unit price for the Trice product, 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.

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 for Q1–Q4 and the totals.

4. Nari wants to show how the individual and total sales of each product changed from one quarter to another. Use the Quick Analysis tool to add Line sparklines to the blank range in the Quarterly Sales table to chart the Q1–Q4 values and totals for each type of product.

5. Nari created the Quarterly Sales chart 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 to meet Nari's request.

a. Hide the rows that show data for the AudioLink, BeepTag, and Radius Headset products.

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 the "For Office Use Only" area, in the cell that should display the customer's first name, enter a formula without using a function that displays the customer's first name from the cell named First_Name.

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

a. In the cell to the right of the Training option button, insert an Option Button (Form Control) using Demo as the option button text.

b. Format the new option button to use 3-D shading and to link it to the same cell as the other Reason for Contact option buttons, if it is not automatically linked.

c. 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 to the Products section.

a. In the cell above the Radius Headset check box, insert a Check Box (Form Control) using AutoCharge as the check box text.

b. Format the new check box to use 3-D shading and to link it to the cell in the "For Office Use Only" area that should contain the TRUE (checked) or FALSE (unchecked) value for the AutoCharge product.

c. 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.

a. To the right of the Save Data button, insert a Button (Form Control) that runs the ClearForm macro when clicked.

b. Resize the new button to a height to 0.4" and a width of 1.1" and display Clear as the button text.

c. 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.

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.

a. To the right of the Clear button, insert a Button (Form Control) that runs the PrintCustomer macro when clicked.

b. Resize the new button to a height of 0.4" and a width of 1.1" and display Print as the button text.

c. 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.

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