Homework answers / question archive / Shelly Cashman Excel 2016 | Modules 8-11: SAM Capstone Project 1a Parma Disaster Recovery Analyzing DATA and solving problems   GETTING STARTED Open the file SC_EX16_CS8-11a_FirstLastName_1

Shelly Cashman Excel 2016 | Modules 8-11: SAM Capstone Project 1a Parma Disaster Recovery Analyzing DATA and solving problems   GETTING STARTED Open the file SC_EX16_CS8-11a_FirstLastName_1

MS Excel

Share With

---

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

Parma Disaster Recovery

Analyzing DATA and solving problems

 

GETTING STARTED

• Open the file SC_EX16_CS8-11a_FirstLastName_1.xlsm, available for download from the SAM website.
• Save the file as SC_EX16_CS8-11a_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_EX16_CS8-11a_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.
    • You will also need to download the following support files for this project:
      • Support_SC_EX16_CS8-11a_Weather.xlsx
      • Support_SC_EX16_CS8-11a_FLPop.xlsx
      • Support_SC_EX16_CS8-11a_Background.jpg
      • If you see a Message Bar with a security warning at the top of the Excel window, click the Enable Content button in the Message Bar to enable the macros contained in the file.
      • This project requires you to use the Solveradd-in. If this add-in is not available on the Data tab in the Analyzegroup (or if the Analyzegroup is not available), click the File tab to open Backstage View and then click the Options button. In the left pane of the Excel Options dialog box, click the Add-Ins option. Click the Manage arrow, click Excel Add-Ins in the Manage list, and then click the Go button. In the Add-Ins dialog box, click the Solver Add-In check box and then click the OK button. Follow any remaining prompts to install Solver.
      • To complete this project, you need to add the Power Pivot and Developer tabs to the Ribbonand the Power View button to the Insert tab as follows:
        • Click the File tab to open Backstage View and then click the Options button. In the left pane of the Excel Options dialog box, click the Advanced option. In the Data section, click the Enable Data Analysis add-ins: Power Pivot, Power View, and 3D Maps check box. (Microsoft Silverlight is required for Power View.) In the left pane of the Excel Options dialog box, click the Add-Ins option. Click the Manage arrow, click COM add-ins in the Manage list, and then click the Go button. Click the check boxes for Microsoft 3D Maps for Excel, Microsoft Power Pivot for Excel, and Microsoft Power View for Excel, and then click the OK button.

*Power Pivot, Power View, and 3D Maps are not available on Microsoft Office Home and Business 2016 and Microsoft Office 365 Business.

• 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 Developer check box, and then click the OK button to close the Excel Options dialog box and add the Developer tab to the Ribbon.

 

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:

Range(“B3:B7”).ClearContents
Range(“D3:D6”).ClearContents
Range(“B15:B19”).ClearContents
Range(“D15:D16”).ClearContents

