Skills Chapter Test

Careers in Practice $4$

Sue, from Pacific NW Outfitters, received some additional data from her distributor that she needs to analyze and give to the marketing group. The data is stored in an Access database. The data includes the company name, address, phone number, first order date, and last. order date for each retailer located in the Pacific Northwest. There is a field that identifies. the total number of orders placed by each retailer for the company's products. The distributor's database also includes total sales by month, from January $2015-$March $2016.$

Complete the following:

Open starter file and save it as Pacific NW Sales.

Make the data into a table.

Make the following changes to the worksheet:

a$.$ Apply the None table style

b$.$ Convert the table to a range.

c$.$ Format columns J: $W$ with comma and two decimal places.

d$.$ Center and bold column headers

e$.$ If necessary, adjust the column width to accommodate the data.

f$.$ Name the worksheet Pacific NW Region

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

In cell AC$1$ enter the report date of $\frac{2}{28}\frac{?}{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.

a$.$ 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.$)$

b$.$ Add CompanyName to the Filters area.

c$.$ Add Total Sales to the values area.

d$.$ Add NumberofOrders below the TotalSales in the values area.

e$.$ 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 NumberofOrdersSeries.

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.