Shelly Cashman Excel 2016 | Modules 8-11: SAM Capstone Project 1a #studyhelpme #education

24 / 7 Live Support
support@studyhelpme.com
website link: www.studyhelpme.com
WhatsApp: +44-7309655268
+91-9828671065
PROJECT STEPS
1. You are an assistant to Blake Harper, a partner in Parma Disaster Recovery in Atlanta, Georgia. The firm offers training and consulting services to companies that want to prepare for or recover from disasters, including natural disasters. Blake asks you to help him automate client information entry, review current sales data, and determine whether the firm should expand into Florida.
Go to the Home Page worksheet. Add a background image to the worksheet, using the support file Support_SC_EX16_CS8-11a_Background.jpg, to make the user interface more attractive.
1. Modify and arrange the form controls to improve the layout as follows:
1. Align the Disaster prevention check box and the Disaster detection check box using Align Top formatting.
2. Align the Management training check box and the Disaster detection check box using Align Left formatting.
3. Format the Disaster detection check box so that it has a cell link to cell $C$30.
4. In Design Mode, select the Option Button 8 option button and then use the text Training to replace the placeholder text.
5. In cell D17, create a formula using the INDEX function to return the value from the named range Request_type (which represents the range A27:A29) based on the value in cell E26.
6. Exit Design Mode, and then test the worksheet by adding a client record as follows:
1. Use the Enter Client Information button to enter the information shown in bold in Table 1 below:
Table 1: Client Information

Field Value Field Value
First name Don Last name Moore
Address 141 Post Road Email DonMoore@mail.cengage.com
City Atlanta Main phone 768-555-2011
State GA Other phone 768-555-8384
Postal code 30315

1. Select Staff Training as the Service requestoption. (Hint:You will need to manually select this option—you will not be prompted by message boxes.)
2. Select Estimate as the Request typeoption.
3. To make the worksheet look neater, Blake wants to remove the Group Box borders.
With the Home Page worksheet active, open the Immediate window in the Visual Basic Editor, and then run the following code:
activesheet.groupboxes.visible=false
(Hint: For this code to work, the Home Page worksheet must be the active worksheet.)
1. Turn on Design Mode. Copy the Print Client Information button, and then paste and size it so that it completely covers the gold area within cell D20. Update the button as follows:
1. Use the text Clear Client Information to replace the placeholder text.
2. Assign the ClearClient macro to the button.
3. View the code associated with this button. Enter the following VBA code between the “Sub ClearClient()” and “End Sub” lines:
(Hint: There are no spaces before or after the parentheses in the code.)
1. Confirm that your VBA code is correct by switching back to the Home Page worksheet, turning off Design Mode, and then clicking the Clear Client Information button. The ranges specified in the code should be blank.
2. Go to the 2020 Sales by Quarter worksheet. Create a Line with Markers chart based on the nonadjacent ranges B3:E3 and B5:E5. Move and resize the chart so that the upper-left corner is within cell A7 and the lower-right corner is within cell E21.
3. Format the line with markers chart as follows:
1. Use Training Projects 2020 as the title of the chart.
2. Add a Primary Vertical Axis title to the chart. Use Sales as the Primary Vertical Axis title.
3. Select the Quarter 2 data point on the chart, and then change the size of the marker to 9 and the fill color of the marker to Ice Blue, Background 2, Darker 75% (3rd column, 5th row in the Theme Colors palette).
4. Blake wants to review how the sales of the training projects might grow over the next two quarters by adding a trendline to the chart.
Add a linear trendline to the chart with the following options:
1. Set the trendline to forecast forward 2 periods.
2. Display the R-squared value on the chart.
3. Go to the 2020 Sales by Project worksheet. To make it easier to insert the correct project type, add data validation to the range C4:C21 as follows:
1. The cells in the range C4:C21 should allow only values displayed in an in-cell drop-down list and ignore blank cells.
2. Add Correction, Detection, Prevention, Management, Staff as the list of acceptable values.
3. Add an Input Message using Project Type as the Title and Select a project type from the list. (including the period) as the Input message.
4. Add an Error Alert using the Stop style for the Error Alert, and use Project Type Error as the Title and The project type must be one of the listed values. (including the period) as the Error message.
5. Use data validation to enter Detection in cell C18.

Видео Shelly Cashman Excel 2016 | Modules 8-11: SAM Capstone Project 1a #studyhelpme #education канала Study Help Me