Can you please give me the formulas I need to finish this project?

Steps to complete this project:

Mark the steps as checked when you complete them.

Open the BoydLogistics_Project4-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.

1. Build a two-variable data table.

Select the Offloading tab and review the formula in cell C7.

Create a reference to the formula in cell F10.

Build the Data Table using column and row input cells.

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

FIGURE 1COMPLETED TWO-VARIABLE DATA TABLE

1. Create and manage scenarios.

Create a scenario named 15 Minutes with cell C6 as the changing cell.

Add scenarios for cell C6 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 B5:C7.

Create a scenario summary report for cells C5, C6, and C7 (Figure 2).

FIGURE 2COMPLETED SCENARIO SUMMARY REPORT

1. Get text data.

Select cell A5 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.

1. Prepare descriptive statistics for data.

Install the Analysis ToolPak if it is not installed.

Select cell H5.

Use the Descriptive Statistics tool with cells F5:F61 as the input range and the label in the first row. Cell H5 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).

FIGURE 3COMPLETED ROUTEDATA SHEET

1. Create, move, and format a PivotTable.

Select cell A6 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 C3.

Edit the label in cell D3 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.

1. Copy a PivotTable.

Select and copy the entire PivotTable to cell F3.

Rename the copied PivotTable as pvtProblems.

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

Edit the label in cell G3 to display # Damaged and AutoFit both columns.

1. Insert a calculated field in a PivotTable.

Select cell G4 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 H3 to display Percent and right-align the label.

AutoFit column H.

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

1. 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 A1.

Type By Origin City in cell A2.

Set the font for both labels to 18 pt italic.

Hide the gridlines on the worksheet.

Insert a column at column A. IMPORTANT NOTE: To ensure accurate grading, it is important that you complete this step.

Save the workbook (Figure 4).

FIGURE 4COMPLETED DASHBOARD

1. Create a OneDrive folder and upload a file. IMPORTANT NOTE: This step is not graded. Only complete this step if told to do so by your instructor. Otherwise, move on to step 13.

Create a folder in your OneDrive account named [your initials] Excel ACP 4.

Save a copy of the [your name]-BoydLogistics_Project4-Excel-ACP-4 workbook to the [your initials] Excel ACP 4 OneDrive folder.

Save the workbook as a macro-enabled workbook (.xlsm).

1. Copy a worksheet and prepare the layout for the template.

Copy the RouteData sheet to the end and name the copied sheet RouteDataTemplate. If a security warning displays about external data, accept it.

Delete columns H:I.

1. Nest UNIQUE and SORT to create a list for data validation.

Select cell K5 and nest SORT and UNIQUE to display the destination city names in ascending order.

Select and copy the cells in the resulting array and paste Values in cell K5 to replace the array with labels.

Select and name cells K5:K11 as Cities.

Hide column K.

1. Remove the connection to external data and add data validation.

Display the Queries & Connections pane and delete the connection to BoydLogisticsTxt-04. The data remains in the worksheet.

Delete the contents of cells A6:F61 on the RouteDataTemplate sheet.

Select cells C6:D61 and add a data validation list using the Cities range.

Test your data validation and delete test or sample data.

Select cell A6.

1. Record a macro to time stamp the sheet.

Confirm that the macro is recorded with absolute references.

Record a macro named TimeStamp. Do not use a shortcut and type the description as Insert date and time.

Select cell H2 and enter the TODAY() function.

Press the up arrow to return to cell H2 and press Ctrl+C to copy the function to the Clipboard.

Press Ctrl+V to paste the date in cell H2, click the Paste Options button, and choose Values to replace the volatile function with the date.

Remove the moving border and stop recording.

Delete the contents of cell H2, select cell H5, and test the macro. (Macro code is shown in Figure 5.)

Format cell H2 to display the date and the time with a 24-hour clock. NOTE: You may have to change the Locale (location): to English (United States) to find this date format.

Delete the contents of cell H2.

1. Assign the macro to a Button form control.

Insert a Button (Form Control) at cell H5 and assign the TimeStamp macro to the button.

Edit the caption to Time/Date.

Size the control to be 1" wide and .45" high.

Deselect the control.

1. Unlock cells and set worksheet protection.

Unlock cells A6:F61.

Unlock cell H2 and the button control.

Select cell A6.

Protect the sheet without a password. Allow locked and unlocked cells to be selected and for rows and columns to be formatted.

Copy the RouteDataTemplate sheet to the end and name it CompletedTemplate.

Enter the data shown here, starting in cell A6. The keyboard shortcut to display a validation list is Alt+Down arrow. Enter the dates in mm/dd/yy format.

	Date	Invoice ID	Origin	Destination	Containers	Damaged
6	Type yesterday's date	BL200	Fargo	Chicago	12	0
7	Type today's date	BL201	St. Louis	Minneapolis	8	1
8	Type tomorrow's date	BL202	Kansas City	Omaha	6	0

Time stamp the sheet and save the macro-enabled workbook. Save the workbook as a macro-free Excel Workbook (.xlsx). Click Yes to continue saving as a macro-free workbook. Your dates will not match the figure (Figure 5).

FIGURE 5COMPLETED EXCEL ACP 4 WORKBOOK AND MACRO CODE

1. Share a OneDrive folder. IMPORTANT NOTE: This step is not graded. Only complete this step if told to do so by your instructor. Otherwise, move on to step 24.

Launch your browser and OneDrive.

Share the [your initials] Excel ACP 4 folder with your instructor.

Close OneDrive and return to Excel.

Uninstall the Analysis ToolPak and move or copy the [your name]-BoydLogistics_Project4-Excel-ACP-4.xlsm if instructed to do so.

Upload and save your project file (Excel Workbook .xlsx file)

Submit project for grading.