In Practice Excel $365$: Application Capstone Project $4$

For this project, you review data about container shipments for Boyd Logistics. You build a two$-$variable data table and create related scenarios. You also import text data, display descriptive statistics, and create PivotTables for a dashboard. In addition, you build a macro$-$enabled template with a button control.

$[$Student Learning Outcomes: $8\mathrm{.}1,8\mathrm{.}2,8\mathrm{.}4,9\mathrm{.}1,9\mathrm{.}4,10\mathrm{.}6,10\mathrm{.}7,11\mathrm{.}3,11\mathrm{.}4]$

Files Needed: BoydLogistics$\_$Project$4-$Excel$-$ACP$-4.$xlsx and BoydLogisticsTxt$-04.$txt

Completed Project File Names: $[$your name$]-$BoydLogistics$\_$Project$4-$Excel$-$ACP$-4.$xlsm$,[$your name$]-$BoydLogistics$\_$Project$4-$Excel$-$ACP$-4.$xlsx

Skills Covered in this Project

Build a two$-$variable data table.

Create and manage scenarios.

Import text data.

Calculate descriptive statistics for a cell range.

Create a PivotTable.

Insert a calculated field in a PivotTable.

Move and copy PivotTables.

Design a dashboard with PivotTables.

Insert a slicer for PivotTables.

Save a file to OneDrive.

Share a OneDrive folder.

Record a macro.

Save a macro$-$enabled template.

Assign a macro to a button control.

Steps to complete this project:

Mark the steps as checked when you complete them.

Open the BoydLogistics$\_$Project$4-$Excel$-$ACP$-4$ start file. If the document opens in Protected View, click the Enable Editing button so you can modify it$.$

The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor.

Build a two$-$variable data table.

Select the Offloading tab and review the formula in cell C$7.$

Create a reference to the formula in cell F$10.$

Build the Data Table using column and row input cells.

Format result cells as Number with two decimal places $($Figure $1).$

Completed one variable data table

FIGURE $1$

COMPLETED TWO$-$VARIABLE DATA TABLE

Create and manage scenarios.

Create a scenario named $15$ Minutes with cell C$6$ as the changing cell.

Add scenarios for cell C$6$ named $10$ Minutes, $20$ Minutes, and $25$ Minutes.

Show the $20$ Minutes scenario in the worksheet.

Use the Create from Selection command to assign range names for cells B$5$:C$7.$

Create a scenario summary report for cells C$5,$ C$6,$ and C$7($Figure $2).$

Completed scenario summary report

FIGURE $2$

COMPLETED SCENARIO SUMMARY REPORT

Get text data.

Select cell A$5$ on the RouteData sheet.

Get$/$import BoydLogisticsTxt$-04$ from your student data files. Load the data to the existing worksheet as a table and add it to the Data Model. You need not use Power Query.

Apply Blue, Table Style Medium $6.$

Shrink the table to end at row $61.$

Prepare descriptive statistics for data.

Install the Analysis ToolPak if it is not installed.

Select cell H$5.$

Use the Descriptive Statistics tool with cells F$5$:F$61$ as the input range and the label in the first row. Cell H$5$ is the output range. Include Summary statistics and Kth values to display the largest and the smallest value in the range.

AutoFit the results $($Figure $3).$

Completed RouteData sheet

FIGURE $3$

COMPLETED ROUTEDATA SHEET

Create, move, and format a PivotTable.

Select cell A$6$ and use Summarize with PivotTable to build a PivotTable on a New Worksheet. Deselect the Add this data to the Data Model box before clicking OK$.$ If you do not deselect this box, you will not be able to insert a calculated field in step $8.$

Rename the new sheet as Dashboard.

Show the Invoice ID field in the Rows area and the Containers field in the Values area.

Use Tabular Form as the report layout.

Select and move the entire PivotTable to cell C$3.$

Edit the label in cell D$3$ to display # of Containers and AutoFit both columns.

Apply Light Blue, PivotStyle Medium $6$ and All Borders to the entire PivotTable.

Name the PivotTable as pvtContainers.

Copy a PivotTable.

Select and copy the entire PivotTable to cell F$3.$

Rename the copied PivotTable as pvtProblems.

Remove the Containers field and display the Damaged field in the Values area.

Edit the label in cell G$3$ to display # Damaged and AutoFit both columns.

Insert a calculated field in a PivotTable.

Select cell G$4$ and insert a calculated field named Ratio.

Build a formula to divide Damaged by Containers.

Format the new field to display Percentage with two decimal places.

Edit the label in cell H$3$ to display Percent and right$-$align the label.

AutoFit column H$.$

Insert a slicer for a dashboard.

Insert a slicer for the Origin field.

Position the slicer at row $3$ centered within columns A:B$.$

Apply Light Blue, Slicer Style Dark $5.$

Connect the slicer to both PivotTables.

Filter the data to show Fargo and Omaha information.

Complete the dashboard design.

Insert a blank row at row $3.$ IMPORTANT NOTE: To ensure accurate grading, it is important that you complete this step.

Set the height of rows $1$:$2$ at $35.$

Type Boyd Logistics Container Deliveries in cell A$1.$

Type By Origin City in cell A$2.$

Set the font