Complete the following:

Open

Make

Make

starter file and save it as Pacific NW Sales. the data into a table

the following changes to the worksheet:

Apply the None table style

Convert the table to a range

Format columns J: W with comma and two decimal places

Center and bold column headers

If necessary, adjust the column width to accommodate the data

Name the worksheet Pacific NW Region

Calculate the total sales since January 2015 for each company in a column named TotalSales.

In cell AC1 enter the report date of 2/28/2016 and name the cell ReportDate

In column Y, calculate the number of days since each company has placed an order

in a column named DaysLastOrder. Format the values as a number with zero decimal places.

Calculate the number of fractional years that each company has been ordering from the company in a column named relationship years. (Hint: Use the function YEARFRAC and the firstorderdate and lastorderdate format for comma, with one decimal place. Name the Column YEARFRAC and be sure to use Actual/365

Create a PivotTable report from the data in the Pacific NW Region worksheet into a sheet called PivotTableReport.

Add the TotalSales field to the Values area and format as currency.

Add the DaysLastOrder field to the Rows area. After adding the field to the PivotTable report, group records in this field in to groups of 100, starting with the value 0.

Add the StateProv Field to the Columns area, and then add the Company Name field below the Values area to count the number of companies in each state TotalSales field in the Values area.

Use the Value Field Settings dialog box to customize the field names in the PivotTable report as Total Sales and Number of Companies.

Insert a slicer for StateProv and place it under the PivotTable.

Insert a 3-line title over the table

Go back to the Pacific Region worksheet and create a second PivotTable.

Put the LastOrderDate in the Rows area and expand to show by month. (Hint: hit the + in each of the years, Qtr, to expand the data.)

Add CompanyName to the Filters area.

Add Total Sales to the values area

Add NumberofOrders below the TotalSales in the values area

Change the worksheet name to PivotTable 2

Add a Timeline for LastOrder Date and place it below the PivotTable

Format and title the PivotTable appropriately.

Create a PivotChart from the PivotTable. Use a Combo chart, clustered column with line. Under Chart type for TotalSales select Clustered Column. Under Chart type for

NumberofOrders select Line with markers. Then check the box for Secondary Axis on the NumberofOrders Series.

Move the chart to its own chart sheet called PivotChart. Give titles to the secondary vertical and horizontal axis

Format the vertical Y axis values to no decimal places.

Create a chart title that gives the company name and describes what data the chart is displaying.

Verify that all of your PivotTables have titles.

Save your file with your name.