(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% (3^rd column, 5^th 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.
   6. Create a PivotTable and PivotChart in the 2020 Sales by Project worksheet to analyze project sales as follows:
      1. Insert a PivotChart & PivotTable in cell G3 based on the Projects table in the range A3:F21.
      2. Move the PivotChart so that the upper-left corner is located within cell A23 and the lower-right corner is located within cell F37.
      3. Add the State field to the Columns area of the PivotTable Fields task pane.
      4. Add the Project Type field to the Rows area of the PivotTable Fields task pane.
      5. Add the Sales field to the Values area of the PivotTable Fields task pane. (Hint: The field name will be updated to Sum of Sales.)
      6. Modify and format the PivotTable and PivotChart to provide more meaningful information as follows:
         1. Refresh the data in the PivotTable and PivotChart to be sure they display accurate data.
         2. Apply the Currency number format, with 0 decimal places and $ as the symbol, to the Sum of Sales field in the PivotTable.
         3. Apply the Pivot Style Medium 14 style to the PivotTable to coordinate with the Projects table on the same worksheet. (Hint: Depending on your version of Office, the PivotTable style may appear as Light Green, Pivot Style Medium 14.)
         4. Add the title Projects by State to the PivotChart in the Above Chart position.
         5. Add a Primary Vertical Axis title to the PivotChart. Use Project Sales as the Primary Vertical Axis title.
         6. Change the fill color of the Chart Area to Green, Accent 6, Lighter 80% (10^th column, 2^nd row in the Theme Colors palette).
         7. Blake wants to analyze sales data and profits in Georgia and North Carolina, two of the three states where his company does business.

Go to the Sales by State worksheet. Change the Report Layout to show the PivotTable in Compact Form, and then display subtotals at the top of each group.

1. Add a new calculated field to the end of the PivotTable report to determine project profit as follows:
   1. Use Profit as the custom name of the calculated field.
   2. Create a formula without using a function that subtracts the Cost of Service from the Sales. (Hint: The calculated field will automatically be added to your PivotTable.)
   3. Use a Slicer to filter the PivotTable report as follows:
      1. Add a Slicer to the PivotTable report based on the State field.
      2. Resize and reposition the State Slicer so that the upper-left corner is located within cell A14 and the lower-right corner is located within cell B20.
      3. Format the Slicer using the Slicer Style Light 6 style. (Hint: Depending on your version of Office, the Slicer style may appear as Light Green, Slicer Style Light 6.)
      4. Use the Slicer to filter the PivotTable report to display only data for Georgia (GA) and North Carolina (NC).
      5. Go to the Training Programs worksheet. Correct the first error in this worksheet, a divide by zero error, as follows:
         1. Use the Trace Precedents arrows to find the source of the error in cell B15, the income per session for Staff 1 programs.
         2. Correct the error by editing the formula in cell B15, which should divide the income per program (cell B14) by the number of Staff 1 sessions (cell B5).
         3. Copy the formula and the formatting from cell B15 to the range C15:F15.
         4. Correct the Name error in cell B20 as follows:
            1. Use Error Checking to determine the source of the error in cell B20, which should calculate the average income per program.
            2. Correct the error by editing the formula in cell B20.
            3. Blake’s goal is to make at least $25,000 from the Staff 1 training programs. Use Goal Seek to determine how to achieve this goal as follows:
               1. Set the gross income for Staff 1 programs (cell B7) to 25,000.
               2. Change the program fee for Staff 1 programs (cell B4) to determine the fee the company needs to charge to achieve the income goal.
               3. Go to the Training Rates worksheet. Blake has already created two scenarios. The Max Attendance scenario calculates profit based on the maximum number of attendees in each program and the Average Attendance calculates profit based on the current attendance. Add a new scenario to compare the profit with low attendance as follows:
                  1. Add another scenario to the workbook, using Low Attendance as the scenario name.
                  2. Use B9:F9 as the changing cells.
                  3. Update the cell values in the range B9:F9 to match the low enrollment values shown in bold in Table 2 below:

Table 2: Cell Values for the Low Attendance Scenario

 

Cell	New Value
Staff_1_Attendees (B9)	15
Staff_2_Attendees (C9)	16
Staff_3_Attendees (D9)	18
Management_1_Attendees (E9)	15
Management_2_Attendees (F9)	15

 

1. Show the Max Attendance scenario values in the Training Rates worksheet.
2. Compare the average profit per program based on the three scenarios as follows:
   1. Create a Scenario Summary report using the range B11:F11 as the result cells to show the average profit per program depending on the attendance. (Hint: The defined names of the range B11:F11 appear in the report.)
   2. Use Attendance Scenario Report as the name of the worksheet containing the report.
   3. Blake also wants to focus on one or two types of training programs at a time when comparing the average profit per program. Return to the Training Rates worksheet and create another type of report as follows:
      1. Create a Scenario PivotTable report using the range B11:F11 as the result cells to compare the average profit per program depending on the attendance changes in a PivotTable. (Hint: The defined names of the range B11:F11 appear in the report.)
      2. Use Attendance PivotTable as the name of the worksheet containing the PivotTable.
      3. Format cells B4:F6 in the Attendance PivotTable worksheet using the Accounting number format with 0 decimal places and $ as the symbol.
      4. Use WordArt to add a watermark to the Training Rates worksheet as follows:
         1. Use the Gradient Fill: Tan, Accent color 5; Reflection option for the watermark.
         2. Enter the text Review Draft into the WordArt.
         3. Reposition and resize the WordArt text box so that the upper-left corner is located in cell B14 and the lower-right corner is located in cell E18.
         4. Set the transparency of the WordArt text fill to 95%.
         5. Go to the Training Schedule worksheet. Blake wants to determine the number of weekday and weekend training sessions the company can offer to make the highest weekly profit within the maximum room capacities, the scheduling needs of the specialist programs, and other practical conditions. Use Solver to find this information as follows:
            1. Use cell G16 (Total_Weekly_Profit) as the objective cell in the Solver model, with the goal of determining the maximum value for that cell.
            2. Use the range B4:F5, which shows the number of weekday and weekend training sessions, as the changing variable cells.
            3. Use the constraints shown in Table 3 below.

Table 3: Solver Constraints

 

Constraint	Cell or Range
Each type of program is scheduled at least once on a weekday and once in the weekend	B4:F5
Each weekday and weekend program value is an integer	B4:F5
Each type of program is scheduled 2 times per week or more	B6:F6
Each type of program is scheduled 5 times per week or less	B6:F6
The total number of weekday programs is 10 or less	Total_Weekday_Programs
The total number of weekend programs is 6 or less	Total_Weekend_Programs
The total number of programs per week is 12	Total_Weekly_Programs
Specialist weekday programs are scheduled 5 times per week or less	Special_Weekday_Programs
Specialist weekend programs are scheduled 2 times per week or less	Special_Weekend_Programs
The total number of specialist programs scheduled per week is 4 or less	Special_Total_Programs

 

1. Use Simplex LP as the solving method.
2. Save the Solver model in cell A26.
3. Solve the model, keeping the Solver solution.
4. Blake wants to document the answer Solver found, including the constraints and a list of the values Solver changed to solve the problem. Produce an Answer report for the Solver model as follows:
   1. Solve the model again, this time choosing to produce an Answer report.
   2. Use Training Schedule Answer Report as the name of the worksheet containing the Answer report.
   3. To help determine whether the company will expand into Florida, Blake has asked you to gather data on Florida demographics for the last year.

Collect Florida population data from another Excel workbook, and then use the Get & Transform tools to create a query and load data from this workbook into a new table as follows:

1. Create a new query that imports data from the Support_SC_EX16_CS8-11a_FLPop.xlsxworkbook.
2. Load the data from the 2019 FL Population worksheet to a table in a new worksheet in the SC_EX16_CS8-11a_FirstLastName_2.xlsm workbook, using Florida Population as the name of the new worksheet.
3. Use the Query Editor to remove Column2, which is blank, and the State column, considering that all of the data is for the population in Florida.
4. Use the Query Editor to remove the top row and the bottom 7 rows. Use the first row as headers, and then load the transformed data into the worksheet.
5. Blake also wants to determine the Florida locations for the most severe thunderstorms in the last two years. To provide this information, create another query and load data from another Excel workbook into a new table as follows:
   1. Create a new query that imports data from the Support_SC_EX16_CS8-11a_Weather.xlsxfile, available for download from the SAM website.
   2. Load the data from the Severe Weather Data worksheet to a table in a new worksheet in the SC_EX16_CS8-11a_FirstLastName_2.xlsm workbook, using Florida Thunderstorms as the name of the new worksheet.
   3. Edit the table to display the data Blake requested and make it more useful as follows:

• Use the Query Editor to remove the top 2 rows, which contain a title and blank cells.
• Remove the fifth column, which includes the time, which Blake doesn’t need.
• Use the first row as headers.
• Filter the data to display only “Thunderstorm” in the Type column.
• Sort the data in descending order by Magnitude.
• Keep the top 20 rows of the data.
• Load the transformed data into the worksheet.

1. Add the two queries you created to the data model so you can use them with Power Pivot as follows:
   1. Add the 2019 FL Population query to the data model.
   2. Add the Severe Weather Data query to the data model. (Hint: Do not close the Power Pivot for Excel window after performing this substep.)
   3. Use the Power Pivot for Excel window to create a PivotTable in a new worksheet.
   4. Use Thunderstorm PivotTable as the name of the new worksheet.

Blake wants to know the number of people in the locations that experienced the most severe thunderstorms in Florida last year. Build the new PivotTable to display the severe weather and population data at the same time as follows:

1. Use the following fields from the _2019_FL_Population table in the PivotTable areas:

• County field: Rows box
• Population field: Values box

1. Use the following field from the Severe_Weather_Data table in the PivotTable areas:

• Magnitude field: Values box

1. Create a relationship using the following tables and columns:

• Table: Severe_Weather_Data
• Column (Foreign): Location
• Related Table: _2019_FL_Population
• Related Column (Primary): Municipality

1. Format the Population data using the Number format with 0 decimal places and a thousands separator.
2. Summarize the Magnitude data using the Average calculation, and format the Magnitude data using the Number format with 1 decimal place and no thousands separator.
3. Use Location as the column heading in cell B3, use Population as the column heading in cell C3, and use Magnitude as the column heading in cell D3. Resize column D to its best fit.

Close the Power Pivot for Excel window.

1. To provide another visual representation of the data for the population of the locations for the five most severe thunderstorms, create a Power View report as follows:
   1. Insert a Power View report on a new worksheet, using Power View as the name of the new worksheet. Move the newly created worksheet so that it’s the fourteenth worksheet in the workbook.
   2. Select the County and Population fields (in that order) in the _2019_FL_Population table. (Hint: If a message appears indicating you may need relationships between tables, close the message. You already created the relationships.)
   3. Resize the table to fill the left pane of the Power View area.
   4. Add the County field in the _2019_FL_Population table to the Filters pane.
   5. Filter the data to display population for the counties Bradford, Collier, Franklin, Jackson, and Lee.
   6. Switch the visualization of the data to a Clustered Bar chart.
   7. Use Most Severe T-storms in Florida 2019 as the chart title.
   8. Add hyperlinks to the Home Page worksheet as follows to allow navigation to the new query data:
      1. In cell F7, link the “Florida Population” text to cell A1 of the Florida Population worksheet in the current workbook.
      2. In cell F8, link the “Florida Thunderstorms” text to cell A1 of the Florida Thunderstorms worksheet in the current workbook.
      3. Blake wants to make sure that no unauthorized edits can be made to this worksheet.

Protect the Home Page worksheet with the default settings and P@rma as the password.

Your workbook should look like the Final Figures on the following pages. (The value in cell B4 of the Training Programs worksheet generated by the Goal Seek analysis has intentionally been blurred out in the Final Figure.) The Training Schedule Answer Report, and Training Schedule worksheets are not provided to prevent manual entry of the Solver results.Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.

 

Final Figure 1: Home Page Worksheet

 

Final Figure 2: 2020 Sales by Quarter Worksheet

 

Final Figure 3: 2020 Sales by Project Worksheet

 

Final Figure 4: Sales by State Worksheet

 

Final Figure 5: Training Programs Worksheet

 

Final Figure 6: Attendance Scenario Report Worksheet

 

Final Figure 7: Attendance PivotTable Worksheet

 

Final Figure 8: Training Rates Worksheet

 

Final Figure 9: Thunderstorm PivotTable Worksheet

 

Final Figure 10: Power View Worksheet

 

Final Figure 11: Florida Population Worksheet

 

Final Figure 12: Florida Thunderstorms Worksheet