$1.$ Open the Excel workbook provided. Become familiar with the data, including relationships between the data. Consider the data needed to answer management's questions.

$2.$ Begin with the first question: What are the days accounts receivable outstanding by customer type? Assume that credit terms are n$/30$ for all customers.

a$.$ Create a PivotTable.

i$.$ Highlight the data in cells A$1$ through H$28.$

ii$.$ Navigate to the home ribbon then press the following: Insert $-->$ PivotTable.

iii. Verify the correct range is selected.

iv$.$ Verify that the PivotTable report will be placed in a new worksheet.

v$.$ Press OK$.$ This will open a PivotTable in a new sheet.

vi$.$ Rename the new worksheet Days AR Outstanding by right$-$clicking on the worksheet tab, selecting Rename, and typing in the new name.

b$.$ Select the fields for which data needs to be provided. Notice that Avg Days A$/$R Outstanding is provided.

i$.$From the PivotTable Fields box, click and drag Customer Type to the Rows dialog box.

ii$.$ From the PivotTable Fields box, click and drag Avg Days A$/$R Outstanding to the Values box. Note that Excel automatically calculates Avg Days A$/$R Outstanding as a SUM. However, the analysis requires the Average Avg Days A$/$R Outstanding.

$1.$ In the Values box, right click the down arrow to the right of Sum of Avg Days A$/$R Outstanding.

$2.$ Select Value Field Settings.

$3.$ Select Average.

$4.$ Select OK to close the Value Field Settings dialog box.

c$.$ For presentation purposes, sort the table largest to smallest by Avg Days A$/$R Outstanding.

i$.$ Select any box containing Average of Avg Days A$/$R Outstanding.

ii$.$ Right click and select Sort.

iii. Select Largest to Smallest.

$3.$ Next, answer the second question: What are write$-$offs by customer type, both in dollars and as a percent of credit sales?

a$.$ Create a new pivot table.

i$.$ Return to the Data worksheet.

ii$.$ Highlight the data in cells A$1$ through H$28.$

iii. Navigate to the home ribbon then press the following: Insert $-->$ PivotTable.

iv$.$ Verify the correct range is selected.

v$.$ Verify that the PivotTable report will be placed in a new worksheet.

vi$.$ Press OK$.$ This will open a PivotTable in a new sheet.

vii. Rename the new worksheet Write$-$Offs by Vendor Type by right$-$clicking on the worksheet tab and selecting rename.

b$.$ Select the fields for which data needs to be provided.

i$.$ From the PivotTable Fields box, click and drag Customer Type to the Rows dialog box.

ii$.$ From the PivotTable Fields box, click and drag Total Write$-$Offs of AR to the Values dialog box.

iii. Because management requested write$-$offs by both customer type and percent of credit sales, create a "Write$-$Offs as Percent Credit Sales" calculated field.

$1.$ In the Home Ribbon, be sure you are in the PivotTable Analyze tab.

$2.$ Click Fields, Items, & Sets.

$3.$ Click Calculated Field.

$4.$ Name the field Write$-$Offs as Percent Credit Sales.

$5.$ The calculation will be $($Total Write$-$offs of A$/$R$)/($Total Credit Sales$).($Note: Be sure to remove $=0$ from the formula bar.$)$

a$.$ From the Fields dialog box, select Total Write$-$offs of A$/$R and click Insert Field.

b$.$ In the Formula field, input $/$ to denote division.

c$.$ From the Fields dialog box, select Total Credit Sales and click Insert Field.

d$.$ Click OK$.$

$6.$ The newly created Write$-$offs as Percent of Credit Sales field should be in the PivotTable. If not, select the newly created Write$-$offs a Percent of Credit sales field and drag it to the Values dialog box.

a$.$ Excel attempts to format this as a dollar amount; however, a percentage is desired.

b$.$ In the Values dialog box, right click on the down arrow net to Sum of Write$-$offs as Percent of Credit Sales and click Value Field Settings.

c$.$ Click Number Format in the left$-$hand bottom corner of the PivotTable Field pop$-$up$.$

d$.$ Select Percentage and then click OK$.$

e$.$ Click OK to exit the PivotTable Field pop$-$up$.$

iv$.$For presentation purposes, format the Sum of Total Write$-$Offs as currency.

$1.$ In the Values dialog box, right click on the down arrow next to Sum of Total Write$-$offs and click Value Field Settings.

$2.$ Click Number Format in the left$-$hand bottom corner of the PivotTable Field pop$-$up$.$

$3.$ Select Currency and then click OK

$4.$ Click OK to exit the PivotTable Field pop$-$up$.$

$4.$ Perform the final analysis: What are returns as a percent of credit sales by customer type?

a$.$ Create a new pivot table.

i$.$ Return to the Data worksheet.

ii$.$ Highlight the data in cells A$1$ through H$28.$

iii. Navigate to the home ribbon then press the following: Insert $->$ PivotTable.

iv$.$ Verify the correct range is selected.

v$.$ Verify that the PivotTable report will